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

古城微笑少年丶 2021-10-19 06:52 302阅读 0赞

**MySQL数据库优化**

1. 引言

2. 数据库优化手段

3. 优化储备知识

3.1 慢查询

3.2 执行计划

4. 索引优化

4.1 索引语法

4.2 索引应用场景

5. 查询缓存

6. SQL优化

7. 读写分离

7.1 CentOS中linux的安装

7.2 MySQL主从复制

7.3 读写分离(基于MyCat实现)

# 引言 #

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

# 数据库优化手段 #

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

# 优化储备知识 #

## 3.1 慢查询 ##

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

1.  查看慢查询的信息状态

> show variables like ‘%quer%’;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70]

1.  开启慢查询

> set global slow\_query\_log=on;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 1]

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

> set GLOBAL long\_query\_time = 1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 2]

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 3]

## 3.2 执行计划 ##

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

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

# 索引优化 #

## 4.1 索引语法 ##

1.  索引的作用

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

1.  创建索引

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

1.  删除索引

> drop index 索引名字

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

> Show index from 表名;

1.  索引的特点

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

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

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

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

1.  使用原则：

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

## 4.2 索引应用场景 ##

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

# ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 5]   查询缓存 #

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

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

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

> ① 只对查询结果进行缓存

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

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

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

# SQL优化 #

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

> select \* from t\_department where department\_name = ‘研发部’ limit 1;

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 6]

1.  在SQL语句中不要有运算，否则MySQL会放弃索引。（mysql在执行查询时，会自动过滤当前语句，当效率大于使用索引时，则默认不使用索引）

![在这里插入图片描述][20190818171706234.png]

1.  对于百万级以上的表数据，如果要做表连接查询，一定要对表先分页，然后对分页结果再做表连接。
2.  尽量避免大事务操作，这样会降低系统的并发能力。在满足实际业务的前提下，尽量缩短事务的边界。
3.  Where子句多条件and连接的情况，要把精确条件放在最先执行的位置，提高效率。Oracle中是从右至左。MySQL中是从左至右。  
    ![在这里插入图片描述][2019081817175944.jpg]

# MySql集群环境之------读写分离、主从复制 #

搭建MySQL的集群环境来优化查询  
详细的使用搭建步骤可参考我之前文章：

[ Centos 7使用MyCat搭建 MySQL-读写分离][Centos 7_MyCat_ MySQL-]

[ Centos 7搭建MySQL-主从复制][Centos 7_MySQL-]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70]: /images/20211018/e2d0e4620d244cce8b014106522a5b45.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 1]: /images/20211018/19b14eb73815444f9ed619ad75dcefa1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 2]: /images/20211018/ddf8cc5240384224a8a8159a049f9cc5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 3]: /images/20211018/343520b23b4343a2a0e75b9caada4bc6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 4]: /images/20211018/3cd3033fe8d84b41b4b26af32d2f948a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 5]: /images/20211018/f23ff56247a04539932b466b0e5645e8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01hY1d4_size_16_color_FFFFFF_t_70 6]: /images/20211018/3e42f63635014c82aa7c9b796094ed72.png
[20190818171706234.png]: /images/20211018/364cb1982df5424090c1451f164b256a.png
[2019081817175944.jpg]: /images/20211018/ea4e460bac614f88966f25e7bea9e05a.png
[Centos 7_MyCat_ MySQL-]: https://blog.csdn.net/MacWx/article/details/98778719
[Centos 7_MySQL-]: https://blog.csdn.net/MacWx/article/details/98777361