MySQl 知识点
2022-02-27 12:59:00 31 举报AI智能生成
登录查看完整内容
MySQL是一种关系型数据库管理系统，它使用结构化查询语言（SQL）进行数据操作。MySQL具有高性能、稳定性强、易于使用等特点，广泛应用于各种规模的应用程序开发中。MySQL支持多种操作系统，如Windows、Linux等，同时也提供了多种客户端工具，方便用户进行管理和操作。在MySQL中，数据以表格的形式存储，每个表格由行和列组成，可以通过SQL语句对表格进行增删改查等操作。此外，MySQL还支持事务处理、存储过程、触发器等功能，能够满足复杂业务需求。总之，MySQL是一款功能强大、应用广泛的数据库管理系统，是开发人员必备的技能之一。
作者其他创作
大纲/内容
tinyint[1个字节]
smallint[2个字节]
mediumint[3个字节]
int[4个字节]
bigint[8个字节]
整型
float[4个字节]
double[8个字节]
小数型
数值类型
0-255指的是字符，char是定长的
如果存放的是一个固定大小大数据，建议使用char
查询速度上 char 要高于 varchar
char 0-255
是变长的，根据实际存储的长度
varchar 最后需要预留出 1-3 个字节用来记录存放的大小
比如 UTF8 一个字符需要存放 3个字节 ，那么最大的存储容量为 （65535-3）/3 = 21844
但是传入的数值还是表示所创建的字符数，而不是字节数。varchar(字符数)
存入的中文，还是英文，数字都占一个字符
注意，这里的最大存放的65535单位为字节，所以不同的编码最大存储的大小也不同
varchar 0-65535 [0~2^16-1]
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 [年]
日期类型
mysql数据类型
START TRANSACTION
SET AUTOCOMMIT=OFF
开始事务
SAVEPOINT 保存点名字
设置保存点
ROLLBACK TO [保存点名字]
如果，后面不加保存点名字，表示直接回退到事务开始的状态
回滚到指定保存点
commit
提交了以后就没法回滚，之前设立的保存点都将删除[所有的数据就正式生效]
提交事务
指令
如果不开启事务，默认DML语句执行以后会自动commit
如果开启了事务，没有设立保存点，启动回滚，会默认回滚到事务开始处
mysql事务机制需要innodb的存储引擎才可以使用，MyISAM无效
注意点
Mysql事务隔离级别定义了事务与事务之间的隔离程度
概念
脏读
不可重复读
幻读
会出现的问题
不加锁
读未提交(Read Uncommitted)
读已提交(Read Committed)
幻读（InnoDB除外）
可重复读（Repeatable Read）
加锁
可串行化（Serializable）
级别
oracle、SqlServer默认隔离级别
mysql默认隔离级别
当一个事务读取另一个事务尚未提交的修改时，产生脏读
同一查询在同一事务种多次进行，由于其他事务提交所作的 修改 或 删除，每次放回不同的结果集，此时发生不可重复读(比如：在一个事务中，多次执行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(可重复读)
依赖 undo log实现回滚操作
原子性
一致性
隔离性
依赖 redo log实现
持久性
事务的特性acid
子主题
MVCC（只在 RC RR中使用）
无主键，无唯一索引下的默认聚集索引
DB_ROW_ID
保存着插入或更新行的最后一个事务的事务ID，自动递增（创建版本号）
DB_TRX_ID
回滚指针（删除版本号）
记录着哪个事务将该字段删除了
DB_ROLL_PTR
Innodb为每一条记录都实现了三个隐藏字段
ReadView当前活跃的事务ID的列表
原理
事务隔离级别
概要
事务
mysql种的用户、都存在系统数据库mysql中user表中
create[ALTER] user '用户名' @'允许登陆位置' identified by ‘密码'
创建用户
drop user '用户名' @ '允许登录位置'
删除用户
SET PASSWORD = PASSWORD('密码')
修改当前用户的密码
SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD('123456')
修改其他用户的密码(需要权限)
附加权限
revoke 权限列表 on 库.对象名 from '用户名'@ '登陆位置'
在一些版本 如果命令无效 可以使用一下 FLUSH PRIVILEGES;
删除权限
权限管理
MySQL管理
同步
长连接
TCP
如果在服务端直接用 mysql 命令大部分使用的就是mysql.sock文件
151
默认
100000
最大连接数
1
最小连接数
mysql连接数
客户端和服务器的连接方式
默认关闭，8.0版本后移除
不推荐使用，如果需要缓存使用redis等其他中间件
查询缓存
语法解析树
解析器
通过了语法解析，后面就是预处理器
该阶段会检测用户权限，表、别名是否存在等
会生成一个新的解析树，让服务端去执行
预处理器
使用 json 连接两种表是，他会自动选择一张表进行先查询
假设一张表有多个索引，他也会自动选择索引
一条sql语句可能有多种执行方式，优化器会自动生成并选择一种方式去执行，同时会进行优化
优化器不是万能的，优化的结果也不是最好的，所以我们任需要对sql语句进行优化
可以通过在 SQL 语句前加 explain 来查看
最后优化器会生成一个执行计划
优化器(MySQL中使用的是CBO - Cost Base Optimizer)
根据执行计划去执行SQL语句
执行器
执行查询语句的内部流程
从磁盘中加载数据到buffer pool里
然后server 对其进行修改
记录 undo log
记录 redo log
写回 buffer pool
写入 Bin log
线程不定期的将 buffer pool 中的内容写回磁盘
执行更新(增删改)语句的内部流程
架构和内部模块
锁
mysqldump -u 用户名 -p -B 数据库1 数据库2. > 文件名.sql.
数据库备份(在DOS界面执行)
Source 文明名.sql
数据库恢复(进入mysql命令行执行)
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等 搜索引擎
全文索引
多个字段共同组合成一个索引
name
能触发索引的有
只有在查询的时候 查询条件中个有最左边的(上例子：name)索引，才会触发索引，不能跳过，不能中断
复合索引
该索引是指一类索引的特征，而不是单独只哪一种索引
MySQL中主键就是聚集索引
如果不存在主键，则默认会选择一个非空的唯一索引作为聚集索引
如果不存在主键，也不存在非空的唯一索引，那么MySQL会自动生成一个隐藏的字段 ROW 作为默认的聚集索引
聚集索引
分类
SHOW INDEX FROM 表名
SHOW INDEXES FROM 表名
SHOW KEYS FROM 表名
相比于之前的几种方式 显示的信息不全 推荐使用前面几种
DESC 表名
显示该表的所有索引
DROP INDEX 索引名 ON 表名
删除某个索引
其他命令
能够优化查询的速度
通过所有对数据进行排序，降低数据排序的成本，降低CPU消耗
优势
需要占用一定空间(这空间随着数据越多而越大)
如果进行增、删、改操作时，需要重新调整索引的，所以会影响更新表的操作
劣势
优缺点
而B+树当中一个节点除了需要保存节点索引外，还需要保存数据，这就减少了一个节点所存储的节点索引数
为什么M有SQL选择B+树？
B+树
每建立一个索引就会生成一课索引树（B+），但是只有聚集索引中的叶子节点才会记录真实的数据，其余的索引树的叶子节点都记录着聚集索引，所以最后需要回到聚集索引树重新查找真实数据，这就是所谓的回表操作
MySQL中 索引就是数据，数据就是索引
底层数据结构
索引
回表操作
为了提供不同的特性适应不同的引用场景
同时MySQL不仅提供了很多的不同特性的存储引擎，同时也开放了存储引擎的标准可以自定义存储引擎
不同的存储引擎都有相同的抽象接口，所以 MySql 可以互相切换存储引擎
MySQL的表类型由存储引擎(Storage Engines) 决定
每次会从磁盘中读取一页(16KB)到内存中操作，内存中的页又叫做脏页，这一块内存中的区域在InnoDB中又叫做 buffer pool
每隔一段时间会有另外的一个线程将内存中的数据同步到磁盘中
所以如果服务器的内存越大，留给buffer pool 的内存越多，性能越好
如果数据库崩溃，那么内存中的数据保证内存中的数据安全写入到磁盘中
当将数据写入 buffer pool 中的时候，需要同时写入日志文件（redo log）
万一发生数据库奔溃，再重启的时候需要将redo log中的数据恢复至内存中然后再刷脏
解决方案
有缓存会有一致性问题（崩溃恢复）
顺序I\\O其实不比写入内存要慢
不需要寻址的过程，可以直接循序写入
是一种顺序I/O
如果需要保证安全性，则可以提高他读写到redo log的频率
反之更看重效率，可以降低读写的频率
为了提高速度，内存中也含有一个 Log Buffer的机制
redo log
由于操作系统的一页是4KB，所以 MySQL 需要通过多词写入才能完全写入，如果其中有一次失败，则当前Mysql的页就被破坏了，所以MySQL 就会写两份，以便于备份
双写缓冲
为了提示性能缓解磁盘读取速度慢预读取机制(局部性读取原理)
InnoDB
事务安全型
方便视剧的迁移
CSV
Memory
多用于历史数据的存储
ARCHIVE
MRG_MYISAM
更多适用于博客，文章管理
支持全文索引
不支持事务
MYISAM
非事务安全型
数据表类型
SHOW ENGINES
查看所有的存储引擎
表类型和存储引擎
SHOW global STATUS like 'Com_______';
查询数据库 SQL命令类型的使用次数
SHOW global STATUS like 'Innodb_rows%';
查看Innodb的命令次数
查看SQL执行频率
show PROCESSLIST
可以实时的查询到 每一个客户端正在执行的慢查询 SQL
定位低效率执行SQL
通过ID这一列来查看 查询语句中表结构的加载顺序
ID越大，优先级越高
id
最简单的Select查询，查询中不包含子查询或者UNION
SMPLE
查询中若包含任何复杂的子查询，最外层查询标记为该标记
PRIMARY
在SELECT 或 WHERE 列表中包含了子查询
SUBQUERY
在FROM列表中包含的子查询，被标记为DERIVED（衍生）MYSQL会递归执行这些子查询，把结果放在临时表中
DERIVED
若第二个SELECT出现在UNION之后，则标记为UNION；若UNION 包含在FROM子句的子查询中，外层SELECT将标记为：DERIVED
UNION
从UNION表获取结果的SELECT
UNION RESULT
select_type
当前锁查询的数据来自于哪一张表
table
显示访问类型
不访问任何表，索引，直接放回结果
NULL
表只有一行记录(等于系统表)，属于const类型的特例，一般不会出现
system
通过索引一次就找到了，const用于比较primary key 或者 unque 索引，因为只匹配一条记录
const
使用主键的关联查询，查询出的记录只有一条
eq_ref
根据非唯一索引，放回的数据可能有多条
ref
range
遍历整个索引树
index
将遍历全表以找到匹配的行
all
参数
type
越往下效率越低，通常情况下，我们只要达到，ref、range 就可以了
可能用到的索引
显示可能应用在这张表的索引，一个或多个
possible_keys
实际用到的索引
实际使用的索引，如果为NULL，则没有使用索引
key
索引的长度
表示索引中使用的字节数，该值为索引字段最大可能长度，而非实际使用长度，再不损失精确性的前提下、长度越短越好
key_len
在查询字段时，扫描的行数
rows
其他的额外的执行计划信息
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”
using filesort
使用了临时表保存中间结果，MySQL在对查询结果排序的时候使用临时表。常见于order by 和 group by
using temporary
表示SELECT 操作使用了索引，避免访问表的数据行，效率不错
using index
需要在存储引擎层进行过滤
using where
索引下推ICP
using index Condition
extra
比如：explain select title from article order by title对文件进行了排序 就需要去扫描该字段文件
优化，对title 添加索引
如果出现了前面两个，就需要优化
简单地说最好存储引擎层的数据到了Server层不需要再次过滤 100%
存储引擎层放回给Server层的数据与最终Server层的数据百分比
filtered
explain
执行效率，越往下越低
show profile 分析SQL
对于复合索引，在查询条件时，必须添加上在创建是的最左边的索引，并且他们之前不能跳远，否则不会走复合索引
最左前缀法则
对于复合索引，范围条件，右边的列不能使用索引
不要在索引列上进行运算、函数表达式等操作，索引将失效
原因，MySQL底层会 没加字符串的值进行隐式的类型转换，这一步骤也就等价于运算操作了
字符串不加 双引号 会将索引失效
说是查询，但是其实在索引中记录了表中对应记录的地址
回表查询：拿到了索引的数据再去表中查询，一整行的数据
在 Extra 中出现了 Using index condition，代表进行了回表的查询
才 SELECT 后面尽量查询的都是包含索引的列，避免回表查询
尽量使用覆盖索引，避免使用SELECT *
如果使用索引的条件查询，避免后面使用or，否则索引失效
解决：使用覆盖索引，SELECT 查询的字段都采用 索引
模糊匹配%加在前面，索引将会失效
如果全表扫描更快的话，就不会走索引，即使这个字段添加有索引
如果当前索引字段基本都是非空 使用了is NOT NUll 它会认为全表扫描效率更高就不会走索引，is NULL 也是同理
in 走索引， not in 索引失效
尽量使用复合索引，而少使用单列索引
索引优化
因为 索引的底层是采用B+树实现的，需要进行一个排序，所以顺序会更加的块
对于同一InnoDB类型表，通过主键顺序进行导入的速度更快
SET UNIQUE_CHECKS=0
SET UNIQUE_CHECKS=1 在导入完成后重新开启
在插入数据时，关闭唯一性校验
SET AUTOCOMMIT = 0
SET AUTOCOMMIT = 1 重新开启
再导入前，关闭自动提交事务
手动提交事务
大量数据导入优化
例如：
如果对一张表要插入很多行数据时，应尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率变高
事务建议使用手动提交
根据主键的顺序进行提交
insert语句优化
查询的字段尽量是覆盖索引(即：SELECT 后面的字段都是有索引的字段)
如果有多字段排序，尽量保持一致排序顺序，即要么都是升序，要么都是降序
order by 后面字段的顺序，尽量与索引的字段顺序相同
order by语句优化
如果仅仅只是需要分组，可以通过在后面order by null不进行排序
group by底层会进行order by操作，从而可能拖慢分组的效率
在排序时，会使用到临时表 using temporary，可以通过为分组字段添加索引来达到优化的目的
group by语句优化
尽量少使用嵌套查询，多用JOIN来代替
嵌套查询的优化
尽量使 or 所关联的字段都有索引
使用 or 时不会走 复合索引
可以使用 union 来代替 or
or 的优化
先利用主键索引字段进行排序，然后利用查询回来的id进行回表操作。
优化思路
这里只适用于主键自增的情况，并且不能出现带ID不能出现断层，也就是不能中途一些ID被删除了
select * from tb_name where id > 2000000 limit 10;
分页查询的优化操作
SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中假日一些人为的提示来达到优化操作的目的
目的是建议MYSQL 在查询时使用那个索引
USE INDEX
忽略某个索引建议
IGNORE INDEX
强制使用某个索引，即使它效率比较低也需要使用他
FORCE INDEX
方式
使用SQL提示
SQL语句优化
生产环境中无法避免出现FileSort的，可以通过sort_buffer_size 和 max_length_for_sort_data系统变量，来增大排序区的大小，提高排序效率
SQL优化
无大小限制，内容可以追加
存储内容为 SQL 执行语句
数据恢复
主从复制
作用
bin log
中继日志
relay log
server层
undo log
redolog
存储引擎层
日志
mysql
0 条评论
回复 删除
下一页
