数据库优化

一、数据库优化法则

二、数据库优化之SQL语句

    （一）操作符

    （二）SQL书写

    （三）SQL语句中索引利用

    （四）更多

三、数据库优化之表结构

    （一）列类型

    （二）字符编码

    （三）适度拆分

    （四）适度冗余

四、数据库优化之索引

    （一）索引设计

    （二）索引副作用

    （三）索引是否越多越好

五、数据库优化之缓存

    （一）Query Cache

    （二）Binlog Cache

    （三）Key Buffer

    （四）innodb_buffer_pool_size

    （五）innodb_log_buffer_size

六、数据库优化之存储引擎

    （一）Mysiam

    （二）InnoDB

    （三）NDBCluster

一、数据库优化法则

（一）减少数据库访问

（二）返回更少数据

（三）减少交互次数

（四）减少数据库服务器CPU运算

（五）利用更多资源

二、数据库优化之SQL语句

（一）操作符

(a) IN 操作符

用in操作，数据库会转换成一个多表的连接，如果转换不成功才先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。

推荐方案：在业务密集的SQL当中尽量不采用IN操作符，用EXISTS 代替。

(b) NOT IN操作符

此操作是强烈不推荐使用的，因为它不能应用表的索引。

推荐方案：用NOT EXISTS 方案代替

(c) IS NULL 或IS NOT NULL操作

判断字段是否为空是不会应用索引的，因为索引是不索引空值的。不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

推荐方案：用其它相同功能的操作运算代替，如：a is not null 改为 a>0 或a>’’等。

(d) > 及 < 操作符

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，99万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时数据库会先找出为2的记录索引再进行比较，而A>=3时数据库则直接找到=3的记录索引。

(e) UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果,如果表数据量大的话可能会导致用磁盘进行排序。实际大部分应用中是不会产生重复的记录。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

(f) Order by语句

ORDER BY语句决定了数据库如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中。

但是任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。

(g) NOT

我们在查询时经常在where子句使用一些逻辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:

... where not (status ='VALID')

如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例:

... where status <>'INVALID';

对这个查询，可以改写为不使用NOT：

select * from employee where salary<3000 or salary>3000;

虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。

（二）SQL书写

WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响。如： 

Select * from A where a = 'aaa' and b=1 
Select * from A where b=1 and a = 'aaa' 
以上两个SQL中a,b两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的a = 'aaa'条件在记录集内比率为99%，而b=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行a及b的比较，而在进行第二条SQL的时候0.5%条记录都进行a及b的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

（三）SQL语句中索引利用

采用函数处理的字段不能利用索引。

（四）更多

（1） WHERE子句中的连接顺序：

数据库采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

（2） SELECT子句中避免使用 *：

解析的过程中, 会将*依次转换成所有的列名, 而这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

（3） 用Where子句替换HAVING子句：

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤.。这个处理需要排序、总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

三、数据库优化之表结构

（一）列类型

列类型选择

1-1数字类型：非万不得已不要使用DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用DECIMAL，建议乘以固定倍数转换成整数存储，可以大大节省存储空间，且不会带来任何附加维护成本。

对于整数的存储，在数据量较大的情况下，建议区分开 TINYINT / INT / BIGINT 的选择，因为三者所占用的存储空间也有很大的差别。当然，如果数据量较小的数据库，也可以不用严格区分三个整数类型。

1-2字符类型：非万不得已不要使用text数据类型，他的性能要低于char或者是varchar类型的处理。

定长字段，建议使用CHAR 类型，不定长字段尽量使用VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定。

1-3时间类型：尽量使用TIMESTAMP类型，因为其存储空间只需要 DATETIME 类型的一半。

对于只需要精确到某一天的数据类型，建议使用DATE类型，因为他的存储空间只需要3个字节。

（二）字符编码

纯拉丁字符能表示的内容，没必要选择 latin1 之外的其他字符编码，因为这会节省大量的存储空间。

如果我们可以确定不需要存放多种语言，就没必要非得使用UTF8或者其他UNICODE字符类型，这回造成大量的存储空间浪费。

（三）适度拆分

有些时候，我们可能会希望将一个完整的对象对应于一张数据库表，这对于应用程序开发来说是很有好的，但是有些时候可能会在性能上带来较大的问题。

当我们的表中存在类似于 text或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。

这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。

（四）适度冗余

被频繁引用且只能通过 Join 2张（或者更多）大表的方式才能得到的独立小字段。这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的IO，完全可以通过空间换取时间的方式来优化。

不过，冗余的同时需要确保数据的一致性不会遭到破坏，确保更新的同时冗余字段也被更新。

四、数据库优化之索引

（一）索引设计

1、我们应该尽量让查找条件尽可能多的在索引中，尽可能通过索引完成所有过滤，回表只是取出额外的数据字段。

如果我们仅仅只是这样告诉对方的：“帮我确认一本数据库类别的讲述 MySQL 的叫做《MySQL性能调优与架构设计》的书是否在藏”，结果又会如何呢？朋友只能一个大类区域一个大类区域的去寻找“数据库”类别，然后再找到 “MySQL”范畴，再看到我们所需是否在藏。由于我们少说了一个“计算机类”，朋友就必须到每一个大类去寻找。

2、字段的顺序对组合索引效率有至关重要的作用，过滤效果越好的字段需要更靠前。

如果我们是这样说的：“帮我确认一本讲述 MySQL 的数据库范畴的计算机丛书，叫做《MySQL性能调优与架构设计》，看是否在藏”。如果这位朋友并不知道计算机是一个大类，也不知道数据库属于计算机大类，那这位朋友就悲剧了。首先他得遍历每个类别确认“MySQL”存在于哪些类别中，然后从包含 “MySQL” 书籍中再看有哪些是“数据库”范畴的（有可能部分是讲述PHP或者其他开发语言的），然后再排除非计算机类的（虽然可能并没有必要），然后才能确认。

3、当我们需要读取的数据量占整个数据量的比例较大或者说索引的过滤效果并不是太好的时候，使用索引并不一定优于全表扫描。

如果我们还有这样一个需求：“帮我将图书馆中所有的计算机图书借来”。朋友如果通过索引来找，每次都到索引柜找到计算机书籍所在的区域，然后从书架上搬下一格（假设只能以一格为单位从书架上取下，类比数据库中以block/page为单位读取），取出第一本，然后再从索引柜找到计算机图书所在区域，再搬下一格，取出一本… 如此往复直至取完所有的书。如果他不通过索引来找又会怎样呢？他需要从地一个书架一直往后找，当找到计算机的书，搬下一格，取出所有计算机的书，再往后，直至所有书架全部看一遍。

在这个过程中，如果计算机类书籍较多，通过索引来取所花费的时间很可能要大于直接遍历，因为不断往复的索引翻阅所消耗的时间会非常长。

4、在实际使用过程中，一次数据访问一般只能利用到1个索引，这一点在索引创建过程中一定要注意，不是说一条SQL语句中Where子句里面每个条件都有索引能对应上就可以了。

如果我们的朋友不知道“数据库”这个类别可以属于“计算机”这个大类，或者图书馆的索引系统中这两个类别属性并没有关联关系，又会怎样呢？也就是说，朋友得到的是2个独立的索引，一个是告知“计算机”这个大类所在的区域，一个是“数据库”这个小类所在的区域（很可能是多个区域），那么他只能二者选其一来搜索我的需求。即使朋友可以分别通过2个索引检索然后自己在脑中取交集再找，那这样的效率实际过程中也会比较低下。

（二）索引副作用

. 图书的变更（增，删，改）都需要修订索引，索引存在额外的维护成本

. 查找翻阅索引系统需要消耗时间，索引存在额外的访问成本

. 这个索引系统需要一个地方来存放，索引存在额外的空间成本

（三）索引是否越多越好

1、对于类似于这样的存在非常大更新量的数据，索引的维护成本会非常高，如果其检索需求很少，而且对检索效率并没有非常高的要求的时候，我们并不建议创建索引，或者是尽量减少索引。

如果我们的这个图书馆只是一个进出中转站，里面的新书进来后很快就会转发去其他图书馆而从这个馆藏中“清除”，那我们的索引就只会不断的修改，而很少会被用来查找图书。

2、对于数据量极小到通过索引检索还不如直接遍历来得快的数据，也并不适合使用索引。

如果我们的书籍量少到只有几本或者就只有一个书架，索引并不会带来什么作用，甚至可能还会浪费一些查找索引所花费的时间。

3、当我们连存储基础数据的空间都捉襟见肘的时候，我们也应该尽量减少低效或者是去除索引。

如果我们的图书馆只有一个10平方的面积，现在连放书架都已经非常拥挤，而且馆藏还在不断增加，我们还能考虑创建索引吗？

五、数据库优化之缓存

（一）Query Cache

1.作用于整个 MySQL ，主要用来缓存 MySQL 中的 ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。

当我们打开了 Query Cache 功能，MySQL在接受到一条select语句的请求后，MySQL 会直接根据HASH算法将select语句以字符串方式进行hash，然后到Query Cache 中直接查找是否已经缓存。

如果已经在缓存中，该select请求就会直接将数据返回。

当然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache 可能会得不偿失。

2.Query Cache的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何种场景下使用 Query Cache。

在以往的经验来看，如果不是用来缓存基本不变的数据的MySQL数据库，query_cache_size 一般 256MB 是一个比较合适的大小。当然，这可以通过计算Query Cache的命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）来进行调整。

query_cache_type可以设置为0(OFF)，1(ON)或者2(DEMOND)，分别表示完全不使用query cache，除显式要求不使用query cache（使用sql_no_cache）之外的所有的select都使用query cache，只有显示要求才使用query cache（使用sql_cache）。

（二）Binlog Cache

Binlog Cache 用于在打开了二进制日志记录功能的环境，用于短时间内临时缓存binlog数据。

一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。

但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。

（三）Key Buffer

key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。

（四）innodb_buffer_pool_size

用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数。

简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。

所以如果我们有足够的内存，尽可将该参数设置到足够大，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。

（五）innodb_log_buffer_size

这是 InnoDB 存储引擎的事务日志所使用的缓冲区。

InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innodb Log Buffer 中，当满足一定条件（比如日志缓冲区写满）之后，才会将日志写到文件（或者同步到磁盘）中。

可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

六、数据库优化之存储引擎

（一）Mysiam

1-1特性

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

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

3. 读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

4. 只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据

1-2适用场景

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

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

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

4. 以读为主

5. 数据一致性要求不是非常高

（二）InnoDB

2-1特性

1.具有较好的事务支持：支持4个事务隔离级别，支持多版本读

2.行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响

3.读写阻塞与事务隔离级别相关

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

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

6.所有Secondary Index都会保存主键信息

2-2适用场景

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

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

3.数据更新较为频繁的场景

4.数据一致性要求较高

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

（三）NDBCluster

3-1特性

1. 分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分

2. 支持事务：和Innodb一样，支持事务

3. 可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互

4. 内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

3-2适用场景

1. 具有非常高的并发需求

2. 对单个请求的响应并不是非常的critical

3. 查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding