创建高性能的索引

创建高性能的索引

• 索引在存储引擎层实现

  • 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
    	-