Performance Tuning - NO.1 Index

索引的类型

http://www.db110.com/?p=1886

索引是在存储引擎中实现的,而不是mysql server级, 但基本的索引类型原理大致类似.

以下为主要的索引类型:

B树索引:

大部分的mysql 存储引擎支持B树索引,Archive engine是一个例外,5.1才支持一个auto_increment 列的索引. B树在MYISAM中的实现采用了前缀压缩的技术,这样索引更小,提高性能效率,而innodb则不压缩索引; MYISAM是通过存储的行物理位置信息来定位行,而innodb是通过主键值来定位行记录的.

由于B树存储记录是排序的,索引很适合范围查找;

以下为B树索引的一些限制:

l  查询比较必须是前导列 ;

l  不能跳过复合索引中的某个字段 ,如某索引包含3个字段(name,birth,work_on),如果查询条件只引用了name,work_on,那么只会应用name这一个索引;

l  Mysql优化器,不能优化第一个范围查找之后的条件查询 ;如有复合索引(last_name,first_name,dob) ,查询select … whre last_name=”smith” and first_name like ‘J%’ and dob=’1976-12-23’,那么将只会应用到索引前两个字段.因为first_name是一个范围查找,这个限制可以使用”相等”条件来旁路.

l  B树索引现仅支持前导列. 支持完整键值查找(match the full value),键的范围查找(match a range of values),键前缀查找(match a column prefix) . mysql的复合索引也支持前一部分准确匹配,后一部分范围查找的情形.

由上可知,B树复合索引其中字段的顺序将影响索引的行为,所以我们可能需要创建多个复合索引(拥有同样的列,但次序不同),以适应我们的查询.

哈希索引

哈希索引是通过构建哈希数组(表) ,通过对值进行hash,查找hash表 , 找到真实的行记录的(hash表存储了行记录的指针. 必须使用哈希索引的所有字段进行条件查询.

目前仅memory storage支持哈希索引,这也是memory tables的默认索引类型.

哈希值可能有冲突,有冲突的值用行指针链表的方式处理.由于hash表存储段的hahs值, hash条目的长度和要索引的字段无关,结构很紧凑.

哈希索引的效率一般比B树高,因为hash可能一次命中,而B树查找可能多个节点,需要多次,但哈希索引也有许多限制

l  哈希索引存储的是 hash code,那么就必须再次查找指针指向的真实记录,而B树要查找的数据可能就已经在索引中了,而不需要去查找真实的行记录. 但memory table的访问是很快的,这点无妨.

l  Hash索引存储不是不排序的,所以不能利用索引来排序

l  哈希索引不支持部分键匹配,哈希值是对所有字段的一个哈希 .

l  哈希索引仅支持 “相等” 连接 ,如= ,in() ,  ó ,也不能加速范围查找.

l  哈希索引是适用于高选择性的值,虽然查找很快,但如果冲突很大,许多值的哈希值相同,那么存储引擎就需要遍历查指针链表比对真实的value来查找正确的 记录,从而影响性能; 同理,冲突率也影响维护操作,一个低选择性(many hash collisions)的表,删除记录,查找行的代价可能很昂贵.

由于以上诸多限制,hash索引适合比较特殊的场合,比如在数据仓库中作为”star” 模式的lookup表 .

NDB也支持unique hash indexes.

Innodb内部支持一个特性;adaptive hash indexes.如果innodb发现一些索引值非常频繁的访问,那么它会在B树索引之上再构建一个内存中的hash索引,以加快访问,这些都是自动的,我们无法控制.

如果我们的存储引擎不支持hash索引,我们也可以构建自己的”hash索引”,通过额外存储一个字段的hash值到记录中,然后查找条件通过查找这个特殊字段来存取数据.

url比价适合这种方式保存,可以用触发器实现自动存储hash(url). 建议用简单的hash函数(可自定义),)实现(一些复杂的算法是强加密函数,这不是我们的目标) ,hash后的值为整型(不要采用字符型),因为整型比对更有效率 , Maatkit(http:///maatkit.sourceforge.net )实现了一个64bit的hash函数,可参考.

一般来说简单的hash函数足够了,各种算法的冲突概率可查 算法专业书籍.哈.

关于spatical indexes,full text indexes略,很少会用到.

 

优化排序

Filesort:在这种方式中，主要是由于没有可以利用的有序索引取得有序的数据，MySQL只能通过将取得的数据在内存中进行排序然后再将数据返回给客户端。 使用filesort的方式排序小结果集很快,但是如果是大量的数据,速度将很慢.

如这个查询. mysql> SELECT <cols> FROM profiles WHERE sex=’M’ ORDER BY rating LIMIT 10;

如果没有索引,以上查询将很慢,即使有了索引,也不定快, 程序的展示页面可能是分页显示,很多页,有些人点击的是中间的某个页,类似如下的查询.
mysql> SELECT <cols> FROM profiles WHERE sex=’M’ ORDER BY rating LIMIT 100000, 10;

这种查询,无论如何索引,都效率奇差,因为高起始(high offset)值,会花费大部分时间扫描大量数据,而这些数据最终会被丢弃;这种情况下,反范式,预计算,缓存可能是好的选择; 一个更好的办法是限制用户看到的页,因为没有什么用户会关注第10000页的内容

另一个办法是使用cover index ,来减少返回的需要排序的结果集.如以下示例

索引index on (sex, rating)

mysql> SELECT <cols> FROM profiles INNER JOIN

-> SELECT <primary key cols> FROM profiles

-> WHERE x.sex=’M’ ORDER BY rating

-> ) AS x USING(<primary key cols>);

 

更新索引统计

Mysql调用两个api,来获取索引情况给优化器使用,
records_in_range( ): 返回范围内的大概记录数;

info( ):返回各种信息,如索引基数,

mysql的优化器是基于成本的,最重要的因素是访问的记录数,我们可以调用ANALYZE TABLE 来获取最新的统计 ,以免mysql优化器生成错误的执行计划;

l  Memory engine不存储索引统计;

l  MYISAM存储统计在磁盘上,执行analyze table的时候,会full index scan来计算基数,会在此过程中lock整个表

l  Innodb不存储统计在磁盘上,但是会粗略的估计,所以innodb的统计不怎么准确,innodb 存储引擎analyze table是非阻塞的,相对myisam来说不是昂贵的操作.所以可以在线操作而不怎么影响系统性能;

mysql> SHOW INDEX FROM sakila.actor/G  可以查看下基数.

可以显示很多信息..具体略.

也可以查询INFORMATION_SCHEMA.STATISTICS表;

减少索引和数据碎片

碎片很多的索引,充满了大量的不连续的块,会严重影响性能;

B+索引需要随机访问磁盘来最终定位到叶子节点,这种随机不可避免,但leaf node顺序紧凑存放,对性能有很大好处,如果非常不连续(碎片多),那么一个full index scan可能会慢几倍, 对cover index尤其如此;

表的数据也可能碎片很多,有两种碎片:1. 行碎片,1行存储在多个物理位置;2. 行间碎片,逻辑上连续的行或者页,在物理上并无连续.(full table scan,cluster index range则需要顺序的磁盘数据分布为佳).

为了减少碎片,我们可以运行 optimize table命令或者重新装载数据 .这种方法适用于大部分存储引擎.

对于myisam,也会重建索引,对于innodb,目前没有办法整理index的碎片,即使重新装载数据也会存在碎片,因为索引的分布依赖于数据的分布.

对于不支持optimize table的存储引擎可以使用”no-op”操作,如.

mysql> ALTER TABLE <table> ENGINE=<engine>.

 

http://hi.baidu.com/litaosmile/blog/item/088db182ed5c5da80df4d2d4.html

B Tree索引只能在索引字段从左往右的搜索里起到优化作用
3) MyISAM支持针对地理数据的Spatial(R-Tree)索引，该索引算法能够满足地理维度独立对等的查找