一、进阶介绍
二、存储引擎-MySQL的体系结构
连接层:接收客户端的连接,完成连接的处理,以及客户端的认证授权。以及相关安全方案,以及检查是否超过最大连接数等等...
服务层:绝大部分核心功能都是在服务层完成的,所有跨存储引擎的实现也都是在服务层完成的,比如DML语句,DDL语句的封装,以及存储过程,视图,触发器。这些都是在服务层。
引擎层:如图,MySQL给我们提供了很多存储引擎供我们选择,如果这些存储引擎不满足我们需求,我们还能在其基础上进行扩展。因此称为可插拔式存储引擎。存储引擎控制的是我们MySQL当中数据的存储和提取的方式。服务器通过API和存储引擎来进行通信和交互。
Index索引是在存储引擎层实现的。因此不同的存储引擎索引的结构不同。
InnoDB引擎是MySQL5.5版本之后默认的存储引擎
存储层:具体数据库的数据最终是存储在磁盘当中的,包含一系列日志和文件。
三、存储引擎:简介
MySQL5.5版本之后,默认存储引擎:InnoDB。
因此不显式指定存储引擎的话,默认就是INNODB。
-- 查询建表语句默认存储引擎
-- 查询当前数据库支持的存储引擎
四、存储引擎-InnoDB介绍
对于InnoDB引擎。每张表都对应这样一个表空间文件。
我们找到对应的目录。不能直接用记事本打开,因为里面存储的是二进制数据
ibd2sdi account.ibd
如果想看一看。可以在cmd窗口。通过这个命令开打开看看。
返回了这一长串json。表里面的字段 都可以在里面看到。这就是表空间文件。
逻辑存储结构
后面讲解SQL优化的时候,还会再提到 逻辑存储结构。
五、存储引擎-MyISAM 和Memory
MyISAM
sdi就是表结构的存储文件。这个是可以直接打开的。里面是json 格式的数据。
MYD是存放数据的
MYI是存储索引的。
Memory
对于memory 只有 sdi 这一个文件。因为它的数据都存放在内存中。这个文件只存放表结构的数据。
InnoDB和MyISAM的区别(重点:可能是面试题 )
六、存储引擎-选择
存储引擎没有好坏之分。只是在合适的业务场景中选择合适的存储引擎。以满足业务的需求
绝大多数场景使用的是InnoDB。
1.使用MySIAM这种存储引擎场景的时候都会被
NoSQL(Not Only SQL)数据库。NoSQL 系列数据库替代。像MongoDB
2.而使用memory存储引擎这样的场景当中又被另外一个NoSQL 系列数据库替代。就是redis
七、存储引擎小结
八、MySQL安装(linux版本)
九、索引-概述
索引是一种数据结构。用的是哪种数据结构呢? 后续会一 一介绍。
1.索引的介绍
如果没有索引。我们查询一张表 会是什么样的呢?
如下图
会从上到下一个一个查询匹配,知道匹配到了45,还会继续匹配,因为可能还有=45的数据
有索引
有索引的话,类似右边,构造了一个二叉树,每个节点与每条数据一 一 匹配。只需要三次就找到了45这条数据。
备注:上述二叉树索引结构只是一个示意图,并不是真正的索引结构。
只是以二叉树为例。
2.索引的优缺点
优点:
1.提高查询效率
2.提高排序效率
缺点:
1.占用空间
2.降低了增删改的效率。
缺点其实可以忽略不计,因为磁盘很便宜,空间也很大。
其次一个正常业务系统。增删改的比例很小。主要是查询
十、索引-结构-介绍
索引是在第三层,存储引擎层实现的。不同的存储引擎有着不同的索引结构。
有哪几种索引结构呢?
1.B+Tree索引:
2.Hash索引:
3.R-(空间索引):用的少,了解一下就行。
4.Full-text(全文索引):用的也很少,了解一下就行
不同的存储引擎对不同索引结构的支持情况
我们平常所说的索引,如果没有特别说明,都是指 B+ 树结构组织的索引。
十一、索引结构-Btree
B树的演变过程
我们演示一个 五阶B树 的构造过程。
五阶B树,就有五个指针。四个key。
一层的时候
一层跳变两层的过程:中间元素向上分裂。
再插入1200的时候。中间元素345向上分裂。下面连接两个子节点。
再插入1000。右边第二层分裂。
中间元素1200向上分类到第一层。
两层分裂三层演示。再插入2546。中间元素1980会向第一层分裂。并且第一层满。
中间元素1200会成为新的第一层元素。
十二、索引-结构-B+tree
MySQL中的B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化,增加了一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
标准B+树的构造过程
以五阶B+树为例。那么就有五个指针,4个key
在B+树的数据结构当中,所有的元素都会出现在叶子节点。叶子节点会形成一个单向链表。
非叶子节点只是起到索引数据的作用。
第一层满。
一层变两层:再插入890 看看会发生什么变化 。
发现中间元素0567向上复制且分裂。叶子节点还存在0567。并且在右边。
且叶子节点形成一个指针。构成一个链表。
当第二层右边一个节点快要满了,此时再插入2345。
中间元素1000向上复制一份。再分裂。右边节点包含1000。
十三、索引-结构-hash
注:如果存在哈希冲突,可能就会多次。 要去链表查询。
十四、索引-结构-思考题
为什么InnoDB存储引擎选择使用B+tree索引结构?
十五、索引-分类
聚焦索引:主键构建的主键索引就是聚集索引。聚集索引的叶子节点挂的数据就是这一行的行数据。
二级索引:如果要针对name字段建立的索引,及其他字段建立的索引就是二级索引。二级索引叶子节点挂的是对应的Id。
执行过程(SQL查询)
select * from user where name = 'Arm';
Arm 和 Lee一对比。A在L之前。因此走左侧的指针。再继续定位到左侧元素。得到Arm。
最终我们返回的是这一行的Id值。之后。
返回聚集索引。再去找这一行的数据。 这个过程也被称之为
回表查询:指的是先走二级索引得到这一行主键值。再回到聚集索引拿到这一行的行数据。
十六、索引-思考题
InnoDB主键索引的B+tree为多高呢?
这里n指的是节点中的key值。
一页 固定是16KB。因此一页存储16行数据。
当高度为2的时候:
非叶子节点不存放数据,只存放 key 和指针 而指针永远比key多一个。因此。假设key有n个
可以列出这个公式。一页最多(第一层就是一页)可以存放的主键和指针数量为:
8*n + (n+1)*6 = 1024*16 →解得n ≈ 1170
因此每一个节点 key 的个数最多大约为1170 。那么这个节点下面就有1171个指针。每一个指针指向下面的子节点。一个子节点最多可以存储16行数据。
第二层:用来存储数据。
一共1171个节点。一个节点存储 16行数据。
因此能够存储的数据是16*1171=18 736行数据。
当高度为3的时候:
一个节点再分裂1171个子节点。一共1171个节点。因此如果是高度为3。就能存放
1171*1171*16 = 21 939 856 行数据。
如果存储四五千万,上亿的记录,就要考虑分库分表了。
十七、索引-语法
创建索引
查看索引
show index from tb_user;
删除索引
drop index idx_user_email on tb_user;
案例
我们准备了一个tb_user表。表的内容如下。
我们先查看一下这张表现有的索引。
show index from tb_user;
如下图,可以看到这张表目前只有一个主键索引。因为id是主键。因此会自动创建一个主键索引。使用的索引结构就是B+Tree。显示的是BTree是因为B+Tree由Btree而来。我们知道这是B+Tree就行了。
1.为name字段创建索引。name可以重复
因为可以重复,那么就不能创建为唯一索引。那么就创建为常规索引。
create index idx_user_name on tb_user(name);
再查看一下这表的索引。发现多了一个 name这个字段的索引。索引名称为idx_user_name。创建索引的时候我们没有指定索引结构。默认是B+Tree。因为我们使用的是InnoDB存储引擎
2.给phone字段创建唯一索引。该字段非null 且唯一。
create unique index idx_user_phone on tb_user(phone);
再次查看这张表拥有的索引。
3.为 profession、age、staus创建联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,staus);
注:再联合索引当中,字段的顺序是有讲究的。后续会讲解。
4.为email建立合适的索引来提升查询效率。
按照常规,我们应该这样写。但是发现报错了。
create index idx_user_email on tb_user(email);
十八、索引-性能分析-查看执行频次
性能分析工具1
后面跟着七个下划线,分别代表七个字符。
查看这个让我们了解这个数据库是以增加、删除、更新还是查询为主。如果查询占了很大一部分。我们就可以针对这个数据库进行优化
十九、索引-性能分析-慢查询日志
性能分析工具2
查询慢查询日志是否开启。
show variables like 'slow_query_log';
默认是没有开启的。需要我们进入配置文件,修改配置文件进行开启。
进入配置文件
在 /etc/my.cnf 这个文件中加入。
#慢查询日志 slow_query_log=1 long_query_time=2
敲回车,后按G,将光标放到最底下。
此时是只读。需要我们按下键盘中的insert按钮。来进行插入数据
加入下面内容
#慢查询日志 slow_query_log=1 long_query_time=2
之后按下Esc退出键。然后
用冒号wq!。再敲回车的方式保存并退出。 w代表保存,q代表退出,!代表强制退出。
由于文件是只读属性,因此我们需要修改文件属性。才能写入并保存。
chattr -i /etc/my.cnf
此时应该就可以进行写入了
如果还是不行。那么就以管理员的身份去编辑这个文件。此时就可以了
sudo vim /etc/my.cnf
此时就可以保存成功了。
接着
重启MySQL服务器
sudo systemctl restart mysqld
再次执行
show variables like 'slow_query_log';
打开慢SQL日志成功!!!!!!!!!!!!!!!!!!!!
我们去看看慢SQL日志生成了吗
cd /var/lib/mysql
在这个目录下会生成一个文件,localhost-slow.log
我们去看一下这个文件。
我这边生成的是VM-16-5-centos-slow.log 这个文件
sudo cat VM-16-5-centos-slow.log
这里面现在只是记录了当前数据库的一些信息,比如
版本:
端口号:
并没有记录其他信息
执行这个命令用来看这个文件里面实时的内容
看文件实时内容
只要这个文件有新的内容添加。就会实时输出。
sudo tail -f VM-16-5-centos-slow.log
我们在另一个窗口,来查询数据库中的表。
select * from user;
发现没有新的内容生成。原因很简单。执行的时间没有超过2秒
当耗时13.35秒,就算是一个慢查询。再看看。那个文件中的变化。
这里就会记录执行SQL语句的时间,以及是哪个用户在哪个主机连接上来的。
以及执行耗时。锁了多少行,返回了多少条记录。用的哪个数据库,以及执行的SQL语句
二十、索引-性能分析-show profiles
慢SQL查询日志,只有超过了我们设定的时间,才会被记录,如果业务中SQL语句
执行达到了1.9秒。我们也需要对其进行优化。那么我们就需要用到
show profiles
性能分析工具3
查询当前MySQL是否支持profile操作。
select @@profiling;
值为0则不支持。我们将值修改为1
此时这个开关就已经打开了
set profiling = 1;
查询SQL语句耗时情况
执行一些SQL语句之后,我们来查看每一条语句的耗时基本情况。
在这里面。即使SQL语句写错了,也能查询到语句的执行时间。
show profiles;
查询指定SQL语句在各个阶段的耗时情况。(了解)
show profile for query query_Id
查询指定SQL的CPU的耗费情况(了解)
show profile CPU for query 1;
二十一、索引-性能分析-explain(重要地位)
性能分析工具4(常用)
explain执行计划
未加
加上desc
加上explain
我们可以看到 Id 、 查询类型、查询哪张表、 分区、连接类型、可能用到的索引、实际用到的索引、索引的长度、扫描的记录数......
EXPLAIN 执行计划各字段的含义
重点关注
Id 、
type:(null的性能最高,all的性能最差) 业务系统中的SQL尽量往前优化,const(主键)使用唯一索引出现const。非唯一性索引出现ref。尽量不要出现all(代表全表扫描)。index(代表用了索引,但也会对索引进行扫描,遍历整个索引树)
possible_key: 可能用到的索引。
key:实际用到的索引。
keyen _len:使用到的索引的字节数,是索引字段最大可能的长度。并非实际使用长度。不损失精度的情况下,越短越好
Extra 是额外的信息。执行查询的过程中前面字段没有展示出来的信息。将在这里展示
当我们进行多表查询的时候,
如果id相同,代表执行顺序从上往下。
拆分为3个SQL。
合并为一个SQL
explain 执行计划(id)
可以看到子子查询是最先执行的、然后执行子查询、然后执行第二个子查询。最后再执行学生表。
二十二、索引-使用规则-验证索引效率
tb_sku是一张表,里面的数据量是相对庞大的,有一千万条数据。
执行一条SQL。来看看耗时情况
根据主键进行查询:
0.00秒查询效率很快。
给后面加上/G就会把每一列转换成每一行。输出。
根据字段 sn 查询 :
返回一条数据,耗时20.78秒。耗时太长了,性能极低。因为这张表中 id 是主键。所以快
而 sn 是没有主键的。
我们针对 sn 这个字段建立一个索引。
给 sn 建立索引的耗时也是很长的。
经历 1分钟11秒 过程中,索引创建成功。 再次查询
执行耗时0.01秒 。这就是索引对于查询效率的提升。
explain一下
二十三、索引-使用规则-最左前法则
最左前缀法则
准备一张表
这张表的索引情况:
一个主键索引 :id
两个单列索引 :phone、name
一个联合索引:profession、age 、status三个字段组成的联合索引。根据最左前缀法则:要想用到这个联合索引。必须保证最左边的列存在。也就是profession这个字段必须存在,并且中间不能跳过某一列。如果跳过,那么后面的索引就会失效。
使用一下联合索引。给这三个字段都赋值。
explain执行计划:
可以看到这个联合索引被使用到了,因为它符合最左前缀法则。
条件变为两个字段:
那么还会走索引吗?
因为最左字段 profession
存在,且中间没有跳过age。因此也会生效。
下面的情况也是。只有一个profession字段。长度为47
如果没有了profession字段。就不会走索引了,如下图,走的是全表扫描。因为没有profession字段就不符合最左前缀法则了。联合索引会失效。
索引部分失效: profession走了索引,status没有走索引,索引部分失效。因为可以看索引字段长度只是47。因此只有profession走了索引
注:放的位置可以不同。但是必须存在。
范围查询
如下图status就失效了
但当我们使用 ≥ 、 ≤ 这个符号的时候,就不会失效。
二十四、索引-使用规则-索引失效情况一
①索引列运算
当我们对索引列进行运算,字符串截取。索引会失效
②字符串不加单引号
不加单引号也可以进行查询。但是会造成索引失效。
字符串不加单引号,存在隐式类型转换,因此索引会失效
③模糊查询(头部模糊匹配)
索引失效,就会进行全表扫描,我们要规避全表扫描。
二十五、索引-使用规则-索引失效情况二
④or连接条件(有一方没有索引)
id 有索引
age 没有索引
phone 有索引
为age建立索引
再次查询,此时就用到了索引了
⑤数据分布影响
当我们查询的 phone
表中少部分数据满足条件。 可以看到走了索引
如果 phone 查询的时候。
这张表绝大部分数据都满足条件。那么走全表扫描。
就不会走索引了。走的是全表扫描,因为此时走全表扫描 比 索引更快点。
二十六、索引-使用规则-SQL提示
use index:
使用我们指定的索引。这只是我们的建议使用哪个索引。mysql还会评估我们指定的索引。
到底快不快,至于最终使用什么索引还是看MySQL
ignore index:
忽视哪个索引,就会使用另外一个索引。
force index:
强制使用哪个索引
二十七、索引-使用规则-覆盖索引&回表查询
查询的字段都已经在 二级索引中找到了。(建立的联合索引也是二级索引)
就不需要回表查询了:这就叫做: 覆盖索引
如果在二级索引(辅助索引)找不到。需要回表查询。拿到这一行的数据。再提取想要的数据。
尽量避免使用 select * 是因为很容易造成回表查询。除非创建了一个联合索引。这个联合索引包括了这个表的所有字段。
查看我们表中此时的索引
一个主键索引: id
两个单列索引: phone 和 name
还有一个联合索引:pro_age_sta
我们使用联合索引。
不使用*作为字段名。使用具体的字段。
不论 id 还是 id,profession 还是 id,profession,age,还是 id,profession,age,status执行计划都是一摸一样的。
Extra: Using where;Using in;(性能高)
此时不需要回表查询,因为需要的数据都在索引列中能找到。如查询的id字段。
user表中的profession、age、status字段。建立了联合索引。所创建的联合索引属于二级索引。二级索引当中叶子节点挂的就是id 。因此在这个二级索引当中,找到了profession,age,status以及叶子节点下面挂的 id 此时查询二级索引就能拿到数据了。不需要再查询聚集索引了。
发现在输入所有字段 或者* 或者加上name,执行计划中的 Extra 这个字段发生了变化
Extra :Using index condition(性能低)
explain select id,profession,age from tb_user where profession = '软件工程' and age = 31 and status = '0';
为什么会发生变化呢?
因为之前的那些字段,查找使用了索引 。且需要的数据都在索引列中能找到,仅此不需要回表查询数据。
而加上name ,查询使用了索引,但是需要回表查询数据。
因为在联合索引中拿不到 name 字段。只能回表查询了
思考题(面试题)
答:针对username,password两个字段建立一个联合索引
create index idx_user_username_password on itcast(username,password);
这就是一个二级索引。在执行这个SQL语句的时候,直接覆盖索引,不需要回表查询
二十八、索引-使用规则-前缀索引
用来解决长字符串 或者 大文本字段 在整个字段进行索引的时候,索引体积过于庞大而造成浪费磁盘IO的情况。对于这种情况使用前缀索引,来降低索引体积,提高索引效率
column(n) :n是要截取的字符的个数。
select count(distinct email)/count(*) from tb_user;
这样求出的就是email这个字段的选择性。
索引的选择性:不重复的索引值(基数)/数据库表中记录总数
这里我们还需要用到一个函数 substring(email,1,10);
截取前10个、9个、8个、7个...我们看看选择性是多少。
从上图看,截取前九个 和 截取前 五个 选择性是一样的。那么从体积的角度考虑。肯定选择截取前五个。
看业务需求。如果想选择性更高,就选截取10个、如果想平衡选择性和体积,那么就选择截取5个。
针对email字段创建前缀索引
crearte index idx_email_5 on tb_user(email(5));
查询当前表的索引
查看执行计划 ,这就用到了前缀索引。
前缀索引执行流程图
截取的字符组成索引、进行匹配,若匹配到相同的。则回表查询去聚集索引。拿到行数据。再对比email的值是否一样。如果一样。则返回二级索引继续查找下一个节点。因为这是个链表。如果下一个节点不匹配。那么就返回这一行数据。如果匹配。还需要继续回表查询,拿到这一行数据。如果email也一样。继续去下一个节点..如果不匹配则会将前两行数据组装返回。如果匹配还会继续查询...直到不匹配为止....
用来解决长字符串 或者 大文本字段 在整个字段进行索引的时候,索引体积过于庞大而造成浪费磁盘IO的情况。对于这种情况使用前缀索引,来降低索引体积,提高索引效率
二十九、索引-使用规则-单列索引&联合索引
我们如今的数据中 phone 和 name 都是单列索引。其中phone 还是唯一索引。
执行一个SQL,看看执行计划 。根据两个字段去查。只用到了phone 这一个单列索引。此时必然会回表查询。不会是覆盖索引。
接下来创建一个 phone 和 name 两个字段的联合索引 。因为phone 已经是唯一索引。因此我们可以加上 unique关键字
执行发现只用到了phone这一个单列索引。并且Extra:null代表要回表查询。
我们可以加上SQL提示
use index(idx_user_phone_name)
现在用的就是联合索引了。Extra:Using index 此时用的是覆盖索引。此时不需要回表查询。
联合索引执行流程
在创建索引的时候,哪个索引放在前面,对查询也是有影响的,因为最左前缀法则。要想用联合索引。phone这个列必须存在。因此创建联合索引的时候,我们要考试字段顺序。
三十、索引-设计原则
第二点:建立索引尽量使用联合索引
三十一、索引-小结
这张表指定了主键,在建表时候默认键一个主键索引。
如果有一列字段值不重复,如身份证号,手机号,用户名,可以根据这些字段建立一个唯一索引。
常规索引就是正常建立的索引。全文索引不做介绍了。
InnoDB引擎中根据索引的存储结构。又分为两类
聚集索引:必须存在且只能有一个。聚集索引叶子节点挂的是一行的数据。默认主键索引就是聚集索引。如果没有主键,那么会选择第一个唯一索引作为聚集索引。如果还没有唯一索引。mysql会自动生成一个隐藏的rowId来作为聚集索引。
二级索引:叶子节点挂的是对应的主键。
索引的使用:小结
联合索引:使用原则:最左前缀法则。最左边的列必须存在。不然整个联合索引都失效。如果最左边列存在,但是中间跳过了某一列。那么后面的列索引会失效。 而且在进行范围查询的时候。右侧的列会失效。我们尽量使用≥ 、≤ 这样的操作符
索引失效:
①不要在索引列上进行函数运算、
②字符串不加引号,会造成隐式类型转换索引会失效。
③like模糊匹配,如果在前面加了%,代表前面要进行模糊匹配,索引会失效。
④all连接的条件,如果一侧有索引,另外一侧没有索引,此时会失效
⑤MySLQ评估,走全表扫描比走索引还快,此时索引会失效。:数据分布的影响
SQL提示:在实现select语句,如果里面有很多索引,myslq会根据内部策略来选择使用哪个索引。此时也可以给mysql一些提示,告诉它用哪个索引,忽略哪个索引,要强制使用哪个索引。
覆盖索引:查询返回的列在索引结构都包含了。不需要回表查询了。而回表查询指的是在查询的时候,先走二级索引,检索到这一行的Id,再根据这个id到聚集索引查找这一行的数据。
前缀索引:主要知道一下前缀索引的适用场景,:当遇到一些字符串长度较长,或者遇到一些大文本字段时。可以针对它建立前缀索引,来缩小索引体积。提高检索效率
单列/联合索引:推荐使用联合索引。联合索引性能较高。且如果联合索引适用得当,可以避免回表查询。
三十二、SQL优化-插入数据
大批量插入数据
1.客户端连接服务器时,加上参数
mysql --local-infile - u root -p
为大批量添加数据 创建数据库 itheima,
2.设置全局参数 --local-infile为1。
开启从本地加载文件导入数据的开关
先查询这个开关打开了吗
select @@local_infile
发现没有打开。手动打开。设置参数为1
set global local_infile = 1;
3.执行load指令
将准备好的数据,加载到表结构中
load data local infile 'root/load_user_100w_sort.sql' into table 'tb_user'
fields terminated by ',' lines terminated by '\n';
可以看到只耗时16.84秒 。如果通过insert 需要十多分钟
为达成这一步,先创建一个测试表。再把数据脚本上传到服务器当中
CREATE TABLE `tb_user` (`id` INT(11) NOT NULL AUTO_INCREMENT,`username` VARCHAR(50) NOT NULL,`password` VARCHAR(50) NOT NULL,`name` VARCHAR(20) NOT NULL,`birthday` DATE DEFAULT NULL,`sex` CHAR(1) DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `unique_user_username` (`username`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
查看数据脚本有多少行。
wc -l load_user_100w_sort.sql
如下图显示100w行记录。
再看一下里面的格式
是什么样的。只看前面几行
head load_user_100w_sort,sql
看当前数据脚本在哪个目录下。
可以看到在root目录下
pwd
三十三、SQL优化-主键优化
一个区包含64个页
主键顺序插入
主键乱序插入(页分裂)
因为是顺序的,因此不能插入新的页。
上述现象就是页分裂现象
页合并
假如要删除16这个数据。并不会真的被删,只是做个标识。如果被标识为删除状态。这块空间就可以被使用。
三十四、SQL优化-order by 优化
上述的前提下是使用了覆盖索引
图的age 和 phone 字段,我们建立了联合索引。因此Extra是 Using index。直接通过有序索引返回有序数据。也不需要进行回表查询。
下图的由于返回的数据 除了id age phone 还有其他字段,因此还需要进行回表查询。因此
Extra 就是 Using filesort
倒序查询(反向扫描索引)
我们创建的联合索引,B+Tree的组织结构都是升序的。此时要倒序排,因此要反向扫描这个索引了。就会出现
Extra : Backward index scan
违背最左前缀法则
创建索引的时候,age是第一个字段,而排序的时候,phone是第一个字段
一个正序一个逆序
创建索引的时候,没有指定顺序。就会默认按照升序往后走。
A代表的是ASC,是升序。
如果是D,就是 降序。
创建一个age 升序 phone 逆序的索引
此时再查就不会出现 filesort 了。
排序缓冲区。
默认大小:256K
如果占满了 ,就涉及到磁盘文件了。会在磁盘文件中排序。此时性能低。
show variable like 'sort_buffer_size';
三十五、SQL优化-group by优化
1.没有索引的执行效率
删掉索引,只留下主键索引。
根据专业进行分组,并且统计每一个专业人员的数据量。
查看执行计划,没有用到索引。
Extra : Usring temporary 用到了临时表,这个性能是比较低的。
2.有索引的时候执行效率
先创建联合索引:注意看创建索引的时候,我们写的字段的顺序。pro是第一个
1.根据profession进行分组
2.如果下面这样只是根据age分组。不满足最左前缀法则。 因此还用到了临时表
3. 根据profession 和 age 进行分组。满足最左前缀法则。因此直接走索引。
4.先用 where 找到职业是软件工程的,再将他们根据age进行分组。此时也直接走索引
这也是满足最左前缀法则的。
三十六、SQL优化-limit优化
有一张表 tb_sku 里面有1000万条数据
查询第一页
会看到执行效率很快。那我们从100万条数据开始查。那么就会很慢了。1.66秒
500万最终执行耗时10.79秒
900万 执行耗时 19.39秒
优化方案(覆盖索引+子查询)
依然从900万开始查。不过这次只查id 字段。
执行这一条语句耗时11.47秒。这样就拿到要查询的 id 了。
我想到这样的子查询,但是MySQL不支持这样的子查询。那么该怎么办呢
我们需要把返回的这个id 看成是一张表。进行多表查询。
子查询(自连接)
select * from tb_sku
where id in (select id from tb_sku order by id limit 9000000,10);
这样耗时从19 秒 变成了 11秒
三十七、SQL优化-count优化
count 没有特别好的优化方案 :
自己计数,借助key value 形式的内存级别的数据库,比如redis。 当我们执行插入数据时,直接把某一个计数 +1。在删除一条数据时 -1。 需要我们自己维护。
count(*) :就是求取这张表的总记录数。不取值,直接进行累加。
count(1):代表查询返回的每行记录放一个1进去,会按行进行累加。如果是1不是null就会进行累加。传递0、1、-1..其实都一样。0就是放个0进去。..1就是放个1进去...但不取值,服务层直接进行累加
count(主键) :就是 主键 有多少个。遍历表。取主键id的值。返回给服务层。再按行累加。
count(段名字):代表 计数的是这个字段值不为null的数据有多少个。如果有not null这个约束。就不判断了。会遍历整张表把每一行的值都取出来判断是不是null。返回服务层,按行进行累加。
演示:
三十八、SQL优化-update优化(加索引.避免行锁升级为表锁)
这里准备了 course 这张表来演示。
InooDB引擎(三大特性事务、行级锁、外键)中。使用了行级锁。
开启事务。
执行 updata 语句。
会把id为1的这一行就会被上锁。上行锁。。
此时只要事务没有提交。就会被一值锁着。若此时在另一个窗口。再更新 id 为4。此时没有任何问题。可以执行成功。
执行name = ‘PHP’ 锁住 这一行的数据。这一行的数据 id = 2。
此时再在另一个窗口。更新 id 为4的数据。按理说可以更新。但是没有更新成功。
因为 name = ‘PHP’更新这个字段的时候 name 没有索引。此时加的就不是行锁了。而是表锁
因此另一半就阻塞了。