一、索引的数据结构 B-Tree(mysql主要使用 B-tree 平衡树)
聚簇索引与非聚簇索引
聚簇索引:索引的叶节点指向数据
非聚簇索引:索引的叶节点指向数据的引用
索引类型
优
劣
聚簇索引
查询数据少时,无须回行
不规则插入数据,频繁的页分裂
myisam使用非聚簇索引,innodb使用聚簇索引
对于innodb引擎:
主键索引既存储索引值,又在叶中存储行数据
如果没有主键,则会使用 unique key 做主键
如果没有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. 复合索引由左到右生效
建立联合索引,要同时考虑列查询的频率和列的区分度。
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 中能够使用索引的典型应用
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://
倒过来存储并建立索引
新增伪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 表的普通索引都会保存主键的键值,所以主键要尽可能选择较短的数据类型,可以有效的减少索引的磁盘占用,提高索引的缓存效果。