Mysql index type creation error causes SQL query to be slow
Index type creation error causes SQL query to be slow
Through pt-query-digest analysis, it is found that this statement% 95 requires more than 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: VBG 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 0 1
# Rows examine 9 40.04M 9.46k 20.30k 15.36k 19.40k 3.60k 15.96k
# Rows affecte 0 0 0
# Bytes sent 0 172.03k 66 66 66 0 66
# Query size 2 560.39k 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
Execute the viewing plan manually
+- -- +
| | 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 and order_check join can't believe there are so many return rows.
There are only more than 5000 data in each of the two tables.
Check two tables (ORDER_INFO,ORDER_CHECK) fields ORDER_NO turns out to be full text index
Modify the index type and find that it is only 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)
Query execution plan
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 |
The gap is obvious.
Official documents related to resolution
Full-text index
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.
Full-text indexing creates text-based columns (char,varchar, or text columns) to help speed up queries and DML operations on the data contained in these columns, mainly supporting
About the explanation of the full-text index, Ali monthly report related materials.
Http://mysql.taobao.org/monthly/2015/10/01/
