mysql优化|尚硅谷MySQL高级笔记—

致谢
感谢尚硅谷周阳老师????
b站视频地址
卸载之前的版本
查看已安装的MySQLrpm -qa|grep -i mysql
逐个卸载即可yum remove mysql-community-server-5.6.36-2.el7.x86_64
MySQL Linux版的安装

下载MySQL安装包wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
安装MySQL源yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
在线安装MySQLyum -y install mysql-community-server
启动MySQLsystemctl start mysqld
设置开机启动systemctl enable mysqld，systemctl daemon-reload
查看默认密码vim /var/log/mysqld.log

文章图片
修改密码ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
设置远程登录GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
停掉防火墙systemctl stop firewalld，放行3306端口最好。
配置MySQL的默认编码
查看MySQL的编码show variables like '%char%';(我已经修改过了)

文章图片
修改

vim /etc/my.cnf# 添加如下配置 character_set_server=utf8 init_connect='SET NAMES utf8'
重启MySQLsystemctl restart mysqld

MySQL逻辑架构

文章图片

第一层：所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的：连接处理，身份验证，安全性等等。
第二层：这是MySQL的核心部分，通常叫做SQL Layer。在 MySQL数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。
第三层：通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统一样。
每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事务，或者取出有特定主键的行。

存储引擎不能解析SQL(innoDB除外，它会解析外键定义，因为MySQL服务本身没有实现该功能)，互相之间也不能通信。仅仅是简单的响应服务器的请求。

MySQL存储引擎
查看存储引擎

show engines;

文章图片

查看当前存储引擎

show variables like '%storage_engine%';

文章图片

MyISAM和InnoDB对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发操作。	行锁，操作时只锁住某一行，不对其他的行有影响。
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据，对内存要求高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务

MySQL执行加载顺序
sql语句

select distinct fromleft_table join right_tableon where group by having order by limit

MySQL服务器处理后的顺序

from left_tableon join_conditionjoin_type join right_tablewhere where_conditiongroup by group_by_listhaving having_conditionselectdistinct select_listorder by order_by_conditionlimit limit_num

先确定要查询的表
然后where条件过滤数据
group by进行分组
having条件过滤每个组中的数据
确定要查询的字段
对于查出来的信息进行排序
限制查询数据的量

七种JOIN理论

文章图片

MySQL并不支持full outer join,这里我们可以使用UNION,UNION用于合并两个或多个SELECT语句的结果集。
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

文章图片

索引
索引是什么

索引是帮助MySQL高效获取数据的数据结构。
排序+查找

目的：提高查找效率，可以类比字典。
优劣优势：

提高数据的检索效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据的排序成本，降低了CPU的消耗。

劣势：

实际索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是占空间的。一般而言，索引表占用空间是数据表的1.5倍。
虽然索引大大提高了查询速度，同时却降低了更新表的速度。

分类 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-enQfZWKl-1623663972304)(https://cdn.jsdelivr.net/gh/zhangliyuangit/img/索引 (1)].png)
基本语法

创建
CREATE [UNIQUE] INDEX indexName ON myTable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
删除
DROP INDEX [indexName] ON myTable;
查看
SHOW INDEX FROM tableName;

索引结构

文章图片

举个例子，在b树中查询数据如下

假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。
第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，10<15，走左路，到磁盘寻址磁盘块2。
第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<10，到磁盘中寻址定位到磁盘块5。
第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，10=10，找到10，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

文章图片

看到这里一定觉得B树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。

如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

B+树：改造B树
B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

B树：非叶子节点和叶子节点都会存储数据。

B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树结构

文章图片

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

举个例子：

等值查询：
假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左路，到磁盘寻址磁盘块2。

第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。

第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在InnoDB中Data存储的为行数据，而MyIsam中存储的是磁盘地址。）

过程如图：

文章图片

范围查询：
假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

首先查找值等于9的数据，将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘IO。

查找到15之后，底层的叶子节点是一个有序列表，我们从磁盘块6，键值9开始向后遍历筛选所有符合筛选条件的数据。

第四次磁盘IO：根据磁盘6后继指针到磁盘中寻址定位到磁盘块7，将磁盘7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将data缓存到结果集。

主键具备唯一性（后面不会有<=26的数据），不需再向后查找，查询终止。将结果集返回给用户。

文章图片

可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。
索引适用情况哪些情况下需要创建索引

主键自动创建唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建字段
查询中排序的字段，排序字段若通过索引去访问将大大提高访问排序速度
查询中统计或分组的字段

性能分析
MySQL Query Optimizer

MySQL中专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的系统信息，为客户端请求的Query提高它认为最优的执行计划。
当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算等。然后分析Query中的Hint信息，看到显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint信息或者Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后得出最后的执行计划。

Explain
能干嘛？

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的使用
每张表有多少行被优化器查询

执行计划包含的信息

文章图片

id id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
select_type

simple：简单查询
primary：查询中若包含任何复杂的子查询，则最外层被标记为paimary，俗称是鸡蛋壳
subquery：在select或where列表包含了子查询
derived：在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表里（临时表会增加系统负担，但有时不得不用）
union：若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为：derived
union result：两种union结果的合并

type

从最好到最差依次是
system > const > eq_ref > ref > range > index > all(全表扫描)
一般来说，得保证查询至少达到range级别，最好是能达到ref。

system：表只要一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计
const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量。explain select * from tbl_emp where id = 1;
eq_ref ：对于每个索引键，表中只有一条数据与之匹配。常见于主键索引或者唯一性索引。
ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行（可以是0，多个）explain select * from tbl_emp where name = 'z3'，name列上有索引。
range：检索指定范围的行，where是一个范围查询explain select * from tbl_emp where id in (1,2,3);
index：查询全部索引中的数据。通常比all快explain select id from tbl_emp;
all：全表扫描。

possible_keys\key possible_keys表示查询可能用到哪些索引。
key表示实际用了哪些索引，

出现possible_keys有列，而key为NULL的情况，这种情况是因为表中的数据不多，MySQL认为索引对此查询帮助不大，选择了全盘扫描。
如果possible_keys为NULL，则没有相关索引。在这种情况下，可以通过检查where子句看是否可以创建一个适当的索引来提高查询性能。

key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref

这列显示了在key记录的索引中，表查找值所用到的列或常量，常见的有：const(常量)、字段名(例：t1.id)

文章图片

rows 根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
EXtra

Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底如何执行给定的查询语句。

using index：查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现，一般使是使用了覆盖索引（索引包含了所有查询的字段）。对于innodb来说，如果是辅助索引性能会有不少的提高。
using where：当我们使用全盘扫描来执行某个表的查询，并且该语句的where子句中有针对该表的搜索条件。
using filesort：说明MySQL会对数据适应一个外部的索引排序。而不是按照表内的索引进行读取，MySQL中无法利用索引完成排序操作称为"文件排序"。
using temporary：使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
using join buffer：使用了连接缓存
impossible where：where子句的值总是false，不能用来获取任何元组
select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

单表优化案例
模拟数据

CREATE TABLE IF NOT EXISTS `article`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `author_id` INT (10) UNSIGNED NOT NULL, `category_id` INT(10) UNSIGNED NOT NULL , `views` INT(10) UNSIGNED NOT NULL , `comments` INT(10) UNSIGNED NOT NULL, `title` VARBINARY(255) NOT NULL, `content` TEXT NOT NULL ); INSERT INTO `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` )VALUES (1,1,1,1,'1','1'), (2,2,2,2,'2','2'), (3,3,3,3,'3','3');

查询category_id 为1且comments>1的情况下，观看数量最多的文章**

explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1 --分析sql

文章图片

type：all，全表扫描
using filesort：文件内部排序

优化因为查询用到了三个字段，我们在这个三个字段上建复合索引。

create index idx_article_ccv on article(category_id, comments, views);

文章图片

再次查看执行计划

文章图片

查看comments=3的情况

文章图片

type变成了range，这是可以忍受的。但是extra里使用的using filesort仍然无法接受。
我们已经建立了索引，为什么没用了？
这是因为按照BTree索引的工作原理。
先排序category_id
如果遇到相同的category_id，则再排序comments，如果遇到相同的comments则再排序views，
当comments字段在联合索引处于中间位置时，
因comments > 1条件是一个范围值(所谓range)
MySQL无法利用索引在对后面的view部分进行检索，即range类型的字段后面的索引无效。

重新创建索引

create index idx_article_cv on article(category_id,views);

查看执行计划

文章图片

完美收工。
双表优化案例
模拟数据

CREATE TABLE IF NOT EXISTS `class`( `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `card` INT (10) UNSIGNED NOT NULL ); CREATE TABLE IF NOT EXISTS `book`( `bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `card` INT (10) UNSIGNED NOT NULL ); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO class(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO book(card)VALUES(FLOOR(1+(RAND()*20)));

查看执行计划

EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card

文章图片

优化

由于是左连接，左表是主表，因此第一次尝试在左表上加索引。

create index idx_class_card on class (card);

再次执行计划

文章图片

结论：虽然type变为index，但是扫描行数依然是全表扫描。
删除左表索引，对右表创建索引。

drop index idx_class_card on class; -- 删除索引 create index idx_book_card on book (card); -- 创建索引

再次执行计划

文章图片

结果：type变为ref，rows只扫描了一行。

结论：这是由于LEFT JOIN的特性决定的，由于左表数据全有，所以关键在于如果从右表进行搜索，所以右表一定要添加索引。
三表优化案例
在双表的基础上创建一张phone表

CREATE TABLE IF NOT EXISTS `phone`( `phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, `card` INT (10) UNSIGNED NOT NULL )ENGINE = INNODB; INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20))); INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

三表均没有建立索引

EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

文章图片

结论：全表扫描，且使用了连接缓存
在phone和book表新增索引

CREATE INDEX idx_phone_card ON phone(card) CREATE INDEX idx_book_card ON book (card) EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card

【mysql优化|尚硅谷MySQL高级笔记——前篇】