https://time.geekbang.org/column/article/75173?screen=full

Mysql 锁分为 全局锁、表级锁、行锁

一、全局锁

对整个数据库实例加锁，使处于只读状态。阻塞语句包括：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构）和更新类型的事务的提交语句。

1
2
3
4

# 加锁(FTWRL)
flush tables with read lock
# 解锁
unlock tables;

场景：做全库逻辑备份，对于全部是 InnoDB引擎的库，可以选择使用 –single-transaction 参数（mysqldump时导数据前会启动一个事务，来确保拿到一致性视图。只适用于所有的表使用事务引擎的库）

一、大量短连接的问题

短连接一般指的是，连接到数据库后，执行很少的 SQL 语句后就断开。下次需要的时候再重连。但是 MySQL 在建立连接的过程中，成本是很高的。除了正常的网络连接三次握手外，还需要做登陆权限判断和获得这个连接的数据读写权限。

MySQL 的 max_connections 参数用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示 Too many connections。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

遇到大量短连接的场景，如果只是单纯调高 max_connections 的值，是不行的。因为设置 max_connections 这个参数的目的就是想保护 MySQL，如果把他改的太大，让更新的连接进来之后，系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

这里有两种方法，但是都是有损的。

1. 先处理掉那些占着连接但不工作的线程

max_connections 的计算是，只要客户端和服务端是连接着的就占用一个计数位置。

MySQL 相关的误删数据，有这样几种分类：

• 使用 delete 语句误删数据行
• 使用 drop table 或者 truncate table 语句误删数据表
• 使用 drop database 语句误删数据库

一、B 树

B 树也称为 B- 树，在一棵M阶B树中,下图是一棵阶数为3的树。B 树的定义：

• 每个节点最多有 M-1 个关键字
• 根节点最少可以只有一个关键字
• 非根节点最少有 M/2 个关键字

一、稀疏索引

对主文件中部分记录（形成的索引字段值），有索引项和它对应，这样的索引称为 稀疏索引.

比如 1、2、3、4、5、6、7 。稀疏索引的做法是将这 6 个值分组，1、2、3 和 4、5、6 和 7 分为不同的 3 组，取这三组中最小的索引值作为索引记录中的索引值，抽象索引记录如下：

• 1 -> 到顺序存储 1、2、3 的起始位置的指针
• 4 -> 到顺序存储 4、5、6 的起始位置的指针

一、explain 的使用

Explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL。使用方法：在 SQL 语句前面加上 explain 运行就可以了。

1
2
3
4
5
6

mysql> explain select * from user where name = '6';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | name_index    | name_index | 363     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+

1
2
3
4
5
6
7
8
9

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

我们在 city 字段上加了索引，而 name 字段并没有索引。有如下的查找语句：

1

select city,name,age from t where city='杭州' order by name limit 1000;

MySQL 支持前缀索引，也就是说可以定义字符串的一部分作为索引。默认情况下，创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

举个例子，我们的数据库表中有一个字段存储的邮箱地址，类型是字符串。

1
2
3
4

alter table t add index index_1(email);

// 对于每个记录只是取前 6 个字节作为索引
alter table t add index index_2(email(6));

前缀索引只取了字符串的一部分字节，因此索引占用的内存空间会变小，这是使用前缀索引的优势。但是可能会增加额外的记录扫描次数，因为可能多个字符串索引的前缀是相同的。

InnoDB索引模型

InnoDB 使用 B+ 树索引模型，数据存储在 B+ 树中，每一个索引在 InnoDB 里面对应一棵 B+ 树。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

• 主键索引：也是聚簇索引，叶子节点存的是整行数据
• 非主键索引：也称为 二级索引，叶子节点存的是主键的值。也就是说，基于非主键索引的查询需要多扫描一颗索引树。