mysql索引理解实践

存储结构

b-tree

---

B-tree 索引特点
1.每个叶子节点都包含指向下一个叶子节点的指针，只有叶子节点包含数据。
2.所有数据是按顺序存储的，很适合范围查找
3.可以对 <，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。
4.b+ 叶子节点存数据key-value，非叶子节点存储key
B-tree 索引限制
1.INSERT、UPDATE和DELETE影响性能，MySQL不仅要保存数据，还要保存索引文件

hash

hash索引限制
1.只存储hash 值，不存储实际的值，不能根据hash索引来进行避免读取行
2.hash索引数值并没有排序
3.hash索引不支持部分列匹配查找，哈希索引是索引列的全部值来计算哈希值
4.hash索引不支持等值比较和范围查询
5.hash冲突时，存储引擎需遍历链表中的所以指针进行比较，比较耗时，维护的代价比较高。
优势
1.hash索引只有精确匹配索引的列查询才有效，查询速度快。

索引建立的原则

---

1. 索引匹配原则，当遇到范围查询（>,<,between,like） 就停止匹配后面的索引列
2. =和in 中可以乱序，因为优化器可以优化索引可以识别的形式
3. 选择区分度高的索引列。count(disctint col)/count(*)
4. 扩展索引而不是新建，索引多会影响数据效率。
5. 索引列，不添加计算，min和max可以只走索引，索引是有序的
6. 索引字段尽量使用数字型

索引执行计划

---

CREATE TABLE IF NOT EXISTS `users`(
   `id` INT ,
   `name` VARCHAR(30) NOT NULL,
   `addr` VARCHAR(40) NOT NULL,
   `birth` DATE
);
mysql> select * from users;
+----+-------+----------+------------+
| id | name  | addr     | birth      |
+----+-------+----------+------------+
|  1 | name1 | beijing  | 2020-10-14 |
|  2 | name2 | shanghai | 2020-10-15 |
|  3 | name3 | hangzhou | 2020-10-15 |
|  4 | name4 | guanzhou | 2020-10-16 |
+----+-------+----------+------------+
4 rows in set (0.00 sec)
mysql>  explain select * from users where id=2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref  # 类型 range,all，const
possible_keys: id_name_addr #可能的索引列
          key: id_name_addr # 实际用的索引列
      key_len: 5  # 实际索引字段长度字节
          ref: const # 索引列
         rows: 1
     filtered: 100.00
        Extra: NULL # 是否下推、覆盖索引
1 row in set, 1 warning (0.00 sec)

索引类型和一些概念

---

索引分类

一个表只能有一个聚簇索引。
聚簇索引 就是以主键创建的索引，聚集索引在叶子节点存储的是表中的数据。
非聚簇索引就是以非主键创建的索引，非聚集索引在叶子节点存储的是主键和索引列
回表拿到主键再查找这个过程
覆盖索引-Using index假设所查询的列，刚好都是索引对应的列，不用再回表查，那么这叫覆盖索引
索引下推-Using index condition，在存储引擎进行数据过滤操作

普通索引允许被索引的数据列包含重复的值。而唯一索引列的值必须唯一，但允许有空值。
主键索引特殊的唯一索引，不允许有空值。

普通索引-》唯一索引-》主键索引 条件越来越严格

组合索引

列值的组合必须唯一
匹配原则

- 最左匹配原则
没有id,不走索引
mysql>  explain select * from users where  name='name2' and addr='shanghai';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 匹配列前缀 
id和name都走索引
mysql>  explain select * from users where id=2 and name like 'name%';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | range | id_name_addr  | id_name_addr | 127     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
id走索引
mysql>  explain select * from users where id=2 and name like '%name%';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | ref  | id_name_addr  | id_name_addr | 5       | const |    1 |    25.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
- 匹配范围值
- or组合索引不走索引
mysql>  explain select * from users where id=2 or  name = 'name2';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | id_name_addr  | NULL | NULL    | NULL |    4 |    43.75 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 匹配范围
mysql>  explain select * from users where id>2 and name like 'name%' and addr='shanghai';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | users | NULL       | range | id_name_addr  | id_name_addr | 5       | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
-覆盖索引
mysql> explain select name,addr  from users  where name='name2' and addr='shanghai';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | id_name_addr | 289     | NULL |    4 |    25.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

索引排序

- 索引顺序扫描
mysql> explain select id,name,addr  from users  order by id,name,addr;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | id_name_addr | 289     | NULL |    4 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- filesort 不能进行索引排序，就利用排序算法在内存中对数据进行排序。
mysql> explain select id,name,addr  from users  order by id,name,addr;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | id_name_addr | 289     | NULL |    4 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

特殊情况，查询数据和条件都走索引
or 也走索引

mysql> explain select name,addr  from users  where name='name2' or addr='shanghai';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | id_name_addr | 289     | NULL |    4 |    43.75 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

索引优化总结

• 索引进行查询的时候尽量不要使用表达式，把计算放在业务层而不是数据库层
  select * from id users where id+1=3;
• 尽量用主键索引，不用其他索引，为了防止回表
• union all,in,or 都能过使用索引，推荐用in
• 不要让字段的默认值为NULL,null值不会包含在索引中，组合索引有一列为null,那这一列对符合索引是无效。
• 前缀索引、索引选择性问题
  对串列进行索引，如果可能应该指定一个前缀长度。
• 用组合索引代替多列索引
• 尽量不要使用NOT IN、<>、!= 操作
• 当需要进行表连接的时候，join的字段数据类型必须一致

索引情况查看

• 查看整体

show index from table_name;# 查看表的索引
optimize table table_name;#
mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 25    |
| Handler_read_key      | 441   | #Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数
| Handler_read_last     | 0     |
| Handler_read_next     | 390   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 41    |
| Handler_read_rnd_next | 93    |#数据文件中读下一行的请求数
+-----------------------+-------+
7 rows in set (0.03 sec)