ALTER TABLE 表名 ADD 索引类型<索引名称> （索引列）
第三种方式：CREATE INDEX 命令创建（只能增加普通索引和UNIQUE索引）
#建立普通索引 CREATE INDEX index_name ON table_name (column_list); #建立外键 CREATE UNIQUE INDEX index_name on table_name (id_card)
#建立普通索引
CREATE INDEX 索引名 ON 表名(字段/列);
#建立外键
CREATE UNIQUE INDEX 索引名 on表名(外键字段)
怎么删除索引？
根据索引名删除普通索引、唯一索引、全文索引
alter table table_name drop KEY index_name;
删除主键索引：alter table 表名 drop primary key index_name（因为主键只有一个）。
这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引），需要取消自增长再行删除：
alter table table_name -- 重新定义字段 MODIFY id int, drop PRIMARY KEY
但通常不会删除主键，因为设计主键一定与业务逻辑无关。
创建索引时需要注意什么？
限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。
避免在取值朝一个方向增长的字段（例如：日期类型的字段）上建立索引；对复合索引，避免将这种类型的字段放置在最前面。由于字段的取值总是朝一个方向增长，新记录总是存放在索引的最后一个叶页中，从而不断地引起该叶页的访问竞争、新叶页的分配、中间分支页的拆分。此外，如果所建索引是聚集索引，表中数据按照索引的排列顺序存放，所有的插入操作都集中在最后一个数据页上进行，从而引起插入“热点”。
对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。
删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
使用索引查询一定能提高查询的性能吗？为什么？
使用索引查询一定能提高查询的性能吗？
通常，通过索引查询数据比全表扫描要快。但是使用索引查询不一定能提高查询性能
为什么？
因为索引需要额外的存储空间和处理，也需要定期维护，那些不必要的索引反而会使查询反应时间变慢， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，使用索引查询不一定能提高查询性能
索引就是为了提高查询性能而存在的,如果在查询中索引没有提高性能,只能说是用错了索引或者讲是场合不同
百万级别或以上的数据如何删除
由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。
我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）,然后删除其中无用数据（此过程需要不到两分钟）,删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。
前缀索引
什么是前缀索引？
前缀索引也叫局部索引，类似这种给某列部分信息添加索引的方式叫做前缀索引，比如给身份证的前 10 位添加索引
为什么要用前缀索引？
有时需要在很长的字符列（如BLOB、TEXT或很长的VARCHAR类型的列）上创建索引，这会造成索引特别大且慢。 ,这个时候可以用前缀索引，前缀索引能有效减小索引文件的大小，节约索引空间，让每个索引页可以保存更多的索引值，从而提高索引效率
但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。
什么情况下适合使用前缀索引？
当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；
前缀索引的使用？
ALTER TABLE 表名 ADD KEY(字段名(N));
N就是要用字段的前几位建立索引。
那么怎么来确认这个N是多少的呢？
先查询出来字段共有多少条数据
首先我们先查询一下字段共有多少条数据：
select count(字段名) from 表名;
这时候我们会得到一个数据，这个数据是这个字段所有数据的长度，然后我们将这个数据记录下来。记录下来之后将这个字段内的所有数据进行去重，去重函数为distinct，用我们刚才所取得的所有的数据数量除以我们去重过后得到的数据的数量，这个时候我们得到的就是我们这个字段的最大辨识度
CREATE TABLE `author` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `name` VARCHAR(32) NOT NULL COMMENT '姓名', `gender` TINYINT(1) NOT NULL COMMENT '性别，0-男，1-女', `age` TINYINT(3) NOT NULL DEFAULT '0' COMMENT '年龄', `email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '邮箱', `homepage` VARCHAR(128) NOT NULL DEFAULT '' COMMENT '主页', `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间', PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; // email列创建前缀索引 CREATE INDEX idx_author_email ON author(email(3)); // 插入5条数据 insert into `author` (`name`, `gender`, `age`, `email`) values('xx','0','20','xx@126.com'); insert into `author` (`name`, `gender`, `age`, `email`) values('yy','1','18','yy@126.com'); insert into `author` (`name`, `gender`, `age`, `email`) values('zz','0','25','zz@126.com'); insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','30','xyz123@126.com'); insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','120','xxx@163.com');
什么是最左前缀原则/最左前缀匹配原则/最左匹配原则？
当对多列创建索引后，并不是只要包含了创建索引的列就能使用索引，索引的使用要遵循最左前缀匹配原则。顾名思义就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
mysql会从左到右一直匹配直到遇到范围查询(>、<、between、like)就停止匹配
比如a = 1 and b = 2 and c > 3 and d = 4
如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为到c>3这里有个>号就会停止匹配了，所以即使你给d加了索引，也用不到这个索引
如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整，简单来说就是=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
再例如对列(A, B, C)创建索引，那么对列(A, B, C)或者(A, C)或者(A, B)进行查询会匹配索引，对(C, A)或者(B, C)来说不能使用索引,也就是都包含了第一个索引A，整个联合索引才会起效
B树和B+树
二叉查找树
二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
如下图所示就是一棵二叉查找树
对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次
二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：
但是这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。
平衡二叉树（AVL Tree）
平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。下面的两张图片，左边是AVL树，它的任何节点的两个子树的高度差<=1；右边的不是AVL树，其根节点的左子树高度为3，而右子树高度为1；
B树定义
定义：B-树是一类树，包括B-树、B+树、B*树等，是一棵自平衡的搜索树，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。
B-树是专门为外部存储器设计的，如磁盘，它对于读取和写入大块数据有良好的性能，所以一般被用在文件系统及数据库中。
定义只需要知道B-树允许每个节点有更多的子节点即可（多叉树）。子节点数量一般在上千，具体数量依赖外部存储器的特性。
为什么会出现B-树这类数据结构？
传统用来搜索的平衡二叉树有很多，如 AVL 树，红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘 IO 上。那么我们如何提高程序性能？减少磁盘 IO 次数，像 AVL 树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。
上图是一颗简单的平衡二叉树，平衡二叉树是通过旋转来保持平衡的，而旋转是对整棵树的操作，若部分加载到内存中则无法完成旋转操作。其次平衡二叉树的高度相对较大为 log n（底数为2），这样逻辑上很近的节点实际可能非常远，无法很好的利用磁盘预读（局部性原理），所以这类平衡二叉树在数据库和文件系统上的选择就被 pass 了。
空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。
我们从“迎合”磁盘的角度来看看B-树的设计。
索引的效率依赖与磁盘 IO 的次数，快速索引需要有效的减少磁盘 IO 次数，如何快速索引呢？索引的原理其实是不断的缩小查找范围，就如我们平时用字典查单词一样，先找首字母缩小范围，再第二个字母等等。平衡二叉树是每次将范围分割为两个区间。为了更快，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确。所以新建节点时，直接申请页大小的空间（磁盘存储单位是按 block 分的，一般为 512 Byte。磁盘 IO 一次读取若干个 block，我们称为一页，具体大小和操作系统有关，一般为 4 k，8 k或 16 k），计算机内存分配是按页对齐的，这样就实现了一个节点只需要一次 IO。
上图是一棵简化的B-树，多叉的好处非常明显，有效的降低了B-树的高度，为底数很大的 log n，底数大小与节点的子节点数目有关，一般一棵B-树的高度在 3 层左右。层数低，每个节点区确定的范围更精确，范围缩小的速度越快（比二叉树深层次的搜索肯定快很多）。上面说了一个节点需要进行一次 IO，那么总 IO 的次数就缩减为了 log n 次。B-树的每个节点是 n 个有序的序列(a1,a2,a3…an)，并将该节点的子节点分割成 n+1 个区间来进行索引(X1< a1, a2 < X2 < a3, … , an+1 < Xn < anXn+1 > an)。
点评：B树的每个节点，都是存多个值的，不像二叉树那样，一个节点就一个值，B树把每个节点都给了一点的范围区间，区间更多的情况下，搜索也就更快了，比如：有1-100个数，二叉树一次只能分两个范围（根节点是50），左子树范围是0-50和右子树范围是51-100，而B树（根节点是25、50、75、100），分成4个范围 1-25， 25-50，51-75，76-100一次就能筛选走四分之三的数据。所以作为多叉树的B树是更快的
平衡多路查找树（B-Tree，读B树不是B减树）
B-Tree是为磁盘等外存储设备设计的一种平衡查找树。
首先要知道系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。
InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：
mysql> show variables like 'innodb_page_size';
而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。
B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。
一棵m阶的B-Tree有如下特性
每个节点最多有m个孩子。
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有2个孩子
所有叶子节点都在同一层，且不包含其它关键字信息
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序。
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)
B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：
每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。
模拟查找关键字29的过程
根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。
B+Tree（这里我读的B加树）
B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
从上面的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
B+Tree相对于B-Tree有几点不同
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针。
数据记录都存放在叶子节点中。
将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：
通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。
因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。
数据库中的B+Tree索引可以分为聚集索引（聚簇索引）和辅助索引（非聚集索引/聚簇索引），如果B+Tree中的叶子节点存放的是整张表的行记录数据那就是聚集索引，如果叶子节点存储相应行数据的只包含主键值和索引字段那就是非聚集索引。当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。
B树和B+树的区别
- B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 O(logn)。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
如下所示B-树/B+树查询节点 key 为 50 的 data。
B-树
从上图可以看出，key 为 50 的节点就在第一层，B-树只需要一次磁盘 IO 即可完成查找。所以说B-树的查询最好时间复杂度是 O(1)。
B+树
由于B+树所有的 data 域都在根节点，所以查询 key 为 50的节点必须从根节点索引到叶节点，时间复杂度固定为 O(log n)。
点评：B树的由于每个节点都有key和data，所以查询的时候可能不需要O(logn)的复杂度，甚至最好的情况是O(1)就可以找到数据，而B+树由于只有叶子节点保存了data，所以必须经历O(logn)复杂度才能找到数据
- B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。
B+树
根据空间局部性原理：如果一个存储器的某个位置被访问，那么将它附近的位置也会被访问。
B+树可以很好的利用局部性原理，若我们访问节点 key为 50，则 key 为 55、60、62 的节点将来也可能被访问，我们可以利用磁盘预读原理提前将这些数据读入内存，减少了磁盘 IO 的次数。
当然B+树也能够很好的完成范围查询。比如查询 key 值在 50-70 之间的节点。
点评：由于B+树的叶子节点的数据都是使用链表连接起来的，而且他们在磁盘里是顺序存储的，所以当读到某个值的时候，磁盘预读原理就会提前把这些数据都读进内存，使得范围查询和排序都很快
B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确
这个很好理解，由于B-树节点内部每个 key 都带着 data 域，而B+树节点只存储 key 的副本，真实的 key 和 data 域都在叶子节点存储。前面说过磁盘是分 block 的，一次磁盘 IO 会读取若干个 block，具体和操作系统有关，那么由于磁盘 IO 数据大小是固定的，在一次 IO 中，单个元素越小，量就越大。这就意味着B+树单次磁盘 IO 的信息量大于B-树，从这点来看B+树相对B-树磁盘 IO 次数少。
从上图可以看出相同大小的区域，B-树仅有 2 个 key，而B+树有 3 个 key。
点评：由于B树的节点都存了key和data，而B+树只有叶子节点存data，非叶子节点都只是索引值，没有实际的数据，这就时B+树在一次IO里面，能读出的索引值更多。从而减少查询时候需要的IO次数！
1.B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次
从这个图可以看出B+树的50关键字出现了多次
使用B+树的好处
优点
- 单次请求涉及的磁盘IO次数少（出度d大，且非叶子节点不包含表数据，树的高度小）；
- 查询效率稳定（任何关键字的查询必须走从根结点到叶子结点，查询路径长度相同）；
- 遍历效率高（从符合条件的某个叶子节点开始遍历即可）；
在B+树中， 由于底层的各个叶子节点都通过指针组织成一个双向链表， 因此，只需要从跟节点到叶子节点定位到第一个满足条件的Key, 然后不断在叶子节点迭代next指针即可实现遍历，此时相当于顺序IO，结构如下图所示。
相反，如果通过每次从根节点查找进行遍历，相当于进行随机IO，效率低下，如下图所示：
缺点
B+树最大的性能问题在于会产生大量的随机IO，主要存在以下两种情况：
- 主键不是有序递增的，导致每次插入数据产生大量的数据迁移和空间碎片；
- 即使主键是有序递增的，大量写请求的分布仍是随机的；
Hash索引和B+树有什么区别或者说优劣呢?
Hash索引和B+树索引的底层实现原理
hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应 的键值，之后进行回表查询获得实际数据(回表查询是因为hash表中存储的是每行数据的地址)。B+树底层实现是多路平衡查找树。 对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
Hash索引和B+树有什么区别
hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持 一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
hash索引不支持使用索引进行排序，hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测，B+树都支持
hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。
因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。
数据库为什么使用B+树而不是B树
B树只适合随机检索，而B+树同时支持随机检索和顺序检索；
B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘 上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素；
B+树的查询效率更加稳定。B树的由于每个节点都有key和data，所以查询的时候可能不需要O(logn)的复杂度，甚至最好的情况是O(1)就可以找到数据，而B+树由于只有叶子节点保存了data，所以必须经历O(logn)复杂度才能找到数据（B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。）
B-树不支持范围查询，B+树支持范围查询。B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。
增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。
B+树在满足非聚簇索引的时候需要回表查询数据吗？
在B+树的索引中，当查询使用聚簇（cu第四声）索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。
数据库中的B+Tree索引可以分为聚集索引（聚簇索引）和辅助索引（非聚集索引/聚簇索引），如果B+Tree中的叶子节点存放的是整张表的行记录数据那就是聚集索引，如果叶子节点存储相应行数据的只包含主键值和索引字段那就是非聚集索引。当通过非聚集索引来查询数据时，InnoDB存储引擎会遍历非聚集索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。
InnoDB中，每个表必须有一个聚簇索引，默认是根据主键建立的。如果表中没有主键，InnoDB会选择一个合适唯一的非空索引列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列DB_ROW_ID作为聚簇索引。
什么是聚簇索引和非聚簇索引？何时使用聚簇索引与非聚簇索引？
根据索引的存储方式来划分，索引可以分为聚簇索引和非聚簇索引。聚簇索引的特点是叶子节点包含了完整的记录行，而非聚簇索引的叶子节点只有索引字段和主键ID。
什么是聚簇（聚集）索引和非聚簇（聚集）索引？
聚簇索引
聚簇索引也叫聚集索引或主键索引，它实际上并不是一种单独的索引类型，而是一种数据存储方式，聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说聚簇索引的叶子节点中包含了一个完整的记录行
特点
一个表只能创建一个聚集索引;
聚集索引尽量建在不会经常发生变动的列上，因为一旦列变动同时也会引索引结构变化，而索引结构中也包含者数据的变动；
数据库在创建主键时如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引
非聚簇索引
非聚簇索引也叫辅助索引或普通索引或二级索引，它的叶子节点只包含主键值和索引字段，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表
澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
例如一个包含了用户姓名和年龄的的数据表，假设主键是用户ID，聚簇索引的结构为（橙色的代表id，绿色是指向子节点的指针）：
叶子节点中，为了突出记录，把(id, name, age)区分开来了，实际上是连在一起的，它们是构成一条记录的整体。
而一个非聚簇索引（以age为索引）的结构是：
它的叶子节点中，不包含整个记录的完整信息，除了索引字段age本身以外，只包含当前记录的主键id。如果想要获取整行记录数据还需要再通过id号到聚簇索引中回表查询。
InnoDB中，每个表必须有一个聚簇索引，默认是根据主键建立的。如果表中没有主键，InnoDB会选择一个合适唯一的非空索引列作为聚簇索引，如果找不到合适的列，会使用一列隐藏的列DB_ROW_ID作为聚簇索引。
何时使用聚簇索引与非聚簇索引？
使用聚簇索引的情况
- 列经常被分组排序
- 返回某范围内的数据
- 小数目的不同值
- 外键列
- 主键列
非聚簇索引使用情况
- 大数目的不同值
- 频繁更新的列
覆盖索引
非聚簇索引中因为不含有完整的数据信息，查找完整的数据记录需要回表，所以一次查询操作实际上要做两次索引查询。而如果所有的索引查询都要经过两次才能查到，那么肯定会引起效率下降，毕竟能少查一次就少查一次。
以上面的age索引为例，它是一个非聚簇索引，如果我想通过年龄查询用户的id，执行了下面一条语句：
select id from userinfo where age=10;
这种情况是否还有必要去回表？因为我只需要id的值，通过age这个索引就已经能拿到id了，如果还去回表一次不就做了无用的操作了吗？实际上确实是不需要的。索引查询中，如果辅助索引已经能够得到查询的所有信息了，就无需再回表，这个就是覆盖索引。
非聚簇索引一定会回表查询吗？
不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。其实就是如果是覆盖索引就不会回表了
以上面的age索引为例，它是一个非聚簇索引，如果我想通过年龄查询用户的id，执行了下面一条语句：
select id from userinfo where age=10;
这种情况是否还有必要去回表？因为我只需要id的值，通过age这个索引就已经能拿到id了，如果还去回表一次不就做了无用的操作了吗？实际上确实是不需要的。索引查询中，如果辅助索引已经能够得到查询的所有信息了，就无需再回表，这个就是覆盖索引。这个时候非聚簇索引就不会回表了
联合索引是什么？为什么需要注意联合索引中的顺序？
联合索引是什么？
联合索引（也叫组合索引）指的是同时对多列创建的索引，创建联合索引后，叶子节点会同时包含所有索引列的值和主键id，并且同时根据多列排序，在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
例如一个包含了用户姓名和年龄的的数据表，假设主键是用户ID，聚簇索引的结构为（橙色的代表id，绿色是指向子节点的指针）：
例如对同时对上面的姓名和年龄创建的索引结构：
每个叶子节点同时保存了所有的索引列，除此之外，还是只包含了主键id。
最左前缀匹配原则
当对多列创建索引后，并不是只要包含了创建索引的列就能使用索引，索引的使用要遵循最左前缀匹配原则。
假设对列(A, B, C)创建索引，那么只有以下场景能使用索引：
对列(A, B, C)或者(A, C)或者(A, B)进行查询会匹配索引，对(C, A)或者(B, C)来说不能使用索引,也就是都包含了第一个索引A，整个联合索引才会起效
通配符只能使用LIKE 'val%'形式，不能使用LIKE '%VAL%'，后者会导致全表扫描。
索引列不能进行运算，例如WHERE A + 1 = 5这种场景会导致索引失效。
索引列不能包含范围值查询，如LIKE/BETWEEN/>/<等都会导致后面的列无法匹配索引。
索引列不能包含有NULL值。
为什么需要注意联合索引中的顺序？
MySQL使用联合索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。
当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可 以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
索引下推
新版本的MySQL（5.6以上）中引入了索引下推的机制：可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
例如针对上面表中的(name, age)做联合索引，正常情况下的查询逻辑：
通过name找到对应的主键ID
根据id记录的列匹配age条件
这种做法会导致很多不必要的回表，例如表中存在(张三, 10)和(张三, 15)两条记录，此刻要查询(张三, 20)的记录。查询时先通过张三定位到所有符合条件的主键ID，然后在聚簇索引中遍历满足条件的行，看是否有符合age = 20的记录。实际情况是没有满足条件的记录的，这个回表过程也相当于是在做无用之功。
索引下推的主要功能就是改善这一点，在联合索引中，先通过姓名和年龄过滤掉不用回表的记录，然后再回表查询索引，减少回表次数。
加了索引一定会走索引吗？索引失效的情况有哪些？
不一定，因为索引可能会失效
1、列与列对比
某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引
select * from test where id=c_id;
这种情况会被认为还不如走全表扫描。
2、存在NULL值条件
我们在设计数据库表时，应该尽力避免NULL值出现，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值，数值型可以给0、-1之类的， 字符串有时候给空串有问题，就给一个空格或其他。
如果索引列是可空的（注意是可为空！不是为空！），那索引失效，索引值是少于表的count(*)值的，所以这种情况下，执行计划自然就去扫描全表了。
3、NOT条件
我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描。说白了就是查询条件中如果有<>、NOT、in、not exists的话那么索引就会失效，例如下面的id如果建立了索引，那索引就可能失效
select * from test where id<>500; select * from test where id in (1,2,3,4,5); select * from test where id not in (6,7,8,9,0); select * from test where not exists (select 1 from test_02 where test_02.id=test.id);
4、LIKE通配符
简单说就是模糊搜索采用的前置通配符那么索引就可能会失效，例如“%明”
当使用模糊搜索时，尽量采用后置的通配符，例如：name||’%’，因为走索引时，其会从前去匹配索引列，这时候是可以找到的，如果采用前匹配，那么查索引就会很麻烦，比如查询所有姓张的人，就可以去搜索’张%’。相反如果你查询所有叫‘明’的人，那么只能是%明。这时候索引如何定位呢？前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符。
select * from test where name like 张||'%';
5、对索引列使用函数
查询条件上尽量不要对索引列使用函数，比如下面这个SQL
select * from test where upper(name)='SUNYANG';
这样是不会走索引的，因为索引在建立时会和计算后可能不同，无法定位到索引。但如果查询条件不是对索引列进行计算，那么依然可以走索引。比如
select * from test where name=upper('sunyang'); --INDEX RANGE SCAN
这样的函数还有：to_char、to_date、to_number、trunc等
不能对索引列进行函数运算，这也包括加减乘除的谓词运算，这也会使索引失效。建立一个sunyang表，索引为id，看这个SQL：
select * from sunyang where id/2=:type_id;
这里很明显对索引列id进行了’/2’除二运算，这时候就会索引失效，这种情况应该改写为：
select * from sunyang where id=:type_id*2;
就可以使用索引了。
6、数据类型的转换
当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式：
select * from sunyang where id='123'; //'123'是字符串
如果我想要强制走某个索引，能实现吗？
使用 关键字 force表示强制走括号中的索引key
select * from table_name force index (index_name) where conditions=2;
