思考

1.索引的作用是什么？

2.索引的种类有哪些？

3.什么是回表/索引覆盖/索引下推/最左前缀原则？

问题1：索引的作用？

提高数据库的查询效率，就像我们上学在查字典一样，索引就给我们提供了数据库（字典）的目录功能

问题2：索引的种类有哪些？

什么是索引？Mysql目前主要的几种索引类型_数据库_liutong123987的专栏-CSDN博客

简单概括就是：普通/唯一/主键/组合/全文索引

其中唯一和主键的两者都是要求列值唯一，区别是主键索引要求列值不能为NULL，且只能有一个主键索引

全文索引是MyISAM特有的

问题3：如何设置索引？

1.创建表时

主键索引：指定PRIMARY KEY如

create table T(

c INT PRIMARY KEY );

或

create table T(

c INT NOT NULL,

PRIMARY KEY (C) ）；

普通索引：INDEX显示指明

create table T( 

c INT PRIMARY KEY,

c1 INT NOT NULL,

INDEX (c1) );

唯一索引：UNIQUE显示指明同理如上

全文索引FULLTEXT显示指明

2.创建表之后

ALTER TABLE 表名 ADD 索引类型 （列名）

删除索引

ALTER TABLE 表名 DROP 索引类型 列名

-显示索引

desc 表名

show index from 表名

问题4：索引的类型有哪些？

根据索引的实现方式不同我们可以划分索引类型

常见的有

1.HASH（哈希表）

即key-value形式，值（value）通过一定的函数或数学方式转换后会生成一串哈希值(key)，(哈希表的时间复杂度为O1)但还是会出现多个值通过换算后会生成同一个哈希值的情况即（不同value，相同key）。

解决方法就是同key时再拉出一个链表

2.有序数组

有序数组的查询时间复杂度为O（logn）（二分法）

3.BTREE搜索树

关于InnoDB的索引类型

通过show index from 表名（前提需要给列添加索引）

我们可以观察到innodb引擎使用B+树来存储索引数据，且每一个索引对应了一颗B+树

在innodb中，主键索引被称为聚簇索引，非主键索引被称为二级索引     ----《mysql实战45讲》

因此有没有用到索引就是有没有用到b+树来进行快速查找

问题5：主键索引和普通索引的区别？

如上表假设主键索引为ID，c为普通索引

在执行select * from T where ID=1时，只需搜索ID一颗B+树；

在执行 select * from T where c=1时，需要先搜索c这颗B+树的索引并获取ID值，再根据ID搜索一次ID的B+树，即涉及两颗树的搜索；

也就是说二级索引的搜索会多一次主键树的扫描，因此尽量使用主键查询

问题6：一定要将需要查询的主要数据做主键？

理清一个概念先，在B+树中

主键索引的叶子节点为整行数据

非主键索引的叶子节点为主键的值

主键越小，普通索引的叶子节点越小，普通索引占空间也越小，所以从存储角度讲自增主键更合理

问题7:什么是回表？

回几次表可以理解为要额外查询几颗树,以下表为例 

create table A( 

id int(10) unsigned not null auto_increment comment '主键ID', 

name varchar(20) comment '普通值', 

age int(2) unsigned comment '普通索引',

primary key(id) using BTREE,

key age (age) using BTREE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

insert into A (name,age) values ('1',1);

insert into A (name,age) values ('2',2);

insert into A (name,age) values ('3',3);

insert into A (name,age) values ('4',4);

insert into A (name,age) values ('5',5);

sql语句如下 select id,name from A where age=2

1.因为age为索引，先扫描了一遍age的B+树，拿到主键 2.再扫描一遍主键的B+树，获取name 这就是回表

再复杂点between? select * from A where age between 2 and 4; 

1.先查找age树，找到age=2，得id=2 

2.回表一次查找id=2对应的数据 3.查找age树，找到age=3，得id=3 

4.回表第二次查找id=3对应的数据 

5.查找age树，找到age=4，得id=4 

6.回表第三次查找id=4对应的数据 5.查找age树，找到age=6，不满足，循环结束

所谓的回表查询，即先定位主键值，再根据主键值定位行记录，性能相对于只扫描一遍聚集索引树的性能要低一些

问题7：什么是索引覆盖？

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表 select id from A where age=2 因为age的B+树上记录的就是主键ID的值 (执行 explain select id from A where age=2 观察到extra using index 即使用了索引，进行了索引覆盖 同理聚合查询 explain select count(age) from A; )

利用索引覆盖优化between select id from A where age between 2 and 4;

问题8：什么是最左前缀原则？

最左前缀的建立就涉及到了联合索引的建立顺序 比如有联合索引 

key name_and_age (age,name)

 select * from table where name=1 和 select * from table where age=1都会用到索引 （根据mysql的原理，只要是索引或者满足索引的一部分就可以使用index方式扫描） 但是name=1效率不高！ 注意：复合索引 什么情况下才能真正被调用？最左侧的索引字段先进行了等值匹配，之后的字段变得有序时，才会用到这个复合索引。简单说。就是复合索引的字段最好有有序的才会确保用到它。 也就是说 建立了联合索引 (a,b,c)就相当于建立了(a),(a,b),(a,b,c)三个索引，从形式上看就是索引向左侧聚集，所以叫做最左原则，因此最常用的条件应该放到联合索引的组左侧。

联合索引失效的原因：联合索引 (a,b)的索引B+树是根据左侧a值的顺序来依次构造树节点的(B+树是一颗按照一定规律有序排列节点的树)，也就是说虽然这颗树的叶子节点的键是(a,b)的值，但是按序遍历这颗树时,a的顺序是可以保证的，但是b是在a相同的情况下才保证是有序的，假设以下是联合索引前序遍历结果

(1,1) (1,2) (2,1) (3,1) (3,2)

a的值依次为1,1,2,3,3 这里看到是有序的

b的值依次为1,2,1,1,2 虽然a相同是有序的，但是按需遍历时却是无序的

select * from A where a=1 and b=2

a有序排列我们可以在找到a的基础上利用二分快速查找的b(二分查找的前提是有序的)

select * from A where  b=2

在没有a的情况下是无序的，也就是说这个索引树存在和没存在都是没区别的，因此联合索引失效！

根据这个原理我们也可以分析为啥select * from A where a>1 and b=2联合索引会失效：因为虽然我们可以利用a>1快速确定字节点，但是a>1的子节点上b的排布不一定是有序的如

(2,1) (2,2)  (3,1)

这个时候也需要全部遍历(select * from A where a=1 and b>2则不同)

带头索引不能死，中间索引不能断！

问题9：什么是索引下推？

（5.6版本之后,更多也是针对联合索引） 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

索引下推（5.6版本+）_憧憬的专栏-CSDN博客_索引下推

什么时候索引失效

如下创建表并插入数据(name为普通索引，sex和age为联合索引)

create table A( id int(10) unsigned not null auto_increment, name varchar(20), age int(10),sex int(2), pimaryrimary key(id) using BTREE, key name(name),key age_sex(age,sex))engine=innodb;

insert into A (name,age,sex) values ('z3',5,1);

insert into A (name,age,sex) values ('z2',1,1);

insert into A (name,age,sex) values ('w3',1,0);

insert into A (name,age,sex) values ('l4',8,1);

1.有or时会进行全表扫描

explain select * from A where id=1 or age=1\G

这里我们观察type值为ALL，表示走了全文搜索，rows为4(一共4条)

关于or走不走索引一直有不同的见解，但是相同点是当or查询两侧没有一列是主键时，肯定走全表扫描，当有一列为主键时，mysql会自己判断走索引还是扫描全表（这根据数据量来判断，我这里没法验证）

2.没使用最左前缀原则(包括乱用!=,<>，or)

这里我们看到遵循和不遵循结果是相差很多的

3.like查询以%开头

原理:字符串在b+树中一般按字母顺序去排序，在第一个相同的情况下拿第二个去比较

4.存在数据类型的隐式转换

5.where字句存在数学运算(或使用函数)

6.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

比如数据量少的表

更多情况参考MySQL高级 之 索引失效与优化详解_wuseyukui的专栏-CSDN博客

什么情况不推荐使用索引

1.数据唯一性差的字段(取值结果只有几个)

2.平凡更新的字段

3.不在where字句中出现的字段

4.where 子句里对索引列使用不等于（<>），使用索引效果一般