1.MySQL体系结构与存储引擎

MySQL体系结构由 Client Connectors 层、MySQL Server 层及存储引擎层组成。

接下来我们用一条 SQL SELECT 语句的执行轨迹来说明客户端与 MySQL 的交互过程，如下图所示。

在 MySQL 5.6 版本之前，默认的存储引擎都是 MyISAM，但 5.6 版本以后默认的存储引擎就是 InnoDB 了。

2.MySQL 锁分类

InnoDB 中的锁：在 MySQL InnoDB 存储引擎中，锁分为行锁和表锁。其中行锁包括两种锁。

1⃣️ 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

2⃣️ 排他锁(X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

排查 InnoDB 锁问题通常有 2 种方法。

打开 innodb_lock_monitor 表，注意使用后记得关闭，否则会影响性能。

在 MySQL 5.5 版本之后，可以通过查看 information_schema 库下面的 innodb_locks、innodb_lock_waits、innodb_trx 三个视图排查 InnoDB 的锁问题。

InnoDB 死锁

互斥条件：一个资源每次只能被一个进程使用;

请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放;

不剥夺条件：进程已获得的资源，在没使用完之前，不能强行剥夺;

循环等待条件：多个进程之间形成的一种互相循环等待资源的关系。

建议：

加锁顺序一致;

尽量基于 primary 或 unique key 更新数据。

单次操作数据量不宜过多，涉及表尽量少。

减少表上索引，减少锁定资源。

相关工具：pt-deadlock-logger。

场景：

session1 首先拿到 id=1 的锁，session2 同期拿到了 id=5 的锁后，两者分别想拿到对方持有的锁，于是产生死锁。

session1 和 session2 都在抢占 id=1 和 id=6 的元数据的资源，产生死锁。

查看 MySQL数据库中死锁的相关信息，可以执行 show engine innodb status\G 来进行查看，重点关注 “LATEST DETECTED DEADLOCK” 部分。

给大家一些开发建议来避免线上业务因死锁造成的不必要的影响。

更新 SQL 的 where 条件时尽量用索引;

加锁索引准确，缩小锁定范围;

减少范围更新，尤其非主键/非唯一索引上的范围更新。

控制事务大小，减少锁定数据量和锁定时间长度 (innodb_row_lock_time_avg)。

加锁顺序一致，尽可能一次性锁定所有所需的数据行。

高性能数据库表该如何设计?

(1)必须指定默认存储引擎为 InnoDB，并且禁用 MyISAM 存储引擎，随着 MySQL 8.0 版本的发布，所有的数据字典表都已经转换成了 InnoDB，MyISAM 存储引擎已成为了历史。

(2)默认字符集 UTF8mb4，以前版本的 UTF8 是 UTF8mb3，未包含个别特殊字符，新版本的 UTF8mb4 包含所有字符，官方强烈建议使用此字符集。

(3)关闭区分大小写功能。设置 lower_case_tables_name=1，即可关闭区分大小写功能，即大写字母 T 和小写字母 t 一样。

规范命名

命名规范如下，命名时的字符取值范围为：a~z，0~9 和 _(下画线)。

(1)所有表名小写，不允许驼峰式命名;

(2)允许使用 -(横线)和 (空格);如下图所示，当使用 -(横线)，后台默认会转化成 @002d;

(3)不允许使用其他特殊字符作为名称，减少潜在风险。

禁用列为 NULL。

原因：

MySQL 难以优化 NULL 列;

NULL 列加索引，需要额外空间;

含 NULL 复合索引无效。

浮点数与定点数区别

浮点数：float、double(或 real)。

定点数：decimal(或 numberic)。

从上图中可以观察到：

(1)浮点数存在误差问题;

(2)尽量避免进行浮点数比较;

(3)对货币等对精度敏感的数据，应该使用定点数。

动力节点在线课程涵盖零基础入门，高级进阶，在职提升三大主力内容，覆盖Java从入门到就业提升的全体系学习内容。全部Java视频教程免费观看，相关学习资料免费下载!对于火爆技术，每周一定时更新!如果想了解更多相关技术，可以到动力节点在线免费观看MySQL数据库视频教程学习哦!