Mysql索引类型创建错误导致SQL查询缓慢
索引类型创建错误导致SQL查询缓慢
通过pt-query-digest分析发现这条语句%95都需要15S以上
# Query 2: 0.00 QPS, 0.01x concurrency, ID 0xB0328811156CFA43 at byte 28152292
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.67
# Time range: 2017-01-17 20:02:15 to 2017-03-02 14:48:20
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2669
# Exec time 22 24668s 3s 20s 9s 15s 4s 9s
# Lock time 2 655ms 117us 1ms 245us 348us 68us 224us
# Rows sent 0 2.61k 1 1 1 1 0 1
# Rows examine 9 40.04M 9.46k 20.30k 15.36k 19.40k 3.60k 15.96k
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 0 172.03k 66 66 66 66 0 66
# Query size 2 560.39k 215 215 215 215 0 215
# String:
# Databases ebiz_kly
# Hosts 10.111.124.41
# Last errno 0
# Users ebiz_kly
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ ########################################################
# Tables
# SHOW TABLE STATUS FROM `ebiz_kly` LIKE 'ORDER_INFO'\G
# SHOW CREATE TABLE `ebiz_kly`.`ORDER_INFO`\G
# SHOW TABLE STATUS FROM `ebiz_kly` LIKE 'ORDER_CHECK'\G
# SHOW CREATE TABLE `ebiz_kly`.`ORDER_CHECK`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(1) from (
SELECT a.* from ORDER_INFO a LEFT JOIN ORDER_CHECK b ON a.ORDER_NO=b.ORDER_NO
WHERE a.DELETED = '0'
GROUP BY a.id
ORDER BY a.CREATE_TIME DESC, a.MODIFIED_TIME DESC
) as t\G
手动执行查看计划
+----+-------------+------------+------+------------------+------+---------+------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+------------------+------+---------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 26682118 | NULL |
| 2 | DERIVED | b | ALL | ORDER_NO | NULL | NULL | NULL | 5182 | Using temporary; Using filesort |
| 2 | DERIVED | a | ALL | PRIMARY,ORDER_NO | NULL | NULL | NULL | 5149 | Using where; Using join buffer (Block Nested Loop)
order_info 跟order_check join 竟然这么多返回行
两张表的数据，每张表才5000多条
查看两张表(ORDER_INFO,ORDER_CHECK)字段ORDER_NO竟然是full text index
修改索引类型发现只要0.3了.
system@localhost 17:45: [ebiz_kly]> SELECT count(1) from (
-> SELECT a.* from ORDER_INFO a LEFT JOIN ORDER_CHECK b ON a.ORDER_NO=b.ORDER_NO
->
-> WHERE a.DELETED = '0'
-> GROUP BY a.id
-> ORDER BY a.CREATE_TIME DESC, a.MODIFIED_TIME DESC
->
-> ) as t\G
*************************** 1. row ***************************
count(1): 5205
1 row in set (0.28 sec)
查询执行计划
system@localhost 17:45: [ebiz_kly]> explain SELECT count(1) from (
-> SELECT a.* from ORDER_INFO a LEFT JOIN ORDER_CHECK b ON a.ORDER_NO=b.ORDER_NO
->
-> WHERE a.DELETED = '0'
-> GROUP BY a.id
-> ORDER BY a.CREATE_TIME DESC, a.MODIFIED_TIME DESC
->
-> ) as t;
+----+-------------+------------+-------+-------------------+-----------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------+-----------+---------+---------------------+------+----------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5157 | NULL |
| 2 | DERIVED | a | index | PRIMARY,idx_oi_on | PRIMARY | 8 | NULL | 5157 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | b | ref | idx_oc_on | idx_oc_on | 768 | ebiz_kly.a.ORDER_NO | 1 | Using where |
差距很明显了
官方文档相关解决
全文索引
InnoDB FULLTEXT Indexes
FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries and DML operations on data contained within those columns,
omitting any words that are defined as stopwords.
InnoDB FULLTEXT indexes have an inverted index design. Inverted indexes store a list of words, and for each word, a list of documents that the word appears in. To support
proximity search, position information for each word is also stored, as a byte offset.
全文索引创建基于文本的列（char，varchar，或文本列）来帮助加快对包含在这些列的数据查询和DML操作，主要支持
关于全文索引解释，阿里月报相关资料
http://MySQL.taobao.org/monthly/2015/10/01/
网页名称：Mysql索引类型创建错误导致SQL查询缓慢
网址分享：http://scyanting.com/article/gjpshg.html
通过pt-query-digest分析发现这条语句%95都需要15S以上
# Query 2: 0.00 QPS, 0.01x concurrency, ID 0xB0328811156CFA43 at byte 28152292
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.67
# Time range: 2017-01-17 20:02:15 to 2017-03-02 14:48:20
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 7 2669
# Exec time 22 24668s 3s 20s 9s 15s 4s 9s
# Lock time 2 655ms 117us 1ms 245us 348us 68us 224us
# Rows sent 0 2.61k 1 1 1 1 0 1
# Rows examine 9 40.04M 9.46k 20.30k 15.36k 19.40k 3.60k 15.96k
# Rows affecte 0 0 0 0 0 0 0 0
# Bytes sent 0 172.03k 66 66 66 66 0 66
# Query size 2 560.39k 215 215 215 215 0 215
# String:
# Databases ebiz_kly
# Hosts 10.111.124.41
# Last errno 0
# Users ebiz_kly
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ ########################################################
# Tables
# SHOW TABLE STATUS FROM `ebiz_kly` LIKE 'ORDER_INFO'\G
# SHOW CREATE TABLE `ebiz_kly`.`ORDER_INFO`\G
# SHOW TABLE STATUS FROM `ebiz_kly` LIKE 'ORDER_CHECK'\G
# SHOW CREATE TABLE `ebiz_kly`.`ORDER_CHECK`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT count(1) from (
SELECT a.* from ORDER_INFO a LEFT JOIN ORDER_CHECK b ON a.ORDER_NO=b.ORDER_NO
WHERE a.DELETED = '0'
GROUP BY a.id
ORDER BY a.CREATE_TIME DESC, a.MODIFIED_TIME DESC
) as t\G
手动执行查看计划
+----+-------------+------------+------+------------------+------+---------+------+----------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+------------------+------+---------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 26682118 | NULL |
| 2 | DERIVED | b | ALL | ORDER_NO | NULL | NULL | NULL | 5182 | Using temporary; Using filesort |
| 2 | DERIVED | a | ALL | PRIMARY,ORDER_NO | NULL | NULL | NULL | 5149 | Using where; Using join buffer (Block Nested Loop)
order_info 跟order_check join 竟然这么多返回行
两张表的数据，每张表才5000多条
查看两张表(ORDER_INFO,ORDER_CHECK)字段ORDER_NO竟然是full text index
修改索引类型发现只要0.3了.
system@localhost 17:45: [ebiz_kly]> SELECT count(1) from (
-> SELECT a.* from ORDER_INFO a LEFT JOIN ORDER_CHECK b ON a.ORDER_NO=b.ORDER_NO
->
-> WHERE a.DELETED = '0'
-> GROUP BY a.id
-> ORDER BY a.CREATE_TIME DESC, a.MODIFIED_TIME DESC
->
-> ) as t\G
*************************** 1. row ***************************
count(1): 5205
1 row in set (0.28 sec)
查询执行计划
system@localhost 17:45: [ebiz_kly]> explain SELECT count(1) from (
-> SELECT a.* from ORDER_INFO a LEFT JOIN ORDER_CHECK b ON a.ORDER_NO=b.ORDER_NO
->
-> WHERE a.DELETED = '0'
-> GROUP BY a.id
-> ORDER BY a.CREATE_TIME DESC, a.MODIFIED_TIME DESC
->
-> ) as t;
+----+-------------+------------+-------+-------------------+-----------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------+-----------+---------+---------------------+------+----------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 5157 | NULL |
| 2 | DERIVED | a | index | PRIMARY,idx_oi_on | PRIMARY | 8 | NULL | 5157 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | b | ref | idx_oc_on | idx_oc_on | 768 | ebiz_kly.a.ORDER_NO | 1 | Using where |
差距很明显了
官方文档相关解决
全文索引
InnoDB FULLTEXT Indexes
FULLTEXT indexes are created on text-based columns (CHAR, VARCHAR, or TEXT columns) to help speed up queries and DML operations on data contained within those columns,
omitting any words that are defined as stopwords.
InnoDB FULLTEXT indexes have an inverted index design. Inverted indexes store a list of words, and for each word, a list of documents that the word appears in. To support
proximity search, position information for each word is also stored, as a byte offset.
全文索引创建基于文本的列（char，varchar，或文本列）来帮助加快对包含在这些列的数据查询和DML操作，主要支持
关于全文索引解释，阿里月报相关资料
http://MySQL.taobao.org/monthly/2015/10/01/
网页名称：Mysql索引类型创建错误导致SQL查询缓慢
网址分享：http://scyanting.com/article/gjpshg.html
