面试八股文--数据库基础知识总结(3)MySQL优化

目录

1、慢查询

Q1：在mysql中如何定位慢查询？

Q2：SQL语句执行很慢，如何分析？

2、索引

 Q3：什么是索引？

Q4：什么是聚簇索引和非聚簇索引？

Q5：什么是回表查询？

Q6：什么是覆盖索引？

 Q7：索引创建的原则有哪些？

Q8：什么情况下索引会失效

3、优化 

Q9：SQL优化的经验

4、事务

Q10：undo log和redo log的区别？

Q11：事务的隔离性是如何保证的呢？

---

1、慢查询

Q1：在mysql中如何定位慢查询？

慢查询表现为页面加载过慢、接口响应时间过长

• 开源工具：如Skywalking
• MySQL自带慢日志：记录了执行时间超过指定参数的SQL语句

Q2：SQL语句执行很慢，如何分析？

使用SQL的EXPLAIN语句

EXPLAIN SELECT * FROM `j_user_role` WHERE id=12

 type字段性能由好到坏：NULL、system、const（根据主键查询）、eq_ref（主键索引查询或唯一索引查询）、range（范围查询）、index（索引树扫描）、all（全盘扫描 select *）

key字段代表是否命中了索引，索引的名称

2、索引

 Q3：什么是索引？

索引（index）是帮助MySQL高效获取数据的数据结构。MySQL的索引底层实现是B+树，原因分析可以看我的另一篇博客：面试八股文--数据库基础知识总结(2) MySQL_dbms-CSDN博客

Q4：什么是聚簇索引和非聚簇索引？

聚簇索引：将数据存储与索引放到一块，索引结构的叶子节点保存了行数据（必须要、且只有一个！一般就是主键）

非聚簇索引（二级索引）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（可以有多个） 

Q5：什么是回表查询？

先通过二级索引找到对应的主键，再通过聚簇索引找到对应的数据，这个过程就是回表查询。

Q6：什么是覆盖索引？

是指查询使用了索引，并且需要返回的列在该索引中已经全部能找到。（触发了回表查询就不是覆盖索引了）

举个例子： id是主键，name也有索引

-- 是覆盖索引，因为数据在索引中能找到
SELECT * FROM `table` WHERE id=1
-- 是覆盖索引，因为name在索引中能找到
SELECT name FROM `table` WHERE name='Alice'
-- 不是覆盖索引，因为gender在name索引中找不到
SELECT name,gender FROM `table` WHERE name='Alice'

 Q7：索引创建的原则有哪些？

• 针对数据量较大，且查询比较频繁的表建立索引
• 表中的某一字段常作为查询条件、排序、分组操作的
• 尽量选择区分度高的列作为索引
• 进来使用联合索引、减少单列索引
• 要控制索引的数量，索引越多维护索引的代价也越大
• 如果数据列不能存储NULL时，最好在创建表时使用NOT NULL约束

Q8：什么情况下索引会失效

 （1）违反最左前缀法则

索引了多列，要从索引的最左前列开始，并且不跳过索引中的列。（也就是说，联合索引中查询只能从最左到最右，跳过就会失效）

（2）范围查询右边的列，索引失效

例如一个联合索引有name、status、address三个，当查询 name='value', status>'value', address='value'时，address的索引会失效，因为status使用了范围查询

（3）索引列上进行运算操作，索引失效（例如求字串、求平均）

（4）字符串不加单引号，索引会失效（发生了类型转换）

（5）模糊查询有可能导致索引失效

name=‘%aaa’或者name=‘%aaa%’会失效，而name=‘aaa%’则不会

3、优化 

Q9：SQL优化的经验

（1）表的设计优化：比如设置合适的类型

（2）索引的优化：如Q7、Q8

（3）SQL语句优化

• select知名字段名称，尽量避免select *
• 尽量避免索引失效
• 使用union all代替union，因为union会多一次过滤操作
• 避免在where中对字段进行表达式操作
• Join优化，能用inner join就不用left join、right jion

（4）主从复制、读写分离

4、事务

Q10：undo log和redo log的区别？

• redo log：记录了事务提交时数据页的物理修改，用来实现事务的持久性。主要用在刷新脏页到磁盘，发生错误时，进行数据恢复使用。
• undo log：回滚日志。记录数据被修改前的信息，作用有：提供回滚和MVCC。undo log记录的是逻辑日志
• redo log保证事务的持久性，undo log保证事务的原子性和一致性

Q11：事务的隔离性是如何保证的呢？

（1）锁：加入排他锁

（2）MVCC（多版本并发控制）：指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC的实现原理：记录中的隐藏字段（事务id和回滚指针）、undo log（回滚日志、版本链）、readview

ReadView（读视图）是读快照SQL执行时MVCC提取数据的依据。

• 当前读：读取的是记录的最新版本
• 快照读：读取的记录数据的可见版本（不加锁）

这个就导致了在不同事务隔离级别下读取数据的不同，在RC级别下，每次操作都会生成一个ReadView，因此可能导致“不可重复读问题”；而在RR隔离级别下，仅对事务中第一次执行快照时生成ReadView，后续复用该ReadView

---

 都看到这里了，给个小心心♥呗~