| |
|
开发:
C++知识库
Java知识库
JavaScript
Python
PHP知识库
人工智能
区块链
大数据
移动开发
嵌入式
开发工具
数据结构与算法
开发测试
游戏开发
网络协议
系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程 数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁 |
-> 数据结构与算法 -> 详解MySQL索引 -> 正文阅读 |
|
[数据结构与算法]详解MySQL索引 |
原文链接详解MySQL索引 索引介绍索引是帮助MySQL高效获取数据的数据结构。在数据之外,数据库系统还维护着一个用来查找数据的数据结构,这些数据结构指向着特定的数据,可以实现高级的查找算法。 本文以MySQL常用的B+Tree来介绍。(MySQL的索引结构不仅只有B+Tree索引,还有Hash索引等。) B+Tree(俗称B+树)我们构造一个具有如下数据的4阶B+树;数字如下:100,26,78,102,657,123,90,12,67,89,90,102,365,256 我们可以发现,所有的数据都会出现在叶子节点(也就是最底部的节点,下面再没有分层),非叶子节点作为key(B+树如何分裂的在此不过多介绍,因为本文只为介绍索引,介绍B+树也只是为了能更好的理解索引,B+树并不是本文的重点。) B+树规定,小于往左走,大于等于往右走; 那么如果我们想查询值为26的数据,B+树是如何查询的呢? 1.首先它会和最顶部的100比较,发现比100小,向左走; 2.到达了存储key为78和90两个值的节点,发现26比78小,再向78的左边走; 3.到达了存储12,26,67的叶子节点,在此就查询到了值为26的数据; 那么我们如果想查询值为100的数据呢? 首先它会和最顶部的100比较,发现等于100,但是非叶子节点只存储key,还会向叶子方向走;大于等于往右走,小于往左走,直到找到叶子节点。 MySQL的索引对B+Tree还做了改良,叶子之间的链表变成了双向链表。 索引分类在MySQL数据库,将索引的具体类型主要分为以下几类: 主键索引、唯一索引、常规索引、全文索引
聚集索引、二级索引在InnoDB存储引擎中(一般我们使用的大都是InnoDB存储引擎,MySQL除InnoDB存储引擎外,还有MyISAM存储引擎等,本文不过多介绍),根据索引的存储形式,又可以分为以下两种: 聚集索引和二级索引
我们介绍一下聚集索引的选取规则: 聚集索引选取规则: 1.如果存在主键,主键索引就是聚集索引; 2.如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引; 3.如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 聚集索引和二级索引的区别聚集索引的叶子节点下挂的是这一行的数据 。 二级索引的叶子节点下挂的是该字段值对应的主键值。 如果我的表user_test里有两个字段id和name,id是主键,name上有二级索引; 第一条sql:select * from user_test where id = 6; 第二条sql:select * from user_test where name = ‘郭靖’; 第一条sql在使用聚集索引查询数据的时候,到达叶子节点,就直接能够查询到这行数据了;第二条sql在使用二级索引查询数据的时候,到达叶子节点,只是拿到了这行数据对应的主键,还需要进行回表查询,才能拿到数据。 索引语法我们创建一张表,表结构如下:
表创建完成之后,如下所示: 查询索引语法如下:
案例如下:
因为我们表里有主键,所以它会有一个默认的主键索引。 创建索引语法如下:
[ UNIQUE | FULLTEXT ]分别表示唯一索引和常规索引; 案例1如下:
如图所示: 案例2如下:
我们现在再查看一下这个表的索引 删除索引语法如下:
案例如下:
再来看一下索引: 可以看到只剩两个索引了。 sql性能分析——explain介绍EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行 过程中表如何连接和连接的顺序。 语法如下:
案例如下:
输出的重要字段介绍可先不看,通过后面的例子理解着记忆
准备演示的表:
我使用java批量插入了100万条数据,如下sql使用了batch,可以很快速得插入大量数据。 java代码如下:
运行完输出如下: 表中信息截取如下:
通过主键索引来查询数据查看索引,目前只有主键索引。 我们通过主键来搜索一下数据:
可以看到千万级数据查询只用了0.174秒。 explain分析
通过explain分析 type是const,性能已经很高; possible_key是PRIMARY,说明可能用到的索引是主键索引; key 是PRIMARY,说明实际用到的索引是主键索引; key_len是4,说明使用该索引时使用的字节数是4; 通过user_name查询数据查看索引,目前只有主键索引。 无索引时分析
运行结果如下: 可以看到8.471s; explain分析
可以看到type是ALL,性能很差。 有索引时分析建立索引
可以看到创建索引的过程需要的时间是140多秒,因为它要建立B+树! 再次查询
可以看到查询时间降到了0.157秒; 为避免MySQL的缓存,我们换个值来查询一下:
可以看到还是不到2秒。 这提升的速度已经很可观了。 explain分析
通过explain分析 type是ref,性能已经很高; possible_key是idx_userinfo_user_name,说明可能用到的索引是我们刚才创建的索引idx_userinfo_user_name; key 是idx_userinfo_user_name,说明实际用到的索引是idx_userinfo_user_name; key_len是152,说明使用该索引时使用的字节数是152; 最左前缀法则如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。最左边的列必须存在! 在介绍之前,为避免干扰,我们先把刚才建立的user_name索引删掉;
介绍我们对tbl_user_info的user_name,password和sex建立联合索引;
请记住我们建立索引的顺序:user_name,password和sex; 查询索引: 可见,在这三个列上都出现了这个联合索引。 user_name,password,sex三个条件查询
可以看到查询速度是0.173秒; explain分析
通过explain分析 type是ref,性能已经很高; possible_key是idx_userinfo_username_password_sex,说明可能用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key 是idx_userinfo_username_password_sex,说明实际用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key_len是304,说明使用该索引时使用的字节数是304; 使用user_name,password查询
我们可以看到查询速度是0.208秒; explain分析
type是ref,性能已经很高; possible_key是idx_userinfo_username_password_sex,说明可能用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key 是idx_userinfo_username_password_sex,说明实际用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key_len是304,说明使用该索引时使用的字节数是304; 使用user_name查询
速度是0.150s; explain分析
type是ref,性能已经很高; possible_key是idx_userinfo_username_password_sex,说明可能用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key 是idx_userinfo_username_password_sex,说明实际用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key_len是152,说明使用该索引时使用的字节数是152; 只使用password查询
查询速度时9秒多! explain分析
type是ALL,性能很差; 没有用到索引; 只使用sex
查询了740多秒还没查出来,给它停了。 explain分析
type是ALL,性能很差; 没有用到索引; 使用user_name和sex查询
explain分析
type是ref,性能已经很高; possible_key是idx_userinfo_username_password_sex,说明可能用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key 是idx_userinfo_username_password_sex,说明实际用到的索引是联合索引idx_userinfo_user_name,idx_userinfo_username_password_sex; key_len是152,说明使用该索引时使用的字节数是152; 使用password和sex查询
使用了8秒多。 explain分析
type是ALL,性能很差; 没有用到索引; 最左前缀法则总结如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。最左边的列必须存在! 范围查询联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。 在介绍之前,我们新建一个表来进行演示;之前那个表的sex是char类型的,不好通过索引使用的长度来分析某一列是否用到了索引;
创建索引先查看下索引: 只有主键索引
请记好顺序,name,age,num; 使用三个条件(不加范围)
key_len是91. 三个条件(age带范围)
key_len是48了,比91小,范围查询右侧的列索引失效。
如果是大于等于,就用上了。 索引失效情况在索引列上运算在索引列上加运算会失效; 执行如下sql,删掉之前的联合索引,为学号建立一个唯一索引。
在索引列上运算查询分析
没有用到索引。 字符串不加引号
没有走索引。 模糊查询尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引将会失效。 我们重新使用tbl_user_info这个表,来进行演示。为什么不用student表了呢?(因为数据量较少,MySQL会去判断走索引和全表扫描的效率都如何,有可能会自己选择了全表扫描)。 将tbl_user_info的索引都删掉(为方便演示,我重建了表,然后只插入了1w条数据,不然建索引的时间太长)。
三个等值条件头部likekey_len是152,长度小于308,没有走索引; 尾部likekey_len等于308,走索引了。 or连接条件用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会 被用到。 tbl_user_info的nick_name没有索引 即使id有主键索引,也没有用到。 数据分布影响如果MySQL评估使用索引比全表更慢,则不使用索引。is null 、is not null是否走索引,得具体情况具体 分析,并不是固定的。 sql提示我们可以告诉MySQL数据库使用哪个索引,忽略哪个索引; 语法: use index : 建议MySQL使用哪一个索引完成此次查询(仅仅是建议,mysql内部还会再次进 行评估)。 force index : 强制使用索引。 ignore index : 忽略指定的索引。 一般使用在某一列有多个索引和我们不想让MySQL自己去评判是否走索引时使用。 案例
覆盖索引尽量使用覆盖索引,减少select *。 覆盖索引是指查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到 。 比如我想查询tbl_user_info的user_name字段,我给它加了单列索引,我如果使用select * 的话,因为没有存储其他列,还需要回表查询;如果我是select user_name,那么直接就能查出来了。 前缀索引当字段类型为字符串(varchar,text,longtext等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO, 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。 语法
前缀索引的索引长度可以根据如下计算:
第一行得出的值除以第二行得出的值,越接近1,查询效率越高。从使用的空间和效率上做平衡。(是否需要牺牲空间换时间) 索引设计原则1.针对于数据量较大,且查询比较频繁的表建立索引。 2.针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。 3.尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。 4.尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间, 避免回表,提高查询效率。 5.要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。 原文链接详解MySQL索引 |
|
|
上一篇文章 下一篇文章 查看所有文章 |
|
开发:
C++知识库
Java知识库
JavaScript
Python
PHP知识库
人工智能
区块链
大数据
移动开发
嵌入式
开发工具
数据结构与算法
开发测试
游戏开发
网络协议
系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程 数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁 |
360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 | -2024/11/13 10:49:43- |
|
网站联系: qq:121756557 email:121756557@qq.com IT数码 |