关于mysql最左前缀原则

发表于 | 分类于 | 评论数： | 阅读次数： | 阅读次数：
本文字数： 6.2k | 阅读时长 ≈ 6 分钟

背景知识：

  1. mysql中可以使用explain关键字来查看sql语句的执行计划。
  2. 最左前缀原则主要使用在联合索引中
  3. 数据库版本Mysql5.5.53

最左前缀原则

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

  • 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；
  • 如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

1、b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

2、比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

关于最左前缀的使用，有下面两条说明：

  • 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

联合索引有一个最左前缀原则，所以建立联合索引的时候，这个联合索引的字段顺序非常重要

下面写了例子说明这个：

CREATE TABLE `test_myisam` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`conference_id` varchar(200) NOT NULL,
`account` varchar(100) DEFAULT NULL,
`status` int(2) DEFAULT NULL COMMENT '0:invite,  1:cancel_invite,  2:decline,  3:connect',
`duration` bigint(20) unsigned DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=myisam AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

以上表结构，我想通过三列进行查询 account ,status,create_time进行查询统计。

如何建立索引？

因为我们有可能按照acccount单独统计，或者按照account status，或者是account，status，create_time进行统计，如何建立索引？？？

下面是建立索引前后的对比600万数据

如何生成：执行如下脚本，account和日期不同还有status不同，分别生成一百万。

CREATE  PROCEDURE `add_data_myisam_cp_27`()
begin
declare v_rows int(10) default 1000000;
declare v_count int(10) default 0;
id_loop:LOOP
insert into test_myisam values(null,round(rand()*1000000000),'cloudp',round(rand()*3),round(rand()*100000),'2016-07-27 00:00:22');
set v_count= v_count + 1;
if v_count>v_rows then
leave id_loop;
end if;
end loop id_loop;
end;

测试结果利用建立的索引性能提高了三倍：

MariaDB [prf]> select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';
+----------+
| count(1) |
+----------+
|   167400 |
+----------+
1 row in set (1.28 sec)

MariaDB [prf]> create index as_index on test_myisam(account,status,create_time);
Query OK, 6000006 rows affected (31.60 sec)
Records: 6000006  Duplicates: 0  Warnings: 0

MariaDB [prf]> select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';
+----------+
| count(1) |
+----------+
|   167400 |
+----------+
1 row in set (0.42 sec)

MariaDB [prf]> explain select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';
+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+
| id   | select_type | table       | type | possible_keys | key      | key_len | ref         | rows   | Extra                    |
+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+
|    1 | SIMPLE      | test_myisam | ref  | as_index      | as_index | 308     | const,const | 520216 | Using where; Using index |
+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+
1 row in set (0.00 sec)

从1.28秒下降到0.42秒
但是这个date(create_time)会对每一列都会转换后对比，这里会比较消耗性能；

如何利用上索引？？

修改为：

MariaDB [prf]> explain select count(1) from test_myisam where account='cloudp' and status =3 and date(create_time)='2016-07-27';
+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+
| id   | select_type | table       | type | possible_keys | key      | key_len | ref         | rows   | Extra                    |
+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+
|    1 | SIMPLE      | test_myisam | ref  | as_index      | as_index | 308     | const,const | 520216 | Using where; Using index |
+------+-------------+-------------+------+---------------+----------+---------+-------------+--------+--------------------------+
1 row in set (0.00 sec)

MariaDB [prf]> select count(1) from test_myisam where account='cloudp' and status =3 and create_time  between '2016-07-27' and '2016-07-28';
+----------+
| count(1) |
+----------+
|   167400 |
+----------+
1 row in set (0.15 sec)

MariaDB [prf]> explain select count(1) from test_myisam where account='cloudp' and status =3 and create_time  between '2016-07-27' and '2016-07-28';
+------+-------------+-------------+-------+---------------+----------+---------+------+--------+--------------------------+
| id   | select_type | table       | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+------+-------------+-------------+-------+---------------+----------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | test_myisam | range | as_index      | as_index | 312     | NULL | 174152 | Using where; Using index |
+------+-------------+-------------+-------+---------------+----------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

如上效率又提高了三倍，是因为扫描的数据行数减少了，最后一个create_time如果不用索引需要扫描52016行，如果使用了索引扫描174152行，命中的行数为：167400行，命中率非常高了。

这里有个疑问：

如果按照天进行统计，create_time作为联合索引的第一列，如何使用上这个索引呢？？？？

至今没有想清楚，如果这一列是date类型可以直接用上索引，如果在oracle中可以date(create_time)建立函数式索引。但是mysql貌似不支持函数式索引。

一个解决方式是：

create_time定义为 date类型，在每一列存入的时候，通过触发器自动把这一行修改为date类型。

如果有好的注意欢迎留言探讨，目前没有好的方式加上create_time，可以从业务上解决，就是每天的统计计算完成以后，直接把数据推到历史表中，统计结果单独存放。

最后说一下关于索引失效的问题：
  1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
  2. 对于多列索引，不是使用的第一部分，则不会使用索引（即不符合最左前缀原则）
  3. like查询是以%开头
  4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
  5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
此外，查看索引的使用情况

  • show status like ‘Handler_read%’;

  • handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

  • handler_read_rnd_next:这个值越高，说明查询低效
坚持原创技术分享，您的支持将鼓励我继续创作！