MySQL索引
程序员文章站
2024-01-20 21:25:46
...
索引(index)是帮助MySQL高效获取数据的数据结构
索引的目的在于提高查询效率，可以类比字典
可以理解为：排好序的快速查找数据结构
优势
1.类似大学图书馆建立书目索引，提高数据检索的效率，降低数据库的IO成本
2.通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势
1.实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的
2.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert，updata，delete，因为更新表时，mysql不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段都会调整因为更新锁带来的键值变化后的索引信息
3.索引只是提高效率的一个因素，如果你的mysql有大数据量的表，及需要花时间研究建立最优秀的索引或优化查询
索引分类
1.单值索引
一个索引只包含单个列，一个表可以有多个单列索引
2.唯一索引
索引列的值必须唯一，但允许有空值
3.复合索引
一个索引包含多个索引列
基本语法
//创建
create [unique] index indexName on tableName(columnName(length))
alert tableName add [unique] index indexName on(columnName(length))
//例如
create index idx_book_id on book(id)
//指定索引的数据结构类型
create index idx_book_id on book(id) using btree
//删除
drop index[indexName] on tableName
//查看
show index from tableName;索引结构
1.BTree索引
2.Hash索引
3.full-text全文索引
4.R-Tree索引
哪些情况适合建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引
5.Where条件里用不到的字段不创建索引
6.倾向创建组合索引，而不是单键索引
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段
哪些情况不适合建索引
1.表记录太少
2.经常增删改的表
3.如果某个数据列包含许多重复的内容，为他建立索引就没有太大的效果(每一行的该列值相同，如国籍)
EXPLAIN
使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理的sql语句的。
//使用方法 explain SQL语句
mysql> explain select * from tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tbl_emp | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+对以上各字段的解释
1.id
select查询的***，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同：执行顺序由上至下
id不同：如果是子查询，id的序号会递增，id值越大，优先级越高，越先被执行
id相同不同，同时存在
2.select_type 查询类型
simple：简单的select查询，查询中不包含子查询或者UNION
primary： 查询中若包含任何复杂的子查询，最外层查询则被标记为primary
subquery：在select或where列表中包含了子查询
derived：在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放到临时表里
union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived
union result：从union表获取结果的select
3.table 表
4.type 访问类型
显示查询使用了何种类型
从最好的最差
system>const>eq_ref>ref>range>index>all
5.possible_keys
显示可能应用到这张表上的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一样被实际查询使用
6.key
实际使用的索引，如果为null，则没有使用索引，若查询中使用了覆盖索引，则该索引仅出现在key列表中
7.key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好，
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，通过表定义获得，而不是实际
8.ref
显示索引的那一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
9.rows
根据表统计信息以及索引选用情况，大致估算出找到所需的记录锁需要读取的行数
10.extra
覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据我呢见，换句话说，查询列要被所建的索引覆盖
explain能干嘛
1.表的读取顺序
2.数据读取操作的操作类型
3.那些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询
索引优化 案例
单表
create table if not exists `article`(
id int(10) unsigned not null primary key auto_increment,
author_id int(10) unsigned not null,
category_id int(10) unsigned not null,
views int(10) unsigned not null,
comments int(10) unsigned not null,
title varbinary(255) not null,
content text not null
);
insert into article(author_id,category_id,views,comments,title,content) values
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3'),
(1,1,1,2,'4','4');
mysql> select * from article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
| 4 | 1 | 1 | 1 | 2 | 4 | 4 |
+----+-----------+-------------+-------+----------+-------+---------+
//查询category_id为1且comments大于1的情况下，views最多的article_id
mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
//对语句进行分析。发现type为all，还用了filesort
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------`
//查看表的索引
mysql> show index from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
//尝试对where后的条件建立索引
mysql> create index idx_article_ccv on article(category_id,comments, views);
//分析语句，查看结果
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 2 | 100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
//若把comments改为=1，更加优化了，因为之前comment是一个范围，让comment后的索引失效了
mysql> explain select id,author_id from article where category_id = 1 and comments = 1 order by views desc limit 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | article | NULL | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
//单独对category_id和comments建立索引
mysql> create index idx_article_cv on article(category_id,views);
//分析语句。发现情况变好了
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | article | NULL | range | idx_article_cv | idx_article_cv | 4 | NULL | 3 | 33.33 | Using index condition; Using where |
+----+-------------+---------+------------+-------+----------------+----------------+---------+------+------+----------+------------------------------------+
多表
create table if not exists class(
id int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(id)
);
create table if not exists book(
bookid int(10) unsigned not null auto_increment,
card int(10) unsigned not null,
primary key(bookid)
);
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into class(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
insert into book(card) values(floor(1+(rand()*20)));
//分析左外连接的查询语句 type有all
mysql> select * from class left join book on class.card = book.card;;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
//给book的card字段建立索引
mysql> create index idx_book_card on book(card);
//分析语句，发现book的type为ref了
+----+-------------+-------+------------+------+---------------+---------------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------------+---------+-----------------+------+----------+-------------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |
| 1 | SIMPLE | book | NULL | ref | idx_book_card | idx_book_card | 4 | test.class.card | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------------+---------+-----------------+------+----------+-------------+
//删除book的索引，给class建立索引
mysql> drop index idx_book_card on book;
mysql> create index idx_class_card on class(card);
//分析
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | class | NULL | index | NULL | idx_class_card | 4 | NULL | 20 | 100.00 | Using index |
| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+----------------------------------------------------+可以看到，左连接索引加在右表，也就是book，效果更好
若是右连接，同理，索引加在左表
索引失效
//建表
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 '员工记录表';
//插入记录
insert into staffs(name,age,pos,add_time) values('z3',22,'manager',now());
insert into staffs(name,age,pos,add_time) values('July',23,'dev',now());
insert into staffs(name,age,pos,add_time) values('2000',23,'dev',now());
//查询
mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+------+-----+---------+---------------------+
| 1 | z3 | 22 | manager | 2018-07-30 13:58:44 |
| 2 | July | 23 | dev | 2018-07-30 13:58:44 |
| 3 | 2000 | 23 | dev | 2018-07-30 13:58:45 |
+----+------+-----+---------+---------------------+
//创建索引
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);1.全值匹配我最爱
//按照索引的建立，在where中和索引一样，有每个字段，并且顺序一致
select * from staffs where name = 'july' and age = 25 and pos='123';
mysql> explain select * from staffs where name = 'july' and age = 25 and pos='123';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
2.最佳左前缀法则
顺序不必一致，因为会被mysql查询优化器优化
但是为了提高效率，避免mysql的翻译和转换，最好顺序一致
//where条件必须从索引的第一列开始，并且中间不能跳过
//只有第一项 用到了索引
mysql> explain select * from staffs where name = 'july';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
//从第二项开始，无索引
mysql> explain select * from staffs where age = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
//跳过第二项，索引只会用到第一列，不会用到第三列
mysql> explain select * from staffs where name = 'july' and pos = '1';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+3.不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而转向全表扫描
//对name截取4个字符，name无法用到索引,这一项也就是name前面的不会受影响
mysql> explain select * from staffs where left(name,4) = 'july';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+4.存储引擎不能使用索引中范围条件右边的列
//age使用的是范围，age可以用到，但是导致后面的pos无法用到索引
mysql> explain select * from staffs where name = 'july' and age > 25 and pos='123';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | staffs | NULL | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
//使用覆盖索引，指定查询的字段，数据直接从索引获取
mysql> explain select name,age,pos from staffs where name = 'july' and age = 25 and pos='123';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+6.mysql在使用不等于(!= < > )的时候无法使用索引会导致全表扫描
//使用！= 索引失效，name无法使用索引
mysql> explain select * from staffs where name != '123';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+7.is null，is not null也无法使用索引
//name无法使用索引
mysql> explain select * from staffs where name is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
mysql> explain select * from staffs where name is not null;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+8.like以通配符开头(‘%abc…’) mysql索引失效会变成全表扫描的操作
若like不是第一项，则like前面都有用，like以及like后会失效
“abc%”不会失效
//必须以通配符开头，只用like没用，name不会用到索引
mysql> explain select * from staffs where name like '%j';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
//如何解决
//使用覆盖索引
mysql> explain select name, age from staffs where name like '%j';
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | staffs | NULL | index | NULL | idx_staffs_nameAgePos | 140 | NULL | 3 | 33.33 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
9.字符串不加单引号索引失效
//字符串不加单引号不会用到索引，测试时，要写数字，会被转化为字符串，直接写字符串会报错，name不会用到索引
mysql> explain select * from staffs where name = 2000;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+10.少用or，用它来连接时会导致索引失效
//
mysql> explain select * from staffs where name = 'zhangsan' or name='lisi';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | staffs | NULL | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | 66.67 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+聚集索引和非聚集索引
聚集索引
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。
非聚集索引
非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。
聚集
create clustered index CLU_ABC on abc(A)
非聚集
create nonclustered index NONCLU_ABC on abc(A)