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操作，主要支援
 關於全文索引解釋，阿里月報相關資料