数据库调优.md · 浪迹/MySQL - Gitee.com

加入 Gitee

与超过 1200万开发者一起发现、参与优秀开源项目，私有仓库也完全免费：）

克隆/下载

数据库调优.md 11.02 KB

数据库调优

![image-20210224153521307](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224153521307.png)

1. 为什么存储过程可以提速 ，首先存储过程只需要在创建的时候 编译，而SQL每次执行都需要被数据库编译。
2. 存储过程可以降低网络开销，

![image-20210224154057665](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224154057665.png)

![image-20210224154129282](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224154129282.png)

![image-20210224154207895](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224154207895.png)

![image-20210224154232971](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224154232971.png)

![image-20210224154950109](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224154950109.png)

![image-20210224155117974](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224155117974.png)

![image-20210224155332402](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224155332402.png)

开启慢查询日志主要 ，修改记录查询的时间不能立即生效，需要切换新的mysql查询窗口。

![image-20210224155854626](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224155854626.png)

记录未使用索引的SQL语句

SET GLOBAL log_queries_not_using_indexes='ON'

查看慢查询日志

SELECT * FROM mysql.slow_log;

![image-20210224161926549](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224161926549.png)

![image-20210224161907350](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224161907350.png)

![image-20210224163254971](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224163254971.png)

![image-20210224163444168](https://gitee.com/langren19/my-sql/raw/master/image/image-20210224163444168.png)

```
sql性能分析    更加细致分析sql
    1、show profile 目前已被废弃，但是还可以用
    2、INFORMATION_SCHEMA.PROFILING （目前已废弃）
    3、preformace_schema (mysql推荐)
```

![image-20210225143500455](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225143500455.png)

![image-20210225143922304](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225143922304.png)

![image-20210225144010994](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225144010994.png)

![image-20210225144305218](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225144305218.png)

![image-20210302151219583](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302151219583.png)

![image-20210302151246168](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302151246168.png)

![image-20210225155716832](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225155716832.png)

![image-20210225155840849](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225155840849.png)

![image-20210225160345641](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225160345641.png)

最左优先原则

1. 当查询列，不包过最左列的时候就无法使用索引，如图如果查询不包过name 字段的话 ，组合索引无法使用。
2. 当跳过索引的索引中的列，会导致索引无法全部使用，如图当跳过age列 ，索引只能使用到name这一列。
3. 查询中有某个列是范围或者模糊查询，则其右边所有的列都无法使用索引，如图age是范围索引 则只能使用到name，age 这两列。

![image-20210302150945929](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302150945929.png)

![image-20210225163701250](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225163701250.png)

![image-20210225163739201](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225163739201.png)

![image-20210225163801893](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225163801893.png)

使用了hash索引就不能使用相关的排序语句，否则会导致hash索引无法使用，hash索引不支持部分索引的查找，hash索引只支持等值查找，不支持范围，模糊查询，hash冲突越严重，性能下降越厉害。

![image-20210302151111702](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302151111702.png)

![image-20210225164059152](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225164059152.png)

```
建议创建索引的情况（6）
1.对于select语句，频繁作为where条件的筛选字段
2.update或delete语句的where条件
3.需要分组或排序的字段
4.distinct所使用的字段
5.字段的值有唯一性约束，例如主键 
6.对于多表查询，连接字段应创建索引，且类型需要保持一致

不建议创建索引的情况
1.表记录非常少
2.字段有大量重复数据，选择性低，例如性别字段
3.修改频繁查询较少的字段       
```

![image-20210225164628942](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225164628942.png)

![image-20210225164650939](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225164650939.png)

![image-20210225164517377](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225164517377.png)

![image-20210225164548287](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225164548287.png)

![image-20210225164740349](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225164740349.png)

```
解决方案    1、不要在where条件中计算
    2、不要在where使用函数
    3、尽量避免左模糊，无法避免，考虑搜索引擎
    4、额外添加索引
    5、规范编写sql，varchar索引传入数字无法用
    6、修改联合索引顺序
    7、使用is null 无法使用索引，建议索引字段添加not null 约束
```

![image-20210225165711023](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225165711023.png)

![image-20210225165722280](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225165722280.png)

![image-20210225170459331](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225170459331.png)

![image-20210225170624174](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225170624174.png)

![image-20210225171349584](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225171349584.png)

![image-20210225171808463](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225171808463.png)

![image-20210225172237655](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225172237655.png)

![image-20210225172329889](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225172329889.png)

![image-20210225172435886](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225172435886.png)

![image-20210225172511786](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225172511786.png)

![image-20210225172837758](https://gitee.com/langren19/my-sql/raw/master/image/image-20210225172837758.png)

![image-20210301085859239](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301085859239.png)

![image-20210301092902941](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301092902941.png)

![image-20210301093158230](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301093158230.png)

![image-20210301093359450](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301093359450.png)

![image-20210301093421640](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301093421640.png)

![image-20210301093646889](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301093646889.png)

![image-20210301093823809](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301093823809.png)

![image-20210301094327432](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301094327432.png)

![image-20210301095303810](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301095303810.png)

![image-20210301095743280](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301095743280.png)

![image-20210301095819057](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301095819057.png)

![image-20210301100638083](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301100638083.png)

![image-20210301100936072](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301100936072.png)

![image-20210301101018056](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301101018056.png)

![image-20210301101124278](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301101124278.png)

![image-20210301104524405](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301104524405.png)

![image-20210301163627426](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301163627426.png)

![image-20210302110105115](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302110105115.png)

![image-20210302111812042](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302111812042.png)

![image-20210302112159497](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302112159497.png)

![image-20210302112315352](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302112315352.png)

![image-20210302112446575](https://gitee.com/langren19/my-sql/raw/master/image/image-20210302112446575.png)

![image-20210301163710517](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301163710517.png)

![image-20210301164538504](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301164538504.png)

![image-20210301164604679](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301164604679.png)

![image-20210301165111160](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301165111160.png)

![image-20210301165156640](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301165156640.png)

![image-20210301165315200](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301165315200.png)

![image-20210301165411198](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301165411198.png)

![image-20210301165743231](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301165743231.png)

![image-20210301165928534](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301165928534.png)

![image-20210301170021313](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301170021313.png)

![image-20210301170117614](https://gitee.com/langren19/my-sql/raw/master/image/image-20210301170117614.png)

一键复制编辑原始数据按行查看历史

提交于 2021-03-12 17:30 . update 数据库调优.md.

数据库调优

为什么存储过程可以提速，首先存储过程只需要在创建的时候编译，而SQL每次执行都需要被数据库编译。
存储过程可以降低网络开销，

开启慢查询日志主要，修改记录查询的时间不能立即生效，需要切换新的mysql查询窗口。

记录未使用索引的SQL语句

SET GLOBAL log_queries_not_using_indexes='ON'

查看慢查询日志

SELECT * FROM mysql.slow_log;

sql性能分析    更加细致分析sql
    1、show profile 目前已被废弃，但是还可以用
    2、INFORMATION_SCHEMA.PROFILING （目前已废弃）
    3、preformace_schema (mysql推荐)

最左优先原则

当查询列，不包过最左列的时候就无法使用索引，如图如果查询不包过name 字段的话，组合索引无法使用。
当跳过索引的索引中的列，会导致索引无法全部使用，如图当跳过age列，索引只能使用到name这一列。
查询中有某个列是范围或者模糊查询，则其右边所有的列都无法使用索引，如图age是范围索引则只能使用到name，age 这两列。

使用了hash索引就不能使用相关的排序语句，否则会导致hash索引无法使用，hash索引不支持部分索引的查找，hash索引只支持等值查找，不支持范围，模糊查询，hash冲突越严重，性能下降越厉害。

建议创建索引的情况（6）
1.对于select语句，频繁作为where条件的筛选字段
2.update或delete语句的where条件
3.需要分组或排序的字段
4.distinct所使用的字段
5.字段的值有唯一性约束，例如主键 
6.对于多表查询，连接字段应创建索引，且类型需要保持一致

不建议创建索引的情况
1.表记录非常少
2.字段有大量重复数据，选择性低，例如性别字段
3.修改频繁查询较少的字段

解决方案    1、不要在where条件中计算
    2、不要在where使用函数
    3、尽量避免左模糊，无法避免，考虑搜索引擎
    4、额外添加索引
    5、规范编写sql，varchar索引传入数字无法用
    6、修改联合索引顺序
    7、使用is null 无法使用索引，建议索引字段添加not null 约束

SQL

1

https://gitee.com/langren19/my-sql.git

git@gitee.com:langren19/my-sql.git

langren19

my-sql

MySQL

master