Intern Day91 - MySQL索引详解
什么是索引
-
实际上，索引是一种保存主键或索引字段的表，以及一个能将每个记录指向实际表的指针。数据库搜索引擎用索引来快速定位记录。
-
当数据库的数据量大的时候，索引的性能对数据库非常重要，甚至比查询优化更重要。但是索引并非查询优化的最佳原则，但大多数情况下足够用。在大数据情况下通常要考虑分库分表。（优化查询 < 加索引 < 分库分表 ）。分库分表见：https://zhuanlan.zhihu.com/p/70025377
-
索引分为很多种，都在下面有一一介绍。
加索引的原因
可以优化大量查找、海量数据的情况的查找时间（快速找到所需数据），否则MySQL就会去一行行遍历，查询效率极慢。
索引好处
-
对于频繁查找的字段，加上索引之后对该字段查询速度会加快
-
对于海量数据的查找，加上正确的索引，可以在一定程度上优化查询速度
-
提高查询效率
-
提高聚合函数查询效率
-
提高排序查询效率
-
使用覆盖索引避免回表
总结：提高查询速度
索引缺点
-
当碰到insert、drop、update、alter等增删改操作的时候，不仅需要对需要的数据进行更新，还需要对索引文件进行更新，耗时。
-
加的索引是存放在磁盘里的，加入过多的索引会占用大量的磁盘空间。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件会得膨胀很快。
总结：降低增删改操作的执行效率 + 过多索引占用磁盘空间
创建索引的情况
-
主键自动建立唯一索引
-
频繁作为查询条件的字段
-
查询中与其它表关联的字段，外键关系建立索引
-
单键/组合索引的选择问题，组合索引性价比更高
-
查询中排序的字段，排序字段如果通过索引去访问将大大提高排序速度
-
查询中统计或者分组字段
不需要创建索引的情况
-
表记录太少
-
经常增删改的表或字段（因为提高了查询速度，但同时会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据还要保存索引文件）
-
Where条件里用不到的字段不创建索引
-
过滤性不好的不适合建索引（如性别列）
注意
-
索引列不能参与计算，只要参与计算查询就不会走索引。因为b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，成本太大。
-
不要在NULL值列上使用索引，尽量使用NOT NULL约束列上使用索引
-
很少查询的字段不要使用索引
-
大数据类型字段不创建索引
-
不要在条件NOT IN、<>、!= 等范围查询中使用索引
-
模糊查询时不要用%开头
-
查询索引的字段不要函数计算
-
联合索引查询时遵循最左前缀匹配原则
-
全部扫描超过30%不会走优化器
-
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
-
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。
-
使用了索引不代表查询最优。
索引失效场景
-
当查询条件不满足最左前缀时，MySQL不会为使用索引（或不会对不符合最左前缀的列使用索引）。
-
当查询条件中含有函数/表达式时，MySQL不会为该列使用索引。
-
在like查询中，以"%"开头的查询值不会使用索引。只有以前缀开头的查询值才能使用索引。
-
在范围查询中，第一个范围列后面的列不会使用索引。
-
在MySQL5.0之前，查询语句中包含or就不会使用索引；5.0之后，只要or连接的两个字段都有独立的索引，那么会通过索引合并的方式进行查找（那么就能使用索引）。
加索引语法
注意：加索引有三种办法，分别是1新建表中添加索引、2在已建表中添加索引、3以修改表的方式添加索引。详情见：https://www.cnblogs.com/bruce1992/p/13958166.html。
我下面只写了 以修改表的方式添加索引 ：
查看索引：show index from student \G;
总：alter table 表名 add 索引 索引名(列名);
主键索引：alter table 表名 add primary key 索引名(列名);
唯一索引：alter table 表名 add unique 索引名(列名);
普通索引：alter table 表名 add index 索引名 (列名);
全文索引：alter table 表名 add fulltext 索引名(列名);
组合索引：alter table 表名 add index 索引名 (列名1,列名2,列名3...)
还可以在创建表的时候直接指定索引，自行查找。
删除索引语法
修改表的方式删除索引：alter table 表名 drop index 索引名;
删除主键索引：alter table 表名 drop primary key;
普通索引index
-
概念：最基本的索引类型
-
适用场景：多行记录有相同值
-
优点：没有任何限制，多行记录可以包含相同值。主要用于提高查询效率
-
缺点：没有任何约束
全文索引fulltext
-
概念：如果我们想要通过关键字的匹配来进行查询过滤，就需要基于相似度的查询，而不是精确数值的比较。全文索引就是为这种场景设计的。你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？解释见下。
-
适用场景：在一篇文章中检索文本信息。（like适用于文本较少的时候，但是对于大量的文本数据检索是不行的。全文索引在大量的数据面前，比like快很多倍，速度不是一个数量级）
-
优点：对于大规模的数据，通过
alter table或create index创建全文索引比把记录插入带有全文索引的空表速度更快。 -
缺点：
-
MySQL 自带的全文索引只支持英文。
-
只可以在VARCHAR或者TEXT类型的列上创建。
-
仅适用于MyISAM表
-
针对较大的数据，生成全文索引耗时和空间
-
全文索引可能存在精度问题
-
主键索引primary key
-
概念：主键索引在唯一索引的基础上增加了不为空的约束，也就是 NOT NULL+UNIQUE，每个表只能有一个主键，且不允许有空值。
-
适用场景：关联查询
-
优点：单表查询中，主键索引与唯一索引的检索效率没有太大区别，但在关联查询中，主键索引的检索速度比唯一索引快。
-
缺点：
唯一索引unique
-
概念：在普通索引的基础上增加了数据唯一性的约束，索引列的值必须唯一，但允许有空值。
-
对于单列索引，要求该列所有数据都不相同，但允许有NULL值
-
对于多列的组合索引，要求这些列的组合是唯一的。
-
-
适用场景：一般我们创建唯一索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
-
优点：防止增加或者修改后产生相同数据，从而保证数据的完整性。
-
缺点：
前缀索引prefix index
-
概念：前缀索引建立的基础就指定列数据有很多的共同前缀
-
适用场景：
-
优点：
-
缺点：
组合/复合/联合索引
-
概念：在索引的创建中，有两种场景，单列索引和多列索引。在表中两个或者两个列以上的基础上创建索引。
-
适用范围：为了更多的提高mysql效率可建立组合索引，遵循最左前缀原则。
-
优点：
-
缺点：
覆盖索引
-
概念：一个索引包含(或者说是覆盖)需要查询的所有字段的值
-
适用场景：
-
优点：
-
缺点：
哈希索引hash
-
概念：字面意思
-
适用场景：哈希索引只有精确匹配索引所有列的查询才有效。
-
优点：查找速度快
-
缺点：
-
存储的数据不是按照索引列的值顺序存储的，所以无法用于排序。
-
只能用于 =、<=>、IN操作符
-
优化器不能用于ORDER BY操作
-
任何查找操作必须是索引的完整列。
-
-
参考：https://blog.csdn.net/sunjin9418/article/details/80334142
B+树索引B+Tree
-
概念：在索引的创建中，有两种场景，单列索引和多列索引。
-
适用范围：
-
用于比较或范围查找操作(=, >, >=, <, <=, BETWEEN, IN)
-
用于(GROUP BY, ORDER BY)操作
-
对于字符串类型的索引，最左前缀字符串也可以充分利用索引
-
-
优点：
-
缺点：
外键索引
-
概念：如果为某个外键字段定义了一个外键约束条件，MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
-
适用范围：
-
用于比较或范围查找操作(=, >, >=, <, <=, BETWEEN, IN)
-
用于(GROUP BY, ORDER BY)操作
-
对于字符串类型的索引，最左前缀字符串也可以充分利用索引
-
-
优点：
-
缺点：
