1公众号关注“杰哥的IT之旅”， 2 3 4 5 6 7 8 选择“星标”，重磅干货，第一时间送达！ 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
转自：赵栩彬
前言
数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷。
1.优化一览图
2.优化
笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置。
2.1 软优化
2.1.1 查询语句优化
-
1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
-
2.例:
DESC SELECT * FROM
user
显示:
其中会显示索引和查询数据读取数据条数等信息.
2.1.2 优化子查询
在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.
2.1.3 使用索引
索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:
-
LIKE关键字匹配'%'开头的字符串,不会使用索引.
-
OR关键字的两个字段必须都是用了索引,该查询才会使用索引.
-
使用多列索引必须满足最左匹配.
2.1.4 分解表
对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,
2.1.5 中间表
对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.
2.1.6 增加冗余字段 类似于创建中间表,增加冗余也是为了减少连接查询.
2.1.7 分析表,,检查表,优化表
分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.
-
1.分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;
-
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:排序缓冲区
2.2.3 分库分表
因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。
2.2.4 缓存集群
如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。
结语
一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.
1往期资源回顾 需要可自取 2 3 4 5 6 7 8 9 DevOps 实践手册、linux系统诊断、大数据工程师必读手册、OSS运维基础实战手册、程序员面试宝典、深入浅出 Kubernetes 实战手册、RDS数据库入门一本通、CDN排坑指南 10 11 12 13 一篇文章带你解读从初级运维工程师到资深运维专家的学习路线 14 15 16 17 40+ 张最全 Linux / C / C++ 思维导图！ 18 19 20 21 学习 Python 的 14 张思维导图！ 22 23 24 25 7 张思维导图带你掌握 “Python学习路线” 26 27 28 29 我花了一周的时间，就为了整理这份 97 页的 Python 自动化系列文档。【附获取方式】 30 31 32 33 我精心整理的 136 页 Excel 数据透视表 PDF 文件！【附获取方式】 34 35 36 37 38 39 40 41 42 43 44 45 46 47 推荐阅读 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 超经典！十步完全理解 SQL 75 数据库分库分表解决方案汇总 76 高性能 MySQL 主从架构的复制原理及配置详解 77 全面解析 52 条 SQL 语句性能优化策略，建议收藏！ 78 MySQL 高频面试题，都在这了 79 免费在线 SQL 数据库环境，学习测试两不误！ 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 点个[在看]，是对杰哥最大的支持！ 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191
本文分享自微信公众号 - 杰哥的IT之旅（Jake_Internet）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。