[MySQL] MySQL索引是啥?不懂就问

• 概述
  
• 从二叉树到B+树
  
• 聚集索引
  
• 非聚集索引
  
• 联合索引和覆盖索引
  
• B+树索引VS哈希索引
  
• 普通索引和唯一索引
  
• InnoDB VS MyISAM
  
• 用explain分析索引使用
  
• 总结
  

mysql> create table test( --创建测试表
  -> id int(10) not null AUTO_INCREMENT,
  -> a int(10) default null,
  -> b int(10) default null,
  -> c int(10) default null,
  -> d int(10) default null,
  -> primary key(id), --主键索引
  -> key idx_a(a), --辅助索引
  -> key idx_b_c(b,c) --联合索引
  -> )engine=InnoDB charset=utf8mb4;
Query OK, 0 rows affected, 5 warnings (0.09 sec)
mysql> drop procedure if exists insert_test_data;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> delimiter | --创建存储过程，插入十万个数据
mysql> create procedure insert_test_data()
  -> begin
  -> declare i int;
  -> set i=1;
  -> while(i<=100000) do
  -> insert into test(a,b,c,d)values(i,i,i,i);
  -> set i=i+1;
  -> end while;
  -> end |
Query OK, 0 rows affected (0.11 sec)
mysql> delimiter ;
mysql> call insert_test_data(); --执行存储过程
Query OK, 1 row affected (11 min 44.13 sec)

   

• .frm：记录了表定义语句.   
  
• myd：记录了真实表数据.   
  
• myi：记录了索引数据
  

   

• B+树索引可能需要多次运用二分查找来找到对应的数据块。   
  
• Hash索引时通过Hash函数，计算出Hash值，在表中找出对应的数据。
  

   

• Insert Buffer   
  
• 对于非聚集索引的插入，先判断插入的非聚集索引是在在缓存池中。若在则直接插入，若不在，则先放入Insert Buffer，之后在一一定频率和情况镜像Insert Buffer和辅助索引页子节点的合并操作。将多次插入合并为一次操作，减少磁盘离散读取。要求索引是辅助索引且不唯一。   
  
• Change Buffer   
  
• 是Insert Buffer的升级版，除了插入还支持删改。通过innodb_change_buffer设置使用场景，默认为all（还有none、inserts、changes等值），通过innodb_change_buffer_max_size设置最大使用内存占比（默认25%，最大值50%），但在RR隔离级别下会出现死锁。同样要求索引是辅助索引且不唯一。
  

   

• 数据修改时，普通索引可用Change Buffer，而唯一索引不行。   
  
• 数据修改时，唯一索引在RR隔离级别下更容易出现死锁。   
  
• 查询数据时，普通索引查到一条记录还需继续判断下一个记录，而唯一索引查到后直接返回。   
  
• 当业务要求某字段唯一时，若代码能保证写入唯一值，则用普通索引，否则用唯一索引。
  

   

• InnoDB要缓存数据和索引；MyISAM只缓存索引，换进换出的减少。   
  
• InnoDB寻址要映射到块再到行；MyISAM直接记录文件的OFFSET，定位更快。   
  
• InnoDB还要维护MVCC一致，或许你的场景没有，但也需要检查和维护。
  

mysql> explain select * from test where a=88888;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE    | test  | NULL     | ref  | idx_a     | idx_a | 5     | const |  1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
mysql> explain select b,c from test where b=88888;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra     |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE    | test  | NULL     | ref  | idx_b_c     | idx_b_c | 5     | const |  1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where a=(select a from test where a=88888);
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra     |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | PRIMARY   | test  | NULL     | ref  | idx_a     | idx_a | 5     | const |  1 |   100.00 | Using where |
|  2 | SUBQUERY  | test  | NULL     | ref  | idx_a     | idx_a | 5     | const |  1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)