阅读 1021

MySQL之索引

1. 前言

1. 最佳左前缀

最左前缀原则,就是最左优先,在创建多列索引时,要根据业务需求,where 子句中使用最频繁的一列放在最左边。 image.png 当我们创建一个组合索引的时候,如 (a1,a2,a3),相当于创建了(a1)、(a1,a2)和(a1,a2,a3)三个索引,这就是最左匹配原则。

1.1 原理分析

先举一个遵循最佳左前缀法则的例子

## 假设ab是联合索引
select * from testTable where a=1 and b=2
复制代码
  • 首先a字段在B+树上是有序的,所以我们可以通过二分查找法来定位到a=1的位置。
  • 其次在a确定的情况下,b是相对有序的,因为有序,所以同样可以通过二分查找法找到b=2的位置。

再来看看不遵循最佳左前缀的例子

select * from testTable where b=2
复制代码

我们来回想一下b有顺序的前提:在a确定的情况下: 现在你的a都飞了,那b肯定是不能确定顺序的,在一个无序的B+树上是无法用二分查找来定位到b字段的。 所以这个时候,是用不上索引的。大家懂了吗?

范围查询右边失效原理

select * from testTable where a>1 and b=2
复制代码
  • 首先a字段在B+树上是有序的,所以可以用二分查找法定位到1,然后将所有大于1的数据取出来,a可以用到索引。
  • b有序的前提是a是确定的值,那么现在a的值是取大于1的,可能有10个大于1的a,也可能有一百个a。
  • 大于1的a那部分的B+树里,b字段是无序的(开局一张图),所以b不能在无序的B+树里用二分查找来查询,b用不到索引。

2. 索引下推

2.1 什么是索引下推

导读:[官网解释索引条件下推(ICP)](dev.mysql.com/doc/refman/…

现在你的a都飞了,那b肯定是不能确定顺序的,在一个无序的B+树上是无法用二分查找来定位到b字段的。

所以这个时候,是用不上索引的。大家懂了吗?-condition-pushdown-optimization.html)

索引下推(index condition pushdown )简称ICP,在Mysql5.6的版本上推出,用于优化查询。

  • 在不使用ICP的情况下,在使用非主键索引(又叫普通索引或者二级索引)进行查询时,存储引擎通过索引检索到数据,然后返回给MySQL服务器,服务器然后判断数据是否符合条件 。

  • 在使用ICP的情况下,如果存在某些被索引的列的判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数,也可以减少MySQL服务器从存储引擎接收数据的次数。

2.2 案例分析

在开始之前先准备一张用户表(user),其中主要几个字段有:id、name、age、address。建立联合索引(name,age)。

假设有一个需求,要求匹配姓名第一个为陈的所有用户,sql语句如下:

SELECT * from user where  name like '陈%'
复制代码
  • 根据 "最佳左前缀" 的原则,这里使用了联合索引(name,age)进行了查询,性能要比全表扫描肯定要高。

问题来了,如果有其他的条件呢?假设又有一个需求,要求匹配姓名第一个字为陈,年龄为20岁的用户,此时的sql语句如下:

SELECT * from user where  name like '陈%' and age=20
复制代码
  • 这条sql语句应该如何执行呢?下面对Mysql5.6之前版本和之后版本进行分析。

Mysql5.6之前的版本

  • 5.6之前的版本是没有索引下推这个优化的,因此执行的过程如下图:

image.png

会忽略age这个字段,直接通过name进行查询,在(name,age)这课树上查找到了两个结果,id分别为2,1,然后拿着取到的id值一次次的回表查询,因此这个过程需要回表两次

Mysql5.6及之后版本

  • 5.6版本添加了索引下推这个优化,执行的过程如下图:

image.png

InnoDB并没有忽略age这个字段,而是在索引内部就判断了age是否等于20,对于不等于20的记录直接跳过,因此在(name,age)这棵索引树中只匹配到了一个记录,此时拿着这个id去主键索引树中回表查询全部数据,这个过程只需要回表一次。

2.3 实践

当然上述的分析只是原理上的,我们可以实战分析一下,因此陈某装了Mysql5.6版本的Mysql,解析了上述的语句,如下图 image.png

  • 根据explain解析结果可以看出Extra的值为Using index condition,表示已经使用了索引下推。

2.4 总结

  • 索引下推在非主键索引上的优化,可以有效减少回表的次数,大大提升了查询的效率。
  • 关闭索引下推可以使用如下命令,配置文件的修改不再讲述了,毕竟这么优秀的功能干嘛关闭呢:
set optimizer_switch='index_condition_pushdown=off';
复制代码

4. 前缀索引

4.1 什么是前缀索引

前缀索引也叫局部索引,比如给身份证的前 10 位添加索引,类似这种给某列部分信息添加索引的方式叫做前缀索引。

4.2 为什么要用前缀索引

前缀索引能有效减小索引文件的大小,让每个索引页可以保存更多的索引值,从而提高了索引查询的速度。但前缀索引也有它的缺点,不能在 order by 或者 group by 中触发前缀索引,也不能把它们用于覆盖索引。

4.3 什么情况下适合使用前缀索引?

当字符串本身可能比较长,而且前几个字符就开始不相同,适合使用前缀索引;相反情况下不适合使用前缀索引,比如,整个字段的长度为 20,索引选择性为 0.9,而我们对前 10 个字符建立前缀索引其选择性也只有 0.5,那么我们需要继续加大前缀字符的长度,但是这个时候前缀索引的优势已经不明显,就没有创建前缀索引的必要了

举例说明

当要索引的列字符很多时 索引则会很大且变慢,可以只索引列开始的部分字符串 节约索引空间 从而提高索引效率

  • 原则:降低重复的索引值

例如:现在有一个地区表,发现 area 字段很多都是以 china 开头的,那么如果以前1-5位字符做前缀索引就会出现大量索引值重复的情况,索引值重复性越低 查询效率也就越高

前缀索引测试

// 创建一个测试表\
CREATE TABLE `x_test` (\
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\
  `x_name` varchar(255) NOT NULL,\
  `x_time` int(10) NOT NULL,\
  PRIMARY KEY (`id`)\
) ENGINE=InnoDB AUTO_INCREMENT=4145025 DEFAULT CHARSET=utf8mb4\
\
// 添加200万条测试数据\
INSERT INTO x_test(x_name,x_time) 
    SELECT CONCAT(rand()*3300102,x_name),x_time
    FROM x_test 
    WHERE id < 30000;
复制代码

image.png

200万 测试数据

  • 在无任何索引的情况下随便查询一条
SELECT * FROM x_test WHERE 
x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

----
查询时间:2.253s
复制代码
  • 添加前缀索引 ( 以第一位字符创建前缀索引 )
alter table x_test add index(x_name(1))

再次查询相同sql语句

SELECT * FROM x_test WHERE 
x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

---
查询时间:3.291s
复制代码

当使用第一位字符创建前缀索引后 貌似查询的时间更长了,因为只第一位字符而言索引值的重读性太大了。200万条数据全以数字开头那么平均20万条的数据都是相同的索引值。

  • 重新建立前缀索引 这次以前4位字符来创建
alter table x_test add index(x_name(4));

再次查询相同sql语句
SELECT * FROM x_test WHERE 
x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';

---
查询时间:0.703s
复制代码

这次以前4位创建索引 大大减少了索引值的重复性 查询速度从3秒提升到0.7秒

  • 200万条数据都以数字开头 而0-9排列组合7位则可达到千万种组合

也就是以前7位来做索引则不会出现重复索引值的情况了

alter table x_test add index(x_name(7));

再次查询相同sql语句

SELECT * FROM x_test WHERE 
x_name = '1892008.205824857823401.800099203178258.8904820949682635656.62526521254';
----
查询时间:0.014s ( 首次执行无缓存状态下 )
复制代码

4.2 最佳实践

有时候需要索引很长的字符列,这会让索引变得大且慢。通常可以索引开始的部分字符,这样可以大大节约索引空间,从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指不重复的索引值(也称为基数,cardinality)和数据表的记录总数的比值,范围从1/#T到1之间。索引的选择性越高则查询效率越高,因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

一般情况下某个前缀的选择性也是足够高的,足以满足查询性能。对于BLOB,TEXT,或者很长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性,同时又不能太长(以便节约空间)。前缀应该足够长,以使得前缀索引的选择性接近于索引的整个列。换句话说,前缀的”基数“应该接近于完整的列的”基数“。

为了决定前缀的合适长度,需要找到最常见的值的列表,然后和最常见的前缀列表进行比较。下面的示例是mysql官方提供的示例数据库

在示例数据库sakila中并没有合适的例子,所以从表city中生成一个示例表,这样就有足够数据进行演示:

mysql> select database();                                                           
+------------+
| database() |
+------------+
| sakila     |
+------------+
1 row in set (0.00 sec)

mysql> create table city_demo (city varchar(50) not null);                          
Query OK, 0 rows affected (0.02 sec)

mysql> insert into city_demo (city) select city from city;                          
Query OK, 600 rows affected (0.08 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> insert into city_demo (city) select city from city_demo;
Query OK, 600 rows affected (0.07 sec)
Records: 600  Duplicates: 0  Warnings: 0

mysql> update city_demo set city = ( select city from city order by rand() limit 1);
Query OK, 1199 rows affected (0.95 sec)
Rows matched: 1200  Changed: 1199  Warnings: 0

mysql>

复制代码

因为这里使用了rand()函数,所以你的数据会与我的不同,当然那不影响聪明的你。

首先找到最常见的城市列表:

mysql> select count(*) as cnt, city from city_demo group by city order by cnt desc limit 10;               
+-----+--------------+
| cnt | city         |
+-----+--------------+
|   8 | Garden Grove |
|   7 | Escobar      |
|   7 | Emeishan     |
|   6 | Amroha       |
|   6 | Tegal        |
|   6 | Lancaster    |
|   6 | Jelets       |
|   6 | Ambattur     |
|   6 | Yingkou      |
|   6 | Monclova     |
+-----+--------------+
rows in set (0.01 sec)

mysql>

复制代码

注意到查询结果,上面每个值都出现了6-8次。现在查找到频繁出现的城市前缀。先从3个前缀字母开始,然后4个,5个,6个:

mysql> select count(*) as cnt,left(city,3) as pref from city_demo group by pref order by cnt desc limit 10;
+-----+------+
| cnt | pref |
+-----+------+
|  25 | San  |
|  15 | Cha  |
|  12 | Bat  |
|  12 | Tan  |
|  11 | al-  |
|  11 | Gar  |
|  11 | Yin  |
|  10 | Kan  |
|  10 | Sou  |
|  10 | Bra  |
+-----+------+
10 rows in set (0.00 sec)

mysql> select count(*) as cnt,left(city,4) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+------+
| cnt | pref |
+-----+------+
|  12 | San  |
|  10 | Sout |
|   8 | Chan |
|   8 | Sant |
|   8 | Gard |
|   7 | Emei |
|   7 | Esco |
|   6 | Ying |
|   6 | Amro |
|   6 | Lanc |
+-----+------+
10 rows in set (0.01 sec)

mysql> select count(*) as cnt,left(city,5) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+-------+
| cnt | pref  |
+-----+-------+
|  10 | South |
|   8 | Garde |
|   7 | Emeis |
|   7 | Escob |
|   6 | Amroh |
|   6 | Yingk |
|   6 | Moncl |
|   6 | Lanca |
|   6 | Jelet |
|   6 | Tegal |
+-----+-------+
10 rows in set (0.01 sec)
复制代码
mysql> select count(*) as cnt,left(city,6) as pref from city_demo group by pref order by cnt desc limit 10; 
+-----+--------+
| cnt | pref   |
+-----+--------+
|   8 | Garden |
|   7 | Emeish |
|   7 | Escoba |
|   6 | Amroha |
|   6 | Yingko |
|   6 | Lancas |
|   6 | Jelets |
|   6 | Tegal  |
|   6 | Monclo |
|   6 | Ambatt |
+-----+--------+
rows in set (0.00 sec)

mysql>
复制代码

通过上面改变不同前缀长度发现,当前缀长度为6时,这个前缀的选择性就接近完整列的选择性了。甚至是一样的。

当然还有另外更方便的方法,那就是计算完整列的选择性,并使其前缀的选择性接近于完整列的选择性。下面显示如何计算完整列的选择性:

mysql> select count(distinct city) / count(*) from city_demo;
+---------------------------------+
| count(distinct city) / count(*) |
+---------------------------------+
|                          0.4283 |
+---------------------------------+
row in set (0.05 sec)

mysql>
复制代码

可以在一个查询中针对不同前缀长度的选择性进行计算,这对于大表非常有用,下面给出如何在同一个查询中计算不同前缀长度的选择性:

mysql> select count(distinct left(city,3))/count(*) as sel3,
    -> count(distinct left(city,4))/count(*) as sel4,
    -> count(distinct left(city,5))/count(*) as sel5, 
    -> count(distinct left(city,6))/count(*) as sel6 
    -> from city_demo;
+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   |
+--------+--------+--------+--------+
| 0.3367 | 0.4075 | 0.4208 | 0.4267 |
+--------+--------+--------+--------+
1 row in set (0.01 sec)

mysql>
复制代码

可以看见当索引前缀为6时的基数是0.4267,已经接近完整列选择性0.4283。

在上面的示例中,已经找到了合适的前缀长度,下面创建前缀索引:

mysql> alter table city_demo add key (city(6));
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
复制代码
mysql> explain select * from city_demo where city like 'Jinch%';
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city_demo | range | city          | city | 20      | NULL |    2 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
复制代码

可以看见正确使用刚创建的索引。

前缀索引是一种能使索引更小,更快的有效办法,但另一方面也有其缺点:

mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。

文章分类
后端
文章标签