MySQL进阶知识

目录

MySQL的Linux安装

存储引擎

MySQL的体系结构

存储引擎简介

存储引擎特点

InnoDB

逻辑存储结构

MyISAM

Memory

对比

存储引擎选择

索引

介绍

索引结构

B+Tree索引

Hash索引

索引分类

索引语法

SQL性能分析

SQL执行频率

慢查询日志

profile详情

explain执行计划

索引的使用

最左前缀法则

范围查询

索引列运算

字符串加引号

模糊查询

or连接的条件

数据分布影响

SQL提示

覆盖索引

MySQL的Linux安装

在MySQL官网下载压缩包MySQL :: Download MySQL Community Server (Archived Versions)

下载完成后将压缩包上传到Linux中。我这里是下的CentOS的压缩包，并且用的是FinalShell工具

可以选择压缩包直接上传。

上传完毕后，新建mysql文件夹，并解压到该文件夹中

mkdir mysql#切换成你的mysql版本
#-c是指定解压文件夹
tar -xvf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C mysql

解压完成后，进入mysql目录，安装mysql

cd mysqlrpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpmyum install openssl-devel# 如果执行该命令报错，请先执行 yum install 的命令
rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpmrpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpmrpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm

启动MySQL

systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld

启动后，我们需要查看默认生成的Mysql密码是什么

grep 'temporary password' /var/log/mysqld.log

然后登录MySQL并修改密码

需要注意的是，MySQL8之后添加了密码校验器，默认的密码等级是中等，需要密码长度不低于8位，并且需要数字与字母搭配。具体文档6.4.3.2 密码验证选项和变量_MySQL 8.0 参考手册

登录过后，我们需要对密码校验器进行修改

set global validate_password.policy = 0;
set global validate_password.length = 6;
#修改完成后，设置新密码
ALTER  USER  'root'@'localhost'  IDENTIFIED BY '123456';

接下来创建一个新用户，可以用于远程连接

create user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
#分配权限
grant all on *.* to 'root'@'%';

之后就可以使用Navicat连接Linux中的MySQL了。

存储引擎

MySQL的体系结构

连接层：最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SOL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如 过程、函数等。

引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

存储层：主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引警是基于表的，而不是基于库的，所以存储引警也可被称为表类型。

在创建表的时候我们可以指定存储引擎，如果不指定默认为INNODB引擎

create table 表明{

字段1, 字段类型,

……

}ENGINE = 引擎名;

如果想查看当前数据库支持哪些存储引擎，我们可以使用语句

show engines;

存储引擎特点

InnoDB

一种兼顾高可靠性与高性能的通用引擎，在Mysql5.5之后，InnoDB是MySQL的默认存储引擎。

特点是：

• DML操作遵循ACID模型，支持事务;
• 支持行级锁，提高并发访问性能；
• 支持外键约束，保证数据的完整性和正确性；

存储文件：

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构(frm、sdi)、数据和索引。该文件可以在Windows目录下C:\ProgramData\MySQL\MySQL Server 8.0\Data下找到，每个文件夹对应的是一个数据库。

如果需要通过idb文件查看表结构，可以通过命令

ibd2sdi 表名.ibd

逻辑存储结构

磁盘操作的最小单元为page，每个page是16K。而每个Extent大小为1M。也就是说，一个Extent包含64个page

MyISAM

MyISAM是MySQL早期的默认存储引擎

特点：

• 不支持事务，不支持外键
• 支持表锁，不支持行锁
• 访问速度快

存储文件：

• xxx.sdi：存储表结构信息
• xxx.MYD：存储数据
• xxx.MYI：存储索引

Memory

Memory引擎的表数据是存储在内存当中的，只能将使用Memory引擎的表作为临时表或缓存使用

特点：

• 内存存放，访问速度快
• 采用hash索引

存储文件：

• xxx.sdi：存储表结构信息

对比

InnoDB与MyISAM最大的区别在于，InnoDB支持事务，支持行锁，支持外键

存储引擎选择

InnoDB：是Mysql的默认存储擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。

MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

通常来讲，我们使用InnoDB就足够了，MyISAM引擎用来记录日志等丢失几条消息也无所谓的信息，MEMORY因为无法存储太多数据，用来做缓存就可以了。但是MyISAM与MEMORY都可以被其他非关系型数据库来替代，比如说Redis。

索引

介绍

索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

比如说存在这样一张表，接下来我们要找到age为45的用户，在没有对age字段添加索引的清空下，是通过id进行全表扫描，一行一行对比age=45的数据。

如果对age字段建立索引，在插入时，数据库会维护索引信息，查找时从根节点开始查找，查询速度快。

优点：

• 提高数据库查询效率，减少数据库IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗

缺点：

• 索引会占用存储空间
• 索引大大提高了查询效率，同时也降低了Insert、Update、Delete效率

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种：

引擎对索引结构的支持情况

B+Tree索引

默认都是使用B+Tree索引，但是MySQL又对经典的B+Tree做了一个优化，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree，提高区间访问的性能。

为什么选择B+Tree索引结构？

相对于二叉树，层级更少，搜索效率高。

对于B-Tree，无论是叶子节点还是非叶子节点都存储数据，这样会导致一页中的存储的键值减少，指针跟着减少，如果需要保存大量数据，B-Tree的高度会比B+Tree高导致性能降低。其次，对B+Tree优化后，形成一个双线链表，对于范围查询更具有优势。

Hash索引

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中

hash索引的特点

• 只能用于对等比较（=、in），不支持范围查询（between，>,<等）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索。

索引分类

在InnoDB中根据索引的存储形式又可以分为以下两种

聚集索引的选取规则：

• 如果存在主键，主键索引就是聚集索引。
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

当需要对name字段进行查询时，先通过二级索引查找到对应的name键值，然后获取对应的id值，然后拿着id值取聚集索引中查找行信息，该操作叫做回表查询

InnoDB的主键索引B+Tree高度为多少？

B+Tree的高度，取决于主键数据类型，首先我们知道，B+Tree的非叶子节点是存储的只有键值与InnoDB的指针，而一个区块只能存放16K的内容，转化为字节为16*1024个字节。

首先InnoDB的指针占用6个字节，假设主键数据类型是bigint，需要占用8个字节的大小，在高度为2的情况下，计算公式为：

n*8+(n+1)*6 = 16*1024

计算出n约等于1170。

如果一行数据大小为1k。那么在高度为2的情况下可以存储 1171*16 =18736 数据。

在高度为三的情况，可以存储1171*1171*16 = 21939856 条数据

索引语法

创建索引

CREATE [ UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,…);

字段可以同时选取多个，这被称为联合索引或组合索引（需要注意的是，创建索引时，字段名的顺序会影响查找的效率，这个我们后面）

查看索引

SHOW　INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

SQL性能分析

SQL执行频率

在对SQL进行优化的时候，我们需要知道该数据库主要是哪些语句执行次数多，将优化重心就放在执行次数多的语句当中，查询SQL执行次数语句如下

SHOW [GLOBAL|SESSION] STATUS LIKE 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。MySQL的慢查询日志默认关闭，需要在MySQL的配置文件（etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志开关
slow_query_log = 1
# 设置慢日志的超时时间为2s
long_query_time = 2

慢日志记录位置为/var/lib/mysql/localhost-slow.log

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have profiling参数，能够看到当前MySQL是否支持profile操作:

SELECT @@have_profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:

SET profiling = 1;

当开启之后执行

# 会查看到每一条SQL的执行时间
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id;

explain执行计划

EXPLAIN或者DESC命令可以获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中如何连接和连接顺序。

语法：

# 在select语句前直接加EXPLAIN或是DESC
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

字段解释

id

select查询的序列号，表示查询中执行select子句或是操作表的顺序，执行顺序从上到下（id相同，从上到下，id不同，值越大越先执行）

select_type

表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等

type

表示连接类型，性能由好到差的连接类型为NULL、system、const、eg_ref、ref、range、index、all。

• NULL：不查询表的时候性能为NULL，项目中不可能优化到NULL
• system：查询系统表的时候为system
• const：查询主键或唯一索引时为const
• ref：查询非唯一性的索引为ref
• index：使用了索引，但还是遍历了整个索引树
• all：全表扫描

possible_key

显示可能应用在这张表上的索引，一个或多个

key

表示实际使用的索引，如果为NULL，则没有使用索引。

key_len

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好

rows

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好

extra

额外信息

索引的使用

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效)[索引字段存在即可，在语句中的位置顺序不重要]

比如说：将一张表的name，age，sex字段创建一个联合索引，那么在查找时，name字段必须存在查询条件中，如果不包含name字段，只查询age与sex字段，那么将会全表扫描。如果查询name与sex字段，那么只有name字段会走索引，而sex字段的索引失效，因为跳过了age字段。

范围查询

联合索引中，出现范围查询（>，<），范围查询右侧的索引失效。

比如说，在查询条件中，加入的age>18的条件，那么sex的索引将会失效。

解决方法：在业务允许的情况下，能够使用>=或<=的情况，不要使用>和<。

索引列运算

不要在索引列上进行运算操作，索引将失效。

字符串加引号

如果字符串类型的字段在使用时，不添加引号，那么索引失效。

模糊查询

如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，那么索引失效。

or连接的条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

解决方法：对没有索引的字符也添加索引。

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

比如说age>=18，如果表中大多数数据都满足这个条件，那么即使age字段存在索引，那么也不会使用。

当age>=18这个条件表中大多数数据不满足时，才会走索引。

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

比如说，name字段存在单列索引，也和age、sex存在联合索引，那么在只查询name字段时，可能会走联合索引。此时我们可以人为干预name字段走单例索引。

使用语法

# use index 在搜索时使用该索引
explain select * from 表名 use index(索引名) where ……;
# ignore index 在搜索时不使用该索引名
explain select * from 表名 ignore index(索引名) where ……;
# force index 强制使用该索引
explain select * from 表名 force index(索引名) where ……;

覆盖索引

尽量使用覆盖索引（查询中使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *的使用。

比如说：name与age字段建立了索引在查找语句时where里对name与age进行条件查询，在返回的字段中只填写name、age字段那么在使用explain查看SQL执行计划时，extra字段的信息显示的时using where;using index（可能会随着MySQL版本不同而显示不同）。

如果返回的字段为name、age与sex字段，但是没有在where里使用sex字段，即使sex建立了索引extra显示的信息也为using index condition。

• using where;using index：使用了索引，但是需要的数据在索引列中可以找到，不需要进行回表查询
• using index condition：使用了索引，但是需要回表查询