MySql学习-索引

学习参考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
    评论
### 回答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、付费专栏及课程。

余额充值