一、myql索引底层数据结构与算法
二、mysql——Explain工具介绍
三、mysql——索引最佳实践
四、mysql索引优化实战
五、mysql——常见sql语句优化
六、mysql索引优化实战二
七、mysql事务隔离机制和锁机制
分页查询优化 不太好的分页查询写法:
SELECT * from employees limit 10000,10;
explain执行计划结果:
文章图片
分析:这条sql看似只查询了10条记录,实际上会读取10010条记录,然后抛弃前10000条,保留你需要的后10条,执行效率低。
优化方案: 1、根据自增且连续的主键排序的分页排序
注:该优化有两个前提:
(1)主键自增且连续
(2)结果是按照主键排序
否则优化后的sql执行结果与优化前不一致。
优化后的sql
SELECT * from employees where id>10000 limit 10;
执行计划结果:
文章图片
分析:优化后的sql利用了主键索引,而且扫描的行数大大减少,提高了执行效率。
根据非主键字段排序的分页查询 现实的业务场景中按照name排序的情况也很常见。
优化前的sql:
SELECT * from employees ORDER BY name limit 9000,5;
执行计划:
文章图片
分析:通过执行计划,可以看出这条sql没有使用任何索引,而且使用了filesort这种低效率的排序方式。
虽然name字段上有联合索引,但是mysql通过内部cost计算,发现使用走name索引比全表扫描效率还低。
优化方案: 让排序时返回的字段尽可能少,所以可以让排序和分页操作先查出主键,然后根据主键查到对应的记录。
优化后的sql,真的巧妙
SELECT * from employees e INNER JOIN (SELECT id from employees ORDER BY name LIMIT 9000,5) ed on e.id=ed.id;
执行计划:
文章图片
分析:这里没有使用filesort排序,而且可以看到走了索引,提高了执行效率。
join关联查询 注意:实际业务中尽量少用join这种方式,如果用最多也不要超过三个表进行join,后期没法优化。
建表语句
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
插入数据
‐‐ 插入一些示例数据
‐‐ 往t1表插入1万行记录
drop procedure if exists insert_t1;
delimiter ;
;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;
;
25 delimiter ;
call insert_t1();
‐‐ 往t2表插入100行记录
drop procedure if exists insert_t2;
delimiter ;
;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;
;
delimiter ;
call insert_t2();
mysql表关联的Nested-Loop Join算法与Block Nested-Loop Join算法 1、Nested-Loop Join ——嵌套循环连接算法
一次一行地从第一张表(驱动表)中读取行,在这行数据中取到关联字段,根据关联字段在另一张表中(被驱动表)里取出满足条件的行,然后取出两张表的结果集合。
EXPLAIN SELECT * from t1 INNER JOIN t2 on t1.a=t2.a
文章图片
分析:驱动表表是t2,被驱动表是t1;mysql优化的时候一般会选择**小表作为驱动表。**总共扫描了200行。
left join:规定了左边是驱动表;
right join:规定了右边是驱动表;
注:一般join语句,如果执行计划中没有出现“Using join buffer”则表示使用的join算法是NLJ。
2、基于快的嵌套循环连接 ——Block Nested-Loop Join算法
把驱动表的数据读取到join buffer中,然后扫描被驱动表,把被驱动表中的每一行取出来跟join buffer中的数据做对比.
EXPLAIN SELECT * from t1 INNER JOIN t2 on t1.b=t2.b
文章图片
分析:扫描的总行数:10000+100;以及在buffer中100万次比对。
join buffer的大小有参数join_buffer_size设定,默认值是256k,如果放不下,则分段存放。
优化方案: (1)关联字段加索引:让mysql做join时尽量选择NLJ算法;
(2)小表驱动达标,必要时可以使用straight join确定驱动表和被驱动表;
in和exsits优化 原则:小表驱动大表
in:当B表的数据小于A表的时候,in优于exists;
select * from A where id in (select id from B)
等价于:
for(select id from B){
select * from A where A.id = B.id
}
exists:当A表的数据集小于B表的数据集时,exists优于in
select * from A where exists (select 1 from B where B.id=A.id)
将主查询A的数据,放到子查询B中做条件验证,根据验证结果(true或false)来决定主查询的数据是否保留。
等价于:
select * from A where exists (select 1 from B where B.id=A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
count(*)查询优化 对比以下四种查询语句:
EXPLAIN SELECT * from t1 INNER JOIN t2 on t1.b=t2.bEXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
注意:以上4条sql只有根据某个字段count时,不会统计字段为null值的数据行。
count(1)和count(字段)执行过程类似,不过count(1)不用取出字段,所以快一些。
count(*)mysql并不会取出所有的字段,而是做了优化,效率有count(1)差不多。
count(id)本来应该走主键索引,但是mysql做了优化,如果有二级会走二级索引,因为二级索引数据量小,效率高。
优化方案: (1)对行数只需要知道个大概
show table status
(2) 将总数维护到redis中
但是很难保证表操作和redis操作的事物一致性
【二|六、mysql索引优化实战二】(3)增加数据计数器
推荐阅读
- Java|Java基础——数组
- 人工智能|干货!人体姿态估计与运动预测
- java简介|Java是什么(Java能用来干什么?)
- Java|规范的打印日志
- Linux|109 个实用 shell 脚本
- mysql|InnoDB数据页结构
- 数据库|SQL行转列方式优化查询性能实践
- 程序员|【高级Java架构师系统学习】毕业一年萌新的Java大厂面经,最新整理
- javaweb|基于Servlet+jsp+mysql开发javaWeb学生成绩管理系统
- 分析COMP122 The Caesar Cipher