《深入浅出Mysql》笔记---优化
一 SQL语句优化
1.1 使用 show status 命令了解sql执行频率
mysql> show session status 查询当前连接统计结果
mysql> show global status 查询自数据库上次启动至今统计结果
或者在操作系统的终端执行
shell> mysqladmin extended-status
可以通过like语句来查询一些特定的内容
mysql> show global status like "Com_%";
上次启动以来的每个Com_xxx语句执行的次数
Com_select: 执行select查询的次数
Com_insert: 执行insert的次数
Com_update: 执行update的次数
Com_delete: 执行delete的次数
以上针对所有存储引擎表操作记录，专门针对某一些存储引擎的如下：
Innodb_rows_read
Innodb_rows_insert
Innodb_rows_update
Innobd_rows_delete
通过上面这些数据的比较可以判断数据库是写为主，还是查询为主
事务信息 可通过
Com_commit
Com_rollback
来了解提交和回滚情况。如回滚操作很频繁，说明应用编写存在问题。
其他重要参数，可展示数据库基本情况句子：
Connections： 试图连接Mysql数据库的次数
Uptime： 服务器工作时间
Slow_queries： 慢查询的次数
1.2 定位执行效率比较低的sql语句
两种方法：
1）慢查询日志定位
用--log-slow-queries[=filename]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒得sql语句的日志文件。
2）show processlist
查看MySQL在进行的线程，包括线程状态，是否锁表等，可实时查看sql执行情况，并对一些锁表操作优化。
1.3 通过Explain分析低效SQL执行计划
通过上面步骤，查到低效sql语句后，可通过 explain和desc命令获取mysql如何执行select语句信息，如表连接。
explain
select sum(money)
from sales a, company b
where a.company_id = b.id and a.year = 2006
*************************** 1. row *****************
id: 1
select_type: SIMPLE
table: user
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 0
Extra: const row not found
1 row in set (0.00 sec)
ERROR:
No query specified
每一列的含义说明
select_type:
表示select的类型
（SIMPLE=>简单表，不使用表连接或者子查询,PRIMARY=>主查询,
外层的查询，UNION=>UNION中的第二个或者后面的查询语句，
SUBQUERY=>子查询中的第一个select）
table: 输出结果集的表
possible_keys: 表示查询时，可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
rows; 扫描的行的数量
Extra: 执行情况的说明和描述
type:
表示表的连接类型，性能有好到差的链接类型为：
system=>只有一行，也就是常量表;
const=>单表中最多有一个匹配行，例如primary key或者unique index；
eq_ref=>对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index；
ref=>与eq_ref类似，区别在于不是使用primay key或者unique index而是使用普通的索引；
ref_or_null=>与ref类似，区别在于条件中包含对null的查询；
index_merge=>索引合并优化；
unique_subquery=>in的后面是一个查询主键字段的子查询；
index_subquery=>与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询；
range=>单表中的查询范围；
index=>对于前面的每一行，都通过查询索引来得到数据；
all=>对于前面的每一行，都通过全表扫描来得到数据
二 索引问题
2.1 索引存储分类
MyISAM存储索引 表数据 和 索引 自动分开存储，各自独立文件
InnoDB存储引擎 表数据 和 索引 存储在同一表空间，但可有多个文件组成
Mysql存储类型只有两种，BTREE和HASH，具体情况和表的存储引擎有关
MyISAM和InnoDb存储引擎 都支持 BTREE索引
MEMORY/HEAP存储引擎 支持HASH，BTREE索引
mysql不支持函数索引，但是能对队列的前面的某一部分进行索引，例如name字段，可以只取name的前四个字符来进行索引。
2.2 使用索引
对相关列使用索引是提高select性能的最佳途径。
使用索引的条件：
a、查询条件中有索引关键字，
b、多列索引只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。
2.2.1 使用索引
一下情况中会使用到索引：
（1） 多列索引，只要查询的条件中用到了最左边的列，索引一般就会被使用
（2） 对于使用like查询，后面如果是常量，只有%号不在第一个字符时，
索引才可能被用到 比如 like "%3" 不会用索引， like "3%"就会走索引
（3） 对大文本进行搜索的时候，使用全文索引，而不是使用 like '%...%'
（4） 如果列名是索引，使用column_name is null将使用索引，
例如： select * from aaa where name is null（name是索引列）
2.2.2 存在索引但不使用
在下列情况下，虽然mysql存在索引，但是并不会使用到索引
（1）如果Mysql估计使用索引比全表扫描更慢，则不使用索引。
例如如果列key_1 均匀分布在1和100之间，那么查询 select * from table where key_1 > 1 and key_1 < 90;
（2）如果使用MEMPRY/HEAP表并且，where条件中不使用“=”进行索引列，
那么不会用到索引。heap表只有在使用“=”的时候，才使用索引
（3）用or隔开的条件，如果or前面的列中有索引，而后面的列中没有索引，
那么涉及的索引都不会被用到（or中有一个条件中的列没有索引就用不到索引）
（4）如果不是索引列的第一部分（复合索引的第一部分）
（5）如果like是%开始的
（6）如果列类型是字符串，那么一定记得在where条件中把字符常量值用引号引起来，
否则即便是这个列上有索引，也不用用到（比如name字段是字符串的，却写了name=123。要改成“123”）
2.3 查看索引使用情况
Handler_read_key
代表一个行被索引值读的次数。
如果索引正在工作，值将很高，很低表明增加索引性能改善不高。
Handler_read_rnd_next
代表数据文件中读下一行的请求数。
查看方法：
show status like 'Handler_read%
值高则查询效率低，应建立索引补救。
如果正在进行大量的表扫描，Handle_read_rnd_next的值较高，则通常说明表索引不正确或者写入的查询没有利用索引。
3 两个简单优化方法
3.1 定期分析表和检查表
1）分析表语法
analyze [local | no_write_to_binlog] table tab1_name [, ta1_name] ...
分析和存储表的关键字分布，得到更准确地统计信息，使sql正确执行。
如果用户感觉实际执行计划并不是与预期的执行计划，执行一次分析表可能会解决问题。
在分析期间，使用一个读取锁对表进行锁定，这对于MyISAM，BDB和InnoDb表有作用。
对于MyISAM表，与使用myisamchk -a 相当。
mysql> analyze table user;
+------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+---------+----------+----------+
| mysql.user | analyze | status | OK |
+------------+---------+----------+----------+
1 row in set (0.05 sec)
2）检查表语法
check table tab1_name [,tab1_name] ... [option] .. \
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
作用：
检查一或多表错误。对MyISAM和InnoDB表有用，对MyISAM表，关键字统计数据被更新。
例如：
mysql> check table user;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| mysql.user | check | status | OK |
+------------+-------+----------+----------+
1 row in set (0.00 sec)
check table也可检查视图错误，如：视图定义被引用表不存在。
3.2 定期优化表
语法：
optimize [local | no_write_to_binlog] table tab1_name [, tab1_name] ...
适用范围：
a、删除了表的一部分
b、对含有可变长度行表（varchar，blob，text列的表）进行了很多更改。
作用：
将表空间碎片合并，消除删除或者更新造成的空间浪费。只适用MyISAM，BDB和InnoDb表。
mysql> optimize table user;
+------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+----------+----------+----------+
| mysql.user | optimize | status | OK |
+------------+----------+----------+----------+
1 row in set (0.01 sec)
4 常用SQL优化
4.1 大批量插入数据优化
当用load命令导入数据的时候，适当的设置可以提高导入的速度
4.1.1 MyISAM引擎表插入大数据
alter table tab_name disable keys;
load the data
alter table tab_name enable keys;
以上是打开或者关闭MyISAM表非唯一索引的更新。
注意：
导入非空数据表，上面方法很有效，但导入空表，索引是数据导入完毕之后才去创建的，所以没有影响。
4.1.2 对InnoDB表数据导入大数据
（1）InnoDB类型表按主键顺序存储，故导入数据按主键顺序排列，可有效提高导入效率；
（2）导入数据前，关闭唯一性校验set unique_checks=0， 导入结束后设为 1 开启，可提高效率；
（3）如应用使用自动提交，导入数据的时候执行 set autocomment=0关闭自动提交，导入后打开；
4.2 优化insert
（1）如从同一客户插入很多行，使用多值表insert语句，将缩短客户端与数据库间链接、关闭等资源消耗，使效率快
如：
insert to test values(1,2),(1,3),(1,4)....
（2）如从不同客户插入多行，使用insert delayed语句得更高速度。
delayed延迟insert语句执行，数据放在内存队列，并没有真正写入磁盘，这比每一条数据分别插入快得多。
low_priority 相反，所有其他用户对表读写后才插入。
3）将索引文件和数据文件 分不同磁盘存放；
4）批量插入增加 bulk_insert_buffer_size 变量值提高速度，但只对MyIsAM表使用；
5）文本文件装载表，使用load file insert比通常sql语句快20倍。
4.3 优化group by
查询含group by但要避免排序结果的消耗，可指定order by null禁止排序
如：
select id, sum(money) from sale2 \
group by id \
order by null
4.4 优化order by
可使用索引满足一个order by语句。
条件：
where条件和order by使用相同索引；
order by顺序和索引顺序相同；
order by字段都升或降序
如：
order by key1,key2
where key1=123 order by key1 desc, key2 desc
order by key1 desc, key2 desc
以下情况不行
order by key1 asc, key2 desc 混合使用ASC和DESC
where key2 = 1 ， order by key1 查询关键字和排序的不一样
order by key1，key2 对不同关键字使用排序
4.5 优化嵌套查询
有时子查询可被更有效的join代替
比如
select * from a where b_id not in(select id from b)
换成
select * from a left join b on a.b_id = b.id
where a.b_id is not null
4.6 优化OR
or每一个条件都使用索引
但若or几个条件是复合索引元素，则无优化效果
4.7 使用SQL提示
在sql中加入一些人为提示进行优化。
例如：
select sql_buffer_results * from
指示MySql生成临时结果集，所有表锁被释放。
这能解决表锁问题，或要花长时间将结果传给客户端，因为资源被快速释放。
1）use index
查询语句表名后加use index指定mysql参考的索引列表，不考虑其他索引。
select * from a use index(ind_a_id) where id = 1
2）ignore index
若打算忽略一多个索引，可用ignore index为hint。
select * from a ignore index(ind_a_id) where id = 1
3）force index
强制MySQL使用特定索引，可在查询中使用。
