MySQL的索引优化

mysql · 浏览次数 : 0

小编点评

| # | 操作 | 描述 | |---|---|---| | 1 | row * | 遍历所有结果 | | 2 | row * | 如果条件中有不是索引的字段，那么这查询就不会使用索引查询 | | 3 | OR | 如果条件中有不是索引的字段，那么这查询就不会使用索引查询 | | 4 | left(t_category,2)='电子' | 如果条件中包含函数，那么这查询就不会使用索引查询 | | 5 | <>或!= | 使用<>或!=操作符匹配查询条件 | | 6 | NOT NULL | 使用NOT NULL来判断索引字段不会使用索引查询 | | 7 | is NULL | 使用IS NULL来判断索引字段不会使用索引查询 | | 8 | select * from t_goods use index(index_category_name,category_part) where (t_category_id = 1 and t_name='手机' ) or t_category = '电子产品' | 使用索引进行查询 | | 9 | ignore index(category_part) | 忽略指定的索引 | | 10 | force index(category_part) | 强迫使用指定的索引进行查询 |

正文

一、索引的使用场景

1、全值匹配

通过主键索引查询

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

可以看到这里查询数据使用了主键索引。

现在我们再创建一个索引。

ALTER Table t_goods ADD INDEX index_category_name(t_category_id,t_name);

这里为t_category_id与t_name创建了联合索引。

mysql> explain select * from t_goods where t_category_id = 1 and t_name = '手机' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

这里的查询条件为t_category_id与t_name，所以查询时使用了联合索引index_category_name

2、查询范围

对索引的值进行范围查找

mysql> explain select * from t_goods where id >= 1 and id <=20 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

type: range说明根据主键索引范围进行查询。这里 Extra: Using where，说明MySQL按照主键确定范围后再回表查询数据。

3、匹配最左前缀

解释：也就是说，在使用索引时，MySQL优化器会根据查询条件使用该索引。只有满足这个匹配原则才会使用索引。例如过程创建的联合索引index_category_name(t_category_id, t_name)，如果我跳过t_category_id直接使用t_name条件查询，那么这个查询将不会使用索引。

mysql> explain select * from t_goods where t_name='手机' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到这个查询并没有使用索引。

4、查询索引列

如果在查询时包含索引的列或者查询的列都在索引中，那么查询的效率会比SELECT * 或者查询没有索引的列的效率要高很多。也就是说，如果查询的列只包含索引列，那么这个效率会高很多。例如

mysql> explain select t_name,t_category_id from t_goods where t_name='手机' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index
possible_keys: index_category_name
          key: index_category_name
      key_len: 208
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

例如这里查询的列都是索引列，所以这个查询的效率会快很多，并且使用了索引。如果有其他不是索引列需要查询，那么这个查询将不会使用索引。例如

mysql> explain select t_name,t_category_id,t_price from t_goods where t_name='手机' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

5、匹配字段前缀

如果某个字段存储的数据特别长的话，那么在这个字段上建立索引会增加MySQL维护索引的负担。匹配字段前缀就是用于解决这个问题。在字段的开头部分添加索引，按照这个索引进行数据查询。

例如在字段的前10个字符上添加索引，查询时进行匹配。

mysql> create index category_part on t_goods(t_category(10));
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次进行模糊匹配查询

mysql> explain select * from t_goods where t_category like '电子%' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: range
possible_keys: category_part
          key: category_part
      key_len: 43
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

可以看到这里使用了我们刚才创建的索引，这个索引应用于字段的前10个字符。

6、精准与范围匹配查询

在查询数据时，可以同时使用两个索引，一个为精准匹配索引，一个为范围匹配索引。例如

mysql> explain select * from t_goods where t_category_id=1 and id>=1 and id<=10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: PRIMARY,index_category_name
          key: index_category_name
      key_len: 5
          ref: const
         rows: 5
     filtered: 66.67
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

这个查询使用了两个索引进行查找，使用index_category_name进行精准匹配并且按照主键索引进行范围查询

7、匹配NULL值

在查询一个字段时，如果这个字段是索引字段，那么在判断这个字段是否为空时也会使用索引进行查询。例

mysql> explain select * from t_goods where t_category_id is null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

这里我查询t_goods表中t_category_id是NULL的字段，可以看到这里是使用了索引进行查找的。

8、连接查询匹配索引

在使用JOIN连接语句查询多个数据表中的数据时，如果连接的字段上添加了索引，那么MySQL会使用索引查询数据

mysql> explain select goods.t_name,category.t_category from t_goods goods join t_goods_category category on goods.t_category_id = category.id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: goods
   partitions: NULL
         type: ref
possible_keys: index_category_name
          key: index_category_name
      key_len: 5
          ref: demo.category.id
         rows: 5
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

在使用JOIN联合多表查询时，如果联合的字段是索引字段，那么这个查询也会使用索引列。

二、不适合使用索引的场景

1、以通配符开始的LIKE语句

在使用LIKE语句时，如果使用通配符%开头，那么MySQL将不会使用索引。例如

mysql> explain select * from t_goods where t_category like '%电' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这里的t_category字段虽然说是索引字段，但是这里的条件是以通配符%开头，所以不会使用索引查询

2、数据类型转换

当查询的字段数据进行了数据转换时，也就是说，某个索引字段的类型为字符，但是在匹配条件时，不是字符类型，那么这个查询将不会使用索引查询。例如

mysql> explain select * from t_goods where t_category = 0 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: category_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 10.00
        Extra: Using where
1 row in set, 3 warnings (0.00 sec)

例如这里的查询就没有使用索引，并且type的类型为ALL，说明进行了全表扫描查询。

3、OR语句

在OR语句中如果条件中有不是索引的字段，那么这查询就不会使用索引查询。例如

mysql> explain select * from t_goods where t_category_id = 1 or t_stock = 2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 40.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

这里因为t_stock不是索引字段，所以哪怕t_category_id索引字段匹配成功，这条语句也不会使用索引查询

4、计算索引列

如果在使用索引条件时，这个索引字段进行了计算或者使用了函数，那么此时MySQL是不会使用索引的。

mysql> explain select * from t_goods where left(t_category,2)='电子'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这里对索引字段t_category使用了函数，判断这个字段的前两个字符是否为“电子”。可以看到有15条记录，但是并没有使用索引，哪怕t_category是索引列。

5、使用<>或!=操作符匹配查询条件

这两个符号都用于表示不等于。当查询条件使用这个时不会使用索引查询。

mysql> explain select * from t_goods where t_category<>'电子产品' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: category_part
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

6、匹配NOT NULL值

在MySQL中，使用IS NULL来判断索引字段会使用索引查询，但是使用NOT NULL来判断时不会使用索引查询。


mysql> explain select * from t_goods where t_category_id is not null \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: index_category_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

三、索引提示

1、使用索引

提示MySQL查询优化器使用特定的索引，不需要评估是否使用其他索引。

mysql> explain select * from t_goods use index(index_category_name,category_part) where (t_category_id = 1 and t_name='手机' ) or t_category = '电子产品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: index_merge
possible_keys: index_category_name,category_part
          key: index_category_name,category_part
      key_len: 208,43
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using sort_union(index_category_name,category_part); Using where
1 row in set, 1 warning (0.00 sec)

这里可以使用use index()指定查询时使用特定的索引。但是MySQL仍然可以根据自身的优化器决定是否使用该索引。

2、忽略索引

可以在查询时，指定不使用某个索引。

mysql> explain select * from t_goods ignore index(category_part) where t_category = '电子产品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

这里使用ignore index（），指定在查询时，忽略指定的索引，使用这条查询没有使用索引，而是进行全表扫描

3、强制使用索引

在查询数据时，强制使用某个索引来检索数据。

与use index()的区别为，FORCE INDEX会强制使用指定的索引，而不会管MySQL的优化器如何选择。

mysql> explain select * from t_goods force index(category_part) where t_category = '电子产品'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_goods
   partitions: NULL
         type: ref
possible_keys: category_part
          key: category_part
      key_len: 43
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql

与MySQL的索引优化相似的内容：

MySQL的索引优化

哪些场景下MySQL会使用索引查询数据，哪些场景下MySQL不会使用索引查询数据，以及如何使用索引提示来告知查询优化器使用索引、忽略索引和强制索引索引。

mysql
0
分布式任务调度内的 MySQL 分页查询优化

本文主要通过图示介绍了用主键进行分片查询的过程，介绍了主键分页查询存在SQL性能问题，如何去创建高效的索引去优化主键分页查询的SQL性能问题。对于数据分布不均如何发现，提供了一些SQL查询案例来进行参考，对MySQL Index Condition Pushdown优化算法做了一些简单介绍。

mysql
0
慢 SQL 优化之索引的作用是什么？

本文针对 MySQL 数据库的 InnoDB 存储引擎，介绍其中索引的实现以及索引在慢 SQL 优化中的作用。本文主要讨论不同场景下索引生效与失效的原因。

sql 优化 索引 作用
419
Mysql索引覆盖

通常情况下，我们创建索引的时候只关注 where 条件，不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询，而不仅仅是 where 条件部分，还应该关注查询所包含的列。索引确实是一种高效的查找数据方式，但是 mysql 也可以从索引中直接获取数据，这样就不在需要读数据行了。

mysql 索引 覆盖
372
数据库系列16：MyISAM与InnoDB的索引对比

相关文章 数据库系列：MySQL慢查询分析和性能优化 数据库系列：MySQL索引优化总结（综合版） 数据库系列：高并发下的数据字段变更 数据库系列：覆盖索引和规避回表 数据库系列：数据库高可用及无损扩容 数据库系列：使用高区分度索引列提升性能 数据库系列：前缀索引和索引长度的取舍 数据库系列：MyS

myisam innodb
0
测试环境治理之MYSQL索引优化篇

测试环境这个话题对于开发和测试同学一定不陌生，大家几乎每天都会接触。但是说到对测试环境的印象，却鲜有好评，下面就主要给大家分享一次实际工作中的Mysql性能优化实践，与大家共勉~

测试环境 治理 mysql 索引
112
提高 MySQL查询效率的方法

当涉及到提高MySQL查询效率时，以下是一些重要的策略和技巧，可以帮助你优化数据库性能。无论你是一个Web开发者、数据工程师还是系统管理员，这些方法都可以帮助你确保你的MySQL数据库运行得更快、更有效。 索引优化： 使用索引是提高查询性能的关键。确保在经常用于过滤和排序的列上创建索引。 使用复合索

提高 mysql 查询 效率
454
MySQL的index merge(索引合并）导致数据库死锁分析与解决方案

在DBS-集群列表-更多-连接查询-死锁中，看到9月22日有数据库死锁日志，后排查发现是因为mysql的优化-index merge（索引合并）导致数据库死锁。

mysql index merge 索引
0
[转帖]MySQL索引优化分析之性能分析（Explain执行计划）

一、MySQL常见瓶颈 二、性能分析工具Explain（执行计划 ） 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。查看官网说明： 使用： Explain + SQL语句 作用： 三、各字段解释 3.1、

mysql 索引 优化 分析
0
MySQL面试题全解析：准备面试所需的关键知识点和实战经验

本次种子题目主要涵盖了MySQL的存储引擎和索引结构，如B+树索引和哈希索引，以及覆盖索引和回表的概念。此外，还包含了MySQL事务的ACID特性和隔离级别。另外，对MySQL主从集群中的binlog日志的执行顺序和作用进行了讨论。最后，还涉及了分库分表和读写分离的概念。这些内容涵盖了MySQL数据库的核心知识和重要技术，不仅在面试中起到关键作用还对于优化数据库性能和应用开发都具有重要意义。

mysql 面试题 解析 准备
486
# 热门排行
微软 New Bing AI 申请与使用保姆级教程（免魔法） ChatGPT API使用介绍 ChatGPT开发实战 一篇带你了解如何使用纯前端类Excel表格构建现金流量表 手把手教你玩转 Excel 数据透视表 为什么 C# 可能是最好的第一编程语言 .NET 入门到高级路线 提高工作效率的神器：基于前端表格实现Chrome Excel扩展插件 React + Springboot + Quartz，从0实现Excel报表自动化 用Echarts实现前端表格引用从属关系可视化
© PmDaddy. 2023 PmDaddy教程网 All rights reserved.