Mysql(InnoDB)索引原理及的使用

已于 2022-10-27 22:35:29 修改
阅读量1.1k 收藏
点赞数
文章标签： java 链表 数据结构
于 2022-03-03 22:42:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_56079407/article/details/123262082
版权

索引概述

  • 正常的业务SELECT居多，而INSERT UPDATE DELETE偏少，所以使用索引， 用空间换时间；查询变快，增删改变慢。
  • 可以选择给单个字段加索引，也可以联合几个字段加索引
  • 主键和外键默认都有索引，没有主键会自动生成rowid

索引结构

索引是存储引擎实现的，InnoDB的索引结构是B+树，大部分的引擎也都支持B+树索引

B+树索引的优点

  • 层级少，2200W的数据只有3层
  • 叶子节点存数据+指针(利于排序)
  • 支持范围查询
  • 支持排序(区间指针)
  • 索引全部出现在最下层，即便是他们作为了上层分支

InnoDB到底支持Hash索引吗？

InnoDB在特定条件下会自适应Hash索引，用户不能手动创建，可以理解为“索引的索引”，可以加快索引查询速度

关于这个问题，参考https://wenku.baidu.com/view/912836ffb5360b4c2e3f5727a5e9856a571226ef.html

相比B+树的优缺点：

  • 不支持范围查询，但指定查询效率更高(一般只需一次检索)
  • 哈希冲突问题使用链表解决
  • 无法排序，哈希值计算结果没有顺序关系

参考

索引4种分类

聚集索引and二级索引

聚集索引：唯一；索引处叶子节点包括行数据
二级索引：多个：索引处叶子节点包括数据+关联的聚集索引

聚类索引

  • 主键就是聚类索引
  • 没有主键就是UNIQUE作为聚类索引
  • 都没有则InnoDB自动生成rowid作为隐藏的聚类索引

二级索引及回表查询

  • 每个叶子节点都包含对应行的聚类索引
  • 回表查询：例如当SELECT * FROM时，这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式

索引语法

详细参考

主键和外键

主键：ALTER TABLE nxbusers ADD PRIMARY KEY ( id );

外键：ALTER TABLE nxbinfolike ADD FOREIGN KEY ( infoid ) REFERENCES nxbpushinfo ( infoid );

也可以在设计表的时候直接添加。关于是否添加外键，可以参考
https://www.cnblogs.com/jxl00125/p/12622969.html
https://blog.csdn.net/bisal/article/details/50934304

查看索引

SHOW INDEX FROM nxbcomments
一般建立的索引都是普通类型，至于索引方法还可以选择Hash，但不建议

创建索引

默认：普通索引

CREATE INDEX idx_comment_infoid ON nxbcomments ( infoid )

全文索引or唯一索引

同理可以把FULLTEXT更换为UNIQUE
CREATE FULLTEXT INDEX idx_comment_infoid ON nxbcomments ( infoid );

联合索引

注意索引命名方式，括号内顺序就是索引建立的顺序，关乎索引失效问题
CREATE INDEX idx_pushinfo_topic_location ON nxbpushinfo ( topic, location );

这样一来就建立了“话题”和“定位”的索引，在查询的时候可以只查询话题（索引失效的应用）

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

删除索引

DROP INDEX idx_comment_infoid ON nxbcomments;
或者

ALTER TABLE nxbcomments DROP INDEX idx_comment_infoid;

索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为**查询条件（where）、排序（order by）、分组（group by）**操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

联合索引失效

  • 索引失效会带来一些问题，同时也可以巧妙应用
  • 索引的触发是通过where的，没有where则不触发索引
  • where的条件的顺序没有影响，有影响的是定义索引时的顺序
  • 总结：跳过了定义时的中间索引(后面的失效)；<>（后面的失效）；进行了运算（后面的失效）；字符串不加引号（后面的失效）；前缀%模糊查询（后面的失效）；or连接条件有的没索引(全失效)；查询已排序好的数据的边缘

最左前缀法则

创建索引（topic , location , tag）即该索引在底层的建立顺序是从左到右

  • 如果where条件中出现了topic = “” 则触发索引机制
  • 如果where条件中只写了topic = “” 和 tag = “” 而跳过了中间的location，则索引只对topic生效
  • 如果where tag = “” and loacation = “” and topic = ""这种顺序也是可以的，只要是出现了topic就能触发索引

我们可以利用该特性实现《只定义一个联合索引，达到多方式筛选的目的》

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
当范围查询使用>= 或 <= 时，走联合索引了，所有的字段都是走索引
的。
所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引列运算

例如explain select * from tb_user where substring(phone,10,2) = ‘15’;
会导致索引失效

字符串不加引号

字符串类型的数据如果不加引号，对查询结果没有影响，但是不会触发索引

不触发：SELECT * FROM nxbpushinfo WHERE infoid = 1643877357315 ;

触发：SELECT * FROM nxbpushinfo WHERE infoid = ‘1643877357315’ ;

头部模糊查询

尾模糊不失效，头模糊失效

or连接条件

or连接的条件必须都有索引，不然都会失效；开发中可以使用UNION进行优化

where topic = “” or location = ""如果topic字段有索引（单独索引or联合索引）但是location没有索引，那么topic的索引也失效

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。
例如：已经按排序字段数据，查询倒数第10到正数第一的所有数据，不走索引
例如：一个表中绝大多数都是null，那么WHERE 有索引的字段 is not null也不走索引

EXPLAIN

直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

可以查询索引的使用情况

SQL提示

SQL提示，是优化数据库的一个重要手段，可以指定查询走联合索引or指定索引

use index建议

建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）

select * from 表 index(指定索引) where 条件；

ignore index忽略

忽略指定的索引。
如果有其他包含的索引，则走其他索引。没有则不走索引

force index强制

放的位置也和use index 一样，强制使用索引，不听mysql评估

索引覆盖(避免回表)

概念：where中出现的字段都有索引，select的字段尽量是这些索引的字段，而非其他字段

如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据（回表查询）

前缀索引

当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO， 影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

例如：对江苏南京，四川绵阳这种location字段的省份建立索引(不考虑黑龙江是三个字)

create index idx_location_province on nxbpushinfo(location(2))

例如：手机号码的前缀决定了地域，也可以添加前缀索引

ZJH'blog
关注
MySQL InnoDB索引使用实践--深入浅出
vipjustin的专栏
11-30 830
本文所有内容、概念、存储结构都基于MySQL5.7及以上版本，存储引擎为：InnoDB，详细介绍MySQL各种索引，以及索引不生效的实例
MySQL的InnoDB索引原理详解
最新发布
CHIKA2333的博客
08-11 111
　　本来不打算从二叉搜索树开始，因为网上已经有太多相关文章，但是考虑到清晰的图示对理解问题有很大帮助，也为了保证文章完整性，最后还是加上了这部分。　　先看看几种树形结构：　　1 搜索二叉树：每个节点有两个子节点，数据量的增大必然导致高度的快速增加，显然这个不适合作为大量数据存储的基础结构。　　2 B树：一棵m阶B树是一棵平衡的m路搜索树。最重要的性质是每个非根节点所包含的关键字个数 j 满足：┌m/2┐ - 1
InnoDB索引机制
weixin_30621919的博客
07-11 71
MySQL－ InnoDB锁机制 InnoDB与MyISAM的最大不同有两点： 一是支持事务（TRANSACTION）；二是采用了行级锁。 MyISAM不支持事务、行锁和外键，访问速度快（表级锁，可同时读，不可写），多使用于多读写少或对事务完整性没有要求的情况； MEMORY：将所有的数据保存在RAM中，对表的大小有限制； MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起...
innodb的索引机制
星星的技术专栏
03-07 769
1）innodb将数据和索引存储在一起 2）innodb索引是基于b-tree的clustered-index，b-tree中的每个结点存储key+row data；innodb中的第二个索引存储，在搜索时，先通过key找到pk，再通过pk搜索b-tree的结点，从中找到row data 3）由于聚集索引和行数据存放一起(在同一个数据页中)，所以利用聚集索引访问数据行时，非常的快，同一个数据页在访
索引及InnoDB
u010287873的博客
10-08 234
https://www.cnblogs.com/jianmianruxin/p/7213690.html  数据库索引 https://www.cnblogs.com/jianmianruxin/p/7238550.html  数据结构及算法 数据如何存储： 表是根据主键顺序组织存放的。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶...
MySQL之InnoDB索引使用规则
weixin_52690231的博客
03-31 949
MySQL之InnoDB索引使用规则
MySQL InnoDB存储及索引原理
oQingTian123456的专栏
09-02 597
MySQL查询执行流程 MySQL执行一条SELECT查询SQL起自于客户端发起的一条请求。MySQL服务收到请求后首先根据SQL查询缓存，若果该查询SQL已经事先执行过，则直接返回缓存中的数据给客户端。否则进入SQL解析优化阶段。MySQL解析器对SQL进行语法解析，生成解析树，然后经过预处理阶段生成一棵新的语法解析树。将解析树送入查询优化器中生成执行计划。查询执行引擎根据执行计划调用...
MySQL Innodb 索引原理详解
02-28
在深入探讨MySQL Innodb索引之前，我们先了解几种基本的树形数据结构，包括二叉搜索树、B树、B+树以及B*树。 ##### 1.1 搜索二叉树（Binary Search Tree） 搜索二叉树是一种特殊的二叉树，每个节点至多有两个子...
MySQL（一）、InnoDB索引原理及优化
王义凯 的博客
08-11 1144
MySQL的索引可以大大提高MySQL的检索速度，在大厂的面试中也多会问关于索引和事务相关的问题，本篇我们来详细介绍MySQL中的索引及其使用和优化。
MySQL中， in和or 会走索引吗
热门推荐
梦~'
08-16 1万+
参考： mysql or走索引吗_加了索引，mysql查询就一定会用吗？ InnoDB行锁变表锁的原因可能有哪些？（待总结...） 1.MySQL in 会用到索引吗？ 不一定，要看情况，具体是由MySQL优化器内部决定是全表扫描还是索引查找，用效率较高的一种方式。 （1）针对索引字段的唯一性不高的情况下（索引的"区分度"低），优化器可能会选择全表扫描，而不是走索引。这可能是因为等值查询符合条件的记录太多了，导致了mysql认为全表扫描比用索引查找更快。 比如你对唯一性不高的字段（如性别：男/女.
mysql InnoDB索引详解
weixin_46591962的博客
03-16 680
mysql 索引
mysql or会用到索引吗_mysql or条件可以使用索引而避免全表
weixin_33765908的博客
01-26 1095
在某些情况下，or条件可以避免全表扫描的。1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。1)myisam表：CREATE TABLE IF NOT EXISTS `a` (`id` int(1) NOT NULL AUTO_INCREMENT,`uid` int(11) NOT NULL,`aNum` char(20) DEFAULT NULL,PRI...
php mysql百万数据count_百万级mysql数据库使用count、group、order查询非常慢，怎么优化...
weixin_29666725的博客
01-18 612
找到问题所在了，又学到一个新知识。SELECTuser_id,count(*)ascountfromprize_numberswhereuser_id>0andtime>1545972485GROUPBYuser_idORDERBYcountdesc如题，查询要6秒，user_id，time都设置了索引。多次尝试后发现以下2句查询速度非常快，在1秒...
InnoDB索引，终于懂了
Java搜索工程技术栈
02-13 218
《数据库索引，终于懂了》介绍了为什么B+树适合做数据库索引，数据库的索引分为主键索引（Primary Inkex）与普通索引（Secondary Index）。InnoDB和MyISAM是怎么利用B+树来实现这两类索引，其又有什么差异呢？ 问题1：MyISAM的索引结构是怎样的？ MyISAM的索引与行记录是分开存储的，叫做非聚集索引（UnClustered Index）。 其主键索引与普通索引没有本质差异： （1）有连续聚集的区域单独存储行记录； （2）主键索引的叶子节点，存储主键，与对应行记录
轻松理解 MySQL InnoDB 索引、B+树索引、查询原理
weixin_45060897的博客
08-27 217
前言 索引对于DB查询的性能起到至关重要的作用。对于索引如何提升查询性能，通常都会拿查字典来做类比。字典前面会有拼音索引，我们查字典会先查拼音索引，以此来提高查字典的速度。对于这个类比，我们可以思考的更深入点，看看通过拼音索引提升查询速度的根本原因是什么。我们考虑如下几个问题： 1、通过拼音索引能直接定位到字的具体位置吗？ 不能，拼音索引只能定位到字所在的页，如果想找到所要的字或者词，还需要在页中再次定位。这个过程和InnoDB的索引设计相同，通过索引只能定位到数据所在页。 2、定位到页后，如何定位到具体字
MySQL索引和B+Tree底层原理
BlueProtocolBlog
10-13 789
MySQL索引方面的知识
mysql insert 索引_MySQL优化INSERT速度由于索引而减慢
weixin_30499315的博客
01-28 772
小编典典如果要快速插入，首先需要的是合适的硬件。假定有足够的RAM，一个SSD而不是机械驱动器以及相当强大的CPU。由于您使用InnoDB，因此默认设置是针对慢速和老式计算机设计的，因此您需要对其进行优化。在那之后，您需要了解一件事-那就是数据库在内部如何执行其工作，硬盘如何工作等等。我将在以下描述中简化该机制：MySQL是一个事务，它等待硬盘驱动器确认它已写入数据。这就是为什么机械驱动器上的事务...
（二）mysql索引操作
Xinghf
02-28 1367
索引 　　当我们对一个字段经常查询,而修改很少的时候 ，数据量又很大的情况下，我们可以对该字段建立索引。所谓的索引就像我们在word为一篇文章建立目录，我们可以根据word的页码和目录尽快的查询出我们想要的内容。在mysql中，索引的意义也是如此，mysql有如下几种索引累心。 一、普通索引 没有特殊的含义索引，代码如下: 直接为某个表的某个字段创
MySQL InnoDB索引深度解析
"深入讲解MySQL InnoDB索引的原理，包括索引...理解并合理使用InnoDB索引原理对于数据库性能优化至关重要。通过创建适当的索引策略，可以显著提高数据检索的速度，降低数据库系统的响应时间，从而提升整个应用的性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值