数据库索引

以下索引知识以mysql索引来阐述

一：MySQL中索引的语法

　　（1）创建索引

--在创建表的时候添加索引
CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
); 

--在创建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
--或者
CREATE INDEX index_name ON my_table(column_name);

注意：

1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够

2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

　　（2）索引相关sql

--删除索引
DROP INDEX my_index ON tablename；
--或者
ALTER TABLE table_name DROP INDEX index_name;

--查看表中的索引
SHOW INDEX FROM tablename

--查看查询语句使用索引的情况
//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';

二：索引的优缺点

　　优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

　　劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

三：索引的分类

　　常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

--1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

--2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值
ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

--3、普通索引：用表中的普通列构建的索引，没有任何限制
ALTER TABLE 'table_name' ADD INDEX index_name('col')；

--4、全文索引：用大文本对象的列构建的索引
ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

--5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

 ** 组合索引 （最左前缀原则）**

　　*遵循“最左前缀”原则，把最常用作为检索或排序的列放在最左，依次递减，组合索引相当于建立了col1,col1col2,col1col2col3三个索引，而col2或者col3是不能使用索引的。

　　*在使用组合索引的时候可能因为列名长度过长而导致索引的key太大，导致效率降低，在允许的情况下，可以只取col1和col2的前几个字符作为索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

　　例子：

　　（1）索引(a,b) 查询条件b是无法使用联合索引的 查询条件a可以使用索引
　　 （2）%%开头是不会使用索引的

**几种树的数据结构图

　　

三：索引的底层数据结构 （B+Tree索引 )

B+Tree索引 :  B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于;

• B+Tree中的非叶子结点不存储数据，只存储键值；
• B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
• B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

　　　　B+Tree的结构如下：

　　　　

B+Tree对比BTree的优点：

1、磁盘读写代价更低

　　一般来说B+Tree比BTree更适合实现外存的索引结构，因为存储引擎的设计专家巧妙的利用了外存（磁盘）的存储结构，即磁盘的最小存储单位是扇区（sector），而操作系统的块（block）通常是整数倍的sector，操作系统以页（page）为单位管理内存，一页（page）通常默认为4K，数据库的页通常设置为操作系统页的整数倍，因此索引结构的节点被设计为一个页的大小，然后利用外存的“预读取”原则，每次读取的时候，把整个节点的数据读取到内存中，然后在内存中查找，已知内存的读取速度是外存读取I/O速度的几百倍，那么提升查找速度的关键就在于尽可能少的磁盘I/O，那么可以知道，每个节点中的key个数越多，那么树的高度越小，需要I/O的次数越少，因此一般来说B+Tree比BTree更快，因为B+Tree的非叶节点中不存储data，就可以存储更多的key。

2、查询速度更稳定

　　由于B+Tree非叶子节点不存储数据（data），因此所有的数据都要查询至叶子节点，而叶子节点的高度都是相同的，因此所有数据的查询速度都是一样的。

带顺序索引的B+TREE

　　很多存储引擎在B+Tree的基础上进行了优化，添加了指向相邻叶节点的指针，形成了带有顺序访问指针的B+Tree，这样做是为了提高区间查找的效率，只要找到第一个值那么就可以顺序的查找后面的值。

　　顺序B+Tree的结构如下：

三：聚簇索引和非聚簇索引　

MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。

　　聚簇索引的解释是:聚簇索引的顺序就是数据的物理存储顺序

　　非聚簇索引的解释是:索引顺序与数据物理排列顺序无关

为了更形象说明这两种索引的区别，我们假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

　　InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

　　MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。　

我们重点关注聚簇索引，看上去聚簇索引的效率明显要低于非聚簇索引，因为每次使用辅助索引检索都要经过两次B+树查找，这不是多此一举吗？聚簇索引的优势在哪？

　　1 由于行数据和叶子节点存储在一起，这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

　　2 辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。

 B+树所有的关键字都出现在叶子节点的链表（稠密索引及聚簇索引）中，且链表中的关键字是有序的。非叶子节点只起索引作用（稀疏索引及非聚簇索引）。
　　如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+树。
　　如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，ID 的值为 500，再到 ID 索引树搜索一次。这个过程称回表。

四：联合索引 

 　　

这是一张表格，col1 是主建，col2和col3 是普通字段。那么主索引 对应的 B+树 结构是这样子的：

 

也可以是这样子的：

 

现在呢，对col3 建立一个单列索引，原文图：

 

看完这个图也是可以理解的，那么想法来了，如果对 col3 和 col2 建立 联合索引，那么 B+ 树会是一个什么样子的呢？

首先可以肯定的是，肯定只有一棵树，又因为 最左原则的存在，那么带着这个想法自己试着画了下：

 

建索引语句 CREATE INDEX IDX_XXX ON TABLE(COL3, COL2);

先根据col3 排序，在根据 col2 排序，如上图。

原文例子中的数据没有重复数据，为了更好的理解，我自己改了下：

红色框是改动的地方，把col3 改成有重复数据了，然后 还是对 col3 ，col2建立联合索引，那么 B+树 如下：

红色框是和原来不一样的地方。

联合索引在查找的时候，比如要找 Alice，34 这条记录 WHERE COL3 = 'Alice' AND COL2 = 34

先根据col3 查找 Alice ，找到了2条记录，在根据col2 查找 34，然后获取到主键 15 ，在根据主键去查找 主索引。

如果 是 WHERE COL2 = 34，由于只有联合索引 (col3, col2)，没有col2 的单列索引。

那么查找的时候，就没法根据上面的这棵树来查找 ，只能全表扫描。

所以为什么会有最左原则，就是因为 B+树 是根据最左边的字段构建的，我的想法是这样子的。

五：索引的使用策略

 　（1）什么时候要使用索引？

　　1：主键自动建立唯一索引；
　　2：经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
　　3：作为排序的列要建立索引；
　　4：查询中与其他表关联的字段，外键关系建立索引
　　5：高并发条件下倾向组合索引；
　　6：用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

　　（2）什么时候不要使用索引？

　　1：经常增删改的列不要建立索引；
　　2：有大量重复的列不建立索引；
　　3：表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

　　索引失效的情况：

　　1：在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
　　2：在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
　　3：LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
　　4：在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
　　5：在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。
　　6：在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
　　7：字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email='99999'。
　　8：在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
　　9：如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
　　10：尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

六：索引的优化　

　　1、最左前缀

　　索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

　　根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

　　2、带索引的模糊查询优化

　　在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

　　3、为检索的条件构建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

　　4、使用短索引

　　对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。