1.表結構
([email protected]) [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.檢視執行計劃
([email protected]) [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.檢視選擇率
([email protected]) [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.查詢時間
([email protected]) [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.建立索引
([email protected]) [(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
([email protected]) [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)
([email protected]) [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 |
+----+-------------+-------+------------+-------+------------------------------+------------------------------+---------+------+-------+-------------+
建立索引絕對是一個技術活，在盡量占用少的磁盤空間，建立出合理的索引，還是要多了解業務，知己知彼。在上線前評估好，真的上線了，建立索引對系統也會有不小的影響。