← 返回学习路线

SYSTEM

索引优化

Java基础索引优化system
src/content/notes/system/MySQL-索引优化.md

索引优化

创建索引

索引是数据库优化最重要的手段之一,通过索引通常可以帮助用户解决大多数的 MySQL 的性能优化问题

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`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(name, status, address); # 联合索引


避免失效

语句错误
  • 全值匹配:对索引中所有列都指定具体值,这种情况索引生效,执行效率高

    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1' AND address='西安市';

  • 最左前缀法则:联合索引遵守最左前缀法则

    匹配最左前缀法则,走索引:

    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技';
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status='1';

    违法最左前缀法则 , 索引失效:

    EXPLAIN SELECT * FROM tb_seller WHERE status='1';
    EXPLAIN SELECT * FROM tb_seller WHERE status='1' AND address='西安市';

    如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:

    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND address='西安市';

    虽然索引列失效,但是系统会使用了索引下推进行了优化

  • 范围查询右边的列,不能使用索引:

    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status>'1' AND address='西安市';

    根据前面的两个字段 name , status 查询是走索引的, 但是最后一个条件 address 没有用到索引,使用了索引下推

  • 在索引列上函数或者运算(+ - 数值)操作, 索引将失效:会破坏索引值的有序性

    EXPLAIN SELECT * FROM tb_seller WHERE SUBSTRING(name,3,2) = '科技';

  • 字符串不加单引号,造成索引失效:隐式类型转换,当字符串和数字比较时会把字符串转化为数字

    没有对字符串加单引号,查询优化器会调用 CAST 函数将 status 转换为 int 进行比较,造成索引失效

    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' AND status = 1;

    如果 status 是 int 类型,SQL 为 SELECT * FROM tb_seller WHERE status = '1' 并不会造成索引失效,因为会将 '1' 转换为 1,并不会对索引列产生操作

  • 多表连接查询时,如果两张表的字符集不同,会造成索引失效,因为会进行类型转换

    解决方法:CONVERT 函数是加在输入参数上、修改表的字符集

  • 用 OR 分割条件,索引失效,导致全表查询:

    OR 前的条件中的列有索引而后面的列中没有索引或 OR 前后两个列是同一个复合索引,都造成索引失效

    EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' OR createtime = '2088-01-01 12:00:00';
    EXPLAIN SELECT * FROM tb_seller WHERE name='小米科技' OR status='1';

    AND 分割的条件不影响:

    EXPLAIN SELECT * FROM tb_seller WHERE name='阿里巴巴' AND createtime = '2088-01-01 12:00:00';

  • 以 % 开头的 LIKE 模糊查询,索引失效:

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

    EXPLAIN SELECT * FROM tb_seller WHERE name like '%科技%';

    解决方案:通过覆盖索引来解决

    EXPLAIN SELECT sellerid,name,status FROM tb_seller WHERE name like '%科技%';

    原因:在覆盖索引的这棵 B+ 数上只需要进行 like 的匹配,或者是基于覆盖索引查询再进行 WHERE 的判断就可以获得结果


系统优化

系统优化为全表扫描:

  • 如果 MySQL 评估使用索引比全表更慢,则不使用索引,索引失效:

    CREATE INDEX idx_address ON tb_seller(address);
    EXPLAIN SELECT * FROM tb_seller WHERE address='西安市';
    EXPLAIN SELECT * FROM tb_seller WHERE address='北京市';

    北京市的键值占 9/10(区分度低),所以优化为全表扫描,type = ALL

  • IS NULL、IS NOT NULL 有时索引失效:

    EXPLAIN SELECT * FROM tb_seller WHERE name IS NULL;
    EXPLAIN SELECT * FROM tb_seller WHERE name IS NOT NULL;

    NOT NULL 失效的原因是 name 列全部不是 null,优化为全表扫描,当 NULL 过多时,IS NULL 失效

  • IN 肯定会走索引,但是当 IN 的取值范围较大时会导致索引失效,走全表扫描:

    EXPLAIN SELECT * FROM tb_seller WHERE sellerId IN ('alibaba','huawei');-- 都走索引
    EXPLAIN SELECT * FROM tb_seller WHERE sellerId NOT IN ('alibaba','huawei');
  • MySQL 实战 45 讲该章节最后提出了一种慢查询场景,获取到数据以后 Server 层还会做判断


底层原理

索引失效一般是针对联合索引,联合索引一般由几个字段组成,排序方式是先按照第一个字段进行排序,然后排序第二个,依此类推,图示(a, b)索引,a 相等的情况下 b 是有序的

  • 最左前缀法则:当不匹配前面的字段的时候,后面的字段都是无序的。这种无序不仅体现在叶子节点,也会导致查询时扫描的非叶子节点也是无序的,因为索引树相当于忽略的第一个字段,就无法使用二分查找

  • 范围查询右边的列,不能使用索引,比如语句: WHERE a > 1 AND b = 1 ,在 a 大于 1 的时候,b 是无序的,a > 1 是扫描时有序的,但是找到以后进行寻找 b 时,索引树就不是有序的了

  • 以 % 开头的 LIKE 模糊查询,索引失效,比如语句:WHERE a LIKE '%d',前面的不确定,导致不符合最左匹配,直接去索引中搜索以 d 结尾的节点,所以没有顺序

参考文章:https://mp.weixin.qq.com/s/B_M09dzLe9w7cT46rdGIeQ


查看索引

SHOW STATUS LIKE 'Handler_read%';	
SHOW GLOBAL STATUS LIKE 'Handler_read%';

  • Handler_read_first:索引中第一条被读的次数,如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)

  • Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,值越低表示索引不经常使用(这个值越高越好)

  • Handler_read_next:按照键顺序读下一行的请求数,如果范围约束或执行索引扫描来查询索引列,值增加

  • Handler_read_prev:按照键顺序读前一行的请求数,该读方法主要用于优化 ORDER BY … DESC

  • Handler_read_rnd:根据固定位置读一行的请求数,如果执行大量查询并对结果进行排序则该值较高,可能是使用了大量需要 MySQL 扫描整个表的查询或连接,这个值较高意味着运行效率低,应该建立索引来解决

  • Handler_read_rnd_next:在数据文件中读下一行的请求数,如果正进行大量的表扫描,该值较高,说明表索引不正确或写入的查询没有利用索引