-- 全值匹配用到了一个索引的全部列进行条件查询
select * from tb_seller where name="小米科技" and status="1" and address="北京市";
-- 使用explain 查看上述sql的执行计划
explain select * from tb_seller where name="小米科技" and status="1" and address="北京市";
2)最左前缀法则,走索引。
-- 在进行条件匹配时,必须从一个索引的最左边的列开始,并且不能跳过中间的列
-- 具体举例如下:(之前建立的索引中列的顺序,name、status、address)
-- 1. 以name 作为条件进行查询,符合该法则,走索引
explain select * from tb_seller where name="小米科技";
-- 2. 以name, status 作为条件进行查询,符合该法则,走索引
explain select * from tb_seller where name="小米科技" and status="1";
-- 3. 以name, address 作为条件进行查询,不符合该法则(跳过了status),但是会走索引,仅以name为索引
explain select * from tb_seller where name="小米科技" and address="北京";
-- 4. 以status, name 作为条件进行查询,符合该法则(没有跳过某列),走索引
explain select * from tb_seller where status="1" and name="小米科技";
-- 5. 以 status,address 作为条件进行查询,不符合该法则(跳过了name),不走索引,最左侧都不存在
explain select * from tb_seller where status="1" and address="北京";
tips:
前提:数据库存在索引I,语句中匹配条件字段集合为S ,存在子集T (T 由S 中所有属于
索引I 的字段组成)。设定集合 ,其中 set 表示其中
的字段是无序的,n 为 |T|。此处是set的原因是:MySQL 优化器会对查询进行优
化,从而顺序并不是很重要。
最左前缀法则:当且仅当,存在 不为 ,且 ,执行语句才会走索引;否则
将不会走索引。特别的是,就算 的补集中存在索引 I 中的字段,只要满足
最左侧法则的条件,也会走索引,走索引只是走最左侧的那部分字段子集。
3)范围查询右边的列,不能使用索引
注:这里的右边应该是在索引中的位于范围查询字段右边的字段(列)
-- 走索引,但是只走name, status。不会走address,因为address 在索引中位于范围查询列status的右边
explain select * from tb_seller where name='小米科技' and status > '1' and address='北京';
拓展:
下述的SQL 语句的索引使用情况?
explain select * from tb_seller where name='小米科技' and address > '北京' and status = '1';
explain select * from tb_seller where name = "阿里巴巴" or nickname="阿里小店";
8)以% 开头的like 模糊查询,索引失效
注:以%开头的模糊查询都会失效,但是 % 只出现在后面的模糊查询不会失效
但是前提是select 出的字段有非索引,如果select 出的字段仅仅包含索引字段,则会走索引
上述的方法即能解决以% 开头的like模糊查询,索引失效的问题,该方法就是上述的覆盖索引
-- 索引失效
explain select * from tb_seller where name like "%科技";
-- 索引失效
explain select * from tb_seller where name like "%科技%";
-- 使用索引
explain select * from tb_seller where name like "科技%";
-- 使用索引,使用的是联合索引(优化器决定的,认为主键索引比联合索引慢)
explain select sellerid from tb_seller where name like "科技%";
-- 使用索引,使用的是联合索引(优化器决定的,认为主键索引比联合索引慢)
explain select sellerid from tb_seller where name like "%科技";
9)如果MySQL 评估使用索引比全表慢,则索引失效
注:这个评估过程应该是由优化器执行的,评估的指标应该是数据的比例
在这个例子中需要建立address 的单列索引,否则必不会走索引(最左前缀原则)
-- 索引失效,因为数据中大部分数据都是 '北京市',优化器评估全表扫描快于索引
explain select * from tb_seller where address = '北京市';
-- 走索引,因为数据中只有极少的数据是 '西安市',优化器评估索引更快
explain select * from tb_seller where address = '西安市';
10)is NULL,is NOT NULL 有时索引失效
注:其实原理和9)相似
11)in,not in。in 走索引,not in 索引失效
注:当使用in 时,也可能会有全表扫描或者走索引的判定。
纠正:
黑马教学视频中说not in 索引失效,但是在实际操作过程中发现是走索引的
但是当执行的是以下命令时,11)所述才符合实际
explain select * from tb_seller where name in ("阿里巴巴", "小米科技", "黑马程序员");
explain select * from tb_seller where name not in ("阿里巴巴", "小米科技", "黑马程序员");
使用name 进行 in 判断操作的SQL执行计划图
使用name 进行 not in 判断操作的SQL执行计划图
-- 走索引
explain select * from tb_seller where sellerid in ("alibaba", "oppo", "xiaomi", "sina");
-- 不走索引
explain select * from tb_seller where sellerid not in ("alibaba", "oppo", "xiaomi", "sina");
12)单列索引和复合索引选择,尽量使用复合索引,而少使用单列索引。
注:进行查询时,数据库底层优化器会选择一个最优的索引(辨识度最高)来使用,并不会使用全部索引
2.2 查看索引使用情况
-- 查看本次连接的使用情况
show status like 'Handler_read%';
-- 查看该表的全部的索引情况
show global status like 'Handler_read%';
1. 验证索引提升查询效率注:以下的查询语句基于tb_item 表,由黑马提供。本文不提供具体的数据和数据链接。1.1 根据 id 查询select * from tb_item where id = 1999\G;上图是上述查询语句的查询结果信息,从执行的时间来看,该条语句的查询效率很高。主要原因是id 是主键,有主键索引。1.2 根据title 字段进行查询select * from tb_item where title = '阿卡尔特 (OT-979) 冰川白 联通.