Mysql-索引

索引实现原理

MySQL索引实现解析

最左匹配原则

MySQL索引最左匹配原则是什么
创建一个表格

CREATE TABLE `test` (  `a` int NOT NULL AUTO_INCREMENT,  
`b` int DEFAULT NULL,  
`c` int DEFAULT NULL,  
`d` int DEFAULT NULL,  
`e` int DEFAULT NULL,  
PRIMARY KEY(`a`),  
KEY `idx_abc` (`b`,`c`,`d`)) 
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;  
INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (1, 2, 3, 4, 5);
INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (2, 2, 3, 4, 5);
INSERT INTO test(`a`, `b`, `c`, `d`, `e`) VALUES (3, 2, 3, 4, 5);

索引下推 (Using index condition)

mysql> explain SELECT * from test where b >1 and c = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_abc       | idx_abc | 5       | NULL |    2 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

索引覆盖 （Using index）

mysql> explain SELECT b, c, d FROM test WHERE d = 2;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | index | idx_abc       | idx_abc | 15      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

走联合索引

mysql> explain SELECT * FROM test where b = 2;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_abc       | idx_abc | 5       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

走主键索引

mysql> explain SELECT * FROM test whtest where a = 2;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | whtest | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

不走索引

mysql> explain SELECT * FROM test where c = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Explain重要字段

• possible_keys：可能选择的索引
• key：实际选择的索引
• type: 链接类型，性能从好到坏：system > const > ref > range>index > all
  • system和const都是查询结果只有一行记录，sytem是特殊的join，查的是系统表。一般是主键索引或者唯一索引；
  • ref：当满足索引的最左前缀规则，或者索引不是主键也不是唯一索引时才会发生。走普通索引
  • index: 全表查询索引表
  • all：全表查询
• rows: MySQL估算会扫描的行数，数值越小越好。
• filtered: 过滤列指示按表条件过滤的表行即rows的估计百分比。 最大值为 100，这意味着没有发生行过滤。For example, if rows is 1000 and filtered is 50.00 (50%), the number of rows to be joined with the following table is 1000 × 50% = 500.
• Extra: 展示有关本次查询的附加信息
  • Using index: 代表使用索引覆盖 The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.
  • Using index condition:代表使用二级索引，会回表 Tables are read by accessing index tuples and testing them first to determine whether to read full table rows. In this way, index information is used to defer (“push down”) reading full table rows unless it is necessary.
  • Using where: 不管有没有通过索引查找，只要加载了数据到内存进行where条件筛选。

索引类型

• Primary Key（主键）（聚簇索引）：包含全量数据集
• 二级索引：除主键外的其他索引，不包含全量数据；
• 普通索引
• 唯一索引：索引列的值必须唯一，但允许有多个空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
• 组合索引
• 全文索引：聊一聊MySQL的全文检索
• 短索引（前缀索引） ：
  • 创建前缀索引

  CREATE TABLE test.t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
  );
  

• 倒排索引: “关键词” => “文档ID”，即关键词到文档id的映射

数据库的NULL存储

• MySQL 的 NULL 值是怎么存储的？
• mysql 是怎么在磁盘上是存储 NULL 的？

索引下推

五分钟搞懂MySQL索引下推

filesort原理

order by 出现 using filesort根因分析及优化

• 双路排序：
  读取行指针(即rowid)和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
  双路排序的开销可能会非常巨大，因为他会读取表两次，第二次读取会引发大量的随机IO,对于myisam涞说，这个代价尤其昂贵，myisam表利用系统调用去提取每行的数据。
• 单路排序：
  读取查询需要的所有列，按照order by 列对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

如果需要排序的列的总大小加上order by列的大小超过了 max_length_for_sort_data定义的字节，mysql就会使用双路排序，当任何需要的列（甚至不是用来order by的列）是text.blob的时候，也会使用双路排序，（可以使用substtring() 把这些列转化为可以单路排序的列）。

参考

• EXPLAIN Output Format
• explain-extra-information