sql最佳化（mysql）

甲骨文技術支援發表於2017-01-06
MySql
mysql版本:

點選(此處)摺疊或開啟

  1. mysql> select version();
  2. +------------+
  3. | version() |
  4. +------------+
  5. | 5.7.12-log |
  6. +------------+
  7. 1 row in set (0.00 sec)
今天早上看到一個sql執行慢，要8秒左右才能出結果，sql如下：

點選(此處)摺疊或開啟

  1. SELECT *
  2. FROM CUSTOMERS
  3. WHERE TYPE = 1
  4. AND STATUS < 7
  5. AND ISAREA = 6
  6. AND INTO_TIME >= '2016-11-01'
  7. AND INTO_TIME <= '2016-12-01'
  8. ORDER BY SCORE DESC LIMIT 1140, 20;
檢視此sql的執行計劃：

點選(此處)摺疊或開啟

  1. mysql> explain SELECT *
  2.     -> FROM CUSTOMERS
  3.     -> WHERE TYPE = 1
  4.     -> AND STATUS < 7
  5.     -> AND ISAREA = 6
  6.     -> AND INTO_TIME >= '2016-11-01'
  7.     -> AND INTO_TIME <= '2016-12-01'
  8.     -> ORDER BY SCORE DESC LIMIT 1140, 20;
  9. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
  10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  11. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
  12. | 1 | SIMPLE | CUSTOMERS | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 11314 | 0.25 | Using where |
  13. +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+-------+----------+-------------+
  14. 1 row in set, 1 warning (0.00 sec)

檢視錶上都有哪些索引：

點選(此處)摺疊或開啟

  1. PRIMARY KEY (`id`),
  2.   KEY `newdata` (`newdata`),
  3.   KEY `cusname` (`cusname`),
  4.   KEY `type` (`type`,`ownerid`),
  5.   KEY `operator` (`operator`),
  6.   KEY `into_time` (`into_time`),
  7.   KEY `isarea` (`isarea`),
  8.   KEY `linkcase` (`linkcase`),
  9.   KEY `status` (`status`),
  10.   KEY `operate_id` (`operate_id`),
  11.   KEY `isparticiple` (`isparticiple`),
  12.   KEY `idx_level_op` (`level_op`),
  13.   KEY `idx_status_op` (`status_op`),
  14.   KEY `renew_ownerid` (`renew_ownerid`),
  15.   KEY `renew` (`renew`),
  16.   KEY `idx_ownerid` (`ownerid`),
  17.   KEY `idx_isarea_renew_owner` (`isarea`,`renew_ownerid`),
  18.   KEY `idx_create_time` (`create_time`),
  19.   KEY `idx_source` (`source`),
  20.   KEY `type_status` (`type`,`status`),
  21.   KEY `end_month` (`end_month`),
  22.   KEY `score` (`score`),
  23.   FULLTEXT KEY `fdx_cusname` (`cusname_idx`)
  24. ) ENGINE=InnoDB AUTO_INCREMENT=2529287 DEFAULT CHARSET=utf8 |
檢視選擇性：

點選(此處)摺疊或開啟

  1. mysql> SELECT COUNT(*) FROM CUSTOMERS where TYPE = 1;
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. | 2347457 |
  6. +----------+
  7. 1 row in set (4.66 sec)

  9. mysql> select count(*) from customers where STATUS < 7;
  10. +----------+
  11. | count(*) |
  12. +----------+
  13. | 2468461 |
  14. +----------+
  15. 1 row in set (3.51 sec)

  17. mysql> select count(*) from customers where ISAREA = 6;
  18. +----------+
  19. | count(*) |
  20. +----------+
  21. | 134726 |
  22. +----------+
  23. 1 row in set (0.17 sec)

  25. mysql> SELECT COUNT(*) FROM CUSTOMERS WHERE INTO_TIME >= '2016-11-01' AND INTO_TIME <= '2016-12-01';
  26. +----------+
  27. | COUNT(*) |
  28. +----------+
  29. | 110859 |
  30. +----------+
  31. 1 row in set (0.26 sec)

看下全表有多少資料:

點選(此處)摺疊或開啟

  1. mysql> select count(*) from customers;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. | 2481386 |
  6. +----------+
  7. 1 row in set (2.01 sec)

選擇性最好就是INTO_TIME，因為這個列也有索引，強制走這個索引

點選(此處)摺疊或開啟

  1. mysql> explain SELECT *
  2.     -> FROM CUSTOMERS USE KEY(into_time)
  3.     -> WHERE TYPE = 1
  4.     -> AND STATUS < 7
  5.     -> AND ISAREA = 6
  6.     -> AND INTO_TIME >= '2016-11-01'
  7.     -> AND INTO_TIME <= '2016-12-01'
  8.     -> ORDER BY SCORE DESC LIMIT 1140, 20;
  9. +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
  10. | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
  11. +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
  12. | 1 | SIMPLE | CUSTOMERS | NULL | range | into_time | into_time | 6 | NULL | 244448 | 0.05 | Using index condition; Using where; Using filesort |
  13. +----+-------------+-----------+------------+-------+---------------+-----------+---------+------+--------+----------+----------------------------------------------------+
  14. 1 row in set, 1 warning (0.00 sec)
用上這個索引了，實際執行不到0.3秒出結果，還是可以接受的。

最後給研發提了幾個建議：

1.儘量不要用select *

2.這個表200多萬資料，可以考慮分表。



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

相關文章