面试题：你对mysql优化了解多少？

从增加索引、设计数据库、水平垂直分库分表、集群、sql优化这5个方面来优化。

一、增加索引

1、索引检索为什么快（好处）？

(1)关键字相对于数据本身，数据量小。

(2)关键字是有序的，二分查找可快速确定位置。

(3)在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间

2、缺点：

(1)创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

(2)索引需要占用物理空间，数据量越大，占用空间越大

3、什么时候需要创建索引

(1)主键自动建立唯一索引

(2)频繁作为查询条件的字段应该创建索引

(3)查询中排序、统计或者分组的字段

4、什么时候不需要创建索引

(1)频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件

(2)表记录太少，不需要创建索引；

(3)经常增删改的表；

(4)数据重复且分布平均的字段。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

5、索引使用场景

(1)where

(2)order by

(3)join on

(4)索引覆盖

6、避免索引失效

二、设计数据库

1、字段设计

(1)原则：尽量使用整型表示字符串

(2)原则：尽可能选择小的数据类型和指定短的长度

(3)原则：尽可能使用 not null ：非null字段的处理要比null字段的处理高效些！且不需要判断是否为null。null在MySQL中，不好处理，存储需要额外空间。

2、范式

(1)第一范式1NF：字段原子性

字段原子性，字段不可再分割。

(2)第二范式：消除对主键的部分依赖

对主键的部分依赖：某个字段依赖复合主键中的一部分。

解决方案：新增一个独立字段作为主键。

(3)第三范式：消除对主键的传递依赖

(4) 范式的优点：

    1）范式化的数据库更新起来更加快；

    2）范式化之后，只有很少的重复数据，只需要修改更少的数据；

    3）范式化的表更小，可以在内存中执行；

    4）很少的冗余数据，在查询的时候需要更少的distinct或者group by语句。

    范式的缺点：

    1）范式化的表，在查询的时候经常需要很多的关联，因为单独一个表内不存在冗余和重复数据。这导致，稍微复杂一些的查询语句在查询范式的schema上都可能需要较多次的关联。这会增加让查询的代价，也可能使一些索引策略无效。因为范式化将列存放在不同的表中，而这些列在一个表中本可以属于同一个索引。

(5)反范式的优点：

    1）可以避免关联，因为所有的数据几乎都可以在一张表上显示；

    2）可以设计有效的索引；

    反范式的缺点：

    1）表格内的冗余较多，删除数据时候会造成表有些有用的信息丢失。

3、选择合适存储引擎

因为MyISAM相对简单所以在效率上要优于InnoDB.如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。

如果系统读少，写多的时候，尤其是并发写入高的时候。InnoDB就是首选了。

三、水平垂直分表分库。

1、水平分表:

(1)场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。

(2)分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。

 2、垂直分表:

(1)场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起。

(2)分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。

3、水平分库

(1)场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

(2)分析：库多了，io和cpu的压力自然可以成倍缓解。

4、垂直分库

(1)场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。

(2)分析：随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。

四、集群

1、主从复制

2、读写分离

五、SQL优化

1、limit offset,rows

尽量保证不要出现大的offset，比如limit 10000,10相当于对已查询出来的行数弃掉前10000行后再取10行，完全可以加一些条件过滤一下（完成筛选），而不应该使用limit跳过已查询到的数据。这是一个offset做无用功的问题。对应实际工程中，要避免出现大页码的情况，尽量引导用户做条件过滤。

2、select * 要少用

即尽量选择自己需要的字段select，但这个影响不是很大，因为网络传输多了几十上百字节也没多少延时，并且现在流行的ORM框架都是用的select *，只是我们在设计表的时候注意将大数据量的字段分离，比如商品详情可以单独抽离出一张商品详情表，这样在查看商品简略页面时的加载速度就不会有影响了。

3、单表和多表查询

多表查询：join、子查询都是涉及到多表的查询。如果你使用explain分析执行计划你会发现多表查询也是一个表一个表的处理，最后合并结果。因此可以说单表查询将计算压力放在了应用程序上，而多表查询将计算压力放在了数据库上。

现在有ORM框架帮我们解决了单表查询带来的对象映射问题（查询单表时，如果发现有外键自动再去查询关联表，是一个表一个表查的）。

4、count(*)

在MyISAM存储引擎中，会自动记录表的行数，因此使用count(*)能够快速返回。而Innodb内部没有这样一个计数器，需要我们手动统计记录数量，解决思路就是单独使用一张表。

参考文章：

https://www.cnblogs.com/littlecharacter/p/9342129.html

https://www.nowcoder.com/discuss/150059

https://www.cnblogs.com/littlecharacter/p/9084291.html