mysql性能分析与优化
Hash索引的限制
Hash索引必须进行二次查找
Hash索引无法用于排序
Hash索引不支持部分索引查找，也不支持范围查找
Hash索引中Hash码的计算可能存在Hash冲突
为什么要使用索引
索引大大减少了存储引擎需要扫描的数据量
索引可以帮助我们进行排序以避免使用临时表
索引可以把随机I/O变为顺序I/O
索引优化策略
索引列上不能使用表达式或函数
前缀索引和索引列的选择性，索引的选择性是不重复的索引值和表的记录数的比值
联合索引
如何选择索引列的顺序
经常会被使用到的列优先
选择性高的列优先
宽度小的列优先
覆盖索引
优点：
可以优化缓存，减少磁盘IO操作
可以减少随机IO，变随机IO操作变为顺序IO操作
可以避免对Innodb主键索引的二次查询
可以避免MyISAM表进行系统调用
无法使用覆盖索引的情况：
存储引擎不支持覆盖索引
查询中使用了太多的列
使用了双%号的like查询
使用索引来优化查询
使用索引扫描来优化排序
通过排序操作
按照索引顺序扫描数据
索引的列顺序和Order By子句的顺序完全一致
索引中所有列的方向（升序，降序）和Order by子句完全一致
Order by中的字段全部在关联表中的第一张表中
模拟Hash索引优化查询
只能处理键值的全值匹配查找
所使用的Hash函数决定着索引键的大小
删除重复和冗余的索引
查找未被使用过的索引
更新索引统计信息及减少索引碎片
analyze table table_name
optimize table table_name 使用不当会导致锁表
如何获取由性能问题的sql
通过用户反馈获取存在性能问题的SQL
通过慢查日志获取存在性能问题的SQL
实时获取存在性能问题的SQL
使用慢查询日志获取有性能问题的SQL
slow_query_log 启动停止记录慢查日志 set global
slow_query_log_file 指定慢查询日志的存储路径及文件（日志存储和数据存储分开存储）
long_query_time 指定记录慢查日志SQL执行时间的伐值 （记录所有符合条件的SQL）
常用的慢查询日志分析工具 （mysqldumpslow）
汇总除查询条件外其他完全相同的sql，并将分析结果按照参数中所指定的顺序输出
mysqldumpslow -s r -t 10 slow-mysql.log
-s order(c,t,l,t,at,al,ar) 指定按哪种排序方式输出结果
c ：总次数
t ：总时间
l ：锁的时间
r ：总数据行
at，al，ar：t，l，r平均数
-t top 指定取前几条作为结束输出
常用的慢查日志分析工具（pt-query-digest）
pt-query-digest \
--explain h=127.0.0.1,u=root,p=p@ssWord \
slow-mysql.log
查询速度为什么会慢？
客户端发送sql语句给服务器
服务器检查是否可以在查询缓存中命中该sql
服务器端进行sql解析，预处理，再由优化器生成对应的执行计划
根据执行计划，调用存储引擎API来查询数据
将结果返回给客户端
查询缓存对sql性能的影响
优先检查这个查询是否命中查询缓存中的数据
通过一个对大小写敏感的哈希查找实现的
对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率
所以在这种情况下建议不要使用查询缓存
query_cache_type 设置查询缓存是否可用
DEMAND 表示只有在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存
query_cache_size 设置查询缓存的内存大小
query_cache_limit 设置查询缓存可用存储的最大值
加上SQL_NO_CACHE可以提高效率
query_cache_wlock_invalidate 设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit 设置查询缓存分配的内存块最小单位
MySQL 依照这个执行计划和存储i 引擎进行交互
这个阶段包括了多个子过程：
解析SQL，预处理，优化SQL执行计划
语法解析阶段是通过关键字对MySQL语句进行解析，并生成一颗对应的“解析树”
MySQL解析器将使用MySQL语法规则验证和解析查询
包括检查语法是否使用了正确的关键字
关键字的顺序是否正确等
预处理阶段是根据MySQL规则进一步检查解析树是否合法
检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等
语法检查全都通过了，查询优化器就可以生成查询计划了
SQL的解析预处理及生成执行计划
会造成MySQL生成错误的执行计划的原因
统计信息不准确
执行计划中的成本估算不等同于实际的执行计划的成本（MySQL服务器层并不知道哪些页面在内存中 哪些页面在磁盘上 哪些需要顺序读取 哪些要页面随机读取）
MySQL优化器所认为的最优可能与你所认为的最优不一样（基于其成本模型选择最优的执行计划）
MySql 不会考虑不受其控制的成本（存储过程，用户自定义的函数）
MySQL优化器可优化的SQL类型
重新定义表的关联顺序（优化器会根据统计信息来决定表的关联顺序）
将外连接转化成内链接 （where条件和库表结构等）
使用等价变换规则（5=5 and a>5）将被改写为a>5
优化count() ,min()和max() (select tables optimized away 优化器已经从执行计划中移除了该表，并以一个常数取而代之)
讲一个表达式转化为常数表达式
子查询优化 （子查询转换为关联查询）
提前终止查询
对in（）条件进行优化
如何确定查询处理各个阶段所消耗的时间
使用profile
减少查询所消耗的时间，加快查询的相应速度
set profiling =1 ；
启动profile ，这是一个session级的配置
执行查询
show profiles; 查看每一个查询所消耗的总时间的信息
show profile for query N; 查询的每个阶段所消耗的时间
如何确定查询处理各个阶段所消耗的时间
使用performance_schema
update `setup_instruments` set enabled = ' YES',TIMED='YES' WHERE NAME LIKE 'stage%';
UPDATE setup_consumers SET enabled='YES' WHERE NAME LIKE 'events%';
大表的数据修改最好要分批处理
1000万行记录的表中删除/更新100万行记录一次只删除/更新5000行记录，暂停几秒
特定的sql的查询优化
如何修改大表的表结构
pt-online-schema-change \
--alter = "MODIFY c VARCHAR(150) NOT NULL DEFAULT '' " \
--user=root --password=Password D=databaseName,t=tableName \
--charset=utf8 --execute
