用了四五年的MySQL，总结一些对其索引的理解。

1.索引是什么？

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。那么在数据量适合的时候，如何调优，索引是必先处理好的部分。目前我接手过的系统中，用户级为几百万，产生的相关数据逼近千万级，在依赖查询统计的方面，索引如果建立和使用，显得尤为重要。

很多人会将数据库比作书本，索引比作目录。索引命中数据的过程，就是按图索骥，这个比喻恰到好处。MySQL常见的索引数据结构有BTREE(B+TREE，目前使用的InnoDB存储引擎都是基于B+TREE实现的)、HASH。

InnoDB索引分为两大类，聚集索引和普通索引。聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：

• 如果表定义了PK，则PK就是聚集索引；

• 如果表没有定义PK，则第一个not NULL unique列是聚集索引；

• 否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

普通索引的叶子节点存储主键值。而所谓的回表查询，指的就是当命中普通索引后，所需要的字段无法直接通过索引获取，那么数据库引擎就会通过普通索引的主键值去扫描聚集索引树，进行记录定位，效率很低，所以在查询时，应当尽量避免产生回表扫描问题。

2.使用索引的一些经验

2.1.左原则构建索引

左原则的匹配规则，必须按照从左到右的顺序匹配，即你的表条件SQL，对应的字段，必须按照索引的字段顺序排列。比如以下查询：

SELECT * FROM `admin_user` WHERE `nickname`='admin' AND `status`='A';

创建以下索引

CREATE INDEX idx_name_status ON `admin_user`(`nickname`,`status`);

则执行查询时，索引会命中使用。但是如果不按照顺序的查询条件，比如：

SELECT * FROM `admin_user` WHERE `status`='A';

上面所建立的索引则不会被命中，因为此索引需要先扫描第一个字段nickname才能继续往下所描。

此外，SQL的条件顺序并不会影响索引的命中，因为SQL的优化器会自动对SQL的条件进行适当的排序处理！

2.2.统一字符校对编码避免索引失效

在一次MySQL5.7升级8.0的过程中，出现了一个索引始终无法命中的问题。折腾好几天后才发现，是因为迁移数据的时候，没注意字符校对编码不一致，导致了一个表中各个字段的字符校对编码混乱，导致索引集体失效。后调整所有的字符校对编码为一致成功解决此问题。

2.3.部分引擎放弃使用索引的情况下酌情使用FORCE INDEX

数据库引擎在优化器器构建查询计划时，当从各索引所扫描的数据超过一定百分比时（具体多少没有深究）阈值，会放弃使用索引，改为全表扫描。这时，如果你依然觉得使用使用索引更快的话，可以使用FORCE INDEX方式强制使用某个索引。

2.4.适当调整SQL语句使得索引被命中

当进行联合查询时，部分逻辑结构可以适当的拆解SQL，直到命中索引为止。

2.5.联表环境下COUNT等函数的问题

目前分页的COUNT问题一直是一个问题，尽量使用聚集索引来进行COUNT统计，减少回表扫描。

2.6.一些MySQL的其他细节问题

• 坚持使用自增主键或雪花ID，使用UUID也最要建立一个自增主键，好处是顺序置入索引，能解决MySQL排序混乱问题；

• 货币字段使用Decimal；

• 时间字段timestamp、datetime、bigint各有优势，依据项目场景采用；

• 尽量不使用default null，采用给予默认值处理；

• 索引不宜建立太多，会影响插入、删除、修改的效率；

• csv插入（即文件上传）需要设置local_infile，另外8.0.14之后的连接器，需要在数据库连接字符串中注明AllowLoadLocalInfile=true；

• 数据误删、误更，可以通过mysqlbinlog逆向构建数据进行还原（需要提前开启mysqlbinlog功能）；

• utf8mb4的字符校对编码可以存储生僻字和emoji表情，占4字节，推荐使用；

• 如果数据库经常发生锁表问题，则需要考虑逻辑业务拆分重构；