读mysql8 CookBook的零碎整理5——mysql 比较好的书籍

最新推荐文章于 2022-08-17 11:14:44 发布

мг丶one

最新推荐文章于 2022-08-17 11:14:44 发布

阅读量498

点赞数 1

分类专栏： mysql 文章标签：数据库 mysql优化

本文链接：https://blog.csdn.net/he_yanghui/article/details/119382831

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1.mysql 必知必会链接：https://pan.baidu.com/s/1fjQt6T7RPXm4AvYgiiSakw 提取码：6uxl
2.高性能mysql 链接：https://pan.baidu.com/s/1YxiAuz1AMfLh0o5iPGI3Hg 提取码：v2w8
3.mysql8 CoodBook 链接：https://pan.baidu.com/s/1aqEkAi6uVUQ-hkI-XauPCw 提取码：x4f4
这三本书都不错。1和3 是 mysql 基础性知识比较多，高性能mysql 偏数据库管理，有很多从环境设备等多方面提升性能比较难啃，不过里面的索引之类的值得一看，很是经典

mysql8 CoodBook 接上次的继续零散总结
事务
5.3 使用保存点
使用保存点可以回滚到事务中的某些点,而且无须中止事务。你可以使用SAVEPOINT标识符为事务设置名称，并使用ROLLBACK To标识语句将事务回滚到指定的保存点而不中止事务。

BEGIN;
SELECT balance INTo@a.bal FROM account WHERE account_number='A';
UPDATE account SET balance=@a.bal-100 WHERE account_number='A';
UPDATE account SET balance=balance+100 WHERE account_number='B';
SAVEPOINTtransfer_to_b;
SELECT balance INTo @a.bal FROM account WHERE account_number='A';
UPDATE account SET balance=balance+100 WHERE account_number='C';
ROLLBACK TO transfer_to_b;
 -- 由于没有行被更新，意味着没有账户'C'，所以可以将事务回滚至成功传输到B的保存点(SAVEPOINT)。
-- 然后'A '将得到从c扣除的100美元。如果不想使用保存点，  则应在两个交易中执行这些操作。

锁
有以下两种类型的锁:
·

内部锁:MySQL在自身服务器内部执行内部锁,以管理多个会话对表内容的争用。
·外部锁:MySQL为客户会话提供选项来显式地获取表锁,以阻止其他会话访问表。

内部锁又可以分为下面两种类型：

行级锁:行级锁是细粒度的。只有被访问的行会被锁定。这允许通过多个会话同时进行写访问,使其适用于多用户、高度并发和OLTP的应用程序。只有InnoDB支持行级锁。
表级锁:MySQL对 MyISAM、MEMORY 和MERGE表使用表级锁，一次只允许一个会话更新这些表。这种锁定级别使得这些存储引擎更适用于只读的或以读取操作为主的或单用户的应用程序。

外部锁:可以使用LOCK TABLE和 UNLOCK TABLES语句来控制锁定。
READ和 WRITE的表锁定解释如下。

READ:当一个表被锁定为READ时,多个会话可以从表中读取数据而不需要获取锁。此外，多个会话可以在同一个表上获得锁,这就是为什么READ锁也被称为共享锁。当READ锁被保持时，没有会话可以将数据写入表格中（包括持有该锁的会话)。如果有任何写入尝试,该操作将处于等待状态,直到READ锁被释放。
WRITE:当一个表被锁定为WRITE时,除持有该锁的会话之外，其他任何会话都不能读取或向表中写入数据。除非现有锁被释放,否则其他任何会话都不能获得任何锁。这就是为什么WR工TE锁被称为排他锁。

如果有任何读取/写入尝试，该操作将处于等待状态，直到WRITE锁被释放。
当执行UNLOCK TABLES语句时或当会话终止时,所有锁都会被释放。

-- 锁定表的语法如下:
mysql>LOCK TABLES table_name [READ | WRITE]
-- 要解锁表,请使用:
mysql>UNLOCK TABLES;
-- 要锁定所有数据库中的所有表，请执行以下语句。在获取数据库的一致快照时需要使用该语句，
-- 它会冻结对数据库的所有写入操作:
mysql> FLUSH TABLES WITH READ LOCK;

锁队列

除共享锁(一个表可以有多个共享锁）之外，没有两个锁可以一起加在一个表上。如果一个表已经有一个共享锁，此时有一个排他锁要进来，那么它将被保留在队列中，直到共享锁被释放。当排他锁在队列中时，所有后续的共享锁也会被阻塞并保留在队列中。
当工nnoDB从表中读取/写人数据时会获取元数据锁。如果第二个事务请求
wRITELOCK，该事务将被保留在队列中，直到第一个事务完成。如果第三个事务想要读取数据，就必须等到第二个事务完成。

-- 事务1:
mysql> BEGIN;
mysql> SELECT * FROM employees LIMIT 10;
-- 请注意COMMIT未执行,该事务仍保持开放状态。
--事务2:
mysql>LOCK TABLE employees WRITE;
-- 该语句必须等到事务1完成才会被执行。
-- 事务3:
mysql> SELECT * FROM employees LIMIT 10;
-- 事务3不会给出任何结果，因为队列中有排他锁(它正在等待事务2完成操作)，而且它阻塞了该表上的所有操作。

性能调优：

-- 使用 EXPLAN
EXPLAIN SELECT dept_name FROM dept_emp 
JOIN employees ONdept_emp.emp_no=employees.emp_no JOIN departments ON
departments. dept_no=dept_emp.dept_no WHERE employees.first_name='Aamer'
-- 使用 EXPLAN JSON
EXPLAIN FORMAT=JSON SELECT dept_name FROM dept_emp 
JOIN employees owNdept_emp.emp_no=employees.emp_no JOIN departments ON
departments.dept_no=dept_emp.dept_no WHERE employees.first_name='Aamer'
-- 添加降序索引
mysql>ALTER TABLE employees ADD INDEX name_desc(first_name AsC,last_name DESC) ;