sql高级操作

一、索引

不同的存储引擎索引也不一样，如MyISAM的全文索引，即便索引叫一个名字内部组织方式也不尽相同，最常用的当然就是InnoDB了（还有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），这里写的是InnoDB引擎。而索引的实现也跟存储引擎，按照实现方式分，InnoDB的索引目前只有两种：BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引，InnoDB的BTREE索引，实际是用B+树实现的，因为在查看表索引时，mysql一律打印BTREE，所以简称为B树索引。 使用了BTREE索引，意味着所有的索引是按顺序排列存储的（升序）。每次搜索是一个区间搜索，有的话就找到了，没有的话就是空。索引能加快访问速度，因为有了它无需全表扫描数据（不总是这样），根据查找的值，跟节点中的值比较，通常使用二分查找，对于排好序的数值来说，平均速度几乎是最快的

一列的索引称为单列索引，多列的称为复合索引，因为BTREE索引是顺序排列的，所以比较适合范围查询，但是在复合索引中，还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

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. 只访问索引的查询

比如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