Mysql数据库优化以及Btree索引，Hash索引介绍

通过Mysql的慢查询日志可以分析出哪些查询是主要的压力来源
1.首先通过show variables like 'slow_query_log’来查看慢查询日志是否开始(on表示开启,off表示关闭)

2.通过set global slow_query_log=ON开启慢查询日志

3.未使用索引的查询也可以被记录到慢查询日志中,on表示开启,off表示关闭

4.慢查询阈值(秒级),当查询时间大于设定的阈值时，记录日志

设置慢查询的阈值为0，记录所有sql的查询记录

5.查看慢查询日志存储路径

单条SQL优化
①使用show profile
可以通过以下修改开启set profiling=1
1. 查询开启工具后的每条SQL执行总统情况
show profiles
2.根据query_id查看某个查询的详细时间耗费
show profile for query 1;
3.查看CPU、IO等信息
show profile block io,cpu for query 1;
4.查询哪些开销花费了多少时间
SELECT state, SUM(duration) AS Total_R,
ROUND(100 * SUM(duration) / (SELECT SUM(duration) FROM information_schema.profiling WHERE query_id = 1), 2) AS Pct_R,
COUNT() as Calls, SUM(duration) /COUNT() AS “R/Call”
FROM information_schema.profiling
WHERE query_id = 1 GROUP BY state ORDER BY total_r DESC;
Creating tmp table:创建临时表
Sorting result:结果的排序
Sending data:发送数据
Creating sort index:当前的SELECT中需要用到临时表在进行ORDER BY排序。建议:创建适当的索引
②执行计划explain
table:对应的表
type:连接类型(system、const、eq_ref、ref、range、index、all)
possible_keys:可能使用的索引
key:实际使用的索引
Key_len:使用索引长度
rows:预计扫描行数
Extra:解析查询的额外信息(using index,using where,using temporary,using filesort)

连接类型(type)
ALL:全表扫描
Index:按索引顺序进行全索引扫描
range:按索引范围查找
eq_ref:是一种索引访问,MySQL知道最多只返回一条符合条件的记录
const\system:使用常量对主键索引或唯一索引扫描

选择优化的数据类型
如果计划在列上建立索引,尽量避免设计可为NULL的列
不同的存储引擎支持的索引不一样

InnoDB使用的是Btree索引,除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引

索引优化(Btree索引)
组合索引（例如一个表创建组合索引(last_name,first_name,email)）
索引生效的情况
匹配最左前缀:查找性为MILLER的人,只使用索引的第一列
explain select * from customer where last_name=‘MILLER’
全值匹配:全职匹配是指和索引中所有的列进行匹配,例如查找姓名为MARIA MILLER,email为MARIA MILLER@sakilacustomer.org的人。
Explain select * from customer where last_name=‘MILLER’ and first_name=‘MARIA’ and email=‘MARIA MILLER@sakilacustomer.org’
匹配列前缀：可以匹配某一列值的开头部分,例如查找以M开头的姓的人。
Explain select * from customer where last_name like ‘M%’
匹配范围值:查找姓大于等于WEINER的人。
Explain select * from customer where last_name >=‘WEINER’
精确匹配
不能跳过索引中的列【没有first_name列】
如果查询中有某个列的范围查询,则右边所有列都无法使用索引优化查找(first_name为范围查找,email列无效)
Expain select * from customer where last_name=‘MILLER’ and first_name>‘MARIA’ and email=‘MARIA MILLER@sakilacustomer.org’
小结论：索引列的顺序非常重要
B-Tree索引的限制：
如果不是按照索引的最左列开始查找，则无法使用索引
不能跳过索引中的列
如果查询中某个列的范围查询，则其右边所有列都无法使用索引优化查找

Mysql中只有Memory存储引擎才支持Hash索引
Hash索引，只有精确匹配索引所有列的查询才有效
Hash索引数据并不是按照索引值顺序存储的，所以也就无法用来排序
Hash索引只支持等值比较,包括=、in()、<=>,不支持任何范围查询
访问Hash索引的数据非常快，除非很多hash冲突

聚簇索引（每个索引树上挂着所有数据）
并不是所有的存储引擎都支持聚簇索引，Innodb支持聚簇索引，会把主键作为聚簇索引的key
聚簇索引的优点：
①可以把相关的数据保存在一起，减少IO次数
②数据访问更快，聚簇索引讲索引和数据保存在同一个BTree中
聚簇索引的缺点：
①更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
②插入速度严重依赖插入顺序