高性能mysql学习笔记

简要记录一下 高性能mysql 的学习. 书的链接: http://shop.oreilly.com/product/0636920022343.do

1.条件语句后面不要加运算表达式，举例如下:

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
因为这里有个计算表达式，数据库不会优化，所以开发者应该改成 actor_id =4；

2.改变数据的默认值： modify the .frm value rather than the original table, which can avoid table duplication

3.对字符串的prefix index
对于BLOB or TEXT，整个字符串太长，不能对整个字符串做index，解决方法是 Prefix indexes（只index某个长度的index），
而这个长度需要根据统计信息，统计的思想是:用尽量短的长短，使通过这个长度的index，找到尽量少的结果(减少最后匹配次数)。 举例如下：
数据库存有apple,average,application，现在要搜索apple。 
假设index长度为1，先开始根据a搜到apple，average，application，然后再从三个里面逐一匹配
如果index长度为2，先开始根据ap搜到apple，application，然后再从两个里面逐一匹配。

MySQL cannot use prefix indexes for ORDER BY or GROUP BY queries

4.suffix index
对某些数据(email address)suffix index会很有效，但mysql不支持，解决方案是把字符逆序存进去

5.不同版本的mysql对query有不同执行方案，举例如下:
mysql> SELECT film_id, actor_id FROM sakila.film_actor
-> WHERE actor_id = 1 OR film_id = 1;

在index了actor_id 和film_id的情况下，mysql 5.0以前是不会对查询做任何优化，只会做table scan
但5.0以后版本会union of two index scans(先分别index，然后union)

但要注意的是，union of two index scans不一定比 table scan效率好(因为如果两个index scans返回大量结果(书上叫not selective)，那么merge的cost就很高)
一个可能的解决方案是 使用single index on multiple column，但这个方案会带来问题(1.索引太大 2.建索引时间长)

single index on multiple column 是使用multicolumn b tree，应该把可选择高的column放在最前(将第一次查询的返回结果尽量减少)

6.不同数据库引擎的（innoDB MyISAM） 区别:聚簇索引(Clustered Index)和非聚簇索引 (Non- Clustered Index)
http://www.tuicool.com/articles/VramY3Y
InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上
MyISM使用的是非聚簇索引，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键， 叶子节点都是指针， 指向行数据【1】

多个单列索引和复合索引区别：
因为数据库查询时候只能执行一个索引，所以多个单列索引不会对组合的搜索词有帮助
复合索引是指在第一列排序的前提下，再对第二列排序，所以对组合关键词的搜索有帮助，但前提是符合索引的第一列要是where里面的第一关键词【2】

covering index:在index加上行其他的数据，
用处一: 在innodb的 second index很有用，因为在second index搜完还要在first index上面搜。
在covering index帮助下，直接在second index上搜完就好
用处二:数据排序，直接遍历index

【1】:http://www.tuicool.com/articles/M3IrE33
【2】:http://www.cnblogs.com/gudi/archive/2014/10/29/4058411.html

7.索引

因为建索引需要cost维护(同步更新)，所以要衡量是否建索引
建索引考虑两点：
1.可选择性（所建的列是否有很多不同的值）
2.使用频率(是不是很多query都用得上)
3.考虑多个query而不是单个query


举例，假设要对一个人建table，属性如下:(sex, country, region, city, age).
对于sex这属性，可选择性低，但很多query会以sex区分，所以应建立索引 sex 

假设现在有索引（sex，country），但如果query仅仅包括country，而没有包括sex，那么如何加快query？
方法一:单独建country的索引表，但增加维护的cost
方法二:在query里面加上 in('m','f'),那么query会利用索引(sex,country)。因为如果query仅包含country，country不符合prefix原则，不能使用(sex,country)索引

8.实时性不高的query

对于某些实时性要求不高的query，例如查七天没登录的用户，直接从lastonline与now时间来计算的话，会导致查询(range query)很慢.
解决方法是:设置一个active属性，设置一个定时任务在后台定时更新维护

9.解决数据库频繁的短连接

引入连接池来管理连接的原因：
1.创建数据库连接有时间开销，2.频繁创建短连接会导致不够时间关闭连接(例如for循环里不断创建并关闭，关闭不够创建得快)，导致连接达到上限。
解决方案：提前创建连接，限制连接上限