MYSQL索引基础

最新推荐文章于 2022-12-10 00:29:46 发布

weixin_42904118

最新推荐文章于 2022-12-10 00:29:46 发布

阅读量451

点赞数 1

分类专栏： MYSQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/weixin_42904118/article/details/122238150

版权

MYSQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

MYSQL索引基础

分类
索引实现
- B-TREE
- Hash
使用
面试常问题
- 一条SQL语句执行得很慢的原因有哪些
- - 大多数情况下很正常，偶尔很慢，则有如下原因
  - 这条 SQL 语句一直执行的很慢，则有如下原因。

分类

MySQL索引分为普通索引、唯一索引、主键索引、组合索引、全文索引。索引不会包含有null值的列，索引项可以为null（唯一索引、组合索引等），但是只要列中有null值就不会被包含在索引中。

（1）普通索引：create index index_name on table(column)；

或者创建表时指定，create table(..., index index_name column);

（2）唯一索引：类似普通索引，索引列的值必须唯一（可以为空，这点和主键索引不同）

create unique index index_name on table(column)；或者创建表时指定unique index_name column

（3）主键索引：特殊的唯一索引，不允许为空，只能有一个，一般是在建表时指定primary key(column)

（4）组合索引：在多个字段上创建索引，遵循最左前缀原则。alter table t add index index_name(a,b,c);
     最左前缀原则：https://mp.weixin.qq.com/s/RemJcqPIvLArmfWIhoaZ1g
      **如何查看组合索引是否命中，就通过key_length长度看**
     
（5）全文索引(FULLTEXT)：表示全文搜索的索引，仅可用于 MyISAM引擎 表。 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。但是在检索的时候数据量比较大的时候，现将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多。

（6）空间索引（SPATIAL）：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

从另外的角度还可以分为

聚集索引。

表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
非聚集索引。
　　表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

索引实现

索引主要由两种实现：B-TREE和HASH。

B-TREE

B-TREE以B+树结构存储数据，大大加快了数据的查询速度
B-TREE索引在范围查找的SQL语句中更加适合(顺序存储)
全值匹配的查询SQL，如 where act_id= ‘1111_act’
联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_actid_name(act_id,act_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引(注：覆盖索引除外)
匹配模糊查询的前匹配，如where act_name like ‘11_act%’
匹配范围值的SQL查询，如where act_date > ‘9865123547215’(not in和<>无法使用索引)
覆盖索引的SQL查询，就是说select出来的字段都建立了索引

Hash

Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引
存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询
Hash索引无法用于排序
Hash不适用于区分度小的列上，如性别字段
Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。
Hash 索引在任何时候都不能避免表扫描。
Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

使用

何时使用索引

MySQL每次查询只使用一个索引。与其说是“数据库查询只能用到一个索引”，倒不如说，和全表扫描比起来，去分析两个索引B+树更加耗费时间。所以where A=a and B=b这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。

（1）主键，unique字段；
（2）和其他表做连接的字段需要加索引
（3）在where里使用＞，≥，＝，＜，≤，is null和between等字段；
（4）使用不以通配符开始的like，where A like 'China%'；
（5）聚集函数MIN()，MAX()中的字段；
（6）order by和group by字段；
（7）离散度大(不同的值多)的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高。
  因为离散度越小，说明查的数量越多，执行器就会比较看是否值得做索引，还是直接用全表比较好，导致索引失效

何时不使用索引

（1）表记录太少；
（2）数据重复且分布平均的字段（只有很少数据值的列）；
（3）经常插入、删除、修改的表要减少索引；
（4）text，image等类型不应该建立索引，这些列的数据量大（假如text前10个字符唯一，也可以对text前10个字符建立索引）；
（5）MySQL能估计出全表扫描比使用索引更快时，不使用索引；

索引何时失效

（1）使用负向查询(not ，not in， not like ，<> ,!= ,!> ,!< ) 不会使用索引。
（2）在索引列上的操作，函数（upper()等）
（3）联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引。
     参考：[链接](%28https://www.cnblogs.com/developer_chan/p/9223671.html)
（4）like未使用最左前缀，where A like '%China'；
（5）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
（6）①最佳左前缀法则。
       -在等值查询时，更改索引列顺序，并不会影响explain的执行结果，因为mysql底层会进行优化。
       -在使用order by时，注意索引顺序、常量，以及可能会导致Using filesort的情况。
（7）left join(左连接)：左边记录全有，所以在右边创建索引
             right join(右连接)：右边记录全有，所以在左边创建索引
             [左右连接](https://www.cnblogs.com/developer_chan/p/9219239.html)
（8）[索引优化](https://www.cnblogs.com/developer_chan/p/9219934.html)

总结：

表中的索引并不是越多越好，冗余或者无用索引会占用磁盘空间并且会影响增删改的效率。
单列索引：节点中的关键字[name]
联合索引：节点中的关键字[name, age]
可以把单列索引看成特殊的联合索引，联合索引的比较也是根据最左匹配原则。把单列索引删除
Where 条件中，like 9%， like %9%， like%9，三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的，决定于列的离散型，结论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描。
使用负向查询(not ，not in， not like ，<> ,!= ,!> ,!< ) 不会使用索引。但是比如IS NULL、like 9%,这些条件还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。理解了这个也就好理解为什么在WHERE子句中出现IS NULL这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。