前言：影响数据库效率的因素

主要可以分为两类：

1、一类是服务器的CPU、内存、网卡流量、磁盘IO等硬件因素；

2、另一类就是数据库的设计和使用方法，例如数据库引擎选择、表结构设计、索引的使用、查询语句等。

对于第一类一般可以通过升级硬件来达到更好的效果，也有一些可以在服务器部署等方面进行优化，例如减少从服务器的数量从而减少数据同步带来的网络传输消耗（从服务器都要从主服务器上复制日志，所以从服务器越多，网络流量越大），进行分级缓存等。

而作为开发人员，本文的重点是从第二个方面进行总结。

一、存储引擎的选择

MySQL常用的存储引擎有三种，即Innodb、Myisam以及memory引擎。

1、Innodb引擎：

主要特性：具有较好的事务支持；支持行级锁定；支持外键；能够缓存索引和数据；所有的二级索引都会保存主键信息，不支持FULLTEXT类型的索引;不保存表的具体行数; DELETE FROM table时，是一行一行删除的，可以回滚。

适用场景：需要事物支持，高并发请求，

2、MyISAM引擎：

主要特性：不支持事务；表级锁定；读写相互阻塞；只会缓存索引

适用场景：不需要事务支持；以读为主的，数据修改相对比较少（读写相互阻塞）；并发低。

3、memory引擎:

是MySQL中一类特殊的存储引擎，它将数据全部放在内存中。这样有利于数据的快速处理，提高整个表的效率。但如果内存出现异常或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般只用在一次性的表。

注：由于实际工作中均使用Innodb引擎，因此本文以下内容均以Innodb引擎为前提

二、表结构的设计

首先，三大范式：

第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

其次，数据字段的设计：

（1）使用尽可能简单的数据类型，int要比varchar类型在mysql处理上更简单，效率更高。

（2）对于数字类型：尽量不使用double（8字节），不仅仅只是存储长度的问题，同时还会存在精确性的问题。而对于固定精度的小数，也不建议使用decimal，可以乘以固定倍数转换成整数存储，这样可以大大节省存储空间。对于整数的存储，建议区分开 tinyint / int / bigint 的选择，例如数字范围在-128到127之内的，可以使用tinyint（1字节），而不使用int（4字节）。

（3）对于字符类型，定长字段最好使用 char类型，不定长字段使用varchar，且要根据实际情况设定适当的最大长度，因为不同的长度范围，MySQL也会有不一样的存储处理。尽量少用text类型，非用不可时最好考虑分表，可以将text字段单独拆分出一个表。

（4）对于时间类型：尽量使用 timestamp类型，因为其储存空间只需要 datetime 类型的一半。对于只需要精确到某一天的数据类型，建议使用date类型，因为他的存储空间只需要3个字节，比timestamp还少。

（5）尽可能的使用not null定义字段，因为null类型比较特殊，MySQL需要有额外的处理。

（6）将字段多的表分割成多个表，比如将使用频率低的字段分割出来组成新的表。

三、索引

索引可以说是针对查询进行优化的最有效和常用的手段，特别是表中的数据量越来越大时，索引对于查询性能可以提升好几个数量级。

1、虽然索引可以大大加快数据的查询速度，但是它也有缺点：

（1）创建索引和维护索引要耗费时间，当对表中的数据进行增、删、改时，索引也需要动态的维护，因此降低了数据维护的速度。并且随着数据量的增加所耗费的时间也会增加；

（2）索引也需要占空间

2、因此并不是说不管什么场景，创建索引一定比没有好，具体什么时候适合建索引，可以参考如下：

（1）对更新非常频繁而查询比较简单的表不建议使用索引；

（2）对数据量小的表不需要使用索引，因为数据量小时直接全表扫码的效率可能会更高；

（3）在值的取值较少的字段上不用建立索引，比如某标志位字段的值只可能是0和1时，因为当索引列有大量数据重复时SQL查询可能不会去利用索引；

（4）经常与其他表进行连接的表，在连接字段上最好建立索引，特别是被驱动表；

（5）经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

（6）索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

（7）索引不只是用于where条件中，还会用于order by，group by，join的on字段。

（8）一个表不能有太多索引，一般一个表的索引数最好不要超过6个。

四、查询优化

首先，查询语句调优要会使用Explain查看执行计划，这对于性能调优非常有用，Explain的使用方法很简单，在查询语句前面加上Explain即可，对于Explain的各个列的含义网上随便搜一下有很多文章，在此就不赘述。

1、有些sql语句会导致无法使用索引，应尽量避免：

（1）where 子句中对字段进行表达式或函数操作时，无法使用索引。如：

SELECT * from dept_emp where id/2= 100

SELECT * from dept_emp where left(int_string, 4)="2000"

应改为:

SELECT * from dept_emp where id= 100 *2

SELECT * from dept_emp where int_string like "2000%“

（2）有左模糊匹配时无法使用索引，应尽量避免，例如

SELECT * from dept_emp where int_string like "%100%"

（3）查询条件中存在某些隐式转换时，无法使用索引：

字段类型为字符串，传入参数为数字时无法使用索引

SELECT * from dept_emp where int_string=1000

但是字段类型为数字，传入字符串时不受影响，仍然可以使用索引。

2、组合索引最左前缀原则：在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，还有其他一些限制条件不太好文字描述，直接看例子：

有组合索引包括四个列，依次为a, b, c, d。

select ...... where a="value1"  => 可以使用到索引

select ...... where b="value2"  => 无法使用到索引

select ...... where a="value1" and b="value2"  and d="value4"  => a和b列可以使用索引，d无法使用

select ...... where a="value1" and b>"value2"  and c="value4"  => a和b列可以使用索引，c无法使用

select ...... where b="value2" and a="value1"  => a和b列可以使用索引，影响组合索引使用的是索引中列的顺序，而不是where语句中的字段顺序。

3、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

4、join联表查询时，不改变结果集的前提下查询条件尽量详细，这样过滤的数据越多，循环次数越少。

5、前面有说到字段类型尽量设置为not null，null除了会占用额外空间外，还可能会带来一些其他的隐藏问题，例如在组合唯一索引中，当其中某个字段为null时，null值是可以重复，这可能会导致某些与预想不一致的结果。可以考虑将字段设置为不为null，然后默认值为空字符串。

例：建立三个列组成的组合唯一索引：a, b, c，以下数据是允许的

a=1, b=2, c=null

a=1, b=2, c=null

6、批量数据处理与事务

InnoDB在大批量插入数据时，尽量自己控制事物而不要使用autocommit自动提交，两者的效率相差很大，因为自动提交是在每插入一条数据的时候就提交一次（若使用spring的事务管理@Transaction则不用担心这个问题，因为spring会将底层连接的自动提交特性设置为false）

实际做了比较如下，10000条数据一个insert语句批量插入时：

没有事务：2790ms

有事务：3447ms

10000条数据循环10000次insert插入时：

有事务：12649ms

没有事务：332970ms

通过sql脚本导入数据时也是同样的道理，最好是导出sql脚本时就是一个insert语句的形式，如果是循环insert的话，可以在第一条插入语句之前加上begin，最后一条后面加上commit，从而将其放入一个事务中只提交一次。

3000条循环插入：

HeidiSQL：

原来使用时间：133s

加上事务后时间：28s

命令行：

原来使用时间：91s

加上事务后时间：1s

五、实践出真知

1、where 子句中使用!=、is null、is not null时无法使用索引？

前提：int_string和extra两个列分别建立了索引，其中只有一行数据int_string值为"6666"，只有一行数据int_string值为null，只有一行数据extra 值不为 "abcdefg"，使用Explain查看以下查询语句是否使用了索引，结果为：

select * from dept_emp where int_string != "6666"（未使用索引）

select * from dept_emp  where extra != "abcdefg"（使用了索引）

select * from dept_emp where int_string is null（使用了索引）

结论：并不是无法使用索引，而是因为多数情况下这些写法获取到的结果集较大，而使用二级索引查询时的回表操作是随机IO（覆盖索引除外，不需要回表效率很高），需要扫描的二级索引记录数量越多，随机IO就越多，当这个数量达到了某个比例时，使用二级索引执行查询的成本也就超过了全表扫描的成本，因此优化器放弃了使用索引。当查询结果集较小时，例如所有数据只有一个不为a，那么查询条件为!=a时仍然时会使用索引的。所以MySQL中决定是否使用某个索引执行查询是由使用成本决定的，并不是是否在where子句中用了!=、is null、is not null这些条件。