MySQl 知识点

2022-02-27 12:59:00   34  举报





AI智能生成

MySQL是一种关系型数据库管理系统，它使用结构化查询语言（SQL）进行数据操作。MySQL具有高性能、稳定性强、易于使用等特点，广泛应用于各种规模的应用程序开发中。MySQL支持多种操作系统，如Windows、Linux等，同时也提供了多种客户端工具，方便用户进行管理和操作。在MySQL中，数据以表格的形式存储，每个表格由行和列组成，可以通过SQL语句对表格进行增删改查等操作。此外，MySQL还支持事务处理、存储过程、触发器等功能，能够满足复杂业务需求。总之，MySQL是一款功能强大、应用广泛的数据库管理系统，是开发人员必备的技能之一。

MySQL进阶

MySQL的内部组件结构

MySQL

MySQL面试

数据库

作者其他创作

大纲/内容

mysql数据类型

数值类型

整型

tinyint[1个字节]

smallint[2个字节]

mediumint[3个字节]

int[4个字节]

bigint[8个字节]

小数型

float[4个字节]

double[8个字节]

decimal[M,D](大小不确定)

M:整数部分长度(默认为 10,最大为65)，D:小数点长度(默认为0，最大为30)

文本类型(字符串类型)

char 0-255

0-255指的是字符，char是定长的

如果存放的是一个固定大小大数据，建议使用char

查询速度上 char 要高于 varchar

varchar 0-65535 [0~2^16-1]

是变长的，根据实际存储的长度

varchar 最后需要预留出 1-3 个字节用来记录存放的大小

注意，这里的最大存放的65535单位为字节，所以不同的编码最大存储的大小也不同

比如 UTF8 一个字符需要存放 3个字节，那么最大的存储容量为（65535-3）/3 = 21844

但是传入的数值还是表示所创建的字符数，而不是字节数。varchar(字符数)

存入的中文，还是英文，数字都占一个字符

text 0~2^16-1

longtext 0~2^32-1

二进制数据类型

blob [0~2^16-1]

longblob[0~2^32-1]

日期类型

date [年月日]

time [时分秒]

datetime[年月日时分秒 YYY-MM-DD HH:mm:ss]

timestamp [时间戳]

year [年]

事务

指令

开始事务

START TRANSACTION

SET AUTOCOMMIT=OFF

设置保存点

SAVEPOINT 保存点名字

回滚到指定保存点

ROLLBACK TO [保存点名字]

如果，后面不加保存点名字，表示直接回退到事务开始的状态

提交事务

commit

提交了以后就没法回滚，之前设立的保存点都将删除[所有的数据就正式生效]

注意点

如果不开启事务，默认DML语句执行以后会自动commit

如果开启了事务，没有设立保存点，启动回滚，会默认回滚到事务开始处

mysql事务机制需要innodb的存储引擎才可以使用，MyISAM无效

事务隔离级别

概念

Mysql事务隔离级别定义了事务与事务之间的隔离程度

级别

读未提交(Read Uncommitted)

会出现的问题

脏读

不可重复读

幻读

不加锁

读已提交(Read Committed)

会出现的问题

不可重复读

幻读

不加锁

可重复读（Repeatable Read）

会出现的问题

幻读（InnoDB除外）

不加锁

可串行化（Serializable）

加锁

不考虑隔离性，可能会引发的问题

脏读

当一个事务读取另一个事务尚未提交的修改时，产生脏读

不可重复读

同一查询在同一事务种多次进行，由于其他事务提交所作的修改或删除，每次放回不同的结果集，此时发生不可重复读
(比如：在一个事务中，多次执行SELECT * FROM user; 应该放回的数据是相同的，但是由于在这过程中其他事务对表进行了就该或删除，导致本事务产生了不同的查询结果)

幻读

同一查询在同一事务种多次进行，由于其他事务提交所作的插入，每次放回不同的结果集，此时发生不可重复读

命令

查看当前会话隔离级别

SELECT @@tx_isolation;

查看系统当前隔离级别

SELECT @@global.tx_isolation;

设置当前会话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED(隔离级别)]

设置当前系统隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED(隔离级别)]

mysql 默认的事务隔离级别是 REPEATABLE READ(可重复读)

事务的特性acid

原子性

依赖 undo log
实现回滚操作

一致性

隔离性

持久性

依赖 redo log实现

原理

MVCC
（只在 RC RR中使用）

子主题

Innodb为每一条记录都实现了三个隐藏字段

DB_ROW_ID

无主键，无唯一索引下的默认聚集索引

DB_TRX_ID

保存着插入或更新行的最后一个事务的事务ID，自动递增（创建版本号）

DB_ROLL_PTR

回滚指针（删除版本号）

记录着哪个事务将该字段删除了

ReadView
当前活跃的事务ID的列表

MySQL管理

mysql种的用户、都存在系统数据库mysql中user表中

命令

创建用户

create[ALTER] user '用户名' @'允许登陆位置' identified by ‘密码'

删除用户

drop user '用户名' @ '允许登录位置'

修改当前用户的密码

SET PASSWORD = PASSWORD('密码')

修改其他用户的密码(需要权限)

SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD('123456')

权限管理

附加权限

grant 权限列表[SELECT,DELETE,CREATE,ALL] on 库.对象[*.*代表全部库的所有数据对象] to ‘用户名’@ '登陆位置' [identified by '密码']

删除权限

revoke 权限列表 on 库.对象名 from '用户名'@ '登陆位置'

在一些版本如果命令无效可以使用一下 FLUSH PRIVILEGES;

架构和内部模块

执行查询语句的内部流程

客户端和服务器的连接方式

TCP

长连接

同步

如果在服务端直接用 mysql 命令大部分使用的就是mysql.sock文件

mysql连接数

默认

151

最大连接数

100000

最小连接数

查询缓存

默认关闭，8.0版本后移除

不推荐使用，如果需要缓存使用redis等其他中间件

解析器

语法解析树

预处理器

通过了语法解析，后面就是预处理器

该阶段会检测用户权限，表、别名是否存在等

会生成一个新的解析树，让服务端去执行

优化器
(MySQL中使用的是CBO - Cost Base Optimizer)

一条sql语句可能有多种执行方式，优化器会自动生成并选择一种方式去执行，同时会进行优化

使用 json 连接两种表是，他会自动选择一张表进行先查询

假设一张表有多个索引，他也会自动选择索引

优化器不是万能的，优化的结果也不是最好的，所以我们任需要对sql语句进行优化

最后优化器会生成一个执行计划

可以通过在 SQL 语句前加 explain 来查看

执行器

根据执行计划去执行SQL语句

执行更新(增删改)语句的内部流程

从磁盘中加载数据到buffer pool里

然后server 对其进行修改

记录 undo log

记录 redo log

写回 buffer pool

写入 Bin log

线程不定期的将 buffer pool 中的内容写回磁盘

锁

常用命令

数据库备份(在DOS界面执行)

mysqldump -u 用户名 -p -B 数据库1 数据库2. > 文件名.sql.

数据库恢复(进入mysql命令行执行)

Source 文明名.sql

use [databasename]

索引

分类

主键索引

ALTER TABLE 表名 ADD PRIMARY KEY（列名）;

删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY

唯一索引

CREATE UNIQUE INDEX 索引名字 ON 表名 (列名);

ALTER TABLE 表名 ADD INDEX 索引名字（列名）;

普通索引

CREATE INDEX 索引名字 ON 表名(id);

全文索引

不建议使用，如果有此需求，可以使用ES等搜索引擎

复合索引

多个字段共同组合成一个索引

CREATE INDEX idx_name_eamil_status ON tb_seller(name,email,status);

能触发索引的有

name

name,email

name,email,status

只有在查询的时候查询条件中个有最左边的(上例子：name)索引，才会触发索引，不能跳过，不能中断

聚集索引

该索引是指一类索引的特征，而不是单独只哪一种索引

MySQL中主键就是聚集索引

如果不存在主键，则默认会选择一个非空的唯一索引作为聚集索引

如果不存在主键，也不存在非空的唯一索引，那么MySQL会自动生成一个隐藏的字段 ROW 作为默认的聚集索引

其他命令

显示该表的所有索引

SHOW INDEX FROM 表名

SHOW INDEXES FROM 表名

SHOW KEYS FROM 表名

DESC 表名

相比于之前的几种方式显示的信息不全推荐使用前面几种

删除某个索引

DROP INDEX 索引名 ON 表名

优缺点

优势

能够优化查询的速度

通过所有对数据进行排序，降低数据排序的成本，降低CPU消耗

劣势

需要占用一定空间(这空间随着数据越多而越大)

如果进行增、删、改操作时，需要重新调整索引的，所以会影响更新表的操作

底层数据结构

B+树

为什么M有SQL选择B+树？

因为MySQL查找操作时，需要读取磁盘中一页到内存(16KB)中操作,一次IO操作时非常消耗性能的，所以一次取我们要取出尽可能多的索引，而B+树除了叶子节点以外，其他节点都仅存索引的，这样可以更有利减少磁盘IO的次数

而B+树当中一个节点除了需要保存节点索引外，还需要保存数据，这就减少了一个节点所存储的节点索引数

MySQL中索引就是数据，数据就是索引

每建立一个索引就会生成一课索引树（B+），但是只有聚集索引中的叶子节点才会记录真实的数据，其余的索引树的叶子节点都记录着聚集索引，所以最后需要回到聚集索引树重新查找真实数据，这就是所谓的回表操作

表类型和存储引擎

MySQL的表类型由存储引擎(Storage Engines) 决定

为了提供不同的特性适应不同的引用场景

同时MySQL不仅提供了很多的不同特性的存储引擎，同时也开放了存储引擎的标准可以自定义存储引擎

不同的存储引擎都有相同的抽象接口，所以 MySql 可以互相切换存储引擎

数据表类型

事务安全型

InnoDB

默认

为了提示性能缓解磁盘读取速度慢
预读取机制(局部性读取原理)

每次会从磁盘中读取一页(16KB)到内存中操作，内存中的页又叫做脏页，这一块内存中的区域在InnoDB中又叫做 buffer pool

每隔一段时间会有另外的一个线程将内存中的数据同步到磁盘中

所以如果服务器的内存越大，留给buffer pool 的内存越多，性能越好

有缓存会有一致性问题
（崩溃恢复）

如果数据库崩溃，那么内存中的数据保证内存中的数据安全写入到磁盘中

解决方案

当将数据写入 buffer pool 中的时候，需要同时写入日志文件（redo log）

万一发生数据库奔溃，再重启的时候需要将redo log中的数据恢复至内存中然后再刷脏

redo log

是一种顺序I/O

顺序I\O其实不比写入内存要慢

不需要寻址的过程，可以直接循序写入

为了提高速度，内存中也含有一个 Log Buffer的机制

如果需要保证安全性，则可以提高他读写到redo log的频率

反之更看重效率，可以降低读写的频率

双写缓冲

由于操作系统的一页是4KB，所以 MySQL 需要通过多词写入才能完全写入，如果其中有一次失败，则当前Mysql的页就被破坏了，所以MySQL 就会写两份，以便于备份

非事务安全型

CSV

方便视剧的迁移

Memory

ARCHIVE

多用于历史数据的存储

MRG_MYISAM

MYISAM

更多适用于博客，文章管理

支持全文索引

不支持事务

查看所有的存储引擎

SHOW ENGINES

SQL优化

查看SQL执行频率

查询数据库 SQL命令类型的使用次数

SHOW global STATUS like 'Com_______';

查看Innodb的命令次数

SHOW global STATUS like 'Innodb_rows%';

定位低效率执行SQL

可以实时的查询到每一个客户端正在执行的慢查询 SQL

show PROCESSLIST

explain

通过ID这一列来查看查询语句中表结构的加载顺序

ID越大，优先级越高

select_type

SMPLE

最简单的Select查询，查询中不包含子查询或者UNION

PRIMARY

查询中若包含任何复杂的子查询，最外层查询标记为该标记

SUBQUERY

在SELECT 或 WHERE 列表中包含了子查询

DERIVED

在FROM列表中包含的子查询，被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表中

UNION

若第二个SELECT出现在UNION之后，则标记为UNION；
若UNION 包含在FROM子句的子查询中，外层SELECT将标记为：DERIVED

UNION RESULT

从UNION表获取结果的SELECT

table

当前锁查询的数据来自于哪一张表

type

显示访问类型

参数

NULL

不访问任何表，索引，直接放回结果

system

表只有一行记录(等于系统表)，属于const类型的特例，一般不会出现

const

通过索引一次就找到了，const用于比较primary key 或者 unque 索引，因为只匹配一条记录

eq_ref

使用主键的关联查询，查询出的记录只有一条

ref

根据非唯一索引，放回的数据可能有多条

range

范围查询，where之后出现 between，<,>,in等操作

index

遍历整个索引树

all

将遍历全表以找到匹配的行

key

possible_keys

可能用到的索引

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

key

实际用到的索引

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

key_len

索引的长度

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

rows

在查询字段时，扫描的行数

extra

其他的额外的执行计划信息

参数

using filesort

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”

using temporary

使用了临时表保存中间结果，MySQL在对查询结果排序的时候使用临时表。常见于order by 和 group by

using index

表示SELECT 操作使用了索引，避免访问表的数据行，效率不错

using where

需要在存储引擎层进行过滤

using index Condition

索引下推ICP

filtered

存储引擎层放回给Server层的数据与最终Server层的数据百分比

简单地说最好存储引擎层的数据到了Server层不需要再次过滤 100%

show profile 分析SQL

索引优化

最左前缀法则

对于复合索引，在查询条件时，必须添加上在创建是的最左边的索引，并且他们之前不能跳远，否则不会走复合索引

对于复合索引，范围条件，右边的列不能使用索引

不要在索引列上进行运算、函数表达式等操作，索引将失效

字符串不加双引号会将索引失效

原因，MySQL底层会没加字符串的值进行隐式的类型转换，这一步骤也就等价于运算操作了

尽量使用覆盖索引，避免使用SELECT *

才 SELECT 后面尽量查询的都是包含索引的列，避免回表查询

回表查询：拿到了索引的数据再去表中查询，一整行的数据

说是查询，但是其实在索引中记录了表中对应记录的地址

在 Extra 中出现了 Using index condition，代表进行了回表的查询

如果使用索引的条件查询，避免后面使用or，否则索引失效

模糊匹配%加在前面，索引将会失效

解决：使用覆盖索引，SELECT 查询的字段都采用索引

如果全表扫描更快的话，就不会走索引，即使这个字段添加有索引

is NULL , is NOT NULL 有时会索引失效

如果当前索引字段基本都是非空使用了is NOT NUll 它会认为全表扫描效率更高就不会走索引，is NULL 也是同理

in 走索引， not in 索引失效

尽量使用复合索引，而少使用单列索引

SQL语句优化

大量数据导入优化

对于同一InnoDB类型表，通过主键顺序进行导入的速度更快

因为索引的底层是采用B+树实现的，需要进行一个排序，所以顺序会更加的块

在插入数据时，关闭唯一性校验

SET UNIQUE_CHECKS=0

SET UNIQUE_CHECKS=1 在导入完成后重新开启

手动提交事务

再导入前，关闭自动提交事务

SET AUTOCOMMIT = 0

SET AUTOCOMMIT = 1 重新开启

insert语句优化

如果对一张表要插入很多行数据时，应尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率变高

例如：

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Jack');
insert into tb_test values(3,'Cat');

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

事务建议使用手动提交

根据主键的顺序进行提交

order by语句优化

查询的字段尽量是覆盖索引(即：SELECT 后面的字段都是有索引的字段)

如果有多字段排序，尽量保持一致排序顺序，即要么都是升序，要么都是降序

order by 后面字段的顺序，尽量与索引的字段顺序相同

group by语句优化

group by底层会进行order by操作，从而可能拖慢分组的效率

如果仅仅只是需要分组，
可以通过在后面order by null不进行排序

在排序时，会使用到临时表 using temporary，可以通过为分组字段添加索引来达到优化的目的

嵌套查询的优化

尽量少使用嵌套查询，多用JOIN来代替

or 的优化

尽量使 or 所关联的字段都有索引

使用 or 时不会走复合索引

可以使用 union 来代替 or

分页查询的优化操作

当使用 select * from tb_name limit 2000000, 10; 时，效率会很低。
原因是因为我们需要对前面20000000条数据进行一个排序的操作，然后取最后10条

优化思路

select * from tb_name t,(select id from tb_name order by id) a where t.id = a.id;

先利用主键索引字段进行排序，然后利用查询回来的id进行回表操作。

优化思路

select * from tb_name where id > 2000000 limit 10;

这里只适用于主键自增的情况，并且不能出现带ID不能出现断层，也就是不能中途一些ID被删除了

使用SQL提示

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

方式

USE INDEX

目的是建议MYSQL 在查询时使用那个索引

SELECT * FOM tb_name,use index(index_name) where name = '拉拉';

IGNORE INDEX

忽略某个索引建议

SELECT * FOM tb_name,ignore index(index_name) where name = '拉拉';

FORCE INDEX

强制使用某个索引，即使它效率比较低也需要使用他

SELECT * FOM tb_name,force index(index_name) where name = '拉拉';

日志

server层

bin log

无大小限制，内容可以追加

存储内容为 SQL 执行语句

作用

数据恢复

主从复制

relay log

中继日志

存储引擎层

undo log

redolog

 收藏

立即使用

Java集合

 收藏

立即使用

MySQl 知识点

 收藏

立即使用

JUC

 收藏

立即使用

JVM

加班小子

职业：Java开发工程师

去主页





0 条评论

下一页

为你推荐

查看更多

