MySQL优化原则

数据库已成为互联网应用必不可少的底层依赖，其中MySQL作为开源数据库得到了更加广泛的应用。最近一直专注于项目工程的开发，对开发过程中使用到的一些关于数据库的优化原则进行了总结，希望能够帮助更多的应用开发人员更好的使用MySQL数据库。

       MySQL的优化主要包括三个方面，首先是SQL语句的优化，其次是表结构的优化，这里主要指索引的优化，最后是服务器配置的优化。 

     1．  SQL语句的优化

1） 查询语句应该尽量避免全表扫描，首先应该考虑在Where子句以及OrderBy子句上建立索引，但是每一条SQL语句最多只会走一条索引，而建立过多的索引会带来插入和更新时的开销，同时对于区分度不大的字段，应该尽量避免建立索引，可以在查询语句前使用explain关键字，查看SQL语句的执行计划，判断该查询语句是否使用了索引；

2）应尽量使用EXIST和NOT EXIST代替 IN和NOT IN，因为后者很有可能导致全表扫描放弃使用索引；

3）应尽量避免在Where子句中对字段进行NULL判断，因为NULL判断会导致全表扫描；

4）应尽量避免在Where子句中使用or作为连接条件，因为同样会导致全表扫描；

5）应尽量避免在Where子句中使用！=或者<>操作符，同样会导致全表扫描；

6）使用like “%abc%” 或者like “%abc” 同样也会导致全表扫描，而like “abc%”会使用索引。

7）在使用Union操作符时，应该考虑是否可以使用Union ALL来代替，因为Union操作符在进行结果合并时，会对产生的结果进行排序运算，删除重复记录，对于没有该需求的应用应使用Union ALL，后者仅仅只是将结果合并返回，能大幅度提高性能；

8）应尽量避免在Where子句中使用表达式操作符，因为会导致全表扫描；

9）应尽量避免在Where子句中对字段使用函数，因为同样会导致全表扫描

10）Select语句中尽量 避免使用“*”，因为在SQL语句在解析的过程中，会将“*”转换成所有列的列名，而这个工作是通过查询数据字典完成的，有一定的开销；

11）Where子句中，表连接条件应该写在其他条件之前，因为Where子句的解析是从后向前的，所以尽量把能够过滤到多数记录的限制条件放在Where子句的末尾；

12）若数据库表上存在诸如index(a,b,c)之类的联合索引，则Where子句中条件字段的出现顺序应该与索引字段的出现顺序一致，否则将无法使用该联合索引；

13）From子句中表的出现顺序同样会对SQL语句的执行性能造成影响，From子句在解析时是从后向前的，即写在末尾的表将被优先处理，应该选择记录较少的表作为基表放在后面，同时如果出现3个及3个以上的表连接查询时，应该将交叉表作为基表；

14）尽量使用>=操作符代替>操作符，例如，如下SQL语句，select dbInstanceIdentifier  from DBInstance where id > 3，该语句应该替换成 select dbInstanceIdentifier from DBInstance where id >=4 ，两个语句的执行结果是一样的，但是性能却不同，后者更加 高效，因为前者在执行时，首先会去找等于3的记录，然后向前扫描，而后者直接定位到等于4的记录。

2．  表结构的优化

    这里主要指如何正确的建立索引，因为不合理的索引会导致查询全表扫描，同时过多的索引会带来插入和更新的性能开销；

1）首先要明确每一条SQL语句最多只可能使用一个索引，如果出现多个可以使用的索引，系统会根据执行代价，选择一个索引执行；

2）对于Innodb表，虽然如果用户不指定主键，系统会自动生成一个主键列，但是自动产生的主键列有多个问题1. 性能不足，无法使用cache读取；2. 并发不足，系统所有无主键表，共用一个全局的Auto_Increment列。因此，InnoDB的所有表，在建表同时必须指定主键。

3）对于区分度不大的字段，不要建立索引；

4）一个字段只需建一种索引即可，无需建立了唯一索引，又建立INDEX索引。

5）对于大的文本字段或者BLOB字段，不要建立索引；

6）连接查询的连接字段应该建立索引；

7）排序字段一般要建立索引；

8）分组统计字段一般要建立索引；

9）正确使用联合索引，联合索引的第一个字段是可以被单独使用的，例如有如下联合索引index(userID,dbInstanceID),一下查询语句是可以使用该索引的，select dbInstanceIdentifier from DBInstance where userID=? ，但是语句select dbInstanceIdentifier from DBInstance where dbInstanceID=?就不可以使用该索引；

10）索引一般用于记录比较多的表，假如有表DBInstance，所有查询都有userID条件字段，目前已知该字段已经能够很好的区分记录，即每一个userID下记录数量不多，所以该表只需在userID上建立一个索引即可，即使有使用其他条件字段，由于每一个userID对应的记录数据不多，所以其他字段使用不用索引基本无影响，同时也可以避免建立过多的索引带来的插入和更新的性能开销；

       3．  MySQL服务器配置优化

            MySQL服务器配置优化主要是指MySQL参数的优化；

           1）MySQL服务器有慢连接日志，可以将超过一定时间间隔和不使用索引的查询语句记录下来方便开发人员跟踪，可以通过设置slow_query_log=ON/OFF打开和关闭慢连接日志功能，slow_query_log_file设置慢连接日志的文件名，long_query_time设置超时时间，单位是ms,注意慢连接日志MySQL默认是关闭的；

           2）MySQL有查询缓存的功能，服务器会保存查询语句和相应的返回结果来减少相同的查询造成的服务器开销，可以通过设置query_cache_size设置查询缓存的大小，0表示关闭查询缓存，但是值得注意的是，一旦该表有更新，则所有的查询缓存都会失效，默认情况下，MySQL是关闭查询缓存的；

           3）可以通过配置max_connections设置数据库的最大连接数，wait_timeout设置连接最长保留时间，该时间单位是s, MySQL默认是8个小时，一旦超过8个小时，数据库会自动断开该连接，这点在使用数据库连接池时由为需要注意，因为连接池中的连接可能已经被服务器断开了，到那时连接池不知道，应用在从连接池中获取到该连接使用时就会出错，max_connect_errors配置如果应用出现多次异常，则会终止主机连接数据库；