初识MySql索引

最新推荐文章于 2022-10-01 13:09:15 发布

cold_azure

最新推荐文章于 2022-10-01 13:09:15 发布

阅读量165

点赞数

分类专栏： MySQL 文章标签： MySQL索引

本文链接：https://blog.csdn.net/greate_blade/article/details/103269823

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

什么是索引？

索引（在MySQL中也叫做“键（key）”）是存储引擎用以快速找到记录的一种数据结构。我们通常谈论索引的时候，若无特别指明类型，那多半说的是B-Tree索引。它使用B+Tree数据结构来存储数据（关于B-Tree和B+Tree的数据结构。除了B-Tree索引外，MySQL还支持哈希索引、空间数据索引（R-Tree）以及全文索引等，这里略过不表。我们下文所说的索引都是指B-Tree索引。

索引的优缺点

我们为什么要创建索引？索引能给我们带来哪些好处？我们知道，索引可以让服务器快速的定位到表的指定位置（类比数的目录）。但是这并不是索引的唯一作用，由于索引是按照顺序存储数据的，所以order by 和group by操作可以通过索引完成。不仅如此，因为索引中存储了实际的列值，所以某些查询只使用索引（覆盖索引）就能够完成全部查询。总结下来索引有如下三个优点：

索引大大减少了服务器需要需要扫描的数据量；
索引可以帮助服务器避免排序和临时表；
索引可以将随机I/O变为顺序I/O；

索引这个主题其实很大，如果想深入理解这部分内容，这里推荐阅读《数据库索引设计与优化》。上面说了索引的优点，那么，索引是我们的最优选择么？总的来说，只有当索引能够帮助存储引擎快速查找到记录所带来的好处大于其带来的额外开销（索引的维护）时，索引才是一种有效的方案。对于非常小的表，大部分情况下简单的全表扫描可能更高效（小的表可以一次性全部加载到内存）。对于中到大型的表，索引就非常有效。但是对于特大型的表，建立和使用索引的代价将随之增长。这种情况下分区技术+索引可能是更好的解决方案。

MySql索引原理

索引的目的

生活中随处可见索引的例子，如飞机的航班表、图书的目录等。其原理都相同，都是通过不断的缩小想要查询的数据的范围来筛选出最终想要的结果。MySQL也一样，但显然要复杂许多，因为我们不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。应该选择怎么样的方式来应对这些问题呢？让我们来联想下字典的做法，能不能把数据分成段，然后分段查询呢？最简单的如果10000条数据，1到200分成第一段，201到400分成第二段，401到600分成第三段……这样查第299条数据，只要找第二段就可以了，一下就排除了98%的干扰数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，MySQL实现比较复杂，数据保存在磁盘上（内存是有限的），而为了提高性能，可以把索引文件加载到内存中（前提是我们的索引文件不是特别大），通过索引过滤掉大部分的数据，只需从磁盘中读取我们需要的数据，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘I/O与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘I/O和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：
Alt
由于磁盘I/O是一个代价高昂的操作，因此操作系统做了一些优化：预读，即进行一次I/O读操作时，不光读取当前磁盘地址的数据，相邻的数据也都会被读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。一次磁盘I/O读取的数据我们称之为一页(page)。一页有多大数据跟具体的操作系统有关，一般为4k或8k。

索引数据结构（B+Tree）

这里所说的索引数据结构（B+Tree）单指MySQL的InnoDB存储引擎的索引，不同的存储引擎的索引数据结构各不相同。为什么采用B+Tree这种数据结构，其优点是什么？详情参考：《B-Tree、B+Tree和B*Tree详解》），这里就不在赘述。MySQL的InnoDB存储引擎采用B+Tree做索引的目的很简单：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。
Alt
如上图，是一颗b+树（索引文件），浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如根节点包含数据项5、28和65，包含指针P1、P2、P3，P1表示处于[5, 28)区间的磁盘块，P2表示处于[28, 65)的磁盘块，P3表示>=65的磁盘块。叶子节点比较特殊，它包含了其所有父节点的数据项和一个执行兄弟节点的指针，这么做看上去浪费了空间，但是对于范围查找我们可以只用顺序遍历叶节点即可，这是一种典型的空间换时间的策略。

高性能的索引策略

上面介绍了索引的一些基本知识，如何正确地创建和使用索引是实现高性能查询的基础。

聚簇索引

总的来讲，聚簇索引并不是一种索引类型，它只是一种数据存储方式—数据行聚集在B+Tree的叶节点。值得注意的有点是MySQL的InnoDB是通过主键聚集数据的，若我们没有定义主键，InnoDB会选择一个唯一的非空索引代替。

独立的列

“独立的列”是指条件中的列不能是表达式的一部分，也不能是函数的参数。下面这个查询无法使用activity_id列的索引：

eg:
    mysql> select activity_id from activity where activity_id + 1 = 3;

我们肉眼很容易看出where中的条件表达式其实等价于activity_id = 2，但是MySQL无法自动解析这个方程式，这完全是用户行为。

前缀索引和索引选择性

我们有时候需要索引很长的字符列，这会让索引变得大且慢（数据项大导致一次磁盘I/O能够读取的磁盘块变少）。一般有如下两个策略：

模拟哈希索引，即额外增加一列用以存放长字符列的hashcode，这个策略的局限性在于只能进行等值查找。
仅索引长字符列开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

索引的选择性是指，不重复的索引值与数据表的记录总数的比值，范围从1 / T 到 1之间。这个值直接影响我们对列前缀长度的选择。下面是完整列的选择性计算公式：

select count(distinct column_name) / count(*) from table_name

通过该公式得到一个选择性的比值，然后通过下面公式：

select count(distinct left(column_name, n)) / count(*) from table_name

再不断调整n的取值来取得一个近似完整列的选择性比值。
当我们选取到合适的n值以后，通过下面的方式创建一个列前缀索引：

alter table tb_name add key(column_name(n));

多列索引

一个通常的误区：为每个列创建独立的索引。然而，在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL在5.0和更新版本引入了一种叫“索引合并”(index_merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。下面是5.0以上版本的测试用例：

explain select creator_id, editor_id from t_biz_activity where creator_id = 1 or editor_id = 1;

Alt
如上图，MySQL5.0及其以上版本查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：

多个OR条件的联合（union）；
多个AND条件的相交（intersection）；
组合1和2两种情况的联合及相交；

索引合并策略有时候是一种优化的结果，实际上更多时候说明表上的索引建的非常糟糕：

当服务器需要对多个索引做联合操作时（通常有多个OR条件），往往需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。尤其当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
优化器不会把优化的时间计算到“查询成本”中，优化器只关心随机页面的读取。这会导致“查询成本”被低估。除上面所说的消耗更多CPU和内存资源外，还可能影响查询的并发性。

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，该索引就被称之为“覆盖索引”。当我们发起一个覆盖索引的查询时，通过EXPALIN可以看到其Extra列显示的是Using index"信息。

MySql索引最左前缀原理使用及优化

下面所用数据均来自mysql test_db
以employees.titles表为例，下面先查看其上都有哪些索引：

SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| titles |          0 | PRIMARY  |            1 | emp_no      | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            2 | title       | A         |        NULL |      | BTREE      |
| titles |          0 | PRIMARY  |            3 | from_date   | A         |      443308 |      | BTREE      |
| titles |          1 | emp_no   |            1 | emp_no      | A         |      443308 |      | BTREE      |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

从结果中可以到titles表的主索引为<emp_no, title, from_date>，还有一个辅助索引<emp_no>。

全列匹配

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10086' AND title='Staff' AND from_date='1988-06-21';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 59       | const |    1 |       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

当查询条件精确匹配索引的所有列时，索引自然可以被使用到。注意，理论上索引列是顺序敏感的，但是由于优化器的存在，我们上面把条件列的顺序打乱也会是一样的效果。

匹配最左前缀

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10086';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>，只能用到索引的一部分。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。

索引列中间部分遗漏

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10086' AND from_date='1988-06-21';
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | titles | ref  | PRIMARY       | PRIMARY | 4       | const |    1 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

同第二种情况类似，也用到了索引，因为title未提供，所以查询只用到了索引的第一列，后面的from_date由于title不存在而无法和左前缀连接。需要对结果进行扫描过滤from_date。针对这种情况，一个较常用的技巧是补齐缺失的中间部分。我们先来看下title有多少值：

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

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

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10086'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1988-06-21';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |    7 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

key_len为59，说明索引被用全了，但是从type和rows看出IN实际上执行了一个range查询。

查询条件没有包含索引第一列

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1988-06-21';
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 443308 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

典型的不满足最左前缀原则，这样的查询显然用不到索引。