Mysql优化-全面详解（学习总结---从入门到深化）

ゝ一纸荒年。 2023-09-23 18:20 16阅读 0赞

# Sql性能下降的原因 #

![e380054320d14a6fbb0df24d9cc5625a.png][]

> 在程序的运行过程中，我们会发现这样的一个现象，随着程序运行 时间的不断推移以及数据量越来越大，程序响应的时间逐渐变慢， 程序变得卡顿，但最开始的时候并不是这样的，那是什么原因导致 的呢？
> 
> **性能下降的原因**
> 
> ![d2c2f95cc5974bc5b428771f3771af07.png][]
> 
> sql性能下降的原因有哪些？
> 
> 1、索引失效
> 
> 2、服务器性能不足
> 
> 3、sql编写不合理

# Sql读取顺序 #

![8f9d87453ca64726a80cf6643b84c531.png][]

> 我们在编写sql代码时的顺序和Mysql内部读取sql时的顺序是不一样 的，因此有必要搞清楚Mysql读取sql语句的顺序。
> 
> ![f3f11202d6f24ee4b6d5674350187166.png][]
> 
>  **整体过程**
> 
> 先对多表进行关联，根据条件找出符合的记录
> 
> 在符合的记录基础上进行where条件过滤
> 
> 对筛选出的记录进行分组操作
> 
> 分组完成后再进行having操作，过滤出满足条件的数据
> 
> 对取出的记录进行排序
> 
> 再按照分页条件取出要显示的数据

> 1.下面关于sql读取顺序正确的是？**from->where->group by->select->order by->limit**

# explain执行计划 #

![c87956b0d45a4700bd79ec057737ad42.png][]

> Mysql 使用 explain 关键字可以模拟优化器执行 sql 语句，我们就 能够知道 Mysql 会如何处理sql，可以根据 explain 的分析结果和 Mysql 底层数据结构优化 sql。不同Mysql 版本可能有差别，但差别 不会很大。
> 
> **执行示例：**
> 
>     EXPLAIN SELECT hco.co_id,hco.patient_name FROM
>     his_care_order hco LEFT JOIN
>     his_care_order_item hcoi ON hco.co_id =
>     hcoi.co_id
> 
> ![701c86a4cb054aae9cab177726d6a6a8.png][]
> 
> ![585e5d5cfd7e4ccb8814238f54e8696f.png][]

> 1、 id：select查询的序号，表示执行select操作时表的顺序。 id相同按顺序走
> 
>     EXPLAIN SELECT * FROM `his_care_order` o
>     ,`his_care_order_item` i WHERE
>     o.co_id=i.co_id
> 
> id不同，序号大的先执行
> 
>     EXPLAIN SELECT * FROM `his_care_order` o
>     WHERE co_id=(SELECT co_id FROM
>     (SELECT co_id FROM `his_care_order_item`
>     WHERE item_id='ITEM1465223982444838912')T )
> 
> id相同，不同同时存在
> 
>     EXPLAIN SELECT * FROM sys_dept d,(SELECT
>     dept_id FROM sys_user GROUP BY dept_id)t
>     WHERE t.dept_id=d.dept_id
> 
> 2、select\_type
> 
> 查询类型，主要用于区别普通查询、联合查询、子查询 simple：简单select查询，查询中不包含子查询或union；
> 
>     SELECT * FROM `his_care_order`
> 
> PRIMARY：主键查询
> 
>     SELECT * FROM `his_care_order`
>     WHERE co_id=(SELECT co_id FROM
>     `his_care_order_item` WHERE
>     item_id='ITEM1465859053631700992')
> 
> SUBQUERY：where条件包含了子查询
> 
>     EXPLAIN SELECT * FROM `his_care_order`
>     WHERE co_id=(SELECT co_id FROM
>     `his_care_order_item` WHERE
>     item_id='ITEM1465859053631700992')
> 
> DERIVED：from的表中包含子查询，被标记为derived（衍 生），把子查询的结果放在临时表中
> 
>     SELECT * FROM `his_care_order` a,(SELECT
>     co_id FROM `his_care_order_item`  GROUP BY
>     co_id)b
>     WHERE a.co_id=b.co_id

> **1、table** 
> 
> 显示这一行的数据是来自于哪张表的
> 
> **2、partitions**
> 
> 如果查询是基于分区的话，会显示查询访问的分区
> 
> **3、type**
> 
> 访问类型，按性能从低到高依次排列为
> 
> ALL：全表扫描，一定要优化
> 
> index：它和All都是扫描全表，但index是从索引中读取表，All 是从硬盘中读取
> 
> range：只检索给定范围的行，key列显示使用了哪个索引， between and或in等查询
> 
> ref：非唯一性索引扫描，本质上也是一种索引访问
> 
> eq-ref：唯一性索引扫描，对于每个索引键只有一条记录与之匹 配
> 
> const：通过索引一次就找到了，常见于primary或unique索引 查找
> 
> system：表中只有一行记录（系统表），很少出现 NULL：不需要访问表
> 
> **4、possible\_keys：**
> 
> 显示可能应用在这张表中的索引，一个或多 个，查询涉及的字段上若建立了索引则会列出来，但不一定被使用
> 
> **5、key：**
> 
> 它和possible\_keys的关系，理论上应该用到哪些索引，实 际上用到了哪些索引
> 
> **6、key\_len：**
> 
> 索引使用的字节数，key\_len显示的值为索引字段的最 大可能长度
> 
> **7、ref：**
> 
> 索引是否被引用到，用到了哪几个索引
> 
> **8、rows：**
> 
> 根据表统计信息及索引使用情况，估算所需读取的记录 行数
> 
> **9、filtered：**
> 
> 满足查询条件记录数量的比例，是百分比，不是具体 的记录数，这个值越大越好，它依赖于统计信息，并不是很准确
> 
> **10、Extra：**
> 
> Using filesort（文件排序，表示mysql无法利用索引完 成排序操作）
> 
> Using temporary（使用了临时表保存中间结果，常见 于order by和group by）
> 
> Using index（如果同时出现Using where，表明索引用 来执行索引键值的查 找，如果没有出现Using where，表 明索引用来读取数据而非执行查找）
> 
> Using where（使用了where过滤）
> 
> Using join buffer（使用了连接缓存）
> 
> impossible where（where子句的值总是false，不能用 来获取记录）

> 1.下面关于explain命令中id列的说法错误的是？ **id列是按正序排列的**
> 
> 2.下面关于explain命令中type列的说法正确的是？
> 
> 2.1 type列包含ALL, index, range, ref, eq\_ref, const, system, NULL等多种类型
> 
> 2.2 按性能，All最差，NULL最高
> 
> 2.3 在实际开发中至少要达到range

[e380054320d14a6fbb0df24d9cc5625a.png]: https://img-blog.csdnimg.cn/e380054320d14a6fbb0df24d9cc5625a.png
[d2c2f95cc5974bc5b428771f3771af07.png]: https://img-blog.csdnimg.cn/d2c2f95cc5974bc5b428771f3771af07.png
[8f9d87453ca64726a80cf6643b84c531.png]: https://img-blog.csdnimg.cn/8f9d87453ca64726a80cf6643b84c531.png
[f3f11202d6f24ee4b6d5674350187166.png]: https://img-blog.csdnimg.cn/f3f11202d6f24ee4b6d5674350187166.png
[c87956b0d45a4700bd79ec057737ad42.png]: https://img-blog.csdnimg.cn/c87956b0d45a4700bd79ec057737ad42.png
[701c86a4cb054aae9cab177726d6a6a8.png]: https://img-blog.csdnimg.cn/701c86a4cb054aae9cab177726d6a6a8.png
[585e5d5cfd7e4ccb8814238f54e8696f.png]: https://img-blog.csdnimg.cn/585e5d5cfd7e4ccb8814238f54e8696f.png