Mysql索引优化

3,224 阅读11分钟

一、索引的数据结构 B-Tree(mysql主要使用 B-tree 平衡树)

聚簇索引与非聚簇索引

聚簇索引:索引的叶节点指向数据 非聚簇索引:索引的叶节点指向数据的引用

索引类型
聚簇索引 查询数据少时,无须回行  不规则插入数据,频繁的页分裂

myisam使用非聚簇索引,innodb使用聚簇索引

对于innodb引擎:

  1. 主键索引既存储索引值,又在叶中存储行数据
  2. 如果没有主键,则会使用 unique key 做主键
  3. 如果没有unique,则mysql会生成一个rowid做主键  

二、索引类型

1. 主键索引

primary key() 要求关键字不能重复,也不能为null,同时增加主键约束 主键索引定义时,不能命名

2. 唯一索引

unique index() 要求关键字不能重复,同时增加唯一约束

3. 普通索引

index() 对关键字没有要求

4. 全文索引

fulltext key() 关键字的来源不是所有字段的数据,而是字段中提取的特别关键字

关键字:可以是某个字段或多个字段,多个字段称为复合索引

建表:
creat table student(
    stu_id int unsigned not null auto_increment,
    name varchar(32) not null default '',
    phone char(11) not null default '',
    stu_code varchar(32) not null default '',
    stu_desc text,
    primary key ('stu_id'),     //主键索引
    unique index 'stu_code' ('stu_code'), //唯一索引
    index 'name_phone' ('name','phone'),  //普通索引,复合索引
    fulltext index 'stu_desc' ('stu_desc'), //全文索引
) engine=myisam charset=utf8;

更新:
alert table student
    add primary key ('stu_id'),     //主键索引
    add unique index 'stu_code' ('stu_code'), //唯一索引
    add index 'name_phone' ('name','phone'),  //普通索引,复合索引
    add fulltext index 'stu_desc' ('stu_desc'); //全文索引

删除:
alert table sutdent
    drop primary key,
    drop index 'stu_code',
    drop index 'name_phone',
    drop index 'stu_desc';

三、索引使用原则

1. 列独立

保证索引包含的字段独立在查询语句中,不能是在表达式中

2. 左前缀

like:匹配模式左边不能以通配符开始,才能使用索引 注意:前缀索引在排序 order by 和分组 group by 操作的时候无法使用。

3. 复合索引由左到右生效

建立联合索引,要同时考虑列查询的频率和列的区分度。

  1. index(a,b,c)
语句 索引是否发挥作用
where a=3 是,只使用了a
where a=3 and b=5 是,使用了a,b
where a=3 and b=5 and c=4 是,使用了a,b,c
where b=3 or where c=4
where a=3 and c=4 是,仅使用了a
where a=3 and b>10 and c=7 是,使用了a,b
where a=3 and b like '%xx%' and c=7 使用了a,b

or的两边都有存在可用的索引,该语句才能用索引。

4. 不要滥用索引,多余的索引会降低读写性能

即使满足了上述原则,mysql还是可能会弃用索引,因为有些查询即使使用索引,也会出现大量的随机io,相对于从数据记录中的顺序io开销更大。

四、mysql 中能够使用索引的典型应用

测试库下载地址:downloads.mysql.com/d...

1. 匹配全值(match the full value)

对索引中所有列都指定具体值,即是对索引中的所有列都有等值匹配的条件。 例如,租赁表 rental 中通过指定出租日期 rental_date + 库存编号 inventory_id + 客户编号 customer_id 的组合条件进行查询,熊执行计划的 key he extra 两字段的值看到优化器选择了复合索引 idx_rental_date:

MySQL [sakila]> explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
          key: rental_date
      key_len: 10
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
 1 row in set, 1 warning (0.00 sec)

explain 输出结果中字段 type 的值为 const,表示是常量;字段 key 的值为 rental_date, 表示优化器选择索引 rental_date 进行扫描。

2. 匹配值的范围查询(match a range of values)

对索引的值能够进行范围查找。 例如,检索租赁表 rental 中客户编号 customer_id 在指定范围内的记录:

MySQL [sakila]> explain select * from rental where customer_id >= 373 and customer_id < 400 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: range
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: NULL
         rows: 718
     filtered: 100.00
        Extra: Using index condition
 1 row in set, 1 warning (0.05 sec)

类型 type 为 range 说明优化器选择范围查询,索引 key 为 idx_fk_customer_id 说明优化器选择索引 idx_fk_customer_id 来加速访问,注意到这个列子中 extra 列为 using index codition ,表示 mysql 使用了 ICP(using index condition) 来进一步优化查询。

3. 匹配最左前缀(match a leftmost prefix)

仅仅使用索引中的最左边列进行查询,比如在 col1 + col2 + col3 字段上的联合索引能够被包含 col1、(col1 + col2)、(col1 + col2 + col3)的等值查询利用到,可是不能够被 col2、(col2、col3)的等值查询利用到。 最左匹配原则可以算是 MySQL 中 B-Tree 索引使用的首要原则。

4. 仅仅对索引进行查询(index only query)

当查询的列都在索引的字段中时,查询的效率更高,所以应该尽量避免使用 select *,需要哪些字段,就只查哪些字段。

5. 匹配列前缀(match a column prefix)

仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找。 例如,现在需要查询出标题 title 是以 AFRICAN 开头的电影信息,从执行计划能够清楚看到,idx_title_desc_part 索引被利用上了:

MySQL [sakila]> create index idx_title_desc_part on film_text(title (10), description(20));
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

MySQL [sakila]> explain select title from film_text where title like 'AFRICAN%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_text
   partitions: NULL
         type: range
possible_keys: idx_title_desc_part,idx_title_description
          key: idx_title_desc_part
      key_len: 32
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
 1 row in set, 1 warning (0.00 sec)

extra 值为 using where 表示优化器需要通过索引回表查询数据。

6. 能够实现索引匹配部分精确而其他部分进行范围匹配(match one part exactly and match a range on another part)

例如,需要查询出租日期 rental_date 为指定日期且客户编号 customer_id 为指定范围的库存:

MySQL [sakila]> MySQL [sakila]> explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <=400\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: rental
   partitions: NULL
         type: ref
possible_keys: rental_date,idx_fk_customer_id
          key: rental_date
      key_len: 5
          ref: const
         rows: 182
     filtered: 16.85
        Extra: Using where; Using index
 1 row in set, 1 warning (0.00 sec)
7. 如果列名是索引,那么使用 column_name is null 就会使用索引。

例如,查询支付表 payment 的租赁编号 rental_id 字段为空的记录就用到了索引:

MySQL [sakila]> explain select * from payment where rental_id is null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ref
possible_keys: fk_payment_rental
          key: fk_payment_rental
      key_len: 5
          ref: const
         rows: 5
     filtered: 100.00
        Extra: Using index condition
 1 row in set, 1 warning (0.00 sec)

五、存在索引但不能使用索引的典型场景

有些时候虽然有索引,但是并不被优化器选择使用,下面举例几个不能使用索引的场景。

1.以%开头的 like 查询不能利用 B-Tree 索引,执行计划中 key 的值为 null 表示没有使用索引
MySQL [sakila]> explain select * from actor where last_name like "%NI%"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where
 1 row in set, 1 warning (0.00 sec)

因为 B-Tree 索引的结构,所以以%开头的插叙很自然就没法利用索引了。一般推荐使用全文索引(Fulltext)来解决类似的全文检索的问题。或者考虑利用 innodb 的表都是聚簇表的特点,采取一种轻量级别的解决方式:一般情况下,索引都会比表小,扫描索引要比扫描表更快,而Innodb 表上二级索引 idx_last_name 实际上存储字段 last_name 还有主键 actot_id,那么理想的访问应该是首先扫描二级索引 idx_last_name 获得满足条件的last_name like '%NI%' 的主键 actor_id 列表,之后根据主键回表去检索记录,这样访问避开了全表扫描演员表 actor 产生的大量 IO 请求。

ySQL [sakila]> explain select * from (select actor_id from actor where last_name like '%NI%') a , actor b where a.actor_id = b.actor_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_actor_last_name
      key_len: 137
          ref: NULL
         rows: 200
     filtered: 11.11
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
     filtered: 100.00
        Extra: NULL

从执行计划中能够看出,extra 字段 using wehre;using index。理论上比全表扫描更快一下。

2. 数据类型出现隐式转换的时候也不会使用索引

当列的类型是字符串,那么一定记得在 where 条件中把字符常量值用引号引起来,否则即便这个列上有索引,mysql 也不会用到,因为 MySQL 默认把输入的常量值进行转换以后才进行检索。

例如,演员表 actor 中的姓氏字段 last_name 是字符型的,但是 sql 语句中的条件值 1 是一个数值型值,因此即便存在索引 idx_last_name, mysql 也不能正确的用上索引,而是继续进行全表扫描:

MySQL [sakila]> explain select * from actor where last_name = 1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: idx_actor_last_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 10.00
        Extra: Using where
 1 row in set, 3 warnings (0.00 sec)

MySQL [sakila]> explain select * from actor where last_name = '1'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
 1 row in set, 1 warning (0.00 sec)

3. 复合索引的情况下,假如查询条件不包含索引列最左边部分,即不满足最左原则 leftmost,是不会使用复合索引的。
4. 如果 MySQL 估计使用索引比全表扫描更慢,则不使用索引。
5. 用 or 分割开的条件,如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。

六、查看索引使用情况

如果索引正在工作, Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表名增加索引得到的性能改善不高,因为索引并不经常使用。 Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描,Handler_read_rnd_next 的值较高,则通常说明表索引不正确或写入的查询没有利用索引,具体如下。

MySQL [sakila]> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 1     |
| Handler_read_key      | 5     |
| Handler_read_last     | 0     |
| Handler_read_next     | 200   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

七、使用索引的小技巧

1. 字符串字段权衡区分度与长度的技巧

截取不同长度,测试区分度

# 这里假设截取6个字符长度计算区别度,直到区别度达到0.1,就可以把这个字段的这个长度作为索引了
mysql> select count(distinct left([varchar]],6))/count(*) from table;

#注意:设置前缀索引时指定的长度表示字节数,而对于非二进制类型(CHAR, VARCHAR, TEXT)字段而言的字段长度表示字符数,所
#      以,在设置前缀索引前需要把计算好的字符数转化为字节数,常用字符集与字节的关系如下:
# latin      单字节:1B
# GBK        双字节:2B
# UTF8       三字节:3B
# UTF8mb4    四字节:4B     
# myisam 表的索引大小默认为 1000字节,innodb 表的索引大小默认为 767 字节,可以在配置文件中修改 innodb_large_prefix 
# 项的值增大 innodb 索引的大小,最大 3072 字节。

区别度能达到0.1,就可以。

2. 左前缀不易区分的字段索引建立方法

这样的字段,左边有大量重复字符,比如url字段汇总的http://

  1. 倒过来存储并建立索引
  2. 新增伪hash字段 把字符串转化为整型
3. 索引覆盖

概念:如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘,这种查询,速度极快,江湖人称——索引覆盖

4. 延迟关联

在根据条件查询数据时,如果查询条件不能用的索引,可以先查出数据行的id,再根据id去取数据行。 eg.

//普通查询 没有用到索引
select * from post where content like "%新闻%";
//延迟关联优化后 内层查询走content索引,取出id,在用join查所有行
select a.* from post as a inner join (select id from post where content like "%新闻%") as b on a.id=b.id; 
5. 索引排序 

排序的字段上加入索引,可以提高速度。

6. 重复索引和冗余索引

重复索引:在同一列或者相同顺序的几个列建立了多个索引,成为重复索引,没有任何意义,删掉 冗余索引:两个或多个索引所覆盖的列有重叠,比如对于列m,n ,加索引index m(m),indexmn(m,n),称为冗余索引。

7. 索引碎片与维护

在数据表长期的更改过程中,索引文件和数据文件都会产生空洞,形成碎片。修复表的过程十分耗费资源,可以用比较长的周期修复表。

//清理方法
alert table xxx engine innodb; 
//或
optimize table xxx;
8. innodb引擎的索引注意事项

Innodb 表要尽量自己指定主键,如果有几个列都是唯一的,要选择最常作为访问条件的列作为主键,另外,Innodb 表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效的减少索引的磁盘占用,提高索引的缓存效果。