MYSQL进阶学习总结

---

一、MySQL底层原理

1.InnoDB使用B+树索引结构

2. InnoDB主键索引的B+树高度为多高

内存中一页为16KB,最多存1170个索引,B+tree的高度最多为3层,也就是二千多万个索引,超过三层则性能降低

3.B-树和B+树之间的区别

二、MySQL调优

（1）看什么？

1.查看执行频次：show global status
2.开启慢日志 – 修改配置文件
3.查看执行计划：explain

（2）怎么调？

1.insert操作：

• 在业务允许的条件下，尽量使用批量插入
• 如果一次性要插入大批量数据，使用insert性能较低，可以改为数据库提供的load指令进行插入
• 主键顺序插入性能高于乱序插入

2.主键优化：

• 主键要有序且递增最好，能防止添加数据时产生页分裂问题（单体主键自增，分布式id用雪花算法生成）
• 主键不要过长，过长会占用过多的磁盘空间，是的I0性能降低，怕。所以在满足业务需求的情况下，尽量降低主键的长度
• 插入数据时，尽量选择顺序插入，选择使用AUTOINCREMENT自增主键
• 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
• 业务操作时，避免对主键的修改

3.order By优化：

• 针对orderBy后面跟的排序字段，去加索引；多字段的话，就建复合索引

4.group By优化：

• 给分组的字段加索引，来提高效率

5.limit优化：

• 一般：可以通过创建覆盖索引+子查询优化
• 实际：通过产品涉及规避深度分页

6.count优化：
数据量小：

- count(主键）：把每一行主键的值拿出来，对主键进行累加--null值不参与统计
- count(字段)：把每一行的该字段的值拿出来，进行累加--null值不参与统计
- count(1)：不取值，每一行放一个数字“1”进去，进行累加
- count(*)：不会把全部字段取出来，直接按行进行累加

按效率排序：count(字段）< count(主键id) < count(1) ~ count(*)

数据量大：

自己在数据库建表维护，或者是用redis

7.update/delete优化
确保where 条件字段有索引，这样去进行修改或删除的时候，触发的是行锁。

三、索引失效

（1）最左前缀法则

ba：mysql会优化成ab,所以还是都走索引
ac：只有a索引,因为ac中间断掉了,所以不走c

（2）范围查询

• 使用联合索引时，要避免< > 的运算，因为一旦使用< >计算，会导致右侧列索引失效。
• 可以使用 >= <=优化mysql

（3）索引列运算

在索引列上进行运算操作，索引将失效

//索引都会失效
explain select * from tb_user where substring(phone,10,2) = ‘15’；
explain select * from tb_user where phone like '%15';
explain select *  from tb_user where phone like '---------15';

MySQL优化：

• 拿空间换时间,在service里面处理，但是jvm空间要足够大
  SELECT * FROM stu

• 使用es倒排索引解决MySQL的模糊查询问题

（4）字符串不加引号

（5）模糊查询

对索引列进行模糊查询，并且通配符写在左侧，会导致索引失效 – 使用es倒排索引解决

//失效
explain select * from user where username like '%ck%';
explain select * from user where username like '_ck';

（6）OR

使用or关键字连接的两端，只要有一侧没走索引，另一侧索引也会失效

//索引失效案例，通配符放左边索引失效
EXPLAIN SELECT * FROM user WHERE email like '%.cn' OR id =1;

（7）如果走索引效率低于全表扫描，那么mysq会选择放弃索引而使用全表扫描

//索引失效案例
EXPLAIN SELECT * FROM user WHERE email is not null

-索引未失效

四、索引的使用

五、索引设计原则

六、 SQL语句的编写

例子：统计不同薪资等级下的员工数量分别是多少,要求展示: 工资等级及对应的员工数量和该等级下员工的最高工资和对应的员工姓名
/*
1. 明确查询字段： salarygrade.grade , count(*) , max(salary), emp.ename
2. 明确要查询的表： salarygrade , emp
3. 关联条件： emp.salary between salarygrade.losalary and salarygrade.hisalary
4. 有没有主表：无
(内连接无主表, 外连接有主表)
5. 统计字段： salarygrade.grade
*/

SELECT t1.*, emp.ename 
FROM (SELECT salarygrade.grade ,  count(*) , max(salary) msalary
FROM salarygrade , emp
WHERE  emp.salary between salarygrade.losalary and salarygrade.hisalary
GROUP BY  salarygrade.grade) t1, emp
WHERE t1.msalary = emp.salary;