MySQL：数据库优化，可以看看这篇文章

左手的ㄟ右手 2021-08-19 13:47 362阅读 0赞

作者 | 赵栩彬  
链接 | segmentfault.com/a/1190000018631870 上 篇 |   [一千行 MySQL 学习笔记][MySQL]

## **前言** ##

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

## **1. 优化一览图** ##

![format_png][]

## **2. 优化**    ##

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

### **2.1 软优化** ###

#### **2.1.1 查询语句优化** ####

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

DESC SELECT * FROM `user`

显示:

![format_png 1][]

其中会显示索引和查询数据读取数据条数等信息.

#### **2.1.2 优化子查询** ####

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

#### **2.1.3 使用索引** ####

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者一文,介绍比较详细,此处记录使用索引的三大注意事项:

>  *  LIKE关键字匹配'%'开头的字符串,不会使用索引.
>  *  OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
>  *  使用多列索引必须满足最左匹配.

#### **2.1.4 分解表** ####

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

#### **2.1.5 中间表** ####

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

#### **2.1.6 增加冗余字段** ####

类似于创建中间表,增加冗余也是为了减少连接查询.

#### **2.1.7 分析表,检查表,优化表** ####

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.**1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;** ![format_png 2][]

>  *  Op:表示执行的操作.
>  *  Msg\_type:信息类型,有status,info,note,warning,error.
>  *  Msg\_text:显示信息.

**2. 检查表: 使用 CHECK关键字,如CHECK TABLE user \[option\]** option 只对MyISAM有效,共五个参数值:

>  *  QUICK:不扫描行,不检查错误的连接.
>  *  FAST:只检查没有正确关闭的表.
>  *  CHANGED:只检查上次检查后被更改的表和没被正确关闭的表.
>  *  MEDIUM:扫描行,以验证被删除的连接是有效的,也可以计算各行关键字校验和.
>  *  EXTENDED:最全面的的检查,对每行关键字全面查找.

**3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE \[LOCAL|NO\_WRITE\_TO\_BINLOG\] TABLE user;** LOCAL|NO\_WRITE\_TO\_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

### **2.2 硬优化** ###

#### **2.2.1 硬件三件套** ####

> 1.  配置多核心和频率高的cpu,多核心可以执行多个线程.
> 2.  配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
> 3.  配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

#### **2.2.2 优化数据库参数** ####

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

>  *  key\_buffer\_size:索引缓冲区大小
>  *  table\_cache:能同时打开表的个数
>  *  query\_cache\_size和query\_cache\_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使用SQL\_NO\_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL\_CACHE.
>  *  sort\_buffer\_size:排序缓冲区

更多参数传送门:

> https://www.mysql.com/cn/why-mysql/performance/index.html

#### **2.2.3 分库分表** ####

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。 另外一个，压力过大把你的数据库给搞挂了怎么办？ 所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。 然后每个主库都挂载至少一个从库，由从库来承载读请求。![format_png 3][]

#### **2.****2.4 缓存集群** ####

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。 然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。 但是这里有一个很大的问题： 数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。 如果你就是简单的不停的加机器，其实是不对的。 所以 **在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。**所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。 所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。 具体来说，就是 **在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。** **这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。**![format_png 4][]

## **结语** ##

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

--------------------

【END】

如果看到这里，说明你喜欢这篇文章，请 **转发** **、点赞**。微信搜索「web\_resource」，关注后回复「进群」或者扫描下方二维码即可进入无广告交流群。**↓扫描二维码进群↓**![format_png 5][]

--------------------

**推** **荐** **阅** **读 *****1.***  [一千行 MySQL 学习笔记][MySQL]***2.***    [一份工作坚持多久跳槽最合适？][Link 1]***3.***  [项目中常用到的 19 条 MySQL 优化][19 _ MySQL]***4. *** *** *** [零基础认识 Spring Boot][Spring Boot]

***5.***  [团队开发中 Git 最佳实践][Git]

![format_png 6][]

**喜** **欢** **文** **章** **，** **点** **个** **在看 ** ![format_png 7][]

[MySQL]: https://blog.csdn.net/qq_37217713/article/details/102658688
[format_png]: /images/20210731/0cb472d8b8cf44ecbce4efca261d5acb.png
[format_png 1]: /images/20210731/20ac6a1157594a08812fe21b0416584e.png
[format_png 2]: /images/20210731/26ed2e792ca44cdf8b97dc93ac133e70.png
[format_png 3]: /images/20210731/46a3077e21d74a6da32634abd5dcc27a.png
[format_png 4]: /images/20210731/4902bf51253f4883a58024a5d2e6a4b3.png
[format_png 5]: /images/20210731/136034723e554544894c9f286b785eef.png
[Link 1]: https://blog.csdn.net/qq_37217713/article/details/103170812
[19 _ MySQL]: https://blog.csdn.net/qq_37217713/article/details/103144284
[Spring Boot]: https://blog.csdn.net/qq_37217713/article/details/103094622
[Git]: https://blog.csdn.net/qq_37217713/article/details/102481709
[format_png 6]: /images/20210731/3df05d2b36a948dea39c0f239ebeed3b.png
[format_png 7]: /images/20210731/f1fac8515a6248c9b15270974d9b9e3e.png