Mysql SQL优化

目录

0 课程视频

1 插入数据的优化

1.1 插入多条 -> 批量插入-> 一次500-1000条

1.2 一次几万条 -> 多次批量

1.3 手动提交事务

1.4 主键顺序插入

1.5 几百万数据插入 -> load 指令

2 主键优化

2.1 课程视频

2.2 数据结构 -> 一段 = 64页 =64 *16k = 1M

2.3 乱序插入 -> 页分裂

2.4 页合并 -> 删除数据后被标记 ->默认阈值50%

3 主键设计原则

3.1 降低主键长度

3.2 使用自增主键

3.3 尽量不要使用 uuid/身份证 作为主键 -> 乱序插入 ->页分裂/过长

3.4 避免主键修改 -> 索引解构同时要改变

4 order by 优化

4.1 非索引排序 -> 低效

4.2 创建索引 排序 -> 高效

 4.3 索引-> 反向扫描排序 倒叙 -> order by age desc 

4.4 多字段联合索引 只能同时正序 同时倒叙

 4.5 解决多字段不同向 非索引排序问题 -> 创建索引

4.6 叶子节点数据结构图

 4.7 默认覆盖索引(示例 就是没有id name phone额外的查询字段 用name phone查)

 4.8 设置排序缓冲区大小 -> 默认256k ->满了在磁盘文件中排序->慢

5 group by 优化

5.1 课程视频

5.2 根据对应的字段设置索引

6  limit 优化

6.1 原理: 覆盖索引 -> 先查id + 子查询-> 再用id 查

7 count聚合函数优化->不取值最优

7.1 count(主键) ->遍历全表->取每行主键id->返回给服务层->累加(主键无null)

7.2 count(字段)->取每行字段->是否not null约束 -> 有则判断null ->服务层null不计数

7.3 count(1) ->不取值 ->每行返回都是1->直接累加

7.4 count(0) or count(n) 与 count(1)一样

7.5 count(*) -> InnoDB引擎优化 不取值 ->直接累加

8 update优化-> InnoDB引擎默认行锁->避免表锁

8.1 演示行锁 -> cmd 登录两遍mysql 开俩并发

8.2  非索引update/不符合索引使用 -> 表锁

---

0 课程视频

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=89&vd_source=ff8b7f852278821525f11666b36f180ahttps://www.bilibili.com/video/BV1Kr4y1i7ru?p=89&vd_source=ff8b7f852278821525f11666b36f180a

1 插入数据的优化

1.1 插入多条 -> 批量插入-> 一次500-1000条

insert into user values(1, 'tom'), (2, 'jack'), (3, 'lucy');

1.2 一次几万条 -> 多次批量

1.3 手动提交事务

start transaction;
insert into user values(1, 'tom'), (2, 'jack'), (3, 'lucy');
insert into ....
insert into  ....
commit;

1.4 主键顺序插入

1.5 几百万数据插入 -> load 指令

-- centos 操作
mysql --local-infile -u root -p //-- 连接数据库 --local-infile 表示加载文件
select @@local_infile ; //-- 查看权限有没有开启
set global local_infile=1 ; //-- 开启权限
use xxx ; //-- 选择数据库
操作 上传文件 -- 上传sql文本 user.sql
wc -l user.sql //-- 查看文本多少行
head user.sql; //-- 查看格式 用什么分隔字段 用什么换行
pwd //-- 查看路径

-- mysql 中 执行
load -- use xxx 就是xxx 库 -> 库中表 user -> 上面格式查看  ','逗号分隔字段 '\' 分隔行 
load data local infile 路径 into　table　user fields terminated by ',' lines terminated by '\n';
select count(*) from user; //-- 查看user行数
-- 100万 耗时17秒 
-- sql insert 10多分钟

2 主键优化

2.1 课程视频

https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=90&spm_id_from=pageDriver&vd_source=ff8b7f852278821525f11666b36f180a

2.2 数据结构 -> 一段 = 64页 =64 *16k = 1M

2.3 乱序插入 -> 页分裂

2.4 页合并 -> 删除数据后被标记 ->默认阈值50%

3 主键设计原则

3.1 降低主键长度

3.2 使用自增主键

3.3 尽量不要使用 uuid/身份证 作为主键 -> 乱序插入 ->页分裂/过长

3.4 避免主键修改 -> 索引解构同时要改变

4 order by 优化

4.1 非索引排序 -> 低效

4.2 创建索引 排序 -> 高效

 4.3 索引-> 反向扫描排序 倒叙 -> order by age desc 

4.4 多字段联合索引 只能同时正序 同时倒叙

 4.5 解决多字段不同向 非索引排序问题 -> 创建索引

create index idx_user_age_pho_ad on user(age asc , phone desc);

4.6 叶子节点数据结构图

 4.7 默认覆盖索引(示例 就是没有id name phone额外的查询字段 用name phone查)

 4.8 设置排序缓冲区大小 -> 默认256k ->满了在磁盘文件中排序->慢

show variables like 'sort_buffer_size';

5 group by 优化

5.1 课程视频

https://www.bilibili.com/video/BV1Kr4y1i7ru?p=92&vd_source=ff8b7f852278821525f11666b36f180a

5.2 根据对应的字段设置索引

6  limit 优化

6.1 原理: 覆盖索引 -> 先查id + 子查询-> 再用id 查

select id from user order by id limit 90000000,10 ; // 前十个

select * from user where id in () ;

-- 要用多表联查的形式组合

select xxx.* from user as xxx (select id from user order by id limit 90000000,10) as yyy where xxx.id = yyy.id ;

7 count聚合函数优化->不取值最优

7.1 count(主键) ->遍历全表->取每行主键id->返回给服务层->累加(主键无null)

7.2 count(字段)->取每行字段->是否not null约束 -> 有则判断null ->服务层null不计数

7.3 count(1) ->不取值 ->每行返回都是1->直接累加 和count(*)效率差不多

7.4 count(0) or count(n) 与 count(1)一样

7.5 count(*) -> InnoDB引擎优化 不取值 ->直接累加

8 update优化-> InnoDB引擎默认行锁->避免表锁

8.1 演示行锁 -> cmd 登录两遍mysql 开俩并发

-- 第一个客户端

begin; -- 开启事务

update user set name = 'jack' where id  = 1; // --行锁 先执行 可以执行成功


-- 第二个客户端

begin; -- 开启事务

update user set name = 'steve' where id  = 1; // 
--行锁 第一个客户端已经行锁 -> 这里等待状态-> 如果第一个客户端执行commit -> 可以执行成功修改

8.2  非索引update/不符合索引使用 -> 表锁

--- name 字段不是索引
--第一个客户端
update user set name = 'xxx' where name = 'yyy'; -> 表锁

--第二个客户端
对user表所有操作->阻塞 等待状态 第一个客户端commit 后 开始执行