Mysql笔记（三）索引使用场景及索引失效分析

最新推荐文章于 2024-07-10 10:50:19 发布
最新推荐文章于 2024-07-10 10:50:19 发布
阅读量530 收藏
点赞数
分类专栏： Mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_33382373/article/details/102580944
版权

索引使用场景

哪些情况需要创建索引
  • 主键自动建立主键唯一索引
  • 频繁作为where查询条件的字段应该创建索引
  • 多表关联查询中，关联on两边的字段应该创建索引
  • 查询中排序的字段，应该创建有顺序的索引B+Tree
  • 利用组合索引完成索引覆盖避免回表
  • 统计或分组字段，应该创建索引
哪些情况不需要创建索引
  • 表记录太少，因为索引也是有存储开销的
  • 频繁更新，因为索引也会更新维护
  • 查询字段使用频率不高
为什么使用组合索引

由多个字段组成的索引，使用顺序就是创建时的顺序。

ALTER TABLE 'table_name' ADD INDEX index_name(col1,col2,col3）

组合索引在一个索引树上存在多个字段，这样的优势是容易形成索引覆盖从而提高查询效率。
组合索引在使用时，遵循最左前缀原则：

  1. 前缀索引
    LIKE '常量%'（‘hello%’） 使用索引
    LIKE '%常量'（’%hello’） 不使用索引
  2. 最左前缀
    从左向右匹配直到遇到返回查询（>、<、between）后索引失效
    例如：索引（idx_a_b_c），其中a、b、c分别代表表中的三个字段
    1. where a=1 and b=1 and c=1 会使用索引（abc）
    2. where a=1 and b=1 and c>1 会使用索引（abc）
    3. where a=1 and b>1 and c=1 会使用索引（ab）
    4. where a=1 and c=1 and b=1 会使用索引（abc）
    5. where b=1 and c=1 不会使用索引

注：组合索引的使用必须按照创建时的顺序才能匹配，mysql会根据where条件自动优化顺序以匹配组合索引。

索引失效分析

示例表结构：

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NULL,
  `sex` tinyint(3) unsigned NULL DEFAULT '0' COMMENT '0 ：男     1：女',
  `age` tinyint(3) unsigned NULL,
  `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_name_sex_age` (`name`,`sex`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8;

示例在使用全索引的情况下，key_length长度应为154；只使用了name的情况为152，name和sex为153。

  1. 全值匹配
mysql> explain select * from `user` where `name`='ym' and sex=1 and age=20;
+----+-------------+-------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_sex_age | idx_name_sex_age | 154     | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+---------+-------------------+------+-----------------------+

条件与索引一一对应的情况，可以使用索引

  1. 最左前缀法则

组合索引：带头索引不能死，中间索引不能断

错误示例：

  • 带头索引死：
mysql> explain select * from `user` where sex=0 and age=20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  • 中间索引断（带头索引生效，其他索引失效）
mysql> explain select * from `user` where `name`='ym' and age=20;
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_sex_age | idx_name_sex_age | 152     | const |    1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
  1. 不要在索引上做计算

不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描

mysql> explain select * from `user` where SUBSTRING(`name`,2)='ym';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  1. 范围条件右边的列失效

不能继续使用索引中范围条件（bettween、<、>、in等）右边的列

mysql> explain select * from `user` where `name`='ym' and sex>0 and age=20;
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name_sex_age | idx_name_sex_age | 153     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
  1. 尽量使用索引覆盖

尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少select *

mysql> explain select * from `user`;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
mysql> explain select `name`,sex,age,time from `user`;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

mysql> explain select id,`name`,sex,age from `user`;
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_sex_age | 154     | NULL | 1000 | Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
  1. 索引字段上不要使用不等于

索引字段上使用（！= 或者 < >）判断时，会导致索引失效而转向全表扫描
注：主键索引会使用范围索引，辅助索引会失效

mysql> explain select * from `user` where `name`!='ym';
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_sex_age | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+

mysql> explain select * from `user` where id != 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | range | PRIMARY       | PRIMARY | 4       | NULL |  999 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  1. 辅助索引使用null判断时

使用is null判断时可以使用索引，而使用is not null时不使用索引

mysql> explain select * from `user` where `name` is null;
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_sex_age | idx_name_sex_age | 153     | const |    1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
mysql> explain select * from `user` where `name` is not null;
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_sex_age | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
  1. 索引字段使用like时不以通配符开头

索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描

mysql> explain select * from `user` where `name` like 'ym%';
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | user  | range | idx_name_sex_age | idx_name_sex_age | 153     | NULL |    1 | Using index condition |
+----+-------------+-------+-------+------------------+------------------+---------+------+------+-----------------------+
mysql> explain select * from `user` where `name` like '%ym';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

解决like通配符开头索引失效问题，可以使用覆盖索引解决。

mysql> explain select id,`name`,sex,age from `user` where `name` like '%ym';
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | user  | index | NULL          | idx_name_sex_age | 156     | NULL | 1000 | Using where; Using index |
+----+-------------+-------+-------+---------------+------------------+---------+------+------+--------------------------+
  1. 索引字段为字符串时要加引号

索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描

mysql> explain select * from `user` where `name`=123 and age=20;
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_sex_age | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
mysql> explain select * from `user` where `name`='123' and age=20;
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | user  | ref  | idx_name_sex_age | idx_name_sex_age | 153     | const |    1 | Using index condition |
+----+-------------+-------+------+------------------+------------------+---------+-------+------+-----------------------+
  1. 索引字段不要使用or

索引字段使用 or 时，会导致索引失效而转向全表扫描

mysql> explain select * from `user` where `name`='ym' or sex=0;
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys    | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | user  | ALL  | idx_name_sex_age | NULL | NULL    | NULL | 1000 | Using where |
+----+-------------+-------+------+------------------+------+---------+------+------+-------------+
总结

假设索引 index(a,b,c)

Where索引使用
where a=3Y，使用到a
where a=3 and b=4Y，使用到a，b
where a=3 and b=4 and c=5Y，使用到a，b，c
where b=4 或 where b=4 and c=5 或 where c=5N
where a=3 and c=5Y，使用到a
where a=3 and b>4 and c=5Y，使用到a，b
where a=3 and b like ‘kk%’ and c=5Y，使用到a，b，c
where a=3 and b like ‘%kk’ and c=5Y，使用到a
where a=3 and b like ‘%kk%’ and c=5Y，使用到a
where a=3 and b like ‘k%kk%’ and c=5Y，使用到a，b，c

优化口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有OR，索引失效要少用；

古树清风
关注
专栏目录
无论你是技术宅，还是刚入行，15个必知的Mysql索引失效场景，别再踩坑了
duan18888的博客
05-05 138
背景 无论你是技术大佬，还是刚入行的小白，时不时都会踩到Mysql数据库不走索引的坑。常见的现象就是：明明在字段上添加了索引，但却并未生效。 前些天就遇到一个稍微特殊的场景，同一条SQL语句，在某些参数下生效，在某些参数下不生效，这是为什么呢？ 另外，无论是面试或是日常，Mysql索引失效的通常情况都应该了解和学习。 为了方便学习和记忆，这篇文件将常见的15种不走索引情况进行汇总，并以实例展示，帮助大家更好地避免踩坑。建议收藏，以备不时之需。 数据库及索引准备 创建表结构 为了逐项验证索引的使
Mysql索引使用场景和索引失效分析
chenzhouwa4173的博客
06-10 225
Mysql的索引使用场景，并且分析常用的组合索引结构及原则 索引的使用场景： 主键自动建立唯一索引 频繁作为查询条件的字段应该创建索引 多表关联查询中，关联字段的两边都应该创建索引（比如A表，B表的name关联，两个表的name都要建立索引） 查询中排序的字段要创建索引（...
mysql什么时候用索性_mysql什么时候用索引
weixin_42394573的博客
02-02 99
MySQL中需要用到索引的情况有：当使用不以通配符开始的link，进行联结时从一个表中提取行时以及找出指定索引的max或min值等MySQL何时使用索引
Mysql 创建了A, B, C联合索引，使用B,C能否索引
最新发布
Gemini1995的博客
07-10 535
使用联合索引 (A, B, C) 时，如果查询条件中只包含 B 和 C 列，索引是无法被利用的。要使查询能够使用索引，需要在查询条件中包含联合索引的前导列 A。在 MySQL 中，创建了 (A, B, C) 的联合索引后，是否能够使用索引取决于查询条件中是否包含了前导列。
【MySQL面试题】建立a，b，c三个字段的联合索引，判断下列语句是否使用到索引？用到了哪些索引？（通过explain的key_len来判断）
weixin_44798288的博客
11-04 2422
通过explain执行计划的key_len判断使用到的索引
一个表中两个索引，WHERE条件中都有用到会用哪个索引？
GravityQ的博客
08-06 1718
碰到这种问题最简单的方法就是验证一下。 EXPLAIN SELECT class_score,class_name from student where class_score=56 and class_name='a' ; 都是唯一索引。type是const 两个都是普通索引，影响行数相同，都是一行，type是ref。使用一个索引但并不是左边的 两个都是普通索引，影响行数不同，type是ref。 两个都是普通索引，影响行数相同，都是两行，type是index_merge。两
一文读懂索引（覆盖索引，最左匹配原则）
jumpe_17的博客
09-17 4129
1. 什么是索引 索引是帮助数据库高效获取数据的数据结构。简而言之,索引是数据结构 2. 索引的底层数据结构 2.1 Hash索引 哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。 为何能够通过 key 快速取出 value呢？ 原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 value 对应的 index，找到了 index 也就找到了对应的 value。 index = hash % array.size()
MySQL索引 使用笔记
04-08
【MySQL索引 使用笔记】 MySQL数据库是世界上最流行的开源关系型数据库管理系统之一，其高效的数据查询能力在很大程度上依赖于索引。本笔记将深入探讨MySQL中的索引使用，旨在帮助你提升数据库性能。 1. 索引的...
mysql高级笔记，mysql索引、存储过程、查询缓存、并发参数调整、MyISAM表锁、系统性能优化
08-19
本笔记将深入探讨MySQL的几个关键高级主题，包括索引、存储过程、查询缓存、并发参数调整、MyISAM表锁以及系统性能优化策略。 首先，我们来讨论**MySQL索引**。索引是提高查询速度的关键，它在数据库中的作用类似于...
mysql之Linux安装，重点是索引的优化笔记
08-02
### MySQL之Linux安装与索引优化笔记 #### 一、MySQL简介及Linux版安装 **1. MySQL概述** MySQL是一种关系型数据库管理系统(RDBMS)，由瑞典MySQL AB公司开发，目前属于Oracle公司。它是一种开源软件，因其性能...
笔记整理-mysql索引
qq_42114504的博客
03-13 1884
MySQL的索引分类 索引分类 1.普通索引index :加速查找 2.唯一索引 主键索引：primary key ：加速查找+约束（不为空且唯一） 唯一索引：unique：加速查找+约束 （唯一） 3.联合索引 -primary key(id,name):联合主键索引 -unique(id,name):联合唯一索引 -index(id,name):联合普通索引 4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。 创建索引 Alter table ‘tablename’add prima
Mysql索引的创建和使用
IHBOS的博客
04-12 7640
这里写目录标题Mysql索引Mysql索引的概念Mysql索引的作用Mysql索引的副作用创建索引的原则依据索引的分类以及创建索引创建的方法普通索引直接创建索引和查询索引的方式修改表方式创建创建表的时候指定索引唯一索引直接创建唯一索引修改表方式创建创建表的时候指定主键索引创建表的时候指定修改表方式创建组合索引（单列索引与多列索引）全文索引直接创建索引修改表方式创建创建表的时候指定索引使用全文索引查询查看索引删除索引直接删除索引修改表方式删除索引删除主键索引 Mysql索引 Mysql索引的概念 1、索引是
【MySQL】MySQL能使用索引的情况以及创建索引注意事项
小七的博客
02-28 823
目录 一、全值匹配 二、匹配左边的列 三、匹配列前缀 四、匹配范围值 五、精确匹配某一列并范围匹配另外一列 六、排序 七、分组 八、使用联合索引进行排序和分组的注意事项 九、不可以使用索进行排序或分组的情况 ASC、DESC混用 十、索引失效总结 十一、如何建立索引 11.1 考虑索引选择性 11.2 考虑前缀索引 十二、总结 条件：一个表创建了（b,c,d）联...
部分会导致索引失效的情况
运气只是当机会来临时，你已为它准备好。
08-11 2607
使所有索引都有效的方案：将c放到建立索引的语句的最后，例如：建立(a,b,d,c)的索引（a,b,d的顺序可以任意调整），这样执行sql的时候，优化器会帮我们调整WHERE后a,b,c,d的顺序（将c放到最后），让我们用上索引。其实单个索引字段，使用is null或is not null时，是可以命中索引的，但是两个不同索引字段用or连接时，索引就失效了。叶子节点的b值为1,2,1,4,1,2，它不是有序的，因此不能使用(a,b)联合索引。假设USER表中的user_id列有索引，age列没有索引。...
索引覆盖与回表
AF_0228的博客
09-09 5615
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
mysql索引的使用
tengjian007的博客
03-15 365
https://www.jb51.net/article/140749.htm 一 、介绍 为何要有索引? 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。 什么是索引？ 索引在MySQL中也叫做“键”...
MYSQL索引优化（索引失效场景）
我神级欧文的博客
08-02 927
学习mysql是作为一名Java工程师必不可少的事情，但是我们只认识mysql的增删查改建表等等的sql语句其实远远不够的，对于进阶mysql来说，索引是一个很重要的部分。下面我们就来说一下在mysql中索引失效的几种场景吧。 首先先来看看测试的表结构 dept部门表 建立的索引（name，number，comment三个字段的复合索引） 测试数据 1.遵从最左前缀...
Mysql联合索引的最左前缀原则b+tree
我的博客
10-09 703
Mysql联合索引的最左前缀原则b+tree 一直都只知道mysql索引的最左原则，今天分享下。 首先说说联合索引的好处： 覆盖索引，这一点是最重要的，重所周知非主键索引会先查到主键索引的值再从主键索引上拿到想要的值，这样多一次查询索引下推。但是覆盖索引可以直接在非主键索引上拿到相应的值，减少一次查询。 在一张大表中如果有 (a,b,c)联合索引就等于同时加上了 (a) (ab) (abc) 三个索引减少了存储上的一部分的开销和操作开销 梯度漏斗，比如 select *from t where
数据库索引失效的一种场景：分析问题的思路和策略
天涯兰的博客
12-29 2291
这是公司研发团队发现的一个关于数据库索引失效方面的问题，我们的工程师对该问题进行了分析和解决并写了这份小结。归根揭底还是对开发框架和技术应用的把握上存在纰漏，但个人觉得在分析问题->找出原因->确认解决方案这一思路和策略上本文能起到一定借鉴作用，所以稍微梳理了一下拿出来和大家分享。
MySQL索引失效：组合索引详解及其影响
本文档深入探讨了MySQL中索引失效的问题，特别是针对组合索引的分析。索引在关系型数据库中扮演着至关重要的角色，它是一种物理存储结构，用于对数据库表中的一列或多列数据进行排序，提供快速的数据检索能力，类似...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值