MySQL性能优化 - 索引匹配 - 最左前缀

原创

爱我所爱

修改于 2024-11-26 20:42:49

1160

文章被收录于专栏：MySQLMySQL

今日推荐： Kubernetes 初学者教程：47 个综合指南(持续完善中)

文章链接： https://cloud.tencent.com/developer/article/2465090

推荐语: 持续不断地学习是程序员的必备技能，而详尽的指南带我们一步一步走向成功，该指南对于学习Kubernate提供了完备的路径和文章目录，相信耐心的跟着作者学完所有课程，则对于该运维工具的使用一定能达到一定的高度。

下面开始今天对于索引匹配最左前缀的介绍

很多因素都会影响MySQL的性能，但是索引的特别之处在于没有索引的话我们的性能目标是无论如何也不可能达到的。

Explain语句可以帮我们验证某个查询是否用到索引以及用的是哪一个索引，但是我们不能漫无目的的建立索引。

所以得先知道索引的一些匹配原则，即在哪些情况下索引可能会生效，哪些情况下索引无法生效，leftmost prefix(最左前缀)

就是索引匹配其中的一个原则，接下来将结合实践演示该原则的使用。

还是以employees数据库中的employees表为例，如果我们为该表建立了索引idx_name(last_name, first_name)

mysql> create index idx_name on employees(last_name, first_name);
Query OK, 0 rows affected (1.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

那么我们在执行以下查询时都可以用到该索引

mysql> explain select * from employees where last_name = "Facello" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 66
          ref: const
         rows: 186
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

从key 可以看到用到idx_name这个索引，匹配到的行是186行

mysql> explain select * from employees where last_name = "Facello" and first_name = "Georgi" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 124
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

从key可以看到用到了idx_name这个索引，匹配到的行是2行

mysql> explain select * from employees where last_name = "Simmel" and (first_name = "Xuejun" or first_name = "Tokuyasu") \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_name
          key: idx_name
      key_len: 124
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

从key可以看到用到了idx_name这个索引，匹配到的行是4行

mysql> explain select * from employees where last_name = "Simmel" and first_name in ("Xuejun", "Tokuyasu") \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: range
possible_keys: idx_name
          key: idx_name
      key_len: 124
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.01 sec)

从key可以看到用到了idx_name这个索引，匹配到的行是4行

以上查询都是last_name在前, 没有用到first_name或者first_name在后，和我们建索引时候的列顺序是最左前缀匹配的，所以能用到该索引。

mysql> explain select * from employees where first_name = "Georgi" and last_name = "Facello" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 124
          ref: const,const
         rows: 2
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

从key可以看到用到了idx_name这个索引，匹配到的同样的2行，这是因为MySQL查询优化器在执行查询之前会重写 SQL 语句，所以实际执行的是select * from employees where last_name = "Facello" and first_name = "Georgi", 和前一条语句一样。

mysql> explain select * from employees where last_name = "Simmel" and emp_no = 295735 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: const
possible_keys: PRIMARY,idx_name
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

从possible_keys和keys可以看到可能用到的key是idx_name和PRIMARY, 但是因为PRIMARY匹配到的行更少，所以在实际执行中会选用PRIMARY这个索引。

但是对于以下的查询，就无法使用索引了

mysql> explain select * from employees where first_name = "Georgi" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 297793
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

从possible_keys 和keys可以看到该查询没有使用索引, type为ALL表明MySQL将遍历全表来找到所需要的行。这是因为该查询值包含first_name这一列，实际上index是从last_name开始的，所以无法用到。

我们来看各个查询的实际用时

| 0.00302800 | select * from employees where last_name = "Facello"                                                       |
| 0.00033100 | select * from employees where last_name = "Facello" and first_name = "Georgi"                             |
| 0.00208400 | select * from employees where last_name = "Simmel" and (first_name = "Xuejun" or first_name = "Tokuyasu") |
| 0.00061400 | select * from employees where last_name = "Simmel" and first_name in ("Xuejun", "Tokuyasu")               |
| 0.00058100 | select * from employees where first_name = "Georgi" and last_name = "Facello"                             |
| 0.00038700 | select * from employees where last_name = "Simmel" and emp_no = 295735                                    |
| 0.14507000 | select * from employees where first_name = "Georgi"

如果我们用如下的三列的索引

mysql> create index indx_name_gender on employees(last_name,first_name,gender);
Query OK, 0 rows affected (1.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_name on employees;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

我们可以看到在没有last_name的情况下都不能用到索引

mysql> explain select * from employees where first_name = "Xuejun" and  gender = "M" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 297793
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from employees where gender = "M"  and first_name = "Xuejun"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 297793
     filtered: 5.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

虽然first_name和gender都在idx_name_gender中，但是因为都不是索引中的最左列，所以在只有它们自己时，用不到索引。

但是从以下的查询中可以看到只要加上last_name就可以用到索引了

mysql> explain select * from employees where gender = "M"  and first_name = "Xuejun" and last_name = "Simmel" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ref
possible_keys: indx_name_gender
          key: indx_name_gender
      key_len: 125
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

实际看下各个查询的用时


| 0.00034900 | select * from employees where gender = "M"  and first_name = "Xuejun" and last_name = "Simmel"         |
| 0.16502500 | select * from employees where gender = "M"  and first_name = "Xuejun"                                  |
| 0.17797900 | select * from employees where  first_name = "Xuejun" and gender = "M"    |

从以上两个profling的数据中可以看出: