日常工作中你是怎么优化SQL的?
SQL优化的一般步骤是什么,怎么看执行计划(explain),如何理解其中各个字段的含义?
如何写sql能够有效的使用到复合索引?
一条sql执行过长的时间,你如何优化,从哪些方面入手?
什么是最左前缀原则?什么是最左匹配原则?
影响mysql的性能因素
-
业务需求对MySQL的影响(合适合度)
-
存储定位对MySQL的影响
- 系统各种配置及规则数据
- 活跃用户的基本信息数据
- 活跃用户的个性化定制信息数据
- 准实时的统计信息数据
- 其他一些访问频繁但变更较少的数据
- 二进制多媒体数据
- 流水队列数据
- 超大文本数据
- 不适合放进MySQL的数据
- 需要放进缓存的数据
-
Schema设计对系统的性能影响
- 尽量减少对数据库访问的请求
- 尽量减少无用数据的查询请求
-
硬件环境对系统性能的影响
性能分析
MySQL Query Optimizer
- MySQL 中有专门负责优化 SELECT 语句的优化器模块,主要功能:通过计算分析系统中收集到的统计信息,为客户端请求的 Query 提供他认为最优的执行计划(他认为最优的数据检索方式,但不见得是 DBA 认为是最优的,这部分最耗费时间)
- 当客户端向 MySQL 请求一条 Query,命令解析器模块完成请求分类,区别出是 SELECT 并转发给 MySQL Query Optimize r时,MySQL Query Optimizer 首先会对整条 Query 进行优化,处理掉一些常量表达式的预算,直接换算成常量值。并对 Query 中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息(如果有),看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据 Query 进行写相应的计算分析,然后再得出最后的执行计划。
MySQL常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈:top,free,iostat 和 vmstat来查看系统的性能状态
性能下降SQL慢 执行时间长 等待时间长 原因分析
- 查询语句写的烂
- 索引失效(单值、复合)
- 关联查询太多join(设计缺陷或不得已的需求)
- 服务器调优及各个参数设置(缓冲、线程数等)
MySQL常见性能分析手段
在优化MySQL时,通常需要对数据库进行分析,常见的分析手段有慢查询日志,EXPLAIN 分析查询,profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。
性能瓶颈定位
我们可以通过 show 命令查看 MySQL 状态及变量,找到系统的瓶颈:
Mysql> show status ——显示状态信息(扩展show status like ‘XXX’)
Mysql> show variables ——显示系统变量(扩展show variables like ‘XXX’)
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行,包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
Explain(执行计划)
是什么:使用 Explain 关键字可以模拟优化器执行SQL查询语句,从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
能干吗:
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
怎么玩:
- Explain + SQL语句
- 执行计划包含的信息(如果有分区表的话还会有partitions)
各字段解释
-
id(select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序)
- id相同,执行顺序从上往下
- id全不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
- id部分相同,执行顺序是先按照数字大的先执行,然后数字相同的按照从上往下的顺序执行
-
select_type(查询类型,用于区别普通查询、联合查询、子查询等复杂查询)
- SIMPLE :简单的select查询,查询中不包含子查询或UNION
- PRIMARY:查询中若包含任何复杂的子部分,最外层查询被标记为PRIMARY
- SUBQUERY:在select或where列表中包含了子查询
- DERIVED:在from列表中包含的子查询被标记为DERIVED,MySQL会递归执行这些子查询,把结果放在临时表里
- UNION:若第二个select出现在UNION之后,则被标记为UNION,若UNION包含在from子句的子查询中,外层select将被标记为DERIVED
- UNION RESULT:从UNION表获取结果的select
-
table(显示这一行的数据是关于哪张表的)
-
type(显示查询使用了那种类型,从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL )
tip: 一般来说,得保证查询至少达到range级别,最好到达ref- system:表只有一行记录(等于系统表),是 const 类型的特例,平时不会出现
- const:表示通过索引一次就找到了,const 用于比较 primary key 或 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,将遍历全表找到匹配的行
-
possible_keys(显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段若存在索引,则该索引将被列出,但不一定被查询实际使用)
-
key
- 实际使用的索引,如果为NULL,则没有使用索引
- 查询中若使用了覆盖索引,则该索引和查询的 select 字段重叠,仅出现在key列表中
-
key_len
- 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
- key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
-
ref(显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值)
-
rows(根据表统计信息及索引选用情况,大致估算找到所需的记录所需要读取的行数)
-
Extra(包含不适合在其他列中显示但十分重要的额外信息)
- using filesort: 说明mysql会对数据使用一个外部的索引排序,不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
- Using temporary:使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率不错,如果同时出现using where,表明索引被用来执行索引键值的查找;否则索引被用来读取数据而非执行查找操作
- using where:使用了where过滤
- using join buffer:使用了连接缓存
- impossible where:where子句的值总是false,不能用来获取任何元祖
- select tables optimized away:在没有group by子句的情况下,基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
- distinct:优化distinct操作,在找到第一匹配的元祖后即停止找同样值的动作
case:
- 第一行(执行顺序4):id列为1,表示是union里的第一个select,select_type列的primary表示该查询为外层查询,table列被标记为,表示查询结果来自一个衍生表,其中derived3中3代表该查询衍生自第三个select查询,即id为3的select。【select d1.name…】
- 第二行(执行顺序2):id为3,是整个查询中第三个select的一部分。因查询包含在from中,所以为derived。【select id,name from t1 where other_column=’’】
- 第三行(执行顺序3):select列表中的子查询select_type为subquery,为整个查询中的第二个select。【select id from t3】
- 第四行(执行顺序1):select_type为union,说明第四个select是union里的第二个select,最先执行【select name,id from t2】
- 第五行(执行顺序5):代表从union的临时表中读取行的阶段,table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
慢查询日志
MySQL 的慢查询日志是 MySQL 提供的一种日志记录,它用来记录在 MySQL 中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的 SQL,则会被记录到慢查询日志中。
- long_query_time 的默认值为10,意思是运行10秒以上的语句
- 默认情况下,MySQL数据库没有开启慢查询日志,需要手动设置参数开启
查看开启状态
SHOW VARIABLES LIKE '%slow_query_log%'
开启慢查询日志
- 临时配置:
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;
也可set文件位置,系统会默认给一个缺省文件host_name-slow.log
使用set操作开启慢查询日志只对当前数据库生效,如果MySQL重启则会失效。
- 永久配置
修改配置文件my.cnf或my.ini,在[mysqld]一行下面加入两个配置参数
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
注:log-slow-queries 参数为慢查询日志存放的位置,一般这个目录要有 MySQL 的运行帐号的可写权限,一般都将这个目录设置为 MySQL 的数据存放目录;long_query_time=2 中的 2 表示查询超过两秒才记录;在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数,表示记录下没有使用索引的查询。
可以用 select sleep(4) 验证是否成功开启。
在生产环境中,如果手工分析日志,查找、分析SQL,还是比较费劲的,所以MySQL提供了日志分析工具mysqldumpslow。
通过 mysqldumpslow --help 查看操作帮助信息
- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
- 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log
- 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log
- 也可以和管道配合使用
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more
也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志
Show Profile 分析查询
通过慢日志查询可以知道哪些 SQL 语句执行效率低下,通过 explain 我们可以得知 SQL 语句的具体执行情况,索引使用等,还可以结合Show Profile命令查看执行状态。
- Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
- 默认情况下,参数处于关闭状态,并保存最近15次的运行结果
- 分析步骤
mysql> show profiles; +----------+------------+---------------------------------+ | Query_ID | Duration | Query | +----------+------------+---------------------------------+ | 1 | 0.00385450 | show variables like "profiling" | | 2 | 0.00170050 | show variables like "profiling" | | 3 | 0.00038025 | select * from t_base_user | +----------+------------+---------------------------------+
- converting HEAP to MyISAM 查询结果太大,内存都不够用了往磁盘上搬了
- create tmp table 创建临时表,这个要注意
- Copying to tmp table on disk 把内存临时表复制到磁盘
- locked
- 诊断SQL,show profile cpu,block io for query id(上一步前面的问题SQL数字号码)
- 日常开发需要注意的结论
- 是否支持,看看当前的mysql版本是否支持
mysql>Show variables like 'profiling'; --默认是关闭,使用前需要开启
- 开启功能,默认是关闭,使用前需要开启
mysql>set profiling=1;
运行SQL
查看结果
查询中哪些情况不会使用索引?
性能优化
索引优化
- 全值匹配我最爱
- 最佳左前缀法则,比如建立了一个联合索引(a,b,c),那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select
- is null ,is not null 也无法使用索引
- like “xxxx%” 是可以用到索引的,like “%xxxx” 则不行(like “%xxx%” 同理)。like以通配符开头(’%abc…’)索引失效会变成全表扫描的操作,
- 字符串不加单引号索引失效
- 少用or,用它来连接时会索引失效
- <,<=,=,>,>=,BETWEEN,IN 可用到索引,<>,not in ,!= 则不行,会导致全表扫描
一般性建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
- 少用Hint强制索引
查询优化
永远小标驱动大表(小的数据集驱动大的数据集)
slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id
当 B 表的数据集必须小于 A 表的数据集时,用 in 优于 exists
select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`
当 A 表的数据集小于B表的数据集时,用 exists优于用 in
注意:A表与B表的ID字段应建立索引。
order by关键字优化
- order by子句,尽量使用 Index 方式排序,避免使用 FileSort 方式排序
- MySQL 支持两种方式的排序,FileSort 和 Index,Index效率高,它指 MySQL 扫描索引本身完成排序,FileSort 效率较低;
- ORDER BY 满足两种情况,会使用Index方式排序;①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列
- 尽可能在索引列上完成排序操作,遵照索引建的最佳最前缀
- 如果不在索引列上,filesort 有两种算法,mysql就要启动双路排序和单路排序
- 双路排序:MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据
- 单路排序:从磁盘读取查询需要的所有列,按照order by 列在 buffer对它们进行排序,然后扫描排序后的列表进行输出,效率高于双路排序
- 优化策略
- 增大sort_buffer_size参数的设置
- 增大max_lencth_for_sort_data参数的设置
GROUP BY关键字优化
- group by实质是先排序后进行分组,遵照索引建的最佳左前缀
- 当无法使用索引列,增大 max_length_for_sort_data 参数的设置,增大sort_buffer_size参数的设置
- where高于having,能写在where限定的条件就不要去having限定了
数据类型优化
MySQL 支持的数据类型非常多,选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据,下面几个简单的原则都有助于做出更好的选择
-
更小的通常更好:一般情况下,应该尽量使用可以正确存储数据的最小数据类型。
简单就好:简单的数据类型通常需要更少的CPU周期。例如,整数比字符操作代价更低,因为字符集和校对规则(排序规则)使字符比较比整型比较复杂。 -
尽量避免NULL:通常情况下最好指定列为NOT NULL