MySQL进阶

存储引擎

MySQL体系结构

连接层

服务层

引擎层       不同存储引擎会有不同的索引结构

存储层

存储引擎简介

存储数据，建立索引，更新和查询数据等技术的实现方式。

 查看建表语句：show create table 表名;

mysql5.5版本之后，默认的存储引擎是InnoDB

在创建表的时候指定存储引擎

 innodb引擎支持事务，行级锁，外键。myIsam是MySQL早期版本的默认存储引擎。memory引擎存储的数据在内存条中，通常用来做临时表。

存储引擎特点

 show variables like 'innodb_file_per_table';

如果这个是打开的，就代表每一张innodb表对应一个存储文件（.ibd），如果是关闭的，就代表共享一个文件。

ibd文件是一个二进制的文件，需要使用cmd进入黑窗口输入ibd2sdi 文件名.ibd可以查看表结构。 

page是innodb操作的最小单元，包括索引page和数据page。

myISAM不支持事务，不支持外键，支持表锁，不支持行级锁，访问速度快。涉及到的文件有三个： xxx.MYD存放的是数据         xxx.MYI存放的是索引                xxx.sdi存放的是表结构的信息

 .sdi存放的是json数据，可以在json.cn网站进行格式化，方便查看。

memory引擎文件只有.sdi文件，因为数据和索引都在内存中存储。访问数据速度很快。hash索引。

三者的区别：

常见的面试题：

myisam和innodb 的区别？

innodb支持行级锁，支持事务，支持外键。

myisam不支持行级锁，只支持表锁，不支持事务，不支持外键。 

存储引擎选择

根据不同的需求选择不同的存储引擎。

平时用的最多的是innidb，myisam被非关系型数据库（nosql）mongodb替代，memory被非关系型数据库redis替代

在linux上安装MySQL

1.准备一台linux远程服务器

2.下载linux版本的MySQL安装包（学习使用的是8.0.26）

 3.将下载好的安装包传到linux服务器

如果使用finalshell远程访问linux工具可以在底栏找到上传选项直接上传本地文件到linux服务器

4.创建目录，并解压

mkdir mysql

tar -xvf mysql.rpm-bundle.tar -C mysql

解压出来9个安装包.rpm格式

5.安装mysql

rpm安装包安装是按照一定顺序的，因为rpm安装包不会解决安装过程产生的依赖问题

yum install openssl-devel 在线安装这个插件，y确定安装。

6.启动MySQL服务

systemctl start mysqld

7.连接MySQL服务

mysql -uroot -p

需要输入密码，因为没有设置密码，所以去MySQL的日志中找到临时密码登录

另开一个linux远程访问连接

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

可选择cat /var/log/mysqld.log或vi /var/log/mysqld.log进行查看

将临时密码复制过来，即可登录

8.修改root用户密码

alter user 'root'@'localhost' identified by '1234';

会报错，说密码太简单了

 我们重新设置一下密码校验的等级

set global validate_password.policy = 0;密码的校验等级设为0

set global validate_password.length= 0;--设置密码长度设为4

之后

再次执行

alter user 'root'@'localhost' identified by '1234';

即可成功

9.创建一个用户可以远程访问

create   user   'root'@'%'   identified  with  mysql_native_password  by  '1234';

10.给用户分配权限

grant  all  on  *.*  to  'root'@'%';

11.远程访问MySQL软件（datagrip）

datagrip：由JetBrains开发的数据库和SQL跨平台IDE(Integrated Development Environment  集成开发环境)

 如果连接不上，需要去linux查看防火墙有没有开放3306端口，或者关闭linux的防火墙。

索引（核心）

索引概述

 索引是一种MySQL高效获取数据的数据结构（有序）。

有索引会比没有索引查找数据更叫高效。

索引的优缺点

索引就像书的目录页，读者可以根据目录快速找到自己想要得到的信息，但是目录需要占用书本几页，如果作者对书本的内容进行增删改操作，目录页同时也要进行维护。

索引结构

索引是在MySQL的存储引擎层实现的，所以不同的存储引擎会有不同的索引结构。

 

 红黑树是自平衡的二叉树，可以解决顺序插入的问题。但是自身还是二叉树，如果数据量比较大，则树的层级较深，检索速度比较慢。

树的度数：一个节点的子节点个数。

B树：有5个度，每个节点有5个指针，有4个key用来存放数据。度数比key多一个。

这个网站是用来演示数据结构的动画：Data Structure Visualization (usfca.edu)

如果超每个节点超过key的数量，中间元素向上分裂。

B+树

非叶子节点存放指针，叶子节点存放数据。所有的key都会出现在叶子节点上。 叶子节点通过单向链表连接。

如果超每个节点超过key的数量，中间元素向上分裂，并保留一份在叶子节点。

B+树和B树的区别：

在MySQL中的B+ 树

 每个page是16KB。双向链表。

hash索引

 

面试题：为什么InnoDB采用，B+树索引结构？

索引分类

 

 

通过二级索引查找数据需要回表查询去聚集索引中查找数据。（查询时间会比直接主键查询慢）

 面试题：

索引语法

创建索引：create [unique] [fulltext]  index 索引名称 on 表名(列名,等);

查看索引：show index from 表名;

删除索引：drop index 索引名称 on 表名;

show index from 表名\G;可以在finalshell中格式化显示sql结果。

索引名称规范：idx_表名简写_列名简写

创建联合索引顺序是需要注意的。

SQL性能分析

1.SQL的执行频率（查看数据库中是select操作比较多还是更新操作多）

 2.慢查询日志（查看是那条sql语句执行超过指定的参数设置时间）

查询慢日志是否开启：show  variables like 'show_query_log';

 linux 中 vi 中的x和wq 都是保存退出，x：如果文件内容有修改，则修改文件修改日期。wq：没有修改文件中的内容也会修改文件日期。

tail -f 文件名   ：实时监控文件的内容变化

这样可以方便看到哪条SQL超时了并进行对应的优化。

3.profile 详情

查看系统是否支持profile：select @@have_profiling;

查看是否开启profile：select @@profiling; 

开启profile：set profiling = 1;

 4.explain执行计划

索引使用

创建索引后，再次查询建立索引后的列，会发现查询速度快了很多。但是创建索引需要花费一定的时间。（建立二级索引B+树索引结构）

最左前缀法则：

主要是针对联合索引，每次查询先从联合索引的最左列开始查询，并且不跳过中间列，如果跳过某一列，索引将会部分失效（后面的索引失效）。若没有最左列开始，联合索引直接失效。

 上图展示的是索引部分失效，只按照联合索引中profession的key_len进行查询。

 这样也是走联合索引的，因为MySQL会自动进行优化，where条件后只要有联合索引的开头列，则会走联合索引。MySQL会自动改变where后查询条件的位置。（联合索引使用时，与查询语句书写的位置无关，只与在创建联合索引时，列的书写顺序有关）

范围查询：

如果age使用<或者>，失效的是status列。如果加=，status就不会失效了。

索引失效情况一：

1.索引列运算会导致索引失效。

例如在索引列上使用substring(索引列phone,10,2)函数

这个代表截取手机号码，从第10位开始截取2位数字。这种经过函数运算的索引会失效。

2.字符串类型的字段不加引号也会造成索引失效。

例如手机号用varchar类型存储数字，还有status使用varchar存储数字（0或1）。

因为存在隐式类型转换所以导致索引失效。

3.模糊查询（like）

如果是头部进行模糊匹配（%张），索引会失效；

如果尾部进行模糊匹配（张%），索引不会失效。

索引失效情况二：

用or连接的条件，如果有一列不涉及索引，那么都不会用到索引匹配。

只有两侧都使用索引，才会走索引。

注意：联合索引中不是第一个索引列，而是第二个或者第三个索引列，也相当于没有索引。

例如 profession,age,status是联合索引，按照这个顺序创建的联合索引。如果没有用到profession列，同时age又没有单独的单列索引和没有以age开头的联合索引，则age单独使用没有索引。

数据分布影响：

若MySQL优化器发现走索引会比全表扫描慢，会走全表扫描。

 使用is null和is not null走不走索引是根据表中数据的分布情况，

如果索引列中数据全部不为null，使用is null 会走索引，使用is not null 会走全表扫描。

如果索引列中数据全部为null，使用is null会走全表扫描，使用is not null会走索引。

SQL提示

覆盖索引

尽量减少select *  的使用，而是指明字段名。 

 如果select 后面查询的是使用了索引，并且完全覆盖了二级索引的字段，就不需要回表查询，速度会快。

例如

联合索引profession,age,status

select id,profession,age,status from 表名 where profession = '';这个不需要回表查询，因为需要的数据全部都在二级索引中。

 面试题：

create index idx_表名_una_pas on 表名(username,password);

创建用户名和密码的联合索引。

前缀索引

主要用在大文本字段，降低索引的体积

单列索引和联合索引

 

索引设计原则

 SQL优化

insert数据优化：

大量数据的插入操作：

 查看MySQL有没有打开本地插入：select @@local_infile;

linux中的命令：wc -l 文件名  查看文件有多少行

                          head 文件名      只查看文件的前10行记录

主键优化

主键乱序插入会导致页分裂

 删除数据会导致页合并。

在满足业务需求的情况下，尽量减少主键的长度。因为二级索引中存放的数据就是 主键数据。

插入数据时，尽量选择顺序插入，选择auto_increment自增主键

尽量不要选择UUID做主键或者身份证号。因为UUID长度长，无序。

业务操作是尽量减少主键的修改。因为二级索引也要进行更改。

 

order by优化

 可以在创建索引的时候指明列的排序规则，asc升序，desc降序。

不指定就是默认升序排列。

联合索引，默认升序排列，如果orderby 位置写反了，不满足最左前缀法则，不会走index排序。

不是覆盖索引会走filesort。

show variables like 'sort_buffer_size';

group by优化

 建立了profession和age的联合索引。

limit优化

count优化

 

update优化

 update根据索引列进行更新，如果是普通列，则会造成innoDB的行级锁升级为表锁，降低并发。