MySql索引

1、什么是索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

2、索引类型

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引等！

2.1 创建索引

主键索引：根据主键建立的索引，不允许重复，不允许空值

ALTER TABLE '表名' ADD PRIMARY KEY 索引名('列名');
ALTER TABLE 'student' ADD PRIMARY KEY name_index('name');

唯一索引： 用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'student' ADD UNIQUE index_name('name');

普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'student' ADD INDEX index_name('name');
或者
//CREATE INDEX 索引名 on 表（'字段名'）
CREATE INDEX id_student_name on student(name)

全文索引：用大文本对象的列构建的索引,快速定位数据

ALTER TABLE 'student' ADD FULLTEXT INDEX st_index('name');
--EXPLAIN 分析sql执行的情况
EXPLAIN SELECT * FROM STUDENT  非全文索引
SELECT * FROM student WHERE MATH(name) AGAINST('刘')//全文索引

组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'student' ADD INDEX index_name('name','sno','age');

查看索引

SHOW INDEX FROM 'student'

删除索引

DROP INDEX index_name ON 'table_name'

3、索引创建的条件

创建索引
  主键自动建立唯一索引
  频繁作为查询条件的字段应该创建索引
  与其他表关联的字段，外键关系应该创建索引
  常用来查询的字段

无需创建索引

数据表记录太少、频繁更新的字段（如果创建索引，每当更新字段都会更新索引）、数据重复且分布平均的字段，不宜创建索引（性别字段，国籍字段等）

4、索引的优缺点

创建索引可以大大提高系统的性能（优点）：
1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时
间。
5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
增加索引也有许多不利的方面(缺点)：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空
间，如果要建立聚簇索引，那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了
数据的维护速度。

5、MySQL的索引

MySQL 使用的是 B+tree 索引

5.1 Hash索引和索引和B+树索引的区别？

hash索引，等值查询效率高，不能排序,不能进行范围查询；B+树数据有序,范围查询

5.2 B树和B+树的区别?

1、B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，
叶子结点不包含任何关键字信息。
2、B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的
指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是
索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而B树的非终节点也
包含需要查找的有效信息)

5.3 为什么说B+B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

1.B+的磁盘读写代价更低
B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
2.B+tree的查询效率更加稳定
由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以
任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，
导致每一个数据的查询效率相当。

5.4 聚集索引和非聚集索引区别?

聚合索引(clusteredindex):
     聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。聚集索引类似于新华字典中用拼音去查找汉字，拼音检索表于书记顺序都是按照a~z排列的，就像相同的逻辑顺序于物理顺序一样，当你需要查找a,ai两个读音的字，或是想一次寻找多个傻(sha)的同音字时，也许向后翻几页，或紧接着下一行就得到结果了。
非聚合索引(nonclusteredindex):
    非聚集索引指定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。非聚集索引类似在新华字典上通过偏旁部首来查询汉字，检索表也许是按照横、竖、撇来排列的，但是由于正文中是a~z的拼音顺序，所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组，大数目的不同值，频繁更新的列中，这些情况即不适合聚集索引。
根本区别：
   聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

这里添加一个楼主觉得学习数据库底层原理比较好的文章地址：https://blog.codinglabs.org/articles/theory-of-mysql-index.html