MySQL索引

索引的实现原理

  索引是一个排序的列表，存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度。因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的缺点在于：索引需要更多的磁盘空间

索引为什么快

  索引以文件的形式存储在磁盘上，所以只使用更少的磁盘io 次数的数据结构更适合做索引。b 树和b+树是多叉树，树的度大，所以高度低。内存和磁盘交互的单位是页，将b 树和b+树的一个节点的大小设置为一个页，能保证一次io 就能读到一个页，同时磁盘采用预读策略，一次性读取相邻的几个页，读入内存后在进行二分查找。

MySQL 的基本存储结构是页

MySQL页和页之间、页和数据之间的关系：

• 数据页和数据页之间，组成一个双向链表
• 每个数据页中的记录，是一个单向链表
• 每个数据页都根据内部的记录生成一个页目录（Page directory），如果是主键的话，可以在页目录中使用二分法快速定位
• 如果我们根据一个非主键、非索引列进行查询，那么需要遍历双向链表，找到所在的页；再遍历页内的单向链表；如果表内数据很大的话，这样的查询就会很慢
  

索引如何定位到数据表中的记录

  一个表的数据块以链表的方式串联在一起，数据以行为单位一行一行的存放在磁盘的块中，扇区是对硬盘而言，块是对文件系统而言，块是文件存取的最小单位，一般一个块由连续的几个扇区组成。

以MySQL的B+树为例，简单说下几种常见场景下数据的定位过程：
第一种场景：索引精确查找
select * from user_info where id = 23 ;
确定定位条件, 找到根节点Page No, 根节点读到内存, 逐层向下查找, 读取叶子节点Page,通过 二分查找找到记录或未命中。

第二种场景：索引范围查找
select * from user_info where id >= 18 and id < 22 ;
读取根节点至内存, 确定索引定位条件id=18, 找到满足条件第一个叶节点, 顺序扫描所有结果, 直到终止条件满足id >=22。

第三种场景：全表扫描
select * from user_info where name = 'abc' ;
直接读取叶节点头结点， 顺序扫描， 返回符合条件记录， 到最终节点结束

第四种场景：二级索引查找
create table table_x(int id primary key, varchar(64) name , key sec_index(name) ) ;
select * from table_x where name = 'd' ;
通过二级索引查出对应主键，拿主键回表查主键索引得到数据， 二级索引可筛选掉大量无效记录，提高效率

何时使用索引

应该在这些列上创建索引：

• 在经常需要搜索的列上，可以加快搜索的速度
• 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
• 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
• 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
• 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

补充：为什么索引加速表连接​
  如果在关联字段添加索引，那么就可以直接用索引的数据，进行关联。索引中只包含了ID字段的值，所以占用的磁盘空间，相对于表来说，会小很多，所以扫描整个索引所需要的时间，远小于扫描全表的时间，本质上就是访问的数据页数少了，IO次数少了，所需要的时间就少了。除此之外如果有其他过滤条件。比如：select A.*,B.* FROM A JOIN B ON A.ID = B.ID WHERE A.ID = 1，那么整个执行过程就是，先用过滤条件 A.ID = 1 在A的索引种查找符合要求的数据，然后，这里sql server应该会做一个谓词的推导，直接在B表的索引中查找 1，可能是1条，最后关联一下数据。

索引的类别

主键索引、唯一索引、普通索引、全文索引、组合索引
1、INDEX（普通索引）
2、UNIQUE（唯一索引）：索引列的值必须唯一，允许有空值
3、PRIMARY KEY（主键索引）：是一种特殊的唯一索引，不允许有空值
4、FULLTEXT（全文索引）：主要用来查找文本中的关键字，仅可用于MyISAM和InoDB。在数据量较大时候，先将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多
5、组合索引：遵循“最左前缀”原则。创建复合索引应该将最常用做限制条件的列放在最左边，依次递减。最左前缀匹配规则：MySQL会一直向右匹配直到遇到范围查询(>,<,between,like)就停止匹配

聚簇索引与非聚簇索引
  MySQL中最常见的两种存储引擎分别是MyISAM和InnoDB，分别实现了非聚簇索引和聚簇索引。聚簇索引的顺序就是数据的物理存储顺序。非聚簇索引的索引顺序与数据物理排列顺序无关。

非聚簇索引（MyISAM）

• MyISAM存储引擎采用的是非聚簇索引，非聚簇索引的主索引和辅助索引几乎是一样的，只是主索引不允许重复，不允许空值，他们的叶子结点的key都存储指向键值对应的数据的物理地址
• 非聚簇索引的数据表和索引表是分开存储的

聚簇索引（InnoDB）

• 聚簇索引的主索引的叶子结点存储的是键值对应的数据本身，辅助索引的叶子结点存储的是键值对应的数据的主键键值
• 聚簇索引的数据和主键索引存储在一起
• 聚簇索引的数据是根据主键的顺序保存。因此适合按主键索引的区间查找，可以有更少的磁盘I/O，加快查询速度

对比

• 使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据
• 因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低成本，因为这时不用维护辅助索引。但是由于主索引存储的是数据本身，因此聚簇索引会占用更多的空间
• 聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要检测主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕

添加索引

1.添加PRIMARY KEY（主键索引） 
mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
2.添加UNIQUE(唯一索引) 
mysql>ALTER TABLE `table_name` ADD UNIQUE ( 
`column` 
) 
3.添加INDEX(普通索引) 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
4.添加FULLTEXT(全文索引) 
mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
5.添加多列索引 
mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

索引的数据结构

MySQL索引主要有两种结构：B+Tree索引和Hash索引
Hash索引
  MySQL中，只有Memory存储引擎显示支持Hash索引，是Memory表的默认索引类型。
  哈希索引适合于等值查询，只需要经过一次算法即可找到相应的键值，检索效率非常高。但是不支持范围查询检索，也没办法利用索引完成排序以及like ‘xxx%’ 这样的部分模糊查询。哈希索引也不支持多列联合索引的最左匹配规则。

B+Tree索引
  B+Tree是MySQL使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。B+Tree所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针。这样提高区间效率，只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点，极大提高了区间查询效率。大大减少磁盘I/O读取，数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点需要一次I/O就可以完全载入。
B树与B+树

索引使用策略及优化

  MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。本章的内容完全基于上文的理论基础，实际上一旦理解了索引背后的机制，那么选择高性能的策略就变成了纯粹的推理，并且可以理解这些策略背后的逻辑。

联合索引及最左前缀原理

联合索引（复合索引）
  首先介绍一下联合索引。联合索引其实很简单，相对于一般索引只有一个字段，联合索引可以为多个字段创建一个索引。它的原理也很简单，比如，我们在（a,b,c）字段上创建一个联合索引，则索引记录会首先按照A字段排序，然后再按照B字段排序然后再是C字段，因此，联合索引的特点就是：

• 第一个字段一定是有序的
• 当第一个字段值相等的时候，第二个字段又是有序的，比如下表中当A=2时所有B的值是有序排列的，依次类推，当同一个B值得所有C字段是有序排列的

| A | B | C |
| 1 | 2 | 3 |
| 1 | 4 | 2 |
| 1 | 1 | 4 |
| 2 | 3 | 5 |
| 2 | 4 | 4 |
| 2 | 4 | 6 |
| 2 | 5 | 5 |

其实联合索引的查找就跟查字典是一样的，先根据第一个字母查，然后再根据第二个字母查，或者只根据第一个字母查，但是不能跳过第一个字母从第二个字母开始查。这就是所谓的最左前缀原理。

最左前缀原理
  我们再来详细介绍一下联合索引的查询。还是上面例子，我们在（a,b,c）字段上建了一个联合索引，所以这个索引是先按a 再按b 再按c进行排列的，所以：

以下的查询方式都可以用到索引

select * from table where a=1；
select * from table where a=1 and b=2；
select * from table where a=1 and b=2 and c=3；

上面三个查询按照 （a ）, （a，b ）,（a，b，c ）的顺序都可以利用到索引，这就是最左前缀匹配。如果查询语句是：

select * from table where a=1 and c=3； 那么只会用到索引a。

如果查询语句是：

select * from table where b=2 and c=3； 因为没有用到最左前缀a，所以这个查询是用户到索引的。

如果用到了最左前缀，但是顺序颠倒会用到索引码？比如：

select * from table where b=2 and a=1；
select * from table where b=2 and a=1 and c=3；

  如果用到了最左前缀而只是颠倒了顺序，也是可以用到索引的，因为mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。但我们还是最好按照索引顺序来查询，这样查询优化器就不用重新编译了。

前缀索引
  除了联合索引之外，对mysql来说其实还有一种前缀索引。前缀索引就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。一般来说以下情况可以使用前缀索引：

• 字符串列(varchar,char,text等)，需要进行全字段匹配或者前匹配。也就是=‘xxx’ 或者 like ‘xxx%’
• 字符串本身可能比较长，而且前几个字符就开始不相同。比如我们对中国人的姓名使用前缀索引就没啥意义，因为中国人名字都很短，另外对收件地址使用前缀索引也不是很实用，因为一方面收件地址一般都是以XX省开头，也就是说前几个字符都是差不多的，而且收件地址进行检索一般都是like ’%xxx%’，不会用到前匹配。相反对外国人的姓名可以使用前缀索引，因为其字符较长，而且前几个字符的选择性比较高。同样电子邮件也是一个可以使用前缀索引的字段
• 前一半字符的索引选择性就已经接近于全字段的索引选择性。如果整个字段的长度为20，索引选择性为0.9，而我们对前10个字符建立前缀索引其选择性也只有0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，没有太大的建前缀索引的必要了。

一些文章中也提到：
  MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

索引优化策略

• 最左前缀匹配原则，上面讲到了
• 主键外检一定要建索引
• 对 where,on,group by,order by 中出现的列使用索引
• 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(* )，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
• 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键
• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
• 为较长的字符串使用前缀索引
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
• 不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
• 对于like查询，”%”不要放在前面。

SELECT * FROMhoudunwangWHEREuname LIKE'后盾%' -- 走索引
SELECT * FROMhoudunwangWHEREuname LIKE "%后盾%" -- 不走索引

• 查询where条件数据类型不匹配也无法使用索引
  字符串与数字比较不使用索引;

CREATE TABLEa(achar(10));
EXPLAIN SELECT * FROMaWHEREa="1" – 走索引
EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引

正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

索引失效

1. 组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引
2. like未使用最左前缀，where A like ‘%China’，而where A like 'China%'会使用到索引
3. 搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引
4. or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
5. 如果列类型是字符串，要使用引号。例如where A=‘China’，否则索引失效（会进行类型转换）
6. 在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等
7. 如果MySQL认为全表扫面要比使用索引快，则不使用索引
8. 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可

数据库在磁盘中如何存储？

以Innodb引擎为准，按照表空间、段、簇、页进行存储。

1. 当新建一个表，就会在磁盘上新建一个表空间，用于存储数据。一个表空间中包含多个段，包括叶子节点段（数据段），非叶子节点段（索引段），回滚段（保证数据完整性）。在Innodb引擎中，数据以索引组织，即聚集索引，新建一个索引，在表空间中会同时建立数据段和索引段。
2. 一个段又包括多个簇。簇是构成段的基本元素，一个段由若干个簇构成。一个簇是物理上连续分配的一个段空间，每一个段至少会有一个簇，在创建一个段时会创建一个默认的簇。如果存储数据时，一个簇已经不足以放下更多的数据，此时需要从这个段中分配一个新的簇来存放新的数据。一个段所管理的空间大小是无限的，可以一直扩展下去，但是扩展的最小单位就是簇。
3. 一个簇由64个连续的页组成。每个页大小为16KB，即每个簇的大小为1MB。页可以理解为簇的细化，在逻辑（页面号连续递增）和物理上页的存储都是连续的，在向表中插入数据时，如果一个页面已经被写完，系统会从当前簇中分配一个新的空闲页面处理使用，如果当前簇中的64个页面都被分配完，系统会从当前页面所在段中分配一个新的簇，然后再从这个簇中分配一个新的页面来使用。