Mysql优化系统性总结

最近面试被问到sql优化的问题，虽然有准备，但是总感觉回答的不好，不系统。

特此总结

优化主要涉及一下几个方面

1.表结构的设计优化

2.索引的使用

3.关键字、sql语句优化

4.数据库锁定机制

我们知道数据库操作，不考虑网络延迟的情况下，90%的时间都用在了I/O上面，减少I/O次数就成了关键

另一个比较耗时的是CPU计算（如order by  group by distinct 等cpu处理内存中数据的比较运算）

上面的前三条都是为了解决上面2个问题

第四条是为了解决sql 增删改时的锁冲突

现在正式开始

        一：表结构

        MySql是基于行存储的数据库，而查询的时候是以page(block)为单位，如果每条记录所占的空间减小，那么每个单元中包含更多的数据，即同样的数据查询，需要更少的I/O次数

    措施

    1.适当分表（一张表中，包含了太多的字段甚至是text类型或很多字段并不常有，就可以使用纵向分表：比如我们项目中的用户信息表就分成了 基本常用信息表user_user   第三方绑定表user_profile  用户扩展表 user_expand 用户账户表  用户分销表）

    2.适度冗余（看似与上面冲突，但是在一些需要频繁使用，通过join 大表得到的独立小字段的时候，就非常有必要了）

    3.尽量使用NOT NULL字段（null比较特殊，极大的影响了索引的效率，以及数据字段的区分度）

    4.字段选择

            小数不到万不得已避免使用 DOUBLE 或 DECIMAL 例如：存钱相关的数据，最好*100以整数形式保存，这样精度最高，速度最快，空间最小    

            时间类型：尽量使用TIMESTAMP，更小的空间占用

            定长字段建议使用char类型

    4.一般情况下请遵循三范式原则（1.列字段的原子性 2.行内数据字段与主键之间的关联性 3.字段与主键直接依赖）

    二：索引

    关于索引的介绍，请查看另一片专门的文章，下面只涉及用法

    索引的坏处

        1.增删数据都需要额外的开销

        2.索引需要额外的空间保存

所以索引不是越多越好

索引适用于数据量大，而大多数操作是查询的情况（一般业务都是这个特点）

为更好说明举例如下：

在图书馆中找一个《mysql性能调优与架构设计》的书

告诉对方：“帮我确认一本数据库类别的讲述 MySQL 的叫做《MySQL性能调优与架构设计》的书是否在藏”，结果又会如何呢?只能一个大类区域一个大类区域的去寻找“数据库”类别，然后再找到 “MySQL”范畴，再看到我们所需是否在藏。这样就必须到每一个大类中一个一个去寻找。

        1.应该尽可能在查询条件中使用索引过滤数据，回表只是去找额外的字段

如果我们是这样说的：“帮我确认一本讲述 MySQL 的数据库范畴的计算机丛书，叫做《MySQL性能调优与架构设计》，看是否在藏”。如果这位朋友并不知道计算机是一个大类，也不知道数据库属于计算机大类，那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中，然后从包含 “MySQL” 书籍中再看有哪些是“数据库”范畴的(有可能部分是讲述PHP或者其他开发语言的)，然后再排除非计算机类的(虽然并没有必要)，然后才能确认

        2.联合索引使用时，顺序就特别重要，遵循最左匹配原则

      3.索引应建立在有较大的区分度的字段上，否则并不优于全表扫描（比如在学校里说，我要找个男生！）

      4.注意一些使索引无效的sql写法（1.字段类型转换 2.函数  3.字段运算 4.like %写在前面 5.联合索引必须最左匹配 6 用union代替or  or使索引无效  7. where 高选择性字段要在前  8.order by/group by 字段使用索引 使用索引本身的排序）

      5.在使用频繁的关联字段上建立索引

    三、sql优化及注意事项

    1.count(*)/count(column)

            1）数据库对count(*)有优化速度快

            2）count(*) 查询全部数据行数   count(column)查询不为null的数据行数

    2.select a,b from .. /select a,b,c from ...

            1）在不适用索引情况下，二者查询在数据库表需要查询的数据量是一致的（数据库是按照行保存数据，已block为基本I/O单元，一般4k/8k..  每个单元存储多行，每行存储所有字段，取一个还是多个实际在数据库表中是一样的）

            2）在使用到索引的情况下，效果可能不一致，比如a、b上面有建立索引，这样就不需要回表查询直接返回，而c需要回表，效率大大提升

    3.索引优化排序：索引数据实际上是有序的，如果我们的需要的数据和某个索引的顺序一致，而且我们的查询又通过这个索引来执行，那么数据库一般会省略排序操作，而直接将数据返回

    4.避免使用select* 虽然这样写不影响I/O量，但是在有order by时会极大影响效率

    5.在多个表进行分页数据查询的时候，最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间

    6.使用正确的类型，避免类型转换（虽然数据库可以帮我们转换，但是大大降低了效率，无法使用索引）

    7. 规避大事务

    8. 不让mysql干多余的事（如计算）

    9.limit分页优化问题