索引对于MySQL是非常重要的，他决定了MySQl检索数据的性能。本文从使用、优化、原理论证三个维度浅聊MySQL索引。
MySQL索引使用介绍
一、索引类型
| MySQL索引类型 | 索引 | 查询DEMO | 使用场景 | |
|---|---|---|---|---|
| primary | 主键索引 | 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键及主键索引 | 常规查询SQL | 常用 |
| normal | 普通索引 | 最基本的索引，它没有任何限制 | 常用 | |
| unique | 唯一索引 | 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一 | 常用 | |
| spatial | 组合索引 | 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。组合索引遵循最左前缀原则 | 最左原则： SELECT * FROM mytable WHREE username="admin" AND city="郑州" and age =18 如果username是最左字段这个组合索引查询必须带上username不然不走索引 | 不常用 |
| full text | 全文索引 | fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用 | 特殊SQL： SELECT * FROM test WHERE MATCH(name) AGAINST('小明') | 不常用 |
二、执行计划分析
我们在写一个SQL我们如何判断自己写的SQL是性能最优的？这里我们就需要查看我们SQL的执行计划了，在执行计划中我们可以查询到SQL执行情况。下面我们还通过列表的形式展示执行计划特征。查看执行计划格式：explain/desc + SQL
dingzhichaodeMacBook-Pro:vem-operator dzc$ /usr/local/mysql/bin/mysql -h 10.213.3.241 -P6446 -u test_user -ptest_user@123456
mysql> use vem;
Database changed
mysql>
mysql> desc select * from vem_order vo left join vem_order_detail vod on vo.id = vod.ORDER_ID where vo.id in(1,2);
+----+-------------+-------+------------+------+---------------+--------------+---------+-----------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-----------+----------+----------+-------------+
| 1 | SIMPLE | vo | NULL | ALL | uk_vem_order | NULL | NULL | NULL | 32011575 | 20.00 | Using where |
| 1 | SIMPLE | vod | NULL | ref | IDX_ORDER_ID | IDX_ORDER_ID | 98 | vem.vo.ID | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+----| 指标名称 | 指标作用 | 指标解释 | |
|---|---|---|---|
| id | 执行序号 | id越大越优先执行，id相同顺序执行 | |
| select_type | 查询的类型 | 1、SIMPLE：简单的select查询，查询中不包含子查询或者union 2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary 3、SUBQUERY：在select 或 where列表中包含了子查询 4、DERIVED：在from列表中包含的子查询被标记为derived（衍生），mysql或递归执行这些子查询，把结果放在零时表里 5、UNION：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived 6、UNION RESULT：从union表获取结果的select | |
| table | 查询表名或者表别名 | SQL中查询的表名称或者表的别名名称 | |
| partitions | 分区信息 | SQL查询表数据在那个分区中 | |
| type | 索引类型 | 索引类型性能由好到坏顺序： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 索引各类指标解释： 1、system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现可以忽略不计 2、const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引。因为只需匹配一行数据，所有很快。如果将主键置于where列表中，mysql就能将该查询转换为一个const 3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。 4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体 5、range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引 6、index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常为ALL块，应为索引文件通常比数据文件小。（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取） 7、all：Full Table Scan遍历全表以找到匹配的行 | |
| possible_keys | SQL可能使用到的索引 | SQL查询使用到的索引，但不一定被查询实际使用 | |
| key | SQL实际到的索引 | 实际使用的索引，如果为NULL，则没有使用索引 | |
| key_len | 索引使用字节数 | SQL查询时候用的所有KEY占用的字节大小 比如：int列 mysql用4字节 | |
| ref | 索引哪一列被使用 | SQL查询索引使用了哪一列 | |
| rows | 检索行数 | SQL查询检索行数越少越好 | |
| filtered | 返回行数占读行数白分比 | Filtered表示返回结果的行数占需读取行数的百分比，该值越大越好 | |
| Extra | 非常重要的额外说明 | 1、Using filesort（需优化）mysql中无法利用索引完成的排序操作称为“文件排序” 比如：desc SELECT * FROM vem_order t left join vem_order_detail tt on t.id = tt.ORDER_ID order by t.id desc; 2、Using temporary（需优化）使用了临时表保存中间结果。常见于排序order by和分组查询group by。比如：desc select * from( SELECT t.* FROM vem_order t left join vem_order_detail tt on t.id = tt.ORDER_ID order by t.id desc) a order by a.id desc; 3、Using index 表示相应的select操作中使用了覆盖索引(covering index)，避免访问了表的数据行效率不错 4、Using where 表明使用了where过滤 5、Using join buffer 使用了连接缓存 6、impossible where where子句的值总是false，不能用来获取任何元组 7、select tables optimized away 在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MYSQL存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化 8、distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样的值的动作 |
三、表信息查询
我们在做索引优化的时候，需要了解表的一些信息，我们需了解表、索引的开销在综合层面做个平衡，下面介绍我们如何查询表的相关信息。
dingzhichaodeMacBook-Pro:vem-operator dzc$ /usr/local/mysql/bin/mysql -h 10.213.3.241 -P6446 -u test_user -ptest_user@123456
mysql> select * from `information_schema`.`TABLES` where `TABLE_NAME` = 'vem_machine';
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+-------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------------+
| def | vem | vem_machine | BASE TABLE | InnoDB | 10 | Dynamic | 105060 | 185 | 19447808 | 0 | 37404672 | 6291456 | NULL | 2021-03-03 17:56:24 | 2021-03-09 16:26:31 | NULL | utf8_general_ci | NULL | | 自贩机列表 |
| 表指标名称及含义 | |
|---|---|
| TABLE_CATALOG | 表登记目录 |
| TABLE_SCHEMA | 表所属的数据库名 |
| TABLE_NAME | 表名称 |
| TABLE_TYPE | 表类型[system view|base table] |
| ENGINE | 使用的数据库引擎[MyISAM|CSV|InnoDB] |
| VERSION | 版本 默认值10 |
| ROW_FORMAT | 行格式[Compact|Dynamic|Fixed] |
| TABLE_ROWS | 表里所存多少行数据 |
| AVG_ROW_LENGTH | 平均行长度 [单位字节] |
| DATA_LENGTH | 表数据长度 [单位字节] |
| MAX_DATA_LENGTH | 最大数据长度 [单位字节] |
| INDEX_LENGTH | 索引长度 [单位字节] |
| DATA_FREE | 空间碎片 |
| AUTO_INCREMENT | 做自增主键的自动增量当前值 |
| CREATE_TIME | 表的创建时间 |
| UPDATE_TIME | 表的更新时间 |
| TABLE_COLLATION | 表的字符校验编码集 |
| TABLE_COMMENT | 表的注释、备注 |
四、索引信息查询
我们在创建索引的时候必须要了解我们创建了什么索引，创建这个索引是否对我们程序性能有多大提升。
mysql> show index from vem_order;
+-----------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| vem_order | 0 | PRIMARY | 1 | primary_id | A | 32011550 | NULL | NULL | | BTREE | | |
| vem_order | 0 | uk_vem_order | 1 | ID | A | 32011576 | NULL | NULL | | BTREE | | |
| vem_order | 1 | IDX_MACHINEID_PAYCATEGORYNAME | 1 | MACHINE_ID | A | 28255 | NULL | NULL | | BTREE | | |
| vem_order | 1 | IDX_MACHINEID_PAYCATEGORYNAME | 2 | PAY_CATEGORY_NAME | A | 31395 | NULL | NULL | YES | BTREE | | || 索引指标名称及含义 | |
|---|---|
| Table | 索引所在表名称 |
| Non_unique | 非唯一性索引0， 唯一性索引则为1 |
| Key_name | 索引名称 |
| Seq_in_index | 索引中序列好，从1开始 |
| Column_name | 列名称 |
| Collation | 列以什么方式存储在索引中。可以是A或者NULL，B+树索引总是A可以排序，如果是Hash索引值为NUll不能排序 |
| Cardinality | 1、列值代表的是此列中存储的唯一值的个数（如果此列为primary key 则值为记录的行数） 总结：Cardinality如果在复合索引表示唯一复合索引的个数，一般指唯一索引的个数。 如果此值大代表通过索引筛选掉的数据越高效，索引的执行效果越好，如果此列值很小就没有必要创建索引了，比如常见的男女查询。 |
| Sub_part | 列是否部分被索引，如果显示100表示列被索引100个字符。如果整列被编入索引，则为NULL |
| Packed | 关键字如何被压缩。如果没有被压缩，则为NULL |
| Null | 如果列含有NULL则YES，如果没有Null则该列含有NO |
| Index_type | 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE |
| Comment | 注释 |
五、MySql聚集索引、辅助索引、覆盖索引、联合索引的使用
5.1、聚集索引
聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。下面举个例子，直观感受下聚集索引。典型的就是表的主键，他就是聚集索引，其他的是辅助索引。
聚集索引的叶子节点称为数据页，每个数据页通过一个双向链表来进行链接，而且数据页按照主键的顺序进行排列。如图所示，每个数据页上存放的是完整的行记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。如果定义了主键，InnoDB会自动使用主键来创建聚集索引。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替主键。如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。 构建索引的数据结构是B+tree
5.2、辅助索引
辅助索引，也叫非聚集索引。和聚集索引相比，叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签（bookmark），该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。构建索引的数据结构是Btree。 如果要查询辅助索引中不含有的字段，得先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少IO操作。比如上图中，以下sql可以直接使用辅助索引。使用辅助索引降低IO次数。
#先找到c=-2的辅助索引对应的聚族索引，然后再查询
select a from where c = -2;5.3、覆盖索引
InnoDB存储引擎支持覆盖索引，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引有啥好处？
#表结构
CREATE TABLE `student` (
`id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
`age` varchar(255) NOT NULL,
`school` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_school_age` (`school`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#查询语句
select count(*) from student
查看执行计划如下图，其查询学生数，只走了索引 idx_name，未查询表。这就是覆盖索引的用法。
5.4、联合索引
联合索引如果索引生效必须带上主索引（聚集索引），而联合索引（辅助索引）的部分就是在主索引数据筛选的基础上再次筛选。比如下图a,b,c构成test表的联合索引 索引覆盖：a,b,c、a,c、a,b 索引不覆盖：b,c、c,b。 为什么这样？ B+Tree也是一种树结构的数据结构，是树结构其遍历方式分别为 先序遍历、中序遍历、后续遍历三种遍历方式。其中先序遍历的原则是根左右，这种遍历的方式树查询效率最高时间复杂度(m底数：阶数目，N待查询数总数)，故MySql采用先序遍历的方式遍历。所以也就有了MySql的最左原则。
六、MySQL索引下推
引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。mysql索引内部的一种优化。比如：SELECT * from user where name like '陈%' and age=20；mysql查询age=20的所有数据，然后在回表查询一次，而不是把name like ‘陈’ 和 age=20的查询出来，然后查询， 降低回表次数。原理：充分利用索引过滤目标数据，然后回表查询需要结果
七、MySQL索引原理介绍
MySQL索引原理介绍参考我以前写的文章《B+tree与MySql》，这里就不在赘述了。
八、MySql索引失效场景
1、在索引列上做运算，比如使用函数，Mysql在生成执行计划的时候，它是根据统计信息来判断是否要使用索引的。而在索引列上加函数运算，导致Mysql无法识别索引列，也就不会再走索引了。不过从Mysql8开始，增加了函数索引可以解决这个问题。
2、不遵守最左原则，在一个由多列构成的组合索引中，需要按照最左匹配法则，也就是从索引的最左列开始顺序检索，否则不会走索引。在组合索引中，索引的存储结构是按照索引列的顺序来存储的，因此在sql中也需要按照这个顺序才能进行逐一匹配。否则InnoDB无法识别索引导致索引失效。
3、字段类型存在隐式转化，当索引列存在隐式转化的时候， 比如索引列是字符串类型，但是在sql查询中没有使用引号。那么Mysql会自动进行类型转化，从而导致索引失效
4、在索引列使用不等于号、not查询的时候，由于索引数据的检索效率非常低，因此Mysql引擎会判断不走索引。
5、like通配符后缀%xxx匹配，使用like通配符匹配后缀%xxx的时候，由于这种方式不符合索引的最左匹配原则，所以也不会走索引。但是反过来，如果通配符匹配的是前缀xxx%，符合最左匹配，也会走索引。
6、使用or连接查询的时候，or语句前后没有同时使用索引，那么索引会失效。只有or左右查询字段都是索引列的时候，才会生效。
九、面试常见问题列举
1、主键查询是否比普通索引快？
答案：是的。 应为主键是聚集索引，其B+tree构建的叶子节点有直接查询的数据。 普通索引是非聚集索引，其叶子节点没有被查询的数据。只有数据的指针，故最后查询还需要查询聚集索引。
2、Mysql索引阶（树高度）如何推导？
答案：在 MySQL 中，B+ 树是一种常用的索引结构，用于加快查询性能。B+ 树的高度影响着索引的查询效率，因此对于大规模数据集，需要设计合适的索引结构，以减少索引的高度。
B+ 树的高度可以根据 B+ 树的阶数和数据总量来推导。假设 B+ 树的阶数为 M，数据总量为 N，那么可以推导出 B+ 树的高度公式：H >= logM(N) + 1
3、Mysql InnoDB 存储引擎索引最大支持多少数据 如何推导？
答案：InnoDB 存储引擎使用 B-tree 数据结构来实现索引，每个 B-tree 节点的大小受到 InnoDB 的页大小限制。默认情况下，InnoDB 的页大小为 16KB。（8K、16K、32K）因此，一个 B-tree 节点的大小为 16KB。
1、页大小限制 当一个索引的长度超过一个 B-tree 节点的大小时，InnoDB 会把该索引拆分为多个节点存储。这就意味着，InnoDB 存储引擎的最大索引长度受到页大小的限制。
2、索引键长限制 InnoDB 存储引擎的索引长度也受到最大索引键长度的限制。默认情况下，InnoDB 的最大索引键长度为 767 字节。如果索引列的长度超过了 767 字节，InnoDB 会把该索引拆分为多个节点存储。
3、Mysql常见字段类型大小
-
整型数据类型（INT、TINYINT、SMALLINT、MEDIUMINT、BIGINT）：这些数据类型用于存储整数值，占用的空间大小分别为 4 字节、1 字节、2 字节、3 字节和 8 字节。
-
浮点型数据类型（FLOAT、DOUBLE）：这些数据类型用于存储浮点数，占用的空间大小分别为 4 字节和 8 字节。
-
定点型数据类型（DECIMAL、NUMERIC）：这些数据类型用于存储固定精度的数值，占用的空间大小取决于精度和规模。
-
日期时间型数据类型（DATE、TIME、DATETIME、TIMESTAMP）：这些数据类型用于存储日期时间信息，占用的空间大小分别为 3 字节、3 字节、8 字节和 4 字节。
-
字符串型数据类型（CHAR、VARCHAR、TEXT）：这些数据类型用于存储文本信息，占用的空间大小取决于字符串的长度和字符集。其中，CHAR 和 VARCHAR 存储固定长度和可变长度的字符串，占用的空间大小分别为 N 字节和 L+1 字节（L 表示字符串长度），而 TEXT 存储大量文本信息，占用的空间大小最多可达 4GB。
-
二进制型数据类型（BINARY、VARBINARY、BLOB）：这些数据类型用于存储二进制数据，占用的空间大小取决于数据的长度。其中，BINARY 和 VARBINARY 存储固定长度和可变长度的二进制数据，占用的空间大小分别为 N 字节和 L+1 字节（L 表示二进制数据长度），而 BLOB 存储大量二进制数据，占用的空间大小最多可达 4GB。
场景一：已知条件，Mysql页大小16K、字段为BIGINT（8字节）、B+树阶一般为3
=> 第一阶推导： 16*1024/8 = 2048
=> 第二阶推导： 2048*2048 = 4194304
=> 第三阶推导： 2048*2048*2048 = 8589935592
场景一总结：Mysql页大小16K、字段为BIGINT（8字节）、B+树阶为3， Mysql最大支持索引容量为 85亿。
591
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
