mysql的索引及索引误区详解

在数据量和访问量不大的情况下（正常小于1000条记录），mysql访问是非常快的，是否加索引对访问影响不大。但是当数据量和访问量剧增的时候，就会发现mysql变慢，甚至down掉，这就必须要考虑优化sql了，给数据库建立正确合理的索引，是mysql优化的一个重要手段。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

聚簇索引和非聚簇索引

在了解这俩个概念之前小了解下主索引和二级索引（辅助索引）。使用主键建立的索引为主索引，主键之外的索引统称二级索引。一张表里只能有一个主索引，可以有多个二级索引。
在innodb引擎中使用的是聚簇索引，myisam是非聚簇索引。聚簇索引和非聚簇索引并不是一种单独的索引类，而是一种数据存储方式，聚簇索引的顺序就是数据的顺序。

innodb的聚簇索引
什么是聚簇索引呢？InnoDB默认对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。一般来说，InnoDB 会以聚簇索引的形式来存储实际的数据，一般主索引就是聚簇索引。
1. InnoDB的主键采用聚簇索引存储，使用的是B+Tree作为索引结构，其叶子节点存储的是索引值和数据本身。
2. InnoDB的二级索引不使用聚蔟索引，叶子节点存储的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。
3. innodb中的数据排列顺序是根据聚簇索引的顺序排序的。因此适合按聚簇索引的区间查找，可以有更少的磁盘I/O，加快查询速度。也是因为这个原因，聚簇索引的插入顺序最好按照主键单调的顺序插入，否则会频繁的引起页分裂，严重影响性能。也就是建议主键自增的原因
4. 聚簇索引内部实现了将相类似的数据存放在一起，当需要查询相类似的内容时，只需要查询比较少的数据页就可以实现对数据的获取
5. 采用聚簇索引查询速度会更快，原因是聚簇索引的每一个索引值的叶子节点就为数据，只要查询到对应的主键索引值，就可以查询到对应的哪一行数据，读取速度相对来说提升了不少。而MyISAM需要特别为索引创建存储空间来存放，在查询过程中还要到磁盘中进行数据的读取
引擎为innodb的表，分为俩文件.frm和.ibd,.fram文件为表结构，.idb为表的数据文件和索引文件
myisam的非聚簇索引
MyISAM的主键索引和二级索引都是非聚簇索引。
MyISAM的主键索引和二级索引叶子节点存储着索引字段对应的索引值以及索引值所在行对应的物理地址，然后myisam再根据物理地址到数据表里查找数据。

在myisam中的表由3个文件组成，.frm文件、.MYD文件和MYI文件。.frm是表的结构文件，.YMD是表的数据文件，YMI是表的索引文件，和innodb相比，myisam的索引文件和数据文件是分开的，也就是说myisam使用索引时会多一次io操作（覆盖索引除外）

myisam中的数据排列顺序是根据数据的插入顺序存储
- innodb和myisam的主键和二级索引的区别
  
  WX20190620-151935.png

以一本英文课本为例，要找第8课，直接翻书，若先翻到第5课，则往后翻，再翻到第10课，则又往前翻。这本书本身就是一个索引，即“聚簇索引”。
如果要找"fire”这个单词，会翻到书后面的附录，这个附录是按字母排序的，找到F字母那一块，再找到"fire”，对应的会是它在第几课。这个附录，为“非聚簇索引”。由此可见，聚簇索引的顺序就是数据存放的顺序，非聚簇索的顺序与数据存放的顺序无关。所以，很容易理解，一张数据表只能有一个聚簇索引。

在《数据库原理》一书中解释：聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

另外有人拿聚簇索引和覆盖索引来比较，首先使用聚簇索引的话查询效率会很快，直接在聚簇索引的叶子节点就可以拿到数据。覆盖索引是一种索引优化的手段，比如在myisam中，索引文件和数据数据文件是分开的，覆盖索引是直接在查询索引文件时就能拿到查询数据而不需要回表数据文件，减少一次io，查询速度也会大大增加。

思考：为什么innodb的二级索引的叶子节点不直接存储对应的物理地址而是存储对应聚簇索引的值呢？
在表结构发生改变时候会有很大的优势。在innodb中的表数据是按照聚簇索引的顺序存储的，比如主键为自增的id且int类型，在删掉一条数据后，这条数据后的其他数据地址都会改变，这时二级索引存储的物理地址都得跟着改变。而存储为聚簇索引的值时就不会出现这种情况。

注意：更改表结构包括创建索引时需要对表加锁，禁止数据读写，因此需要在业务空闲的时候创建，在生产环境最好不要创建索引。

mysql索引及使用场景

mysql索引包括索引类型和索引方法。
mysql的索引类型包括

normal
用表中的普通列构成的索引，没有任何的限制
unique
构成唯一索引的列的值必须是唯一的，但是允许有null值。主键就是一个唯一索引
fullText
用大文本对象的列构建的索引
spatial
primary
主键索引，构成索引的列的值必须是唯一且不为null的。

mysql的索引方法包括

Btree
在navicat中默认是有俩种索引方法，一种是Btree另一种就是hash，hash只有在memory存储引擎中才会使用。一般像innodb和myisam存储引擎使用Btree即可。
hash
只有memory存储引擎才能使用hash索引，hash索引速度非常快，要快于Btree索引很多。哈希索引使用索引列的值然后计算该值的hash(value)，然后在hash(value)相应的位置存储该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hash(value)，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

索引的优缺点
优点：加快查询速度，也就是select操作，减少i/o次数，加快检索速度。根据索引分组和排序，可以加快分组和排序，也就是group by和order by。
缺点：在myisam中有单独的索引文件，在innodeb中索引文件和数据文件是一个文件。索引需要占用磁盘空间，索引越多，占用的空间就越大。其次创建索引表需要时间，这个时间成本会随着数据量的增大而增大。最后构建索引会降低数据的修改效率(update,insert,delete操作),因为在修改数据的同时也要修改索引。

查询语句判断索引是否生效

将select语句前面加上explain关键字，即mysql查询优化器explain，常用于优化sql，主要看参数type。在命令行界面输入，如下

mysql> explain select * from bl_message where company_id=2;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra             |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | bl_message | NULL       | ALL  | company_id    | NULL | NULL    | NULL | 13   | 84.62    |      Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

主要关注几个参数

type:代表检索的类型，如果是all则代表检索的是全表，也就是没使用索引或者索引失效，具体类型下面会介绍。
possible_keys:查询可能使用到的索引key_name，并不是索引的colume_name。这个参数没太大意义。下面也会介绍。
key:查询过程中使用到的索引key_name
rows:查询过程中检索的行数也叫扫描的行数，理想情况下扫描行数和返回的行数是一致的。如果这里没有使用索引，rows值一定是整个表的行数，如果使用了索引，就会是索引扫描的行数，比如上述中company_id为2的值在数据库中有俩条数据，此时rows就是2,这种情况是最好的，精确定位，也是好的优化方案。比如有一个company_id的索引，而查询条件是where company_id=2 and name ='小雨'，这时数据库中数据符合该条件的比如是5条，而该rows可能是20条。rows并不是符合查询语句的行数，而是检索的行数，也就是说company_id为2的行数有20条，然后在从这20条中筛选出了5条。

不常用的一些参数

select_type
1. simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个，这个是最常见的。
2. primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
3. union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
4. dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
5. union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
6. subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
7. dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
8. derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
table：显示的查询表名，如果查询使用了别名，那么这里显示的是别名。
type: 优化性能依次从好到差，sql调优主要看这个参数即可：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
1. system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
2. const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
3. eq_ref：出现在要连接过个表的查询计划中也就是join或leftjoin，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
4. ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
5. fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
6. ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
7. unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
8. index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
9. range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
10. index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
11. index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、如使用索引排序order by colume desc limit n或者分组的查询groub by。
12. all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。也就是没有使用索引

介绍一下索引的key_name和索引的colume_name的区别

下图是查看bl_message的所有索引。每个表至少有一个PRIMARY索引，也就是主键。其次就是一个索引列colume_name对应一个索引key_name,那么问题来了，我如果是一个组合索引，那是不是多个colume_name对应一个key_name呢（意思是一条数据）？答案是多条记录，图中就是一个title列和company_id列的一个组合索引，索引名叫做title,我们平时叫的索引也就是title

mysql> show index from bl_message;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------  +---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part |   Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------  +---------+---------------+
| bl_message | 0          | PRIMARY  | 1            | id          | A         | 13          | NULL     | NULL   |      | BTREE          |         |               |
| bl_message | 1          | title    | 1            | title       | A         | 5           | NULL     | NULL   | YES  | BTREE      |         |               |
| bl_message | 1          | title    | 2            | company_id  | A         | 7           | NULL     | NULL   | YES  |     BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

使用索引的场景

最常见的where条件的列加单索引
where and条件使用组合索引

对order by条件中出现的列建立索引
排序使用了索引字段，相当于全表扫描。就算加了limit n 也是全表扫描，type为index，index类型就是索引全表扫描，常用于排序和分组。

mysql> explain select * from users order by id desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len   | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | PRIMARY | 4       | NULL | 10   | 100.00   | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+

对group by条件中出现的列建立索引
对使用聚合函数的列也要加索引，max(column_1)或者count(column_1)时的column_1就需要建立索引

不使用索引的场景

经常增删改查的列不要建索引，因为修改数据的同时，索引也是要修改的。
有大量重复的列不要创建索引。mysql查询结果要小于30%才会使用索引，不然会使用全表扫描。mysql优化器认为全表扫描的成本小于索引，所以放弃索引，这是很多情况下没使用索引的原因。下面会具体介绍。所以具有唯一性或者重复性很少的列建立索引会非常有效
表记录太少不要建立索引，只有当数据库已经有列足够多的数据时，它的性能才会有实际参考价值。只有当记录超过1000条数据时，数据总理也超过了mysql服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的场景

以下图为基准，name字段中有null值

数据库

在组合索引中某一索引列有null值，则索引失效。这句话其实是不对的，在单列索引中索引列有null值不会失效。在组合索引中索引列有null值也是可以使用组合索引的，MySQL难以优化引用了可空列的查询，它会使索引、索引统计和值更加复杂。可空列需要更多的储存空间，还需要在MySQL内部进行特殊处理。当可空列被索引的时候，每条记录都需要一个额外的字节，还可能导致 MyISAM 中固定大小的索引(例如一个整数列上的索引)变成可变大小的索引，所以尽量避免null值
LIKE操作中，like name '%aaa%',则name索引会失效，但是like name ‘aaa%’是可以使用索引。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。

在查询条件中索引列使用is null或者is not null会导致索引失效。这个说法是不对的，数据表如下图，新建一个单列索引,索引列为name

 mysql> explain select * from users where name is null;
+----+-------------+-------+------------+------+---------------+------+---------+-------+--  ----+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+
| 1  | SIMPLE      | users | NULL       | ref  | name          | name | 1023    | const | 1    | 100.00   | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+

mysql> explain select * from users where name is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len |   ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | name          | NULL | NULL    | NULL | 10   | 90.00    | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

可以看出name is null时使用了name索引，而name is not null 时未使用索引。这也是因为上述所说的mysql查询结果在30%以内才会使用索引导致的。所以准确的说是查询条件使用is null或者is not null时可能会导致索引失效。

字符串在单列索引中不加单引号会导致索引失效。更准确的说是类型不一致会导致索引失效。如下password为varchar类型，且有一个password的单列索引。

mysql> explain select * from users where password=11;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | password      | NULL | NULL    | NULL | 10   | 10.00    | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------        +----------+-------------+
1 行于数据集 (0.03 秒)

  mysql> explain select * from users where password='11';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  | 1  | SIMPLE      | users | NULL       | ref  | password      | password | 1022    | const | 1    | 100.00   | NULL  |
  +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引。这时应该改为两次查询，然后用UNION ALL连接起来。还是以上图为数据库，有一个索引为password。

mysql> explain select * from users where password='11';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | ref  | password      | password | 1022    |   const | 1    | 100.00   | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 行于数据集 (0.02 秒)

mysql> explain select * from users where password='11' or name ='11';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | password      | NULL | NULL    |       NULL | 10   | 19.00    | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> explain select * from users where name='11' union all select * from users where password='11';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1  | PRIMARY     | users | NULL       | ref  | merge         | merge | 1023    | const | 1    | 100.00   | NULL        |
| 2  | UNION       | users | NULL       | ALL  | NULL          | NULL  | NULL    | NULL  | 10   | 10.00    | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+

然后在加一个单列索引name索引列

mysql> explain select * from users where password='11' or name ='11';
+----+-------------+-------+------------+-------------+---------------+---------------+-----------    +------+------+----------+-----------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key           |   key_len   | ref  | rows | filtered | Extra                                   |
+----+-------------+-------+------------+-------------+---------------+---------------+-----------+------+------+----------+-----------------------------------------+
| 1  | SIMPLE      | users | NULL       | index_merge | password,name |       password,name | 1022,1023 | NULL | 2    | 100.00   | Using     union(password,name); Using where |
+----+-------------+-------+------------+-------------+---------------+---------------+--------      ---+------+------+----------+-----------------------------------------+

可以看出上述使用列俩个索引分别为password和name,且type为index_merge,这个级别优化也是太好。那么试试将password和name改为组合索引使用or有效果吗？

mysql> explain select * from users where name ='11' or password='11';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | password      | NULL | NULL    |   NULL | 10   | 19.00    | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 行于数据集 (0.03 秒)

mysql> explain select * from users where password='11' or name ='11';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | password      | NULL | NULL    | NULL | 10   | 19.00    | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

事实可见组合索引对or不起作用，只对and查询有用，这时该sql应该拆分称俩句sql然后使用union all连接。

select * from users where password='11'
union all
select * from users where name='11'

如果排序使用了索引，而select列未使用索引列，则该索引失效，这是因为优化器执行直接执行全表扫描速度更快。主键索引除外，任何一张表都有一个唯一索引primary,索引列为主键列。如下新建列一个password索引，索引列为account。

mysql> explain select * from users order by account desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   | 100.00   | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 行于数据集 (0.05 秒)

mysql> explain select account from users order by account desc;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | password | 4       |   NULL | 10   | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 行于数据集 (0.12 秒)

mysql> explain select account from users order by id desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | PRIMARY | 4       | NULL | 10   | 100.00   | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 行于数据集 (0.05 秒)

mysql> explain select * from users order by id desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | PRIMARY | 4       |       NULL | 10   | 100.00   | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+

在查询语句中where也使用了索引，order by 也使用了索引。这时where 中的索引生效，order by中的索引失效。当where中的索引失效后order by中的索引才会生效。有一个merge的组合索引，向左原则包括account索引，id为主键，为primary索引。

mysql> explain select * from users where account='11' order by id desc;
+----+-------------+-------+------------+------+----------------+-------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key   | key_len | ref   | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+----------------+-------+---------+-------+------+----------+---------------------------------------+
| 1  | SIMPLE      | users | NULL       | ref  | merge,password | merge | 4       |   const | 1    | 100.00   | Using index condition; Using filesort |
+----+-------------+-------+------------+------+----------------+-------+---------+-------+------+----------+---------------------------------------+
1 行于数据集 (0.11 秒)

mysql> explain select * from users where password='11' order by id desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | PRIMARY | 4       |       NULL | 10   | 10.00    | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

在查询条件中索引(包括字符串索引和整形索引，主键除外)使用!=大多数会失效，因为!=筛选后的数据大多数大于整表数据的30%,使用索引的代价大于直接在数据文件中查询，所以大多数情况会失效。在整形索引中使用<,>和!=的效果一样，大多数情况下会失效。为什么主键索引使用!=,>,<都不会失效呢？因为主键索引就是聚簇索引，聚簇索引的顺序就是数据文件的顺序，在数据文件中查询主键><或者!=肯定大于全表扫描啊，因为主键索引也是有序的。

mysql> explain select * from users where password  ='11';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | ref  | password      | password | 1022    | const | 1    | 100.00   | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 行于数据集 (0.07 秒)

mysql> explain select * from users where password  !='11';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | password      | NULL | NULL    | NULL | 10   | 100.00   | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

上图有一个password索引为字符串索引，索引列为password。

mysql> explain select * from users where account =11;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | ref  | account       | account | 4       | const | 1    | 100.00   | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

1 行于数据集 (0.04 秒)

mysql> explain select * from users where account !=11;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | ALL  | account       | NULL | NULL    | NULL | 10   | 100.00   | Using     where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 行于数据集 (0.04 秒)

mysql> explain select * from users where account > 11;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1  | SIMPLE      | users | NULL       | range | account       | account | 4       | NULL | 1    | 100.00   | Using     index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 行于数据集 (0.04 秒)

上图是一个account索引为int索引，索引列为account,结果显示只有在使用!=时候才会失效。

组合索引最左原则问题

网上说组合索引遵循最左原则。比如有一个组合索引有3个列，account,name,phone。这时相当于新建了3个索引，分别是accout、account和name。而查询只能是以下3种才会使用索引。

select * from users where account='1';
select * from users where account='1' and name='22';
select * from users where account='1' and name='22' and phone='15942075450';

而具体测试并不只是上面的那3种情况。现在索引还是上面那个索引测试，数据库以上面的图为准。
如下情况也是可以使用索引的

  mysql> explain select * from users where name='22' and account='11';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
  | 1  | SIMPLE      | users | NULL       | ref  | merge         | merge | 1027    | const,const | 1    | 100.00   | NULL  |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------+
  mysql> explain select * from users where name='22' and account='11' and phone='111';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref               | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
  | 1  | SIMPLE      | users | NULL       | ref  | merge         | merge | 2050    | const,const,const | 1    | 100.00         | NULL  |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
  1 行于数据集 (0.04 秒)
  mysql> explain select * from users where name='22' and phone='111' and account='11';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref               | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+
  | 1  | SIMPLE      | users | NULL       | ref  | merge         | merge | 2050    | const,const,const | 1    | 100.00     | NULL  |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------------+------+----------+-------+

上面的这些情况顺序是可以颠倒的，也就是最左原则失效了？其实是mysql优化器会优化where子句的条件顺序，让查询符合索引顺序，那么你可能会有疑问顺序颠倒后经过mysql优化器效率会不会降低，经测试是基本没有什么影响。最左原则是指：mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，所以这两条都是会命中索引的。要记住和顺序是无关的

为什么只能匹配最左呢？其实这个和like一样，只能匹配类似like 'a%'这种的，而不能匹配类似like '%a%',理解了like的原理自然就理解为什么只能最左匹配了

为什么下面这种情况还会用到merge索引呢？

  mysql> explain select * from users where phone='111' and account='11';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                 |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+
  | 1  | SIMPLE      | users | NULL       | ref  | merge         | merge | 4       | const | 1    | 10.00    | Using index condition |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-----------------------+

上面的where条件并没有用到merge中的3个索引，为什么还是使用到索引了呢？由上面可以看到Extra值为Using index condition,意义为二次查询。首先要知道最左原则是没有顺序的，上面的where中可以看到由account='11'，也就是说这里使用到了merge索引，然后在merge索引中找到对于数据行的id,根据该id在找到数据表中的数据行（回表），然后在这些数据行中筛选phone为11的数据行，这就是Using index condition，大概意思就是即使用了索引，又得需要回表，最终得到数据。
由此得出结论：只有where条件中有组合索引中的第一个字段就肯定会使用索引。

单列索引和组合索引优先级问题

组合索引是优先于单列索引的。比如现在有一个account索引为account索引列,和一个merge组合索引拥有account,name,phone索引列。

mysql> explain select * from users where account='11';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | ref  | merge,account | merge | 4       | const | 1    | 100.00   | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 行于数据集 (0.04 秒)

从上述可以看出possible_keys有merge和account俩个索引，key为merge。也就是组合索引优先级于单列索引。

联合索引相对于单列索引的优点

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加更新操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引表取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

sql查询常用的优化方法

mysql是存储在磁盘上的一个个文件，减少了mysql的交互也就减少i/o的交互，i/o交互是阻塞的。所以要减少mysql的交互。

减少索引长度：设置索引时可能的话应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。在navicat中设置索引长度如下。

添加索引长度

覆盖索引：查询列也就是select的字段要被所使用的索引覆盖,查询列中可包括主键。覆盖索引其实就是减少了一个回表的过程，直接在索引表里面就获得了自己想要的字段和数据。索引是一张表，这个列表存储着索引字段和索引字段对应的索引值以及索引值所在行对应的物理地址。索引可以不用扫描全表来定位数据，而是直接找到该值对应的物理地址然后访问相应的数据。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。以下为几种覆盖索引的案例。

还是以上面的数据库为准，无where条件查询时

mysql> explain select account from users;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   | 100.00   | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

可以看出type为all，为全表扫描，接下来我们可以新建一个索引为account,索引列为account

mysql> explain select account from users;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | account | 4       | NULL | 10   | 100.00   | Using   index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

可以看出type使用了index,不是全表扫描了，然后使用了索引account,在Extra列中的值为using index,using index是🈯️查询时不需要回表查询，直接通过索引就可以获取查询的数据。

二次检索优化
前提是有一个account索引，索引列为account为整数类型。

mysql> explain select name from users where account >5;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1  | SIMPLE      | users | NULL       | range | account       | account | 4       | NULL | 1    | 100.00   | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+

可以看出Extra为Using index condition，即代表二次检索。这里查询列是name，而索引中没有这个列，所以要使用覆盖索引的话，需要新建一个组合索引merge,索引列组合为account和name，有俩个索引，一个是account单列索引，另一个是account和name的组合索引。

mysql> explain select name from users where account >5;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | merge | 1027    | NULL | 10   | 33.33    | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+--------------------------+

可以看出Extra值变为了Using index了

排序优化
前提是有一个account索引，索引列为account为int类型。

mysql> explain select id,name from users order by account desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1  | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10   | 100.00   | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

可以看出上述sql未使用索引，新建了account索引为什么没使用呢？上面已经介绍过了，因为select的字段中没有account。接下来我们使用覆盖索引来优化，新建一个组合索引merge，索引列为account和name。

mysql> explain select id,name from users order by account desc;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | users | NULL       | index | NULL          | merge   | 1027    | NULL | 10   | 100.00   | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

可以看出上述使用了merge索引，且Extra为Using index,使用了覆盖索引。上面不是说覆盖索引是查询列包含在所使用的索引中吗？那么id也不包含在merge索引中啊，id为主键，这就是primary索引的不同。

总结：覆盖索引适用于select查询列比较少的情况下，这样不需要回表，查询更加优化

常见问题

组合索引中有3个索引列，那么where and中必须使用这3个索引列吗？
还是以上图的数据库为例，此时有一个组合索引为merge,有索引列为name,phone，account。

  mysql> explain select * from users where name='1' and phone='22' and password!='11';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra         |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
  | 1  | SIMPLE      | users | NULL       | ref  | merge         | merge | 2046    | const,const | 1    | 90.00    | Using where |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+

password不在索引列，此时也是使用了merge索引。执行顺序应该是name='1' and phone='22'使用merge索引查出一组数据，然后在这组数据中在筛选出password=11的数据。所以也是使用了merge索引。

phone使用!=会使索引失效吗

  mysql> explain select * from users where name='1' and phone='22' and password!='11';
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
  | 1  | SIMPLE      | users | NULL       | ref  | merge         | merge | 2046    | const,const | 1    | 90.00    |   Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+

上述不是说索引使用！=会使用索引失效吗？为什么这里还是使用了merge索引呢？
merge索引有3个索引name,name和phone，name和phone和account。由于！=会使索引失效，索引这里使用的是merge索引中的name和phone，也就是在name和phone索引中查出一部分数据，然后从这些数据中在根据条件password!='11'来筛选对应的数据。

加了一个单列索引，但是没使用到索引
还是以上图中的数据库为准，新建了一个account索引，索引列为account。

  mysql> explain select * from users where account =2;
  +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
  | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
  +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
  | 1  | SIMPLE      | users | NULL       | ref  | account       | account | 4       | const | 2    | 100.00   | NULL  |
  +----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
  1 行于数据集 (0.07 秒)

  mysql> explain select * from users where account =1;
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | 1  | SIMPLE      | users | NULL       | ALL  | account       | NULL | NULL    | NULL | 10   | 70.00    | Using     where |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

由上图看出，account=2时使用了索引，而account=1时未使用索引，这是为什么？
因为mysql优化器认为全表扫描的成本小于使用索引，所以放弃使用索引了。account字段字段中为1的数据超过了30%，所以mysql认为扫描全表的代价更低，

以上是自己的一些总结和理解，如果有不正确的地方，留言指正。

补充

mysql中的数据是按照数据页的形式存储的。这补充三点关于数据页的问题：

为什么innodb主键建议自增
innodb的主键是聚簇索引，聚簇索引的叶子节点存储的是索引值和数据行。如果使用b-tree索引的话，数据存在有序性，也就是说聚簇索引的排列顺序就是数据的排列顺序。如果使用自增的方式，数据就会在数据页中有序排列，该数据页存满之后才会存到下一页，这样空间可以得到充分利用，最重要的是性能也可以提升。如果不是自增的话，每次数据的插入不能保证是最后一行，往往是插入到俩行数据的中间位置，这样就造成了大量后面的行移动，进而造成很多的页分裂，由于数据不是连续的，这就造成的数据碎片化。文件大小和数据页多少有直接关系。
垂直分库的原因
当表中有大字段如text类型的、或者是表中字段非常多就需要垂直分表。你可以把一张数据表理解为一本书，索引为目录，表中所有数据为整本书的内容，而书中内容是以每页的形式展现给你的， $\color{blue}{恰巧你查询后返回的mysql数据也是以每页每页的形式给你的}$ ，也就是所说的数据页。由于每页的字书是固定不变的,也就是说如果有大字段的话，它会占用你每页中的很多空间，接着每页查询的记录数就会变少，查询的页数就会变多。你看书时一个内容明明一页可以看完，现在要翻俩页，简单点说就是读的效率降低了，也就是说 $\color{blue}{垂直分表能够提高读的速度。}$
怎么解决数据页的碎片化
删除表中大量数据后，发现表的数据文件大小并没有减少，这是为什么呢？mysql删除大量数据后，并不会删除数据页，只是清除了数据页中的数据而已，这就是在程数据页碎片化的原因。
为什么要设计程这样呢？有俩点原因：
1.不删除数据页，这样可以避免后面的大量数据的地址往前移动，往前移动肯定消耗cpu且这些移动的数据必须加锁。
2.当有新的数据插入时，会被插入到这些闲置的数据页中，充分利用这些空白的数据页。
缺点：
1.缺点很明显了，空白的数据页会占用很多的磁盘空间。

其实上面删除数据的情况我们可以理解为，mysql为一本书，每页存储着很多数据，当需要删除某页的数据时，我们并不是把这页给撕掉，而是拿橡皮擦把这页中的所有数据给擦掉了，当写入新数据时，只需要在这个空白页内写入即可。

mysql> optimize table users;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                          |
+--------------+----------+----------+-------------------------------------------------------------------+
| xuehua.users | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| xuehua.users | optimize | status   | OK                                                                |
+--------------+----------+----------+-------------------------------------------------------------------+

如果innodb使用该命令会有上述的提示。
注意：1. 该命令锁表。2.该命令只使用与myisam和innodb 。3.该命令不需要每次运行，每周一次或者每月一次即可
使用该命令后也就是数据在物理存储达到了最优

范围查询
explain中很难区分是范围查询值还是查询列表值(in),explain的type返回的都是range,所以这里范围查询分俩类。
第一类： >,<,between,like操作

使用>,<,between,like操作后面的索引列时，当筛选的数据大于表数据的30%左右时，由于需要回表的操作，使用索引的代价就会大于直接在数据表中全表扫描的代价，以至于不使用索引。举个例子，比如user_id为索引，查询语句为select * from a where user_id>3,且发现索引文件中的user_id>3的数据占据50%。如果这条sql需要使用索引，首先得去索引文件中查出这50%的数据，然后根据user_id索引中的主键值在回表查询，注意回表操作也是消耗性能的，然后在数据表中在接着查询这50%的数据，获取select的相关字段，通过这一系列的操作，综合性能不如直接在数据文件中全表扫描100%的数据来的快，所以这种情况是不使用索引的，如果索引文件中的user_id>3的数据占据很少的部分，那么综合下来使用索引会比直接全表扫描的代价小的多，所以最后mysql优化器会选择使用索引。
总结：>,<,!=,between,like这些操作是否使用索引的决定性因素就是查询的数据所占比例是否超过来30%

比如我创建了一个复合索引nick，列为nickname和picking_code。
执行explain操作，发现扫描了31行。

mysql> explain select id from shopping_order_info where  nickname like '吴%' and picking_code=2160 ;
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table               | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | shopping_order_info | NULL       | range | nick          | nick | 1027    | NULL |   31 |    10.00 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

我们在执行下面的查询操作。

mysql> select count(*) from shopping_order_info where  nickname like '吴%';

+----------+
| count(*) |
+----------+
|       31 |
+----------+
mysql> select count(*) from shopping_order_info where nickname like '吴%'  and picking_code=2160 ;
+----------+
| count(*) |
+----------+
|        1 |

发现 select id from shopping_order_info where nickname like '吴%' and picking_code=2160 该查询语句中like之后的类picking_code并没有用到索引。所以证实了我们的说法。

我们继续验证。我重新创建了一个复合索引nick，列为picking_code和nickname，与上面那个索引列调换顺序。

mysql> explain select id from shopping_order_info where picking_code=2160 and nickname like '吴%';
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table               | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | shopping_order_info | NULL       | range | nick          | nick | 1027    | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

或者（为什么下面这个也能使用索引？查看上面的最左原则解析）

mysql> explain select id from shopping_order_info where nickname like '吴%' and picking_code=2160;
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table               | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | shopping_order_info | NULL       | range | nick          | nick | 1027    | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

我们看到此时筛选的列已经变为1条。所以说>,<,between,like后面的列将不会使用索引，创建索引时要选择合适的列顺序。

注意：between就相当于>和<的集合，所以使用between时能用到索引的也就是>的条件。

第二类： in操作

mysql可以使用in操作后面的索引列

我创建了一个复合索引nick，列为picking_code和nickname。
我们执行explain 查询计划。

explain select id from shopping_order_info where picking_code in(4105,9967) and  nickname = '吴淋';
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table               | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | shopping_order_info | NULL       | range | nick          | nick | 1027    | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+---------------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+

然后继续查询

mysql> select count(*) from shopping_order_info where picking_code in(4105,9967) and  nickname = '吴琳';
+----------+
| count(*) |
+----------+
|        2 |
+----------+

发现in后的字段确实可以继续走索引。

我们验证了该猜想。

explain中type为inde理解

表示从头到尾扫一遍索引文件，而没有回表查询数据。简而言之，就是使用覆盖索引的方式在索引文件中扫描了所有数据，而没有回表,常用于order by 或者group。
俩个关键字：所有数据和覆盖索引。即order by 或者group的字段使用了索引（主键索引除外）且实现了覆盖索引，整个sql没有其他筛选的条件，类似where。

表字段如下

| users | CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sex` tinyint(255) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
KEY `33` (`sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

我们执行以下sql,查看执行计划

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|       19 |
+----------+
mysql> explain select sex from users order by sex asc;
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | 33  | 1       | NULL |   19 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----+---------+------+------+----------+-------------+
mysql> explain select name from users order by sex asc;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   19 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

type为index和all都是扫描全表，为何index的效率略高于all?

innodb索引为聚簇索引，聚簇索引包含表的所有字段，而非聚簇索引只包含索引字段和id。

我们知道了sex索引只包含了sex和id字段，而主键索引（聚簇索引）包含id，name和sex字段。

我们知道type为index和type为all,筛选的数据行是一样的，唯一的区别就是index是在索引中筛选数据，而all是在数据表,也可以理解为在聚簇索引中筛选数据。

当select sex时，返回给用户的是只带有sex字段的行数据。其实当type为all时mysql的查询过程是先查询出符合筛选条件的所有行数据，这些行数据包含所有的表字段，最后再筛选出sex字段的行数据。type为index是也是从索引字段中筛选出sex字段的行数据返回给用户的。

如上的sql使用了sex索引，那么就只是从sex和id字段中筛选sex字段，如果没有使用索引，而type为all时，就在整个表的字段中筛选sex字段（本表的字段较少）。到此哪个效率更高，我们也就很明了了。

常见sql分析

首先明确一条sql的查询顺序，查询顺序其实从左到右,以下面的例子

select name from users where class=3 group by grade order by email asc

查询顺序就是：先在users表中筛选class为3的数据，然后从上次筛选的数据中给grade分组再次筛选中新的数据，最后在从grade分组后的新数据按照email的升序排序，最后得出所需数据。说白了，查询顺序就是你sql语句从左到右的顺序。也更加表明了sql选择索引的顺序就是sql的查询顺序，比如现在有class字段的索引，grade字段的索引和email字段的3个索引，该sql最后选择的索引也就是最先执行的索引class索引了。

其次要了解索引的执行顺序：首先要知道覆盖索引的效率(using insex)肯定大于回表的效率。索引(innodb)执行顺序就是首先查看索引列是否包含select的所有字段，如果全部包含则直接覆盖索引，查询结束，否则根据索引找到对应的id,然后根据查询的id到数据表中找到对应的数据行，然后在这些数据行中筛选索引列中不包含的那个列，最后查处最终数据，这就是回表。

如果排序使用了索引，而select列未使用索引列，则该索引失效（主键索引除外）
这个其实上面的type为index中已经介绍了，如果排序使用了索引，且select没有选择该索引字段，就代表着需要回表查询，mysql优化器就会认为扫描数据表比使用索引效率更高。而如果select选中的了索引字段的话，则不需要回表查询了，自然选择使用索引。
在查询语句中where也使用了索引，order by 也使用了索引。这时where 中的索引生效，order by中的索引失效。
这个很好理解了，我们上面说了，sql的索引选择就是sql的执行顺序，where的执行顺序优先于order by,所以索引也就优先选择where的索引喽。
在重复性很高的列上加索引未必失效
表结构如下

users | CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sex` tinyint(255) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `sex` (`sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

我们对sex分组,发现sex列的重复数据很多，选择性不高。

mysql> select sex,count(*) from users group by sex;
+-----+----------+
| sex | count(*) |
+-----+----------+
|   1 |       18 |
|   2 |        1 |
+-----+----------+

我们执行下面的sql，发现即使使用了重复性很高的索引列，但是如果使用覆盖索引的话，仍然是可以使用该索引的。

mysql> explain select id,sex from users where sex=1;
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref  | sex           | sex | 1       | const |   18 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)
 
mysql> explain select name from users where sex=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | sex           | NULL | NULL    | NULL |   19 |    94.74 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

即使该列的重复性数据为100%，优化器也是会认为使用索引效率更高。

我们之前不是说，不推荐在重复性很高的列上加索引嘛，现在又说可以加，是不是很矛盾呢？其实不是的，只是mysql优化器选择执行效率更高的方式罢了。我们看到sex=1的数据将近占了数据的99%，第一条sql还是使用了sex索引，且rows是18，很精确，而第二条sql却没有使用索引，选择了全表扫描。

分析下什么原因，什么情况下，优化器会认为执行索引效率更高，什么情况下执行数据表效率更高

我们就对上面的俩条sql进行分析。第一条sql如果使用sex索引的话，可以在索引文件中根据sex=1的条件直接获得id,sex的值，且只有18条数据（f覆盖索引）。如果不使用sex索引的话，需要在数据文件中全表查询，在19条数据中筛选sex为1的数据，筛选出来的数据是含有所有字段的，最后在筛选的数据中获得id,sex字段的数据，并返回给用户。俩种方式很明显，使用索引的方式更简单。

在对第二条sql进行分析，如果使用sex索引，首先在索引文件中根据sex=1的条件获得18条数据的id,然后根据这18行数据的id再回表查询，在数据文件中根据id继而查询到18条数据，该18条数据是包含所有字段的，然后这些数据中筛选出name字段，最后返回给用户，这个过程mysql相当于查询了36条数据。如果不使用sex索引，而直接全表查询，过程为：直接查询全表19条数据，筛选出18条数据，该18条数据是包含所有字段的，然后这些数据中筛选出name字段，最后返回给用户。俩种方式很明显直接查表效率更高。

其实可以看出，决定效率高不高的因素就是是否覆盖索引。只有当sql回表查询时mysql优化器才会决定是选择索引效率更高还是直接全表查询效率更高。如果没有回表使用了覆盖索引，mysql优化器肯定会认为使用索引效率高于回表查询效率。

那我们第二条sql只能全表查了吗？现在是19条数据，如果是9w,90w条数据呢，全表查询岂不废了？肯定是有优化方案的，我们称这种方案为延迟关联,sql如下

select * from users inner join 
(select id from users where sex=1)as a 
on a.id=users.id

我们可以看到使用内连接的方式，我们可以将上面的sql拆解为俩条sql

select id from users where sex=1
//使用上面sql获取的id查询第二条sql
select * from id=

我们已经知道了第一条sql使用了sex索引的覆盖索引，直接获取sex=1的18条数据。而第二条sql使用的是主键索引，执行查询计划,可以看到我们的猜想完全正确。

mysql> explain select * from users inner join (select id from users where sex=1)as a on a.id=users.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ref    | PRIMARY,sex   | sex     | 1       | const            |   18 |   100.00 | Using index |
|  1 | SIMPLE      | users | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | laravel.users.id |    1 |   100.00 | NULL        |

可能看到这里有的小伙伴会说，直接创建一个sex和name的复合索引就可以了，何必这么麻烦。是的，创建一个复合索引确实是可以的，我们这里主要是介绍延迟关联思想的,接下来继续介绍一个很常见的延迟关联的例子

分页查询优化（延迟关联）
一般分页查询为

select name from users where sex=1 limit n,m

数据量少的时候效果还行，当n偏移量很大的时候，尽管我们在sex上加上了索引，该方式查询会很慢。此时我们可以使用 inner join来实现，以下这种情况是最理想的sql,也是延迟关联的一种形式。

select * from users inner join (select id from sex=1 limit n,m) as a 
on a.id=users.id

注意：这里要使用内连接，不能使用左连接，否则查到的不是m条数据，而是整个users表的数据。其实延迟关联是解决这个问题的最好方式。具体可以查看我的这篇文章

select name from users where name='xiaoyu' and sex=1
有索引列为name的索引，分析sql执行顺序。
先分析走name索引的情况：在name的索引数据中，查找name='xiaoyu'的数据行，获得行id，然后根据这些id回表（sex字段不在name索引中，所有需要回表）查询。在数据文件中根据索引文件中获得的id查询到对应的行数据，然后在这些行数据的基础上筛选sex=1的行数据，最后将筛选后的数据返回给用户。
不走name索引的情况：直接在数据文件中全表搜索name='xiaoyu' and sex=1的数据行，将最终的数据行返回用户。

mysql优化器选择效率更高的方式执行该sql，决定该sql是使用索引还是全表查询。mysql具体怎么选择在上面有介绍。

select email from uers where name='xiaoyu'
有索引列为name的索引，分析sql执行顺序。
这条sql和上面那个sql很像，因为select的字段email不属于name索引，所有在使用name索引的情况下也得回表。

mysql优化器会决定是使用索引效率更高，还是直接全表查询效率更高，然后选择效率高的那种方式执行该sql。

在有where和order的情况下，怎么设置索引
例如如下sql

select * from users where sex=1 order by old asc

因为select是*,肯定会回表查询，我们前提是排除回表查询效率大于使用索引的效率，也就是这条sql肯定使用索引，我们该怎么设置合理呢？

我们知道索引的选择顺序就是sql的执行顺序，所以设置一个含有sex,old列的复合索引是最好的。

在有where,order和group的情况下，怎么设置索引
例如如下sql

select * from users where sex=1 group by class order by old asc

因为select是*,肯定会回表查询，我们前提是排除回表查询效率大于使用索引的效率，也就是这条sql肯定使用索引，我们该怎么设置合理呢？

我们知道索引的选择顺序就是sql的执行顺序，所以设置一个含有sex,class和old列的复合索引是最好的。

有连表的情况下，怎么给俩张表设置索引
例如如下sql

select users.name  from users left join class on users.classId=class.id 
where class.name='3班'

连表情况下分析怎么加索引，只要sql拆开就一目了然了，上述sql可以拆解为俩条sql

select  id from class where class.name='3班'
//根据上面查询到的id查询以下sql
select users.name from users where users.classId=class.id

拆分成这俩sql后，我们就很容易看出怎么加索引了啊。在class表上加一个含有name字段的索引，在users表上加一个含有classId和name字段的复合索引。

参考文档

深入理解mysql索引原理和实现
 mysql的单列索引和组合索引
 mysql-覆盖索引
 mysql的Explain关键字查看
 innoDB的主键采用聚簇索引，二级索引不采用聚簇索引

最后编辑于：2021.01.28 10:37:15

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 160,165评论 4赞 364
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,720评论 1赞 298
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 109,849评论 0赞 244
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,245评论 0赞 213
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,596评论 3赞 288
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,747评论 1赞 222
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,977评论 2赞 315
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,708评论 0赞 204
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,448评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,657评论 2赞 249
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,141评论 1赞 261
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,493评论 3赞 258
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,153评论 3赞 238
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,108评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,890评论 0赞 198
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,799评论 2赞 277
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,685评论 2赞 272