存储引擎
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的行级锁升级为表锁,降低并发。