前几天统计一个sql,是一个人提交了多少工单,顺便做了相关sql优化。数据大概2000多w。
select CustName,count(1) c from WorkOrder where CreateDate>'-5-1' and CreateDate<'-1-1'group by CustName having c>100 order by c desc;
为了实验最少受其他因素干扰,将生产库的200多w数据导出来,用测试服务器进行测试。
导出来的数据是一个堆表,没有主键,没有索引。
mysql> show index from WorkOrder; 查询index方法1Empty set (0.00 sec)mysql> show keys from WorkOrder; 查询index方法2Empty set (0.00 sec)
1.堆表的情况
这时候就在这时候,用执行计划分析下语句。
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+| id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra|+----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+| 1 | SIMPLE| WorkOrder | ALL | NULL| NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort |+----+-------------+-----------+------+---------------+------+---------+------+---------+----------------------------------------------+1 row in set
select_type的值为SIMPLE,表示简单的select查询,不使用union或子查询。
type的值为ALL,表示要对表进行表扫描。
possible_keys 表示能使用哪个索引找到行记录。
key 表示Mysql决定使用的索引(键)。
key_len 表示Mysql决定使用索引的长度。
ref 表示使用哪个列和key一起从表中选择行。
rows 表示Mysql认为它执行查询时必须检查的行数。
extra 表示查询的详情信息,用到where,临时表,排序。
执行下该语句三次,发现执行了16.30 sec、16.34 sec、16.24 sec。
2.有索引的情况
建了四个索引,分别以custname,CreateDate建两个单列索引,另外两个是联合索引,只是最左边列不一样。
alter table WorkOrder add index ix_name(custname) alter table WorkOrder add index ix_date(CreateDate) alter table WorkOrder add index ix_namedate(custname,CreateDate) alter table WorkOrder add index ix_datename(CreateDate,custname)
mysql> show keys from WorkOrder;+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| WorkOrder |1 | ix_name| 1 | CustName | A |1264363 |NULL | NULL | YES | BTREE| ||| WorkOrder |1 | ix_date| 1 | CreateDate | A |2528727 |NULL | NULL || BTREE| ||| WorkOrder |1 | ix_namedate | 1 | CustName | A |1264363 |NULL | NULL | YES | BTREE| ||| WorkOrder |1 | ix_namedate | 2 | CreateDate | A |2528727 |NULL | NULL || BTREE| ||| WorkOrder |1 | ix_datename | 1 | CreateDate | A |2528727 |NULL | NULL || BTREE| ||| WorkOrder |1 | ix_datename | 2 | CustName | A |2528727 |NULL | NULL | YES | BTREE| ||+-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+6 rows in set (0.00 sec)
之后,用执行计划分析下sql查询语句。
mysql> explain select CustName,count(1) c from WorkOrder where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+| id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+| 1 | SIMPLE| WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort |+----+-------------+-----------+-------+-----------------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+1 row in set (0.01 sec)
从执行计划可以看出,Mysql从四个索引中选取了ix_datename这个索引,type为range表示索引范围扫描。rows的数量值是没堆表的1/3。
执行语句三次,时间是8.64 sec、8.61sec、8.55 sec。
我建了三个索引,那么我想用下另外三个索引怎么办?
这里可以用force index(),这个指令可以指定本次查询强制使用哪个索引,因为Mysql优化器的选择并不是最优的索引。
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_namedate) where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+| id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+| 1 | SIMPLE| WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_namedate | 307| NULL | 2528727 | Using where; Using index; Using temporary; Using filesort |+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+---------+-----------------------------------------------------------+
选用另一个联合索引ix_namedate,这次type变为index,可以这样理解,根据索引的顺序进行全表扫描,比ALL效率要高些,rows的值和堆表的值差不多。
执行语句三次,时间是7.84 sec、7.92 sec、7.84 sec。
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_name) where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+| id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra|+----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+| 1 | SIMPLE| WorkOrder | index | ix_name,ix_namedate,ix_datename | ix_name | 303| NULL | 2528727 | Using where; Using temporary; Using filesort |+----+-------------+-----------+-------+---------------------------------+---------+---------+------+---------+----------------------------------------------+1 row in set
选用另一个联合索引ix_name,这次type是index,可以这样理解,根据索引的顺序进行全表扫描,比ALL效率要高些,rows的值和堆表的值差不多。
执行语句三次,时间是 1 min 28.17 sec、1 min 27.64 sec、1 min 27.58 sec。
mysql> explain select CustName,count(1) c from WorkOrder force index(ix_date) where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+| id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra |+----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+| 1 | SIMPLE| WorkOrder | range | ix_name,ix_date,ix_namedate,ix_datename | ix_date | 4 | NULL | 921062 | Using index condition; Using MRR; Using temporary; Using filesort |+----+-------------+-----------+-------+-----------------------------------------+---------+---------+------+--------+-------------------------------------------------------------------+
选用另一个联合索引ix_date,这次type是range,表示索引范围扫描,rows的值是堆表的1/3多些 。
执行语句三次,时间是 9.55 sec、9.52 sec、9.39 sec。
假如我不想用索引了怎么办?
可以使用ignore index(),这个指令可以强制Mysql在查询时,不使用某索引。
mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date) where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+| id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+| 1 | SIMPLE| WorkOrder | range | ix_name,ix_namedate,ix_datename | ix_datename | 4 | NULL | 824372 | Using where; Using index; Using temporary; Using filesort |+----+-------------+-----------+-------+---------------------------------+-------------+---------+------+--------+-----------------------------------------------------------+mysql> explain select CustName,count(1) c from WorkOrder ignore index(ix_date,ix_name,ix_namedate,ix_datename) where CreateDate>'-5-1' and CreateDate<'-1-1' group by CustName having c>100 order by c desc;+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+| id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra|+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+| 1 | SIMPLE| WorkOrder | ALL | ix_name,ix_namedate,ix_datename | NULL | NULL | NULL | 2528727 | Using where; Using temporary; Using filesort |+----+-------------+-----------+------+---------------------------------+------+---------+------+---------+----------------------------------------------+
上面第一个强制不使用ix_date索引,那么就Mysql就从剩下的三个索引中,选取他认为是最优的索引。第二个时将四个索引都不使用,那么Mysql就进行全表扫描了。
总结:
1.Mysql的语句优化,没有绝对的正确,explain也只是给出个大致的方向,例如key_len值小的,rows小的按理说,时间应该最短,效率最高。但是,实验中时间最少的却不是那个值最小的。
2.优化还需根据实际数据情况,例如,假如我where选取的时间范围变化,或者说CustName的分布有些变化,可能跟刚才的实验,又会产生一定偏差。
3.同样我还实验了,当给表加上主键时,整体的查询时间会缩短些。
------------------附相关index命令--------------
删除主键ALTER TABLE WorkOrder MODIFY id int(11); --1.先删除auto_increment
ALTER TABLE WorkOrder DROP PRIMARY KEY; --2.再删除主键ALTER TABLE WorkOrder DROP index ix_datename;--删除索引
参考:mysql如何添加主键约束和唯一性约束,删除主键和唯一性约束
mysql强制使用索引与不使用索引
利用 force index优化sql语句性能
mysql 存在索引但不能使用索引的典型场景
mysql explain用法和结果的含义
MySQL 优化之 index merge(索引合并)
MySQL单列索引和组合索引(联合索引)的区别详解
休对故人思故国 且将新火试新茶 诗酒趁年华
分类:DBA之路,数据库
出处:Mysql中的force index和ignore index