mysql--各层面优化方式总结

schema与数据类型优化：

        1.选择正确存储数据的最小类型。（ tinyint 与 int，占用更少的磁盘，内存，cpu缓存 ）

        2.简单的数据类型操作需要更少的cpu周期。（ 整形 > string，使用date，datetime代替字符串存储时间）

        3.尽量避免null

        4.使用BIGINT避免浮点计算不精确以及DECIMAL精确精算代价高的问题。

摘自--<<高性能MySQL>>

        5. varchar变长，对性能也有帮助。但是需要1-2个额外字节记录字符串长度。

        6.避免使用BLOB和TEXT类型

摘自--<<高性能MySQL>>

        7.使用ENUM代替字符串类型（mysql在存储枚举时十分紧凑，但是修改时需要使用alert table，对大表很不友好）

        8. TIMESTAMP存储时间戳只用了4个字节，其空间效率 > DATETIME 

        9. 避免太多的列

        10. 避免太多的关联（mysql限制关联不能超过61张表，单个查询尽量控制在12张表之内）

        11. 使用缓存表，汇总表来统计数据（精确度不高）

---

高性能索引优化：

        1.自定义hash索引

            如果使用b树存储url，存储的内容会很大，我们可以使用crc32做哈希，追加一个url_crc列。

            这样做性能会非常高，因为mysql优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找

        2. 前缀索引与索引的选择性

            对于很长的字段可以取其开始的部分字符做索引，大大节约索引空间

        3. 索引覆盖

            如果一个索引包含（覆盖）所有需要查询的字段的值，我们就称之为索引覆盖。（无需回表，极大的提升性能）

        4. 延迟关联（常用场景：大数据量下的分页操作）

            在查询的第一阶段mysql可以使用覆盖索引，在from子句的子查询中找到匹配的prod_id，然后根据这些prod_id 值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但是总比完全无法利用索引覆盖的好

        5. 使用索引扫描来做排序

            就是复合索引（a,b,c），查询的时候使用 where a = “XXX” order by b,c

        6. 清除冗余索引和重复索引

        7. 支持多种过滤条件

            （1）尽可能的将需要做范围查询的列放在索引的后面
            （2）使用in()的方式覆盖那些不在where子句中的列。通常来说不应该在选择性底的列上建立索引。但是也有例外。例如sex，假设其使用频率很高，还关联了很多其他搜索条件。我们依然可以在sex上建立复合索引，即使查询没有用到sex，我们也可以使用 and sex in（“男”，“女”）来绕过。 

        8. 减少索引和数据碎片（执行optimize，或者重新导入再导出）

---

查询性能优化：         

        1. 避免select * 

        2. 加上limit

        3. 使用缓存减少重复的查询

        4. 加索引

        5.切分查询

        6.分解关联查询

            好处：让缓存的效率更高，减少锁竞争，减少冗余记录的查询，本身效率也会提升

        7.使用 STRAIGHT_JOIN  倒转关联顺序，使查询进行进行更少的嵌套循环和回溯操作。（仅适用于inner join，尽量还是让优化器自己来判断吧）

        8.关联子查询 in() 优化 (把in变成inner join)   

            select * from a where a.id in ( select aid from b where b.aid = 1) 
            会被转换为：
            select * from a where exists ( select * from b where b.id=1 and a.id = b.aid） //因为没有a.id字段，所以mysql会先扫外层的全表。
            优化方式：
            select * from a where inner join b on b.aid = a.id where b.aid = 1

        9. union的优化

            union会把单个查询结果放在临时表中，然后重新读取临时表的数据完成union

            (select * from a order by time) union all (select * from b order by time) limit 20； //这样会扫a,b两个全表数据，然后再limit 20 
            优化为
            (select * from a order by time limit 20) union all (select * from b order by time limit 20) limit 20；

        10. min，max优化

            select min(actor_id) from actor where first_name = "liu";    //first_name没有索引，会扫全表
            优化为
            select actor_id from actor use index(primary) where first_name = “liu” limit 1；

        11. count优化

            id>5的城市总数 = 总城市数目 - id<5的城市总数

        12. 大数据量limit分页使用延迟关联来优化

---

服务器设置优化:

        innodb_buffer_pool_size: 用于缓存 索引 和 数据 的内存大小 (越多越好，一般设置 buffer pool 大小为总内存的  3/4 至 4/5)

        innodb_log_file_size ：该参数决定着mysql事务日志文件的大小（过大过小都不好）