索引的使用

0. tips：

1. 本文章使用的是mysql 8.0.x，不同版本的优化器存在差异，对相同语句可能有优化升级。
2. 本文章涉及的索引还未更加深入，比如优化器关于全表扫描和索引使用的评估、关于各个索引间如何进行选择等。感兴趣的可以继续深入

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) 冰川白 联通3G手机3';

 上图是上述查询语句的查询结果信息，从执行的时间来看，该条语句的查询效率远低于使用 id字段的查询效率。主要原因是title 字段不是索引字段。

 1.3 解决上述SQL 语句查询慢的问题方法

为title 创建索引，建立该索引大概消耗时间在1分钟左右。主要时间的消耗在于建立B+ 树上。

 2. 索引的使用

注：以下的查询语句，使用的是tb_seller 表，该表同样是由黑马提供的，但是是可以自己进行创建的。该表在name，status，address三个字段上建立了联合索引。该表创建过程如下：

create table `tb_seller` (
    `sellerid` varchar (100),
    `name` varchar (100),
    `nickname` varchar (50),
    `password` varchar (60),
    `status` varchar (1), 
    `address` varchar (100),
    `createtime` datetime,
    primary key(`sellerid`)

)engine=innodb default charset=utf8mb4;


-- 插入数据
insert into `tb_seller` (`sellerid`, `name`, `nickname`, \
                         `password`, `status`, `adress`, \ 
                         `createtime`) values (`alibaba`, \
                         `阿里巴巴`, `阿里小店`, '123456', \
                         `1`, `北京市`, `2088-01-01 12:00:00`);

-- 建立索引
create index idx_seller_name_sta_addr on tb_seller(name, status, address);

插入数据https://download.csdn.net/download/qq_38560825/34935769https://download.csdn.net/download/qq_38560825/34935769

2.1 避免索引失效

1）全值匹配，对索引中所有的列都指定具体值，走索引

-- 全值匹配用到了一个索引的全部列进行条件查询
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';

解答：

        索引结构为name，status，address。而范围查询位于address，因此应该是使用了三个索引字段。原因是MySQL 优化器会对其进行优化，所以在SQL 语句中范围查询的位置不是很重要，而范围查询的字段才是关键。当然按索引顺序写会减少优化器的开销。

4）不要在索引列上进行运算操作，索引将会失效

-- 对name进行了运算，将不会走索引
explain select * from tb_seller where substring(name, 3, 2) = '科技';

5）字符串不加单引号，造成索引失效

注：原因是底层会对字符串进行隐式的（对上层透明）类型转换，即满足 4)

-- 会走索引，但是只会使用name，因为status是字符串，但没用单引号
explain select * from tb_seller where name = '小米科技' and status = 1;

6) 尽量使用覆盖索引，避免使用select *

注：尽量使用覆盖索引的意思是，只访问索引的查询，即select 后的字段均是索引中的字段。但是还是具体的业务需求。

7）用or 分割开的条件，就算or 前的条件中的列有索引，但是后面的列无索引，那么涉及的索引都不会被用到

注：就算使用覆盖索引，索引仍然失效。

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 ("阿里巴巴", "小米科技", "黑马程序员");

-- 走索引
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%';