创建高性能的索引
-
索引在存储引擎层实现
-
B-Tree索引
- MyISAM使用前缀压缩技术使得索引更小,通过数据的物理位置引用被索引的行;InnoDB实际使用B+Tree,按照原格式存储,根据主键引用被索引的行。
- 叶子节点指向的是被索引的数据。BTree对索引列是顺序组织存储的,适合查找范围数据
- 适合:全值匹配(和索引中所有列进行匹配);匹配最左前缀(如只使用索引第一列);匹配列前缀(只用某列开头部分);匹配范围值;精确匹配某一列并范围匹配另一列;只访问索引的查询
- 限制:非最左列开始查找无法使用;不能跳过索引中的列;如果查询中有某列的范围查询,其右边所有列都无法使用索引优化查询
-
哈希索引:只能精确匹配索引所有列。只有Memory引擎显式支持哈希索引。
- 索引结构紧凑,查询快,但是索引只包含哈希值和行指针,不能和BTree一样使用索引中的值来避免读取行。自然也无法用于排序。不支持部分索引列匹配查找。只支持等值比较查询。哈希冲突时必须遍历。
- 自适应哈希索引:InnoDB注意到某些索引值引用频繁时,会在内存中基于BTree索引之上再创建一个哈希查找(完全自动的,内部的行为)
- 创建自定义哈希索引:依然使用B-Tree查找,但是它使用哈希值而不是键本身进行索引查找
mysql> select word,crc from words where crc=CRC32('gnu') and word = 'gnu'
触发器示例代码:
#建表语句 CREATE TABLE pseudohash ( id int unsigned NOT NULL auto_increment, url varchar(255) NOT NULL, url_crc int unsigned NOT NULL DEFAULT 0, PRIMARY KEY( id) ); #触发器语句 DELIMITER // CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc= crc32(NEW.url); END; // CREATE TRIGGER pseudohash_ crc_ upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW. url_ crc= crc32( NEW. url); END; // DELIMITER;
记住不要使用
SHA1()
和MD5()
作为哈希函数。因为这两个函数计算出的哈希值是非常长的字符串,会浪费大量空间,比较时也更慢。设计的目标是最大程度消除冲突,这里不需要这么高的要求。 -
空间数据索引 R-Tree:MyISAM表支持,无须前缀索引,而是从所有维度来索引数据。查询时可以有效使用任意维度来组合查询。
-
全文索引:查找的是文本中的关键词,而非索引中的值。
-
其他索引:如TokuDB使用分形树索引。
-
-
索引优点
- 大大减少服务器需要扫描的数据量
- 帮助服务器避免排序和临时表
- 将随机I/O转变为顺序I/O
缺点:
- 使用索引会影响插入数据和修改数据的性能。
- 有时坏的索引会诱导优化器进行很差的索引查询,甚至比全表查询还要慢。
一个索引是否适合某个查询的“三星系统”:
- 索引将相关的记录放到一起则获得一星;
- 如果索引中的数据顺序和查找中的排列顺序一致则获得二星;
- 如果索引中的列包含了查询中需要的全部列则获得三星;
-
高性能的索引策略
-
独立的列:索引列不能是表达式的一部分,也不能是函数的参数,如下面行为不可
mysql> select actor_id from sakila.actor where actor_id + 1 = 5; mysql> select ... where TO_DAYS(current_date) - TO_DAYS(date_col) <=10;
-
前缀索引和索引选择性
- 索引选择性:不重复的索引值(基数cardinality)和数据表记录总数(#T)的比值。索引选择性越高查询效率越高。
- 要选择足够长的前缀以确保较高的选择性,又不能太长(可以通过实验计算)
-
多列索引:注意不是为每个列创建独立的索引,或者按照错误的顺序创建多列索引
索引合并:一定程度缓解”在多个列上建立独立的单独索引“对性能的拖垮。对于下列查询条件(注意actor_id 和film_id是分别建立的主键)
mysql> select actor_id,film_id from sakila.film_actor -> where actor_id=1 or film_id=1; # 改进 mysql> select actor_id,film_id from sakila.film_actor where actor_id = 1 -> UNION ALL -> select actor_id,film_id from sakila.film_actor where film_id = 1 -
-