高性能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循环里不断创建并关闭,关闭不够创建得快),导致连接达到上限。
解决方案:提前创建连接,限制连接上限



阅读更多

没有更多推荐了,返回首页