MySql学习-索引_mysql数据分布对索引的影响-优快云博客

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

索引概述

概述

优缺点

索引结构

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

(1)二叉树

二叉树实现https://blog.youkuaiyun.com/xiangdahaodaima/article/details/122799566

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

(3)B树

(4)B+树

很明显，

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

所有数据都在叶子结点，

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

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

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

相较于B树

相较于hash

2.hash

哈希实现https://blog.youkuaiyun.com/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.youkuaiyun.com/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.youkuaiyun.com/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.youkuaiyun.com/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.youkuaiyun.com/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;

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