18 SQL优化
1.SQL语句优化的一般步骤
1).了解各种SQL的执行频率
客户端连接成功后,可以通过SHOW [SESSION | GLOBAL] STATUS 命令来查看服务器状态信息;
也可以在操作系统上使用 mysqladmin extended-status 命令获得这些消息.
SESSION : 表示当前连接,作为默认值
GLOBAL : 自数据库启动至今
demo : SHOW STATUS LIKE 'Com_%' ;
Com_xxxx 表示每个xxx语句执行的次数.我们通常比较关心的是以下几个统计参数.
Com_select : 每次查询累计加1
Com_insert : 执行INSERT的次数, 对于批量插入的INSERT操作,只累加1;
Com_update : 执行UPDATE操作的次数.
Com_delete : 执行DELETE的次数.
上面的参数是所有存储引擎通用的.
下面这几个参数只针对InnoDB存储引擎,累加算法也略有不同.
Innodb_rows_read : SELECT 查询返回的次数
Innodb_rows_inserted : 执行INSERT操作插入的行数.
Innodb_rows_updated : 执行UPDATE操作更新的行数.
Innodb_rows_deleted : 执行DELETE操作删除的行数.
其中更新操作的计数,是对执行次数的计数,不论提交还是回滚都会累加.
对于事务型的应用,通过Com_commit 和Com_rollback可以了解事务提交和回滚的情况,对于回滚操作非常频繁的数据库,可能意味着应用编写存在问题.
此外,以下几个参数便于用户了解数据库的基本情况.
Connections : 试图连接MySQL服务器的次数.
Uptime : 服务器工作时间.
Slow_queries : 慢查询次数.
2).定位执行效率较低的SQL语句.
方式1:
通过慢查询日志定位执行效率较低的SQL语句.
用--log-slow-queries[=file_name]选项. 启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句日志文件.
方式2:
使用 SHOW PROCESSLIST 命令查看当前MySQL在进行的线程
包括线程的状态,是否锁表等.可以实时查看SQL的执行情况,同时对一些锁表操作进行优化.
3).通过EXPLAIN分析低效SQL的执行计划
通过EXPLAIN 或 DESC 命令来获取 MySQL如何执行SELECT 语句的信息.
包括SELECT语句执行过程中如何连接以及连接顺序.
返回的结果中,有很多信息,包括select_type , table , type , possable_keys , key , key_lenth , rows , Extra 等.
select_type : 表示SELECT的类型.常见的取值有
SIMPLE(不使用表连接/子查询)
PRIMARY(主查询,即外层的查询)
UNION(UNION中的第二个或者后面的查询语句)
SUBQUERY(子查询中的第一个SELECT)
table:输出结果集的表.
type:表示连接类型.性能由好到差如下:
system -> const -> eq_ref -> ref -> ref_or_null -> index_merge -> unique_subquery -> index_subquery -> range -> index -> all
system : 表中仅有一行,即常量表.
const : 单表中最多有一个匹配行 , 例如primary key或者unique index
eq_ref : 对于前面的每一行,在此表中只查询一条记录.简单来说,就是多表连接使用primary key 或unique index)
ref : 与eq_ref类似,区别在于使用的是普通索引.
ref_or_null : 与ref类似,区别在于包含对NULL的查询
index_merge : 索引合并优化
unique_subquery : IN的后面是一个查询主键字段的子查询.
range : 单表中的范围查询.
index : 对于前面的每一行,都通过查询索引来得到数据.
all : 对于前面的每一行,都通过全表扫描来得到数据.
possible_keys : 表示查询时,可能使用的索引.
key : 表示实际使用的索引.
key_len : 索引字段的长度.
rows : 扫描行的数量.
Extra : 执行情况的说明和描述.
4).确定问题并采取相应的优化措施
书中例子是a表的type为all,所以为WHERE中的列加了个索引,type变为ref
2.索引问题
1).索引的存储分类
MyISAM存储引擎的表,数据和索引时自动分开存储的,各自是独立的文件.
InnoDB存储引擎的表,数据和索引时存储在同一个表空间里面,但可以由多个文件组成.
MySQL中的存储类型目前只有两种:BTREE和HASH.
MyISAM和InnoDB都只支持BTREE索引;
MEMORY/HEAP可以支持HASH以及BTREE索引.
MySQL目前不支持函数索引,但是能对列的前面某一部分进行索引,称为前缀索引.
这个特性可以大大缩小索引文件的大小.
2).如何使用索引
查询要使用索引最主要的条件是
查询条件中需要使用索引关键字.
如果是多列索引,那么只有查询条件使用多列关键字最左边的前缀时,才可以使用索引,否则将不能使用索引.
a.使用索引
(1) 对于创建的多列索引,只要查询条件中用到了最左边的列,索引一般就会被使用.
例如对col1,col2(假设col1在前)建立联合索引,查询的WHERE条件中不必一定要用两个列都参与筛选,只要最左边的(这是是col1)参与条件筛选了,就可以使用到索引了.这就是索引的前缀特性.
(2) 对于使用LIKE的查询,后面如果是常量,并且只有%不在第一个字符,索引才可能会被使用.
另外,如果LIKE后面跟的是一个列的名字,那么索引也不会被使用.
(3) 如果对大的文本进行搜索,使用全文索引而不使用 LIKE '%...%'
(4) 如果列名是索引,使用column_name is null将使用索引.
b.存在索引,但不使用索引
(1) 如果MySQL估计 使用索引比全表扫描还慢,则不实用索引.
(2) 如果使用 MEMORY/HEAP 表并且WHERE条件中不使用'='进行索引列,那么不会用到索引. heap表只有在'='条件下才会使用索引.
(3) 用OR 分割开的条件,如果OR前的条件中的列有索引,而后面的列中没有索引,那么涉及到的索引都不会被使用.--简而言之,OR语句中所有条件必须都是用到了索引.
(4) 如果不是索引列的第一部分,复合索引,第一列必须参与条件筛选,才可能hi用到索引.
(5) LIKE 问题 , 参见a.(2)
(6) 如果列类型字符串,必须要有引号,否则MySQL会认为是数值型,不实用索引了.
c.查看索引使用情况
SHOW STATUS LIKE 'Handler_read%';
Handler_read_key代表了一个行被索引值读的次数.很低的值表明增加索引得到的性能改善不高,因为该索引并不经常使用.
Handler_read_rnd_next的值高意味着查询运行低效,并且应该建立索引补救.
这个值的含义是在数据文件中读下一行的请求数.如果正在进行大量的表扫描,该值将较高,说明索引不正确或写入的查询没有利用索引.
3).两个简单实用的优化方法
a.定期分析表和检查表.
分析表 :
ANALYZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name ...]
本语句用于分析和存储 表的关键字的分布,分析的结果将可以使得系统得到准确的统计信息.
在分析期间使用一个读取锁定对表进行锁定.这对于MyISAM/BDB/InnoDB表有作用.
对于MyISAM表 , 本语句与是一哦那个myisamchk -a相当.
检查表: CHECK TABLE tbl_name [,tbl_name ...] [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} ;
CHECK TABLE 对MyISAM 和 InnoDB表有作用.对于MyISAM表,关键字统计数据被更新.同时CHECK TABLE 也可以检查视图是否有错误.
b.定期优化表
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name ..];
如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表(含有VARCHAR,BLOB或TEXT列的表)进行了很多更改,
则应使用OPTIMIZE TABLE 命令进行空间碎片整理,实现表优化.
但是OPTIMIZE TABLE只对MyISAM/BDB/InnoDB表起作用.
4).常用SQL的优化
a.优化大批量插入数据
当用load命令导入数据的时候,适当设置可以提高导入的速度.
对于MyISAM存储引擎的表,可以通过以下方式快速导入大量数据.
ALTER TABLE tbl_name DISABLE KEYS ;
## here loading the data
LOAD DATA INFILE '/home/mysql/film_test.txt' INTO TABLE film_test ;
ALTER TABLE tbl_name ENABLE KEYS ;
DISABLE/ENABLE KEYS 用来关闭/打开MyISAM表中 非唯一索引 的更新.
对于导入大量数据到一个 空的MyISAM表时,默认就是先导入数据然后才创建索引,所以不用进行设置.
对于InnoDB 表, 可以有以下几种方式提高InnoDB表的导入效率.
(1)将导入的数据按照主键的顺序排列,可以有效提高导入数据的效率.
(2)在导入数据前执行SET UNIQUE_CHECKS=0 , 即关闭唯一性校验.
导入结束后 SET UNIQUE_CHECKS=1 恢复唯一性校验,可以提高导入效率.
(3)如果应用使用自动提交的方式,建议导入前执行SET AUTOCOMMIT=0,关闭自动提交;导入结束后再执行 SET AUTOCOMMIT = 1 .
b.优化INSERT 语句
(1) 使用INSERT INTO tbl_name VALUES(..),(..),(..)..;
这种比分开执行单个INSERT语句快很多,减少了客户端与数据库之间的连接/关闭等形式的资源消耗.
(2) 如果从不同客户端插入很多行,通过使用INSERT DELAYED 语句得到更高的速度.
DELAYED的含义是让INSERT语句马上执行,其实数据都被放在内存的队列中,并没有真正写入磁盘;
LOW_PRIORITY刚好相反,在所有其他用户对表的对鞋完成后,才进行插入.
(3) 利用建表中的选项,将索引文件和数据文件分在不同的磁盘上存放.
(4) 如果进行批量插入,可以增加bulk_insert_buffer_size的阀值.但是只对MyISAM表起作用.
(5) 从文本文件加载数据,使用LOAD DATA INFILE.通常比使用很多INSERT语句快20倍.
c.优化GROUP BY 语句
如果查询包括GROUP BY,但用户想避免排序结果的消耗,可以使用 ORDER BY NULL ;
例如 EXPLAIN SELECT id , SUM(money) FROM sale GROUP BY id ORDER BY NULL ; -- 这样就不使用 filesort 了,而filesort往往很耗时.
d.优化ORDER BY语句
可以使用索引代替ORDER BY子句,就不需要额外排序了.
同时满足下面三个条件的SQL语句,可以使用索引代替ORDER BY:
WHERE 条件和ORDER BY使用相同的索引
并且 ORDER BY的顺序与索引顺序相同
并且 ORDER BY的字段 都是升序/降序.
Demo :
SELECT * FROM t ORDER BY key_part1,key_part2 ,.. ;
SELECT * FROM t WHERE key_part1 = 1 ORDER BY key_part1 DESC , key_part2 DESC ;
SELECT * FROM t ORDER BY key_part1 DESC , key_part2 DESC ;
而以下几种情况不能使用索引,demo:
# order by的字段混合了 ASC 和DESC
SELECT * FROM t ORDER BY col1 DESC , col2 ASC ;
# 查询行的关键字 与 ORDER BY 中所使用的不相同
SELECT * FROM t WHERE col2 = xx ORDER BY col1 ;
# 对不同关键字使用ORDER BY
SELECT * FROM t ORDER BY key1 , key2 ;
e.优化嵌套查询
子查询使用SELECT语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中.
使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的SQL操作,同时也可以避免事务或者表锁死,写起来也容易.
但是,有些情况下,子查询可以被更有效率的连接(JOIN)替代.
例如:
EXPLAIN SELECT * FROM sales WHERE company_id NOT IN (SELECT id FROM company) ;
可以替换为
EXPLAIN SELECT * FROM sales LEFT JOIN company ON sales.company_id = company.id WHERE sales.company_id IS NULL ;
连接之所以更高效一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作.
f.优化 OR 条件
增加OR条件的索引.--这 tm 不太好吧
这种方式,对联合索引不起作用. --也tm 没给解决方式.
g.使用SQL提示
SQL HINT 是优化数据库的一个重要手段,就是在SQL语句中加入一些认为的提示来达到优化操作的目的.
例如: SELECT SQL_BUFFER_RESULTS * FROM ..
这个语句强制MySQL生成一个临时结果集.结果集生成后,表上的锁定被释放.可以尽快释放资源.
以下是MySQL中常用的一些SQL提示.
(1) USING INDEX
在查询语句中表名后面,添加USE INDEX 来提供希望MySQL去参考的索引列表,就可以让MySQL不再考虑使用其他可用索引.
例如: EXPLAIN SELECT * FROM sales USE INDEX (idx_sales_id) WHERE id = 3 ;
(2) IGNORE INDEX
如果用户只是想忽略某个或者多个索引则可以使用IGNORE INDEX作为HINT.
例如: EXPLAIN SELECT * FROM sales IGNORE INDEX(idx_sales_id) WHERE id = 3 ;
(3) FORCE INDEX
为强制MySQL使用一个特定索引.这是MySQL留给用户的一个自行选择执行计划的权利.
例如: EXPLAIN SELECT * FROM sales FROCE INDEX (idx_sales_id) WHERE id > 0 ;
