MYSQL索引与数据结构

MYSQL索引与数据结构

• 索引简介

索引是一种利用某种规则的数据结构与实际数据的关系加快数据查找的功能

• 索引的创建/删除/查看

--建表时创建
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);
--删除
DROP INDEX my_index ON tablename；
--或者
ALTER TABLE table_name DROP INDEX index_name;
--查看
SHOW INDEX FROM tablename ;
--查询索引情况
explain SELECT * FROM tablename WHERE column_1 = 'xxx';
--缺点
--1.索引占用空间
--2.创建时需要对表加锁，需要考虑业务时间。
--3.维护成本随着数据增大而增大
--4.降低修改操作的效率，修改时还要修改索引表

• • 索引类型
• • • 主键索引：根据主键建立，非空非重复。

ALTER TABLE 'tablename' ADD PRIMARY KEY py_index('col');

• • • 唯一索引：索引列唯一非空

ALTER TABLE 'tablename' ADD UNIPUE index_name('col');

• • • 普通索引：无限制

ALTER TABLE 'tablename' ADD INDEX index_name('col');

• • • 全文索引：用大文本对象的列构建的索引

ALTER TABLE 'tablename' ADD FULLTEXT ft_index('col');

• • • 组合索引：

ALTER TABLE 'tablename' ADD INDEX index_name('col','col1','col2');
--最左前缀原则：把最常用作检索或排序的列放在最左，依次递减，组合索引相当于建立
--了“col;colcol1;colcol1col2”三个索引，col1;col2时不能使用索引的。

• 无索引查找：

• 使用平衡二叉树结构索引的情况访问

• 磁盘IO与预读

• • 内存分：内存储器、外存储器，内存块、小、贵、断电丢失；外存即为磁盘读取，机械运动，每次分寻道时间、旋转延迟、传输时间三部分。考虑到磁盘IO很昂贵，计算机做了优化，每次IO会把目标地址相邻的数据也读取到内存缓冲区，局部预读性原理告诉我们，当计算机访问一个地址的数据时，相邻数据也会被访问到。

• 索引的数据结构：哈希表、二叉搜索树（BST）、自平衡二叉查找树（AVL）、红黑树、B数和B+树

• • 哈希表

哈希表是索引利器，直接把值通过哈希函数转换为固定长的key地址，通过地址进行查找。

但是无限的数据去映射固定长的哈希，会存在哈希碰撞。不同的值会计算出同一个结果。

解决碰撞的创建方法是链地址法，通过链表把碰撞的数据连接起来，如果存在碰撞，遍历链表。

哈希的算法复杂度为O（1），但是考虑到数据检索的常用操作是范围查找，mysql没有采用哈希做索引结构。使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找。

• • 二叉搜索树（BST）

数据结构如图

二叉的算法复杂度是O(logn)，二叉理想情况如图查找7个数据最多比较3次，也解决了哈希无法解决的范围查找问题

但是二叉有个缺点，极端情况下会退化为链表，如图

检索0007直接变为遍历复杂度达到了O(N)。二叉树的不平衡导致检索性能降低，也不适合做mysql底层索引。

• • 自平衡二叉查找树（AVL）和红黑树

二叉树通过自动旋转和调整，能让二叉树始终保持基本的平衡状态，这样就能保持二叉树的最佳性能，这类树有AVL和红黑树。

红黑树的事件复杂度为O（logn）比如从1到7升序插入数据，普通二叉会退化为链表，但是红黑树会不断调整状态，如图

红黑树有着不错的性能，但是当大量数据顺序插入时，红黑树会出现倾斜，并没有完全解决二叉树的问题

AVL则更为严格，在AVL树中任何节点的两个子树的高度最大差别为1，是高度平衡的二叉树，1-16顺序插入依然能够保持平衡

但是AVL也不适合Mysql的索引数据结构，这次是在IO上。

数据库查询数据的瓶颈在磁盘IO，如果使用AVL，我们每个树节点只存储了一个数据，一次磁盘IO只能取出一个节点的数据加载到内存，查几次就要进行磁盘IO几次，消耗大量时间。

磁盘IO的特点是：读取1B和1KB数据所消耗的事件是基本一样的，针对这个特性，节点上尽可能的多放数据就可大大提升效率。B和B+树就是这个设计原理。

• • B-tree：

B树的查找性能为O（h*log N）h为树高，N为每个节点关键词的个数，可提高I/O效率，加快检索速度。

B树的每个节点的元素可视为一次IO读取，树的高度表示最多的IO次数，

如图，每个节点限制最多存储两个key，key超过限制就会自动分裂

但是由于每次IO读取1B、1KB时间基本一样，可以优化为每个节点最多6个数据

相对AVL树，磁盘IO效率得到了大幅提升。

• • B+tree：

B树已经很优秀了，B+树与B树的区别在于B+树的节点存的是索引（地址），B树节点存储数据个数受数据大小限制，B+树的节点可以存储很多索引，B+树的叶子节点存所有的数据。B+树的叶子节点是数据阶段用了链表串联起来，便于范围查找

B+树在单节点存储有限的情况下存储地址，使树的高度大大降低，减少了磁盘IO；其次叶子节点是真正存储数据的地方，用链表连接，这个链表本身就是有序的，范围查找更有效率，所以Mysql选择了B+树作为索引结构。

B+对比B的优点：

1.磁盘读写代价低，

2.效率稳定，非终点节点存放叶子节点关键字的索引，所以任何关键字的查找必须走一条从根节点到叶子节点的路，所以关键字查询的路径长度相同，导致每个数据的查询效率相当。

3.便于范围查询，B提高了IO性能并没有解决元素遍历的效率低下的问题，B+只需遍历叶子节点就可实现整棵树的遍历，数据库范围查询是频繁的，B-tree的效率太低。

补充：B树的范围查找是中序遍历，而B+树是链表遍历；

B+劣势：查找结果不成功不如B，内存中B+没有优势，磁盘中有优势。

• • 索引最终选择B+树的原因:
• 1. 1. hash很快，但每次IO只能取一个数
     2. AVL和红黑树，在大量数据的情况下，IO操作还是太多
     3. B树每个节点内存储的是数据，因此每个节点存储的分支太少,B+磁盘读写代价低
     4. B+节点存储的是索引+指针(引用指向下一个节点)，可以存储大量索引，同时最终数据存储在叶子节点，并且有引用横向链接，可以在2-3次的IO操作内完成千万级别的表操作。
     5. 索引是是自增长数字，这样适合范围查找

• Mysql常见数据引擎

• • MyISM
• • • 查找：性能极佳
  • 存储：myisam在磁盘上存储上有三个文件.frm(存储表定义，创建表的语句) .myd（表数据） .myi（表索引文件）MyISAM的主索引和数据是分开的（非聚集索引方式），保存表具体行数；innodb不保存。
  • 锁：MyISAM使用的是表级锁不支持高并发，以读为主
  • 事务：MyISAM没有事务支持和MVCC
  • 全文索引：MyISAM支持FULLTEXT类型的全文索引
  • 主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址
  • 外键：不支持
• • InnoDB
• • • 存储：innodb磁盘上存储的是表空间数据文件和日志文件，innodb表大小只受限于操作系统大小，数据文件本身就是主索引文件。InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。生成的文件frm（表结构，创建语句）、idb（数据+索引，聚集索引方式）

• • • 锁：InnoDB支持行锁（共享锁、排他锁）粒度更小，但执行时不能确定扫描范围同样锁全表。
    • 事务：InnoDB支持ACID兼容的事务事务和MVCC
    • 全文索引：InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好
    • 主键：InnoDB如果没有设定主键或非空唯一索引，就会自动生成一个6字节的主键，数据是主索引的一部分，附加索引保存的是主索引的值
    • 外键：支持
• • 引擎对比：

	MyISAM	innoDB
索引类型	非聚簇	聚簇
支持事务	是	否
支持表锁	是	是
支持行锁	否	是（默认）
支持外键	否	是
支持全文索引	是	是（5.6以后支持）
适用操作类型	大量select下使用	大量insert、delete和update下使用

• • MyIsM索引实现
• • • 主键索引

MyISAM引擎使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。执行查询操作的时候会先搜索B+树，找到对应叶子结点，根据叶子节点的值（地址），拿出整行数据。下图为MyISAM表的主索引，Col1为主键。

• • • 辅助索引

在MyISAM中，主索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

• • InnoDB索引实现
• • • 主键索引

同样是B+树，实现方式却完全不同。InnoDB表数据文件本身就是一个索引结构，树的叶节点data域保存了完整的数据记录，这种索引叫做聚集索引。

---

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则mysql会自动选择一个可以唯一标识数据记录的列作为主键。如果不存在这种列，则mysql自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

• • • 辅助索引

InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引

因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以如果主键定义的比较大，其他索引也将很大。InnoDB 不会压缩索引。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

• 索引适用的场景

1. 1. 经常用于排序（order by ）、(group by )分组的列，因为索引已经排序过了。
   2. 有唯一值限制的列，比如主键、用户名。
   3. 较频繁的作为查询条件的字段应该创建索引；
   4. 唯一性太差的字段不适合单独创建索引，即使该字段频繁作为查询条件比如性别；
   5. 更新非常频繁的字段不适合创建索引。

• 总结

  数据库引擎和索引底层的数据结构各有各自的优缺点，具体要结合业务场景进行优化，比如同样的mysql优化不佳会时刻面临性能瓶颈，有的公司却可以支撑起网购秒杀抢购等场景。