SQL_索引优化

 mysql的查询流程

mysql客户端通过协议与mysql服务器建立连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，有一系列预处理，比如检查语句是否写正确了，然后是查询优化（比如是否使用索引扫描，如果是一个不可能的条件，则提前终止），生成查询计划，然后查询引擎启动，开始执行查询，从底层存储引擎调用API获取数据，最后返回给客户端。怎么存数据、怎么取数据，都与存储引擎有关。然后，mysql默认使用的BTREE索引，并且一个大方向是，无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

 

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 '员工记录表';

添加三列的复合索引

alter table staffs add index idx_nap(name, age, pos);

1. 全值匹配

如select * from staffs where name = 'July' and age = '23' and pos = 'dev' 

2. 匹配最左列

对于复合索引来说，不总是匹配所有字段列，但是可以匹配索引中靠左的列。 　　

如select * from staffs where name = 'July' and age = '23'，key字段显示用到了索引，

注意，key_len字段（表示本次语句使用的索引长度）数值比上一条小了，意思是它并未使用全部索引列，

事实上只用到了name和age列

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）上却可以，不测试下还真不能确定说就用到了索引==

5. 精确匹配一列并范围匹配右侧相邻列

即前一列是固定值，后一列是范围值，它用了name与age两个列的索引

（key_len推测） 　　如select * from staffs where name = 'July' and age > 25

6. 只访问索引的查询

比如staffs表的情况，索引建立在(name,age,pos)上面，前面一直是读取的全部列，如果我们用到了哪些列的索引，

查询时也只查这些列的数据，就是只访问索引的查询，

如

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