面试官：你是怎么进行MySQL优化的？

数据库优化的方向

• 数据库设计：数据表设计遵循三范式，使用合适的数据类型，使用合适的存储引擎
• 适当创建索引
• 数据库扩展：数据库的分表分库，读写分离等
• SQL语句优化等

数据库设计

数据表结构

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

所有的字段都需要细分到不能再分的地步，如在一个地址字段中包含很多信息，如一个字段中包含省市两个信息，必须对他进行再次细分。

第二范式2NF： 每一列都要与主键相关，且不存在部分依赖

MySQL属于关系型数据库，如果某个字段和主键无关，那它将不应该出现在本表中，如果某个字段只和联合主键中的部分主键相关也是不合理的。

商品id	商品名	用户名
1	自行车	张三

比如如上情况是不合理的，用户名与主键无关。

商品id	订单id	订单价格	商品描述
1	1	12	好东西

联合主键为商品id和订单id，商品描述只和商品id相关，而不与订单id相关，不合理。

第三范式3NF：任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

如下图，用户信息都依赖于用户名，而用户名依赖于订单id。中间存在着传递依赖。

拆分后，使用用户id代替用户名，拆除传递依赖。

三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库！需求才是粑粑

数据字段优化

尽量使用可以正确存储数据的最小数据类型

更小的数据类型意味着更快，占用更少的磁盘，内存、缓存和处理时间

尽量使用整型表示字符串

因为字符集和校对规则，使处理字符比整型更复杂，比如：我们使用数据库内置的datetime类型存储时间而不是字符类型，我们使用整型存储ip而不是直接将ip字符串存到数据库中

尽可能使用not null

这个值是很烦人的，建字段时请尽量指定是否非空，NULL使得索引，统计，比较都变得更复杂，而且索引尽量不要创建到可以为null的字段上

恰当建立索引

• 在经常需要搜索的列上建立索引，可以加快搜索的速度
• 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构，在InnoDB存储引擎，默认给主键添加索引
• 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度
• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的
• 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询
• 在经常出现在WHERE子句中的列上添加索引，加快条件的判断速度
• 大数据量下建立索引，小数据量不建立索引。

数据库拓展

在数据库的并发访问量过大时或者数据库存储的数据量极大时，又或者实现数据库的高可用时，可以对数据库本身进行优化。

• 当数据库访问量过大时，可以为数据库进行集群配置。
• 当数据库存储的数据量过大时，可以为数据库进行分库分表。

数据库集群配置

参考 数据库主从复制实现集群配置

分库分表

参考 数据库基于MyCat实现分库分表

SQL语句优化

1. 查询尽量避免全表扫描，首先考虑在where、order by字段上添加索引

2. 避免在where字段上使用NULL值，所以在设计表时尽量使用NOT NULL约束，有些数据会默认为NULL，可以设置默认值为0或者-1

3. 避免在where子句中使用!=或<>操作符，Mysql只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE使用索引

4. 避免在where中使用OR来连接条件，否则可能导致引擎放弃索引来执行全表扫描，可以使用UNION进行合并查询

   select id from t where num = 30 union select id from t where num = 40;
   

5. 尽量避免在where子句中进行函数或者表达式操作

6. 最好不要使用select * from t，用具体的字段列表代替"*"，不要返回用不到的任何字段

7. in 和 not in 也要慎用，否则会导致全表扫描，如select id from t where num IN(1,2,3)如果是连续的值建议使用between and，select id from t where between 1 and 3；

8. select id from t where col like %a%;模糊查询左侧有%会导致全表检索，如果需要全文检索可以使用全文搜索引擎比如es，slor

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

参考数据库优化