MySQL

存储引擎：
查看 MySQL 提供的所有存储引擎：

  1. mysql> SHOW engines;
  3. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  4. | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
  5. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
  6. | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
  7. | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
  8. | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
  9. | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
  10. | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
  11. | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
  12. | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
  13. | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
  14. | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
  15. +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

查看默认的存储引擎：

  1. mysql> SHOW VARIABLES LIKE '%storage_engine%';
  3. +---------------------------------+-----------+
  4. | Variable_name                   | Value     |
  5. +---------------------------------+-----------+
  6. | default_storage_engine          | InnoDB    |
  7. | default_tmp_storage_engine      | InnoDB    |
  8. | disabled_storage_engines        |           |
  9. | internal_tmp_mem_storage_engine | TempTable |
  10. +---------------------------------+-----------+

查看表的引擎：

  1. SHOW TABLE STATUS LIKE '<table_name>';

MyISAM 和 InnoDB 的区别：

  • 是否支持 MVCC（Multi-Version Concurrency Control）：

    仅 InnoDB 支持。应对高并发事务，MVCC 比单纯的加锁更高效； MVCC 只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作； MVCC 可以使用乐观锁和悲观锁来实现，各数据库中 MVCC 实现并不统一

字符集及校对规则：
查看字符编码设置：

  1. mysql> SHOW VARIABLES LIKE '%character%';
  3. +--------------------------+--------------------------------+
  4. | Variable_name            | Value                          |
  5. +--------------------------+--------------------------------+
  6. | character_set_client     | utf8mb4                        | # 客户端来源数据使用的字符集
  7. | character_set_connection | utf8mb4                        | # 连接层字符集
  8. | character_set_database   | utf8mb4                        | # 当前选中数据库的默认字符集
  9. | character_set_filesystem | binary                         |
  10. | character_set_results    | utf8mb4                        | # 查询结果字符集
  11. | character_set_server     | utf8mb4                        | # 默认的内部操作字符集
  12. | character_set_system     | utf8                           | # 系统元数据（字段名等）字符集
  13. | character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
  14. +--------------------------+--------------------------------+

查看数据库支持的所有字符集（charset）:

  1. mysql>  show character set;
  3. +----------+---------------------------------+---------------------+--------+
  4. | Charset  | Description                     | Default collation   | Maxlen |
  5. +----------+---------------------------------+---------------------+--------+
  6. ......
  7. | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
  8. | utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
  9. +----------+---------------------------------+---------------------+--------+

查看校验规则：

  1. mysql> SHOW COLLATION;

查看当前字符集和校验规则设置：

  1. mysql> SHOW VARIABLES LIKE 'collation_%';
  3. +----------------------+--------------------+
  4. | Variable_name        | Value              |
  5. +----------------------+--------------------+
  6. | collation_connection | utf8mb4_0900_ai_ci |
  7. | collation_database   | utf8mb4_0900_ai_ci |
  8. | collation_server     | utf8mb4_0900_ai_ci |
  9. +----------------------+--------------------+

校验规则特征：

  • 两个不同的字符集不能有相同的校对规则；
  • 每个字符集有一个默认校对规则
  • 存在校对规则命名约定：以其相关的字符集名开始，中间包括一个语言名，并且以_ci（case insensitive）（case insensitive）、_cs（case sensitive）或_bin（将字符串中的每一个字符用二进制数据存储，区分大小写）结束。

索引：
MySQL索引使⽤的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝⼤多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余⼤部分场景，建议选择BTree索引。
MySQL的BTree索引使⽤的是B树中的B+Tree，但对于主要的两种存储引擎的实现⽅式是不同的。

  • MyISAM：

    B+Tree叶节点的data域存放的是数据记录的地址。 在索引检索的时候，⾸先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。 这被称为“⾮聚簇索引”。

  • InnoDB：

    其数据⽂件本身就是索引⽂件。 相⽐MyISAM，索引⽂件和数据⽂件是分离的，其表数据⽂件本身就是按B+Tree组织的⼀个索引结构，树的叶节点data域保存了完整的数据记录。 这个索引的key是数据表的主键，因此InnoDB表数据⽂件本身就是主索引。 这被称为“聚簇索引（或聚集索引）”。⽽其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值⽽不是地址，这也是和MyISAM不同的地⽅。 在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再⾛⼀遍主索引。 因此，在设计表的时候，不建议使⽤过⻓的字段作为主键，也不建议使⽤⾮单调的字段作为主键，这样会造成主索引频繁分裂。

事务的四大特性：

  • 原子性（Atomicity）
  • 一致性（Consiitency）
  • 隔离性（Isolation）
  • 持久性（Druability）

并发事务的问题：

  • 脏读（Dirty read）
  • 丢失修改（Lost to midifty）
  • 不可重复读（Unpreatable read）
  • 幻读（Phantom read）

事务隔离级别：

  • 读未提交（READ-UNCOMMITTED）
  • 读已提交（READ_COMMITTED）
  • 可重复度（REPEATABLE-READ）
  • 可串行化（SERIALIZABLE）：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰

MySQL InnoDB 默认支持的隔离级别：

  1. mysql> SELECT @@transaction_isolation;
  3. +-------------------------+
  4. | @@transaction_isolation |
  5. +-------------------------+
  6. | REPEATABLE-READ         |
  7. +-------------------------+

与 SQL 标准不同的地⽅在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使⽤的是Next-Key Lock 锁算法，因此可以避免幻读的产⽣ InnoDB 存储引擎在分布式事务的情况下一般会用到可串行化（SERIALBLE）隔离级别

表级锁和行级锁对比：

  • 表级锁：

    MySQL中锁定 粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。 其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，MyISAM和 InnoDB引擎都⽀持表级锁

  • 行级锁：

    MySQL中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁

InnoDB 存储引擎的锁的算法：

  • Record Lock：当个行记录上锁
  • Gap Lock：间隙锁，锁定一个范围，不包括记录本身
  • Next-key Lock：Record + Gap 锁定一个范围，包含记录本身

相关：

  • InnoDB 对于行的查询使用 Next-key Lock
  • Next-key Lock 为了解决幻读问题（Phantom Problem）
  • 当查询的索引含有唯一属性时，将Next-key Lock 降级为 Record Lock
  • Gap 锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
  • 由两种方式显式关闭 Gap 锁（除了外键约束和唯一性检查外，其余情况仅使用 Record Lock）：
    • A：将事务隔离级别设置为 READ-COMMITED
    • B：将参数 innodb_locks_unsafe_for_binlog设置为 1

数据分片的常见方案：
客户端代理：

分⽚逻辑在应⽤端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当⽹的 Sharding-JDBC 、阿⾥的TDDL是两种⽐᫾常⽤的实现。

中间件代理：

在应⽤和数据中间加了⼀个代理层。分⽚逻辑统⼀维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、⽹易的DDB等等都是这种架构的实现。

数据库连接池：

数据库连接本质就是⼀个 socket 的连接。 数据库服务端还要维护⼀些缓存和⽤户权限信息之类的 所以占⽤了⼀些内存。 我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重⽤这些连接。 为每个⽤户打开和维护数据库连接，尤其是对动态数据库驱动的⽹站应⽤程序的请求，既昂贵⼜浪费资源。 在连接池中，创建连接后，将其放置在池中，并再次使⽤它，因此不必建⽴新的连接。 如果使⽤了所有连接，则会建⽴⼀个新连接并将其添加到池中。 连接池还减少了⽤户必须等待建⽴与数据库的连接的时间。

全局 ID：

  • UUID：

    不适合作为主键，因为太⻓了，并且⽆序不可读，查询效率低。⽐᫾适合⽤于⽣成唯⼀的名字的标示⽐如⽂件的名字。

  • 数据库自增 ID：

    两台数据库分别设置不同步⻓，⽣成不重复ID的策略来实现⾼可⽤。这种⽅式⽣成的 id 有序，但是需要独⽴部署数据库实例，成本⾼，还会有性能瓶颈。

  • 利用 Redis 生成 ID：

    性能比较好，灵活⽅便，不依赖于数据库。但是，引⼊了新的组件造成系统更加复杂，可⽤性降低，编码更加复杂，增加了系统成本。

  • Twitter 的 Snowflake 算法

  • 美团的 Leaf 分布式 ID 生成系统：

    Leaf 是美团开源的分布式ID⽣成器，能保证全局唯⼀性、趋势递增、单调递增、信息安全，⾥⾯也提到了⼏种分布式⽅案的对⽐，但也需要依赖关系数据库、Zookeeper等中间件。

SQL 语句执行：

MySQL 高性能优化建议：

慢 SQL 原因：