前言

以我的习惯来讲，每开始一个新的项目都需要先把思路完善，紧接着就需要建立数据库，在码代码的时候，就一般不会在修改数据库的构造了，因此，数据库的结构通常关乎着查询的速度以及程序的完善程度，一个好的结构可以让你少写很多代码，也能让程序的运行速度更加快，通常在大公司都是由DBA来做这件事，但是事无绝对，作为一名合格的后端，掌握一些少量的数据库优化也是很需要的。

MySQL优化 - 数据类型及CURD

PROCEDURE ANALYSE()

PROCEDURE ANALYSE() [prəˈsējər ˈænəlaɪz]是一个MySQL自带的给我们提供数据库优化建议的函数，他可以直接运行在MySQL中，直接在执行语句中加上这个函数即可

1
SELECT * FROM  `list` WHERE 1 PROCEDURE ANALYSE ( )

这段SQL执行过后，将会把list表中的数据分析一遍，并把他的分析结果展示出来

Field_nameMin_valueMax_valueMin_lengthMax_length
Empties_or_zerosNullsAvg_value_oravg_lengthstdOptimal_fieldtype

他将会把分析出来的 字段名 最短值 最大值 以及最后一列就是MySQL给出的分析结果，我们可以在有一定数据的时候使用这个函数来分析，这样给出的结果会更精确一些，只需要查看最后一列Optimal_fieldtype的值即可，这个函数并不适用于数据库设计阶段，它适用于后期使用

EXPLAIN

EXPLAIN是一个非常好用的MySQL语法，在我们功能测试阶段，如果发现某页面非常慢，排除静态资源问题后就可以试试使用EXPLAIN，我们可以在执行语句前面加上 EXPLAIN 来获得执行过程，通过该结果我们可以看到SQL如何改变会减少查询时间和次数。

1
EXPLAIN SELECT * FROM  `list` WHERE 1

这段SQL执行后，将会返回如下格式的分析结果

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

我们主要看rows就行，为了得到想要的结果，rows的值越小越好，使用EXPLAIN来调试简直是再好不过了！

ENUM(枚举)类型

很多程序员往往喜欢统一一个数据类型，比如说 ‘varchar’ ，这可能是我见过最多的数据类型了，早些时期，的确是有很多的公司或者程序都是大面积使用，随着MySQL的革新换代，很多的类型都可以避免使用它。
我在很多得程序上测试过（有数据）PROCEDURE ANALYSE()方法，他给出了很多 ‘varchar’ 替换为 ‘enum’ 的建议，这说明，enum类型的确是一个应该被重视的数据类型，但由于他是一个枚举类型，我们在定义数据类型的时候并不适合直接上手定义，所以很多时候都是在有一定的数据量的时候才想要换数据类型的。
可以理解为枚举即时索引，枚举就相当于给这个字段的可能值都加上了一个索引，与我们为了优化查询加索引是一样的概念。
enum更适用于选项卡类字段，例如性别，订单状态等，如果您字段中只有几个重复的值也是非常推荐使用的。

JOIN

链接查询，这是我们在开发中非常常用的查询方式，首先要知道，我们在学校里学习的大多数是 AND 链接多表查询，虽然能够将结果无误的查询出来，但是速度就影响的非常多了，这里还是推荐大家使用JOIN来连接查询
有些同学可能不太理解JOIN，简单说一下JOIN的内连接和外链接，左外链接和右外链接吧

内连接即是A B两表链接，只取两表共有的数据，假设 B 中 有的数据 A 表内没有对应的数据则无法查询到

1
SELECT * FROM list1 INNER JOIN list2 on list1.id = list2.id

外连接（FULL JOIN 也称作全连接）即是A B两表链接，取两表所有的数据，即使 B 表中的某些数据无法匹配链接条件时，也正常链接

1
SELECT * FROM list1 FULL JOIN list2 on list1.id = list2.id

左外连接，即是 A B两表链接，取两表所有数据，若A表中有B表不匹配的数据，同样展示出来，B表如果有A不匹配的数据，则不展示

1
SELECT * FROM list1 LEFT OUTER JOIN list2 on list1.id = list2.id

右外连接，即是 A B两表链接，取两表所有数据，若B表中有A表不匹配的数据，同样展示出来，A表如果有B不匹配的数据，则不展示，与左外连接相反

VvmQFU.png
VvmQFU.png

MySQL优化 - 结构

FULLTEXT INDEX

FULLTEXT INDEX(全文索引)，更适用于文章内容搜索的索引，我们在作搜索功能的时候，很多人喜欢将文章内容(content)建立普通索引，但是实际上，这种做法并不会增加查询速度，通常我们做搜索的时候，执行下列语句。

1
SELECT content FROM  `list` WHERE content LIKE '%风向标%'

如果搜索功能权重比较高的网站，就需要将content这个字段建立索引。

1
ALTER TABLE  `list` ADD FULLTEXT (`content`)

如果是phpmyadmin用户，在phpmyadmin中直接点击’全文搜索’即可。

MyISAM OR InnoDB？

就我现阶段写出来的东西来看（数据量小，查询次数少，用户量较少），MyISAM肯定是最适合我的，它更适用于小型网站，以及事务处理较少的网站
InnoDB则与之相反，如果你的业务比较复杂，针对数据库的操作较多的时候，InnoDB就会更适合一些。
使用INSERT插入数据时 MyISAM 就比 InnoDB 更快一些，而 UPDATE 时 InnoDB 就会比 MyISAM 快一些

如果您是轻度SQL用户，重功能不重视业务的项目，那么我个人以为 MyISAM 更适合一些
如果您感觉业务逻辑复杂，经常使用SQL，那么可以尝试使用 InnoDB

最后也是见仁见智，没有好坏，如果您希望测试，也是可以通过直接修改数据库引擎来测试速度的

MySQL优化 - 小知识点

  • 不要使用 SELECT * 查询
  • 不要使用 NULL
  • 频繁查询的字段建立索引
  • 索引过多时会影响 UPDATE 和 INSERT 的执行速度
  • 避免在 WHERE 时使用 != <> 等操作符，MySQL会自动放弃索引，直接全表扫描
  • 避免使用 IN 和 NOT IN，尽量使用BETWEEN，MySQL会自动放弃索引，直接全表扫描
  • 可以使用 EXISTS 来代替 IN 使用
  • 某些情况下可以使用强制使用索引查询 SELECT * FROM list with(index(索引名)) WHERE ….
  • 避免使用 OR 作为调件，可以使用 UNION 并集查询将两次查询结果合并
  • 尽可能将表内容长度固定
  • 查询时如果只查询一条信息，就使用 LIMIT 1
  • 避免使用比较表达式 如 10000+1 = id 可以使用 id = 10000+1
  • 记得将查询链接即时关闭掉
  • 使用变量来给MySQL开启查询缓存，避免使用MySQL内置变量函数
  • 设置的主键尽量使用长度短且最好是int类型
  • 垂直分割，将大量的字段的表优化成多个少字段的表
  • INSERT 和 DELETE 是一个可以锁定数据表的SQL语句，必须等待执行完毕后才会解除锁定，如果这条语句执行起来过于缓慢，请谨慎使用
  • Object Relational Mapper
  • Prepared Statements

参考: Top 20+ MySQL Best Practices
参考: 廖雪峰的个人网站 - 链接查询