高性能 mysql -- 第五章
19 August 2018

哈希索引

哈希索引的缺点:

  1. hash index 只存了 hash 值和行指针,没有字段值,不能走覆盖索引
  2. 不能用于排序
  3. 使用联合索引的时候不能走最左前缀规则。例如 idx(A, B),hash index 里面存储的是 A,B 的 hash 值,只根据 A 来查,查不到
  4. 只有 =, in, ! 这些操作会走索引,<, >, between 等范围查询不会走 hash index

自适应哈希索引
意思是在 b-tree 上创建一个 hash 索引,这个操作是 innodb 自动完成的。
innodb 如果发现某个索引值用得频繁,会在内存中基于 b-tree 索引上再创建一个 hash index,这样就可以让 b-tree 索引也具有 hash index 的一些优点。

用户自定义的 “哈希索引”
在有长字符串的比较时候,为长字符串的 hash 值建立索引
尽量不要用 sha1 和 md5 来做哈希函数,浪费空间,而且慢

create table urls (
     url varchar(1024) not null default ''
) engine = innodb default charset = utf8mb4;
alter table urls add index idx_url (url);

select * from urls where url = 'xxxx';


create table urls (
        url varchar(1024) not null default '',
        url_crc int unsigned not null default 0
        ) engine = innodb default charset = utf8mb4;
alter table urls add index idx_url_crc (url_crc);

select * from urls where url = 'xxxx' and url_crc = crc32('xxx');

index merge

默认情况下,mysql 只会选择一个索引,但 index merge 是个例外。例如

select * from tb where col1 < 100 or col2 < 100

不能只根据 col1 或者 col2 的索引来查。mysql 会判断当结果集很小的时候,会走这两个索引,索引取出来后再来 merge。
index merge 的性能并不一定好

排序

order by col1, col2 顺序 (2018-09-12 update: mysql 8 开始,这个情况已经改变了 http://www.innomysql.com/descending-index/)

alter table add index idx (col1, col2);
order by col1 asc, col2 asc    走
order by col1 desc, col2 desc  走
order by col1 asc, col2 desc   不走
order by col1 desc, col2 asc   不走


alter table add index idx (col1 asc, col2 desc);
order by col1 asc, col2 desc   走
order by col1 desc, col2 asc   走
order by col1 asc, col2 asc    不走
order by col1 desc, col2 desc  不走

如果真要需要的话,需要再创建一个 col,用来存储该列值的反转串或者相反数

另外,explain 出来的 using filesort 不一定使用磁盘文件,innodb 有个 buffer 专门用来排序的

order by 的问题
类似 order by xxx limit 8000000, 10 这种,mysql 要过滤前面的 8000000 这么多行,方法是先从表里取出 id,再 inner join 一次自身

select * from tb inner join (select id from tb where xxx order xxx limit 8000000, 10) as tmp using(id)

多种过滤条件

考虑一个约会网站,用户信息有国家,地区,城市,性别等。

alter table tb add index idx (sex, country, xxx);

为什么选择性低的 sex 作为前缀
几乎所有的查询都会用到 sex 列。如果查询没有 sex 列,也可以用 sex in ('m', 'f') 来绕过(卧槽,亏你想得出。。。)

当有多个范围查询的时候,可以使用下面的技巧

alter table add index idx(col1, col2)

explain select * from tb where col1 < 10000 order by col2 desc;    -- 全表扫描
explain select * from tb where col1 < 10 order by col2 desc;       -- 走索引(mysql 自动判断了)
explain select * from tb where col1 in (1,2,3) order by col2 desc; -- 走索引

索引的原则

什么情况下不要建索引

  1. 频繁更新
  2. 唯一性太差(例如用户性别这种,不能作为索引)

什么情况下不会走索引

  1. where col=func(xxx) 如果 func 是用户自定义的函数,不会走。系统的函数会走
  2. where func(col)=xxx 不会走。可以建立虚拟列来走索引
  3. 复合索引没有根据左前缀原则
  4. where like

    where like "%xx"   不走索引
    where like 'xx%'   会走索引
    where like "xx%xx" 会走索引
    
  5. 强制类型转化,有可能不走

    where str_col = "123"  会走索引
    where str_col = 123    不走索引
    where num_col = "123"  走索引
    where num_col = 123    走索引
    # 对数字的 col,加不加 "" 都可以走索引
    # 对字符串的 col,不加 "" 不会走索引
    
  6. order by 多列的时候顺序和创建索引的时候不一致

    alter table add index idx (col1, col2);
    order by col1 asc, col2 asc    走
    order by col1 desc, col2 desc  走
    order by col1 asc, col2 desc   不走
    order by col1 desc, col2 asc   不走
    
    
    alter table add index idx (col1 asc, col2 desc);
    order by col1 asc, col2 desc   走
    order by col1 desc, col2 asc   走
    order by col1 asc, col2 asc    不走
    order by col1 desc, col2 desc  不走
    

重建索引表

alter table tb engine=innodb;