MySQL索引

最新推荐文章于 2022-10-31 08:54:10 发布

小熊123～

最新推荐文章于 2022-10-31 08:54:10 发布

阅读量151

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/xx897115293/article/details/111569621

版权

数据库专栏收录该内容

11 篇文章 0 订阅

订阅专栏

MySQL索引

MySQL高效获取数据的数据结构

MySQL索引图解珍藏版

MySQL索引知识点珍藏版2

SQL执行计划详解

MySQL索引分类

主键索引   唯一、不能为null
唯一索引   唯一
普通索引
全文索引   解决CHAR,VARCHAR,TEXT类型字段太长时模糊查询性能低的问题
前缀索引   解决CHAR,VARCHAR,TEXT类型字段太长时前缀模糊查询性能低的问题，可指定索引列长度
空间索引   5.7新特性

单列索引
联合索引最左匹配原则

MySQL索引数据结构

Hash
   key存索引列,value存行
   等值查询时O(1);范围查询时全表扫描
有序数组
下标存索引列,值存行,顺序排列
   等值查询和区间查询都很快,但和ArrayList一样，虽然查询快，但添加删除元素都要移动后面所有的元素,维护成本太高
二叉查找树
   二叉树;有序;
   极端情况下会出现树不分叉的情况,很不稳定
平衡查找树
   二叉树;左右子树层级最多差1;
   接近二分查找，不会出现左右子树高度相差很大的情况;O(log2n)
   时间复杂度和树高有关,不支持范围查询快速查找,范围查询时需要从根节点多次遍历
B树
   多叉平衡查找树
       B树的节点中存储着多个元素，每个内节点有多个分叉
       节点中的元素包含键值和数据，节点中的键值从小到大排列。也就是说，在所有的节点都储存数据
       父节点当中的元素不会出现在子节点中
       所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接
   B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
   如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。
B+树
   B+树和B树最主要的区别在于非叶子节点是否存储数据的问题
       B树：非叶子节点和叶子节点都会存储数据。
       B+树：只有叶子节点才会存储数据，非叶子节点只存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
   B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。
   所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟着增加的，
   所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

MyISAM索引

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。

InnoDB索引

主键索引(聚簇索引)
       在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
       如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
       如果以上两个都没有，InnoDB 会使用一个6字节长整型的隐式字段ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
       主键索引的叶子节点会存储数据行
       磁盘IO数量：3次。

   辅助索引(非聚簇索引)
       辅助索引只会存储主键值
       根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询
       磁盘IO数：辅助索引3次+获取记录回表3次

   组合索引
       最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的
       可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引
       组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配

覆盖索引

主键索引和组合索引设置的好可以避免回表(回表的意思是根据辅助索引查到主键索引，再根据主键索引回表查，如果组合索引把要查询的字段都覆盖到了，就不需要回表了)

总结一

避免回表

       在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。
       这个过程我们称为回表。想想回表必然是会消耗性能影响性能。那如何避免呢？
       使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）
       如果在一个场景下，select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。
       这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。

联合索引的使用

       联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。
       比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

       联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，
       也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那我建议这种情况下使用联合索引。

联合索引的使用

考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

索引失效的情况

1、函数或表达式

select * from test where  num  +  1 = 5

2、存在NULL值条件

select * from user where user_id is not null;

我们在设计数据库表时，应该尽力避免NULL值出现，如果数据有为空的情况可以给一个默认值，比如数值型的可以给0、-1，字符类型的可以给空字符串。

3、用or表达式作为条件，有一个列没有索引，那么其它列的索引将不起作用

select * from user where user_id = 700 or user_name = "老薛";

像这种，如果user_id有加索引，而user_name没有的话，那么执行的时候user_id的索引也是失效的，这也是为什么开发中尽量少用or的原因，除非是两个字段都加了索引。

4、列与列对比，某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引

select * from test where id = c_id;

5、数据类型的转换。如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

create index `idx_user_name` ON user(user_name)
select * from user where user_name = 123;

像上面这种，虽然给user_name建立了索引，但查询的时候条件没有当成字符串，这样的话就不会走索引。

6、NOT条件

当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

select * from user where user_id<>500;
select * from user where user_id in (1,2,3,4,5);
select * from user where user_id not in (6,7,8,9,0);
select * from user where user_id exists (select 1 from user_record where user_record.user_id = user.user_id);

7、like查询是以%开头

当使用模糊搜索时，尽量采用后置的通配符，例如要查姓张的人，可以用user_name like ‘张%’，这样走索引时，可以从前面开始匹配索引列，但如果是这样user_name like ‘%张’，那么就会走全表扫描的方式

8、多列索引，遵循最左匹配原则，这个上面说了

什么时候该用索引

前面说了，索引虽然能加快查询速度，但本身也会占用空间，所以，索引的创建并不是越多越好，为了使索引能有效应用，我们要把索引留给最有用的查询字段，一般来说，应该在这些字段上创建索引：

主键字段，这不用多说了吧；
经常需要搜索的列，比如where条件经常用到的字段；
其他表的外键字段，作为连接表的条件字段，可以有效加快连表查询速度；
查询中作为排序、统计或者是分组的字段；

同样，对于有些字段不应该创建索引，这些列包括

频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
where条件里用不到的字段，不创建索引；
表记录太少，不需要创建索引；
对于那些定义为text，image类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引；
数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，这种字段建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。

explain关键字

explain是MySQL的关键字，通过该关键字我们可以查看搜索语句的性能。

这是查询表的数量，一共有三千多万行，这么多的数据，我们搜索的时候肯定要用到索引才行，至于索引是否会生效，我们也可以通过该关键字来看下，

看，搜索的条数瞬间降到了16条，走的索引是 index_user_id，证明我们的索引是生效的。

关于explain的几个重要参数，我们有必要了解一些：

id：查询的序列号

select_type：查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

type：

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref >fulltext > ref_or_null > index_merge > unique_subquery >index_subquery > range > index > ALL

System效率最高，ALL的话已经是全表扫描了，一般来说，查询至少要达到range级别。

key：

显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

key=primary的话，表示使用了主键；

key=null表示没用到索引。

possible_keys：

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要检查语句中是不是有什么情况导致索引失效。

rows：

表示执行计划中估计扫描的行数，是个估计值。

Extra：

如果是Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不着where，一般就是没查出来啥。
出现using index就说明我们的索引是生效的。

总结二

好了，索引的知识点就介绍到这了，最后总结一下索引的注意事项吧。

1、索引要根据表数据的使用情况来创建，不能创建太多，一般一张表不建议超过6个索引字段

2、好刀要用在刀刃上，经常用于查询，没多少重复数据，搜索行数不超过表数据量4%的字段用索引的效果比较好

3、创建联合索引要注意最左匹配原则，切记，最左边的字段是必传字段，这点我他妈就吃过大亏

4、查询语句要用explain执行计划来查看性能。

小熊123～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引

MySQL索引MySQL高效获取数据的数据结构 MySQL索引分类主键索引唯一、不能为null唯一索引唯一普通索引全文索引解决CHAR,VARCHAR,TEXT类型字段太长时模糊查询性能低的问题前缀索引解决CHAR,VARCHAR,TEXT类型字段太长时前缀模糊查询性能低的问题，可指定索引列长度空间索引 5.7新特性单列索引联合索引最左匹配原则MySQL索引数据结构Hash表 ...
复制链接

扫一扫