索引类型创建错误导致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
# Attributepcttotal min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 72669
# Exec time 22 24668s 3s 20s 9s 15s 4s 9s
# Lock time 2655ms117us 1ms245us348us68us224us
# Rows sent 02.61k111101
# Rows examine9 40.04M9.46k 20.30k 15.36k 19.40k3.60k 15.96k
# Rows affecte00000000
# Bytes sent 0 172.03k 66 66 66 660 66
# Query size 2 560.39k 215 215 215 2150 215
# String:
# Databasesebiz_kly
# Hosts10.111.124.41
# Last errno0
# Usersebiz_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/