mysql的基本数据类型
数值类型
如果想要设置某个字段(这里用年龄来表示，年龄不能为负数)可以用sql这样来表示
CREATE TABLE user_table(
id int PRIMARY KEY not null auto_increment,
age TINYINT UNSIGNED,
user_name VARCHAR(10)
)
- Paimary key 表示这个字段设置为主键
- TINYINT UNSIGNED 表示这个字段为TINYINT类型 且范围为0-255
- not null 表示这个字段不能为空
- auto_increment 表示设置这个字段自动增长
字符串类型
日期类型
mysql表操作
添加字段
ALTER TABLE cs ADD sex char(1) not null;
ALTER TABLE 表名称ADD 添加字段名称 类型 ;
修改字段
ALTER TABLE cs MODIFY sex VARCHAR(1) NOT NULL;
ALTER TABLE 表名称 MODIFY 修改字段名称 类型;
修改字段名称
ALTER TABLE cs CHANGE sex user_sex VARCHAR(1) NOT NULL;
ALTER TABLE 表名称CHANGE 旧的字段名称 新的字段名称 类型；
删除字段
ALTER TABLE cs DROP user_sex;
ALTER TABLE 表名称DROP 字段名称;
修改表名称
ALTER TABLE cs RENAME TO user_table;
ALTER TABLE 旧的表名称RENAME TO 新的表名称;
删除表数据
TRUNCATE TABLE user_table;
TRUNCATE TABLE 表名称;
执行该命令会把表删除 然后重新创建一张表出来 但是只留下表结构
删除表
DROP TABLE user_table;
DROP TABLE 表名称;
mysql语句
查询表数据
SELECT * FROM user_table;
SELECT 查询出表的字段，*表示为全部 FROM 表名称;
增加表数据
INSERT INTO user_table (`id`, `age`, `user_name`, `sex`) VALUES (1, 1, 'zhansan', '');
INSERT INTO 表名称 (表字段) VALUES (添加的对应字段的值);
修改表数据
UPDATE user_table SET `age` = 20, `user_name` = 'zhansan' WHERE `id` = 1;
UPDATE 表名称 SET 修改的字段名 = 修改的值WHERE 对应该行的字段 = 该行存在的字段的值;
删除表数据
DELETE FROM user_table WHERE id = 1
DELETE FROM 表名称WHERE 对应该行的字段 = 该行存在的字段的值
sql基础查询
SELECT
字段列表
-- 聚合查询
COUNT(列名) 统计记录数
MAX(列名) 最大值
MIN(列名) 最小值
AVG(列名) 平均值
SUM(列名) 求和
FROM
表名称
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
示例
----测试数据
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (12, 'zhansan', '男');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (15, 'lisi', '男');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (21, 'wangwu', '女');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (22, 'zhansan1', '男');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (30, 'zhansan2', '女');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (18, 'zhansan3', '男');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (40, 'lisi1', '女');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (52, 'lisi2', '男');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (66, 'wangwu1', '女');
INSERT INTO user_table(`age`, `user_name`, `sex`) VALUES (43, 'wangwu2', '男');
查询表有多少条数据
SELECT count(*) FROM user_table
查询表age字段大于30的有多少条数据
SELECT count(*) FROM user_table WHERE age > 30
----查询表中sex字段最大值
SELECT max(age) as age FROM user_table
----sex最小值
SELECT min(age) as age FROM user_table
----sex平均值
SELECT avg(age) as age FROM user_table
----sex总和
SELECT sum(age) as age FROM user_table
查询性别男女的数量
SELECT sex,count(*) FROM user_table GROUP BY sex
查询age大于30岁的男女数量
SELECT sex,count(*) FROM user_table WHERE age > 30 GROUP BY sex
查询age大于20的男女数量且数量大于3的数据
SELECT sex,count(*) FROM user_table WHERE age > 20 GROUP BY sex HAVING count(*) > 3
根据age排序
---升序 升序asc可以省略不写
SELECT * FROM user_table ORDER BY age asc
---降序
SELECT * FROM user_table ORDER BY age DESC
分页查询
----0为索引 2为显示的条数
SELECT * FROM user_table LIMIT 0,2;
查询sex为21和22的数据
SELECT * FROM user_table WHERE FIND_IN_SET(age,'21,22')
查询名字为四个字的数据
----like条件下有几个_表示有几个字符
SELECT * FROM user_table WHERE user_name LIKE '____'
查询年龄在20-40之间
SELECT * FROM user_table WHERE age BETWEEN 20 AND 40;
mysql用户管理
mysql用户表放在mysql数据库下的user表
创建用户并且设置密码
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456'
test为用户名
localhost为主机 如果需要设置用户可以访问任意主机可以设置为%
123456为设置的密码
修改用户密码
ALTER USER 'test'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456789'
删除用户
DROP USER 'test'@'localhost'
权限控制
查询权限
SHOW GRANTS FOR 'test'@'localhost'
test用户名 localhost主机
查询结果是该用户现在没有其他权限 只能够登录数据库而已
授予访问指定数据库权限
GRANT ALL ON test.* TO 'test'@'localhost'
test.*表示授予test这个数据库里面的所有表
撤销权限
REVOKE ALL ON test.* FROM 'test'@'localhost'
函数
字符串函数
SELECT CONCAT('my','sql') --Mysql
SELECT LOWER('MYSQL') --mysql
SELECT UPPER('mysql') --MYSQL
SELECT LPAD('hello',10,'mysql') --mysqlhello
SELECT RPAD('hello',9,'mysql') --hellomysq
SELECT TRIM(' hello mysql ') --hellomysql
SELECT SUBSTRING('mysql',3,5) --sql 索引从1开始
数值函数
SELECT CEIL(1.1) ---2
SELECT FLOOR(1.9) ---1
SELECT MOD(3,2) ---1
SELECT RAND() ---0.7149837256548774(每次出现的数字都不一样)
SELECT ROUND(3.145,2)---3.15
----随机生成6位数数字
SELECT lpad(ROUND(RAND()*1000000,0),6,'0')
日期函数
SELECT DATE_ADD('2022-07-01',INTERVAL 6 DAY) --2022-07-07 INTERVAL->间隔 6->天数 DAY->类型
SELECT DATEDIFF('2022-07-01','2022-06-01') --30
流程函数
SELECT case WHEN 10>5 THEN '大于5' ELSE '小于5' END ---大于5
约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据。为了保证数据库中数据的正确、有效性和完整性。
CREATE TABLE test_table(
id int PRIMARY KEY auto_increment, ----设置主键 自动增长
age int NOT NULL CHECK(age > 0 && age <= 120), ----设置不能为空 检查约束
user_name VARCHAR(10) NOT NULL UNIQUE, ----设置不能为空 不能重复
user_status CHAR(1) DEFAULT('1') ----设置默认值
)
外键 保证数据的一致性和完整性
----用户表(user_table)字段(id,user_name,age,sex,dept_id)
----部门表(dept_table)字段(id,dept_name)
----创建外键
ALTER TABLE user_table ADD CONSTRAINT fk_user_dept_id FOREIGN KEY (dept_id) REFERENCES dept_table(id)
ALTER TABLE 表名ADD CONSTRAINT 外键名称FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
当user_table表的dept_id有dept_table表id相同的id对应的数据 这个时候就不能删除dept_id这条id对应的数据，因为存在这条记录存在该外键 如果dept_table表id字段的某一条id user_table表没有存在该id对应的用户 这时候如果想删除dept_table表这条id的记录就可以删除
----删除外键
ALTER TABLE user_table DROP FOREIGN KEY fk_user_dept_id
ALTER TABLE 表名DROP FOREIGN KEY 外键名称
ALTER TABLE user_table ADD CONSTRAINT fk_user_dept_id FOREIGN KEY (dept_id) REFERENCES dept_table(id) ON UPDATE CASCADE ON DELETE SET NULL
ALTER TABLE 表名ADD CONSTRAINT 外键名称FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE SET NULL
ON UPDATE 表示主表id如果修改了 那么dept_id对应的一些id数据就会自动执行CASCADE条件(更新跟主表id相同的id)
ON DELETE 表示主表id这条数据删除了 那么dept_id对应的一些id数据就会自动执行SET NULL条件(主表id对应数据删除不存在，对应的dept_id设置为null，前提是dept_id字段需要设置可以为null)
多表查询
一对多关系
多对多关系
内连接
隐式内连接
select a1.id,a1.age,a1.user_name,a1.sex,a2.dept_name from user_table a1,dept_table a2 where a1.dept_id = a2.id
select 子段列表from 表1 ,表2 where 条件
显示内连接
SELECT a1.id,a1.age,a1.user_name,a1.sex,a2.dept_name FROM user_table a1 INNER JOIN dept_table a2 on a1.dept_id = a2.id
select 子段列表from 表1 Inner join 表2 on 条件
如果不满足条件的数据将不会被查询出来
外连接
左外连接：left join 相当于查询左表的所有数据包含2张表交集部分的数据
SELECT a1.id,a1.age,a1.user_name,a1.sex,a2.dept_name FROM user_table a1 LEFT JOIN dept_table a2 ON a1.dept_id = a2.id
select 子段列表from 表1 left join 表2 on 条件
右外连接：right join 相当于查询右表的所有数据包含2张表交集部分的数据
SELECT a1.id,a1.age,a1.user_name,a1.sex,a2.dept_name FROM user_table a1 right JOIN dept_table a2 ON a1.dept_id = a2.id
select 子段列表from 表1 right join 表2 on 条件
自连接
自连接查询 可以为内连接查询，也可以外连接查询
Select 字段列表 from 表1 别名A join 表2 别名2 on 条件
----示例
select a1.name,a2.name from user_table a1,user_table a2 where a1.teacher_id = a2.id
select a1.name,a2.name from user_table a1 left join user_table a2 on a1.teacher_id = a2.id
联合查询
把多次查询的结果合并起来，形成一个新的查询结果集
示例
select * from user_table WHERE age < 20
SELECT * FROM user_table WHERE sex = '男'
上面两条sql分别执行
使用union 或者 union all合并
----使用union all合并
select * from user_table WHERE age < 20
UNION ALL
SELECT * FROM user_table WHERE sex = '男'
发现有一些相同的数据 使用union 去重
----使用union 去重合并
select * from user_table WHERE age < 20
UNION
SELECT * FROM user_table WHERE sex = '男'
子查询
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT的任何一个。
标量子查询
子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询。常用的操作符: = <> > >= < <=
----示例
SELECT * FROM user_table WHERE dept_id = (SELECT id FROM dept_table WHERE dept_name = '开发部');
列子查询
子查询返回的结果是一列（可以是多行）
----示例
SELECT * FROM user_table WHERE dept_id = ANY (SELECT id FROM dept_table WHERE dept_name = '开发部' OR dept_name = '运维部');
SELECT * FROM user_table WHERE dept_id in (SELECT id FROM dept_table WHERE dept_name = '开发部' OR dept_name = '运维部');
行子查询
子查询返回的结果是一行(可以是多列)
----示例
SELECT * FROM user_table WHERE (dept_id,dept_type) = (SELECT id,type FROM dept_table WHERE dept_name = '开发部');
表子查询
子查询返回的结果是多行多列
----示例
SELECT * FROM user_table WHERE (dept_id,dept_type) = any (SELECT id,type FROM dept_table WHERE dept_name = '开发部' OR dept_name = '运维部');
select a1.* FROM (SELECT * FROM user_table WHERE sex = '男') a1 LEFT JOIN dept_table a2 ON a1.dept_id = a2.id
事务
事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提 交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
----查询事务提交方式 1表示自动提交 0表示手动
SELECT @@autocommit;
----设置手动提交
SET @@autocommit = 0;
----开启事务
START TRANSACTION 或者 BEGIN
----提交事务
COMMIT;
----回滚事务
ROLLBACK;
操作事务
示例
测试用户一减去10 用户二加上10
---正常结果
UPDATE transaction_table set num = num - 10 WHERE user_name = 'zhansan';
UPDATE transaction_table set num = num + 10 WHERE user_name = 'lisi';
----模拟异常情况
UPDATE transaction_table set num = num - 10 WHERE user_name = 'zhansan';
异常
UPDATE transaction_table set num = num + 10 WHERE user_name = 'lisi';
----使用事务处理
----开启事务
START TRANSACTION;
UPDATE transaction_table set num = num - 10 WHERE user_name = 'zhansan';
异常
UPDATE transaction_table set num = num + 10 WHERE user_name = 'lisi';
出现异常 但是没有提交结果
使用ROLLBACK回滚
----正常情况使用事务
START TRANSACTION;
UPDATE transaction_table set num = num - 10 WHERE user_name = 'zhansan';
UPDATE transaction_table set num = num + 10 WHERE user_name = 'lisi';
没有出现异常 但是没有更新数据
使用commit提交事务
得到想要结果
如果不用START TRANSACTION来操作事务也可以设置mysql的@@autocommit设置手动提交 出现异常执行回滚 没有出现异常再提交也可以 (推荐使用事务进行操作)
事务的四大特性
- 原子性(Atomicity) :事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性(Consistency) :事务完成时，必须使所有的数据都保持一致状态。
- 隔离性(Isolation) :数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability) :事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
事务并发问题
- 脏写：此时数据库的值为”数据0“，事务一、事务二和事务三同时对数据进行修改，并且修改成功了，但是只有一个事务能够得到自己修改的数据，其余的被覆盖更新了，这就是脏写。
- 脏读：此时数据库的值为”数据0“，事务二开始事务想要修改为”数据2“，但是还没提交，这是事务一和事务三进行查询的操作，得到的返回值竟然是事务二想要修改的值，如果事务二这时候进行事务回滚操作，那么其余两个事务得到的值就是错误的了，这就是脏读。
- 不可重复读：此时数据库的数据为”数据0，数据2，数据3“，然后事务一进行查询操作，事务二和事务三进行修改数据的操作，修改完之后，事务一又再一次读取操作，这次读取到的数据跟第一次读取的数据不一样，这就是不可重复读。
- 幻读：幻读最容易跟不可重复读混乱了，它们的区别关键在于添加数据。此时数据库的数据为”数据0“，事务一第一次读到”数据0“，接着事务二和事务三进行添加”数据2“和”数据3“，这是事务一再一次进行读取操作，读到的数据跟第一次不一样，这就是幻读。
事务隔离级别
-
读未提交（READ UNCOMMITTED）：会出现脏读、不可重复读、幻读
-
读提交 （READ COMMITTED）：解决脏读，但是会出现不可重复读、幻读
-
可重复读 （REPEATABLE READ）：默认级别，解决脏读、不可重复读，会出现幻读
-
串行化 （SERIALIZABLE）：不会出现脏读、不可重复读、幻读
性能：读未提交>读提交>可重复读>串行化
安全性：读为提交<读提交<可重复读<串行化
----查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION
----设置事务隔离级别
----示例
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
session----当前客户端
read uncommitted---读未提交
READ UNCOMMITTED隔离级别模拟示例
设置为读未提交隔离级别
模拟同时开启两个事务
此时另一个事务进行修改操作不提交 设置读未提交事务隔离级别的客户端进行查询
可以看到 事务并没有提交 但是另一个事务已经读取到了修改后数据 这就是脏读
READ COMMITTED隔离级别模拟演示
设置隔离级别未read committed
此时另一个事务修改数据但是read committed未出现脏读的情况
事务提交之后 查询的数据就有变化 说明read committed解决了脏读的问题，但是在一个事务当中，同一条sql查询出的结果不一致，这个问题就是出现了不可重复读问题，所以read committed出现了不可重复读的问题
REPEATABLE READ隔离级别模拟演示
设置隔离级别为REPEATABLE READ（默认级别）
此时另一个事务修改数据然后提交 repeatable read隔离级别在事务中两次查询的数据还是一样的 这样就解决了不可重复读的问题
把repeatable read隔离级别的事务提交 就能查询到修改后数据了
此时2个客户端再开启事务，第一个事务查询表中没有id为3的数据 然后第二个事务插入一条id为3的数据并提交事务 此时第一个事务插入一条id为3的数据发现插入不了，因为已经存在id为3的数据，所以插入不成功，但是重新查询的时候还是显示没有存在id为3的数据，这种情况就是出现了幻读
把事务提交后就能正常的查询出来
SERIALIZABLE隔离级别模拟演示
将事务隔离级别设置为SERIALIZABLE
此时继续模拟第一个事务查询id为4的数据，然后查询出来没有数据，第二个事务插入id为4的数据，这个时候第二个事务插入不进去，因为事务堵塞了，得第一个事务提交完成后才能进行操作
等第一个事务提交结束之后，第二个事务执行操作就没有出现堵塞的情况，这样就解决了幻读的情况
事务的隔离级别越高，数据越安全，相反性能就越低
存储引擎
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。
查看当前数据库支持的存储引擎
SHOW ENGINES;
可以看到 当前数据库默认的存储引擎是innoDB
创建表的时候指定存储引擎只需要在后面加 ENGINE=指定的存储引擎即可 没有指定则默认是innoDB
示例
CREATE TABLE `transaction_table` (
`id` int NOT NULL AUTO_INCREMENT,
`user_name` varchar(50) DEFAULT NULL,
`num` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3
InnoDB存储引擎
InnoDB是事务型数据库的首选引擎，通过上图也看到了，InnoDB是目前MYSQL的默认事务型引擎，是目前最重要、使用最广泛的存储引擎。支持事务安全表（ACID），支持行锁定和外键。InnoDB主要特性有：
1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合
2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
5、InnoDB被用在众多需要高性能的大型数据库站点上
InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。
场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
MyISAM存储引擎
MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。
MyISAM主要特性有：
1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持
2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成
3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16
4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上
5、BLOB和TEXT列可以被索引，支持FULLTEXT类型的索引，而InnoDB不支持这种类型的索引
6、NULL被允许在索引的列中，这个值占每个键的0~1个字节
7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩
8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快
9、可以把数据文件和索引文件放在不同目录
10、每个字符列可以有不同的字符集
11、有VARCHAR的表可以固定或动态记录长度
12、VARCHAR和CHAR列可以多达64KB
存储格式：
1、静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。
2、动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。
3、压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。
静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。如果需要保存的内容后面本来就有空格，在返回结果的时候也会被去掉。（其实是数据类型char的行为，动态表中若有这个数据类型也同样会有这个问题）
使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。
场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。
MERGE存储引擎
MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。
场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。
MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：
1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度
2、MEMORY存储引擎执行HASH和BTREE缩影
3、可以在一个MEMORY表中有非唯一键值
4、MEMORY表使用一个固定的记录长度格式
5、MEMORY不支持BLOB或TEXT列
6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引
7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）
8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享
9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）
MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用B-+Tree型要快，但也可以使用B树型索引。由于这种存储引擎所存储的数据保存在内存中，所以其保存的数据具有不稳定性，比如如果mysqld进程发生异常、重启或计算机关机等等都会造成这些数据的消失，所以这种存储引擎中的表的生命周期很短，一般只使用一次。现在mongodb、redis等NOSQL数据库愈发流行，MEMORY存储引擎的使用场景越来越少。
场景：如果需要该数据库中一个用于查询的临时表。
ARCHIVE存储引擎
Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。
场景：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。
CSV存储引擎
使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。csv的编码转换需要格外注意。
场景：这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件，将其存放在MySQL服务器的数据目录中，服务器就能够马上读取相关的CSV文件。同样，如果写数据库到一个CSV表，外部程序也可以立刻读取它。在实现某种类型的日志记录时，CSV表作为一种数据交换格式，特别有用。
BLACKHOLE存储引擎（黑洞引擎）
该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。
场景：如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程，执行binlogdump命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽，可以单独建立一个伪的从服务器或者叫分发服务器。
PERFORMANCE_SCHEMA存储引擎
该引擎主要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
场景： DBA能够较明细得了解性能降低可能是由于哪些瓶颈。
Federated存储引擎
该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。
Federated存储引擎可以使你在本地数据库中访问远程数据库中的数据，针对federated存储引擎表的查询会被发送到远程数据库的表上执行，本地是不存储任何数据的。
缺点：
1.对本地虚拟表的结构修改，并不会修改远程表的结构
2.truncate 命令，会清除远程表数据
3.drop命令只会删除虚拟表，并不会删除远程表
4.不支持 alter table 命令
5.select count(*), select * from limit M, N 等语句执行效率非常低，数据量较大时存在很严重的问题，但是按主键或索引列查询，则很快，如以下查询就非常慢（假设 id 为主索引）
select id from db.tablea where id >100 limit 10 ;
而以下查询就很快：
select id from db.tablea where id >100 and id<150
6.如果虚拟虚拟表中字段未建立索引，而实体表中为此字段建立了索引，此种情况下，性能也相当差。但是当给虚拟表建立索引后，性能恢复正常。
7.类似 where name like “str%” limit 1 的查询，即使在 name 列上创建了索引，也会导致查询过慢，是因为federated引擎会将所有满足条件的记录读取到本地，再进行 limit 处理。
场景： dblink。
总结
| 功 能 | InnoDB | MYISAM | Memory | Archive |
|---|---|---|---|---|
| 存储限制 | 64TB | 256TB | RAM | None |
| 支持事物 | Yes | No | No | No |
| 支持全文索引 | No | Yes | No | No |
| 支持数索引 | Yes | Yes | Yes | No |
| 支持哈希索引 | No | No | Yes | No |
| 支持数据缓存 | Yes | No | N/A | No |
| 支持外键 | Yes | No | No | No |
索引
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
| 优点 | 缺点 |
|---|---|
| 提高数据检索的效率，降低数据库的I0成本 | 索引列也是要占用空间的。 |
| 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消 | 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。 |
索引结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种:
| 索引结构 | 描述 |
|---|---|
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 |
| R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
| 索引 | InnoDB | MylSAM | Memory |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-tree(空间索引) | 不支持 | 支持 | 不支持 |
| Full-text(全文索引) | 5.6版本之后支持 | 支持 | 不支持 |
索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建，只能有一个 | PARMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，有两种根据索引的存储形式
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，只能有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键, 将使用第一个唯一 (UNIQUE) 索引作为聚集索引。
- 如果表没有主键, 或没有合适的唯一索引， 则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
二级索引指向的也是索引
例如如果要查询一张表里面指定某一个用户名称的全部信息 假设这个名称不是主键 所以mysql先会去走二级索引，二级里面的b+树存放的是这个用户名的主键 这时候取到主键，然后走聚合索引，聚合索引里面的b+树叶子节点存放的是这一行的数据，那这个时候就就可以拿到这个用户的全部信息了 这个操作也叫回表查询
回表操作就是先到二级索引找到对应的主键值 再根据拿到的主键值到聚集索引拿到对应的行数据
索引语句
1.PRIMARY KEY（主键索引）
ALTER TABLE 表名称 ADD PRIMARY KEY ( 字段 )
2.UNIQUE(唯一索引)
ALTER TABLE 表名称 ADD UNIQUE (字段 )
3.INDEX(普通索引)
ALTER TABLE 表名称ADD INDEX index_name ( 字段)
4.FULLTEXT(全文索引)
ALTER TABLE 表名称ADD FULLTEXT ( 字段)
5.多列索引
ALTER TABLE 表名称ADD INDEX index_name ( 字段1, 字段2, 字段3)
增加普通索引和UNIQUE索引
create index 索引名称on 表名称(字段) ;
create index 索引名称on 表名称(字段1, 字段2, 字段3) ;
create unique index 索引名称 on 表名称(字段) ;
删除索引
drop index 索引名称 on 表名称;
alter table 表名称drop index 索引名称;
alter table 表名称drop primary key ;
性能分析
查看执行频率
通过以下语句可以分析[session | global] 查看当前数据库的增删改查的访问频率
SHOW GLOBAL STATUS LIKE 'Com_______'
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_ query_ time, 单位:秒,默认10秒)的所有SQL语句的日志。
mysql默认没有开启
查看mysql是否开启慢日志
SHOW VARIABLES LIKE 'slow_query_log'
可以在mysql配置文件里面开启
#开启mysql慢日志开关
slow_query_log=1
#设置慢日志的时间 sql执行时间超过设置时间，将视为慢日志，记录慢日志
long_query_time=3
show profiles
查询sql语句的耗时时间
show profiles
SHOW PROFILE FOR QUERY 69 //69--->query_id
通过query_id可以看到每条语句的执行时间
如果想要同时查看cpu的使用情况
SHOW PROFILE cpu FOR QUERY 100 //100--->query_id
explain
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
在查询语句前面加上explain或者desc就可以查看了
EXPLAIN SELECT id FROM dept_table WHERE dept_name = 'xxx'
-
id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从.上到下; id不同，值越大,越先执行。
-
select_type：表示SELECT的类型，常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
-
type：表示连接类型,性能由好到差的连接类型为NULL、system、 const、 eq_ _ref、 ref、 range、 index、 all 。
不查询表 出现NULL 查询系统的表出现system 查询主键或者唯一值出现const 非唯一索引出现ref index表示用了索引，但是遍历了索引 出现all表示全表扫描(这个性能就比较低)
-
possible_keys：显示应用在这张表上的索引 一个或者多个或者为null
-
key：实际用到的索引，如果为null，就是没有使用索引
-
key_len：表示索引中使用的字节数， 该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下，长度越短越好。
-
ref： ref列显示使用哪个列或常数与key一起从表中选择行。
-
rows：MySQL认为必须要执行查询的行数,在innodb引擎的表中，是一个估计值，可能并不总是准确的。
-
filtered：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
-
Extra： 包含MySQL解决查询的详细信息。
索引失效情况
如果索引了多列(联合索引) , 要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列, 索引将部分失效(后面的字段索引失效)。
----联合索引顺序 sex age phone
where sex=xx and age = xx and phone=xxx ----这个时候就正常走联合索引
where sex=xx and age=xx ----正常走联合索引
where sex = xx and phone = xxx ----正常走联合索引,因为最左边的列存在，但是查询phone字段索引失效了,因为中间间隔了age字段索引
where age=xxx and phone=xxx and sex=xxx ----三个字段正常走索引，因为最左边的字段存在，跟查询的顺序无关
where age=xx and phone = xx ----索引失效
where phone=xxx ----索引失效
如果在使用联合索引的时候，查询提交某个字段使用了>或者<，范围查询将导致右侧列索引失效，即后面的字段索引将失效
----联合索引顺序 sex age phone
where sex=xxx and age>xx and phone = xxx ----phone字段索引将失效
where sex=xx and age >= xxx and phone = xxx ----全部走联合索引
如果在索引列上使用函数运算操作，索引将失效
如果索引列是字符串，使用的查询的时候没有添加单引号，索引也将失效
在模糊查询的情况下 如果前面模糊匹配的话，索引将失效，后面模糊匹配的话，索引不失效，前后模糊的话，索引失效
例如
where email like '%@qq.com' ----这种情况索引失效
where email like '10000%' ----这种情况索引不失效
where email like '%@qq%' ----这种情况索引失效
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
where id = x or age = xx ----没有用到索引，age没有索引，使用id索引也失效
如果MySQL评估使用索列比全表更慢，则不使用索引。
sql提示
SQL提示，是优化数据库的一个重要手段,简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
use index：表示建议数据库用你指定的索引名称，如果其中另一个索引性能比较快，数据库还是会使用另一个索引
ignore index：表示数据库忽略指定的索引名称
force index：表示数据库强制指定的索引名称
----假如有一个单列索引(idx_age) age 联合索引(idx_age_sex)age sex
select * from user_table where age = xx ----走(idx_age_sex)索引
select * from user_table use index(idx_age)where age = xx ---走(idx_age) 索引
select * from user_table ignore index(idx_age)where age = xx ---走(idx_age_sex)索引
select * from user_table force index(idx_age)where age = xx ----走(idx_age)索引
覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列，在该索引中已经全部能够找到) , 减少select *。
using index condition:查找使用了索引，但是需要回表查询数据
using where; using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
----user_table表有id,name,sex,age,email字段 id为主键
----联合索引(idx_sex_age) sex,age
select * from user_table where id = x; ----走聚集索引
select id ,sex,age from user_table where sex = x and age = x ---走二级索引，但是查询的id，sex，age已经在二级找到，所以不进行回表查询
select id,sex,age,email from user_table where sex = x and age = x ---走二级索引,但是二级索引没有email字段数据,需要进行回表查询
select * from user_table where sex = x and age = x ---走二级索引,需要进行回表查询
索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引。
- 针对于 常作为查询条件(where) 、排序(order by)、分组(group by)操作的字段建立索引。
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高,使用索引的效率越高。
- 如果是字符串类型的字段,字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表,提高查询效率。
- 要控制索引的数量，索引并不是越多越好，索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
sql优化
插入语句优化
尽量使用批量插入
---示例
insert into user_table(sex,age,phone) values('男',20,'15555555555'),('男',21,'18888888888')
插入时间分配
链接耗时 （30%）
发送query到服务器 （20%）
解析query （20%）
插入操作 （10% * 词条数目）
插入index （10% * Index的数目）
关闭链接 （10%）
可以看出单条语句，会在链接、解析部分耗费大量时间，因此我们大多时候会采用批量插入来提升插入速度，争取在一次链接中尽可能多的写入数据
插入大批量数据
如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。
连接数据库 加上参数–local-infile
mysql --local-infile -uroot -p
查看local-infile开关是否开启
select @@local_infile;
如果查出来的结果是0，就是没有开启，如果是1就是开启了
如果没有开启local_infile开关的话，设置全局参数local-infile为1，开启从本地加载文件导入数据的开关 开启之后才能加载本地的文件到数据库的表结构中
set global local_infile = 1
执行load指令将准备好的数据加载到表结构中
load data local infile 文件路径 into table 表名称 fields terminated by ‘每一个字段使用什么符号分割(一般是逗号)’ lines terminated by ‘每一行使用什么分割(一般是\n)’
usersql.sql文件内容
1,zhansan,21,15555555555
2,lisi,22,18888888888
3,wangwu,23,13333333333
----示例
load data local infile '/log/usersql.sql' into table `user_table` fields terminated by ',' lines terminated by '\n'
主键最好按顺序插入，顺序插入的性能要高于乱序插入
主键优化
插入数据时，尽量选择顺序插入，选择使用AUTO_ INCREMENT自增主键。
尽量降低主键的长度（如果二级索引比较多，因为二级索引存储的是主键id，主键长度比较长，将会占用大量的磁盘空间，搜索的时候将耗费大量的磁盘io）
尽量不要使用UUID做主键或者是其他自然主键，如身份证号，因为这样插入的时候是乱序的，可能会存在页分裂的现象，因为这些长度相对较长，使用在查询的时候也会消耗大量的磁盘io
尽量不要修改主键
order by优化
- Using filesort :通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
- Using index :通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,不需要额外排序,操作效率高。
----示例
----表结构 id age user_name sex dept_id
建议一个联合索引
CREATE INDEX idx_age_sex on user_table(age,sex);
执行sql
EXPLAIN SELECT id,age,sex FROM user_table ORDER BY age;
此时是正常的 如果执行以下语句
EXPLAIN SELECT id,age,sex FROM user_table ORDER BY sex;
因为联合索引是age、sex，order by是根据sex排序，违背了最左原则 所以出现了using filesort，如果返回的字段包含了除了联合索引和id外的字段，那就表示没有走联合索引，走的全表扫描，就会出现using filesort
执行以下语句
EXPLAIN SELECT id,age,sex FROM user_table ORDER BY age,sex;
可以看到出现using index，这种效率高一点，因为都清楚 如果order by后如果没有加asc默认是asc升序的，如果此时有其中一个字段改为desc的话，会出现以下情况
EXPLAIN SELECT id,age,sex FROM user_table ORDER BY age,sex desc;
可以看到 出现了using filesort 因为建的联合索引没有指定哪一个字段是升序 哪一个字段是降序，索引默认是升序
可以创建一个联合索引(其中age还是给他升序，sex降序)
CREATE INDEX idx_age_sex_ad on user_table(age,sex desc);
可以看到sex的Collation 已经是D(desc)降序了 现在继续执行刚刚的sql语句
使用的是刚刚创建的idx_age_sex_ad索引，出现了using index
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
- 尽量使用覆盖索引。
- 多字段排序,一个升序一个降序, 此时需要注意联合索引在创建时的规则(ASC/DESC) 。
- 如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort_ buffer_ size(默认256k)。
limit优化
在大数据的情况下，比如当我们用 limit 1000000, 10 的时候，MySQL会先扫描满足条件的1000010行，扔掉前面的1000000行，返回后面的10行。所以offset越大的时候，扫描的行就越多，效率也就越慢了，这时候建议用覆盖索引加子查询的形式来进行优化
update优化(避免行锁升级成表锁)
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。
示例
先查看一下当前表的数据和索引
模拟俩个事务进行update操作
可以看到 这个时候根据查询指定的id去执行update语句是正常的，因为id有索引 所以锁住的是行锁，其他事务操作其他行没有出现问题，这个时候继续测试 根据指定的user_name去执行update语句
这个时候可以看到 因为第一个事务执行了update语句，而且这个表没有user_name索引，索引执行成功之后就升级成了表锁，这个时候第二个事务执行update的时候就失败了，只有当第一个事务提交后第二个事务才能正常执行
怎么避免将行锁升级成表锁 因为修改的user_name字段表中没有这个字段的索引，所以才会出现这种情况，这个时候可以创建一个user_name字段索引来解决这个问题
CREATE INDEX idx_username on user_table(user_name);
继续模拟刚刚的事务操作
这个时候就可以看到同时执行查新指定user_name字段进行update语句的修改没有跟刚才的情况一样，把行锁升级成表锁了，因为是行锁，所以如果修改查询的字段已经在另一个事务修改了，但是这个事务还没有提交的情况下，这时这条数据是行锁的情况下，其他事务是不能对这条数据进行修改操作的
锁
MySQL中的锁，按照锁的粒度分，分为以下三类：
- 全局锁：锁定数据库中的所有表。
- 表级锁：每次操作锁住整张表。
- 行级锁： 每次操作锁住对应的行数据。
全局锁
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。
其典型的使用场景是做全库的逻辑备份,对所有的表进行锁定,从而获取一致性视图,保证数据的完整性。
使用全局锁
flush tables with read lock;
数据备份
控制器执行语句
mysqldump -usql用户账号 -psql用户密码 备份的数据库名称 > 备份存放路径
备份完成后释放锁
unlock tables;
在InnoDB引擎中，可以在备份时加上参数–single-transaction参数来完成不加锁的一致性数据备份 。
mysqldump --single-transaction -usql用户账号 -psql用户密码 备份的数据库名称 > 备份存放路径
表级锁
表级锁,每次操作锁住整张表。锁定粒度大,发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、 BDB等存储引擎中。
表锁
加锁 lock tables 表名称 read/write；
lock tables user_table read ----user_table表加读锁
lock tables user_table write ----user_table表加写锁
-
表共享读锁(read lock)
加了读锁，不能加写锁，其他事务只能读不能写，加了读锁不会阻塞事务的读操作，但是会阻塞写操作
-
表独占写锁(wirte lock)
当前客户端加了写锁，当前客户端可以进行读操作，也可以进行写操作，但是其他客户端不能进行读写操作
释放锁
unlock tables;
元数据锁
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性,在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
查看metadata lock的加锁信息
select * from performance_schema.metadata_locks;
可以看到第一个客户端执行了一条查询user_table表语句，此时查询metadata_lock表，多了一条user_table的加锁记录，加锁类型为SHARED_READ,且状态是已授予（GRANTED)。查询不加锁，是指不在表上加innodb行锁。
此时进行执行了一条修改语句，查询metadata_lock表，多了两条加锁记录，加锁类型为SHARED_WRITE，状态是GRANTED
SHARED_READ和SHARED_WRITE不互斥，但是与EXCLUSIVE互斥
两个客户端同时开启了事务，第一个客户端修改了表结构，这个时候就出现阻塞情况，查看metadata_lock表，看到了多了条user_table表加锁记录，加锁类型为EXCLUSIVE
意向锁
为了避免DML在执行时，加的行锁与表锁的冲突,在InnoDB中引入了意向锁,使得表锁不用检查每行数据是否加锁,使用意向锁来减少表锁的检查。
- 意向共享锁(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的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。
行锁
锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持。
- 共享锁(S) :允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
- 排他锁(X) :允许获取排他锁的事务更新数据,阻止其他事务获得相同数据集的共享锁和排他锁。
| sql | 行锁类型 | 说明 |
|---|---|---|
| INSERT… | 排他锁 | 自动加锁 |
| UPDATE… | 排他锁 | 自动加锁 |
| DELETE… | 排他锁 | 自动加锁 |
| SELECT… | 不加任何锁 | |
| SELECT…LOCK IN SHARE MODE | 共享锁 | 需要手动在SELECT语句后面加LOCK IN SHARE MODE |
| SELECR…FOR UPDATE | 排他锁 | 需要手动在SELECT语句后面加FOR UPDATE |
间隙锁
锁定索引记录间隙(不含该记录)，确保索引记录间隙不变,防止其他事务在这个间隙进行insert,产生幻读。在RR隔离级别下都支持。
- 索引上的等值查询(唯一索引), 给不存在的记录加锁时,优化为间隙锁。
- 索引上的等值查询(普通索引),向右遍历时最后一一个值不满足查询需求时，next-key lock退化为间隙锁。
- 索引上的范围查询(唯一索引) 会访问到不满足条件的第一个值为止。
间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。
临键锁
行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。
22万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
