索引的使用

最新推荐文章于 2022-06-08 18:17:17 发布
最新推荐文章于 2022-06-08 18:17:17 发布
阅读量540 收藏 4
点赞数
分类专栏： Mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/sinat_28317385/article/details/109577550
版权

文章目录

建表sql

CREATE TABLE staffs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',
  age INT NOT NULL DEFAULT 0 COMMENT '年龄',
  pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
  add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
 
 
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
 
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

(案例)索引失效

全值匹配我最爱。
最佳左前缀法则。
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描。
索引中范围条件右边的字段会全部失效。
尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。
MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。
is not null无法使用索引,但是is null是可以使用索引的
like以通配符开头%abc索引失效会变成全表扫描。
字符串不加单引号索引失效。
少用or，用它来连接时会索引失效。

全值匹配我最爱。

  • 索引 idx_staffs_nameAgePos 建立索引时 以 name ，age，pos 的顺序建立的。全值匹配表示 按顺序匹配的
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

在这里插入图片描述
Using index condition指的是查找使用了索引，但是需要回表查询数据

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

在这里插入图片描述

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

在这里插入图片描述

最佳左前缀法则

  • and忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。
EXPLAIN SELECT * FROM staffs WHERE age = 25 AND name = 'July';

在这里插入图片描述

  • 当使用覆盖索引的方式时，(select name/age/pos/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以name开头，也会使用idx_nameAge索引。
EXPLAIN SELECT id FROM staffs WHERE age = 25

在这里插入图片描述

EXPLAIN SELECT pos FROM staffs WHERE age = 25

在这里插入图片描述

  • 既select后的字段 有索引，where 后的字段也有索引，则无关执行顺序。
  • 除开上述条件 才满足最左前缀法则。
EXPLAIN SELECT * FROM staffs WHERE age = 25  #没用到索引

在这里插入图片描述

EXPLAIN SELECT * FROM staffs WHERE name = 'July' and age = 25 # 用到索引

在这里插入图片描述

  • 左前缀"失效"的情况
    有种情况可能会忽略，当表的字段比较少(只有3,4个左右)，恰好建索引把所有的字段包含进去了，select *会走覆盖索引的规则
特殊情况
EXPLAIN select * from staffs where name > '33'

在这里插入图片描述

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM staffs WHERE left(name,4)='July'

在这里插入图片描述

索引中范围条件右边的字段会全部失效

EXPLAIN SELECT * FROM staffs WHERE name = 'July' and age=14 and pos = 'dev'

在这里插入图片描述

EXPLAIN SELECT * FROM staffs WHERE name = 'July' and age>14 and pos = 'dev'

在这里插入图片描述
说明没用到pos字段的索引

尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *

EXPLAIN select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'

在这里插入图片描述

EXPLAIN select * from staffs where name = 'July' and age = 25 and pos = 'dev'

在这里插入图片描述

EXPLAIN select name,age,pos from staffs where name = 'July' and age > 25 and pos = 'dev'

在这里插入图片描述

EXPLAIN select name,age,pos from staffs where name = 'July' and age = 25

在这里插入图片描述

EXPLAIN select name from staffs where name = 'July' and age = 25

在这里插入图片描述

EXPLAIN select pos from staffs where  age = 25

在这里插入图片描述

MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描

EXPLAIN select * from staffs where  name <> 'July'

在这里插入图片描述

EXPLAIN select * from staffs where  name = 'July' and age !=1

在这里插入图片描述

EXPLAIN select * from staffs where  name = 'July' and age =1 and pos<>'dev'

在这里插入图片描述

is not null无法使用索引,但是is null是可以使用索引的

EXPLAIN select * from staffs where  name is null

在这里插入图片描述

EXPLAIN select * from staffs where  name is null and age =1 and pos='dev'

在这里插入图片描述

EXPLAIN select * from staffs where  name is not NULL

在这里插入图片描述

EXPLAIN select * from staffs where name = 'July' and age = 1 and pos is not null

在这里插入图片描述
上面有个问题不太懂，type的类型不知道为啥变成range，希望有懂的朋友在评论区说一下

like以通配符开头%abc索引失效会变成全表扫描。

EXPLAIN select * from staffs where name like '%July%'

在这里插入图片描述

EXPLAIN select * from staffs where name like '%July'

在这里插入图片描述

EXPLAIN select * from staffs where name like 'July%'

like ‘abc%’ type 类型为 range ，算是范围，可以使用索引
在这里插入图片描述

EXPLAIN select * from staffs where name like 'July%' and age=1

在这里插入图片描述

问题:解决like’%字符串%'时索引不被使用的方法

  • 如果一定要使用%like，而且还要保证索引不失效，那么使用覆盖索引来编写SQL

字符串不加单引号索引失效

底层隐式转换导致索引失效

EXPLAIN select * from staffs where name = 1

在这里插入图片描述

EXPLAIN select * from staffs where name = '1'

在这里插入图片描述

少用or，用它来连接时会索引失效

EXPLAIN select * from staffs where name = '1' or name = 'July'

在这里插入图片描述

EXPLAIN select * from staffs where name = 'July' and age = 11 or age = 23

在这里插入图片描述

EXPLAIN select pos from staffs where name = 'July' and age = 11 or age = 23 # 覆盖索引

在这里插入图片描述

热身case

  • 建表语句
create table test03(
 id int primary key not null auto_increment,
 c1 char(10),
 c2 char(10),
 c3 char(10),
 c4 char(10),
 c5 char(10)
);
 
insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
 
select * from test03;
 
【建索引】
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
show index from test03;

例子

explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

在这里插入图片描述

 explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';

在这里插入图片描述

 explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';

在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;

c3作用在排序而不是查找
在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' order by c3;

在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' order by c4;

出现了filesort
在这里插入图片描述

explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;

只用c1一个字段索引，但是c2、c3用于排序,无filesort
在这里插入图片描述

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了
在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;

在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;

用c1、c2两个字段索引，但是c2、c3用于排序,无filesort
在这里插入图片描述

explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;

因为c2为常量，所以order by后面不会出现filesort
在这里插入图片描述

explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

在这里插入图片描述

explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;

在这里插入图片描述

explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;

在这里插入图片描述

一般性建议

  • 对于单值索引，尽量选择针对当前query过滤性更好的索引。
  • 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
  • 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

口诀：

  • 带头大哥不能死。
  • 中间兄弟不能断。
  • 索引列上不计算。
  • 范围之后全失效。
  • 覆盖索引尽量用。
  • 不等有时会失效。
  • like百分加右边。
  • 字符要加单引号。
  • 一般SQL少用or。
安澜仙王
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
mysql之不走索引和sql优化
qq_42488996的博客
11-16 857
一. 不走索引的情况 使用 != 和 <> 不等于符号查询 使用 not in、 not exists 查询 使用like后缀查询，如’%string’ 在建立索引和没有索引的字段上使用or，会导致查询不走索引 使用>、 <、 <=、 >= 符号，可能不走索引，这个要看优化器的判断 字符串类型字段使用数字进行比较，不走索引，如 (varchar(200) name) name=123 在索引字段上计算 在索引字段行使用函数操作 二. 优化 or语句使用union替代
mysql 索引 大于等于 走不走索引 最左前缀
热门推荐
Jesse
12-30 4万+
你可以认为联合索引是闯关游戏的设计例如你这个联合索引是state/city/zipCode那么state就是第一关 city是第二关， zipCode就是第三关你必须匹配了第一关，才能匹配第二关，匹配了第一关和第二关，才能匹配第三关你不能直接到第二关的索引的格式就是第一层是state，第二层才是city上面举例介绍来源 https://zhidao.baidu.com/question/1863
mysql中or是否走索引详解
08-11
NULL 博文链接：https://bugyun.iteye.com/blog/2242094
索引的使用说明
weixin_33937499的博客
07-04 101
索引的使用说明 题外话：     昨天比较郁闷，维护一客户的数据库系统时候，发现系统处理数据就出现假死。开始因为是软件问题，拷得乱忙一下午还没有拷定，后来一直弄到晚上7点，突然想起索引问题。后来就使用了一命令： dbcc dbreindex('TableName','',80) 即可实现。比较郁闷和晕，自己犯这样的错误。     这错误，正好是我想写这帖的原因，希望能为大家解...
----------------索引的使用----------------
fredrickhu的专栏
10-18 4766
A. 使用简单索引下面的示例为 authors 表的 au_id 列创建索引。SET NOCOUNT OFFUSE pubsIF EXISTS (SELECT name FROM sysindexes       WHERE name = au_id_ind)   DROP INDEX authors.au_id_indGOUSE pubsCREATE INDEX au_id_ind   ON
MongoDB索引使用详解
12-16
索引就像书的目录，如果查找某内容在没有目录的帮助下，只能全篇查找翻阅，这导致效率...可以使用show collections/tables查看数据库为空。 然后在mongodb命令行终端执行如下代码 > for(var i=0;i<100000;i++) { ..
MySql索引使用策略分析
12-14
4.建立索引,在查询中使用索引 可以提高性能 索引缺点 1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加 2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间 3....
Oracle 监控索引使用率脚本分享
09-10
主要介绍了Oracle 监控索引使用率脚本分享,本文给出的脚本将得到索引的使用率，可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进,需要的朋友可以参考下
mysql索引使用技巧及注意事项
09-09
本篇文章主要介绍了mysql索引使用技巧及注意事项，小编觉得挺不错的，现在分享给大家，也给大家做个参考。一起跟随小编过来看看吧
mysql关于or的索引问题
weixin_30553837的博客
01-28 292
摘自： http://www.educity.cn/wenda/590849.html http://blog.csdn.net/hguisu/article/details/7106159 问： 不是说，一条sql语句只能用一个索引么 但SELECT * FROM `comment` WHERE `toconuid` = '10' or `tocomuid` = '10' 其中 to...
关于索引的详细使用
Klaus_originals的博客
06-08 4916
1、索引介绍索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，与在表中搜索的所有行相比，索引有助于更快地获取信息2、索引分类（1）聚簇索引与数据存放一起，与数据的存放顺序一致，主键，没有主键使用唯一键，没有唯一键使用隐藏字段rowid（2）非聚簇索引（二级索引，辅助索引）除聚簇索引外的其他索引，存放索引数据，并引用聚簇索引；查找到数据后，先跳到聚簇索引，再回到表查询（3）联合索引（复合索引）多个字段组合的索引3、B+Tree 4、索引语法（1）创建索引CREATE[
mysql复合(组合)索引失效的几种情况总结（未使用索引、索引中断、范围查询、否定条件、模糊查询、使用函数、类型转换及“key_len”的说明）
whl8614john的博客
09-13 4266
复合(组合)索引失效的几种情况总结 创建一张测试表：emp 表 CREATE TABLE emp ( id INT (11) NOT NULL AUTO_INCREMENT, empno INT NOT NULL, NAME VARCHAR (30) DEFAULT NULL, age INT (13) DEFAULT NULL, deptId INT (11) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE = INNODB AUTO_INCREME
Mysql索引原理
weixin_33755554的博客
06-12 120
目录为什么要有索引索引原理磁盘IO与预读索引的数据结构b+树的查找过程b+树性质MySQL的索引分类索引的两大类型hash与btree添加索引，必须遵循原则慢查询优化的基本步骤欢迎关注笔者，优质文章都在这里等你。一、为什么要有索引索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化...
mysql的索引使用
最新发布
08-31
下面是关于 MySQL 索引使用的一些常见问题和注意事项： 1. 什么是索引？ 索引是按照一定的规则对表中的数据进行排序和组织的数据结构，以加快对数据的检索速度。它类似于书籍的目录，可以根据关键字快速找到所需的...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

安澜仙王

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值