MySQL索引

2024-03-12 MySQL 评论

索引分类

逻辑分类
- 主键索引：当关系表中定义主键时，会自动创建主键索引。每张表中主键索引只能有一个，要求主键索引中每个值都唯一，即不可重复，也不能有空值
- 唯一索引：数据列不能有重复，可以有空值。一张表可以有多个唯一索引，但每个唯一索引只能有一列，如身份证，卡号等
- 普通索引：一张表可以有多个普通索引，可以重复可以为空值
- 全文索引：可以加速模糊查询，不常用
物理分类
- 聚集索引：据在物理存储中的顺序跟索引中数据的逻辑顺序相同，比如以ID建立聚集索引，数据库中ID从小到大排列，那么物理存储中该数据的内存地址值也按照从小到大存储。一般是表中的主键索引，如果没有主键索引就会以第一个非空的唯一索引作为聚集索引。一张表只能有一个聚集索引
- 非聚集索引：数据在物理存储中的顺序跟索引中数据的逻辑顺序不同。非聚集索引因为无法定位数据所在的行，所以需要扫描两遍索引树。第一遍扫描非聚集索引的索引树，确定该数据的主键ID，然后到主键索引（聚集索引）中寻找相应的数据

ALTER TABLE tableName ADD PRIMARY KEY(colList)
ALTER TABLE tableName ADD INDEX(colList)        # 普通索引
ALTER TABLE tableName ADD UNIQUE(colList)       # 唯一索引
CREATE INDEX indexName ON tableName (colList)   # 普通索引
CREATE UNIQUE indexName ON tableName (colList   # 唯一索引

索引优化思路

左边是数据表，最左边是数据记录的物理地址（在逻辑上相邻的记录在磁盘上并不一定是物理相邻的）。为了加快对Col2的查找，可以维护一棵右侧所示的二叉查找树

B-TREE和B+TREE

B-TREE

指标	描述
	B-TREE的度
	B-TREE的高度

每个非叶节点由个和个指针组成，其中
每个叶子节点最少包含一个和两个指针，最多包含个和个指针，叶节点的指针均为NULL
所有的叶节点具有相同的深度

B+TREE

内节点不存，只存储；叶子节点不存储指针
一般来说，B+TREE比B-TREE更适合实现外存储索引结构

带顺序访问指针的B+TREE

Why B+TREE

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上，那么索引查找的过程就要产生磁盘IO，相对于内存存取，磁盘存取要高几个数量级

存取模型

主存存取：主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响

磁盘存取：与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。为了提高效率，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存

预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行

B-/+TREE的性能分析

我们认为一个BTREE上的节点，对应磁盘上的一页大小的空间（注意磁盘上没有页这个概念），对应内存中的一页，并且设一页大小为

一般实际应用中，BTREE的出度会很大，所以非常小（通常不超过3），那么磁盘IO就不会发生太多次

B+TREE比B-TREE在这方面更优秀，因为B+TREE的非叶节点做得更极端，把丢了，就可以让理论的值更大，值更小

实现

MyISAM索引实现

主索引：要求是唯一的

辅助索引：不要求是唯一的

MyISAM中，主索引和辅助索引在结构上没有任何区别，MyISAM的索引方式也叫做”非聚集“的，是为了和InnoDB的聚集索引进行区分

InnoDB索引实现

InnoDB的数据文件本身就是索引文件，MyISAM中索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点域保存了完整的数据记录，这个索引的是数据表的主键

这种索引叫聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

第二个与MyISAM索引的不同是InnoDB的辅助索引域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为域

回表

在使用聚集索引/主键索引查询时，搜索到的叶节点就是我们要查询的数据，而辅助索引/非主键索引搜索到的叶子节点保存了主键，我们还需要再拿着主键再去主键索引树里面找一次，此乃回表

覆盖索引

回表存在，但非主键索引的查询并不一定会触发回表

覆盖索引（Covering Index），指一个查询语句的执行只用从索引中就能取得，不必从数据表中读取（查询只和在索引树上走的路径有关，和叶子上存储的数据无关）。也可以称为实现了索引覆盖。在这种情况下就不需要回表了

例如表covering_index_sample中有一个普通索引id_key1_key2(key1,key2)。当我们使用：

select key2 from covering_index_sample where key1="keytest";

在id_key1_key2对应的索引树上行走的时候，直接取子树的key2值就行了

索引使用策略及优化

MySQL的优化主要分为结构优化（Scheme Optimization）和查询优化（Query Optimization）。此处的高性能索引策略主要属于结构优化范畴

最左前缀原理与相关优化

联合索引：MySQL中的索引可以以一定顺序引用多个列，一般的，一个联合索引是一个有序元组，其中各个元素均为数据表的一列。另外，单列索引可以看作联合索引元素数为1的特例

以employees.titles表为例：

+--------+------------+----------+--------------+-------------+-----------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
+--------+------------+----------+--------------+-------------+-----------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |
| titles |          0 | PRIMARY  |            2 | title       | A         |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |
+--------+------------+----------+--------------+-------------+-----------+

+-------------+----------+--------+------+------------+
| Cardinality | Sub_part | Packed | Null | Index_type |
+-------------+----------+--------+------+------------+
|      299687 |     NULL |   NULL |      | BTREE      |
|      442248 |     NULL |   NULL |      | BTREE      |
|      442605 |     NULL |   NULL |      | BTREE      |
+-------------+----------+--------+------+------------+

+---------+---------------+---------+------------+
| Comment | Index_comment | Visible | Expression |
+---------+---------------+---------+------------+
|         |               | YES     | NULL       |
|         |               | YES     | NULL       |
|         |               | YES     | NULL       |
+---------+---------------+---------+------------+

可以发现titles表的主索引为

全列匹配

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+
| id | select_type | table  | partitions | type  | possible_keys | key     |
+----+-------------+--------+------------+-------+---------------+---------+
|  1 | SIMPLE      | titles | NULL       | const | PRIMARY       | PRIMARY |
+----+-------------+--------+------------+-------+---------------+---------+

+---------+-------------------+------+----------+-------+
| key_len | ref               | rows | filtered | Extra |
+---------+-------------------+------+----------+-------+
| 209     | const,const,const |    1 |   100.00 | NULL  |
+---------+-------------------+------+----------+-------+

当按照索引中的所有列进行精确匹配（这里的精确匹配指=或IN匹配），索引可以被用到。需要注意，理论上索引对顺序是敏感的，但是MySQL的查询优化器会自动调整WHERE子句的条件顺序，以使用合适的索引

最左前缀匹配

当查询条件精确匹配索引的左边连续的一个或几个列时，如或，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
+----+-------------+--------+------------+------+---------------+---------+
| id | select_type | table  | partitions | type | possible_keys | key     |
+----+-------------+--------+------------+------+---------------+---------+
|  1 | SIMPLE      | titles | NULL       | ref  | PRIMARY       | PRIMARY |
+----+-------------+--------+------------+------+---------------+---------+

+---------+-------+------+----------+-------+
| key_len | ref   | rows | filtered | Extra |
+---------+-------+------+----------+-------+
| 4       | const |    1 |   100.00 | NULL  |
+---------+-------+------+----------+-------+

精确匹配，但中间某个条件缺失

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
+----+-------------+--------+------------+------+---------------+---------+
| id | select_type | table  | partitions | type | possible_keys | key     |
+----+-------------+--------+------------+------+---------------+---------+
|  1 | SIMPLE      | titles | NULL       | ref  | PRIMARY       | PRIMARY |
+----+-------------+--------+------------+------+---------------+---------+

+---------+-------+------+----------+-------------+
| key_len | ref   | rows | filtered | Extra       |
+---------+-------+------+----------+-------------+
| 4       | const |    1 |    10.00 | Using where |
+---------+-------+------+----------+-------------+

此时索引使用情况，与单独指定相同，因为未提供，所以查询只用到了索引的第一列

隔离列

（已提供），（未提供），（已提供）

首先让我们看一下总共有多少种取值：

mysql> SELECT DISTINCT(title) FROM employees.titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+

只有7种。在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀：

mysql> EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';
+----+-------------+--------+------------+-------+---------------+---------+
| id | select_type | table  | partitions | type  | possible_keys | key     |
+----+-------------+--------+------------+-------+---------------+---------+
|  1 | SIMPLE      | titles | NULL       | range | PRIMARY       | PRIMARY |
+----+-------------+--------+------------+-------+---------------+---------+

+---------+------+------+----------+-------------+
| key_len | ref  | rows | filtered | Extra       |
+---------+------+------+----------+-------------+
| 209     | NULL |    7 |   100.00 | Using where |
+---------+------+------+----------+-------------+

实测结果：

| 0.00043450 | SELECT * FROM titles WHERE emp_no='44345' AND from_date='1987-02-16' |
| 0.00045025 | SELECT * FROM titles WHERE emp_no='44345' AND title IN ......        |

没什么优化，推测如果筛下后数据仍然很庞大，优化才有效果

查询条件没有指定索引第一列

mysql> EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';
+----+-------------+--------+------------+------+---------------+------+
| id | select_type | table  | partitions | type | possible_keys | key  |
+----+-------------+--------+------------+------+---------------+------+
|  1 | SIMPLE      | titles | NULL       | ALL  | NULL          | NULL |
+----+-------------+--------+------------+------+---------------+------+

+---------+------+--------+----------+-------------+
| key_len | ref  | rows   | filtered | Extra       |
+---------+------+--------+----------+-------------+
| NULL    | NULL | 442605 |    10.00 | Using where |
+---------+------+--------+----------+-------------+

由于不是最左前缀，这样的查询显然用不到索引

匹配某列的前缀字符串

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+------------+-------+---------------+---------+
| id | select_type | table  | partitions | type  | possible_keys | key     |
+----+-------------+--------+------------+-------+---------------+---------+
|  1 | SIMPLE      | titles | NULL       | range | PRIMARY       | PRIMARY |
+----+-------------+--------+------------+-------+---------------+---------+

+---------+------+------+----------+-------------+
| key_len | ref  | rows | filtered | Extra       |
+---------+------+------+----------+-------------+
| 206     | NULL |    1 |   100.00 | Using where |
+---------+------+------+----------+-------------+

如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀

范围查询

mysql> EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
+----+-------------+--------+------------+-------+---------------+---------+
| id | select_type | table  | partitions | type  | possible_keys | key     |
+----+-------------+--------+------------+-------+---------------+---------+
|  1 | SIMPLE      | titles | NULL       | range | PRIMARY       | PRIMARY |
+----+-------------+--------+------------+-------+---------------+---------+

+---------+------+------+----------+-------------+
| key_len | ref  | rows | filtered | Extra       |
+---------+------+------+----------+-------------+
| 4       | NULL |   14 |    10.00 | Using where |
+---------+------+------+----------+-------------+

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引

查询条件中含有函数或表达式

很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）

索引选择性与前缀索引

索引选择性

如果表记录比较少，例如一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。至于多少条记录才算多，经验值：2000

索引选择性较低，也不建议使用索引，索引选择性定义如下：：不重复的索引值

：表记录数

前缀索引

有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。下面以employees表为例介绍前缀索引的选择和使用

索引下推

索引下推（Index Condition Pushdown Optimization），在MySQL5.6引入，默认开启

SELECT * FROM people 
WHERE zipcode="95054" AND lastname LIKE "%etrunia%" AND address LIKE "%Main Street%";

其中构成一个索引

如果没有索引下推技术，那么MySQL会把zipcode="95054"查到的集合返回到MySQL服务端，然后MySQL服务端再基于lastname LIKE "%etrunia%"和address LIKE "%Main Street%"去判断数据是否符合条件

如果使用了索引下推，那么会在索引树上继续走完lastname LIKE "%etrunia%"这个条件，这样返回给MySQL服务端的索引数又会减少，可以减少回表次数

查询优化器

一条SQL语句的执行，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是执行计划。优化过程大致如下：

根据搜索条件，找出所有可能使用的索引
计算全表扫描的代价
计算使用不同索引执行查询的代价
对比各种执行方案的代价，找出成本最低的那一个

本文链接： http://whosejam.github.io/2024/03/12/MySQL索引/

版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

whoseJam