MySql学习-索引

已于 2022-08-07 08:59:39 修改
阅读量480 收藏 1
点赞数 1
分类专栏： mysql进阶篇 文章标签： mysql 学习 数据库
于 2022-08-06 18:12:45 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xiangdahaodaima/article/details/126173101
版权

学习参考https://www.bilibili.com/video/BV1Kr4y1i7ru?p=89&spm_id_from=pageDriver&vd_source=c1e24194b786e20ae3c82b47a6df3723

目录

索引概述

        概述

        优缺点 

索引结构

        1.（从二叉树到B+树）

                (1)二叉树

                (2)红黑树（平衡二叉树）

                (3)B树

                (4)B+树

        2.hash

索引分类

        (1)聚集索引

        (2)二级索引

        思考题

索引语法

​编辑

        1.创建索引

        2.查看索引

        3.删除索引

sql性能分析

        1.查看执行频次

        2.慢查询日志

        3.show profiles

                (1)是否支持 

                (2)是否开启

                (3)查看指令耗时

        4.explain执行计划

        explain各字段的含义 

使用规则

        (1)验证索引效率

        (2)最左前缀法则(针对联合索引)

        (3)范围查询

        (4)索引失效情况

                1.索引列运算

                2.字符串不加‘’

                3.模糊查询 

                4.or连接的条件

                5.数据分布影响

        sql提示

                1.use index;

                2.ignore index;

                3.force index; 

        覆盖索引

        前缀索引

单列索引和联合索引 

设计原则

索引概述

        概述

        优缺点 

索引结构

 

        1.（从二叉树到B+树）

                (1)二叉树

二叉树实现https://blog.csdn.net/xiangdahaodaima/article/details/122799566

                (2)红黑树（平衡二叉树）

                (3)B树

                (4)B+树

很明显，

所有元素都会出现在叶子结点，

所有数据都在叶子结点，

所有叶子结点被连起来形成单向链表

 

其实就是把叶子结点从单向链表变为了双向循环链表使得范围搜索变得容易实现。

相较于二叉树，b+树层级更低，搜索效率更高

相较于B树

相较于hash

        2.hash

 哈希实现https://blog.csdn.net/xiangdahaodaima/article/details/122771032

索引分类

        (1)聚集索引

        (2)二级索引

 找的是对应行的id。在会到聚集索引查找对应的行，也是回表查询。

 所以，一般为了高效率的查找某一个数据，一般先查找他的主键或者唯一。

也就是尽量通过聚集索引查找而不是二级索引查找。

        思考题

首先，让我们来看一下假设：

1.一行数据大小为1k （即为1024个字节）。

2.一页可以存储16行这样的数据 （即为一页的大小为1024*16个字节）。

3.innodb的指针占用6个字节。

4.主键占用8个字节。

5.指针永远比主键多一个。（不懂的重修数据结构B树，不想深究的可以看上面的图，在一个页中指针比主键多一个）

假设主键有n个（所占空间为n*8），

那么innodb指针就有（n+1）个（所占空间为(n+1)*6），

一页的大小为16*1024.

那么就有等式 n*8+(n+1)*6 = 16*1024

可以得知一页中最多（有1170个主键和1171个innodb指针）。

若一个B+树的高度为2，那么他所能存储的数据（row）为

1171*16（即为一个innodb指针对应一页数据，一页能存放16row） 

若一个B+树的高度为3，那么他所能存储的数据（row）为

1171*1171*16

第一高（根）                        共有1171个innodb指针，对应1171个第二高度中的页

第二高                                   共有1171*1171个指针，对应存放（row）数据的页

第三高（叶子）                    共有1171*1171*16行数据

索引语法

        1.创建索引

create table sku(
	id int primary key auto_increment,
	name varchar(10),
	phone char(11),
	age int unsigned,
	status int,
	email varchar(20)
);
insert into sku(name,phone,age,status,email)values
('1','12345678901',5,1,'1348664623s'),
('2','12345678901',6,1,'1348664623w'),
('3','12345678901',7,1,'1348664623q'),
('4','12345678901',8,1,'13486646237'),
('5','12345678901',9,1,'13486646236'),
('6','12345678901',10,1,'13486646235'),
('7','12345678901',11,0,'13486646232'),
('8','12345678901',12,0,'13486646234'),
('9','12345678901',13,0,'13486646237'),
('10','12345678901',1,0,'13486646234');

create index XXXXX on 表名(字段);

没有指定索引结构，默认是Btree（B+TREE）。

create unique index XXXXXX on 表名(字段);

在个字段添加unique约束时会自动创建一个索引。

用以上代码创建时又会多一个索引。

create index XXXXXX on 表名(字段,字段,字段,....);

        2.查看索引

show index from 表名;

show index from 表名\G;

 

可以看出，以id为关键字的B树索引（实际上是B+树）。

        3.删除索引

drop index 索引名 on 表名;

注意到age索引是删除不掉的(先天)，而 xxage能被删除掉(后天)。

sql性能分析

        1.查看执行频次

show global status like 'Com_______';

 进行一次select查询后

 select次数从5变为6.

        2.慢查询日志

show variables like 'slow_query_log';

 在liunx系统下(我的是kali)默认是关的

但在windows系统下默认是开启的。

先查看一下我的my.dnf的路径

 因为我的finalshell无法以root用户登入到我的kali,所以我是直接在kali系统上面更改my.cnf.

 更改后尝试重启mysql

报错，寄

 查看日志

发现是我改了my.cnf的问题

决定在改回去

启动成功。

啧啧。。。 

因为之前乱搞，直接把mysql给初始化了，

所以重启用户。。。。。。。。。。。。。。。。。。。。。。

yysy，之前那张粉毛狐狸的背景图把我看花眼了。

---------------------------------------------------------------------------------------------------------------------------------

另辟蹊径

在root用户下采用命令行形式

set global slow_query_log = 1;

 

 成功。

set global long_query_time = 2;

 成功。

在当前目录下发现慢查询日志kali-show.log

发现并没有写入。。。

参考博客https://blog.csdn.net/TomAndersen/article/details/105754295?ops_request_misc=&request_id=&biz_id=102&utm_term=%E4%BD%BF%E7%94%A8%E5%91%BD%E4%BB%A4%E8%A1%8C%E6%9F%A5%E7%9C%8B%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-1-105754295.142^v39^pc_rank_v38,185^v2^control&spm=1018.2226.3001.4187https://blog.csdn.net/TomAndersen/article/details/105754295?ops_request_misc=&request_id=&biz_id=102&utm_term=%E4%BD%BF%E7%94%A8%E5%91%BD%E4%BB%A4%E8%A1%8C%E6%9F%A5%E7%9C%8B%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduweb~default-1-105754295.142%5Ev39%5Epc_rank_v38,185%5Ev2%5Econtrol&spm=1018.2226.3001.4187

https://blog.csdn.net/weixin_30995429/article/details/114798804?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165974881716782391873162%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=165974881716782391873162&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~pc_rank_v38-1-114798804-null-null.142^v39^pc_rank_v38,185^v2^control&utm_term=%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%9C%89%E8%AE%B0%E5%BD%95%E6%95%B0%E4%BD%86%E6%89%BE%E4%B8%8D%E5%88%B0%E8%AE%B0%E5%BD%95&spm=1018.2226.3001.4187https://blog.csdn.net/weixin_30995429/article/details/114798804?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165974881716782391873162%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=165974881716782391873162&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~pc_rank_v38-1-114798804-null-null.142%5Ev39%5Epc_rank_v38,185%5Ev2%5Econtrol&utm_term=%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%9C%89%E8%AE%B0%E5%BD%95%E6%95%B0%E4%BD%86%E6%89%BE%E4%B8%8D%E5%88%B0%E8%AE%B0%E5%BD%95&spm=1018.2226.3001.4187

终于成功。

那么，对于本人步骤中

1，在root用户下 ： set global slow_query_log='ON';

2，在一般用户下： set long_query_time = 0.00001;

3, 不用重启，重启的话上述设置会归为默认值，也是是off，10.

4，profiles相关语句不会写入到日志中。

        3.show profiles

                (1)是否支持 

select @@have_profiling;

                (2)是否开启

select @@profiling;

 

为0，该功能未被打开。

开启该功能

set profiling = 1;

 

 

                (3)查看指令耗时

show profiles;

这四条指令是我开启profile后输入的。

show profile for query id;
#id为上面所查表里的id
show profile cpu for query id;

 

        4.explain执行计划

desc 语句;

explain 语句;

        explain各字段的含义 

使用规则

        (1)验证索引效率

 sn无索引

建立索引

 

 验证效率

 效率嘎嘎高。

        (2)最左前缀法则(针对联合索引)

 

以上三种情况均走联合索引

 这就不走联合索引了，因为没有最左的profession。

只要profession在，后面的字段顺序就无关紧要。

要注意row的变化。

profession的位置也无关紧要，但必须存在。

        (3)范围查询

 

 row为49而不是54，说明status没有走联合索引，因为前面的age用的是范围查询(>,<)。

优先级顺序上述 pro，age，status。

所以写的时候不论位置，只要age使用了范围查询(>,<),那么优先级比age低的字段索引都会失效。

建议使用(>=,<=) 

        (4)索引失效情况

                1.索引列运算

 索引无了。

                2.字符串不加‘’

隐式类型转换，索引失效。

 status 使用隐式类型转换，对其索引失效，row值不为54而为49.

                3.模糊查询 

 后面模糊，走索引。

只要前面模糊，不走索引。

                4.or连接的条件

 age只在联合索引，也就是age没有索引。

                5.数据分布影响

 只要大部分数据满足where后面的条件，就可能不走索引 。

只要where后面满足的数据量够大，就不走索引。

        sql提示

        mysql选择走联合索引   

                1.use index;

虽然但是，只是建议使用这个索引，mysql会有自己的优化判断。

                2.ignore index;

 忽视这个索引

                3.force index; 

强制使用这个索引 

        覆盖索引

  

 以上的执行计划都差不多一样。extra都是using where、 using index性能高

通过二级索引都能拿到所有信息的就是覆盖索引。

但加上name后，using index comdition.性能低

需要通过二级索引拿到id后在通过id查到name信息。

 

避免使用select*就是为了避免回表查询。

根据主键查询性能是最高的。

        前缀索引

单列索引和联合索引 

其实前面认真听了有思考的一看就懂

我认为这一节都能直接跳过了。

设计原则

--------------------------------------------------------------------------------------------------------------------------------

 #在root用户下
set global slow_query_log='ON'; 
 #在一般用户下
set long_query_time = 0.00001;
set profiling = 1;

终于写完了，奖励一下自己。

爱睡觉更爱学习
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySQL学习-索引部分
05-20
用Xmin制作的思维导图
MYSQL学习笔记-索引
08-28
MYSQL学习笔记-索引MYSQL学习笔记-索引MYSQL学习笔记-索引
MySQL索引学习
qq_38533859的博客
07-23 396
我们本次先讲一下索引的种类，再详细讲一下我们常使用到的B+索引。最后说一下索引使用过程中的一些问题。
mysql索引学习（一）
u013374645的博客
12-02 216
前言 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是一种数据结构。数据库查询是数据库的主要功能之一，最基本的查询算法是顺序查找（linear search）时间复杂度为O(n)，显然在数据量很大时效率很低。优化的查找算法如二分查找（binary search）、二叉树查找（binary tree search）等...
mysql学习之索引篇
m0_52963553的博客
09-04 176
mysql索引基础介绍和类型
MySQL基础索引学习
棉花泡泡糖的博客
06-08 264
MySQL中InnoDB引擎的索引是B+树，具体要去了解一下为什么不用其他的数据结构链表：显然不合适，不方便查找BST：当数据是递增的时候，退化成了链表AVL：能保持树的平衡，但条件严格，高度不能超过2，需要不停左旋右旋，是一种插入慢，检索快的数据结构红黑树：算是BST和AVL的折中版本，但如果数据量过，一侧的树会很深B树：一个磁盘块16KB中能存的数据较少，存放的是index和dataB+树：只有叶子结点才会有数据data，并且是按顺序排的，存放的数据更多索引列如果是varchar，优化的时候可以进行截取
新手学习MySQL索引
09-09
如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有索引的MySQL就是一个人力三轮车。通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢，下面小编来带大家了解下索引
MySQL SQL高级特性 字段约束-索引-视图-外键学习实践
04-27
MySQL SQL高级特性 字段约束-索引-视图-外键学习实践，很不错
MYSQL学习笔记-索引参照.pdf
12-08
MYSQL学习笔记-索引参照.pdf
数据分布不均匀导致查询不走索引。
whb234174124的博客
10-20 804
测试表 SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID ...
第19期：索引设计（哈希索引数据分布与使用场景）
ActionTech的博客
12-30 403
这里讲述 MySQL 哈希索引的实现方式以及使用场景。 哈希表在 MySQL 里有如下应用： 各种存储引擎的哈希索引存储。MySQL 的 Memory，NDB 都有哈希索引的具体实现； MySQL 内部生成的哈希表。比如： 数据在 innodb buffer pool 里的快速查找； 子查询的物化自动加哈希索引； JOIN KEY 无 INDEX 下的 HASH JOIN 等。 一、哈希数据分布 哈希索引显式应用主要存在于内存表，也就是 Memory 引擎，或者是 MySQL 8.0 的 Temp.
分布式索引入门
鸭梨的博客
08-05 1034
说到分布式索引时，我们在谈论什么？ 首先，我要说明一下谈到分布式索引，需要了解什么样的内容。你已经知道存储引擎中包含数据文件和索引文件，同时索引文件中又有索引组织表这种主要的形式。 目前世界上主要的分布式数据库的数据存储形式，就是围绕着索引而设计的。 为什么会这样呢？ 由于分布式数据库的数据被分散在多个节点上，当查询请求到达服务端时，目标数据有极大的概率并不在该节点上，需要进行一次甚至多次远程调用才可查询到数据。由于以上的原因，在设计分布式数据库存储引擎时，我们更希望采用含有索引的数据表，从而减少查询的延迟
MySQL索引笔记
jerryhao0216的博客
04-03 103
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）。当字段类型为字符串（varchar，text）等式，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。其中前缀长度可以根据索引的选择性来确定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
MySQL进阶（一）
weixin_56697114的博客
02-05 536
MySQL体系结构：（1）连接层最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。（2）服务层第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。（3）引擎层存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。
MySQL 优化 (四) --------- 索引设计与使用规则
在森林里麋了鹿
05-18 649
目录一、索引设计二、索引失效三、索引使用 一、索引设计 1.针对于数据量大，且查询比较频繁的表建立索引。 2.针对于常作为查询条件 (where)，排序 (order by)，分组 (group by) 操作的字段建立索引。 3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 6
MySQL----索引失效
emmmm.....
04-25 1030
文章目录索引失效情况1 索引列运算2 字符串不加引号3 模糊查询4 or连接条件5 数据分布影响 索引失效情况 1 索引列运算 不要在索引列上进行运算操作， 索引将失效。 目前表格拥有的索引： show index from tb_user; 当根据phone字段进行函数运算操作之后，索引失效。 explain select * from tb_user where substring(phone,10,2) = '15'; 当根据phone字段进行等值匹配查询时, 索引生效。( 没有进行运
使用数据库索引的利弊
wj_warmly的专栏
09-19 8937
优点：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构， 作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的。
头歌mysql数据库 - 初识mysql
最新发布
06-28
### 回答1： MySQL是一种开源的关系型数据库管理系统，它是最流行的数据库之一。它可以在多种操作系统上运行，包括Windows、Linux和Mac OS等。MySQL具有高性能、可靠性和可扩展性等优点，被广泛应用于Web应用程序、企业级应用程序和移动应用程序等领域。初学者可以通过学习MySQL的基本概念、语法和操作来掌握它的使用方法。 ### 回答2： MySQL 是一种开源的关系型数据库管理系统，由于其开源、跨平台、易用性等特点，成为了当前最流行的数据存储方式之一。初识MySQL需要了解以下几个方面： 一、MySQL 的概述 MySQL 是一个关系型数据库管理系统。它可以进行快速、可靠、高效的大容量数据处理。其主要特点是在进行大量数据操作时速度快、延迟低。 MySQL 具有多线程能力，可以提高数据库的并发处理能力。而且还可以实现多个用户并发访问。 MySQL 采用客户端/服务器模式架构，安装简单，且性能和稳定性都相对较好。 二、 MySQL 的安装与配置 MySQL 可以在各种操作系统上安装，如在 Linux 上使用 apt-get 命令，也可以在 Windows 上使用安装程序。在安装完成后需要进行配置，包括设置 root 用户密码，指定数据存储路径，端口等。 三、MySQL 的基本概念 MySQL 中的一些基本概念包括： 1. 数据库（Database）：MySQL 数据库是由一系列表组成的容器，用于存储数据。 2. 表（Table）：表是 MySQL 数据库中最小的单元，用于存储数据。 3. 列（Column）：表中的每个数据项都是基于列的。 4. 行（Row）：每一行代表一条记录，实际上是一个记录的集合。 5. 主键（Primary key）：一种特殊的约束，用于保证表中每个记录均有唯一标识，常用于在进行数据更新或删除时进行定位。 四、MySQL 的基本操作 MySQL 的基本操作包括增删改查等，其中最常用的是查询数据。关于查询数据，需要注意使用 SQL 语句，语法应正确且逻辑正确。 五、MySQL 的应用 MySQL 在各种场景下都有广泛的应用，如 Web 应用程序（例如在线商店、博客），移动应用程序，游戏等。由于其量级小、效率高、稳定性好的特点，深受开发者的欢迎。 总体来说，初识 MySQL 需要了解其基本概念、安装配置、基本操作等。作为开源的关系型数据库管理系统，MySQL 在各大领域都有广泛的应用，其影响力不断扩大。 ### 回答3： MySQL是一种关系型数据库管理系统（RDBMS）。它是一种开源软件，是世界上最广泛使用的关系型数据库管理系统之一。MySQL由瑞典MySQL AB公司所开发，现在由Oracle公司维护。 MySQL具有兼容性高、开放的源代码、体积小、性能优越、易于安装和配置等优点。MySQL广泛应用于Web服务器上，它与PHP、Python、Perl等编程语言之间有着良好的兼容性。 MySQL支持多种操作系统，如Linux、Windows、Unix、Free BSD等等。同时也支持多种编程语言，如C、C++、Java、PHP、Python、Perl等等。 在学习MySQL时，你需要掌握数据库创建、数据表创建、数据表间关系的定义和维护、记录插入、查询和修改等技能。MySQL是一个非常灵活、易于学习的数据库管理系统，但它的灵活性也可能会导致一些问题，例如由于外键、约束、索引等原因而引起的性能问题，因此，使用MySQL需要掌握一些基本知识。 此外，在MySQL学习过程中需要注意的是安全性问题。我们可以使用用户名和密码来保护我们的MySQL系统，确保只有授权的用户可以访问数据库。在创建用户时，我们需要为每个用户分配不同的权限，以便他们可以执行他们需要的操作而且不能执行其他命令。 总之，MySQL是一种非常强大、广泛使用的数据库管理系统，它不仅具有广泛的应用层面，而且易于学习和使用。因此，学习MySQL是每个开发者的必备技能之一。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱睡觉更爱学习

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值