MySQL性能优化的21个方法（个人理解）

1. 使用查询缓存优化查询

我们知道，一条SQL语句的执行需要经过：解析、优化和截断过程。当有相同的查询SQL被执行了多次时，这些查询结果就会放到一个缓存中。后续相同的查询结果就不需要再次执行SQL语句，而直接从缓存中获取结果。加快了查询性能。

目前大多数MySQL数据库自动开启了查询缓存。但是如果我们使用一些数据不固定的查询语句（例如使用rand()函数），这样就导致每次查询的结果都不固定，数据库就不会使用查询缓存。

2. EXPLAIN 你的select 查询

使用 explain 命令可以让你知道MySQL是如何执行你的SQL语句的。可以知道是否使用了索引，使用了哪个索引，数据表是如何被搜索和排序的等等。

3. Limit 1

当你只需要一行数据时：比如你想要知道某个User实例在数据库中存不存在。那么这时候我们不需要 select 出所有的结果然后去判断。我们加一个 limit 1 的限制条件，当SQL查找到指定数据中就会直接返回结果，不用再继续向下执行。极大地提高了效率
select * from user where id=1 ×
select 1 from user where id=1 limit 1 √

4. 为搜索的关键字加索引

索引不一定只加在主键上。有的时候我们需要对其它某个非主键字段进行多次查询，我们也可以对这个字段建立索引。但是要注意不要让索引失效哦！

5. Join 表的时候使用数据类型一致的列，并索引

要确保两个表用来Join的字段是建过索引的，这样MySQL内部会自动优化Join的SQL语句。
要确保用来Join的字段类型时相同的，否则将无法使用索引。
就比如将 DECIMAL类型字段和 INT 类型字段进行Join的话，就会导致索引失效。如果是字符串类型的字段进行 Join，还要保证字符串的编码相同。

6. 不要使用 order by rand()

这条语句的作用就是：从数据表中取出随机的几条数据。通过慢查询日志我们可以知道，这样做是会导致全表扫描的，效率非常低。使用这条语句的目的是为了做随机测试，但是要慎重使用！

7. 避免select *

查的字段越多，查的效率越低。 如果web服务器和SQL服务器是两台独立的服务器，那么还会增加网络传输的负担。 只要取有用的即可。

8. 永远为每张表设置一个ID

应该为每张数据表设置一个 INT类型（最好是 UNSIGNED）的主键ID，并设置自增标志。即使表中有其他字段可以唯一标识数据（比如手机号），那也别用来当主键，因为数据库的查询性能是和字段的长度成反比的。而且在关联多表时， ID 更加得心应手。

9. 使用ENUM而不是VARCHAR

枚举类型 ENUM是非常轻量的。 它保存的其实是 TINYINT，外表显示为字符串，这样一来 ENUM用来做一些选项列表是非常合适的。（比如 sex：男/女， 登录状态：已登录/未登录）。 如果使用 varchar， 不免有种大材小用的感觉。

10. 从 procedure analyse() 取得建议

procedure analyse() 过程分析，MySQL会去分析你的数据表中的字段和实际数据，提出一些有用的建议来提高性能。

就比如： 数据表中有某个 varchar字段， 由于数据量不大，MySQL会建议你将其改为 enum 类型。

这种建议是基于数据表字段和数据量来分析的，数据量小的时候会缺乏准确性。

11. 尽可能使用 NOT NULL

不到万不得已，不要去使用 NULL 值。
原因：1、 NULL值也是需要额外的存储空间的。 2、进行 NULL值比较时，程序会更复杂，也会影响性能。

12. Prepared Statements

Prepared Statements 有太多好处：一个是性能，一个是安全。
性能方面：我们可以通过设置Prepared Statements的一些参数，来使得相同的SQL语句只会被解析一次，大大提升性能。

安全方面：Prepared Statements会检查你将要执行的SQL语句中所绑定的变量，防止SQL注入问题。

13. 无缓冲查询

我的理解是：这相当于 Java的 NIO模型。 无缓冲查询就是，当一条SQL查询语句还未结束时，我们就可以着手处理已经查询到的结果，无需等到所有结果返回。这里就会出现一问题： 已经返回的结果如果没有使用，就会占据缓存区域，可能会与下次的查询结果混淆。

因此使用无缓冲查询，要么将所有查询结果都处理了，要么就在下次查询前清除查询结果。

14. 把IP地址存成UNSIGNED INT

IP地址我们一般是使用varchar类型来保存。 但是如果我们用 整形(unsigned int) 来存放，只需要4个字节，并且可以有定长的字段。 而且如果要查找范围 IP，我们甚至可以直接：
select * from IP between ip1 and ip2

15. 使用固定长度的表

如果表中所有的字段都是定长的，那么这张表就会被认为是 “static” 或 “fixed_length”。
这样就有一个好处：查询的时候，我们可以通过当前位置的数据，很容易计算出下一个数据的偏移量，查询自然就会增快。 如果不定长的话，我们就需要重新找到主键，再通过主键进行索引，这样效率会比较低。

16. 垂直分表

将一张表，按列分成多张表的方法。可以降低表的复杂度和字段的数目，从而达到优化的目的。

比如：
一张User表，我们只需要 ID，Name，Password 三个字段， 那何不将其他的不是经常使用的如 地址address，性别sex等分到其他的表中。小表的性能自然会更好。

再比如：
还是一张User表，里面有个上次登录时间 last_login字段，这个字段值并不是固定的。按照我们上文所说，查询缓存将会失效。因此我们可以将这个字段单独分离出去。

我们还可以把一些不定长的字段列分离出去，只留下定长字段，这样也可以提升性能。

！！需要注意：分表后，要确保不会经常 Join，否则将会导致比不分表更差的性能。而且分表要注意不要违反了三范式。

17. 拆分大的 delete 或 insert语句

意思就是，不要一次性执行过多的 插入删除操作。如果是在服务器上执行，会导致服务器阻塞一段时间，在高并发的场景下这是非常致命的。

我们可以 通过 limit 来限制一次性执行的数据量，然后通过 sleep()方法来控制空闲时间。

18. 越小的列会越快

数据量不大的时候，我们就没必要使用 INT来做主键，可以使用更小的 MEDIUMINT,SMALLINT,或者是 TINYINT。如果不需要记录具体时间，可以用DATE来替换
DATETIME。

注意你先需要留下数据扩展的空间。

19. 选择合适的存储引擎

目前 MySQL使用最多的引擎就是 MyISAM和 InnoDB。
具体的选择问题还是要先去了解二者的区别，这边就不仔细阐述了。

20. 使用 关系映射器（ORM）

Java 推 MyBatis，其他不了解 T^T

21. 小心数据库永久链接

这个问题还不太了解，先记录一哈。

以上内容是基于网上博客+《高性能MySQL》+自己的理解整合的，大家看看就好，
有错误帮忙指出一下，谢谢搭嘎。