本文所指的SQL优化主要是指MySQL优化,当然思想是通用的,其他SQL语言不排除其通用性。谈到SQL优化,最好有一个指导或者规范,照着这个规范去对比,发现可以优化的地方。参考博文MySQL推荐使用规范。
总体思路
优化的总体思路,也可以说是数据库性能优化方法论。遇到性能问题时,要判断是哪一种,然后才能知道需要优化什么,及如何优化。任何计算机应用系统最终性能瓶颈问题可以归结为:
导致慢SQL的原因
在遇到慢SQL情况时,不能简单的把原因归结为SQL编写问题,实际上导致慢SQL有很多因素,甚至包括硬件和MySQL本身的bug。出现的概率从大到小排序:
数据库优化
数据库优化可以从架构优化,硬件优化,DB优化,SQL优化。呈倒三角,此上而下,位置越靠前优化越明显,对数据库的性能提升越高。我们常说的SQL优化反而是对性能提高最小的优化。
常见的优化手段有:分布式缓存,读写分离,分库分表。
引入分布式缓存中间件如Redis,需要考虑缓存一致性问题,即如果DB数据变更后,缓存内的数据是否能及时同步更新;也需要应对极端情况,如:缓存穿透、缓存击穿和缓存雪崩的问题。
读写分离,常用于读多写少的应用场景,通过增加数据库服务器节点,形成一主多从的架构,主库负责接受写请求,从库负责接受读(查询)请求。
主从之间,通过binlog同步数据。当准备实施读写分离时,为了保证高可用,需要实现故障的自动转移,主从架构会有潜在主从不一致性问题。
包括垂直拆分和水平拆分
垂直分库是根据数据库里面的数据表的相关性进行拆分,比如:一个数据库里面既存在用户数据,又存在订单数据,那么垂直拆分可以把用户数据放到用户库、把订单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式,常见的是把一个多字段的大表按常用字段和非常用字段进行拆分,每个表里面的数据记录数一般情况下是相同的,只是字段不一样,使用主键关联。
优点:
缺点:
分区也是一种简单的水平拆分。MySQL5.1版本开始就支持分区。参考MySQL分区Partition
水平拆分是通过某种策略将数据分片来存储,分库内分表和分库两部分,每片数据会分散到不同的MySQL表或库,达到分布式的效果,能够支持非常大的数据量。前面的表分区本质上也是一种特殊的库内分表 库内分表,仅仅是单纯的解决了单一表数据过大的问题,由于没有把表的数据分布到不同的机器上,因此对于减轻MySQL服务器的压力来说,并没有太大的作用,大家还是竞争同一个物理机上的IO、CPU、网络,这个就要通过分库来解决
前面垂直拆分的用户表如果进行水平拆分,结果是:
实际情况中往往会是垂直拆分和水平拆分的结合,即将Users_A_M和Users_N_Z再拆成Users和UserExtras,这样一共四张表
优点:
不存在单库大数据和高并发的性能瓶颈
应用端改造较少
提高了系统的稳定性和负载能力
缺点:
分片原则
这里特别强调一下分片规则的选择问题,如果某个表的数据有明显的时间特征,比如订单、交易记录等,则他们通常比较合适用时间范围分片,因为具有时效性的数据,我们往往关注其近期的数据,查询条件中往往带有时间字段进行过滤,比较好的方案是,当前活跃的数据,采用跨度比较短的时间段进行分片,而历史性的数据,则采用比较长的跨度存储。
总体上来说,分片的选择是取决于最频繁的查询SQL的条件,因为不带任何Where语句的查询SQL,会遍历所有的分片,性能相对最差,因此这种SQL越多,对系统的影响越大,所以我们要尽量避免这种SQL的产生。
以机械硬盘、普通固态硬盘、PCIE固态硬盘三种不同的硬盘为例,来看他们的评测数据:
吞吐率:单位时间内读写的数据量
机械硬盘:约100MB/s ~ 200MB/s
普通固态硬盘:200MB/s ~ 500MB/s
PCIE固态硬盘:900MB/s ~ 3GB/s
IOPS:每秒IO操作的次数
机械硬盘:100 ~200
普通固态硬盘:30000 ~ 50000
PCIE固态硬盘:数十万
可以明显发现:越昂贵的硬盘,其运行效率越高,对SQL执行的效率提升越明显。
数据库实例参数优化遵循三句口诀:日志不能小、缓存足够大、连接要够用。
数据库事务提交后需要将事务对数据页的修改刷(fsync)到磁盘上,才能保证数据的持久性。这个刷盘,是一个随机写,性能较低,如果每次事务提交都要刷盘,会极大影响数据库的性能。数据库在架构设计中都会采用如下两个优化手法:
所以日志跟缓存对数据库实例尤其重要。而连接如果不够用,数据库会直接抛出异常,系统无法访问。
常见的参数配置:
sync_binlog
:可选值,1,100,0,1安全性最好max_connections
:根据业务调整innodb_flush_log_at_trx_commit
:2,安全和性能的折中考虑interactive_timeout
:back_log
:back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。可以从默认的50升至500wait_timeout
:数据库连接闲置时间,闲置连接会占用内存资源。可以从默认的8小时减到半小时max_user_connection
: 最大连接数,默认为0无上限,最好设一个合理上限thread_concurrency
:并发线程数,设为CPU核数的两倍skip_name_resolve
:禁止对外部连接进行DNS解析,消除DNS解析时间,但需要所有远程主机用IP访问key_buffer_size
:索引块的缓存大小,增加会提升索引处理速度,对MyISAM表性能影响最大。对于内存4G左右,可设为256M或384M,通过查询show status like 'key_read%'
,保证key_reads / key_read_requests
在0.1%以下最好innodb_buffer_pool_size
:缓存数据块和索引块,对InnoDB表性能影响最大。通过查询show status like 'Innodb_buffer_pool_read%'
,保证(Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests
越高越好innodb_additional_mem_pool_size
:InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小,当数据库对象非常多的时候,适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率,当过小的时候,MySQL会记录Warning信息到数据库的错误日志中,这时就需要该调整这个参数大小innodb_log_buffer_size
:InnoDB存储引擎的事务日志所使用的缓冲区,一般来说不建议超过32MBquery_cache_size
:缓存MySQL中的ResultSet,也就是一条SQL语句执行的结果集,所以仅仅只能针对select语句。当某个表的数据有任何任何变化,都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以,当我们的数据变化非常频繁的情况下,使用Query Cache可能会得不偿失。根据命中率Qcache_hits/(Qcache_hits + Qcache_inserts)*100)
进行调整,一般不建议太大,256MB可能已经差不多了,大型的配置型静态数据可适当调大。可以通过命令show status like 'Qcache_%'
查看目前系统Query catch使用大小read_buffer_size
:MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区,MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁,可以通过增加该变量值以及内存缓冲区大小提高其性能sort_buffer_size
:MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试增加sort_buffer_size
变量的大小read_rnd_buffer_size
:MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如,按照排序顺序),将分配一个随机读缓存区。进行排序查询时,MySQL会首先扫描一遍该缓冲,以避免磁盘搜索,提高查询速度,如果需要排序大量数据,可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过大。record_buffer
:每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描,可能想要增加该值thread_cache_size
:保存当前没有与连接关联但是准备为后面新的连接服务的线程,可以快速响应连接的线程请求而无需创建新的table_cache
:类似于thread_cache_size
,但用来缓存表文件,对InnoDB效果不大,主要用于MyISAM通过工具可以帮助定位慢SQL,以及针对性地进行SQL优化。
SQL优化技巧有很多,包括单表优化和多表优化,简单分类如下:
name like 'ABC%'
方式,优于name like '%ABC%'
。不用使用select *
,务必指明字段名称:
select *
增加很多不必要的消耗(CPU、IO、内存、网络带宽)*
依次转换为所有的列名,需查询数据字典,增加解析成本select *
select id from t where num in(1,2,3)
对于连续的数值,能用between就不要用in了;再或者使用连接来替换。log(n)
级别,in的个数建议控制在200以内select id from t where num=10 or num=20
,可以这样查询:select id from t where num=10 union all select id from t where num=20
=
左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引select user_id,user_project from user_base where age*2=36;
会造成引擎放弃使用索引,建议改成:select user_id,user_project from user_base where age=36/2;
select id from t where num is null
,可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0
select * from order where status!=0 and stauts!=1
,not in/not exists都不是好习惯,可以优化为in查询:select * from order where status in(2,3)
对于查询语句:select A.id, B.name from A join B on A.id = B.user_id
LEFT JOIN A表为驱动表,INNER JOIN MySQL会自动找出那个数据少的表作用驱动表,RIGHT JOIN B表为驱动表。
select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;
常见的优化技巧:
在日常开发工作中,可以做一些工作达到预防慢SQL问题,比如在上线前预先用诊断工具对SQL进行分析。常用的工具有:
SQL优化过程
SQL优化方法
优化目标:
SQL优化层级
应用程序级调优
实例级调优
操作系统交互
提到SQL优化,一定要知道执行计划,也要能看懂执行计划。通过EXPLAIN关键词获取执行计划:
desc/explain sql;
extended explain sql;
show warnings
:可以查看实际执行的语句,告警信息
索引覆盖(covering index):需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index;
ICP特性(Index Condition Pushdown):本来index仅仅是data access的一种访问模式,存数引擎通过索引回表获取的数据会传递到MySQL Server层进行where条件过滤。5.6版本开始当ICP打开时,如果部分where条件能使用索引的字段,MySQL Server会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解MySQL的架构图分为Server和存储引擎层;
索引合并(index merge):对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到,如果是AND条件,考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge,EXTRA会显示具体的合并算法和用到的索引。
using filesort、using temporary十分耗费性能,在使用group by时,虽然没有使用order by,如果没有索引,是可能同时出现using filesort,using temporary的,因为group by就是先排序在分组,如果没有排序的需要,可以加上一个order by NULL来避免排序,这样using filesort就会去除,能提升一点性能。
system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现;
const:如果通过索引依次就找到了,const用于比较主键索引或者unique索引。因为只能匹配一行数据,所以很快。如果将主键置于where列表中,MySQL就能将该查询转换为一个常量;
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描;
ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体;
range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where语句中出现between、<、>、in等的查询,这种范围扫描索引比全表扫描要好,因为只需要开始于缩印的某一点,而结束于另一点,不用扫描全部索引;
index:Full Index Scan ,index与ALL的区别为index类型只遍历索引树,这通常比ALL快,因为索引文件通常比数据文件小,也就是说虽然ALL和index都是读全表,但index是从索引中读取的,而ALL是从硬盘读取的;
all:Full Table Scan,遍历全表获得匹配的行。
EXPLAIN
拿到慢SQL后,先用explain运行一下,查看SQL执行计划。关于explain的结果的解读,参考博文MySQL Explain详解。重点关注下面5个指标数据
type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式。
key_len列,索引长度。
rows列,扫描行数。该值是个预估值。
extra列,详细说明。注意,常见的不太友好的值,如下:Using filesort,Using temporary。
当只需要一条数据或者明确知道只有一条返回结果时,使用limit 1
使EXPLAIN中type列达到const类型;数据库并不知道只有一条数据,加上limit 1
让它主动停止游标移动;