如何查询mysql数据库中哪些字段适合建索引?

原创已于 2023-11-05 16:57:12 修改 · 217 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql

于 2023-11-05 16:55:51 首次发布

如何查询mysql数据库中哪些字段适合建索引?

适合建索引的字段一般是字段重复值较少，而且经常被使用的字段。对部分开发同事而言，他们可能不知道某个字段是否经常被引用，我们DBA可以通过下列语句对单表查询的返回行数和实际检查行数对比，进行评估。对单表而言，如果平均返回1行需要检查很多行，而且这个语句大量被执行，那一般是需要建索引的。

方法

可以查询performance_schema.events_statements_summary_by_digest 表
该表中记录的是自数据库启动以来，按SQL 摘要信息他统计的语句执行统计。
SQL 摘要是指的标准化SQL，即去掉空格，where条件转换为？的语句。
参考
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-digests.html

下面是一个查询示例：

mysql> SELECT * FROM performance_schema.events_statements_summary_by_digest
    -> where digest='fab4a13e43da8ab96bae93aaa00889d731ff6f0c1516c46d920359b0911494f4' \G
*************************** 1. row ***************************
                SCHEMA_NAME: tsc
                     DIGEST: fab4a13e43da8ab96bae93aaa00889d731ff6f0c1516c46d920359b0911494f4
                DIGEST_TEXT: SELECT * FROM `world` . `city` WHERE NAME = ? LIMIT ?, ...
                 COUNT_STAR: 74
             SUM_TIMER_WAIT: 424898047231
             MIN_TIMER_WAIT: 3115676851
             AVG_TIMER_WAIT: 5741838133
             MAX_TIMER_WAIT: 20876941561
              SUM_LOCK_TIME: 461000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 61
          SUM_ROWS_EXAMINED: 301846
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 74
      SUM_SORT_MERGE_PASSES: 0
             SUM_SORT_RANGE: 0
              SUM_SORT_ROWS: 0
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 74
     SUM_NO_GOOD_INDEX_USED: 0
               SUM_CPU_TIME: 0
      MAX_CONTROLLED_MEMORY: 1136856
           MAX_TOTAL_MEMORY: 3526179
            COUNT_SECONDARY: 0
                 FIRST_SEEN: 2023-11-05 16:24:38.546758
                  LAST_SEEN: 2023-11-05 16:26:30.264411
                QUANTILE_95: 13182567385
                QUANTILE_99: 20892961308
               QUANTILE_999: 20892961308
          QUERY_SAMPLE_TEXT: select * from world.city where name ='shenzhen'
LIMIT 0, 1000
          QUERY_SAMPLE_SEEN: 2023-11-05 16:26:11.906785
    QUERY_SAMPLE_TIMER_WAIT: 16244737236
1 row in set (0.00 sec)

该语句执行了74次 COUNT_STAR: 74
返回61行（不是每次都能查到数据） SUM_ROWS_SENT: 61
但是检查了总计301846行数据 SUM_ROWS_EXAMINED: 301846
很明显在城市名字name上应该建一个索引
当然，这里检查语句也验证name列没有索引

mysql> show index from world.city\G
*************************** 1. row ***************************
        Table: city
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: ID
    Collation: A
  Cardinality: 4035
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: city
   Non_unique: 1
     Key_name: CountryCode
 Seq_in_index: 1
  Column_name: CountryCode
    Collation: A
  Cardinality: 232
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.03 sec)

这是查询一个语句是否适合在哪个字段建索引的其中一个方法