mysql索引使用

准备环境

create index idx_seller_name_sta_addr on tb_seller(name,status,address);

1).全值匹配效率最高

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'

2). 最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。
示例

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
--走索引
explain select * from tb_seller where name='小米科技' and status='1';
-- 走索引
explain select * from tb_seller where name='小米科技' and address='北京市';
-- 走了name索引,但是address索引没走,失效了 如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:
explain select * from tb_seller where status='1' and address='北京市';
-- 不走索引
explain select * from tb_seller where status='1' and address='北京市' and name='小米科技';
-- 走索引,和where子句中的顺序无关

类似爬楼梯,上了一层才能上下一层

3). 范围查询右边的列,不能使用索引 。

explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';
--只走name和status索引,范围查询后面的索引失效

4). 在索引列上进行运算操作, 当前列和后面的索引将失效。

explain select * from tb_seller where substring(name,3,2) = '科技' and status='1' and address='北京市';

explain select * from tb_seller where substring(name,3,2) = '科技'
-- 二者都不走索引

5). 字符串不加单引号,造成索引失效。
数字类型的字符串不加单引号也能查出来,但是当前列和当前列后面的索引会失效,因为底层会判断他是字符串类型而进行隐式类型转换,也就是运算操作,所以索引会失效

explain select * from tb_seller where name='小米科技' and status=1 and address='北京市';
-- 只走了name索引

6). 尽量使用覆盖索引,避免select * 尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select * ,减少回表操作。
mysql索引使用

EXPLAIN select sellerid,name,status,address from tb_seller where name = '小米科技'
-- 例如这里查询复合索引列和主键列都在一颗树上,无需回表操作,但在加了其他字段之后就需要回表操作,效率较低

mysql索引使用

7). 用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。

示例,name字段是索引列 , 而createtime不是索引列,中间是or进行连接是不走索引的,交换顺序也一样 :

explain select * from tb_seller where name='小米科技' or createtime = '2088-01-01 12:00:00';
-- 不走索引

8). 以%开头的Like模糊查询,索引失效。

如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。

explain select * from tb_seller where name like '%米科技';

9). 如果MySQL评估使用索引比全表更慢,则不使用索引。

explain select * from tb_seller where address='北京市';
-- 不走索引
explain select * from tb_seller where address='西安市';
-- 走索引

数据库中大多数都是北京市索引,走索引不如直接全表扫描,但是数据库扫描之前怎么知道大多数都是北京呢?
10). is NULL , is NOT NULL 有时索引失效。

explain select * from tb_seller where address is NULL;-- 走索引

explain select * from tb_seller where address is not NULL;-- 不走索引

因为表中大多数数据都是 not null,所以走索引不如全表扫描,,反之亦然,根据具体情况分析,null比较多则null不走索引,not null走

11). in 走索引, not in 索引失效。
12). 单列索引和复合索引。

尽量使用复合索引,而少使用单列索引 。
create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 :
name
name + status
name + status + address
使用单列索引则数据库会选择一个最优的索引(辨识度最高索引)来使用,并不会使用全部索引 。

上一篇:Elasticsearch7.15.2 ik中文分词器 定制化分词器之扩展词库


下一篇:索引必备使用方法