MySQL性能优化小记

关于MySQL性能优化我的几点总结：

1. 根据业务选择合适的数据库；
2. 采用数据库缓存技术，如memcache、redis等。可以使用动态服务器层的的程序来控制数据库缓存的分布式访问；
3. 采用MySQL主从结构；
4. 如果业务量更加巨大时，可以对数据库进行拆表、拆库，实现数据库级别的负载均衡。

下面是在开发过程中关于MySQL优化的几点最佳实践：

• 为查询缓存优化你的查询：

大多数MySQL数据库都开启了查询缓存，这是提高性能的最有效的方式。而这时是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了很多次就会被放在一个缓存中，这样，后续的查询就可以直接访问缓存而不用再去操作表了。

• EXPLAIN你的SELECT查询：

使用EXPLAIN关键字可以让你知道MySQL是如何处理你的SQL语句的，可以帮你分析你的查询语句或表结构的性能瓶颈。

• 当只有一行数据时使用LIMIT1：

当你查询表的时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或许也许会去检查返回的记录数。在这种情况下，加上LIMIT1可以增加性能。MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。

• 为搜索字段建索引：

索引并不一定就是给主键或唯一的字段，如果你在表中，有个字段你总要经常用来做搜索，那么，你就可以为它建立索引。

    但同时要注意会导致引擎放弃索引的情况：

（1）where子句使用like关键字时，前置百分号会导致索引失效 （起始索引不确定都会失效）。如“select id from test where name like%巴拉拉”

（2）where子句使用is null或者is not null时，因为null值会被自动从索引中排除，索引一般不会建立在有空值的列上

（3）where子句中使用or关键字时，or左右字段如果存在一个没有索引，一个有索引字段也会失效；而且即使都有索引，因为二者的索引存储顺序并不一致，效率还不如全表扫描，这时引擎有可能放弃使用索引，所以要慎用or

（4）where子句中使用！=操作符时，将放弃使用索引，因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描。

（5）where子句中使用in或not in关键字时，会导致全表扫描，能使用exists或between and替代就不使用in

（6）where子句中应避免对索引字段操作(表达式操作或函数操作)

（7）在使用复合索引时，查询时必须使用到索引的第一个字段，否则索引失效；并且应尽量让字段顺序和索引顺序一致

（8）查询时必须使用正确的数据类型。数据库包含了自动的类型转换，比如纯数字赋值给字符串字段时可以被自动转换，但如果查询时不加引号查询，会导致引擎忽略索引

• 避免SELECT*
• 为每一张表设置一个ID
• 千万不要使用ORDRE BY RAND()
• 使用ENUM而不是VARCHER:

ENUM类型是非常快和紧凑的，在实际上，其保存的是TINYINT，但其外表上显示为字符串。如果你有一个字段，比如“性别”、“国家”，“民族”、“状态”和“部门”这些字段的取值是有限且固定的，那么应该使用ENUM而不是VARCHAR

• Prepared Statements:

Prepared Statements很像存储过程，是一种运行在后台的SQL语句的集合。

Prepared Statements可以检查一些绑定的变量，这样可以保护你的程序不会受到""SQL注入式攻击。性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

• 垂直分割：

一种把数据库中表按列变成几张表的方法，这样可以降低表的复杂度和字段数目，从而达到优化的目的。

• 选择合适的存储引擎：

MySQL中常用的两个存储引擎MyISAM和InnoDB，每个引擎都有利弊。

    

    MyISAM引擎和InnoDB引擎详解：

1、MyISAM引擎是MySQL默认的存储引擎。

具有如下特性：

（1）不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能用；

（2）表级锁定：其锁定机制是表级锁定，这虽然可以让锁定的实现成本减小，但也同时大大降低了其并发性能；

（3）读写互相阻塞：不仅会在写入的时候阻塞读取，也会在读取的时候阻塞写入，但读本身不会阻塞另外的读；

（4）只会缓存索引：MyISAM通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引。

应用场景：

（1）不需要事务支持（不支持）；

（2）并发相对较低（锁定机制问题）；

（3）数据修改相对较少（阻塞问题）；

（4）以读为主；

（5）数据一致性要求很高。

2、InnoDB引擎：

具有如下特性：

（1）具有较好的事务支持：支持四个事务隔离级别，支持多版本读；

（2）行级锁定：通过索引实现；

（3）读写阻塞与事务隔离级别相关；

（4）具有非常高效的缓存特性：能缓存索引，也能缓存数据；

（5）整个表和主键以Cluster方式存储，组成一颗平衡树；

（6）所有Secondary Index都会保存主键的信息。

应用场景：

  （1）需要事务支持（具有较好的事务特性）；

        （2）行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成；

（3）数据更新较为频繁的场景；

（4）硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能较少磁盘IO。