mysql学习篇-索引

什么是索引

索引就像书里面的目录，可有快速帮我们定位我们想要的数据在什么位置。
换到数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

索引类型

• 按[数据结构] 分类：B+tree索引、Hash索引、Full-text索引。
• 按[物理存储] 分类：聚簇索引（主键索引），二级索引（辅组索引）
• 按[字段特性] 分类：主键索引，唯一索引，普通索引，前缀索引。
• 按[字段个数] 分类：单列索引，联合索引。

按数据结构分类

innodb在mysql5.5之后成为了mysql默认的存储索引，B+tree索引类型也是mysql存储索引采取最多的索引类型。
在创建表时，innodb存储引擎根据不通的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键；
• 如果上面两个都不存在的情况下，innodb将自动生成一个隐式自增id列作为聚簇索引的索引键；

什么是B+Tree

1. B+tree是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，每个节点里面存放的是主键。
2. 每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息。
3. 每个叶子节点都有两个指针，分别指向上一个节点和下一个节点，形成了双向链表。

如何通过b+tree查询数据呢？

1. 先在根节点中寻找，当前的id是在哪个范围里。
2. 在根节点中找到了范围，进入第二层，在第二层里面寻找当前id是在第二层节点的哪个范围。
3. 在第二次找到范围后，进入第三层叶子节点，通过叶子节点的id比较找到对应的数据。

二级索引

1. 我们以某个字段创建二级索引，那么会通过该字段创建出一个b+tree，节点里面存放的是以该字段床架的索引（橙色部分）
2. 子叶子节点里面存放的是索引字段和主键值。

主键索引的B+ tree和二级索引的B+Tree的区别：

3. 主键索引的b+tree的叶子节点存放的是实际完整的数据。
4. 二级索引的b+tree的叶子节点存放的是数据的主键，而不是所有的数据。
5. 我们通过二级索引取查询数据的时候分两种情况，如果我们只获取主键这个字段，那么通过二级索引查询数据的时候是直接可以获取到id的，如果我们想获取到完整的数据，通过二级索引查询数据，我们获取到key以后，还是需要通过回表查询来获取到全部的数据。（也就是说我们需要查询两个b+tree）
   

b+tree和数据结构的区别

b+ tree

• b + tree 只在叶子节点存储数据，所有的叶子节点都通过指针相连，这个对范围查询非常有利，因为可以通过指针从一个叶子节点移动到下一个叶子节点。（有序的双向链表），所有的叶子节点在同一层
• b + tree 非叶子节点有多少个子节点，非叶子节点不存储数据，比高度相同的b-tree有更多的分支，减少了给定键的io操作。
• b + tree由于数据都在叶子节点上，而且叶子节点通过链表相连，所以数据的物理存储是稳定的，不会因为插入和删除操作而改变。
• b + tree广泛的用于数据库和磁盘读写，因为查询写入的时候效率高。
• b + tree 比b - tree 更矮更胖。

在innoDb中一页默认存储16k的数据，一个bigint是8字节，一个指针是6字节，所以一个非叶子节点能存储的单元数 （16k*1024 / 14 byte=1170个）假设一个数据大小为1k，那么底层叶子节点一页16k就能存16条记录，那么2层高度的b+tree 能记录的数据数约16 * 1170 = 18720， 那么3层高度的b+tree 能记录的数据数约 16 * 1170 * 1170=21902400 ，2190w条记录，那么得出3层高度的b+tree 可以存储 2000w条数据。

b- tree

• 自平衡：在删除和插入节点的时候，会通过旋转和重新平衡操作来保持树的平衡。
• 多节点：b-tree 不同于二叉树，每个节点可以有多于两个的子节点。
• 所有的叶子节点都在同一层，有助于高效的磁盘读写。
• 节点包含多个键和指针，键用于搜索和排序，指针指向存储的数据
• 高度较低，由于b树的每个节点可以有多个子节点，因此b树的高度较低，这有助于在磁盘上查找数据。
• 查询速度是O(logn)

二叉树

• 每个节点最多有两个子节点。
• 子节点有左右之分，key 小于根节点那么他是左子节点，key大于根那么他是右子节点。
• 二叉树的子树也是二叉树
• 当每次插入的元素都是最大元素的时候，二叉树会退化成一个链表查询速度从O(logN)退化成O(n)。

hash

• hash查询速度快，是通过hash函数将元素映射到一个大的空间中，使得我们可以快速定位到元素的位置，查询速度是o（1）。
• hash表的性能在很大程度上取决与hash函数的质量和解决hash冲突的方法。
• hash表不维护元素的有序性，因此不能做范围查询。
• hash表更适合做内存中的数据存储，因为可以提供快速的访问。

防止hash冲突的方法：

• 开放寻址：发现有hash冲突的时候，寻找下一个空的hash地址
• 链地址法：每个hash桶都维护一个链表，所有的hash值相同的元素都存储在这个链表中，当发生冲突的时候，只需要在链表尾部添加新的元素。

索引类型

• 主键索引：主键索引的b+tree的叶子节点里面存放的是实际数据

create table table_name (
	PRIMARY KEY(id) USING BTREE
)
create primary key 索引名称 on table_name(key)
ALTER TABLE table_name 
ADD PRIMARY KEY (ID);

• 二级索引: 二级索引的b+tree 里面存放的是主键（id），二级索引在查询的时候如果只获取主键，这不需要回表，如果要获取主键之外的数据则需要回表查询。

CREATE TABLE table_name (
    ID INT NOT NULL,
    Name VARCHAR(100),
    PRIMARY KEY (ID),
    INDEX (Name)
);
create index 索引名称 on table_name(key);
ALTER TABLE table_name ADD INDEX (Name);

• 唯一索引：一张表可以在多个字段上创建unique 索引，但是索引的列必须的唯一的，允许为空

create table table_name (
	UNIQUE (name,name1...)
)
create unique index 索引名称 on table_name(key);
alert table table_name ADD UNIQUE(name);

• 普通索引：在普通字段上创建的索引，既没有主键索引一样需要设置主键，也没有唯一索引需要列数据唯一

CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);
create index 索引名称 on table_name(name1, name2)
alert table table_name add INDEX(name1)

• 前缀索引：前缀索引是指对字段的前几个字符创建索引，而不是整个字段，前缀索引可以建立的字段类型有，char，varchar，binary，varbinary。

CREATE TABLE table_name(
    ID INT NOT NULL,
    Name VARCHAR(255),
    PRIMARY KEY (ID),
    INDEX (Name(10))
);
create index 索引名称 on table_name(Name(length));
ALTER TABLE table_name ADD INDEX (Name(10));

• 联合索引： 通过将多个字段组合成一个索引，我们称之为联合索引。联合索引的非叶子节点用两个字段作为索引，叶子节点里面是索引和主键，叶子节点和叶子节点之间使用指针相连。

create index index_product_on_name ON product(product_on, name);

使用联合索引的时候要遵循最左匹配原则，也就是按照最左边优先的方式进行索引匹配，如果最左边的不在索引字段里面，那么联合索引就会失效。

我们这里举联合索引的例子（a，b）a索引是（1，2，3，4，5，6，7，8，9），b索引是（12，7，8，2，3，8，10，5，2），如果我们跨过a索引去使用b索引查询，因为b索引是无序的所以联合索引失效了。

联合索引范围查询

select a,b from table_name where a > 1 and b = 2;

因为联合索引是先按照a字段进行排序的，所以a > 1的查询条件的索引记录是肯定相邻，所以进行了索引扫描的时候定位到了符合a>1的第一条记录，但是在a>1条件的二级索引记录的范围里，b = 2的字段是无序的，因此我们不能根据条件查询b = 2来减少扫描数据的数量，所以得出在上述sql 中a >1用到了联合索引，而b字段没有使用联合索引。


如果数据库里面有多余字段，那么使用*查询回导致联合索引失效。

select * from t_table where a >= 1 and b = 2

因为联合索引是先按照a字段的值排序的，所以a >= 1的查询条件的索引记录是肯定相邻，所以进行了索引扫描的时候定位到了符合a>=1的第一条记录，虽然在a >=1添加查询的范围里，b字段的值是无序的，但是对于符合a = 1的二级索引的记录里，b字段的值是有序的，所以对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序。所以以上这条查询语句a和b字段都使用到了联合索引。

SELECT a,b from t1 where a BETWEEN 2 and 6 and b = 2;

因为在BETWEEN 查询里面，在mysql中相当于 >= and =<,同理上述sql a>= 2 AND a <= 6 的条件查询的索引记录是相邻的，所以在进行索引扫描的时候定位到了a>=2的第一条数据，虽然在 a>=2 AND a <= 6 的查询范围里，b字段是无序的，但是对于符合 a = 2 and a =6的时候b字段是有序的，所以上面的sql用到了a和b的联合索引

SELECT * FROM t_user WHERE name like 'j%' and age = 22

因为联合索引是先按照name字段的值排序的，所以前缀为j的name字段的二级索引记录都是相邻的，于是在进行索引扫描的时候，可以定位到符合前缀为j的name的第一条记录，在前缀为j的索引记录里面 age字段是有序的，那么可以判断在上述sql里面name和age的联合索引是有效的。

SELECT * FROM t_user WHERE name like '%j' and age = 22

因为联合索引是先按照name字段的值排序的，但是因为通配符在左边所以匹配的是j结尾的，所以在二级索引记录里面是不相邻的，又因为联合索引需要遵循最左匹配，所以name和age没有命中联合索引

总结
从上面的sql中我们得出联合索引在遇到 > 和 < 做范围查询的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询后的字段是无法使用联合索引的，但是对于 >= 和 <=，BETWEEN，like，前缀匹配的查询，并不会停止匹配，匹配后的字段因为在联合索引里是有序的，所以可以使用联合索引。

索引如何优化

• 前缀索引

1. 使用前缀索引是为了减小索引字段的大小，可以增加一个索引页中存储的索引值，有效的提高索引的查询速度。
2. 前缀索引无法使用order by
3. 无法把前缀索引用作覆盖索引

• 覆盖索引:

1. 覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

• 主键索引

1. 主键索引最好是自增，因为每次插入新的数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。如果我们不使用自增作为主键，那么每次插入的时候就可能回插入到现有数据也中间的某个位置，甚至需要从一个页面复制数据到另一个页面，我们通常将这种情况称为页分裂，页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

• 索引最好不要设置为空

1. 索引列在存在null就会导致优化器在做索引选择的时候更加复杂，因为null的列回使索引，索引统计和值比较都变的更加复杂。
2. NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。

如何防止索引失效

1. 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
2. 当我们在查询条件中对索引列做了计算，函数，类型转换的时候，就会导致索引失效
3. 联合索引要能正确使用最左原则，也就是按照最左优先的方式进行索引匹配，负责就会失效。
4. 在wehere 子句中，如果在or 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。