MySQL的SQL語句最佳化一例

甲骨文技術支援發表於2017-02-14
原文網址 : http://blog.itpub.net/20893244/viewspace-2133512/
MySql

今天在系統上看到一條sql執行時間達到9秒，不符合規範要求，最佳化之~~

sql如下：sql強制用了into_time索引


  1. # Time: 2017-02-14T11:35:01.594499+08:00
  2. # User@Host: oms_readonly[oms_readonly] @ [10.44.xxx.xxx] Id: 41636892
  3. # Query_time: 9.299612 Lock_time: 0.000124 Rows_sent: 20 Rows_examined: 2439330
  4. SET timestamp=1487043301;
  5. select * from customers force index(`into_time`) where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,20;
檢視錶的相關狀態：


  1. mysql> show table status like 'customers' \G;
  2. *************************** 1. row ***************************
  3.            Name: customers
  4.          Engine: InnoDB
  5.         Version: 10
  6.      Row_format: Dynamic
  7.            Rows: 2504609
  8.  Avg_row_length: 710
  9.     Data_length: 1780383744
  10. Max_data_length: 0
  11.    Index_length: 1253048320
  12.       Data_free: 6291456
  13.  Auto_increment: 2546101
  14.     Create_time: 2017-01-07 01:59:34
  15.     Update_time: 2017-02-14 13:58:17
  16.      Check_time: NULL
  17.       Collation: utf8_general_ci
  18.        Checksum: NULL
  19.  Create_options:
  20.         Comment:
  21. 1 row in set (0.00 sec)
表一共大約有250萬行記錄，檢視下滿足into_time<='2017-01-31 23:59:59'這個條件的有多少行


  1. mysql> select count(*) from customers where `into_time`<='2017-01-31 23:59:59';
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 2439147 |
  6. +----------+
  7. 1 row in set (0.95 sec)

顯然into_time這個列的索引已經不合適了，檢視下錶上都有那些索引


  1. Create Table: CREATE TABLE `customers` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   PRIMARY KEY (`id`),
  4.   KEY `newdata` (`newdata`),
  5.   KEY `cusname` (`cusname`),
  6.   KEY `type` (`type`,`ownerid`),
  7.   KEY `operator` (`operator`),
  8.   KEY `into_time` (`into_time`),
  9.   KEY `isarea` (`isarea`),
  10.   KEY `linkcase` (`linkcase`),
  11.   KEY `score` (`score`),
  12.   FULLTEXT KEY `fdx_cusname` (`cusname_idx`)
  13. ) ENGINE=InnoDB AUTO_INCREMENT=2546101 DEFAULT CHARSET=utf8
  14. 1 row in set (0.00 sec)
可以看到score列有索引，如果能採用這個列的索引是個比較好的選擇，去掉強制索引看下執行計劃



    1. mysql> explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 8 AND `into_time`<='2017-01-31 23:59:59' order by score desc limit 0,20;
    2. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+
    3. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    4. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+
    5. | 1 | SIMPLE | customers | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 270 | 0.92 | Using where |
    6. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+
    7. 1 row in set, 1 warning (0.00 sec)


可以看到用了score索引，執行時間從最9秒多，到最佳化後的0.0幾秒。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/20893244/viewspace-2133512/，如需轉載，請註明出處，否則將追究法律責任。

相關文章