Mysql复合索引误区实践

背景：很多dba在生产生活中经常会使用到MySQL的联合索引，作者在工作中也经常遇到，本文讲解下其中的一个误区，sql语句中联合索引必须在where条件后面按索引字段的先后顺序写吗？下面案例将实际讲解下
环境：os：centos7.4 mysql_version:mysql5.7.21

1、建表t2 创建了复合索引idx_con_update(realname,age)
   CREATE TABLE `t2` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `realname` varchar(255) NOT NULL,
      `age` tinyint(1) NOT NULL DEFAULT '0',
      `createdAt` datetime NOT NULL,
      `updatedAt` datetime NOT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_con_update` (`realname`,`age`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2、插入数据
(root@localhost:mysql.sock) [test]>select * from t2;
+----+----------+-----+---------------------+---------------------+
| id | realname | age | createdAt           | updatedAt           |
+----+----------+-----+---------------------+---------------------+
| 1 | kitten   | 20 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 2 | kitten1 | 21 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 3 | kitten2 | 22 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 4 | kitten2 | 22 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 5 | kitten3 | 23 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 6 | kitten4 | 24 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 7 | kitten5 | 25 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 8 | kitten6 | 26 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 9 | kitten7 | 27 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 10 | kitten8 | 28 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
| 11 | kitten9 | 29 | 2016-03-02 15:10:00 | 2016-03-08 16:28:00 |
+----+----------+-----+---------------------+---------------------+
11 rows in set (0.00 sec)
3、查看执行计划
   (root@localhost:mysql.sock) [test]>(root@localhost:mysql.sock) [test]>explain    select * from t2 where realname='kitten5' and age=25\G;
   *************************** 1. row ***************************
           id: 1
    select_type: SIMPLE
        table: t2
       partitions: NULL
         type: ref
   possible_keys: idx_con_update
          key: idx_con_update
      key_len: 768
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
   1 row in set, 1 warning (0.00 sec)


   分析执行计划:很明显查询语句走了复合索引idx_con_update
   把where条件顺序反一下是否还会走idx_con_update索引呢？看下面

   (root@localhost:mysql.sock) [test]>explain select * from t2    where age=25 and realname='kitten5'\G;
   *************************** 1. row ***************************
           id: 1
      select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ref
   possible_keys: idx_con_update
              key: idx_con_update
      key_len: 768
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
   1 row in set, 1 warning (0.01 sec)

   分析执行计划:查询条件顺序更换之后查询依然走索引

总结:经常有同事、朋友咨询我索引的事情、所以大胆使用复合索引、不用担心索引字段的先后顺序





新闻名称：Mysql复合索引误区实践
网页地址：http://cdxtjz.cn/article/jssipj.html

Mysql复合索引误区实践

其他资讯