Mysql索引详解

1.什么是MySQL索引？

官方介绍索引是帮助MySQL高效获取数据的数据结构,通俗一点就是索引就类似于书本的目录，能快速查找到我们想要的数据.其本质上可以看成一种排序好的数据结构。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

1.1索引的原理

索引的存储原理大致为：空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕，有索引的情况下直接在目录查找索引位置，以此快速定位查询数据

1.2索引的优缺点

优点

：大大提高查询速度
：降低数据库读写
：被索引的列会自动排序

缺点

：占用物理空间，空间换时间
：查速度快，但是增删改会变慢，因为需要更新对应的索引文件，降低了数据的维护速度
：维护索引需要消耗数据库资源，耗费时间
：不正确使用或者数据库数据量不大，索引不会带来多大的性能提升

1.1索引的分类

1 、 普通索引：MySQL基本索引，没有什么限制，仅为了查询数据更快

2、 唯一索引：索引列的值必须是唯一的，且允许空值

3、 主键索引：是一种特殊的唯一索引，建表的时候创建主键自动创建的索引，不允许为空

4、 组合索引：数据库表中多个字段组合创建的索引，使用组合索引时遵循最左前缀原则，字段索引的顺序先按照左边第一个字段排序，再按照第二个字段，只有在查询中使用了这些字段的最左边字段时，索引才会被使用

5、 全文索引：Full Text,在MySQL5.7之前，只有在myisam引擎上才能使用。支持值得全文查找，可以在char，varchar上创建，用于搜索很长的一篇文章的时候，效果最好。

6、 空间索引：几乎不用

7、 前缀索引： 也称局部索引，简单来说就是对于文本前面几个字符建立索引，在可以降低重复的索引值时可以使用，不过不能再排序和分组里面触发前缀索引

1.2索引的使用

创建

: :普通索引

CREATE TABLE user(
    id INT NOT NULL,
    name varchar(30) NOT NULL,
    index(id)
);

: : :唯一索引

CREATE TABLE user1(
    id INT NOT NULL,
    name varchar(30) NOT NULL,
    UNIQUE INDEX uk_idx_id(id)
);

: : : 主键索引：创建表之后，对应的主键自动创建主键索引

`CREATE TABLE book(
   book_id(10) INT UNSIGNED AUTO_INCREMENT ,
   book_name VARCHAR(100),
   authors VARCHAR(100),
   info VARCHAR(100) ,
   comment VARCHAR(100),
   year_publication YEAR,
   PRIMARY KEY(book_id)
);

`

: :单列索引

CREATE TABLE user(
    id INT NOT NULL,
    name CHAR(50) NULL,
    INDEX single_idx_name(name)
);

: : :组合索引

CREATE TABLE user(
    id INT(11) NOT NULL,
    name CHAR(30) NOT NULL,
    age INT(11) NOT NULL,
    info VARCHAR(255),
    INDEX multi_idx(id,name,age)
);

: : :全文索引，如下为关键sql

FULLTEXT INDEX futxt_idx_info(字段)`

: : :空间索引，如下为关键sql

SPATIAL INDEX spa_idx_geo(字段)

修改

: :表已经创建好的时候，对索引的操作

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

删除

: :使用ALTER TABLE的方式删除索引

ALTER TABLE table_name DROP INDEX index_name;

: : :使用DROP INDEX的方式删除索引

DROP INDEX index_name ON table_name;

2.MySQL索引的数据结构

2.1 Hash表

哈希索引就是通过Hash表实现的，只有精确匹配索引所有列的查询才会有效，对于每一行数据，存储引擎都会对所有的索引列技计算一个哈希码，即HashCode，并且把所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。但是这样就会有一些限制，如下：
1，只能是等值查询，时间复杂度为O(1)，不支持范围快速查找，范围查找的时候还是只有全表扫描
2，哈希冲突，当出现冲突的时候，维护成本高
3, 无法排序

2.2 二叉树找树

二叉树每个节点最多有俩个分叉，左子树和右子树数据顺序左小右大
二叉树的实现主要是为了每次查找都可以折半减少IO次数，但还有一种情况是出现了树不分叉，退化成链表了，争对这个问题，有平衡二叉树的出现，

平衡二叉树也是AVL 树除了具备二叉树的特点之外，最主要的特征是左右俩个子树的高度差的绝对值不超过1，并且左右俩个子树都是一颗平衡二叉树。在插入删除数据时通过左旋/右旋的操作保持二叉树的平衡，不会出现左子树高右子树矮的情况，对应的缺点如下:
1，不支持范围查询，范围查询需要从根节点多次遍历，效率不高
2，表数据量大的时候，查询性能就差

2.4 B树

为了减少磁盘读取操作，一次性读取更多的数据，B树产生了，B树是一种多叉平衡查找树，主要特点是：
1，叶子节点都在同一层，叶子节点没有指针链接
2，B树的节点中存储着多个元素，每个内节点有多个分叉
3，节点中的元素包含键值和数据，节点中的键值从大到小排列

2.5 B+树

B树的升级版，俩者的不同点：
1，数据都放在叶子节点中，
2，非叶子节点值存储键值信息，不存数据
3，所有叶子节点之间都有一个指针，指向下一个叶子节点，而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表