MySQL

学习视频：https://www.bilibili.com/video/BV1Kr4y1i7ru/

数据库就是存放数据的仓库，负责存储、管理和检索数据。

学习要求：

1. 能根据业务场景独立设计库表
2. 熟练编写 SQL 语句

基础篇

MySQL概述

数据库相关概念

名称	解释	简称
数据库	保存有组织的数据的容器（通常是一个文件或一组文件）	DataBase（DB）
数据库管理系统	操纵和管理数据库的大型软件	DataBase Management System（DBMS）
SQL	操作关系型数据库，定义了一套操作关系型数据库的统一标准	Structured Query Language（SQL）

• 通常用‘数据库’来指代数据库管理系统，这是不对的。

什么是关系型数据库？

关系型数据库是依据关系模型来创建的数据库，简单来说关系就是二维表格。

MySQL的安装

MySQL官方提供两个版本：

社区版（MySQL Community Server）	商业版（MySQL Enterprise Edition）
免费，MySQL不提供任何技术支持	收费，官方提供技术支持

Windows启动/停止服务：

// 管理员启动cmd
// 启动
net start mysql80
// 关闭
net stop mysql80

SQL

SQL通用语法

1. SQL语句可以单行也可以多行书写，以分号结尾。
2. SQL语句可以使用空格缩进来增强语句的可读性。
3. MySQL数据库的SQL语句不区分大小写，关键字建议大写。
4. 注释：
   1. 单行注释：--注释内容或#注释内容（MySQL特有）
   2. 多行注释：/*注释内容*/

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象（数据库，表，字段）
DML	Data Manipulation Language	数据库操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据库查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据库控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

数据库操作

• 查询

  查询所有数据库

  SHOW DATABASES;

  查询当前数据库

  SELECT DATABASE();

• 删除

  DROP DATABASE[IF EXISTS] 数据库名;

• 使用

  USE 数据库名;

• 创建

  • CHARSET:指定数据库采用的字符集
  • COLLATE：指定数据库的排序规则

  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

表操作

• 查询

  1. 查询当前数据库所有的表

     SHOW TABLES;

  2. 查询表结构

     DESC 表名;

  3. 查询指定表的建表语句

     SHOW CREATE TABLE 表名;

• 创建

  CREATE TABLE 表名(
  	字段1 字段1类型[ COMMENT 字段1注释],
      字段2 字段2类型[ COMMENT 字段2注释]
  )[ COMMENT 表注释];

• 修改

  • 添加字段

    ALTER TABLE 表名 ADD 字段名 类型 [COMMENT 注释] [约束];

  • 修改

    -- 1.修改数据类型
    ALTER TABLE 表名 MODIFY 字段名 新数据类型;
    
    -- 2.修改字段名和字段类型
    ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 [COMMENT 注释] [约束];

  • 删除字段

    ALTER TABLE 表名 DROP 字段名;

  • 修改表名

    ALTER TABLE 表名 RENAME TO 新表名;

• 删除

  -- 删除表
  DROP TABLE[ IF EXISTS] 表名;
  -- 删除指定表，并重新创建该表
  TRUNCATE TABLE 表名;

DML

• 添加数据

  -- 给指定字段添加数据
  INSERT INTO 表名(字段1，字段2,...) VALUES(值1,值2,...);
  -- 给全部字段添加数据
  INSERT INTO 表名 VALUES(值1,值2,...);
  -- 批量添加数据
  INSERT INTO 表名(字段1,字段2,...) VALUES(值1,值2,...),(值1,值2,...);

  注意：

  • 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
  • 字符串和日期型数据应该包含在引号中
  • 插入的数据大小，应该在字段的规定范围内。

• 修改数据

  UPDATE 表名 SET 字段1=值1,字段2=值2,...[ WHERE 条件];

• 删除数据

  DELETE FROM 表名[ WHERE 条件];

DQL

DQL语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数

select 查询列表					7
from 表1 别名					 1
连接类型 join 表2 别名			  2
on 连接条件						3
where 筛选条件					4
group by 分组列表				5
having 分组后的筛选条件			 6
order by 排序列表				8
limit 起始条目索引,条目数;		 9

基本查询

• 查询多个字段

  SELECT 字段1,字段2,... FROM 表名;
  -- 尽量不要写用 * ，1. 不直观 2. 影响效率
  SELECT * FROM 表名;

• 设置别名

  SELECT 字段1[AS 别名1], 字段2[AS 别名2]... FROM 表名；

  • AS 可以省略

• 去除重复记录

  SELECT DISTINCT 字段列表 FROM 表名;

  • 当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个字段，并且distinct只能放到所有字段的前面
  • distinct不会去除空值，会对Null去重

条件查询

- 语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;

条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<>或!=	不等于
BETWEEN…AND…	在摸个范围之内（含最小与最大）
IN(…)	在in之后的列表中的值，多选一
LIKE 占位符	模糊匹配（_匹配单个字符，%匹配多个字符）
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 ||	或者（多个条件任意一个成立）
NOT 或 !	非，不是

聚合函数

将一列数据作为一个整体，进行纵向计算。

常见聚合函数：

函数	作用
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法：

SELECT 聚合函数(字段列表) FROM 表名;

• NULL值不参与所有聚合函数的运算，count（*）都为null都会计算。

分组查询

语法：

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

• 分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段没有任何意义，可能会报错。

排序查询

语法：

SELECT 字段列表 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;

排序方式：

ASC：升序（默认值）

DESC：降序

• 如果多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

• 起始索引从0开始，起始索引 = （查询页码 - 1）*每页显示记录数。
• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

DCL

用来管理数据库用户、控制数据库的访问权限。

管理用户

• 查询用户

  SELECT * FROM mysql.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 '用户名'@'主机名';

注意：

1. 多个权限使用逗号进行分隔
2. 授权时，数据库名和表名可以使用*进行通配，代表所有。

函数

函数是指一段可以直接被另一端程序调用的程序或代码。

字符串函数

函数	功能
CONCAT(S1,S2,…Sn)	字符串拼接
LOWER(str)tr	转小写
UPPER(str)	转大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，到达n个字符串长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，到达n个字符串长度
TRIM(str)	去除字符串头部与尾部的空格
SUBSTRING(str,start,len)	返回子串

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数0< RAND() <1
ROUND(x,y)	求参数x的四舍五入，保留y位小数

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回date1和date2之间的天数

-- 往后7个月
select date_add(now(),INTERVAL 7 MONTH);

流程控制函数

函数	功能
IF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1,vaule2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [resl] … ELSE [ default] END	如果val1为true，返回res1，…否则返回default默认值0
CASE [ expr ] WHEN [ val1 ] THEN [resl] … ELSE [ default] END	如果expr的值等于val1，返回res1，否则返回default默认值

-- 例1：
SELECT
    CASE id
    WHEN 0 THEN
        '00'
    WHEN 1 THEN
        '11'
    ELSE
        'OTHERS'
    END AS id_new
FROM
    test

-- 例2：
SELECT
    CASE
    WHEN id < 3 THEN
        '<3'
    WHEN id >= 3
    AND id < 5 THEN
        '>=3 && <5'
    ELSE
        '>=5'
    END AS id_new
FROM
    test

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据。保证数据库中数据的正确、有效和完整性。

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束（8.0.16版本以后）	保证字段满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY
自增		AUTO_INCREMENT

外键约束

语法：

-- 添加外键
-- 1
CREATE TABLE 表名(
	字段名 数据类型,
    ...
    [CONSTRAINT 约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);

-- 2
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 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 外键名称 FOREIGN KEY(外键名称) REFERENCES 主表(主表列名) ON UPDATE 行为 ON DELETE 行为;

多表查询

多表关系

一对多（多对一）

多对多

一对一

多表查询概述

指从多张表中查询数据

• 笛卡尔积：笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。（在多表查询时，需要消除无效的笛卡尔积）

多表查询的分类

• 连接查询
  • 内连接：查询两张表交集部分数据
  • 外连接
    • 左外连接：查询左表所有数据，以及两张表交集部分数据
    • 右外连接：查询右表所有数据，以及两张表交集部分数据
  • 自连接：当前表与自身的连接查询，自连接必须使用表别名
• 子查询

内连接

语法：

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;

外连接

语法：

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

联合查询

就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表1...
UNION [ALL]
SELECT 字段列表 FROM 表2...;

• UNION ALL ：查询结果直接合并
• UNION ：查询结果去重
• 列数与字段类型要保持一致

子查询

SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

根据查询结果不同，可以分为：

• 标量子查询（子查询结果为单个值）

  子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询。

  常用的操作符：> 、< 、= 、!=

• 列子查询（子查询结果为一列）

  子查询返回的结果是一列，这种子查询称为列子查询。

  常用的操作符：IN、NOT IN、ANY、SOME、ALL

  操作符	描述
  IN	在指定的集合范围之内，多选一
  NOT IN	不在指定的集合范围之内
  ANY	子查询返回列表中，有任意一个满足即可
  SOME	与ANY等同，使用SOME的地方都可以使用ANY
  ALL	子查询返回列表的所有值都必须满足

• 行子查询（子查询结果为一行）

  子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。

  常用的操作符有：= 、<> 、 IN 、NOT IN

  例：

  select * from emp where (salary,managerid) = (select salary,managerid from emp where name = 'z');

• 表子查询（子查询结果为多行多列）

  子查询返回的结果是多行多列，这种子查询称为表子查询

  常用的操作符：IN

事务

事务的简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

• 默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

事务操作

方式一

• 查看/设置事务提交的方式

  -- 1 为自动提交， 2 为手动提交
  -- 查看事务提交方式
  SELECT @@autocommit;
  -- 设置事务提交方式
  SET @@autocommit = 0;

• 提交事务

  COMMIT;

• 回滚事务

  ROLLBACK [TO 回滚点];

• 设置回滚点

  SAVEPOINT 回滚点名;

方式二

• 开启事务

  START TRANSACTION;
  -- 或
  BEGIN;

• 提交事务

  COMMIT;

• 回滚事务

  ROLLBACK [TO 回滚点];

• 设置回滚点

  SAVEPOINT 回滚点名;

事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致的状态。(一致的状态是数据库在事务执行前后满足预定义规则和约束、符合业务逻辑要求，并且数据完整且没有损坏的状态。)
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称为不可重复读。
幻读	一个事务执行条件查询数据时，没有对应的数据行，但是在插入数据时，这行数据已经存在，好像出现了幻影。

事务隔离级别

隔离级别	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

• 从上到下，隔离级别逐渐变高，性能逐渐变差

• 查看事务隔离级别

  SELECT @@TRANSACTION_ISOLATION;

• 设置事务隔离级别

  SET [SESSION|GLOBAL] TRANSACTION IOSLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

  • SESSION ：当前窗口有效
  • GLOBAL：全部窗口有效
  • 默认是Repeatable Read

进阶篇

存储引擎

MySQL体系结构

• 连接层

  最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

• 服务层

  第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。

• 引擎层

  存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

• 存储层

  数据存储层， 主要是将数据(如: redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上，并完成与存储引擎的交互。

存储引擎简介

存储引擎是MySQL特有的，存储引擎就是存储数据、建立索引。更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

1. 在创建表时，指定存储引擎

   CREATE TABLE 表名(
       字段1 字段1类型 [COMMENT 字段1注释],
       ...
       字段n 字段n类型 [COMMENT 字段n注释]
   )ENGINE=INNODB [COMMENT 表注释];

2. 查看当前数据库支持的存储引擎

   SHOW ENGINES;

存储引擎特点

InnoDB

• 介绍

InnoDB是一种兼顾高可靠和高性能的通用存储引擎，在MySQL5.5之后，InnoDB是默认的存储引擎。

• 特点

1. DML操作遵循ACID模型，支持事务；
2. 行级锁，提高并发访问性能；
3. 支持外键FOREIGN KEY约束，保证数据的完整性和正确性；

• 文件

xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都对应这样一个表空间文件，存储该表结构（frm、sdi）、数据和索引。

参数：innodb_file_per_table(决定是否多张表共用一个表空间)

InnoDB逻辑存储结构

MyISAM

• 介绍

MyISAM是MySQL早期的默认存储引擎。

• 特点

1. 不支持事务，不支持外键
2. 支持表锁，不支持行锁
3. 访问速度快

• 文件

xxx.sdi:存储表结构数据

xxx.MYD:存储数据

xxx.MYI:存储索引

Memory

• 介绍

Memory引擎的表数据存储在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存表使用。

• 特点

1. 内存存放
2. hash索引（默认）

• 文件

xxx.sdi:存储表结构信息

数据存在内存中

存储引擎特点

存储引擎选择

在选择存储引擎时，因该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB：是MySQL的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是合适的。（使用不高，基本使用MongoDB）
• MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。（使用不高，基本使用Redis）

索引

索引概述

• 介绍

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

• 优缺点

优势	劣势
提高数据检索的效率，降低数据库的IO成本	索引列也是要占用空间的
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低

索引结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种

索引结构	描述
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+Tree

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

根节点：只存储key、指针（每个指针都会指向一页），而指针永远都会比key多一个—–>叶子节点：1.所有的元素都会出现在叶子节点中并形成一个单向链表（mysql中为双向链表）2.叶子节点才会存储数据

• Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

特点

1. Hash索引只能用于对等比较（=，in），不支持范围查询（between，>,<,…）
2. 无法利用索引完成排序操作
3. 查询效率高，通常只需要一次检索，效率通常要高于B+tree索引

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的重复值	可以有多个	UNIQUE
常规索引	快速定位特殊值	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引放到一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则：

1. 如果存在主键，主键索引就是聚集索引。
2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
3. 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

例子

索引语法

• 创建索引

  -- 关联一个字段为单列索引，多个字段为联合索引
  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执行频率

  MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

  SHOW GLOBAL STATUS LIKE 'Com_______';
  --七个'_'模糊匹配

• 慢查询日志

  慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒) 的所有SQL语句的日志。

  MySQL的慢查询日志默认没有开启。

  -- 查看慢查询信息
  show variables like 'slow_%';
  -- 开启慢查询
  set [session|global] slow_query_log=ON;
  -- 设置慢查询时限(/秒)
  set long_query_time=xx;

  配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf中。

• profile详情

  show profiles 能在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作

  -- 查询是否支持
  SELECT @@have_profiling;
  -- 默认profiling是关闭的，可以通过set语句在session/global级别开启profiling
  -- 查看profiling
  SELECT @@profiling;
  SET profiling=1;
  
  
  -- 查看每一条SQL的耗时基本情况
  show profiles;
  -- 查看指定query_id的SQL语句各个阶段的耗时情况
  show profile for query <query_id>;
  -- 查看指定query_id的SQL语句CPU使用情况
  show profile cpu for query  <query_id>;

• explain执行计划

  EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。

  语法：

  # 直接在select语句之前加上关键字explain/desc
  EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

  explain执行计划

  EXPLAIN执行计划各字段含义：

  • id

    select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。

  • select_type

    表示查询的类型。

  • type

    查询使用了何种类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all

    NULL:不涉及表操作

    system：系统表，记录只有一行

    const：只索引一次

    eq_ref : 主键查询，唯一性索引查询

    ref：非唯一性索引查询

    all：全表扫描

  • possible_key

    表示在MySQL中通过哪些索引，能让我们在表中找到想要的记录，一旦查询涉及到的某个字段上存在索引，则索引将被列出，但这个索引并不定一会是最终查询数据时所被用到的索引。

  • key

    key是查询中实际使用到的索引，若没有使用索引，显示为NULL

  • key_len

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

  • rows

    MySQL认为必须要执行查询的行数，在InnoDB引擎的表中，是一个估计值，可能并不总是准确的,一般情况下 rows 值越小越好。

  • filtered

    表示返回结构的行数占需读取行数的百分比，filtered的值越大越好

  • Extra

    额外信息

史上最详细的mysql底层和explan type和type中index和all的区别_explain type index-CSDN博客

索引使用

• 最左前缀法则

  如果索引了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

  如果跳过某一列，索引将部分失效（后面的字段索引失效）

• 范围查询

  联合索引中，出现范围查询（>,<）,**范围查询右侧的列索引失效**（>=或<=不失效）

• 索引列运算

  不要在索引列上进行运算操作，索引将失效。

• 字符串不加引号

  字符串类型字段使用时，不加引号，索引失效

• 模糊查询

  如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

  ... like '软件%';  # 索引不失效
  ... like '%软件';  # 索引失效

• or连接条件

  用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及索引都不会用到。

• 数据分布影响

  如果MySQL评估使用索引比全表更慢，则不使用索引。

• SQL提示

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

  -- use index 建议索引
  explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
  -- ignore index 忽略索引
  explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
  -- force index 强制索引
  explain select * from tb_user force index(idx_user_pro)where profession='软件工程';

• 覆盖索引

  尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少select *。

  • using index condition：查找使用了索引，但是需要回表查询数据
  • using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据。

• 前缀索引

  该字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

  • 语法

  create index idx_xxx on table_name(column(n));
  -- n 表示截取字段的前几个字符构建索引

  • 前缀长度

    可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的总记录数的比值，索引选择性越高则查询效率越高，唯一性索引的选择性是1，这是最好的索引选择性，性能也是最好的。

    select count(distinct substring(<column>,1,n)/count(*) from table_name;

• 单列&联合索引

  单列索引：即一个索引只包含单个列

  联合索引：即一个索引包含了多个列

  在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引。

索引设计原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的 代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储NULL值，请在创建表示使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好确定哪个索引最有效地用于查询。

SQL优化

insert优化

• 大批量插入数据

  如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

  

主键优化

• 数据组织方式

  在InnoDB存储引擎中，表数据都是根据组件顺序组织存放的，这种存储方式的表称为索引组织表（index organized table ，IOT）。

• 页分裂、页合并

  https://www.bilibili.com/video/BV1Kr4y1i7ru/?p=90

• 主键设计原则

  1. 在满足业务需求的情况下，尽量降低主键的长度。
  2. 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
  3. 尽量不要使用uuid做主键或者是其他自然主键，如身份证号。
  4. 业务操作时，避免对主键进行修改

order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接放回排序结果的排序都叫FileSort排序。
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

group by优化

• 在分组操作时，可以通过索引来提高效率。
• 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

count优化

数据量比较大时耗时，可以自己计数优化

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且改索引不能失效，否则会从行锁升级为表锁。

视图/存储过程/触发器

介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视

图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作

就落在创建这条SQL查询语句上。

语法

• 创建

  CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

• 查询

  -- 查看创建视图语句
  show create view 视图名称;
  -- 查看视图数据
  select * from 视图名称 ...;

• 修改

  -- 方式一
  CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] 
  -- 方式二
  ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

• 删除

  DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

MySQL中的锁，按照锁的粒度分，分为以下三类：

• 全局锁：锁定数据库中的所有表。

• 表级锁：每次操作锁住整张表。

• 行级锁：每次操作锁住对应的行数据。

全局锁

介绍

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

语法

加全局锁

-- 全局锁，所有数据库处于只读状态，其他操作均阻塞
flush tables with read lock;

释放全局锁

unlock tables;

特点

数据库中加全局锁，是一个比较重的操作，存在以下问题：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。

• 如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction 参数来完成不加锁的一致性数据备份。

mysqldump --single-transaction -uroot -p123456 test > test.sql

表级锁

介绍

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

• 表锁

• 元数据锁（meta data lock，MDL）

• 意向锁

表锁

对于表锁，分为两类：

1. 表共享读锁（read lock）
2. 表独占写锁（write lock）

语法：

1. 加锁：lock tables 表名… read/write。
2. 释放锁：unlock tables / 客户端断开连接。

特点

A. 读锁

左侧为客户端一，对指定表加了读锁，不会影响右侧客户端二的读，但是会阻塞右侧客户端的写

B. 写锁

左侧为客户端一，对指定表加了写锁，会阻塞右侧客户端的读和写。

结论: 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

元数据锁

meta data lock, 元数据锁，简写MDL。

MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

• 这里的元数据，大家可以简单理解为就是一张表的表结构。

• 在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（SHARED_READ/SHARED_WRITE）（共享）；当对表结构进行变更操作的时候，加MDL写锁（EXCLUSIVE）（排他）。

查看元数据锁：

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

美团一面：能不能通俗的解释下为什么要有意向锁这个东西？-51CTO.COM

• 意向共享锁(IS): 由语句select … lock in share mode添加 。 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥。

• 意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。

查看意向锁及行锁加锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

行级锁

介绍

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在

InnoDB存储引擎中。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的

锁。对于行级锁，主要分为以下三类：

• 行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。

• 间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

• 临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。 在RR隔离级别下支持。

行锁

介绍：

InnoDB实现了以下两种类型的行锁：

• 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

两种锁的兼容性：

常见的SQL语句，在执行时，所加的锁：

查看意向锁与行锁的加锁情况：

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

间隙锁&临建锁

InnoDB引擎

MySQL管理

运维篇

日志

主从复制

分库分表

读写分离