Mysql创建索引注意事项

---

索引的基础：

1. 索引就相当于一本书的目录，目的是快速定位数据
2. 索引真正的价值是使用了索引的树搜索功能，而不是全表或全索引扫描，有效减少了扫描行数
3. mysql是索引组织表，即数据本身是存储在主键索引的叶子节点的，如果表没有定义主键，系统也会创建一个隐藏的默认主键
4. 索引的存储结构都是B+树，等值查询的时间复杂度都是log(N)，B+树是有序存储，叶子节点之间也有指针
5. 写入慢查询的标准是Sql执行时间跟long_query_time比较，如果执行时间超过long_query_time就会认为是一个慢查询，然后写入慢查询日志，这个时间默认是10s，一般线上业务会设置为1s，甚至更短
6. 多使用explain来分析自己的Sql，其中rows字段的内容是预计扫描行数，不是一个精确或准确的值，这个值是mysql通过采样统计得到的
7. 要关注扫描行数而不是是否使用索引，如果使用了索引还是进行了全表扫描或全索引扫描，那索引的价值也没有发挥出来
8. 主键索引默认是AUTO_INCREMENT，其是从auto_increment_offset开始，步长为auto_increment_increment，默认两个值都是1
9. 主键索引值并非一定按照步长连续增加，可能由于写入失败、写入回滚及使用insert … select等原因导致不连续，其中insert…select在申请自增值时会预留多个，用不完浪费掉的就是空隙
10. 自增值到达数值类型的最大值之后会保持不变，因此再次写入会引发主键冲突
11. 优化器在选择索引时候会基于扫描行数、是否使用临时表及是否排序等规则综合判断后，来决定是否使用索引以及使用哪个索引。
12. 事务有4种隔离机制，从宽到严分别是读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)、串行化(serializable)，最常用是读提交和可重复读，可通过变量’transaction_isolation’查看或修改当前隔离级别
13. 默认有两种日志redo log和binlog，redo log是innodb引擎的日志，缓存了需要磁盘io的数据，可以减少磁盘操作，也方便实现故障恢复，binlog是Server层的日志，可以给所有引擎共用的逻辑日志，两种日志通过两阶段提交策略实现记录和同步。

索引的规则：

1. 覆盖索引：普通索引的叶子节点存储的是主键值，如果要查询的内容不止是主键值，需要回表操作，即得到主键值之后还需要到主键索引进行查找得到记录值
2. 最左前缀原则：利用最左前缀原则为最常用查询创建索引，来减少索引的个数，从而减少mysql开销
3. 索引下推：mysql5.6之后引入了索引下推优化，相当于对联合索引做了查询优化，减少了回表操作，即对索引种包含的字段先做判断，直接过滤掉不满足条件的记录。
4. 普通索引在命中记录后，会查找下一个，直到找到不满足条件的记录，而唯一索引在命中后直接返回
5. 普通索引的更新会使用change buffer缓存结果，而不是每次更改都写入磁盘，因此对于时效不强的数据尽可能使用普通索引而非唯一索引

索引的优化策略：

1. 对于较长字符串的列的查询，可以考虑使用mysql5.7引入的虚拟列来创建索引
2. 对于双M主备结构，auto_increment_increment可以都设置为2，一个写奇数一个写偶数，防止两个库的主键冲突
3. 写入较多的情况下，主键数据类型可以设置为big int，有8个字节，上限可以到达2⁶⁴-1
4. 对于比较大的表做查询，可以考虑使用临时表，然后给临时表创建索引
5. 对于字符串字段可以创建前缀索引来节省空间，前缀长度可以使用count(distinct)来判断，尽可能高区分度
6. 对于区分度不打的字符串，可以考虑使用逆序存储或者hash值索引，这种方法仅适用等值查找，范围扫描会导致全索引扫描
7. 有时mysql的优化器会选错索引，可以使用force index来强制指定索引，也可以通过修改语句来引导优化器，当然也可以直接增加或删除索引来避免，也可以使用analyze table [表名]重新统计索引信息后再执行
8. 在使用join时要使用被驱动表的索引（使用了NLJ算法），尽可能让小表（满足条件的数据量相对较小的表）做驱动表，因为被驱动表的索引查找可以最大限度减少查询次数
9. explain看到join语句的Extra中有Block Nested Loop(BNL)时表示被驱动表做了全表扫描，要避免这种写法，如果无法避免，则join_buffer_size可以设大一点让驱动表的数据可以加载多一点，从而减少被驱动表全表扫描的次数（因为join_buffer一次放不下驱动表数据时，会分多次装入后跟被驱动表的全表数据做对比）

索引的锁机制

1. 默认加锁的基本单位是next-key锁，next-key锁本身是前开后闭的
2. 查找过程中访问到的对象才会加锁，访问到就会加锁
3. 索引上的等值查询，给唯一索引加锁时，next-key会退化为行锁（innodb）
4. 索引上的等值查询，向右遍历且最后一个值不满足等值条件时，next-key会退化为区间锁（前开后开）
5. 索引默认向后遍历，如果有order by … desc字段要求逆序排列，则会从后向前遍历

转自：https://www.zhenxiangsimple.com/2020/03/07/tech/mysql-index/