加入收藏 | 设为首页 | 会员中心 | 我要投稿 温州站长网 (https://www.0577zz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Mysql索引类型创建错误导致SQL查询缓慢

发布时间:2022-03-24 22:19:26 所属栏目:MySql教程 来源:互联网
导读:索引类型创建错误导致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
      索引类型创建错误导致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 tG
 
手动执行查看计划
+----+-------------+------------+------+------------------+------+---------+------+----------+----------------------------------------------------+
| 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 tG
*************************** 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操作,主要支持。

(编辑:温州站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读