《高性能mysql》笔记-索引

索引基础

索引类型
B-Tree索引（默认指明索引）
按照顺序存储数据

哈希索引
概念：哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。
哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
（只有Memory引擎显式支持哈希索引）

Innodb的“自适应哈希索引”
InnoDB引擎有一个特殊的功能叫“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引。
这是一个完全自动的、内部的行为，用户无法控制或者配置。

image

伪哈希索引

思路：在B-Tree基础上创建伪哈希索引，这和真正的哈希索引不是一回事。它是使用哈希值而
不是键本身进行索引查找，需要在查询的where子句中手动指定使用哈希函数；

实例：

需要存储大量的url ， 并需要根据url进行搜索查找。直接用B-Tree来存储url存储的内容就会很大，
正常情况的查询有：

mysql> select id from url where url="http://www.mysql.com";

若删除原来的url列上的索引，而新增一个被索引的url_crc列 ，使用CRC32做哈希，就可以使用下面的方式查询：

  mysql> select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找

缺陷：需要维护哈希值。可以手动维护，也可以使用触发器实现。

使用触发器实现在插入和更新是维护url_crc列：

创建如下表：
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)
);

创建触发器，先临时修改一下语句分隔符，这样就可以在触发器定义中使用分号：
mysql> delimiter //
mysql> create trigger pseudohash_crc_ins before insert on pseudohash for each row begin set new.url_crc=crc32(new.url); end;//
mysql> create trigger pseudohash_crc_upd before update on pseudohash for each row begin set new.url_crc=crc32(new.url); end;//
mysql> delimiter ;#注意这里的空格键

那么，新增或更新url同时url_crc也回自动更新；

注意：
采用这种方式切记不要使用SHA1()和MD5()作为哈希函数。这两个函数是强加密函数，计算出来的hash值时非常长的字符串，会浪费大量空间，比较时也会更慢。它们设计目标是最大限度消除冲突（这里不需要这样高的要求）。如果数据表非常大，crc32()会出现大量的哈希冲突，可以考虑实现一个简单的64位哈希函数（返回整数），例如：

mysql> selectCONV(RIGHT(MD5('http://www.mysql.com/'),16),16,10) as hash64;

为了避免哈希冲突导致查询无法正常工作,where条件包含常量值

url='http://www.mysql.com'：
select id from url where url="http://www.mysql.com" and url_crc=CRC32("http://www.mysql.com");

高性能索引策略

1.独立列

2.前缀索引

作用：
有时候需要索引很长的字符列，这会让索引变得很大且慢。
一个方法使用伪哈希索引，另外还可以是前缀索引；
前缀索引：可以大大节约索引空间，从而提高索引效率。但会降低索引的选择性(基数)；

image

创建前缀索引

alter table huang add index `indexname`(city(7));// 选择索引前缀长度为7

image

3.多列索引

image

注意:选择合适的索引列顺序

4.聚簇索引

聚簇索引并不是一种单独索引类型，而是一种数据存储方式。
InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
如果没有定义主键，InnoDB会选择唯一的非空索引替代。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

缺点：
1、如果数据全部放到内存中，聚簇索引没什么优势；
2、更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
3、二级索引可能比想象的要更大，因为二级索引的叶子节点包含了引用行的主键列；
4、二级索引访问需要两次索引查找；

• 二级索引（非聚簇索引）叶子结点保存的不是指向行的物理位置的指针，而是行的主键值；意味着通过二级索引查找行，
  存储引擎需要找到二级索引的叶子结点获得对应的主键值，然后根据这个值去聚簇索引中查找到对应的行。
  两次B-Tree查找，自适应哈希索引能够减少这样的重复工作。（好处：在移动行数据时无须更新二级索引的这个“指针”）

image

顺序主键什么时候会造成更坏的结果？

对于高并发工作负载，在innodb中主键顺序插入可能会造成明显的争用。主键的上界会成为"热点"，并发插入可能导致间隙锁竞争。
另一个热点可能是AUTO_INCREMENT锁机制；
解决：重新设计表或应用，或更改innodb_autoinc_lock_mode配置(可能不支持)；

5.覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称“覆盖索引”；

覆盖索引的好处

1、索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量；
2、如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询；