MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MYSQL性能调优05_覆盖索引、索引下推、如何选择合适的索引、Order by与Group by优化、索引设计原则(一)

①. 坏境准备


CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());
-- 插入一些示例数据
drop procedure if exists insert_emp; 
delimiter ;;
create procedure insert_emp()        
begin
  declare i int;                    
  set i=1;                          
  while(i<=100000)do                 
    insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');  
    set i=i+1;                       
  end while;
end;;
delimiter ;
call insert_emp();


②. 覆盖索引、索引下推


  • ①. 联合索引第一个字段用范围不会走索引
    (结论:联合索引第一个字段就用范围查找不会走索引,mysql内部可能觉得第一个字段就用范围,结果集应该很大,回表效率不高,还不如就全表扫描)


EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager'


微信图片_20220109125523.png


②. 强制走索引

结论:虽然使用了强制走索引让联合索引第一个字段范围查找也走索引,扫描的行rows看上去也少了点,但是最终查找效率不一定比全表扫描高,因为回表效率不高


EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';


微信图片_20220109125601.png


-- 做了一个小实验:
-- 关闭查询缓存
set global query_cache_size=0;  
set global query_cache_type=0;
-- 执行时间0.333s
SELECT * FROM employees WHERE name > 'LiLei';
-- 执行时间0.444s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';


③. 覆盖索引优化


EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';


微信图片_20220109125648.png


④. in和or在表数据量比较大的情况会走索引,在表记录不多的情况下会选择全表扫描


EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';


微信图片_20220109125709.png


-- 做一个小实验,将employees 表复制一张employees_copy的表,里面保留两三条记录
EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';


⑤. like KK% 一般情况都会走索引


EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE name like 'LiLei%' AND age = 22 AND position ='manager';



微信图片_20220109125742.png


⑥. 这里给大家补充一个概念,索引下推(Index Condition Pushdown,ICP), like KK%其实就是用到了索引下推优化,什么是索引下推了?


补充说明:


(1). 对于辅助的联合索引(name,age,position),正常情况按照最左前缀原则,SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position

=‘manager’ 这种情况只会走name字段索引,因为根据name字段过滤完,得到的索引行里的age和position是无序的,无法很好的利用索引。


(2). 在MySQL5.6之前的版本,这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引,然后拿这些索引对应的主键逐个回表,到主键索引上找出相应的记录,再比对age和position这两个字段的值是否符合


(3). MySQL 5.6引入了索引下推优化,可以在索引遍历过程中,对索引中包含的所有字段先做判断,过滤掉不符合条件的记录之后再回表,可以有效的减少回表次数。使用了索引下推优化后,上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后,同时还会在索引里过滤age和position这两个字段,拿着过滤完剩下的索引对应的主键id再回表查整行数据


(4). 索引下推会减少回表次数,对于innodb引擎的表索引下推只能用于二级索引,innodb的主键索引(聚簇索引)树叶子节点上保存的是全行数据,所以这个时候索引下推并不会起到减少查询全行数据的效果。


⑦. 为什么范围查找Mysql没有用索引下推优化?

估计应该是Mysql认为范围查找过滤的结果集过大,like KK% 在绝大多数情况来看,过滤后的结果集比较小,所以这里Mysql选择给 like KK% 用了索引下推优化,当然这也不是绝对的,有时like KK% 也不一定就会走索引下推


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
算法 关系型数据库 MySQL
MySQL (索引 & 事务)
MySQL (索引 & 事务)
11 3
|
2天前
|
存储 关系型数据库 MySQL
MySQL索引
MySQL索引
8 0
|
2天前
|
SQL 算法 关系型数据库
【MySQL】索引介绍、索引的数据结构
【MySQL】索引介绍、索引的数据结构
16 0
|
1天前
|
SQL Java 关系型数据库
Mysql 数据库基本操作
Mysql 数据库基本操作
|
5天前
|
关系型数据库 分布式数据库 数据库
【阿里云云原生专栏】云原生时代的数据库选型:阿里云RDS与PolarDB对比分析
【5月更文挑战第24天】阿里云提供RDS和PolarDB两种数据库服务。RDS是高性能的在线关系型数据库,支持MySQL等引擎,适合中小规模需求;而PolarDB是分布式数据库,具备高扩展性和性能,适用于大规模数据和高并发场景。RDS与PolarDB在架构、性能、弹性伸缩、成本等方面存在差异,开发者应根据具体需求选择。示例代码展示了如何通过CLI创建RDS和PolarDB实例。
308 0
|
6天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
38 2
|
7天前
|
存储 数据可视化 关系型数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
【MySQL进阶之路 | 基础篇】创建和管理数据库
|
7天前
|
存储 SQL 关系型数据库
【MySQL】数据库基础 -- 详解
【MySQL】数据库基础 -- 详解
|
7天前
|
关系型数据库 MySQL 数据库
如何在MySQL中查看已创建的数据库列表?
【5月更文挑战第22天】如何在MySQL中查看已创建的数据库列表?
18 1
|
9天前
|
存储 关系型数据库 MySQL
MySQL中数据库的相关操作
MySQL中数据库的相关操作
232 62