MySQL优化思路

数据库优化思路

1、数据库为什么要优化

1.1场景

​ 假设一个场景，你正在使用一个APP搜索东西，搜索一个问题去后台查询1分多钟返回了结果，那时候你心里是什么样子，是不是快崩溃了，是不是想砸手机，下次还愿意用这个APP吗？

所以要对数据库进行优化来提高APP的响应时间，从而让用户心里得到安慰，这样才会让用户长期使用我们的APP。

1.2什么时候去优化

那么我们怎么会发现数据库变慢了，或者说什么时候去优化呢？

a、用户的反馈

​ 上面的背景就是基于用户的反馈，这也是最重要的一点，因为APP开发就是为用户使用的，用户反馈APP响应速度很迟钝，那么我们就有必要去聆听用户的反馈，进一步去优化。

b、日志分析

​ 很多数据库连接软件都会在执行完sql之后有执行耗时，数据很少你可能不会发现什么问题，觉得点了一下就返回了呀，其实数据很多的话，也需要等好久才返回，而且执行完sql之后下面会有执行多长时间。

c、机器性能分析

​ 现在我们的服务都部署在服务器上，服务器会监控哪些项目很耗时，会有告警，我们也可以查看机器的IO、CPU等使用量，看哪些服务比较费性能。

2、怎么优化

上面说完了什么时候去优化，下面该介绍怎么去优化了。一谈到数据库优化可能第一反应就是建立索引，对，建立索引只是数据库优化的一部分，下面我会带你们分析。

2.1选择合适的dbms

​ 现在的数据库分为关系型数据库和非关系型数据库，关系型数据库使用较多的有：Mysql、Oracle、Sql Server等，非关系型数据库使用较多的有：redis、mongo、es等；如果需要用到事务你还只能使用innoDB引擎的Mysql，因为MyISAM引擎不支持事务；如果存储的数据没有固定的数据结构，你这时就要考虑非关系型数据库了，所以在项目开始之前选择一个合适的数据库比选择一个不合适后期再优化的数据库要事半功倍。

2.2合理的建立表结构

​ 当数据库确定好之后，如果选择的Mysql、Oracle、Sql Server这类的关系型数据库，你肯定听说过数据库的三大范式，但是不一定非要满足数据库的三大范式，要根据场景随机应变。为什么这么说呢，首先三大范式可以帮助我们建立良好的表结构，减少冗余字段，后期也好排查问题。但是如果业务大多数是聚合查询，多表关联，子查询嵌套子查询，一个sql几百行都不解气的这种情况，劝你一个表多建一些字段也未尝不可，虽然有冗余字段，但是可以减少大量的子查询，速率绝对会有所提升。还有如果你提前已经知道某字段的长度范围，那么就尽量给这个字段规定好长度，能用char就别用varchar。

​ 扩展三大范式：

1NF:每个列都不可再分

上面的表就不符合第一范式，是因为联系方式还可以拆分，所以不满足第一范式，这种表建立的就不合理。

2NF:满足第一范式的基础上，还要非主键列完全依赖于主键列，而不能依赖主键列的一部分

像上面的表虽然满足了第一范式，但不满足第二范式，在这个表里id和课程都是主键，而姓名、联系方式、地址都只依赖于id而不依赖课程号，成绩确实需要id和课程号共同确定，但是存在非主键列部分依赖主键列，所以上面建立的表也不合理。可以拆分成下面两张表。

3NF:满足第二范式的基础上，还要非主键列只依赖主键，而不能依赖其它非主键

上面这张图满足第一范式，也满足第二范式，即属性不可拆分，也不存在非主属性对主属性的部分依赖，但是不满足第三范式，是因为存在非主属性依赖主属性之外还以来非主属性，就是那个班主任，班主任依赖于专业，所以可以拆成下面样式

2.3逻辑查询优化

​ 到这才是sql的优化，sql能有什么优化呢？sql的优化方法有等价sql转换，重写子查询，简化查询条件等

比如 EXISTS 子查询和 IN 子查询的时候，会根据小表驱动大表的原则选择适合的子查询。在 WHERE 子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。

select a,b,c from A where subString(a,1,3)='abc'

上面就不会使用索引，可以优化成下面的样式

select a,b,c from A where a like 'abc%';

2.4物理查询优化

​ 物理查询优化就是建立适当的索引来提高查询效率，为什么要建立适当的索引呢？因为你建立的索引可能在查询的时候没有生效，还会存储索引，更加剧了性能损耗，那么怎么看我们的sql有没有使用索引呢？可以使用explain关键字查看。

常见的一些索引不生效的希望大家牢记

a、对索引列使用运算

b、%在最前面

c、not in 、not exist 、is null、is not null

d、单独引用聚合索引的非第一列字段

e、使用<>或者！=

f、用or连接两个查询条件

g、字符串不加引号

后面会深入研究这一块，包括explain怎样看使用什么索引，原理分析

2.5缓存

​ 加缓存之所以能提高查询速率是因为我们可以把经常查询的数据放到内存中，减少io访问磁盘的次数，这个场景在大流量时，大并发场景效率特别明显。

​ 目前常用的缓存数据库有 Redis 和 Memcached，它们都可以将数据存放到内存中。

从可靠性来说，Redis 支持持久化，可以让我们的数据保存在硬盘上，不过这样一来性能消耗也会比较大。而 Memcached 仅仅是内存存储，不支持持久化。

从支持的数据类型来说，Redis 比 Memcached 要多，它不仅支持 key-value 类型的数据，还支持 List，Set，Hash 等数据结构。 当我们有持久化需求或者是更高级的数据处理需求的时候，就可以使用 Redis。如果是简单的 key-value 存储，则可以使用 Memcached。

2.6分库分表

​ 因为数据库中的表存储到一定大小或者到达了多少记录数时再往里面存数据访问就会变慢，当单表超过500万就有必要分表了，首先我们可以根据业务将相关联的表分到一个库了，关系不大的表分到别的库里，这就是分库，单表数据很大，需要根据主键进行分表来提高查询效率时这就是分表。但是这时候还要考虑事务，分布式主键一系列情况，要慎重。

3、总结

​ 本篇文章介绍了数据库优化思路以及数据库优化的必要性，我也只列举了自己知道的一些，并不全面，如果要深入还是要看官方文档和专业数据，希望大家看完会有所收获。