MySQL
MySQL
配置环境
- D:\Environment\mysql-目录下创建my.ini，并如下配置
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\Environment\mysql-5.7.19\mysql
# 设置mysql数据库的数据的存放目录
datadir=D:\Environment\mysql-5.7.19\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#跳过连接验证密码【8.0以上版本这行配置会导致数据库启动后几秒内自动停止】
skip-grant-tables
#设置默认时区
default-time-zone='+08:00'
-
系统环境变量Path中添加D:\Environments\mysql_5.7.19\bin
-
管理员身份运行CMD
cd /d D:\Environment\mysql-5.7.19\bin %切换到SQL的bin目录下%
mysqld --initialize
%
如果此处报错 Can't create/write to file
将my.ini文件如二 中的 basedir 和 datadir 路径见引号和双斜杠
# 设置mysql的安装目录
basedir="D:\\tool\\Mysql\\mysql-5.7.26-winx64"
# 设置mysql数据库的数据的存放目录
datadir="D:\\tool\\Mysql\\mysql-5.7.26-winx64\\data"
%
mysqld --install %安装SQL%
net start mysql %启动服务%
mysql -u root -p %连续回车，不用输密码直至进入%
update mysql.user set authentication_string=password('123456') where user='root'; %更新密码%
# 8.0以后版本，首次初始化时在控制台打印日志，获取初始化密码
mysqld --initialize --console
# 通过初始密码登录后修改密码，并刷新
alter user 'root'@'localhost' identified by '123456';
flush privileges;
#新密码重新登录后修改root访问主机授权
update mysql.user set host = '%' where user ='root';
因为8.0版本后数据库安全策略改变，重置密码步骤如下
停止MySQL服务，管理员运行cmd切换到bin目录下，执行
mysqld --console --skip-grant-tables --shared-memory不关闭该窗口再开一个cmd窗口，跳过密码登录。登录清空密码【此时无法执行修改密码语句】
update mysql.user set authentication_string ='' where user='root'; update mysql.user set authentication_string ='$A$005$F0Np!NPb1t4h_`RrXO22DQ0i7n3Kwr4Tl4n8S3LSJtnoMWcBwpG907awj/' where user='root'; -- 或者使用以下，注意mysql_native_password密码插件已被官方定义为过时 update mysql.user set plugin = 'mysql_native_password' where user user='root'; update mysql.user set authentication_string ='*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' where user='root'; -- 关闭所有窗口重启服务即可使用密码：123456登录修改密码
- 注释掉my.ini文件中跳过密码的设置，然后cmd重启MySQL服务
# skip-grant-tables
net stop mysql
net start mysql %重启服务%
基础
SQL
DDL （Data Definition Language操作数据库对象【数据库，表，字段】）
-
查询数据库
- 查询所有数据库
SHOW DATABASES;- 查看当前使用的数据库
SELECT DATABASE(); -
创建数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集][COLLATE 排序规则];tips：因为MySQL
UTF-8只有三个字节，为避免特殊字符无法正常储存，一般规定数据库字符集为utf8mb4 -
删除数据库
DROP DATABASES [IF EXISTS] 数据库名; -
使用数据库
USE 数据库名; -
创建表
-
CREATE TABLE 表名( 字段1 数据类型1[COMMENT 字段1的注释]， 字段2 数据类型2[COMMENT 字段2的注释]， ... 字段3 数据类型[COMMENT 字段3的注释] )[COMMENT 表注释]; -
SQL数据类型
-
| 分类 | 数据类型 | 大小 | 描述 | 字段定义形式 |
|---|---|---|---|---|
| 数值类型 | TINYINT | 1byte | 小整数值 | |
| SMALLINT | 2bytes | 大整数值 | ||
| MEDIUMINT | 3bytes | 大整数值 | ||
| INT或INTEGER | 4bytes | 大整数值 | ||
| BIGINT | 8bytes | 极大整数值 | ||
| FLOAT | 4bytes | 单精度浮点数值 | ||
| DOUBLE | 8bytes | 双精度浮点数值 | score double(5,2)字段 double(最大值有数，保留小数位数) |
|
| DECIMAL | 小数值 | |||
| 日期和时间类型 | DATE | 3 | 日起值 | |
| TIME | 3 | 时间值或持续时间 | ||
| YEAR | 1 | 年份值 | ||
| DATETIME | 8 | 混合日期和时间值 | ||
| TIMEDTAMP | 4 | 混合日期和时间值，时间戳 | ||
| 字符串类型 | CHAR | 0 ~ 255bytes | 定长字符串 | name char(10)括号内的为限制最大空间，存储内容短于最大长度会空白补全 |
| VARCHAR | 0 ~ 65 535bytes | 变长字符串 | name char(10)括号内的为限制最大空间，会根据字段实际长度变化实际存储空间大小 |
|
| TINYBLOB | 0 ~ 255bytes | 不超过255字符的二进制字符串 | ||
| TINYTEXT | 0 ~ 255bytes | 短文本字符串 | ||
| BLOB | 0 ~ 65 535bytes | 二进制形式的长文本数据 | ||
| TEXT | 0 ~ 65 535bytes | 长文本数据 | ||
| MEDIUMBLOB | 0 ~ 16 777 215bytes | 二进制形式的中等长度文本数据 | ||
| MEDIUMTEXT | 0 ~ 16 777 215bytes | 中等长度文本数据 | ||
| LONGBLOB | 0 ~ 4 294 967 295bytes | 二进制形式的极大文本数据 | ||
| LONGTEXT | 0 ~ 4 294 967 295bytes | 极大文本数据 |
-
查询表
-
查询当前数据库下所有表名称
SHOW TABLES; -
查询表结构
SHOW DESC 表名; -
查询指定表的建表语句
SHOW CREATE TABLE 表名;
-
-
修改表
-
添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度)[COMMENT 字段注释][约束]; -
修改字段
-- 修改数据类型 ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度); -- 修改字段名和字段类型 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度)[COMMENT 字段注释][约束]; -
删除字段
ALTER TABLE 表名 DROP 字段;
-
-
修改表名
ALTER TABLE 原表名 RENAME TO 新表名; -
删除表
DROP TABLE [IF EXISTS] 表名; -
删除表并重新创建该表
TRUNCATE TABLE 表名;
DML（Data Manipulation Language表数据的增删改）
增数据
-
添加一条数据
INSERT INTO 表名(字段1，字段2，...) VALUES(值1，值2，...); INSERT INTO 表名 VALUES(值1，值2，...);-- 仅全部列添加可省略字段 -
批量添加数据
INSERT INTO 表名(字段1，字段2，...) VALUES((值1.1,值1.2),(值1.2，值2.2)，...); INSERT INTO 表名 VALUES((值1.1,值1.2),(值1.2，值2.2)，...);-- 仅全部列添加可省略字段
删除
DELETE FROM 表名 WHERE 字段m =值m; -- 不加where条件会删除所有行
修改
UPDATE 表名 SET 字段1=值1，字段2=值2，...字段n=值n WHERE 字段m =值m; -- 不加where条件会修改所有行
DQL（Data Query Language数据的查询）
SELECT
字段列表 FROM表名列表 -- 查询基础
WHERE
条件列表 -- 查询条件
GROUP BY -- 分组条件
分组字段
HAVING
分组后条件
ORDER BY -- 排序查询
排序字段
LIMIT 分页限定 -- 分页查询
-- 查询去重记录
SELECT DISTINCT address FROM stu;
-- 查询结果用别名显示字段（下段SQL可以省略as关键字，但是字段和别名之间必须间隔至少一个空格）
SELECT name AS 姓名,address AS 地址 FROM stu;
查询条件判断符号
| 符号 | 功能 |
|---|---|
> |
大于 |
< |
小于 |
>= |
大于等于 |
<= |
小于等于 |
| = | 等于 |
<>或者!= |
不等 |
BETWEEN...AND... |
在某个范围之间（都包含） |
IN(...) |
多选一 |
LIKE占位符 |
模糊查询_单个任意字符，%多个任意字符 |
IS NULL |
是NULL |
IS NOT NULL |
不是NULL |
AND或&& |
并且 |
OR或` |
|
NOT或! |
非，不是 |
**注意SQL中null值不能使用比较符，要用IS NULL或者IS NOT NULL
排序查询
-- 排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序字段1 ASC,排序字段2 DESC ,...;
-- 多排序规则，只有字段1同值才会用后方排序规则
-- ASC：升序排列（默认值，可不写）
-- DESC：降序排列
聚合函数语法
SELECT 聚合函数名(字段) FROM 表;
| 函数名 | 功能 |
|---|---|
| count(字段) | 统计数量（统计列中不能为空） |
| max(字段) | 最大值 |
| min(字段) | 最小值 |
| sum(字段) | 求和 |
| avg(字段) | 平均值 |
**null值不参与聚合函数运算
分组查询
**分组后，查询字段仅能为聚合函数或分组字段，其他字段无任何意义
**执行顺序：where>聚合函数>having
-- 分页查询，注意表的索引开始索引是0开始，起始索引 = （当前要显示的页码 - 1）* 每页显示的条数
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询条目数
**MySQL分页查询关键字为：LIMIT
**SQL server分页查询关键字为：TOP
**Oracle分页查询关键字为：ROWNUMBER
DCL（Data Control Language数据库控制权限【创建用户，控制数据库访问权限】）
数据库用户的增删改
-
查询用户
USE mysql; SELECT * FROM USER; -
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';**任意主机访问主机名设置为
% -
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'; -
删除用户
DROP USER '用户名'@'主机名'
权限控制
| 权限 | 说明 |
|---|---|
| ALL,ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
查询权限
SHOW GRANTS FOR '用户名'@'主机名'
授予权限
GRANT 权限列表 ON 数据库名,表名 TO '用户名'@'主机名'
撤销权限
REVOKE 权限列表 ON 数据库名,表名 FROM '用户名'@'主机名'
函数
MySQL中直接被调用的程序或者代码
字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,...Sn) | 字符串拼接，将S1,S2,...Sn拼成一个字符串 |
| LOWER(str) | 将字符串str全部转换成小写 |
| UPPER(str) | 将字符串str全部转换成大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串str从start位置起的len个长度的字符串 |
数字函数
| 函数 | 功能 |
|---|---|
| CEIL(X) | 向上取整 |
| FLOOR(X) | 向下取整 |
| MOD(X,Y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND() | 求参数x的四舍五入值 |
日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YAER(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
| 函数 | 功能 |
|---|---|
| IF(value,t,f) | 如果value为true，返回t，否则返回f |
| IFNULL(value1,value2) | 如果value1不为空，返回value1，否则返回value2 |
| CASE WHEN [val1] RHEN [res1] ...ELSE [default] END | 如果val1为true，返回res1，...否则返回default默认值 |
| CASE [expr] WHEN [val1] RHEN [res1] ...ELSE [default] END | 如果expr的值等于val1，返回res1，....否则返回default默认值 |
约束
| 约束名称 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 保证所有字段中数据不能有NULL | NOT NULL |
| 唯一约束 | 保证字段中所有数据各不相同 | UNIQUE |
| 主键约束 | 主键是一行的唯一标识，要求非空且唯一（自增：AUTO_INCREMENT） |
PRIMARY KEY |
| 检查约束（8.0.16版本后） | 保证字段中的值满足某一条件 | CHECK |
| 默认约束 | 保存数据时，未指定值则采用默认值 | DEFAULT |
| 外键约束 | 外键用来让两个表之间建立链接，保证数据的唯一性和完整性 | FOREIGN KEY |
外键约束
-
添加约束
-- 创建表时添加约束 CREATE TABLE 子表名 ( 列名 数据类型, ... CONSTRAINT fk_子表名_主表名 FOREIGN KEY (子表关联列名) REFERENCES 主表名(主表关联列名) ); -- 建完表后添加约束 ALTER TABLE 子表名 ADD CONSTRAINT fk_子表名_主表名 FOREIGN KEY (子表关联列名) REFERENCES 主表名(主表关联列名) -
删除约束
ALTER TABLE 子表名 DROP FOREIGN KEY 外键名;**添加外键时，子表字段不能有数据，否则会报错
| 行为 | 说明 |
|---|---|
| NO ACTION | 在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致） |
| RESTRICT | 在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致） |
| CASCADE | 在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录 |
| SET NULL | 在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则外键在子表中该键值设为null（要求该外键允许取null） |
| SET DEFAULT | 父表有变更时，子表将外键字段设置成一个默认值（Innodb不支持） |
添加行为约束
ALTER TABLE 子表名 ADD CONSTRAINT fk_子表名_主表名 FOREIGN KEY (子表关联列名) REFERENCES 主表名(主表关联列名) ON UPDATE NO ACTION ON DELETE NO ACTION;
多表查询
表关系多对一：多的一方建立外键指向一的主键
表关系多对多：第三张中间表至少包含两个外键，分别关联两方主键
表关系一对一：任意一方加入外键关联另一方主键，并设置外键为唯一（UNIQUE）
-- 隐式内连接查询
SELECT * FROM 表A,表B WHERE 表A.列1 = 表B.列B;
SELECT * FROM 表A AS T1,表B AS T2 WHERE T2.列1 = T2.列B;-- t1,t2为设置的别名
-- 显示内连接查询
SELECT * FROM 表A INNER JOIN 表B ON 表A.列1 = 表B.列B;
SELECT * FROM 表A AS T1 INNER JOIN 表B AS T2 ON T1.列1 = T2.列B;-- t1,t2为设置的别名
-- 外连接查询
-- 左外连接
SELECT * FROM 表A LEFT OUTER JOIN 表B ON 表A.列1 = 表B.列B;-- OUTER 可不写
-- 右外连接
SELECT * FROM 表A RIGHT OUTER JOIN 表B ON 表A.列1 = 表B.列B;-- OUTER 可不写v
自连接
SELECT * FROM 表A AS A LEFT OUTER JOIN 表A AS B ON A.列1 = B.列B;
联合查询
将多次查询结果合并起来，形成一个新的查询结果集【多次查询必须字段数、内容保持一致；UNION ALL 为直接拼接，UNION为去重拼接】
SELECT * FROM 表A ...
UNION [ALL]
SELECT * FROM 表B ...;
事务
事务的四个特征
- 原子性（Atomicity）：事务是不可分割的最小单位，要么同时成功，要么同时失败；
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态；
- 隔离性（Isolation）：多个事务之间，操作的可见性；
- 持久性（Durability）：事务一旦提交，他对数据库中的数据改变就是永久的
**MySQL默认事务自动提交，Oracle默认事务手动提交
-- 查询MySQL的事务设置，0手动，1自动
select @@autocommit;
-- 设置事务规则为手动提交
set @@autocommit = 0;
-- 设置手动提交后，更新插入必须提交事务
commit;
-- 出现问题回滚事务
rollback;
-- 不设置全局配置，手动开启事务
start transaction;
并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另一个事务还没提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但是两次读取的数据不同，成为不可重复读 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影” |
事务隔离级别
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read | × | × | √ |
| Serializable | × | × | × |
MySQL默认Repeatable Read，Oracle默认Read committed
事务级别越高，能避免问题越多，数据越安全，性能越弱
-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
-- 设置事务隔离级别SESSION为单次会话隔离级别，GLOBAL为全局
SET [SESSION][GLOBAL] TRANSACTION ISOLATION LEVEL Read uncommitted;
进阶
MySQL体系结构
-
连接层
最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证等相关安全方案。服务器也会为安全接入每个客户端验证它所具有的操作权限
-
服务层
第二台价格主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层，如：过程、函数等
-
引擎层
储存引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样可以根据需求选取合适的存储引擎
-
储存层
主要是将数据储存在文件系统上，并完成与储存引擎交互
储存引擎
MySQL5.5版本后默认INNODB，早期版本默认的是MyISAM。
存储引擎是定义表的存储方式、建立索引、更新查询数据等技术的实现方式，同一个数据库中不同表可以指定不同的存储引擎
-- 展示当前数据库支持的存储引擎
SHOW ENGINES;
-
INNODB存储引擎
一种兼顾高可靠性和高性能的通用存储引擎；
特点：1.DML遵守ACID模型，支持事务；2.行级锁，提高并发访问性能；3.支持外键约束
文件：
xxx.ibd每一张表都对应一个表空间文件INNODB逻辑存储结构
一个表空间Tablespace包含若干段Segment
一个段Segment包含若干区Extent
一个区Extent包含若干页Page
一个页Page包含若干行Row
Row中包含最后一次操作事务的ID，指针，字段
区的默认大小是固定的1M，页为16K【一个区固定包含64个页】
-
MyISAM存储引擎
早期的MySQL默认存储引擎
不支持事务，不支持外键，支持表级锁，不支持行锁，访问速度快
-
Memory存储引擎
Memory引擎存储的表存储在内存中，受硬件问题或断电等问题影响，之恶能作为临时表或者缓存使用
特点是速度快，支持hash索引
存储引擎特点
| 特点 | INNODB | MyISAM | Memory |
|---|---|---|---|
| 存储限制 | 64TB | 有 | 有 |
| 事务安全 | 支持 | - | - |
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | - | - | 支持 |
| 全文索引 | 支持（5.6版本以后） | 支持 | - |
| 空间使用 | 高 | 低 | N/A |
| 内存使用 | 高 | 低 | 中等 |
| 批量插入速度 | 低 | 高 | 低 |
| 支持外键 | 支持 | - | - |
存储引擎的选择
- INNODB：对事务完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多更新、删除操作，那么使用INNODB存储引擎
- MyISAM：如果是读操作和插入操作位置，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高
- Memory：数据全部保存在内存中，访问速度快，通常用于临时表或者缓存，缺陷是对表大小由限制，太大无法缓存在内存中，无法保证数据安全性
索引
索引是帮助MySQL高效的获取数据的数据结构（有序）。在数据之外，数据库系统还维护着特定查找算法的数据结构，这些数据结构以某种方式（指向）数据，这样就可以这些数据结构上实现高级查找算法，这种数据结构就是索引。
索引的优缺点
| 优势 | 劣势 |
|---|---|
| 提高数据检索的效率，降低数据库的IO成本 | 索引列占用储存空间 |
| 通过索引列对数据进行排列，降低数据排序的成本，降低CPU的消耗 | 所以哦那大大的提高了查询效率，同时也降低了更新表的速度，如对表进行INSERT、UPDATE、DELETE，效率降低 |
索引的常见结构
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是哈希表，只有精确匹配索引列才会有效，不支持范围查询 |
| R-Tree（空间索引） | 空间索引是MyISAM引擎的特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text（全文索引） | 是一种通过建立倒排索引，快速匹配文档的方式。类似与Lucene，Solr，ES |
常见引擎对索引支持情况
| 索引结构 | INNODB | MyISAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-Tree | 不支持 | 支持 | 不支持 |
| Full-text | 5.6版本后支持 | 支持 | 不支持 |
b+树所有元素都会在叶子节点，树结构仅用于排序查找，Mysql在经典B+树优化，在原有相邻叶子节点的链表增加一个指针提高区间访问性能。所有节点均储存在页层级
Hash索引是对行取哈希值，然后对需要索引字段再取哈希值映射存储到哈希表中，如果字段内容哈希值映射到相同槽位，产生哈希碰撞，则在该槽位使用链表处理。因此Hash不支持范围查询，只能用于对等比较，无法排序；一般情况下查询效率高，如果没有链表，只需要一次检索即可
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB中，根据索引的储存形式，又可以分为以下两种
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储与索引放在一起，索引结构的叶子节点保存行数据 | 必须有，且只有一个 |
| 二级索引 | 将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引的选取规则：如果存在主键，主键索引就是聚集索引；没有主键则第一个唯一索引作为聚集索引
根据二级索引获取聚集索引位置，再通过聚集索引取的行数据成为回表查询
索引语法
-
创建索引
-- 创建唯一或者全文索引（不加关键词则为常规索引）；一个索引可以关联多个关键字 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性能分析
-- 查询当前数据库各种语句访问频次
SHOW GLOBAL STATUS LIKE 'COM_______';
慢查询日志，MySQL慢查询日志默认不开启【Linux系统/etc/my.cnf；Windows中在my.ini中】
-- 查询数据库慢查询日志功能是否开启
show variables like 'slow_query_log';
通过以下配置开启慢查询记录；记录S执行时间超过设定值的QL语句的详细信息【连接者，操作时间，查询耗时，锁时间，返回记录数，使用的数据库】
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间，单位是秒，SQL语句执行超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
profile详情
可以通过profile可记录SQL语句执行耗时耗费在什么地方，
-- 查询当前数据库是否支持profile记录（mysql默认设置关闭）
select @@have_profiling;
-- 查询当前配置（0关闭，1开启）
select @@profiling;
-- 开启
set @@profiling =1;
-- 开启后可以查询每个sql语句的耗时
SHOW profiles;
-- 查询单条语句的各阶段耗时，和cpu占用
show profile cpu for query {sql的id}
explain执行计划
通过命令获取MySQL如何执行SELECT语句执行过程中表如何连接和连接顺序
直接在语句前加上关键字explain或者desc即可获取SQL执行的信息
执行计划各字段含义
| 字段 | 含义 |
|---|---|
| ID | select查询语句中的序列号，表示查询中执行select子句或者操作表的顺序，如果相同则从上到下执行，值越大则越先执行 |
| select_type | 表示select的类型，常见取值有SIMPLE（简单表，即不用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNIONh中的第二个或者后面的查询语句）、SUBQUERY（select/where之后包含了子查询）deng |
| type | 表示连接的类型，由好到坏连接类型为NULL、system、const、eq_ref、ref、range、index、all【优化数据库索引时，常把类型往好的优化】 |
| possible_key | 可能使用到的索引 |
| key | 实际用的索引，如果没用到索引，则为null |
| key_len | 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精度的前提下，越短越好 |
| rows | MySQL认定必须执行查询的行数，在innodb引擎中，这是一个预估值，并不准确 |
| filtered | 表示返回结果的啊行数占所需读取行数的百分比，filtered的值越大越好 |
索引使用
-
最左前缀法则
如果索引了多列（联合索引），要遵守最左前缀法则；指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，则索引部分失效（后面的字段索引失效）。如果不从最左边列开始，则该联合索引失效
-
范围查询
联合索引中，如果出现了范围查询
><范围查询右侧的字段索引失效；在业务条件允许的情况下尽量使用>=<=此类范围查询联合索引不失效 -
索引列运算
不要在索引列上进行运算操作，索引会失效
-
字符串不加引号
字符串类型的字段不加引号查询，索引会失效
-
模糊查询
字符串后模糊匹配时索引有效，前模糊匹配索引失效
-
or连接的条件or两侧查询的字段都要有索引，那么涉及的索引才会被使用 -
数据分布原则
如果MySQL自动判断索引比全表扫描慢时，会自动放弃索引
-
SQL提示
在SQL中加入设置来优化数据库查询操作过程
-- 指定建议使用索引 explain select * from tb_user use index(idx_user_pro)where profession = '软件工程'; -- 不使用索引 explain select * from tb_user ignore index(idx_user_pro)where profession = '软件工程'; -- 强制指定使用索引 explain select * from tb_user force index(idx_user_pro)where profession = '软件工程'; -
覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引总已经全部找到），减少使用
select *如果sql执行过程中有以下的额外信息，尽量避免回表，节省时间
using index condittion：使用了索引名单时需要回表查询数据using where；using index：查找使用了索引，但是需要返回的数据在索引中都能找到，所以不需要回表查询数据
-
前缀索引
当字段类型为字符串（varchar，text等）时，有时需要索引很长的字符串，这回让索引变得很大，查询时浪费大量磁盘IO，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，节省索引空间，提高效率
-- 截取某个字段储存的字符串前n个字符建立索引 create index idx_xxx on table_name(column(n)); -- 计算索引长度的选择性【不重复索引值和数据表种植的壁纸越高查询效率越高，唯一索引选择性是1】 -- 求取email字段不为空且不重复的数据占总表数据比值 select count(distinct email)/count(*) from tb_user; -- 求取email字段截取前5个字符的选择性 select count(distinct substring(email,1,5))/count(*) from tb_user; -
单列索引和多列索引
若存在多个查询条件时，考虑针对与查询关键字段建立联合索引而非单列索引
索引设计原则
- 针对数据量较大，且查询比较频繁
- 针对于作为条件、排序、分组操作字段建立索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度愈高，使用索引效率越高
- 如果时字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间，避免回表，提高查询效率
- 要控制索引数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
- 如果索引列不能存储
NULL值，在创建表时需要使用NOT NULL来约束。当优化器知道每列时候包含NULL值时，它可以很好的确定那个索引最有效的用于查询
增删改SQL分析优化
插入数据
-
新增数据数量较多时，使用批量插入，过多则分割多条insert语句，每条语句500-1000条数据
-
手动事务提交；多条insert完成后统一commit
-
最好主键顺序插入
-
大批量数据一次性插入时，使用insert性能较低，MySQL提供
load指令【图形化界面的导入】# 客户端连接服务端式，加上参数 --local-infile mysql --local-infile -u root -p #设置全局参数local_infile为1，开启本地加载文件导入数据的开关 set global local_infile=1; #执行load指令将准备好的数据，加载到表结构中 load data local infile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
主键优化
在InnoDB中表中数据都是根据主键顺序组织存放的，称为索引组织表
在主键乱序插入操作时，可能会出现页分裂情况；在页中删除记录达到MERGE_THRESHOLD【默认50%】，InnoDB会寻找最近的前后页是否可以优化空间使用，称为页合并
主键设计原则
- 在满足业务需求的情况下，尽量降低主键的长度
- 插入数据时，尽量选择顺序插入，选择使用
AUTO_INCREMENT自增主键 - 尽量不要使用UUID做主键或者其他自然主键，如身份证
- 尽量不要对主键进行修改
order by的优化
- Using Filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接分会排序结果的排序都叫FileSort排序
- Using Index：通过有序索引顺序扫描直接返回有序数据，这种情况即为Using Index，不需要额外排序，操作效率高
索引默认使用的是升序排列，查询排序操作时，仅使用索引，全升序排列、使用索引，全降序排列会是Using Index，不使用索引或者前一个升序后一个降序都会出现Using Filesort
group by的优化
分组操作时，也需要满足最左前缀法则
limit by的优化
大数据量时，越靠后的分页查询效率越低
官方推荐使用覆盖索引加子查询形式
count优化
MyISAM引擎将一个表的总行数存在了一个磁盘上，因此执行Count(*)效率很高
InnoDB，执行count(*)的时候，需要把数据一行一行地从引擎中读出来，然后累计计数
优化思路：自己计数
count()是一种聚合函数，对于返回的结果集，一行一行的判断，如果count函数的参数不是NULL累计值就加1，否就不加。
- count(主键)遍历整张表，将主键id均取出，返回服务层，服务层进行累加
- count(字段)如果没有
NOT NULL约束，会将每行字段取出，返回服务层，服务层判断是否为NULL，不为NULL计数累加，是则计数不加；如果有约束会把每行字段取出，返回服务层，直接进行累加 - count(1)遍历整张表，但是不取值，服务层对于返回每一行，放一个数字“1”进去进行累加
- count(*)不会取出全部字段，在服务层直接进行累加
对于效率为count(字段)<count(主键)<count(1)<ount(*)，所以业务尽量使用count(*)
update优化
update条件设置需要使用有索引的字段，如果没有索引，锁的级别会从行锁为表锁
InnoDB中，行锁是针对索引加锁，不是针对记录，索引索引不能失效，否则都会升级为表锁
视图/储存过程/触发器
视图
视图是一种虚拟存在的表，视图中的数据并在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的
-- 创建
CREATE [OR REPLACE] VIEW 视图名称[(列表名称)] AS SELECT语句
-- 查询视图
show create view 视图名称;
select * from 视图名称;
-- 修改视图
create [OR REPLACE] VIEW 视图名称[(列表名称)] AS SELECT语句
alter VIEW 视图名称[(列表名称)] AS SELECT语句
-- 删除视图
drop view [if exists] 视图名称[]
避免操作视图插入数据是不被视图所筛选出【数据可以合法插入表，但是数据不满足视图条件】，可以限制视图插入条件，使操作视图插入数据均视图可见
-- 视图的检查选项
-- 避免操作视图插入数据是不被视图所筛选出【数据可以合法插入表，但是数据不满足视图条件】，可以限制视图插入条件，使操作视图插入数据均视图可见
CREATE [OR REPLACE] VIEW 视图名称[(列表名称)] AS SELECT语句 with cascaded check option;
-- cascaded 是检查当前视图（包括依赖的隐藏视图）在内所有视图条件是否满足
-- local 是仅检查符合当前视图
视图的更新
视图中的行于基础表中的行必须是一对一的关系，如果视图包含以下任何一项，则不可更新
- 聚合函数或者窗口函数【sum()、min()、max()、count()...】
- distinct
- group by
- having
- union或union all
- ...
视图的作用
-
简单
视图可以简化用户对数据的理解，也可以简化操作。经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部条件
-
安全
数据库可以授权，但是不能授权到数据库特定行和特定的列上，通过视图用户只能查询和修改他们所能见到的数据
-
数据独立
视图可以帮助用户屏蔽真实表结构变化带来的影响
存储过程
封装SQL语句，方便复用，可以接收参数，也可以返回数据，减少网络交互，效率提升
-- 创建存储过程【navcat中称MySQL的存储过程为函数】
create procedure 存储过程名称([参数列表])
begin
-- SQL语句;
end;
-- 命令行创建存储过程
delimiter $$
create procedure 存储过程名称([参数列表])
begin
-- SQL语句;
end$$
delimiter ;
-- 调用
call 名称([参数]);
-- 查看
select * from information_schema.routines where routine_schema='xxx'; -- 查看指定数据库的存储过程及状态信息
show create procedure 存储过程名称; -- 查询某个存储过程的定义
-- 删除
drop procedure if exists 存储过程名称;
存储过程允许传变量参数
变量：
-
系统变量：由MySQL服务器提供，分为全局变量（global）和会话变量（session）
-- 系统变量【不指定默认session级别系统变量】 show [session |global] variables; -- 查看所有系统变量 show [session |global] variables like 'xxx'; -- 通过like模糊匹配方式查找变量 select @@[session |global].系统变量名; -- 查看指定变量值 -- 设置系统变量 set [session |global] 系统变量名=值; set @@[session |global] 系统变量名=值;注意：MySQL服务重启后所有系统变量会恢复初始值，如果保持修改一直有效，则需要修改配置文件
my.ini -
用户自定变量：由用户根据自己需求定义的变量，使用前不用提前声明，在使用的时候直接用“@变量名”即可，作用域为当前连接
-- 赋值 set @var_name = expr; set @var_name := expr; select @var_name := expr; select 字段名 into @var_name from 表名; -- 使用 select @var_name; -
局部变量：根据需要定义的局部生效的变量，访问之前，需要declare声明。可用作存储过程的局部变量和输入参数，局部变量的发我内实在其声明的begin...end块
create procedure p1() begin declare 变量名 变量类型[default...]; end;
if
-- 条件判断
if 条件1 then
...
elseif 条件2 then
...
else 条件3 then
...
end if;
参数
| 类型 | 含义 | 备注 |
|---|---|---|
| in | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| out | 该类参数作为输出，也就是该参数可以作为返回值 | |
| inout | 既可以作为输入参数，也可以作为输出参数 |
create procedure p1(in score int,out result varchar(10))
begin
if score>=85 then
set result := '优秀';
elseif score>=60 then
set result := '及格';
else
set result := '不及格';
end if;
end;
case
case case_value
when when_value1 then statement_list1
when when_value2 then statement_list2
...
else statement_list
end case;
-- ----------------------
case
when search_condition1 then statement_list1
when search_condition2 then statement_list2
...
else statement_list
end case;
while
-- 先判定条件，如果为true，则执行逻辑，否则不执行
while 条件 do
sql逻辑
end while;
repeat
repeat
sql逻辑
until 条件
end repeat;
loop
-- 不添加退出条件则为死循环
begin_lable: loop
sql逻辑
iterate begin_lable;-- 跳过本次循环
leave begin_lable;-- 结束该循环
end loop begin_lable;
cursor游标
-- 声明游标
declare 游标名称 cursor for 查找语句;
-- 打开游标
open 游标名称;
-- 获取游标记录
fetch 游标名称 into 变量;
-- 关闭游标
close 游标名称;
handler
declare handler_action handler for condition_Value ... statement;
-- 状态标识
-- 执行步骤
handler_action
continue: 继续执行当前流程
exit：终止执行当前程序
-- 异常状态码
condition_Value
sqlstate sqlstate_value:状态码，如02000
sqlwarning:所有以01开头的sqlstate代码的简写
not found:所有以02开头的sqlstate代码的简写
sqlexception:所有没有被sqlWarning或not found捕获的sqlstate代码的简写
存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是in类型
create function 存储函数名称(参数列表)
return type [characteristic...]
begin
-- sql语句
return...;
end;
-- characteristic说明
deterministic：相同输入参数总是产生相同的结果
no sql：不包含sql语句
reads sql data：包含读取数据的语句，但是不包含写入数据的语句
触发器
与表有关的数据库对象，指在insert/update/delete之前或之后，执行并执行触发器中定义的sql集合。触发器的特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
| 触发器类型 | NEW和OLD |
|---|---|
| INSERT型触发器 | NEW表示将要或者已经新增的数据 |
| UPDATE型触发器 | OLD表示修改前的数据，NEW表示将要或已经修改后的数据 |
| DELETE型触发器 | OLD表示将要或者已将删除的数据 |
MySQL目前只支持行级触发器【一个语句不论影响多少行，就会触发多少触发器】
-- 创建
create trigger trigger_name
before/after insert/update/delete
on table_name for each row -- 行级触发器
begin
trigger_statement;
end;
-- 查看
show triggers;
-- 删除
drop trigger [schema_name.]trigger_name; -- 如果没有指定schema_name，默认为当前数据库。
锁
计算机协调多个进程或线程并发访问某一资源的机制，在数据库中，除传统计算资源外，数据也是一种供多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是数据库并发访问性能的一个重要因素。
全局锁：锁定数据库中所有的表
对整个数据库实例加锁，使实例处于只读状态，后续的DML，DDL语句，已更新操作的事务提交都将被阻塞。典型使用场景为全库逻辑备份，对所有表锁定，从而获取一致性视图，保证数据的完整性。
flush tables with read lock;-- 全局锁
mysqldump -uroot -p123456 数据库名>绝对存储路径 -- 此行在命令行执行
unlock tables;-- 释放全局锁
特点：数据库中加全局锁，是一个比较重的操作，存在以下问题
- 如果在主库上备份，那么备份期间都不能执行更新，业务基本上停摆
- 如果在从库上备份，那么备份期间从库不能执行主库同步过来的二进制日志，会导致主从延迟
在InnoDB引擎中，我们可以在备份时加上参数--single-transaction来完成不加锁的一致性数据备份
表级锁：每次操作锁整张表
操作锁住整张表。锁定粒度大，发生锁冲突概率最高，并发度最低，应用在MyISAM、InnoDB、BDB等存储引擎中
表锁有以下三类
-
表锁
1.1表共享读锁（read lock）
允许所有客户端读取数据，阻塞所有写入操作【申请锁表的客户端写入会报错】
1.2表共享写锁（write lock）
仅允许申请锁的客户端读写，其他所有客户端读写均阻塞
lock tables 表名 read/write unlock tables; -- 或者申请锁的客户端断开连接 -
元数据锁（mate data lock；MDL）
加锁过程为系统自动控制，主要用于维护表元数据的数据一致性，在表上有事务活动时，不可以对数据进行读写操作
MySQL5.5引入该锁机制，对一张表进行增删改查时对加MDL读锁（共享），对表结构进行改变时，加MDL写锁（排他）
对应SQL 锁类型 说明 lock tables xxx read/write SHARED_READ_ONLY/SHARED_NO_READ_WRITE select、select...lock in share mode SHARED_READ 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 insert、update、delete、select...for update SHARED_WRITE 与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥 alter table... EXCLUSIVE 与其他的MDL都排斥 -- 检查数据库MDL锁信息，锁级别、锁在的数据库，锁对对象名（表名/数据库名），锁种类，锁持续时间 select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks ; -
意向锁
避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入意向锁，是的表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
3.1意向共享锁
由语句select...lock in share mode添加【与表共享锁兼容（read），与排他锁互斥（write）】
3.2意向排他锁
由insert、update、delete、select...for update添加【与表共享锁、排他锁互都互斥，意向锁之间不会互斥】
-- 注意5.7.19版本中并无data_locks表，8.0.30中存在 select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
行级锁
每次操作锁住对应行数据，锁定粒度最小，发生锁冲突概率最低，并发度最高
InnoDB引擎中是基于索引加锁而不是对记录加锁。对于行级锁，主要分为以下三类
-
行锁（Record Lock）：锁定单个行记录，防止其他事务对此进行update、delete。在RC、RR隔离级别以下都支持
1.1共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
1.2排他锁（X）：允许获取排他锁的事务更新数据，组织其它事务获得相同数据集的共享锁和排他锁
SQL 行锁类型 说明 insert... 排他锁 自动加锁 update... 排他锁 自动加锁 delete... 排他锁 自动加锁 select...（正常语句） 不加任何锁 select...lock in share mode 共享锁 需要手动在select之后加 lock in share modeselect...for update 排他锁 需要手动在select之后加 for update -
间隙锁（Gap Lock）：锁定索引记录间隙（不包含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别以下都支持。
默认情况下
2.1索引上的等值查询（唯一索引），给并不存在的记录加锁时，优化为间隙锁
2.2索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
2.3索引上的唯一范围查询（唯一索引），会访问到不满足条件的第一个值为止
-
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁定数据和数据前面的间隙。在RR隔离级别以下都支持
间隙锁的唯一目的是防止其他事务插入间隙，间隙锁可以共存。一个事务采用的间隙锁不会组织另一个事务在同一间隙采用间隙锁
InnoDB引擎
逻辑存储架构
一个表空间Tablespace包含若干段Segment
- 每一个表空间都是一个ibd文件，一个MySQL实例可对应多个表空间，用于存储记录、索引等数据
一个段Segment包含若干区Extent
- 数据段（Leaf node segment）
- 索引段（Non-leaf node segment）
- 回滚段（Rollback segment）
一个区Extent包含若干页Page
一个页Page包含若干行Row
Row中包含最后一次操作事务的ID，指针，字段
- Trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列
- Roll_pointer：每次对某条记录改动时，都会把旧版本写入到undo日志中，然后这个隐藏列就相当于一个指针，可以通过他来找到该条记录修改前的信息
架构
下方为InnoDB内存和磁盘结构架构图
内存架构
-
Buffer Poll：缓冲池是主内存中的一个区域，里面可缓存磁盘上的真是数据，在执行增删改时，先操作缓冲池中的数据（没有数据则从磁盘中加载并缓存），然后再以一定频率刷新到磁盘，以减少磁盘IO，加快处理速度。
缓冲池以page为单位，底层采用链表数据结构管理。根据状态，page分为三种类型
- free page：空闲页，未被使用
- clean page：被使用page，数据没有被修改过
- dirty page：脏页，被使用page，数据被修改过，页中数据与磁盘中产生了不一致
-
Change Buffer：更改缓冲区（针对于非唯一二级索引页），在执行DML语句时，如果这些数据page没有在Buffer Poll中，不会直接操作磁盘，而会将数据变更数据存在更改缓冲区Change Buffer中，在未来读取时，再将数据合并 恢复到Buffer Pollz中，再将合并后的数据刷新到磁盘中。
-
Adaptive Hash Index：自适应哈希索引，用于优化Buffer Poll数据查询，系统自动监控表上各索引页的查询，如果观察到Hash索引能提升速度，则建立Hash索引。无需人工干预。控制参数为
innodb_adaptive_hash_index默认开启状态 -
Log Buffer：日志缓冲区，用来保存要写入磁盘中log日志数据（redo log、undo log），默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或者删除许多行的事务，增加日志缓冲区的大小可以节省磁盘IO。
innodb_log_buffer_size控制缓冲区大小，innodb_flush_log_at_trx_commit日志刷新到磁盘时机（1：日志在每次事务提交时写入并刷新到磁盘，0：每秒将日志写入并刷新到磁盘一次，2：日志在每次事务提交互殴写入，并每秒刷新到磁盘一次）
磁盘结构
-
System Tablespace：系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。（在MySQL5.x版本中还包含InnoDB数据字典、undo log等）参数
innodb_data_file_path确定系统表空间文件名 -
File-Per-Table Tablespace：每个表文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上单个数据文件中。参数
innodb_file_per_table控制是否为每张表单独生成表空间文件，默认开启 -
General Tablespace：通用表空间，不创建默认不存在，需要通过
create Tablespace语法创建通用表空间，在创建表时，可以指定该表空间-- 创建表空间，指定表空间名，文件名，使用引擎 create tablespace ts_Tablespace_name add datafile 'Tablespace.ibd' engine = engine_name; -- 创建表时指定表空间 create table table_name tablespace ts_Tablespace_name; -
Undo Tablespace：撤销表空间，MySQL实例在初始化时会自动创建两个默认的undo表空间（初始16M），用于存储undo log日志
-
Temporary Tablespace：InnoDB使用的会话临时表空间和全局临时表空间，存储用户创建的临时表等数据
-
Doublewrite Buffer Files：双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据也写入双写缓冲区文件中，便于系统异常时恢复数据
-
Redo log：重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（Redo log buffer）以及重做日志（Redo log），前者在内存中，后者在磁盘中。当事务提交后会把所有修改信息都存到该日志中，用于在刷新脏页到磁盘时，发生错误时，进行数据恢复使用
后台线程
-
Master Thread
核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页回收
-
IO Thread
在InnoDB存储引擎中大量使用了AIO来处理IO请求，这样可以极大的提高数据库的性能，而IO Thread主要负责这些IO请求的回调
线程类型 默认个数 职责 Read thread 4 负责读操作 Write thread 4 负责写操作 Log thread 1 负责将日志缓冲区刷新到磁盘 Insert buffer thread 1 负责将写缓冲区内容刷新到磁盘 -
Purge Thread
主要用于回收事务已经提交了的undo log，在事务提交之后，undo log可能不用了，就用他来回收
-
Page Cleaner Thread
协助Master Thread刷新脏页到磁盘的线程，它可以减轻Master Thread的工作压力，减少阻塞
事务原理
-
redo log
重做日志，记录的时事务提交数据时的物理修改，用来实现事务的持久性
该日志由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志中，用于在刷新在那脏页到磁盘，发生错误时，进行数据恢复使用
-
undo log
回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）
- undo log和redo log记录物理日志不一样，它是逻辑日志。可认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录，它记录一条记录相应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚
- undo log 销毁：undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC
- undo log存储：undo log采用段的方式进行管理和记录，存放在前面介绍的rollback segment回滚段中，内部包含1024undo log segment
MVCC
基本概念
-
读取的是记录的最新斑斑，读取时还要保证其他并发事务不饿能修改当前的记录，会对读取记录加锁，会对读取的记录进行加锁。对于日常的操作，如：select...lock in share mode（共享锁），select...for update、update、insert、delete都是一种当前读
-
快照读
简单的select（不加锁）就是快照读，读取的时记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。
- Read Commited：每次select，都生成一个快照读
- Repeatale Read：开启事务后第一个select语句才是快照读开始的地方
- Serializable：快照读会退化为当前读
-
MVCC
Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL提供了一个非阻塞读功能。MVCC具体实现，还需要依赖于数据库记录中隐式字段、undo log日志、readView
实现原理
-
记录中的隐藏字段
创建表后InnoDB引擎自动增加三个隐藏字段
隐藏字段 含义 TB_TRX_ID最近修改事务ID，记录插入这条记录或最后一次修改该条记录的事务ID DB_ROLL_PTR回滚指针，指向这条记录得的上一个版本，用于配合undo log，指向上一个版本 DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段 -
undo log
回滚日志，在inset、update、delete的时候产生的便于回滚的日志
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除
-
undo log版本链
不同事务或相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧纪录，林彪尾部是最早的旧纪录
-
readView（读视图）是快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务（未提交的）id
readView中包含四个核心字段
字段 含义 m_ids 当前活跃的事务ID集合 min_trx_id 最小活跃事务ID max_trx_id 预分配事务ID，当前最大事务ID+1 creator_trx_id Read View创建者的事务ID
MySQL管理
系统数据库
MySQL安装后会自带四个数据库
| 数据库 | 用途 |
|---|---|
mysql |
存储MySQL服务器正常运行所需各种信息（时区、主从、用户、权限等） |
information_schema |
提供访问数据库元数据的各种表和视图，包含数据库、表、字段类型及访问权限等 |
performance_schema |
为MySQL服务器运行时提供一个底层监控功能，主要用于收集数据库服务器性能参数 |
sys |
包含一系列方便DBA和开发人员利用performance_schema性能数据库进行调优和诊断的视图 |
常用工具
-
mysql
mysql [options] [database] #options有以下选项 -u, --user=name #指定用户名 -p, --password[=name] #指定密码 -h, --host=name #指定服务器IP或域名 -p, --port=port #指定连接端口 -e, --execute=name #执行sql语句并退出【常用于脚本，不用连接数据库】 -
mysqladmin
一个执行管理操作的客户端程序，可以用来检查服务器状态和配置当前状态、创建并删除数据库等
-
mysqlbinlog
用于服务器生成二进制日志文件以二进制格式保存，如果想要检查这些文本的文本格式，就会使用mysqlbinlog日志管理工具
mysqlbinlog [options] log-files1 #options有以下选项 -d , --database=name #指定数据库名称，只列出指定数据相关操作 -o , --offset=# #忽略掉日志中的前n行命令 -r , --result-file=name #将输出的文本格式日志输出到指定的文件 -s , --short-form #显示简单格式，省略掉一些信息 --start-datatime=date1 -stop-datatime=date2 #指定日期间隔内所有日志 --start-position=pos1 --stop-position=pos2 #指定位置间隔内所有日志 -
mysqlshow
mysqlshow [options] [db_name [table_name [col_name]]] #options有以下选项 --count #显示数据库及表的统计信息 -i #显示指定数据库或表的状态信息 -
mysqldump
mysqldump [options] db_name [tables] mysqldump [options] --database/-B database_name mysqldump [options] --all-databases/-A #options包含连接选项 -u, --user=name #指定用户名 -p, --password[=name] #指定密码 -h, --host=name #指定服务器IP或域名 -p, --port=port #指定连接端口 #options包含输出选项 --add-drop-database #在创建每个数据库语句前加上drop database语句 --add-drop-table #在每个表创建语句前加上drop table语句，默认开启；（关闭--skip-add-drop-table） -n , --no-create-db #不包含数据库的创建语句 -t , --no-create-info #不包含数据表的拆功创建语句 -d , --no-data #不包含数据 -T , --tab=name #自动生成两个文件：一个.sql文件，创建表结构语句；一个.txt文件，数据文件【注意，仅MySQL配置环境中信任目录下能储存数据文件，其他路径该命令不会生成数据文件。环境配置变量名为secure_file_priv】 -
mysqlimport/source
客户端数据导入工具，用来导入mysqldump加-T参数后到处的文本文件
mysqlimport [options] db_name textfile1 [textfile2] #如果需要导入sql文件，可以使用mysql中的source指令 source /root/xxx.sql
运维
日志
-
错误日志
该日志是MySQL最重要的日志之一，记录mysqld启动和停止，以及服务器运行过程中发生的任何严重错误相关的信息。日志默认开启，默认存放位置为
/var/log，查看日志位置show variables like '%log_error%'; -
二进制日志【8.0版后默认开启】
作用
- 灾难时数据恢复
- MySQL的主从复制。
-- 查看二进制日志相关信息 show variables like '%log_bin%';日志格式 含义 STATEMENT基于SQL语句的日志记录，记录的时SQL语句，对数据进行修改的SQL文件都会记录到日志文件中 ROW基于行的日志记录，记录的每一行的数据变更（默认） MIXED混合了 STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况会自动切换ROW进行记录-- 当前日志记录格式 show variables like '%binlog_format%';使用mysqlbinlog来解析二进制log文件
对于繁忙的业务系统来说，每天生成binlog数据巨大，如果长时间不清理，会占用大量磁盘空间
指令 含义 reset master删除全部binlog日志，删除之后，日志编号，将binlog.00001开始 purge master logs to 'binlog.xxxxx'删除xxxxx编号之前的所有日志 purge master logs before 'yyyy-mm-dd hh24:mm:ss'删除日志为“yyyy-mm-dd hh24:mm:ss”之前产生的所有日志 -- 配置文件控制二进制文件过期时间，时间过后自动删除 show variables like '%binlog_expire_logs_seconds%'; -
查询日志
查询日志记录客户但所有操作语句。默认情况下不开启，
-- 展示查询日志开关和日志存储位置 show variables like '%general%';变更需要在
my.ini配置文件中修改#该选项控制查询日志开关，0关闭，1开启 general_log=1 #设置文件名，如果不设置，默认为host_name.log general_log_file=mysql_query.log -
慢查询日志
慢查询日志记录了所有执行超过参数
long_query_time设置值【默认10秒】，并扫描记录数不小于min_examined_row_limit【默认0】的所有sql语句的日志，默认未开启#开启MySQL慢日志查询开关 slow_query_log=1 #设置慢日志的时间，单位是秒，SQL语句执行超过2秒，就会视为慢查询，记录慢查询日志 long_query_time=2注意，默认不会记录管理语句，也不会记录不使用索引进行查找的查询，
#记录执行比较慢的管理语句 log_slow_admin_statement=1 #记录执行较慢的未使用索引的语句 log_queries_not_using_indexes=1
主从复制
-
概述
主从复制是将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后通过从库对这些日志进行重新执行（又叫重做），从而使得从库和主库的数据保持同步
使用主从复制的优点有以下
- 主库出现问题，可以快速切换到从库提供服务
- 实现读写分离，降低主库的访问压力
- 可以在从库中执行备份，以避免备份期间影响主库服务
-
原理
执行步骤
- master主库在事务提交时，会将数据变更记录在二进制日志文件binlog中
- 从库读取主库的二进制文件binlog，写入到从库中继日志relay log
- slave重做中继日志中的事件，将改变反映它自己的数据
-
搭建
-
主库配置
1.1修改配置文件
#mysql服务id，保证整个集群环境唯一，取值为1~2^32-1，默认1 server-id=1 #是否只读。1代表只读，0代表读写 read-only=0 #忽略的数据，指不需要同步的数据 #binlog-ingnore-db=mysql #指定同步的服务器 #binlog-do-db=db011.2重启主库服务
1.3登录mysql，创建远程连接的账号，并授予主从复制权限
-- 创建用户，并指定访问主机 create user 'salve'@'%' identified with mysql_native_password by 'root@123456'; -- 为该用户分配主从复制权限 grant replication slave on *.* to 'salve'@'%';1.4检查二进制日志坐标
show master status; -- 字段含义 -- file：从那个日志文件开始推送日志文件 -- position：从哪个位置开始推送日志 -- binlog_ignore_db：指定不需要同步的数据 -
从库配置
2.1修改配置文件
#mysql服务id，保证整个集群环境唯一，取值为1~2^32-1，默认1 server-id=1 #是否只读。1代表只读，0代表读写 read-only=1 #超级管理员只读【上述设置只能设定普通用户，超级管理员需要单独设置】 suoer-read-only=12.2重启服务
2.3登录mysql，配置主库配置
-- 设置主库IP，连接主库用户名，连接密码，binlog日志文件名，binlog文件位置 -- 8.0.23起之后的语法 CHANGE REPLICATION SOURCE to SOURCE_HOST='xxx.xxx.xxx.xxx',SOURCE_USER='xxx',SOURCE_PASSWORD='xxx',SOURCE_LOG_FILE='xxx',SOURCE_LOG_POS=xxx; -- 8.0.23之前版本语法 CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xxx;2.4开启同步操作
-- 8.0.22版本之后语法 start replica; -- 8.0.22之前语法 start salve;2.5查看主从同步状态
-- 8.0.22版本之后语法 show replica status\G; -- 8.0.22之前语法 show salve status\G;检查
Replica_IO_Runing和Replica_SQL_Runing两个值均为YES即可
-
分库分表
数据库非常庞大后，单数据库存储会存在以下性能瓶颈
- IO瓶颈：热点数据太多，数据库缓存不足，产生大量磁盘IO，效率较低。请求太多，带宽不足网络IO瓶颈
- CPU瓶颈：排序、分组、连接查询、聚合统计等sql会耗费大量的CPU资源，请求数太多，CPU出现瓶颈
拆分策略
-
垂直拆分
-
垂直分库
以表为依据，根据业务不同将不同表拆分到不同的库中
特点
- 每个库的表结构都不一样
- 每个库的数据也不一样
- 所有库的并集是全量数据
-
垂直分表
以字段为依据，根据字段属性将不同字段拆分到不同表中
特点
- 每个表结构都不一样
- 每个表的数据也不一样，一般通过一列关联（主键/外键）
- 所有表并集为全量数据
-
-
水平拆分
-
水平分库
以字段为依据，以一定策略，将一个库的数据拆分到多个库中
特点
- 每个库的表结构都一样
- 每个库的数据都不一样
- 所有库的并集是全量数据
-
水平分表
以字段为依据，以一定策略，将一张表中数据拆分到多个表中
特点
- 每个表的表结构都一样
- 每个表的数据都不一样
- 所有表的并集是全量数据
-
Mycat
基于Java语言编写的MySQL数据库中间件，官网
安装配置
解压后检查依赖包mysql-connector-java版本，不能低于使用的MySQL数据库版本
分片配置
schema.xml文件配置
<!-- 逻辑结构配置 -->
<!-- 顶层逻辑库库名 -->
<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
<!-- 逻辑表名 ，该表数据节点，分片规则-->
<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
</table>
</schema>
<!-- 数据节点名，数据节点物理服务器，数据库名 -->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<!-- 数据节点物理服务器连接配置 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1"
url="jdbc:mysql://localhost:3306?useSSL=false&serverTimeZone=Asia/Shanghai&characterEncoding=utf8" user="root" password="123456">
</writeHost>
</dataHost>
server.xml文件配置
<!-- Mycat用户及用户权限 -->
<!-- root用户权限 -->
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<!-- 访问逻辑库名 -->
<property name="schemas">DB01</property>
</user>
<!-- 普通用户权限 -->
<user name="user">
<property name="password">user</property>
<property name="schemas">DB01</property>
<!-- 是否只读 -->
<property name="readOnly">true</property>
</user>
注意启动后默认占用8066端口
配置文件解析
-
schema.xml配置文件最重要的配置文件之一，涵盖Mycat逻辑库、逻辑表、分片规则、分片节点、数据源配置，主要标签有
-
scherma用于定义MyCat实例中的逻辑库，一个实例允许多个逻辑库，通过该标签来划分，等同MySql中的database概念，checkSQLschema属性定义是否允许不切换数据库直接操作其他数据的表，sqlMaxLimit为不指定limit默认查询数据量table标签定义逻辑库下的逻辑表，所有拆分的表都需要在table标签下定义属性 含义 name 定义逻辑表名，在该逻辑库下唯一 dataNote 定义逻辑表所属的dataNote，该属性需要与dataNote标签中的name对应，多个dataNote需要逗号分隔 rule 分片规则名，在 rule.xml中定义primaryKey 逻辑表对应真实表的主键 type 逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配置为global -
dataNotedataNote标签中定义了MyCat中的数据节点，也就是通常所说的数据分片。一个dataNote标签就是一个独立的数据分片
属性 含义 name 定义数据节点名称 dataHost 数据库实例主机名称，引用dataHost标签中name属性 database 定义分片所属数据库 -
datahost作为底层标签存在，直接定义了具体数据库实例、读写分离、心跳语句.
属性 含义 name 唯一标识，供上层标签使用 maxCon/minCon 最大/最小连接数 balance 负载均衡策略取值0，1，2，3 writeType 写操作分发方式【0：写操作转发到第一个writeHost，第一个挂了切换到第二个；1：写操作随机分发到配置writeHost】 dbDriver 数据库驱动，支持native，jdbc
-
-
rule.xml配置文件该文件定义所有拆分表的拆分规则，使用过程中可以灵活的使用分片算法，对同一个分片算法使用不同的参数，让它分片过程可配置化。主要包含两类
-
tableRule
表分片规则，定义根据哪个字段进行何种规则分片
-
Function
与上述属性中的规则名对应，提供分片方法映射配置文件
-
-
system.xml配置文件MyCat系统运行中环境定义和用户及用户权限定义
check属性定义为true后；用户对表的操作权限使用四位二进制数定义，顺序为增改查删，1为允许，0为禁止。如果定义表权限则使用表权限设置，不定义则使用库定义权限
MyCat分片
-
垂直分库
不同表根据标签映射到不同物理节点上
-
水平分表
根据逻辑规则将数据分摊到不同物理节点上（常用于日志均摊到各个物理节点中）
常见分片规则
-
范围规则
根据指定的字段及其配置的范围与数据节点的对应情况来决定该数据属于哪一个片区
-
取模规则
根据指定字段与节点数量进行取模运算，根据运算结果来确定数据属于哪一个片区
-
一致性Hash规则
根据字符串的哈希值来决定数据存储到哪一个片区，一样的哈希值永远落在同一个片区，不会因为增加节点而变更归属节点
-
枚举规则
根据枚举值来决定数据分布到不同节点上，本规则常见用于按照省份、性别、状态拆分数据业务
-
应用指定规则
运行阶段由应用自主决定路由到那个分片，直接根据字符子串（必须是数字）计算分片
-
固定分片Hash算法规则
类似于十进制取模运算，但是是二进制操作
-
字符串Hash解析
根据特定字段的字符串的哈希值截取片段进行分片
-
按照天分片
-
按照自然月分片
MyCat管理和监控
通过MySQL工具通过端口9066来连接MyCat管理系统
读写分离
在scherma.xml文件中的dataHost下配置写主机为主服务器，写为从库即可。并将balance修改为1
balance负载均衡属性有四种
| 值 | 含义 |
|---|---|
| 0 | 不开启读写分离机制，所有读写操作军发送到当前可用的writeHost上【会自动忽视readHost】 |
| 1 | 全部的readHost与备用的writeHost都参与select语句的负载均衡（主要针对双主双从模式） |
| 2 | 所有的读写操作都随机在writeHost，readHost上分发 |
| 3 | 所有的读请求随机分发到writeHost对应的readHost执行writeHost不负担读压力 |
双主双从架构
设定两个主库互为对方从库即可
浙公网安备 33010602011771号