mysql sql层面的优化

索引优化：

mysql中索引是在存储引擎层实现的。
索引可以包含一个或者多个列 ，多列情况下 顺序很重要，索引只能高效的使用索引的最左前缀列。

B+tree索引
btree意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。
btree索引适用于全键值、键值范围或者键前缀查找。其中键前缀只适用于根据最左前缀的查找。
btree有效的场景：
全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列。
btree在order by中也可以使用只要order by满足上面的场景。
btree的限制：
如果不是按照索引的最左列开始查找，则无法使用。
不能跳过索引中的列
如果查询中有范围查询 比如like in 右边的列都无法使用索引了

hash索引
基于hash表实现 只有精确匹配索引所有列的查询才有效。
在mysql中 只有memory引擎显式支持hash索引。
hash索引查找很快 但是有限制：
hash索引只包含hash值和行指针 不存字段值，所以也要读取行。
hash索引不能用于orderby 因为他不是按照顺序存储的。
hash索引不支持部分索引列匹配查找，因为这个是基于所有列算出来的。
hash索引只支持等值比较
有hash冲突 就要遍历链表中的所有行指针，如果hash冲突很多，索引维护的代价就会很高。

不要使用sha1()和md5()作为hash函数，这俩hash值太长 浪费空间 还慢。
innodb自定义hash函数 使用hash索引的话 需要自己去写触发器 维护hash索引。
还有全文索引和空间数据索引。

索引好处：
避免服务器排序 和创建临时表。
将随机io变为顺序io。

小表没必要建索引， 中大型表需要，特大的表也不用索引，用表分区。

怎样创建出高性能的索引

独列索引

在字段那一侧避免有任何的计算。
前缀索引
一些字符串太长了可以创建前缀索引 通过公式算出选择性最大的方案
计算公式 count(distinct(列名))/count(*)
选择性接近百分之三十就可以用了
创建语法：
alter table test add key(city(7));这里使用city前七个字符 作为前缀索引。
前缀索引局限性：
无法使用前缀索引做group by 和order by 和覆盖扫描。
没有后缀索引 存的时候可以倒腾一下顺序
多列索引
有 a b c 3个索引 老版本不会进行索引合并 要想都用到 除非用union all
新版本的 直接可以把这三个索引合并 查询的时候 都能用上。
将选择性最高的列放到所以最前列
组簇索引
组簇索引不是一个单独的索引类型，而是一种数据存储方式。
主键是顺序的，记录会续在上一条的后面 避免了大量的io操作，但是在高并发下 主键的上界和auto_increment锁机制会成为热点，高并发用redis做自增主键会比较好。
覆盖索引
一个索引包含（或者说覆盖）所有需要查询的字段的值，就叫做覆盖索引。
说白了 select 索引 就要覆盖索引。

排序的时候使用场景必须满足的条件：
1所有列方向一样 都是desc或者都是asc
2 如果关联多张表，order by 引用的字段必须全为第一张表

查询性能优化

只查需要的记录 避免select *
分解关联查询，在应用层面 把一个大查询拆分成几个小查询
mysql 优化器会帮我们做哪些优化：
重新定义关联表的顺序、将外连接转化成内连接、使用等价变化规则、优化count() min() max（）、预估并转化为常数表达式、覆盖索引扫描、子查询优化、提前终止查询、等值传播，等等。
mysql关联执行的策略
mysql对任何关联都执行嵌套循环关联的操作，即mysql先在一个表循环中取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。
子查询尽可能使用关联查询来代替。

limit优化
如果数据量很大可以记录上一次分页的最大id where id>lastid limit 20;
或者用索引：
Select *from temp a join(select id from temp limit 10000,5) b on a.id =b.id
回表 ：
建索引的时候 比如name 这个最后面叶子节点存储的是主键 ，先查name列B+tree 找到主键 再根据主键的B+tree去找
索引下推 ：
多个条件的时候 直接在存储引擎 直接过滤掉多个条件 ，以前是一层一层过滤的 前提是这多个条件建了组合索引

分区表：

Create table 加个parrern by 分而治之
分区表 名称前面带# 可以把表分为多个文件存储
分区和分库分表的区别
分区表主要是水平切分
引用场景：
表太大，或者部分热点数据 把热点数据摘出来
好维护批量删 就直接删除分区
能够避免某些特殊的瓶颈（索引互斥访问）
限制 1024 个分区 5.7以后 8196 限制的原因：跟liux相关
如果分区中有主键或者唯一索引的列，那么所有主键和唯一索引的列都要包含进来
从分区文件大小不能区分数据放在哪个分区了
没有适配的分区 数据插入不进去
按天分区 按月分区 按年分区 看官网
分区表无法用外键约束
分区表原理：
更新的时候最好不要更新分区列