本文详细介绍了MySQL索引的概念、作用、分类以及操作，包括主键索引、普通索引、唯一索引、全文索引和组合索引的创建与删除。同时，分析了索引内部结构，如B+Tree，以及MyISAM和InnoDB的索引差异。通过对SQL查询的分析，展示了如何使用EXPLAIN命令优化查询性能，讨论了函数、IS NULL、LIKE、IN、OR、ORDER BY和GROUP BY等操作对索引使用的影响。
一、索引概念
官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。通俗来讲，索引类似文章的目录，用来提高查询的效率。
二、索引作用
- 设置合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
- 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
- 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。
- 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
- 可以加快表与表之间的连接。
- 在使用分组和排序时，可大大减少分组和排序的时间。
建立索引在搜索和恢复数据库中的数据时能显著提高性能。但是索引需要占用额外的磁盘空间。对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而 InnoDB 引擎的表数据文件本身就是索引文件。更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新，导致性能会相应的有所下降。
三、索引分类
- 从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、。
- 从应用层次划分：普通索引、唯一索引、主键索引、复合索引。
- 从索引键值类型划分：主键索引、辅助索引（二级索引）。
- 从数据存储和索引键值逻辑关系划分：聚簇索引、非聚簇索引。
四、MySQL中索引操作
当一张表，把某个列设为主键的时候，则该列就是主键索引。
create table table_demo (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(20) not null default '' COMMENT '姓名',
`age` tinyint(4) NOT NULL default 0 COMMENT '年龄',
`card_no` varchar(30) NOT NULL COMMENT '身份证号',
`base_info`text default NULL COMMENT '基础信息',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='测试demo';
①主键索引
这里id就是表的主键，如果当创建表时没有指定主键索引，也可以在创建表之后添加：
alter table table_demo add primary key (card_no);
②普通索引
以name为例创建普通索引
CREATE INDEX table_demo_name_IDX USING BTREE ON table_demo (name);
或者
alter table table_demo add index table_demo_name_IDX (name);
③全文索引
全文索引主要针对文本文件，比如文章，标题。在MySQL5.6之前，只有MyISAM存储引擎支持全文索引，MySQL5.6之后InnoDB存储引擎也支持全文索引，以base_info为例创建普通索引。
CREATE FULLTEXT INDEX table_demo_base_info_IDX ON table_demo (base_info);
或者
alter table table_demo add FULLTEXT index table_demo_base_info_IDX (base_info);
④唯一索引
索引列中的值必须是唯一的，但是允许为空值，相比主键索引，主键字段不能为null，也不能重复， 以card_no为例创建普通索引 。
CREATE UNIQUE INDEX table_demo_card_no_IDX USING BTREE ON table_demo (card_no);
或者
alter table table_demo add UNIQUE index table_demo_card_no_IDX (card_no);
⑤组合索引
用多个列组合构建的索引，以name,age为例创建普通索引。
CREATE INDEX table_demo_name_age_IDX USING BTREE ON table_demo (name,age);
或者
alter table table_demo add index table_demo_name_age_IDX (name,age);
⑥删除索引
ALTER TABLE table_demo DROP INDEX 索引名称;
⑦修改索引
没有直接修改命令，只能删除重建。
五、索引内部结构
索引实际上是数据库中满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法 。
①索引的特性
- MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。
- 索引值和data数据分布在整棵树结构中。
- 树节点中的多个索引值从左到右升序排列。
- 从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。
- 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值。
- 叶子节点包含了所有的索引值和data数据。
- 叶子节点用指针连接，提高区间的访问性能。
- 相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。
②名词介绍
- 回表查询
InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询。
- 覆盖索引
在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
- 左前缀原则
复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。
- 索引下推
在MySQL 5.6之前，没有索引下推，存储引擎根据联合索引找到name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。
MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，由于联合索引中包含列，所以存储引擎直接再联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。
③B+树索引存储结构
B+树索引本身并不能找到具体的一条记录，能找到的只是该记录所在的页。数据库把页载入内存后，再通过Page Directory再进行二分查找。只不过二分查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。
Page Directory与记录的关系如下图：
页目录创建的过程如下：
- 将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录。
- 每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）。
- 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。
从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。因为记录是按照「主键值」从小到大排序的，所以我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录，无需从最小记录开始遍历整个页中的记录链表。
索引如何选择
如何一个表拥有多个索引，那么数据库最终会选择哪个索引了？这里就涉及到索引选择问题，一般数据库会计算不同根据不同索引的查询开销，那一个开销最小，那么就选择这个索引。
那么查询开销如何计算了？开销一般包括：IO开销，CPU开销，以及网络开销。其中网络开销我们一般忽略不计，那么主要考虑IO开销以及CPU开销，这里IO开销一般占大头。所以数据库会重点参考索引扫描行数。如果扫描行数越多，那么代价越大。
这里行数怎么得出了，难道是每次都去实时查吗？并不是的，数据库可以通过抽样获取，比如随机抽取几页，然后统一一下分布，然后根据分布乘以总页数，那么就得到了数据的一个大致的基数，这样就可以通过这个基数来判断IO开销了。
那么，数据库是否会存在选错索引的情况了？是存在的，这里主要有两种情况：1）表增删十分频繁，导致扫描行数预估的统计信息不准确，可能会选择错误的索引。解决该类问题的方法是强制触发统计信息的更新，即analyze table。这个操作只是触发重新采样更新统计信息，因此用户不用担心这个操作会影响DML操作；2）有时候扫描的行太多，再加上回表等操作，优化器认为，还不如不走这个索引，此时也会出现不符合预期的情况。
对于没有使用预期的索引，我们应该怎么做了？可以使用force index强制使用索引。其外，如果有按扰且无用的索引存在，那么可以删除这个干扰的索引。
索引页的结构
首先看下InnoDB的逻辑存储结构，如下图所示：
TableSpace可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。InnoDB存储引擎有一个共享的空间ibdata1，即所有的数据都存放在这个空间内，如果用户启用了参数innodb_file_per_table，则每张表一个单独的表空间。表空间由各种Segement组成，常见Segment比如数据段、索引段、回滚段等。
Segement由一个个Extent组成。Extent是由连续的Page组成的空间。Page是InnoDB磁盘管理的最小单位。
在File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表，如下图所示：
④MyISAM索引结构
⑤InnoDB索引结构
六、索引分析
MySQL提供了一个EXPLAIN 命令，它可以对SELECT语句进行分析，并输出SELECT执行的详细信息，供开发人员有针对性的优化。
(注：下面的sql分析都是基于mysql 8.0.36版本，结果可能会与mysql5.7版本有差异)
EXPLAIN 命令的输出内容大致如下:
explain select * from test1 where name like '%三%';
| 字段名称 | 字段描述 | 常用值说明 |
| select_type | 查询类型 | SIMPLE ： 表示不需要union操作或者不包含子查询的简单select查询。 有连接查询时，外层的查询为simple，且只有一个。  PRIMARY：一个需要union操作或者含有子查询的select，位于最外层的单位查询的 select_type即为primary，且只有一个。 SUBQUERY：除了from子句中包含的子查询外，其他地方出现的子查询都可能是 Subquery。  DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果。 Derived ：from字句中出现的子查询，也叫做派生表  UNION：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union。  DEPENDENT UNION：与union一样，出现在union 或union all语句中， 但是这个查询要受到外部查询的影响。 UNION RESULT：包含union的结果集，在union和union all语句中, 因为它不需要参与查询，所以id字段为null。 |
| table | 查询涉及的表名 | 如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作， 那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表， 后边的N就是执行计划中的id，表示结果来自于这个查询产生。 如果是尖括号括起来的<union M,N>，与类似，也是一个临时表， 表示这个结果来自于union查询的id为M,N的结果集。 |
| partitions | 查询涉及的分区数量 | 如果查询语句使用了分区表，并且查询条件中包含了分区键，那么 partitions 列的值就会显示为使用的分区数量;如果查询语句没有使用分区表， 或者查询条件中不包含分区键，那么 partitions 列的值就会显示为 NULL。 需要注意的是，partitions 列的值并不一定等于分区表的分区数， 因为查询条件可能会限制查询的分区范围。 |
| type | 查询数据时采用的方式 | 依次从最优到最差分别为： NULL>system>const>eq_ref>ref>ref_or_null> index_merge>range>index>ALL
ALL：表示全表扫描，性能最差。  index：表示基于索引的全表扫描，先扫描索引再扫描全表数据。  range：表示使用索引范围查询。使用>、>=、<、<=、in等等。  ref：表示使用非唯一索引进行单值查询。  eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一行结果。  const：表示使用主键或唯一索引做等值查询，常量查询。  System: 表只有一行记录（等于系统表），这是const类型的特列  NULL：表示不用访问表，直接从索引中获取，速度最快。  |
| possible_keys | 索引名列表 | 查询时能够使用到的索引名列表 ,注意并不一定会真正使用，显示的是索引名称。 |
| key | 索引名称 | 表示查询时真正使用到的索引名称. |
| Key_len | 查询时使用索引的字节数量。 | 用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。 |
| ref | 查找值所用到的列或常量
| 如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func const，func，null，字段名
|
| rows | mysql估计要读取或检测的行数
| 查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录,原则上rows是越少效率越高，可以直观的了解到SQL效率高低 |
| filtered | 结果行占读取 行百分比 | 表示结果的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。 |
| Extra | 额外信息 | Using where 表示查询需要通过索引回表查询数据。 Using index 表示查询需要通过索引，索引就可以满足所需数据。 Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。 Using temprorary 查询使用到了临时表，一般出现于去重、分组等操作。 |
七、常见sql分析
以下面表结构为例进行分析
CREATE TABLE `test4` (
`a` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
`b` bigint NOT NULL DEFAULT '0' COMMENT 'b',
`c` bigint DEFAULT '0' COMMENT 'c',
`d` bigint NOT NULL DEFAULT '0' COMMENT 'd',
`e` varchar(20) DEFAULT '0' COMMENT 'e',
`f` varchar(10) DEFAULT NULL COMMENT 'f',
`g` bigint NOT NULL COMMENT 'g',
PRIMARY KEY (`a`),
KEY `test_e_IDX` (`e`) USING BTREE,
KEY `test_b_c_d_IDX` (`b`,`c`,`d`) USING BTREE,
KEY `test_g_IDX` (`g`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='测试4';
①函数
列使用函数、对列进行运算、隐式转换，该列的索引将不起作用。
补充：
explain select * from test4 where a = '5';
explain select * from test4 where e = 23;
对于这两条sql,为啥第一条sql可以走索引，第二条sql不能走呢？原因对于字符串与数字比对时，mysql会统一把字符数据转为数字，然后进行比对。如果字符是数字（‘5’）直接转为相对应的数字，如果是字符（‘a’）统一转为0，所以第一条sql会把等号右边的条件转为数字，然后走a字段的索引，第二条sql会把等号左边的条件转为数字，那就是mysql中e字段，这个工作量就比较大，因此mysql分析出不走索引，性能会更好。
②is null、is not null
如果字段定义为NOT NULL,那么使用is not null过滤就是全表扫描，如果字段定义为 Default NULL 或可以为NULL，那么is nul或is not null 都可以走索引（对于在索引树中对于为null的数据会单独进行存储）。
③like、not like
如果like或not like左模糊匹配，如果查询字段是索引字段，mysql优化器会计算出走索引，还是全表扫描效率更高。因为在非主键索引叶子节点的data是主键索引的id，而主键索引的叶子节点data是实际数据（InnoDB中索引和数据是同一份数据），这样对于非主键索引每个叶子节点的记录行数更多，遍历的页数也少，效率更高，因此最左匹配原则有些时候不使用最左边字段也可以走索引。实操结果如下：
④范围查询
对于检索的数量比较大时，版本有差异，Mysql5.7 和Mysql8.0 不一样。
对于Mysql5.7 ，考虑需要重新回表，直接采用全表扫描。
而Mysql8.0使用索引。
⑤in、not in
如果in 或者not in字段为主键索引字段，那就会走索引。如果为非主键索引字段，in会走索引，not in并不一定会走索引。原因非主键索引查询not in性能消耗比较多，并且可能需要回表，若需要回表，就全表扫描。
⑥or
如果b字段有索引，e字段也有索引，那么会走索引，此时就是索引合并Index Merge。
如果只有一个字段有索引，或者两个字段都没有索引，那么不会走索引。
⑦ORDER BY
从执行结果可以看出，b、c、d联合索引和主键索引mysql优化器会做出选择，如果走联合索引还需要进行回表操作，直接会全表扫描，如果走联合索引在这个索引树就可以覆盖到所查询的字段，那就会走联合索引。
⑧group by
如果在GROUP BY中提及的列必须包含在索引中，不符合最左原则的，必须覆盖索引才能走索引。
扫一扫
1352
到【灌水乐园】发言
