Mysql中的force index和ignore index

柔情只为你懂 2023-06-21 03:58 4阅读 0赞

前几天统计一个sql，是一个人提交了多少工单，顺便做了相关sql优化。数据大概2000多w。

select  CustName,count(1) c from WorkOrder  where CreateDate>'2016-5-1' and CreateDate<'2017-1-1'
    group by CustName having c>100 
    order by c desc;

为了实验最少受其他因素干扰，将生产库的200多w数据导出来，用测试服务器进行测试。

导出来的数据是一个堆表，没有主键，没有索引。

mysql> show index from WorkOrder;   查询index方法1
    Empty set (0.00 sec)
    
    mysql> show keys from WorkOrder;    查询index方法2
    Empty set (0.00 sec)

**　1.堆表的情况**

这时候就在这时候，用执行计划分析下语句。

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)

之后，用执行计划分析下sql查询语句。

mysql> explain select  CustName,count(1) c from WorkOrder 
         where CreateDate>'2016-5-1' and CreateDate<'2017-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优化器的选择并不是最优的索引。

选用另一个联合索引 **ix\_namedate，**这次type变为index，可以这样理解，根据索引的顺序进行全表扫描，比ALL效率要高些，rows的值和堆表的值差不多。

执行语句三次，时间是 7.84 sec、7.92 sec、7.84 sec。

选用另一个联合索引** 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>'2016-5-1' and CreateDate<'2017-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在查询时，不使用某索引。

上面第一个强制不使用**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]

[mysql强制使用索引与不使用索引][mysql 1]

[利用 force index优化sql语句性能][force index_sql]

[mysql 存在索引但不能使用索引的典型场景][mysql 2]

[mysql explain用法和结果的含义][mysql explain]

[MySQL 优化之 index merge(索引合并)][MySQL _ index merge]

[MySQL单列索引和组合索引(联合索引)的区别详解][MySQL]

休对故人思故国 且将新火试新茶 诗酒趁年华

分类: [DBA之路][DBA], [数据库][Link 1]

出处：[Mysql中的force index和ignore index][Mysql_force index_ignore index]

[mysql]: https://www.2cto.com/database/201709/683142.html
[mysql 1]: http://blog.csdn.net/wufei1314a/article/details/51753425
[force index_sql]: http://blog.csdn.net/bruce128/article/details/46777567
[mysql 2]: http://www.cnblogs.com/dsitn/p/7091283.html
[mysql explain]: http://www.cnblogs.com/yycc/p/7338894.html
[MySQL _ index merge]: http://www.cnblogs.com/digdeep/p/4975977.html
[MySQL]: https://www.phpsong.com/586.html
[DBA]: https://www.cnblogs.com/sdadx/category/1047983.html
[Link 1]: https://www.cnblogs.com/sdadx/category/899234.html
[Mysql_force index_ignore index]: https://www.cnblogs.com/sdadx/p/8391985.html