3.索引

3.1说说你对Mysql索引的理解

索引是帮助Mysql高效获取数据的排好序的数据结构。
索引是在存储引擎层中实现的。因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。Mysql索引的存储类型主要有BTree和Hash，具体和表的存储引擎相关。MyIsam和InnoDB存储引擎只支持BTree索引；Memory存储引擎支持Hash和BTree索引。
【优点】：
（1）通过创建唯一索引，可以保证数据库表中每一行该列字段的唯一性。
（2）索引大大减少服务器需要扫描的数据行数。
（3）索引可以帮助服务器避免排序和使用临时表。
（4）索引可以将随机IO变成顺序IO。
【缺点】：
（1）创建索引和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
（2）索引需要占用一定的磁盘空间，除了数据库占数据空间之外，每一个索引还要占用一定的物理空间。
（3）当对表中的数据进行增加、删除和修改的时候，索引页要动态地维护，这样就降低了数据的维护速度。

3.2索引有哪几种？

mysql的索引可以分为以下几类
（1）普通索引和唯一索引
普通索引是Mysql中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引要求索引列的值必须唯一，但允许有空值。
主键索引是一种特殊的唯一索引，不允许有空值。
（2）单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会生效。使用组合索引时遵循最左前缀匹配原则。
【最左前缀匹配原则是什么】？
比如where关键字后面跟了多个条件判断，我们就可以为这些字段建立一个联合索引。打个比方，如果是员工表有三个字段name,age,departmant，我们需要经常使用到这三个字段做条件查询，就这为这三个字段建立一个联合索引。

create index name_age_department on employees(name,age,department);

建立了联合索引之后，InnoDB存储引擎会帮我们创建三个索引，分别是index(name),index(name,age),index(name,age,department)；怎么建立这个联合索引其实取决于区分度，需要把区分度高的条件列字段放在最左边，把区分度低的条件列字段往右边放。
最左前缀匹配原则：
1.mysql会一直从左往右匹配直到遇到了范围查询(>\<\between\like)就停止匹配。比如where name=’Bill’ and age>30 and department=’dev’；这样的语句mysql的优化器就只能走index(name,age)的组合索引，不会使用到第三个字段的组合索引。这种情况也称之为索引下推。如果是where age=30 and deparement =’dev’；这样压根就不会走索引。因为第一个字段没有匹配上。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。如果我们的where条件条件使用到了三个等值查询无论是什么顺序，mysql优化器都一定会走index(name,age,department)的组合索引。
【为什么一定要遵循最左前缀匹配原则】？
那我们来看看为什么如果不遵循最左前缀匹配原则的话，mysql到底会怎么执行的？

where name=’Bill’ and age>30 and department=’dev’；
组合索引也是InnoDB存储引擎使用B+Tree的数据结构来组织的，严格按照递增的顺序来组织实现。使用这条查询语句时，mysql底层直接筛选出name=’Bill’的结果集，后面的name不是’Bill’的直接不用看了，然后根据age再来进行筛选，
where age=30 and deparement =’dev’
如果使用这条查询语句，那Mysql怎么走索引呢？名字叫’Bill’的员工也有age=30的，名字叫’Lilei’的员工也有age=30的，mysql只能走全表扫描。所以在建立组合索引的时候，一定要根据实际情况把区分度高的条件列字段一定要往左边放。
（3）前缀索引和倒序索引
【前缀索引】：
现在几乎所有的系统都支持邮箱登录，那如何在邮箱这样的字段上建立合理的索引。

create table SUser(
ID bigint unsigned primary key,
email varchar(64), 
)engine=innodb;

如果要使用邮箱登录，业务代码中一定会出现类似于这样的语句；

select f1, f2 from SUser where email='xxx';

mysql是支持前缀索引的，也就是说可以定义字符串的一部分作为前缀索引

mysql> alter table SUser add index index1(email);
或
mysql> alter table SUser add index index2(email(6));

如果使用的是index2(email(6)的索引结构)，执行顺序是这样的：
（1）从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；
（2）到主键上查到主键值是ID1的行，判断出email的值不是’zhangsxyz@xxx.com’，将这行记录丢弃；
（3）取index2上刚刚查找的位置的吓一跳记录，发现仍然是’zhangs’，取出id2，再到ID索引上取郑航然后判断，这次值对了，将这行记录加入结果集；
（4）重复上一步，直到index2上取到的值不是’zhangs’，循环结束。
也就是说，使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。实际上，我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

select 
  count(distinct left(email,4)）as L4,
  count(distinct left(email,5)）as L5,
  count(distinct left(email,6)）as L6,
  count(distinct left(email,7)）as L7,
from SUser;

【倒序索引】
场景：如果现在维护的数据库是一个市的公民信息系统，这时候如果对身份证做长度为6的前缀索引的话，这个索引的区分度就非常低了。索引选取得越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。
使用倒序存储，存储身份证号的时候把它倒过来存，每次查询的时候可以这么写。

select field_list from t where id_card = reverse('input_id_card_string');

 由于身份证的最后6位没有地址码生日这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。<br />（1）从CPU消耗方面来看，倒序方式每次写和读的时候，都需要额外调用一次reverse函数。<br />（2）从占用空间来看，倒序存储的方式仍然在索引上，不会占用额外开销。

3.3如何创建即保存mysql的索引

（1）在创建表的时候创建索引

create table t1(
  id int not null,
  name char(30) not null,
  unique index UniqIdx(id)
);

（2）在已存在表上增加索引

alter table book add unique index UniqIdx(bookId);
或者是
create unique index UniqIdx On book(bookId);

3.4mysql如何判断要不要加索引？

索引是用来快速地寻找那些具有特定值的记录，所有mysql索引都以b+树的形式保存。如果没有索引，执行查询的mysql语句时必须从第一个记录开始扫描整个表的所有记录，直到找到符合要求的记录。表里面的数据数量越多，这个操作的执行效率就越低。如果作为索引条件的列已经创建了索引，mysql就可以迅速得到目标记录所在的位置。
建议按照以下的原则来创建索引
（1）避免对经常更新的表进行过多的索引。应该对经常用于查询的字段创建索引，要避免添加不必要的字段。
（2）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，而且创建索引也需要占用额外的空间。
（3）在条件表达式经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建议索引。比如学生表的性别字段上只有男和女两个不同值，因此就无需建议索引，如果建立索引不但不会提升查询效率，反而会严重降低数据的更新效率。
（4）当唯一性是某种数据本身的特征时，可以指定唯一索引。使用唯一索引需要确保定义的列的数据完整性，以提高查询速度。
（5）在频繁进行order by或group by的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

3.5只要创建了索引，就一直会走索引吗？

（1）在使用组合索引时就必须遵从”最左前缀”的原则进行搜素，否则索引是不起作用的。打个比方，我在id,name,age的字段上建立了一个名为combine的组合索引，索引行以id、name、age的顺序进行存储，索引可以搜索id、id name、id name age的字段组合，但如果where列字段不构成最左前缀，mysql就不能使用局部索引，比如age和nama、age组合就不能使用该索引查询。
（2）like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
（3）当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
（4）数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
（5）当全表扫描速度比索引速度快时，走全表扫描。
打个比方，比如abc三个字段建立了组合索引，
select from t_table where a>1；
这种情况mysql的优化器会觉得，走index(a)的索引去取对应的主键还要回表通过主键去主键索引树上找真实的数据记录，还肯定是不如直接走全表扫描的，所以这种情况mysql肯定是会走全表扫描的。全表扫描其实就是去主键索引树上的叶子节点去遍历那条双向链表。
（6）当我们在查询条件中对索引列进行了表达式计算，就会导致索引失效。
select from t_user where id + 1 = 10; 不走索引
select from t_user where id = 10-1; 会走索引
（7）对索引使用了函数，就会导致索引失效。【注意】：这与倒序索引不同，那个是reverse传入的值
select from t_user where length(name)=6;不走索引

3.6索引是越多越好吗

索引不是越多越好，一个表中如果有大量的索引，不仅占用磁盘空间，还会影响insert、delete、update等语句的性能，因为在表中的数据更新时，索引页会进行调整和更新。

3.7所有的字段都适合创建索引吗？

以下几种情况，是不适合创建索引的：
（1）频繁更新的字段不适合建立索引；
（2）where条件中用不到的字段不适合建立索引；
（3）数据比较少的表不需要建立索引；
（4）数据重复且分布比较均匀的字段不适合建立索引，例如性别、真假值等等
（5）参与列计算的列不适合建立索引。

3.8说说搜索引的实现原理

在Mysql中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的， 可以在建立数据库表的时候指定不同的存储引擎来实现索引结构。
【MyIsam索引实现】：
MyIsam引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址指针。在MyIsam中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyIsam索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
【InnoDB索引实现】：
虽然InnoDB也是使用B+Tree作为索引结构，但具体实现方式却与MyIsam截然不同。
第一个区别是InnoDB的数据文件本身就是索引文件。MyIsam的索引文件和数据文件是分离的，索引文件仅保存数据记录的指针。而在InnoDB中，表数据文件本身就是按B+Tree组织的的一个索引结构，这棵树的叶子节点data域保存了完整的数据记录。这个索引的key就是数据库表的主键，这种索引叫作聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键，而MyIsam可以没有，如果没有显式指定，则Mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则Mysql自动会为InnoDB表生成一个隐含字段作为主键，这个字段的长度为6字节，类型为长整型。

第二个区别是InnoDB辅助索引data域存储的是主键的值而不是地址，InnoDB的所有辅助索引都引用主键作为data域。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要回表查询，首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。通过这个区别我们就可以知道，为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。用非单调的字段作为主键在InnoDB表中也不是一个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree特性而频繁分裂调整。

3.9说说InnoDB存储引擎和MyIsam存储引擎的区别？

mysql存储引擎默认使用InnoDB存储引擎，如果需要使用MyIsam存储引擎可以在创建表的时候指定表的存储引擎的实现方式。
区别：
（1）事务方面：InnoDB是事务安全的，而MyIsam不支持事务。InnoDB给Mysql提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。
（2）锁机制方面：InnoDB支持行级锁，间隙锁，而MyIsam只支持表级锁。
（3）存储方面：在mysql/data目录下，创建一张表的存储实现是不同的。打个比方如果是user表
另外在sql查询中，可以自由地将InnoDB类型的表与其他Mysql表的类型混合起来，在一个查询中也可以混合。InnoDB是为处理巨大数据量的最大性能设计，它的cpu效率应该是其他基于磁盘的关系型数据库引擎不能匹配的。
【MyIsam】
会生成user.frm、user.myi、user.myd三份文件，frm后缀存储的是表结构信息；myd文件存储的是user表的真实数据记录；myi文件索引的是user表的索引信息。
【InnoDB】
只会生成user.frm、user.ibd两份文件。其中的ibd文件包含了这张表的索引信息和真实的数据记录信息。
虽然myisam与Innodb的索引结构都是用b+树组织的，但是mysisam索引文件与数据文件分离，myisam的叶子结构存储的key为索引列字段，value存储应该是6位的映射物理磁盘地址，所以一般来说用innodb的查询效率是比myisam稍微快一点，因为可以少一次磁盘io交互。
（4）count()函数：
MyIsam直接把一个表的总行数存在了磁盘上，因此执行count()的时候会直接返回这个数，效率很高；但InnoDB引擎就比较麻烦了，它执行count()的时候，需要把数据一行一行去引擎里面读取出来，然后累计计数。
从效率上来说count(*)=count(1)>count(主键)>count(字段)
count()是一个聚合函数，函数的参数不仅可以是字段名， 也可以是其他表达式，该函数的作用就是统计符合查询条件的记录中，函数指定参数中不为null的记录有多少个。

select count(1) from t_order;

这条语句就是统计t_order表中有多少个记录，因为1这个表达式永远不为null。

select count(id) from t_order;   id是主键

就是记住原则，server层向存储引擎层要什么，引擎层就给什么；如果count函数指定的参数不为null，那变量count就+1，直到符合查询的全部记录被读完，就退出循环。存储引擎层读取一条id然后返回给server层，server层判断id是不是null，如果不是就把count变量+1，然后就这样一直循环。但是count(id)主键的效率并不好，mysql底层的优化器优化的时候如果这个表有二级索引，会使用二级索引而不会使用主键索引。
因为主键索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。相同数量的二级索引记录可以比聚集索引记录占用更少的存储空间，所以二级索引树比聚集索引树小，这样遍历二级索引的IO成本比遍历主键索引的IO成本小，所以优化器选择的是二级索引。

select count(name) from t_order;   name是字段;

如果是这种方式的话，那就更慢了。每次执行器拿到存储引擎层返回的name结果都需要去看一下这个name是不是空，如果不是空就+1，因为主键的值是不能为空的，所以count(主键)>count(字段)。
为什么InnoDB不跟MyIsam一样，也把数字存起来呢？
因为在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB表应该返回多少行也是不确定的。这和InnoDB的事务设计有关，可重复读是它默认的隔离级别，在代码上就是通过MVCC来实现的。每一行记录都要判断自己是否对这个会话可见，因此对于count(*)来说，InnoDB只好把数据一行一行地读出来依次判断。
解决方案：
比如如果需要统计首页有多少篇文章这种频繁更新的字段，这时候可以使用redis来直接持久化存储这个数量。每次查询的时候直接从redis中获取数量，这样也能避免使用count()函数效率低，MyIsam的性能不如InnoDB好的问题了。

3.11组合索引的存储结构是什么，它的有效方式是什么？

如果我们需要对mysql一张表上的多个字段进行条件查询或者范围查询时，就可以为了提高mysql的检索效率，为这些字段建立一个组合索引。需要注意的是我们在为这多个字段建立组合索引的时候，应该把区分度高的字段放在前面，区分度低的段放在后面。比如性别、年龄、状态这种字段区分度就很低，所以我们一般放在后面。索引的底层是一颗b+树，联合索引的底层也就是一颗b+树，由于构建一颗b+树只能根据一个值来确定索引关系，所以数据库依赖组合索引的最左字段构建。
另外如果我们想要使用组合索引来优化查询的效率，就比如遵守最左前缀匹配原则，即最左优先，在检索数据时从组合索引的最左边开始匹配。需要注意的是，如果我们为数据库的三个字段(a,b,c)建立了组合索引，sql查询语句where 不管(a,b,c,(b,c,a)啥的)都能够使用到组合索引，这是因为mysql有查询优化器explain，所以sql语句字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断这条sql语句究竟以什么样的顺序执行效率最高，最后才生成真正的执行计划，所以不管以何种顺序都可以使用到组合索引。另外还需要注意的是，当我们为sql语句中使用到了abc三个字段，通过explain语句查询sql执行流程时，会发现type字段是ref，表示非唯一性索引扫描，并且key_len字段的值为这三个字段的总长度；我们如果只使用了a或者ab，这也符合最左前缀匹配原则，explain语句查询sql执行流程时，key_len字段的值为a字段长度或者a+b字段的长度；如果我们查询的where条件不符合最左前缀匹配原则，但explain语句执行输出的type字段值为index,这表示它会对整个索引树进行扫描，但它的效率远不如最左前缀匹配原则的查询效率高，index类型的扫描方式是从索引第一个字段一个一个查找，直到找到符合的某个索引，与all不同的是，inde是对所有索引树进行扫描，而all是对整个磁盘的数据进行全表扫描。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

3.12Mysql的存储引擎为什么使用B+Tree这种数据结构来作为实现？

其实Mysql的设计者不是没有考虑过其他的数据结构作为索引的实现。那我们就来分别对比一下这些实现。
【二叉搜索树】
如果使用二叉搜索树作为索引实现，二叉搜索树的特性是每个父节点有左右两个子节点，左子节点小于父节点，右子节点大于父节点。按照这种特征，升序插入1、2、3、4、5、6这六条数据记录。其实这就相当于是一个单向链表，单向链表的查询时间复杂度是O（n），这种效率低的数据结构明显不能使用。

【红黑树】
红黑树与AVL树最大的不同是红黑树不是一种绝对平衡的树，它通过左旋右旋和变色达到相对平衡的状态，每个父节点有左右两个子节点。但是当我依次插入1、2、3、4、5、6、7、8，插入到第八条数据记录的时候，树层就达到了4层，如果插入百万条数据记录的话，每遍历一次相等于一次磁盘io，那效率也非常低。

【B树】
所以 既然不能纵向拓展效率低，那就必须想办法横向拓展 Mysql的设计者就想到了多路树来解决问题，一个父节点存储多个子节点的能力称为这课树的度。比如10度的树，一个父节点就能存储10个子节点。 而b+树与b树的不同地方是，1.b树的每个节点没有设置冗余节点，并且存放了真实的数据记录。2.叶子节点没有通过双向链表的方式组织起来。如果是范围查找id>13。找到id是13的数据记录后，还得返回父节点继续查找。虽然能够通过磁盘加载文件到内存，使得即时比对的效率很高，但是如果数据量大，这种返回父节点再进行搜索的效果肯定是不如b+树的。

【B+树】
那B+树有什么好处呢？在InnoDB存储引擎中，每页个页的大小为16KB。我们来算算3层的b+数能存放多少数据记录。

b+树的非叶子节点主键之间，中间的空白区域存储真实的磁盘索引地址占6字节，每个主键索引就算他是bigint占用8字节，索引文件以页存储每页可存16kb，每页大约可存161024/6+8=1170个索引值及其下级节点磁盘地址。主键的叶子索引存储真实的数据记录，撑死了算这个数据记录占用1Kb，那一页可存16个真实的数据记录。三层的b+树可以存放11701170*16个数据记录。
这也是因为b+树的特征来实现的：
（1）非叶子节点不存储真实数据记录，子层节点会把父节点的主键值存储索引(冗余)，可以存更多的索引。
（2）叶子节点包含所有索引字段
（3）叶子节点用指针连接，提高区间访问的性能。
【总结】
Mysql需要将数据持久化在磁盘，而存储功能是由Mysql存储引擎实现的。要设计一个索引数据结构，不仅仅要考虑数据结构增删改查的时间复杂度，更重要的是要考虑磁盘IO操作次数。因为索引和数据记录是存放在磁盘的，磁盘是一个非常慢的存储设备，我们在查询数据的时候，需要尽可能减少磁盘IO的操作次数。
二分查找树虽然是一个天然的二分结构，能很好利用二叉查找快速定位数据，但是它存在一种极端的情况每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，查询的时间复杂度从O(logN)降低为O(N)。
为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在O(logN)。但是它本质上还是一颗二叉树，每个节点只能有2个子节点，随着元素的增多，树的高度就会越多越高。而树的高度就决定了磁盘IO操作次数，因为树是存储在磁盘中的，访问每个节点，都对应一次磁盘IO操作。
B树和B+树都是通过多叉树的方式，会将树的高度变矮，但是这两个数据结构非常适合检索存于磁盘中的数据。
但是Mysql的存储引擎MyIsam和InnoDB都是采用B+树作为索引的数据结构，原因有：
（1）B+树的非叶子节点不存放实际的数据记录，仅存放索引，因此在数据量相同的情况下，相比存储既存索引又存记录的B树，B+树的非叶子节点可以存放更多的索引，因此B+树可以比B树更矮胖，查询底层节点的磁盘IO次数会更少。
（2）B+树有大量的冗余节点(所有非叶子节点都是冗余索引)，这些冗余索引会让B+树在插入、删除的效率都更高。
（3）B+树叶子节点之间使用双向链表连接起来，有利于范围查询。而B树要实现范围查询的时候，只能通过树的遍历来完成范围查找，这会涉及多个节点的磁盘IO操作，范围查找效率不如B+树。

3.13Mysql的Hash索引和B树索引有什么区别？

hash索引底层就是hash表，进行查找时调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点放可获得所查剑指，然后根据查询判断是否需要回表查询数据。但是哈希索引有明显的缺点。
（1）hash索引进行等值查询的时候速度更快，但是却无法进行范围查找。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。
（2）hash索引不支持使用索引排序。
（3）hash索引不支持模糊查询以及组合索引的最左前缀匹配。
（4）hash索引虽然在等值查询上快，但是不稳定，性能不可预测。当存在哈希冲突的时候，它取决于使用什么策略来处理哈希冲突。
自适合哈希索引：
如果要在b+数里面准确定位一条数据，那也许要经过3-4次Io才能找到数据页，那怎么让查询变得更加高效呢？哈希就是个不错的方法，在哈希表中查询一条数据的时间复杂度是o(1)。InnoDb会监控表上各索引页的查询，如果观察该数据被访问的频次符合规则,那么就建议哈希索引来加快速度访问的速度，自适应哈希索引是通过缓冲池的b+树页来构建的，建立的速度很快，并且不对整棵树都建立哈希索引，可以理解为只有热点数据才会进入这个哈希表。哪些页会被构成哈希索引?以同一查询条件进行了100次以上的访问。

3.14聚集索引和非聚集索引有什么区别？

在InnoDB存储引擎中，可以将B+树索引分为聚集索引和非聚集索引。无论是何种索引，每页的大小都是16Kb，且无法更改。
聚集索引是根据主键创建的一颗b+树，聚集索引的叶子节点存放了表中的所有记录。辅助索引是根据索引键创建的一颗b+树，与聚集索引不同的是，其叶子节点仅存放索引键值以及该索引键值指向的主键值。也就是说如果通过辅助索引来查找数据，那么当找到辅助索引的叶子节点后，有可能还需要根据主键值查找聚集索引来得到真实的数据记录，这又称之为回表。因为辅助索引不包含数据记录的所有数据。但是这也意味着辅助索引的每页能存放更多的键值，因此其高度一般都要小于聚集索引。

3.15select in语句中如何使用索引？

索引是否其作用，主要取决于字段类型：
（1）如果字段类型为char或者是varchar，需要给in查询中的数值添加引号，索引才能其作用。
（2）如果字段类型为int型，则in查询中的值不需要添加引号，索引也会起作用。

3.16模型查询语句中如何使用索引？

在Mysql中模糊查询moblie like ‘%3155’，这种情况不能使用mobile上的索引的，那么如果需要根据手机号码后四位进行模糊查询的话，可以用以下方法进行改造。
加入虚拟列，比如mobile_reverse，内部存储为mobile的倒序文本，为mobile_reverse字段建立索引，查询中使用语句mobile_reverse like ‘5513%’或者是mobile_reverse like reverse(‘%3155’);

3.17InnoDB是如何存储数据的？

Mysql支持多种存储引擎，不同的存储引擎，存储数据的方式也是不同的。以我们最常使用的InnoDB存储引擎为例。数据记录的基本单位是行，但是数据库的读取并不以行为单位，否则一次读取(也就是一次IO)只能处理一行数据，效率会非常低。
因此，InnoDB的数据是按[数据页]为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读取出来，而是以页为单位，将其整体读入内存。数据库的IO操作的最小单位是页，InnoDB数据页的默认大小是16KB，意味着数据库每次读写都是以16KB为单位的，一次最少从磁盘读取16KB内容到内存，一次最少把内存中的16KB内容刷新到磁盘中。
数据页包括7个部分，结构如下：


在File Header中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页就相等于一个双向链表。物理上不连续，但逻辑上连续。
数据页中包含用户记录，每个记录之间按照【主键】顺序组成单向链表，单向链表的特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上所有节点才能完成检索。为了加快在数据页内高效查询记录，设计了一个页目录，页目录存储各个槽(分组)，每个分组大约有6条真实的数据记录，且主键值是有序的，于是可以通过二分查找算法来进行检查从而提高效率。页目录和分组也是用来快速定位一条数据记录的。

为了高效查询记录所在的数据页，InnoDB采用B+树作为索引，每个节点都是一个数据页。将数据库中的所有记录平均分成几个组，页目录用来存储每组最后一条记录的地址。页目录中用来指向每个分组中最后一条记录的数据结构叫作槽。
查询步骤：
（1）通过二分查找算法查找该记录所在的槽，并找到该槽所在分组中主键最小的那条记录。
（2）通过记录的next_record属性遍历该槽所在组中的各个记录。
如果叶子节点存储的是实际数据就是聚集索引，一个表只能有一个聚集索引；如果叶子节点存储的不是实际数据，而是主键就是二级索引，一个表中可以有多个二级索引。在使用二级索引进行查找时，如果查询的数据能在二级索引找到，那么就是[覆盖索引]操作，如果查询的数据不在二级索引里，就需要先在二级索引找到主键值，需要去聚集索引中获得数据行，这个过程叫作回表。

3.18 order by的工作原理

假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前 1000 个人的姓名、年龄。

select city,name,age from t where city='杭州' order by name limit 1000  ;

【全字段排序】

Mysql会给每一个线程分配一块内存用于排序，称为sort_buffer。
执行流程：
（1）初始化sort_buffer，确定放入name、city、age三个字段；
（2）从索引city找到第一个满足city=’杭州’条件的主键id；
（3）到主键id索引树上取出整行，取name、city、age三个字段的值，存入sort_buffer中；
（4）从索引city取下一个记录的主键id；
（5）重复步骤345，直到city的值不满足查询条件；
（6）对sort_buffer中的数据按照字段name做快速排序；
（7）返回排序结果的前1000行给客户端；
按”name”排序的这个动作，有可能在内存中完成，也有可能需要使用外部排序，这取决于排序所需内存和参数sort_buffer_size。sort_buffer_size就是Mysql为排序开辟的内存的大小。如果需要排序的数据量小于sort_buffer_size，排序就可以在内存中完成。但如果排序数据量太大，内存放不大，就不得不使用磁盘临时文件辅助排序；如果Mysql使用是磁盘临时文件辅助排序使用的就是归并排序。
【row_id排序】：使用max_length_for_sort_date命令，指定空余排序的行数据长度参数。
执行流程：
（1）初始化sort_buffer，确定放入两个字段，name和id；
（2）从索引city找到第一个满足city=’杭州’条件的主键id；
（3）到主键id索引树上取name、id两个字段存人sort_buffer中；
（4）从索引city取出下一个主键id；
（5）重复步骤34，直到city的值不满足查询条件
（6）对sort_buffer中的name字段进行排序；
（7）遍历排序结果，取前1000行，按照id的值再回表去取出city、name、age三个字段的值返回给客户端；
Mysql如果实在担心排序的内存太小会影响排序效率的话，才会使用row_id排序，这样排序过程中，一次可以排更多行，但是需要回表去取数据；
Mysql如果认为内存足够大，会优先选择全字段排序，把需要的字段都放入到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不需要再回表去取数据。
row_id排序要求回表多，因此不会被优先选择。
【优化】：
Mysql之所以需要生成临时表，并且在临时表上做排序操作，原因是原来的数据都是无序的。所以我们可以在表上创建一个index(city、name)的组合索引；
执行流程：
（1）从索引(city、name)中找到第一个满足’杭州’条件的主键id；
（2）到主键id索引中取出整行，取出name、city、age三个字段的值，作为结果集的一部分；
（3）到索引(city、name)取下一个记录主键id；
（4）重复步骤234，直到查到第1000行记录，或者是不满足条件就退出循环。

这样Extra字段也不会有Using filesort了，而且由于(city、name)这个组合索引本身有序，所以这个查询就不用把4000行全部都读一遍再进行排序，只要找到满足条件的前1000行记录就可以退出了。