1
2
3
4
5
6
7
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位',
add_time timestamp not null default current_timestamp comment '入职时间'
) charset utf8 comment '员工记录表';
添加三列的复合索引
1
alter table staffs add index idx_nap(name, age, pos);
在BTREE索引的使用上，以下几种情况可以用到该索引或索引的一部分（使用explain简单查看使用情况）：
1. 全值匹配
如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ，key字段显示使用了idx_nap索引
2. 匹配最左列，对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列
如select * from staffs where name = 'July' and age = '23'，key字段显示用到了索引，注意，key_len字段（表示本次语句使用的索引长度）数值比上一条小了，意思是它并未使用全部索引列（通常这个长度可估摸着用了哪些索引列，埋个坑），事实上只用到了name和age列
再试试select * from staffs where name = 'July'，它也用了索引，key_len值更小，实际只用到了索引中的name列
3. 匹配列前缀，即一个索引中列的前一部分，主要用在模糊匹配，如select * fromstaffs where name like 'J%'，explain信息的key字段表示使用了索引，但是mysql的B树索引不能非列前缀的模糊匹配，如select * from staffs where name like '%y' 或者 like '%u%'，据说是由于底层存储引擎的API限制
4. 匹配范围，如select * from staffs where name > 'Mary'，但俺在测试时发现>可以，>=却不行，至少在字符串列上不行（测试mysql版本5.5.12），然而在时间类型（timestamp）上却可以，不测试下还真不能确定说就用到了索引==
1
2
3
4
5
6
select * from indexTest1 where count > '10'
select * from indexTest1 where count >= '10'
select * from indexTest1 where count > '10%'
select * from indexTest1 where count >= '10%'
select * from indexTest1 where count > '%10%'
select * from indexTest1 where count >= '%10%'
5. 精确匹配一列并范围匹配右侧相邻列，即前一列是固定值，后一列是范围值，它用了name与age两个列的索引（key_len推测）
如select * from staffs where name = 'July' and age > 25
1
2
select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'
select name,age from staffs where name = July and age > 25
第一句用到了全部索引列，第二句只用了索引前两列，select的字段就最多只能是这两列，这种查询情况的索引，mysql称为覆盖索引，就是索引包含（覆盖）了查询的全部字段。是不是用到了索引查询，在explain中需要看最后一个Extra列的信息，Using index表明使用了覆盖索引，同时Using where表明也使用了where过滤
1
select count(distinct left(a, 7))/count(*) as non_repeat from tab;
定好一个前缀数目，如9，添加索引时可以这样
1
2
alter table tab add index idx_pn(name(9)) --单独前缀索引
alter table tab add index idx_cpn(count, name(9)) --复合前缀索引
以上为常见的使用索引的方式，有这么些情况不能用或不能全用，有的就是上面情况的反例，以key(a, b, c)为例
1. 跳过列，where a = 1 and c = 3，最多用到索引列a；where b = 2 and c = 3，一个也用不到，必须从最左列开始
2. 前面是范围查询，where a = 1 and b > 2 and c = 3，最多用到 a, b两个索引列；
3. 顺序颠倒，where c = 3 and b = 2 and a = 1，一个也用不到；
4. 索引列上使用了表达式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表达式是一大忌讳，再简单mysql也不认。有时数据量不是大到严重影响速度时，一般可以先查出来，比如先查所有有订单记录的数据，再在程序中去筛选以'cp1001'开头的订单，而不是写sql过滤它；
5. 模糊匹配时，尽量写 where a like 'J%'，字符串放在左边，这样才可能用得到a列索引，甚至可能还用不到，当然这得看数据类型，最好测试一下。
排序对索引的影响
order by是经常用的语句，排序也遵循最左前缀列的原则，比如key(a, b)，下面语句可以用到（测试为妙）
1
2
3
select * from tab where a > 1 order by b
select * from tab where a > 1 and b > '2015-12-01 00:00：00' order by b
select * from tab order by a, b
以下情况用不到
1. 非最左列，select * from tab order by b;
2. 不按索引列顺序来的，select * from tab where b > '2015-12-01 00:00:00' order by a;
3. 多列排序，但列的顺序方向不一致，select * from tab a asc, b desc。
聚簇索引与覆盖索引
前面说到，mysql索引从结构上只有两类，BTREE与HASH，覆盖索引只是在查询时，要查询的列刚好与使用的索引列完全一致，mysql直接扫描索引，然后就可返回数据，大大提高效率，因为不需再去原表查询、过滤，这种形式下的索引称作覆盖索引，比如key(a,b)，查询时select a,b from tab where a = 1 and b > 2，本质原因：BTREE索引存储了原表数据。