MySQL性能优化

关于数据库的性能，不只是数据库管理员DBA才需要关心的事，更是我们程序员需要去关注的事情。
当我们去设计表结构，操作数据库（尤其是查询语句）时，我们都需要注意操作的性能。这里只是针对MySQL聊聊一些优化的技巧。
1.查询缓存
大多数的MySQL数据库都开启了查询缓存。查询缓存的作用就是当接收到一个和之前一样的查询语句时，服务器将从查询缓存中检索结果，而不是再次执行上次的查询，这是提高性能最有效的方法之一。
对程序员来说这里最主要的问题是，某些查询语句会使MySQL不使用缓存。
// 查询缓存不开启
SELECT COUNT(1) FROM `member` WHERE `created` < NOW();
// 查询缓存有效
$now = date('Y-m-d H:i:s');
SELECT COUNT(1) FROM `member` WHERE `created` < $now;
所以对什么样的查询语句无法缓存结果集，大致有以下几类：
1. 查询语句中加了SQL_NO_CACHE；
2. 查询语句中含有获得值的函数，如：CURDATE(),NOW(),RAND()等；
3. 对系统数据库的查询：mysql，information_schema；
4. 查询语句中使用了SESSION级别的变量或存储过程中的局部变量；
5. 事务隔离级别为Serializable情况下，所有查询语句都不能缓存；
6. 存在警告信息的查询语句；

2.EXPLAIN 查询语句
使用EXPLAIN关键字可以让你知道MySQL是如何使用索引来处理SELECT语句以及连接表。这样可以帮你分析查询语句或表结构的性能瓶颈，选择更好的索引和写出更优化的查询语句。

3.当只要一行数据的时候请使用LIMIT 1
当你查询表的时候，你已经确定结果只有一条结果，这时加上LIMIT 1可以增加性能。这样一来，MySQL数据库引擎会在找到一条数据后停止搜索，而不会继续往后查找下一条符合条件的数据。

4.为搜索字段建立索引
索引并不一定就是给主键或是唯一字段。如果在你的表中，某个字段被频繁用来做搜索，那就为它建立索引吧。

5.在JOIN表的时候使用相同类型的列，并为其建立索引
如果你的语句中有很多JOIN查询，应该确认两个表中JOIN的字段是被建过索引的，这样MySQL内部会启动为你优化JOIN语句的机制，而且JOIN的字段应该是相同类型的，否则无法使用索引。

8.尽量避免SELECT *
从数据库中读出的数据越多，查询的速度越慢，所以应该养成需要什么取什么的好习惯。

9.永远为每张表设置一个ID
除了一些特别需求需要用到联合主键外，我们应该为数据库里的每张表都设置一个ID作为其主键，而且最好是INT型（推荐UNSIGNED）,并且设置自增长。

10.使用ENUM而不用VARCHAR
ENUM类型是非常快和紧凑的。实际上，其保存的是TINYINT,但外表上显示的是字符串。

11.从PROCEDURE ANALYSE()获取建议
PROCEDURE ANALYSE()会让MySQL帮你分析你的字段和其实际的数据，并给你一些有用的建议。数据越多，建议越准确。
语法：SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
max_elements （默认值256） ANALYSE()查找每一列不同值时所需关注的最大不同值的数量。
ANALYSE()还用这个值来检查优化的数据类型是否该是ENUM,如果该列的不同值的数量超过了max_elements值ENUM就不做为建议优化的数据类型。
max_memory   （默认值8192） ANALYSE()查找每一列所有不同值时可能分配的最大的内存数量。

12.字段尽可能用NOT NULL
字段尽可能用NOT NULL，而不是NULL，除非特殊情况。
MySQL官网文档：
“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
原因一目了然。

13.Prepared Statements
使用Prepared Statements有很多好处，无论性能方面还是安全方面。

14.固定长度的表会更快
如果表中的所有字段都是”固定长度的“，整个表会被认为”static“或”fixed-length“。例如，表中没有如下类型的字段：VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为MySQL搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。

15.垂直分割
垂直分割是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。
比如：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比而言你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？这样会让你的表有更好的性能，对于用户表来说，只有用户ID，用户名，密码，用户角色等会被经常使用。小一点的表总是会有好的性能。
不过需要注意的是，这些被分出去的字段所形成的表，不会经常性地去JOIN它们，不然的话，这样的性能会比不分割时还要差，而且，会是指数级的下降。

16.拆分大的DELETE或INSERT语句
如果需要执行一个大的INSERT或DELETE语句时，需要非常小心，因为这两个操作都会锁表，表一锁住，就不能进行别的操作了。所以处理大数据的时候LIMIT是个不错的方法。

17.选择正确的存储引擎
MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事务安全表，其他存储引擎都是非事务安全表。
MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。 
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。 
MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务关闭，表中的数据就会丢失掉。 
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同。MERGE表本身没有数据，对MERGE类型的表进行查询、更新、删除的操作，就是对内部的MyISAM表进行的。