数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.
① 优化一览图
笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.
①首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.
显示:
其中会显示索引和查询数据读取数据条数等信息.
在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,所以呢效率比嵌套子查询高.
索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者MySQL数据库索引一文,介绍比较详细,此处记录使用索引的三大注意事项:
对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,
对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.
类似于创建中间表,增加冗余也是为了减少连接查询.
分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.
① 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user;
option 只对MyISAM有效,共五个参数值:
LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.
①配置多核心和频率高的cpu,多核心可以执行多个线程.
优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.
因为数据库压力过大,首先一个问题就是高峰期系统性能可能会降低,因为数据库负载过高对性能会有影响.另外一个,压力过大把你的数据库给搞挂了怎么办?所以此时你必须得对系统做分库分表 ◆ 读写分离,也就是把一个库拆分为多个库,部署在多个数据库服务上,这时作为主库承载写入请求.然后每个主库都挂载至少一个从库,由从库来承载读请求.
如果用户量越来越大,此时你可以不停的加机器,比如说系统层面不停加机器,就可以承载更高的并发请求.然后数据库层面如果写入并发越来越高,就扩容加数据库服务器,通过分库分表是可以支持扩容机器的,如果数据库层面的读并发越来越高,就扩容加更多的从库.但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的,所以通常来说,数据库单机每秒承载的并发就在几千的数量级,而且数据库使用的机器都是比较高配置,比较昂贵的机器,成本很高.如果你就是简单的不停的加机器,其实是不对的.所以在高并发架构里通常都有缓存这个环节,缓存系统的设计就是为了承载高并发而生.所以单机承载的并发量都在每秒几万,甚至每秒数十万,对高并发的承载能力比数据库系统要高出一到两个数量级.所以你完全可以根据系统的业务特性,对那种写少读多的请求,引入缓存集群.具体来说,就是在写数据库的时候同时写一份数据到缓存集群里,然后用缓存集群来承载大部分的读请求.这样的话,通过缓存集群,就可以用更少的机器资源承载更高的并发.
一个完整而复杂的高并发系统架构中,一定会包含：各种复杂的自研基础架构系统.各种精妙的架构设计.所以呢一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.
将查询语句放到服务器命令行去跑,如果慢,则可以考虑通过添加索引来提高查询速度.
如已有索引或添加索引后查询速度仍未改善,查看语句执行计划中,是全表扫描还是走索引.如果走了索引,那就可能考虑是服务器性能瓶颈或数据库设置问题,涉及的设置项比较多,你没有提供相关信息,无法继续提供优化建议.如果没有走索引,检查语法（查询条件添加函数不走索引）和表属性（关联表字符集不统一不走索引）.
如果服务器本地快,但页面查询慢,那就排除了性能问题,考虑网络问题与页面查询语句调用的驱动模块是否有问题.检测网络连接速度,如慢尝试更换网线.网络连接速度正常,则尝试更换调用的驱动包,重新下一个或换一个版本.
mysql支持几十万的数据,响应速度应该是毫秒级的.
看了下你的语句,不要用IN了,改INNER JOIN吧,套那么多层IN,肯定没效率.
问题
实验
写个简单的脚本,制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下 SQL 看看效果：
...
现在用一下 DBA 三板斧,看看执行计划：
感觉有点惨,由于 information_schema.columns 是元数据表,没有必要的统计信息.
那我们来 show warnings 看看 MySQL 改写后的 SQL：
我们格式化一下 SQL：
可以看到 MySQL 将
select from A where A.x not in (select x from B) //非关联子查询
转换成了
select from A where not exists (select 1 from B where B.x = a.x) //关联子查询
如果我们自己是 MySQL,在执行非关联子查询时,可以使用很简单的策略：
而关联子查询就需要循环迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA： ? ? 扫描 B 表,找到其中的第一条满足 rA 条件的记录.
显然,关联子查询的扫描成本会高于非关联子查询.
我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化,MATERIALIZATION）,但MySQL 认为不缓存更快,我们就需要给予 MySQL 一定指导.
整理
我们诊断的关键点如下：
\1. 对于 information_schema 中的元数据表,执行计划不能提供有效信息.
但目前我们的实验仅限于猜测,猜中了万事大吉,猜不中就无法做出好的诊断.
①.、使用行级别锁,避免表级别或页级别锁
尽量使用支持行级别锁的存储引擎,如InnoDB;只在读操作显著多于写作的场景中(如数据仓库类的应用)使用表级别锁的存储引擎,如MyISAM;.
临界区(仅允许单一线程访问的资源)会严重降低MySQL系统并发性;InnoDB缓冲池(buffer pool)、数据字典等都是常见的临界区;幸运的是,新版本的InnoDB已经能够较好的运行于多核处理器,支持使用 innodb_buffer_pool_instances服务器变量建立多个缓冲池实例,每个缓冲池实例分别自我管理空闲列表、列表刷写、LRU以及其它跟缓冲池相关的数据结构,并通过各自的互斥锁进行保护.
此外,异步I/O也可以在一定程度上提高系统的并发能力,在Linux系统上,可以通过将MySQL的服务器变量innodb_use_native_aio的值设定为ON设定InnoDB可以使用Linux的异步I/O子系统.
默认情况下,MySQL的清写(purge)操作(用于移除带删除标记的记录)由InnoDB的主线程完成,这可以降低内部资源竞争发生的概率,进而增强MySQL服务伸缩能力.不过,随着InnoDB内部各式各样的竞争越来越多,这种设置带来的性能优势已几乎不值一提,所以呢,生产环境中应该通过为innodb_purge_threads服务器变量设定为ON将主线程与清写线程分开运行.
通过sysbench的oltp_read_write测试来模拟业务压力、以此来给指定的硬件环境配置一份比较合理的MySQL配置文件.
环境介绍
硬件配置
软件环境
优化层级与指导思想
优化层级
MySQL数据库优化可以在多个不同的层级进行,常见的有：
SQL优化
参数优化
架构优化
本文重点关注：参数优化
指导思想
日志先行 -- 一个事务能否成功提交的关键是日志是否成功落盘,与数据没有太大的关系；也就是说对写的优化可以表述为各方面的资源向写操作倾斜.
瓶颈分析 -- 通过show global status 的各个计数器的值基本上就能分析出当前瓶颈所在,再结合一些简单的系统层面的监控工具如top iostat 就能明确瓶颈.
整体性能是"读""写"之间的再平衡.
以上就是土嘎嘎小编为大家整理的mysql查询瓶颈怎么办相关主题介绍,如果您觉得小编更新的文章只要能对粉丝们有用,就是我们最大的鼓励和动力,不要忘记讲本站分享给您身边的朋友哦！！