MySQL数据库4种常用优化方式，sql语句书写优化规范

最新推荐文章于 2023-09-25 15:55:03 发布

清如许.

最新推荐文章于 2023-09-25 15:55:03 发布

阅读量928

点赞数 3

分类专栏：数据库文章标签：常用MySQL优化手段 MySQL优化 MySQL索引优化慢查询

本文链接：https://blog.csdn.net/MacWx/article/details/99706398

版权

数据库专栏收录该内容

15 篇文章 1 订阅

订阅专栏

MySQL数据库优化

引言

实际项目中，我们的数据往往存储在数据库中，但是由于数据库本身是持久化存储，数据的查询速度受到磁盘IO瓶颈的限制，同时，随着数据量的增长，数据的查询也会因为单表数据量的巨大，影响查询速度。所以我们需要对数据库进行优化，提高系统的响应效率。

数据库优化手段

索引优化。
添加查询缓存。
优化数据库SQL语句。
数据库Cluster(搭建数据库集群环境)。

优化储备知识

3.1 慢查询

慢查询就是查看我们每条sql语句查询所用的时间，
开启慢查询的目的是将我们每次查询所用的时间记录在日志里进行观察检测，如果检测到查询的时间到达超过一定的预定时间（比如查询时间超过1秒的语句），将被记录下来进行优化！

查看慢查询的信息状态

show variables like ‘%quer%’;

在这里插入图片描述

开启慢查询

set global slow_query_log=on;

在这里插入图片描述

修改慢查询最短时间为1s

set GLOBAL long_query_time = 1;

在这里插入图片描述

查看MySQL执行过的慢查询SQL语句。

在这里插入图片描述

3.2 执行计划

补充：数据库查询优化中需要了解MySQL执行计划命令，查看sql的执行效率

进行一条查询语句，点下左上角的解释看语句解释：
在这里插入图片描述

索引优化

4.1 索引语法

索引的作用

加快以索引字段为条件的查询效率。

创建索引

create index 索引名字 on 表(字段)

删除索引

drop index 索引名字

查询某张表创建了哪些索引

Show index from 表名;

索引的特点

①索引会占用存储空间，虽然比较少，但是也占用了。

②MySQL会自动为primary 主键列和unique 唯一列自动增加索引。

③MySQL数据库对数据做DML操作时，需要同时做维护索引的操作。

隐含之意，就是索引会降低数据库增删改的效率。

使用原则：

一般会对sql中的where条件字段或者order by字段建立索引。

4.2 索引应用场景

实际项目中，如果从业务角度出发，发现经常会用到某个字段，对表数据进行查询，或者根据经常用到某个字段的排序，则该数据是需要增加索引的。

查询缓存

在数据库操作对象DAO处增加查询缓存。

参考Ehcache缓存，或者MyBatis+Ehcache整合方案、Hibernate二级缓存Ehcache。

需要注意的问题是，要注意数据的一致性，所以缓存思路如下：

① 只对查询结果进行缓存

②
缓存结果存放要以业务逻辑相关为划分单位。比如，如果是单表，则可以创建一个缓存区域，与该表对应，如果业务中涉及到表连接操作，要将查询结果缓存在这多张表的查询结果都要缓存在对应的一个缓存区域中。

当执行增删改的时候，为了避免出现脏数据。
基于以上的特点，缓存原则是对那些查询需求远大于增删改需求的数据，进行缓存。

例如：新闻网站的首页数据、电商的类别数据等。

SQL优化

尽量不要在要给在SQL语句的where子句中使用函数，这样会使索引失效。
如果已经确定查询结果只有一条数据（当表中数据的该字段是唯一的），在查询SQL末尾增加
limit 1，这样MySQL的查询执行引擎在找到1条数据之后就会停止搜索，提高效率。（mysql5.6之后有效）
例如：

select * from t_department where department_name = ‘研发部’ limit 1;

模糊查询尽量使用右模糊：‘xx%’，这样可以利用上索引，而使用‘%xx’这种模糊查询会导致该查询字段上的索引失效。
如果非要使用表连接，最好要保证连接的两个字段都是创建了索引的。
查询的时候最好用什么数据查询什么数据，避免使用select
*,原因是数据库访问往往是远程通过网络来访问，这也就意味着，查询结果也是通过网络来传输的，如果查询了额外的无用的数据，他们会额外占用网络带宽，数据传输效率也会降低。
尽量使用非空 not
null，可以使用‘’空串代替null。因为‘’不占用空间，null会占用空间的。
使用!= 或者<> 数据库会放弃索引。

在这里插入图片描述