索引（B+tree、联合唯一索引、）

参考博客1
参考博客2

MySQL 索引类型有：唯一索引，聚集（主键）索引，非聚集索引，全文索引

前言

假设有联合索引（scontent、score），唯一主键索引则

1. 下面的查询都使用了联合索引

   EXPLAIN select * FROM user where content + score = 2 and  score = 90
   EXPLAIN select * FROM user where score + content = 2 and score = 1
   

   结果：
   

2. 下面的查询没有使用联合索引

   EXPLAIN select * FROM user where content + score = 2
   

   结果：
   解释：

1、id
是每个select查询的序列号，一个sql中有几个查询就会有几个序列号，id越大查询越先执行，相同的id是从上往下执行，id为nul则最后执行。
2、select_type查询的类型
1）、SIMPLE:简单的查询，不使用union或子查询
2）、PRIMARY:子查询中最外层的查询
3）、UNION: union中第二个或后面的查询语句
4）、DERIVED：from子句的子查询，mysql会把子查询存放到一个临时表中，叫做派生表
5）UNION RESULT：查询所有union的合并结果
6）SUBQUERY：子查询中的第一个查询，结果不依赖于外部查询
7）DEPENDENT SUBQUERY：子查询中的第一个查询，依赖于外部查询。
3、Table
表示执行的sql访问的表名称，但有union时是<union2,3,4> ，有子查询时是
4、Type
常用的类型有：ALL、index、range、ref、eq_ref、const、system、NULL（从左到右性能从差到好）
ALL：扫描全表进行查询，访问的是聚集索引的所有叶子节点
Index：扫描全索引拿到结果，一般是扫描二级索引的的全部叶子节点，这种一般是覆盖索引
Range:范围扫描，通常出现在in、between、<、>=之中，使用一个索引来检索给定的行
Ref：不使用唯一索引，而是使用部分索引字段或不是唯一索引所的有字段或者唯一索引的前缀，可以查询到多行记录。
eq_ref:使用唯一索引进行连接查询，每个条件最多返回一行记录
Const、system：mysql对查询进行优化把查询结果转换为一个常量，如将主键作为唯一一个查询条件，mysql会把这个查询转变为常量。system是const类型的特列，但表中只有一行记录的情况下就是system类型。
NULL：mysql在优化过程中分解语句，在执行时甚至不用访问表和索引，例如此哦那个一个索引列里选取最小值，可以通过单独索引查找完成。
5、possible_keys
可能被使用的索引，但是不一定被用到，因为mysql根据自己的优化器可能不使用该索引。如果为null,则表示没有相关的索引。
6、Key
显示mysql实际采用的索引，如果没有使用索引，则该值是null，如果想强制使用或忽略possible_keys中的索引，可以在查询中使用force index、use index或者ignore index.
7、key_len
表示索引中使用的字节数，key_len计算规则如下：
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字
或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为
varchar是变长字符串
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型
date：3字节timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
8、ref:
列于索引的比较，表示表的连接匹配条件，即哪些列或者常量被用于查找索引列上的值，常见的有const、字段名
9、Rows
估算找到所需的记录所要读取的行数。
10、Extra
Using where :使用where语句来处理结果，并且查询的列未被索引覆盖；
Using index：使用覆盖索引，也就是要查询的字段都可以从被查询索引上获取，一般是针对二级索引。
Using index condition:查询的列没有被使用的索引完全覆盖，并且使用where条件中使用的是索引的范围查询。
Using temporary：mysql创建一个临时表来处理查询，比如distinct、group by、order by，这种情况要进行优化，一般用索引进行进行优化，把把操作的字段建立索引。
Using filesort：使用的是外部排序而不是索引排序，数据较小时在内存排序，否则在磁盘文件排序，这种情况下应该给排序的字段建立索引来优化，比如order by 排序的字段建索引。
Using join buffer：连表查询时，在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果，这种情况应该添加索引来优化。
Select tables optimized away:使用聚合函数min 、max来访问是索引的字段，但是前提是没有where条件，因为有查询条件聚合函数就不能直接找到其值了。

1、索引的底层实现原理和优化

推荐博客：一文弄懂索引数据结构 B+Tree

InnoDB建议为大部分表使用默认自增的主键作为主索引。

1.1 B树。

• 图例说明：键值key为记录的键值，可理解为表中的主键值，数据data为一行记录中除主键外的数据。

• B树特点：每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的主键key、除主键外的数据data、三个指向子树根节点的指针p1 p2 p3。指针存储的是子节点所在磁盘块的地址。

  以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

• 缺点：每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小、当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，影响查询效率。

1.2 B+树，

• 图例说明：假设每个磁盘块能存储4个键值及指针信息。键值key为记录的键值，可理解为表中的主键值，数据data为一行记录中除主键外的数据。

• B+树是B数的优化版，特点如下：
  （1）在同一层的叶子节点上，存放键值和数据，按照键值大小顺序存放所有数据记录节点
  （2）在所有的叶子结点中增加了指向下一个叶子节点的双向指针。 意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。说明支持范围查询和天然排序。
  （2）非叶子节点上只存储key值信息、磁盘指针。可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。

• B+树一般有多大深度

  InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为103。也就是说一个深度为3的B+Tree索引可以维护103 * 103 * 103 = 10亿条记录。

  ---

  实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

3、什么情况下设置了索引但无法使用

以“%”开头的LIKE语句，模糊匹配
OR语句前后没有同时使用索引
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）

1. 没有查询条件，或者查询条件没有建立索引
2. 在查询条件上没有使用引导列
3. 查询的数量是大表的大部分，应该是30％以上。
4. 索引本身失效
5. 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;
6. 对小表查询
7. 提示不使用索引
8. 统计数据不真实
9. CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。
10. 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn=‘13333333333’;
11. 1,<> 2,单独的>,<,(有时会用到，有时不会)
12. like “%_” 百分号在前.
13. 表没分析.
14. 单独引用复合索引里非第一位置的索引列.
15. 字符型字段为数字时在where条件里不添加引号.
16. 对索引列进行运算.需要建立函数索引.
17. not in ,not exist.
18. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
19. B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
20. 联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。

4、简单描述mysql中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

• 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

• 普通索引的唯一任务是加快对数据的访问速度。(普通索引：由关键字KEY或INDEX定义的索引)
  普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说， 唯一索引可以保证数据记录的唯一性。

• 主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY 来创建。

• 联合索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引 。

索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

5、 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

a)、索引的目的是什么？

1. 快速访问数据表中的特定信息，提高检索速度
2. 创建唯一性索引，保证数据库表中每一行数据的唯一性。
3. 加速表和表之间的连接
4. 显著减少查询中分组和排序子句的时间

b)、索引对数据库系统的负面影响是什么？

1. 创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；
2. 索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
3. 当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

c)、为数据表建立索引的原则有哪些？

1. 在最频繁使用的、用以缩小查询范围的字段上建立索引。
2. 在频繁使用的、需要排序的字段上建立索引

d)、 什么情况下不宜建立索引？

1. 对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引
2. 对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

6、你怎么看到为表格定义的所有索引?

索引是通过以下方式为表格定义的： SHOW INDEX FROM <tablename>;

7、实现索引的方式? 索引的原理? 索引的代价? 索引的类型?

实现索引的方式有两种：

1. 针对一张表的某些字段创建具体的索引,如对oracle: create index 索引名称 on 表名(字段名)；
2. 在创建表时为字段建立主键约束或者唯一约束，系统将自动为其建立索引。

索引的原理：
根据建立索引的字段建立索引表，存放字段值以及对应记录的物理地址，从而在搜索的时候根据字段值搜索索引表的到物理地址直接访问记录。

索引的代价：
引入索引虽然提高了查询速度,但本身占用一定的系统存储容量和系统处理时间,需要根据实际情况进行具体的分析.

索引的类型有：
B树索引，位图索引，函数索引等。

8、聚簇索引和普通索引在不同的SQL语句中哪个效率更高?（聚簇索引和普通索引的区别）

概念类：
聚合函数? Oracle的存储过程 ? 什么是构造函数?

9、怎样创建一个一个索引,索引使用的原则,有什么优点和缺点

创建标准索引： CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;
创建唯一索引: CREATE unique INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;
创建组合索引: CREATE INDEX 索引名 ON 表名 (列名1,列名2) TABLESPACE 表空间名;
创建反向键索引: CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

索引使用原则：
索引字段建议建立NOT NULL约束
经常与其他表进行连接的表，在连接字段上应该建立索引；
经常出现在Where子句中的字段且过滤性很强的，特别是大表的字段，应该建立索引；
可选择性高的关键字 ，应该建立索引；
可选择性低的关键字，但数据的值分布差异很大时，选择性数据比较少时仍然可以利用索引提高效率

复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
A、正确选择复合索引中的第一个字段，一般是选择性较好的且在where子句中常用的字段上；
B、复合索引的几个字段经常同时以AND方式出现在Where子句中可以建立复合索引；否则单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

频繁DML的表，不要建立太多的索引；

不要将那些频繁修改的列作为索引列；

索引的优点：

1. 创建唯一性索引，保证数据库表中每一行数据的唯一性

2. 大大加快数据的检索速度，这也是创建索引的最主要的原因

3. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

索引的缺点：

1. 索引创建在表上，不能创建在视图上

2. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

3. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

4. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

10、索引的实现方式

都是 B+树索引， Innodb 是索引组织表， myisam 是堆表， 索引组织表和堆表的区别要熟悉

11、Oracle索引分为哪几类，说出唯一索引和位图索引的概念。

Oracle索引有B树索引，位图索引，函数索引，簇索引等。

唯一索引也是B树索引的一种，它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。

位图索引并不是采用象唯一索引那样存储（索引字段值，记录ROWID）来创建索引段的，而是为每一个唯一的字段值创建一个位图，位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。

12、如何写 sql 能够有效的使用到复合索引。

由于复合索引的组合索引，类似多个木板拼接在一起，如果中间断了就无法用了，所以要能用到复合索引，首先开头(第一列)要用上，比如index(a,b) 这种，我们可以select table tname where a=XX 用到第一列索引 如果想用第二列 可以 and b=XX 或者and b like‘TTT%’

要注意！使用复合索引需要满足最左侧索引的原则，也就是查询的时候如果where条件里面没有最左边的一到多列，索引就不会起作用。

13、当数据表中A、B字段做了组合索引，那么单独使用A或单独使用B会有索引效果吗？（使用like查询如何有索引效果）

1. 看A、B两字段做组合索引的时候，谁在前面，谁在后面，如果A在前，那么单独使用A会有索引效果，单独使用B则没有，反之亦然。
2. 同理，使用like模糊查询时，如果只是使用前面%，那么有索引效果，如果使用双%号匹配，那么则无索引效果

14、数据库索引的实现(B+树介绍、和B树、R树区别)

http://blog.csdn.net/kennyrose/article/details/7532032

http://www.xuebuyuan.com/2216918.html

15、索引的使用注意事项

https://www.cnblogs.com/zlingh/p/3883716.html

16、MYsql 的索引原理，索引的类型有哪些，如何创建合理的索引，索引如何优化。

索引是通过复杂的算法，提高数据查询性能的手段。从磁盘io到内存io的转变

普通索引，主键，唯一，单列/多列索引建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

http://tech.meituan.com/mysql-index.html

http://www.cnblogs.com/cq-home/p/3482101.html

17、聚集索引和非聚集索引的区别。

• 聚集索引就是索引和记录紧密在一起。MySQL里主键就是聚集索引。 定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。单单从定义来看是不是显得有点抽象，打个比方，一个表就像是我们以前用的新华字典，聚集索引就像是拼音目录，而每个字存放的页码就是我们的数据物理地址，我们如果要查询一个“哇”字，我们只需要查询“哇”字对应在新华字典拼音目录对应的页码，就可以查询到对应的“哇”字所在的位置，而拼音目录对应的A-Z的字顺序，和新华字典实际存储的字的顺序A-Z也是一样的，如果我们中文新出了一个字，拼音开头第一个是B，那么他插入的时候也要按照拼音目录顺序插入到A字的后面。数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

非聚簇索引 索引文件和数据文件分开存放，索引文件的叶子页只保存了主键值，要定位记录还要去查找相应的数据块。

18、数据库中 BTREE 和 B+tree 区别。

B+是btree的变种，本质都是btree，btree+与B-Tree相比，B+Tree有以下不同点：

每个节点的指针上限为2d而不是2d+1。

内节点不存储data，只存储key；叶子节点不存储指针。

http://lcbk.net/9602.html

Btree 怎么分裂的，什么时候分裂，为什么是平衡的。

Key 超过1024才分裂B树为甚会分裂？ 因为随着数据的增多，一个结点的key满了，为了保持B树的特性，就会产生分裂，就向红黑树和AVL树为了保持树的性质需要进行旋转一样！