mysql索引建立错误_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/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24486203/viewspace-2134615/，如需转载，请注明出处，否则将追究法律责任。