索引小tips

一、优化原则

关于创建索引：

1. 【强制】InnoDB表必须主键为id int/bigint auto_increment，且主键值禁⽌被更新 。

2. 【强制】InnoDB和MyISAM存储引擎表，索引类型必须为 BTREE 。 

3. 【建议】主键的名称以 pk 开头，唯⼀键以 uni 或 uk 开头，普通索引以 idx 开头，⼀律 使⽤⼩写格式，以字段的名称或缩写作为后缀。

4. 【建议】多单词组成的columnname，取前⼏个单词⾸字⺟，加末单词组成column_name。如: sample 表 member_id 上的索引：idx_sample_mid。

5. 【建议】单个表上的索引个数不能超过6个 。

6. 【建议】在建⽴索引时，多考虑建⽴联合索引 ，并把区分度最⾼的字段放在最前⾯。

7. 【建议】在多表 JOIN 的SQL⾥，保证被驱动表的连接列上有索引，这样JOIN 执⾏效率最⾼。 8. 【建议】建表或加索引时，保证表⾥互相不存在冗余索引 。 ⽐如：如果表⾥已经存在key(a,b)， 则key(a)为冗余索引，需要删除。

 关于SQL编写：

1. 【强制】程序端SELECT语句必须指定具体字段名称，禁⽌写成 *。

2. 【建议】程序端insert语句指定具体字段名称，不要写成INSERT INTO t1 VALUES( … )。

3. 【建议】除静态表或⼩表（100⾏以内），DML语句必须有WHERE条件，且使⽤索引查找。

4. 【建议】INSERT INTO … VALUES(XX),(XX),(XX).. 这⾥XX的值不要超过5000个。 值过多虽然上线很 快，但会引起主从同步延迟。

5. 【建议】SELECT语句不要使⽤UNION，推荐使⽤UNION ALL，并且UNION⼦句个数限制在5个以内。

6. 【建议】线上环境，多表 JOIN 不要超过5个表。

7. 【建议】减少使⽤ORDER BY，和业务沟通能不排序就不排序，或将排序放到程序端去做。ORDER BY、GROUP BY、DISTINCT 这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

8. 【建议】包含了ORDER BY、GROUP BY、DISTINCT 这些查询的语句，WHERE 条件过滤出来的结果 集请保持在1000⾏以内，否则SQL会很慢。

9. 【建议】对单表的多次alter操作必须合并为⼀次 对于超过100W⾏的⼤表进⾏alter table，必须经过DBA审核，并在业务低峰期执⾏，多个alter需整合在⼀起。 因为alter table会产⽣表锁 ，期间阻塞对于该表的所有写⼊，对于业务可能会产⽣极⼤影响。

10. 【建议】批量操作数据时，需要控制事务处理间隔时间，进⾏必要的sleep。

11. 【建议】事务⾥包含SQL不超过5个。 因为过⻓的事务会导致锁数据较久，MySQL内部缓存、连接消耗过多等问题。

12. 【建议】事务⾥更新语句尽量基于主键或UNIQUE KEY，如UPDATE … WHERE id=XX; 4 否则会产⽣间隙锁，内部扩⼤锁定范围，导致系统性能下降，产⽣死锁。

二、关于索引失效：

1. 最佳左前缀法则

2. 主键插⼊顺序

3. 计算、函数导致索引失效

4. 类型转换导致索引失效

5. 范围条件右边的列索引失效

6. 不等于(!= 或者<>)索引失效

7. is null可以使⽤索引，is not null⽆法使⽤索引

8. like以通配符%开头索引失效

9. OR 前后存在⾮索引的列，索引失效

10. 数据库和表的字符集统⼀使⽤utf8mb

三、为什么不⽤红⿊树⽽是⽤B+tree

MySQL选择使⽤B+树⽽不是红⿊树作为其索引数据结构，主要是出于以下⼏个原因：

1. 磁盘访问效率 ：

数据库系统通常需要处理⼤量的数据，这些数据往往存储在磁盘上。磁盘访问的代价远⾼于内存访问，因此优化磁盘访问效率⾄关重要。B+树的设计特别适合磁盘存储， 其节点可以容纳更多的键值对，减少了磁盘I/O次数。

2. 数据组织 ：

B+树将所有数据记录存储在叶⼦节点上，⽽⾮叶⼦节点只包含键值和指向其他节点的指针。这种结构使得范围查询和顺序访问更加⾼效，因为数据记录在叶⼦节点上连续存储，便于磁盘的顺序读取。

3. ⾼度平衡 ：

B+树的⾼度通常较低，因为每个节点可以包含更多的⼦节点。这意味着从根节点到叶⼦节点的距离较短，查询效率更⾼。红⿊树虽然也是平衡树，但在插⼊和删除操作中可能需要进⾏较多的结构调整，以保持平衡。

4. 空间利⽤率 ：

B+树的空间利⽤率更⾼。由于⾮叶⼦节点不存储数据记录，节点的空间可以被更充分地利⽤，减少了空间浪费。

5. 并发控制 ：

在数据库系统中，并发访问和锁机制是⾮常重要的。B+树的结构使得它可以更好地⽀持并发操作，尤其是在进⾏范围查询时，可以更容易地实现锁的粒度控制。

综上所述，B+树在磁盘存储、数据组织、查询效率、空间利⽤率和并发控制等⽅⾯都更适合⽤于 数据库索引，因此MySQL选择了B+树作为其索引数据结构。