数据库优化总结
MySql优化：
InnoDB在大多数情况下如果如果要运行得很好，配置大小合适的缓冲池(Buffer Pool)和日志文件(Log File)是必须的，默认值都太小了。其他的所有InnoDB设置都是可选的。
1. 缓冲池：我们建议，当配置内存缓存区的时候，宁可谨慎，而不是把它们配置的过大。如果把缓冲池配置的比它可以设的值少了20%，很可能只会对性能产生小的影响，也许就只影响几个百分点。
如果设置的大了20%，则可能会造成更严重的问题：内存交换、磁盘抖动、甚至内存耗尽和硬件死机。
2. 日志文件：作为一个经验法则，日志文件的全部大小，应该足够容纳服务器一个小时的活动内容。
数据库优化
1. 选择优化的数据类型
1.1 数据库类型使用原则：
a). 更小的通常更好：更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理需要的CPU周期也更少。
b). 简单就好：简单的数据类型的操作通常需要更少的CPU周期。例如：整型比字符串操作的代价更低，因为字符集和校对规则(排序规则)是字符串比较比整型比较更复杂。这里有两个例子：
一个是应该使用MySQL内建的类型而不是字符串来存储日期和时间，另外一个是应该使用整型存储IP地址。
c). 避免使用null：通常情况下最好指定列为not null，除非真的需要存储null。因为null列使得索引、索引统计和值比较都更复杂。可为null的列会使用更多的存储空间，在MySQL中也需要特殊处理。
1.2 选择优化的数据类型：
1). 整数类型：尽量选择小的：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT
2). 实数类型：因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将对应的值扩大N倍。
3). 字符串类型选择：
a. VARCHAR：它比定长类型更节省空间，因为它仅使用必要的空间。
b. CHAR: 定长，当存储CHAR值时，MySQL会删除所有的末尾空格。定长的CHAR类型不容易产生碎片，对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率，VACHAR还有一个记录长度的额外字节。
二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串是，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比字符比较简单的多，所以也就更快。
4). 日期和时间类型：使用数据库时间类型，不要使用字符串；或者考虑使用BIGINT类型存储微秒级别的时间戳。
5). 选择标识符(identifier，主键)：
a. 在可以满足值的范围要求，并且预留未来增长空间的前提下，应该选择最小的数据类型。
b. 如果可能，应该避免使用字符串类型作为标识列，因为它们很消耗空间，并且通常比数字类型慢。
6). 一旦选择了一种类型，要确保在所有关联表中都使用同样的类型。使用用不同数据类型可能导致性能问题，即使没有性能影响，在比较操作时隐式类型转换也可能导致很难发现错误。
2. 索引优化：
2.1 索引的优点：索引可以让服务器快速定位到表的指定位置。最常见的B-Tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY 和 GROUP BY操作。总结下来，索引有如下三个优点：
1). 索引大大减少了服务器需要扫描的数据量
2). 索引可以帮助服务器避免排序和临时表
3). 索引可以将随机IO变为顺序IO。
2.2 索引优化：
1). 独立的列：独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。如果查询中的列不是独立的，则MySQL就不会使用索引。
2). 前缀索引和索引选择性：有时候需要索引很长的字符列，这会让索引变得很大且慢。通常可以索引开始部分的字符，这样可以大大节约索引空间，从而提高索引效率。但这样会降低索引的选择性。
3). 选择合适的索引列顺序：正确的索引顺序依赖于使用该索引的查询，并同时满足需要考虑如何更好地满足排序和分组(多列索引时)的需要。
4). 覆盖索引：如果一个索引包含(或者说覆盖)所有需要查询的字段的值，我们就称之为"覆盖索引"
5). 删除冗余和重复索引
3. 其他优化：
1). 范式(修改多，关联查询少)和反范式(查询多，修改少)的选择
2). 缓存表和汇总表：
a. 缓存表：存储那些可以比较容易的从schema其他表获取(但每次获取速度缓慢)数据的表(例如逻辑上冗余的数据)
b. 汇总表：保存的是使用GROUP BY语句聚合数据的表。实时计算统计值是很昂贵的操作。
c. 在使用缓存表和汇总表时，必须决定是实时维护数据还是定期重建。哪个更好依赖于应用程序，但是定期重建并不只是节省资源，可以保持表不会有很多碎片，以及完全顺序组织的索引。
3). 计数器表：创建一张独立的表存储计数器通常是一个好主意。使用计数器表的一些技巧
a. 为了防止互斥锁影响效率，可以添加多条记录，随机更新一条记录。统计总数时，将所有数据相加。
b. 对于需要根据时间更新计数器，也可以用上述方法，不过多加一步操作。每天定时将前一天的总数合计起来，插入到计数器表，删除那些零散的统计记录。
详见：http://www.cnblogs.com/Jtianlin/p/5143873.html
4. 查询优化：
4.1 对于低效查询，可以通过下面两个步骤来分析：
1). 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候可能是访问了太多的列。
a. 查询不需要的记录：一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是返回全部结果集在进行计算。最简单有效的解决方法是在这样的查询后面加上LIMIT。
b. 多表关联时返回全部列
c. 总是取出全部列：每次看到SELECT * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部列？取出全部列会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的网络、IO、内存和
CPU的消耗。
d. 重复查询相同的数据：比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能会更好。
2). 确认MySQL服务器层是否在分析大量超过需要的数据行。
a. 扫描的行数和返回的行数
b. 扫描的行数和访问类型(以什么方式访问数据的，全部扫描、范围扫描等)
4.2 查询优化：
1). 避免获取不必要的列：每次看到SELECT * 的时候都需要用怀疑的眼光审视，是不是真的需要返回全部列？取出全部列会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的网络、IO、内存和CPU的消耗。
2). WHERE子句中的连接顺序：SQL SERVER采用自下而上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前，那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。
3). 尽量使用索引，尽量使用覆盖索引
4). 使用表的别名(Alias)：当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上，这样可以减少解析的时间并减少那些由Column歧义引起的语法错误。
5). 选择最有效率的表名顺序：多表查询数据少的放最后；表连接查询，数据少的做交叉表
6). 数据库连接池：预编译
7). 优化COUNT()查询：使用count(*)
8). 优化UNION查询：
a. 经常需要手工地将WHERE,LIMIT,ORDER BY等子句"下推"到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化。
b. 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL，这一点很重要。
9). 优化GROUP BY ：使用ORDER BY NULL，让MySQL文件不再进行排序。如果没有通过ORDER BY子句显式地指定排序列，当查询使用GROUP BY 子句的时候，结果集会自动按照分组
的列进行排序。如果不关心结果集的顺序，而这中默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL文件不再进行排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，
使分组的结果集按照需要的方向排序。
10). 优化LIMIT分页：使用索引；有时候也可以将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描找到对应的结果。
11). 使用缓存
12). 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
13). 切分查询：例如分时间段定时删除无用数据
14). 分解关联查询：减少锁的竞争；让缓存的效率更高；
15). 业务上优化表结构，分表存储等
16). 避免隐含的类型转换。保证SQL语句中类型与字段类型一致。
17). 慎用SELECT DISTINCT
其他优化：
1. 统一SQL语句的写法，包括大小写，这样才能复用存储方案
2. 选择最有效率的表名顺序：
1). SQLSERVER的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表driving table）将被最先处理，在FROM子句中包含多个表的情况下，必须选择记录条数最少
的表作为基础表，当SQLSERVER处理多个表时，会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表)并对记录进行排序；然后扫描第二个表(FROM子句中最后第二个表)；
最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。
2). 如果有3个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表。
3. 慎用SELECT DISTINCT：DISTINCT子句仅在特定功能的时候使用，即从记录集中排除重复记录的时候。这是因为DISTINCT子句先获取结果集然后去重，这样增加SQLServer有用资源的使用。
当然，如果你需要去做，那就只有去做了。当如果你知道SELECT语句将从不返回重复记录，那么使用DISTINCT语句对SQLServer资源不必要的浪费。
4. 在WHERE子句里不可SARG的搜索条件如"IS NULL", "<>", "!=", "!>", "!<", "NOT", "NOT EXISTS", "NOT IN", "NOT LIKE"和"LIKE '%500'"，通常（但不总是）会阻止查询优化器使用索引
执行搜索。另外在列上使用包括函数的表达式、两边都使用相同列的表达式、或和一个列（不是常量）比较的表达式，都是不可SARG的。
5. 避免困难的正规表达式：MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：
SELECT * FROM customer WHERE zipcode LIKE "98_ _ _"
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode >="98000"，在执行查询时就会利用索引来查询，显然会大大提高速度。
6. EXISTS和IN的使用：
在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。 在这种情况下，使用EXISTS(或NOT EXISTS)通常将提高查询的效率。在子查询中，NOT IN子句将执行一个内部的排序和合并。
无论在哪种情况下，NOT IN都是最低效的，因为它对子查询中的表执行了一个全表遍历。为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
7. 避免在索引列上使用IS NULL和IS NOT NULL：
避免在索引中使用任何可以为空的列，SQLSERVER将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录；对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不
为空，则记录存在于索引中。
8. 避免在索引列上使用计算：
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。
9. 用WHERE子句替换HAVING子句：
避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。
10. 避免或简化排序：
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。
11. 避免查询不需要的记录：一个常见的错误是常常会误以为MySQL会只返回需要的数据，实际上MySQL却是返回全部结果集在进行计算。最简单有效的解决方法是在这样的查询后面加上LIMIT。
12. 优化关联查询：
一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
13. 确保任何的GROUP BY 和ORDER BY中的表达式只涉及到一个表中的列。这样MySQL才有可能使用索引来优化这个过程。
14. 优化子查询：关于优化子查询我们给出的最重要的优化建议就是尽可能使用关联查询代替，至少当前MySQL版本需要这样。
15. 避免不必要的使用select * , 无论是单表查询还是多表关联时都应该注意这个问题
16. 重复查询相同的数据：比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能会更好。
17. 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
18. 使用索引覆盖扫描(在Extra列中出现Using index)来返回记录
替代like的查询方法：
事实上，可以使用 locate(position) 和 instr 这两个函数来代替
一、LOCATE语句
SELECT `column` from `table` where locate(‘keyword’, `condition`)>0
二、或是 locate 的別名 position
POSITION语句
SELECT `column` from `table` where position(‘keyword’ IN `condition`)
三、INSTR语句
SELECT `column` from `table` where instr(`condition`, ‘keyword’ )>0
locate、position 和 instr 的差別只是参数的位置不同，同时locate 多一个起始位置的参数外，两者是一样的。
mysql> SELECT LOCATE(‘bar’, ‘foobarbar’,5);
速度上这三个比用 like 稍快了一点。
