Mysql索引

一个表最多建16个索引列

Mysql索引是为了让数据库高效获取数据的数据结构，相当于书本的目录。
mysun存储引擎，索引文件和数据文件单独存储，innodb存储引擎，会把数据和索引存到一个文件。mysql优先把索引加载到内存中。

覆盖索引：
针对于辅助索引，一般使用辅助索引拿到主键，再根主键进行回表查询聚簇索引拿到完整的行数据
如果不需要进行回表查询，就是覆盖索引。
例如class_id字段为辅助索引。
select class_id from table where class_id=xx
或者(class_id ,user_id)为联合索引
select class_id ,user_id from table xx where class_id=xxx
就是查询的数据本身就是辅助索引列，不需要回表，就是辅助索引查询。

索引回退

hash索引的缺点：
不支持范围查询，不支持排序操作
hash冲突

B+树缺点：
B+树最大的性能问题是会产生大量的随机IO，随着新数据的插入，叶子节点会慢慢分裂，逻辑上连续的叶子节点在物理上往往不连续，甚至分离的很远，但做范围查询时，会产生大量读随机IO。对于大量的随机写也一样，举一个插入key跨度很大的例子，如7->1000->3->2000 … 新插入的数据存储在磁盘上相隔很远，会产生大量的随机写IO.

作者：sunTengSt
链接：https://www.jianshu.com/p/06f9f7f41fdb
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

索引的数据结构有以下几种
1、Hash表

• 利用Hash来存放数据，数据就是存放在内存里了，直接根据索引读取数据，内存空间有限。
• hash本身只能等值查询，根据一个索引找到一个数据，无法进行范围查询。
• 数据散列不均匀，浪费空间
• hash冲突拉链法越拉越长会降低性能。

2、二叉树搜索树
二叉搜索树和红黑树都会因为树的高度越来越高而导致效率变慢。
3、B树，B+的非叶子结点存储数据和子节点的指针，叶子结点只存储数据。
4、B+树（mysql使用B+树作为索引），B+树的非叶子结点都只是指针，指向子节点，只有叶子节点才会存储数据。同一级的兄弟节点（page）使用链表的形式存储，每个节点都是一个page，一个page占16KB，如果这个page是非叶子结点，其可以存1000个子节点的地址，如果这个page是叶子结点，其可以存大约200条行数据。B+树比B树更高效就是因为B+树的非叶子结点不存储数据，非叶子结点就可以保存更多的指针，即非叶子结点可以有更多的子节点，B+树就更加的矮胖，IO次数就越少。

使用索引查找过程：
查找主键key为1的数据

• 首先从磁盘加载根节点
• 根据key为1找到子节点的位置
• 访问磁盘，io，加载子节点
• 访问磁盘，io，加载子节点
• 直到加载到叶子结点，在这个page里遍历数据，找到主键为1的行数据。

可以看到如果树越矮胖，加载非叶子结点的次数越少。

常用的存储引擎有innodb,mysun.
操作系统将磁盘和内存划分为页，一页4kb，磁盘和内存交换数据是以页为单位交换数据的，每次访问磁盘，必须是页的整数倍。innodb存储引擎每次读取16kb
磁盘读写的两种方式：

• 顺序读写
• 随机读写

磁盘读写有一个最少内容的限制，即使我们只需要这个簇上的一个字节的内容，我们也要含着泪把一整个簇上的内容读完。而红黑树只有左右两个子节点，不能完全填满一个簇，B+树可以有多个节点，读取的内容不会白读，可以都放入B+树。
数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的

聚簇索引
非聚簇索引（辅助索引）

索引里面存什么？

• key，行数据的某一列的值，一般使用自增主键，多表也不重复。
• （指针）表地址，这条数据所在表的地址。
• 指针，偏移量，这行数据，在这张表中的偏移量。
  索引是K-V结构，K里面放行数据的唯一标识，V里面放表地址和偏移量。但是这样索引文件会非常大，加载索引文件会很慢，而且很费内存。所以数据库直接这样存索引不合适。数据仓库可以使用这种方式。

mysql不需要把所有的索引加载到内存，只需要加载根节点，索引的val也不保存表地址和偏移量，val直接保存完整的行数据。索引文件和数据文件一起保存。

联合索引：
create table student(
id int,
class_id int,
name varchar(64),
primary key(id),
key(id,class_id)
)
key(id ,class_id)就是以id和class_id创建联合索引。
联合索引创建的时候谁在前，就先按照谁排序
key(a,b)
先按照a排序，在a一样的情况下再按照b列排序。
B+联合索引树非叶子结点存储的键为(a的值，b的值)。
所以B+树里面a是全局有序的。b只是局部有序，在a一样的时候,b是有序的。
w’here a=xx and b=xx 索引有效
where a=xx 索引有效
where b=xx 索引无效，因为b不是全局有序的。

索引失效：
1、查询条件带有or。除非所有查询都有索引，否则索引失效
例如 id带索引，name不带索引
select * from table xx where id =xxx or name=‘xxx’

2、联合索引失效
3、对索引列使用模糊查询
比如name列创建了索引
select * from table where name =‘%xxx’
4、如果对于字符串类型的列创建索引，查询必须使用单引号括起来，否则不走索引
name创建了索引
select * from table where name=‘xx’ 索引查询
select * from where name =xxx 索引失效

5、索引列参与计算，索引失效
select * from table where id-1=0;索引失效

6、in范围查询，如果范围过大大于全表数据的30%，不会走索引，会全表扫描