面试必问MySQL：执行流程、树、索引、优化、数据结构一网打尽

最新推荐文章于 2024-02-16 00:36:22 发布

Java架构师联盟

最新推荐文章于 2024-02-16 00:36:22 发布

阅读量184

点赞数

分类专栏： java mysql 文章标签：数据库二叉树数据结构 mysql java

git地址：https://github.com/biws-byte/mxq

本文链接：https://blog.csdn.net/weixin_42864905/article/details/107825406

版权

java 同时被 2 个专栏收录

457 篇文章 6 订阅

订阅专栏

mysql

23 篇文章 0 订阅

订阅专栏

SQL优化背景

开发项目上线初期，由于业务数据量相对较少，一些SQL的执行效率对程序运行效率的影响不太明显，而开发和运维人员也无法判断SQL对程序的运行效率有多大，故很少针对SQL进行专门的优化，而随着时间的积累，业务数据量的增多，SQL的执行效率对程序的运行效率的影响逐渐增大，此时对SQL的优化就很有必要。

SQL优化发生在业务量达到一定规模的时候目的是优化SQL的执行效率

除了这些之外，sql的优化也是一个可以引申出来其他相当多的内容，不信，来看看

MySQL 优化

优化范围

硬件资源
操作系统参数，数据库参数配置
SQL语句，索引优化

SQL优化

数据库设计优化【规范，前期设计】
SQL语句优化
索引优化
读写分离，分库分表

慢查询语句

慢查询：10s无返回结果，定义为慢查询

SHOW STATUS LIKE "slow_queries";

SHOW VARIABLES LIKE "long_query_time";//可以显示当前慢查询时间

set long_query_time=1 ;//可以修改慢查询时间

常用优化方法

查询优化

避免全表扫描（考虑在 where 及 order by 涉及的列上建立索引）
尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描select id from t where num=10 or num=20 可以这样查询： select id from t where num=10 union all select id from t where num=20
in 和 not in 也要慎用，否则会导致全表扫描select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描select id from t where num/2=100 应改为: select id from t where num=100*2
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描select id from t where substring(name,1,3)='abc'--name以abc开头的id 应改为: select id from t where name like 'abc%'
很多时候用 exists 代替 in 是一个好的选择select num from a where num in(select num from b) 用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率(5)
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销
尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写

后记——了解MySQL索引

什么是索引？

索引是一种数据结构，具体表现在查找算法上。

索引分为主键索引和辅助索引，辅助索引又分为唯一性索引，普通索引，复合索引，覆盖索引。

索引的本质：以空间换时间。

索引目的

提高查询效率

【类比字典和借书】

如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。

去图书馆借书也是一样，如果你要借某一本书，一定是先找到对应的分类科目，再找到对应的编号，这是生活中活生生的例子，通用索引，可以加快查询速度，快速定位。

二叉树

每个节点最多含有两个子树的树称为二叉树。

二叉查找树ADT Tree

左子树的键值小于根的键值，右子树的键值大于根的键值。

平衡二叉树AVL Tree

在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

BTree

BTree也称为平衡多路查找树

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

B+Tree

B+Tree是在B-Tree基础上的一种优化

非叶子结点只存储键值信息，不存储数据
所有的叶子结点都有一个链指针
数据记录都存放在叶子结点中

MySQL默认使用B+Tree索引

索引本身也很大，所以存储在磁盘中,需要加载到内存中执行。

故：索引结构优劣标准：磁盘I/O次数

BTree是为了充分利用磁盘预读功能而创建出来的一种数据结构。

局部性原理和磁盘阅读

局部性原理：当一个数据被用到，其附近的数据很可能会马上用到

磁盘阅读：由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入主存。