「MySQL系列」索引设计原则、索引失效等常见场景优化

发布于 2022-4-22 21:12
浏览
0收藏

作者 |花花和Java
来源 | 花花和Java(ID:gh_02ed7983d1ec)

索引使用

概述

1. 定义

索引帮助MySQL高效获取数据的数据结构(按照一定规则)。

2. 定义解释

MySQL在存储数据之外,数据库系统还维护者满足特定查找算法的 数据结构,这些数据结构以某种方式引用(指向)数据, 这样就 可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

3. 优缺点

优点 提高数据检索效率,降低数据库IO成本。通过索引对数据进行排序降低数据排序成本,降低CPU消耗。缺点 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的。更新表时,MySQL 不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。

索引结构(InnoDB)

MySQL数据库中默认的存储引擎InnoDB的索引结构为B+树,而根据 叶子节点的内存存储不同,索引类型分为主键索引和非主键索引。

1. 主键索引(聚簇索引)

主键索引的叶子节点存储的是整行数据,其结构如下:「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

2. 非主键索引(二级索引或辅助索引)

而非主键索引的叶子节点内容存储时的主键的值,其结构如下:「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

 索引使用规则

没有建立索引,执行计划如下「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

建立索引

create index idx_seller_name_status_address on tb_seller(name, status, seller);

1. 全值匹配,对索引所有列都制定具体值

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

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

2. 最左前缀法制「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

违背最左法则,索引失效「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

如果符合最左法则,但是出现跳跃某一列,只有最左列索引生效:「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

3. 范围查询右边的列,不能使用索引「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

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

4. 索引列上进行运算操作,索引失效「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

5. 字符串不加单引号,造成索引失效「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区由于,在查询是,没有对字符串加单引号,MySQL的查询优化器, 会自动的进行类型转换,造成索引失效。

6. 用or分割开的条件

示例,name字段是索引列 , 而createtime不是索引列,中间是 or进行连接是不走索引的 :

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

7. 以%开头的Like模糊查询,索引失效。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区解决方案

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

8. 如果MySQL评估使用索引比全表更慢,则不使用索引「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

9. is NULL,is NOT NULL有时索引失效。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

10. in,not in有时索引失效「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

11. 尽量使用覆盖索引,避免select

尽量使用覆盖索引(只访问索引的查询(索引列完全包含查询列)),减少select。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

如果查询列,超出索引列,也会降低性能。

using index :使用覆盖索引的时候就会出现
using where:在查找使用索引的情况下,需要回表去查询所需的数据
using index condition:查找使用了索引,但是需要回表查询数据
using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要
回表查询数据

 索引设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考 虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。

对查询频次较高,且数据量比较大的表建立索引。索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。使用唯一索引,区分度越高,使用索引的效率越高。索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话,MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。利用最左前缀,N个列组合而成的组合索引,那么相当于是创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。

二 常见SQL优化

 数据库准备

sql

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`)
values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);

 order by优化

1. filesort 排序

第一种是通过对返回数据进行排序,也就是通常说的 filesort排 序,所有不是通过索引直接返回排序结果的排序都叫 FileSort排 序。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

2. using index

第二种通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区多字段排序
「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

了解了MySQL的排序方式,优化目标就清晰了:尽量减少额外的排 序,通过索引直接返回有序数据。where 条件和Order by 使用 相同的索引,并且Order By 的顺序和索引顺序相同, 并且 Order by 的字段都是升序,或者都是降序。否则肯定需要额外的 操作,这样就会出现FileSort。

对上面两种进行优化

通过创建合适的索引,能够减少 Filesort 的出现,但是在某些 情况下,条件限制不能让Filesort消失,那就需要加快Filesort 的排序操作。对于Filesort , MySQL 现在采用的是一次扫描算 法:一次性取出满足条件的所有字段,然后在排序区 sortbuffer 中排序后直接输出结果集。排序时内存开销较大,但是排序效率 比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小 和Query语句取出的字段总大小, 来判定是否那种排序算法,如 果max_length_for_sort_data 更大,那么使用第二种优化之后 的算法;否则使用第一种。

可以适当提高 sort_buffer_size max_length_for_sort_data 系统变量,来增大排序区的大小,提高排序的效率。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

 group by优化

由于GROUP BY 实际上也同样会进行排序操作,而且与ORDER BY 相比,GROUP BY 主要只是多了排序之后的分组操作。当然,如果 在分组的时候还使用了其他的一些聚合函数,那么还需要一些聚 合函数的计算。所以,在GROUP BY 的实现过程中,与 ORDER BY 一样也可以利用到索引。

如果查询包含 group by 但是用户想要避免排序结果的消耗, 则 可以执行order by null 禁止排序。如下 :

drop index idx_emp_age_salary on emp;
explain select age,count(*) from emp group by age;

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

优化后

explain select age,count(*) from emp group by age order by null;

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

从上面的例子可以看出,第一个SQL语句需要进行"filesort",而 第二个SQL由于order by null 不需要进行 "filesort", 而上 文提过Filesort往往非常耗费时间。

创建索引

create index idx_emp_age_salary on emp(age,salary);

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区 limit优化

一般分页查询时,通过创建覆盖索引能够比较好地提高性能。一 个常见又非常头疼的问题就是 limit5000000,10 ,此时需要 MySQL排序前5000010 记录,仅仅返回5000000 - 5000010 的记 录,其他记录丢弃,查询排序的代价非常大 。

limit分页操作, 越往后, 性能越低 :「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

优化方案

select * from tb_sku t , (select id from tb_sku order by id limit 9000000,1) a where t.id = a.id;

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区count优化

在很多的业务系统中,都需要考虑进行分页操作,但是当我们执 行分页操作时,都需要进行一次count操作,求取总记录数,如果 数据库表的数据量大,在InnoDB引擎中,执行count操作的性能是 比较低的,需要遍历全表数据,对计数进行累加。

优化方案

①. 在大数据量的查询中,只查询数据,而不展示总记录数 ;②. 通过缓存redis维护一个表的计数,来记录数据库表的总记录数,在执行插入/删除时,需要动态更新;③. 在数据库表中定义一个大数据量的计数表,在执行插入/删除时,需要动态更新。

大批量插入优化

1. 环境准备

https://mp.weixin.qq.com/s/h1VKdQ9JK5DTInSbsD-OmA#:~:text=CREATE%C2%A0TABLE%C2%A0%60tb_user,DEFAULT%C2%A0CHARSET%3Dutf8%C2%A0%3B

当使用 load 命令导入数据的时候,适当的设置可以提高导入的效率。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

对于InnoDB 类型的表,有以下几种方式可以提高导入的效率:

主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的,所以将导入的数 据按照主键的顺序排列,可以有效的提高导入数据的效率。如果 InnoDB表没有主键,那么系统会自动默认创建一个内部列作为主 键,所以如果可以给表创建一个主键,将可以利用这点,来提高 导入数据的效率。

脚本文件介绍 :
  sql1.log ----> 主键有序
  sql2.log ----> 主键无序

插入ID顺序排列数据:

load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

插入ID无序排列数据:「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0,关闭唯一性校验,在 导入结束后执行 SET UNIQUE_CHECKS=1,恢复唯一性校验,可以 提高导入的效率。「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

手动提交事务

如果应用使用自动提交的方式,建议在导入前执行 SET AUTOCOMMIT=0,关闭自动提交,导入结束后再执行 SET AUTOCOMMIT=1,打开自动提交,也可以提高导入的效率。

「MySQL系列」索引设计原则、索引失效等常见场景优化-开源基础软件社区

收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐