MySQL优化之联合索引-编程学习网

1.表结构

(root@localhost) [test]> show create table t_demo\G;
*************************** 1. row ***************************
       Table: t_demo
Create Table: CREATE TABLE `t_demo` (
  `vin` varchar(17) NOT NULL DEFAULT '' COMMENT '底盘号',
  `lng` decimal(10,6) DEFAULT NULL COMMENT 'GPS经度',
  `lat` decimal(10,6) DEFAULT NULL COMMENT 'GPS纬度',
  `gps_time` datetime DEFAULT NULL,
  `crmkey` char(36) DEFAULT NULL ,
  `veh_model_desc` varchar(30) DEFAULT NULL ,
  `veh_series_desc` varchar(30) DEFAULT NULL ,
  `update_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' ,
  PRIMARY KEY (`vin`,`update_time`),
  KEY `idx_crmkey` (`crmkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

1 row in set (0.00 sec)

2.查看执行计划

(root@localhost) [test]> explain partitions SELECT COUNT(1)
    -> FROM t_demo tvghg
    -> WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
    ->   AND tvghg.update_time >=  '2016-07-19 06:00:00' 
    ->  AND tvghg.update_time < '2016-07-19 07:00:00' 
    -> AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+-------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows    | Extra       |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+-------------+
|  1 | SIMPLE      | tvghg | p20160724  | ref  | idx_crmkey    | idx_crmkey | 109     | const | 1961366 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)

3.查看选择率

(root@localhost) [test]> select count(distinct(crmkey))/count(*) AS selectivity1,count(distinct(update_time))/count(*) AS selectivity2, count(distinct(veh_series_desc))/count(*) AS selectivity3 from t_demo;
+--------------+--------------+--------------+
| selectivity1 | selectivity2 | selectivity3 |
+--------------+--------------+--------------+
|       0.0001 |       0.0000 |       0.0000 |
+--------------+--------------+--------------+
1 row in set (43.95 sec)

select count(distinct(concat(crmkey,update_time)))/count(*) from t_demo;
+------------------------------------------------------+
| count(distinct(concat(crmkey,update_time)))/count(*) |
+------------------------------------------------------+
|                                               0.0136 |
+------------------------------------------------------+
1 row in set (52.66 sec)

4.查询时间

(root@localhost) [test]> SELECT COUNT(1)
    -> FROM t_demo tvghg
    -> WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
    ->   AND tvghg.update_time >=  '2016-07-19 06:00:00' 
    ->  AND tvghg.update_time < '2016-07-19 07:00:00' 
    -> AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));
+----------+
| COUNT(1) |
+----------+
|     2695 |
+----------+
1 row in set (3.67 sec)

这里考虑索引怎么创建，涉及到多个字段，需要我们去做判断，查看选择率是我们创建索引的一个很重要的参考。这里的表是一个分区表，按照时间做的分区，查询字段里也包含时间字段，索引创建索引我们肯定要有时间字段的哦。

5.创建索引

(root@localhost) [(none)]> create index idx_tvghg_crmkey_update_time on `test`.`t_demo`(crmkey,update_time);
Query OK, 0 rows affected (4 min 40.21 sec)
Records: 0  Duplicates: 0  Warnings: 0
(root@localhost) [test]> alter table `test`.`t_demo` drop index idx_crmkey;
Query OK, 0 rows affected (1.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

6.查询，查看执行计划

SELECT COUNT(1)
     FROM t_demo tvghg
     WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
       AND tvghg.update_time >=  '2016-07-19 06:00:00' 
      AND tvghg.update_time < '2016-07-19 07:00:00' 
     AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));
+----------+
| COUNT(1) |
+----------+
|     2695 |
+----------+
1 row in set (0.03 sec)

(root@localhost) [test]> explain partitions SELECT COUNT(1)
    ->      FROM t_demo tvghg
    ->      WHERE tvghg.crmkey ='cf71ea00-65ff-4521-b336-fdc13846e2e2'
    ->        AND tvghg.update_time >=  '2016-07-19 06:00:00' 
    ->       AND tvghg.update_time < '2016-07-19 07:00:00' 
    ->      AND (tvghg.veh_series_desc IN ( 'A6', 'A6L'));
+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
| id | select_type | table | partitions | type  | possible_keys                | key                          | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | tvghg | p20160724  | range | idx_tvghg_crmkey_update_time | idx_tvghg_crmkey_update_time | 114     | NULL | 12446 | Using where |
+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+

创建索引绝对是一个技术活，在尽量占用少的磁盘空间，创建出合理的索引，还是要多了解业务，知己知彼。在上线前评估好，真的上线了，创建索引对系统也会有不小的影响。