#yyds干货盘点#Mysql数据库优化原则-摩杜云开发者社区

数据库优化

1、数据库优化原则

1、数据库在设计的时候严格按照范式设计
2、使用字段数据类型的时候，尽可能的用小的数据类型
3、在数据库里面开启慢查询，分析哪些SQL语句执行比较慢，将比较慢的SQL语句查询使用日志记录方式记录下来再进行分析和处理
4、使用explainSQL语句进行分析，分析为什么比较慢，从而改写SQL语句或者针对需要的字段适当的加上索引
5、应用层面进行优化，例如加上缓存（memcached和redis），或者页面静态化，让后面的请求不再查询数据库，这样效率更高，将效率分摊至前端方便扩展应用服务器
6、在架构层面进行调整，可以使用主从、读写分离的方式减轻数据库服务器的压力，避免在单台机器上过度消耗资源
7、必要的时候，可以对数据表进行纵切，垂直分表，将不经常读的内容和经常操作的内容放置不同的表中。不经常读取内容，降低磁盘IO
8、可以使用mysql表分区技术，将一个表分成多个不同的文件。
9、如果内容特别多可以采用数据库中间件或者类似于分库分表分机器的技术将表分至不同的数据库或者数据库实例（服务器）中
10、选择存储引擎的时候，可以按照一些具体业务场景选择存储引擎（引擎都有各自的特性，你可以使用不同的引擎处理不同的业务）
11、尽量不要使用or，like，搜索的加上主键，不用或者少用全文索引
12、如果非要使用至全文索引，将全文索引专门建立全文索引服务器
13、配置文件优化
14、可以提升机器性能例如用固态硬盘、用更大的内存

2、存储引擎

myisam  innodb

3、数据表设计

三范式：数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据存储方式。在关系型数据库中这些规范就可以称为范式。
第一范式：当关系模式的所有属性都不能在分解为更基本的数据单位时，称是满足第一范式的，简记为1NF。
第二范式：如果关系模式满足第一范式，并且所有非主属性都完全依赖于的每一个候选关键属性，称满足第二范式，简记为2NF。
第三范式：这是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于的任意一个候选关键字，称R满足第三范式，简记为3NF。

4、慢查询

5、Explain

查询执行计划，在执行sql语句之前，通过这个来查看你的语句的预执行情况，通过查看mysql如何执行这条语句，来分析这条语句如何优化
   id: 1
select_type: SIMPLE
table: user
partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
key_len: NULL
  ref: NULL
 rows: 99951
filtered: 10.00
Extra: Using where

select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
table：输出结果集的表。
type:ALL代表全表扫描,通常是不好的，其他的如index、range、const、ref、system则是较好的
    type=ALL，全表扫描，MySQL遍历全表来找到匹配的行
    type=index，索引全扫描，MySQL遍历整个索引来查询匹配的行
    type=range，索引范围扫描，常见于<、<=、>、>=、between等操作符
    type=ref，使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行
    type=eq_ref，类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用 primary key或者 unique index作为关联条件。
    type=const/system，单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引 unique index进行的查询。
    type=NULL，MySQL不用访问表或者索引，直接就能够得到结果

possible_keys:可能被用到的索引
key:查询过程中实际用到的索引，当为null时表示没有使用索引，通常不好
key_len:索引字段最大可能使用的长度，也叫作索引基数，索引基数越大，表名可能查询的行数越多，效率越低
rows:mysql估计的需要扫描的行数，行数越多，效率越低
这一列只有在EXPLAIN EXTENDED语句中才会出现。
extra:显示上述信息之外的其它信息，其主要有以下返回结果
    Usingindex
    表明此查询使用了覆盖索引(CoveringIndex)，即通过索引就能返回结果，无需访问表。
    若没显示"Usingindex"表示读取了表数据。
    Usingindex condition
    可能会使用索引，
    Using index就是一定使用索引，这种索引成为覆盖索引，Using index condition则是在必要的时候才使用索引
    Usingwhere
    表示 MySQL 服务器先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。效率较慢。
    Usingfilesort
    表示Mysql会按查询所需的顺序对结果进行排序，这时就会出现 Usingfilesort 。排序自然会增加查询时间，导致效率变慢。
    解决方法是利用索引进行排序。若查询所需的排序与使用的索引的排序一致，因为索引是已排序的，因此按索引的顺序读取结果返回，此时就不会出现Using filesort。

6、索引

7、语句优化

1、表设计
    1、数据表的设计
    1、表设计满足三范式
    2、字段数目不易过多，不常用的字段可以分成另外一个表
    3、选择合适的字段类型
    4、尽量使用整型
2、sql语句使用优化
(1)原则：
    从Explain入手
    尽可能在索引中完成排序
    只取自己需要的Column
    尽可能避免复杂的join和子查询
(2)优化limit
    select * from test1 order by id limit 99999,10 
    虽然使用了id索引，但是相当于从第一行定位到99999行再去扫描后10行，相当于扫描全表
    修改为如下方法：
    select * from test1 where id>=100000 order by id limit 10
(3)尽量不要使用  * 
(4)不让默认排序
    explain select * from user group by province\G
    explain select * from user group by province order by null\G
    在group by后面增加 order by null 就可以防止排序
(5)使用连接来替代子查询
    有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。
    select * from user where user_id in(select uid from goods);
    select * from user, goods where user.id=goods.uid;
    【注】连接中左外连接效率最高
(6)使用LIMIT 1取得唯一行