Mysql

1. Mysql 存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

使用 show engines; 命令可以查看当前数据库支持的存储引擎。
默认的存储引擎是 InnoDB。

2. 索引

2.1 索引概述

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构。索引的作用就相当于书的目录。打个比方: 我们在查字典的时候，如果没有目录，那我们就只能一页一页的去找我们需要查的那个字，速度很慢。如果有目录了，我们只需要先去目录里查找字的位置，然后直接翻到那一页就行了。索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构。

优点：使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
缺点：创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。索引需要使用物理文件存储，也会耗费一定空间。

2.2 索引底层数据结构

2.2.1 B 树 && B+ 树

B 树也称 B-树,全称为 多路平衡查找树 ，B+ 树是 B 树的一种变体。B 树和 B+树中的 B 是 Balanced （平衡）的意思。目前大部分数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 作为索引结构。

B 树& B+树两者有何异同呢？

• B 树的所有节点既存放键(key) 也存放数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
• B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
• B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。
• 在 B 树中进行范围查询时，首先找到要查找的下限，然后对 B 树进行中序遍历，直到找到查找的上限；而 B+树的范围查询，只需要对链表进行遍历即可。综上，B+树与 B 树相比，具备更少的 IO 次数、更稳定的查询效率和更适于范围查询这些优势。

2.2.2 Hash 索引

哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

但是！哈希算法有个 Hash 冲突 问题，也就是说多个不同的 key 最后得到的 index 相同。通常情况下，我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后 HashMap 为了减少链表过长的时候搜索时间过长引入了红黑树。

2.2.3 红黑树

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，
它具有以下特点：

• 每个节点非红即黑；根节点总是黑色的；
• 每个叶子节点都是黑色的空节点（NIL 节点）；
• 如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
• 从任意节点到它的叶子节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

和 AVL 树不同的是，红黑树并不追求严格的平衡，而是大致的平衡。正因如此，红黑树的查询效率稍有下降，因为红黑树的平衡性相对较弱，可能会导致树的高度较高，这可能会导致一些数据需要进行多次磁盘 IO 操作才能查询到，这也是 MySQL 没有选择红黑树的主要原因。也正因如此，红黑树的插入和删除操作效率大大提高了，因为红黑树在插入和删除节点时只需进行 O(1) 次数的旋转和变色操作，即可保持基本平衡状态，而不需要像 AVL 树一样进行 O(logn) 次数的旋转操作。

红黑树的应用还是比较广泛的，TreeMap、TreeSet 以及 JDK1.8 的 HashMap 底层都用到了红黑树。对于数据在内存中的这种情况来说，红黑树的表现是非常优异的。

2.2.4 为什么选择 B+树？

红黑树是二叉树，相对于二叉树层级更少，搜索效率高。

对于 B 树，无论叶子节点还是非叶子节点都会保存数据，导致一页中存储键值减少，指针减少，要保存大量数据只能增加树的高度，导致性能下降。

对于哈希索引，只能进行等值查询，无法进行范围查询。B+树支持范围匹配和排序，适合数据库索引。

2.3 索引分类

主键索引：主键索引是一种唯一索引，不允许有重复的值，一个表只能有一个主键索引。
唯一索引：唯一索引是一种索引，不允许有重复的值，但允许有空值。可以有多个唯一索引。
普通索引：普通索引是最基本的索引，没有任何限制。
全文索引：全文索引是对文本的索引，只能在 MyISAM 表上创建。

在 Innodb 中根据索引的存储方式，可以分为：
聚集索引：聚集索引的叶子节点存储了整行数据，Innodb 的主键索引就是聚集索引。
二级索引：二级索引的叶子节点存储了主键的值，通过主键值可以找到整行数据。（回表查询）

2.4 索引语法

2.4.1 创建索引

语法： create [unique|fulltext] index 索引名 on 表名(字段名,字段名...);

create index idx_user_name on user(name);

2.4.2 删除索引

drop index idx_user_name on user;

2.4.3 查看索引

show index from user;

2.5 性能分析

2.5.1 查看 sql 执行频率

show global status like 'Com_______';

2.5.2 慢查询日志

慢查询日志是 MySQL 用来记录查询时间超过指定时间的 SQL 语句，可以通过慢查询日志来查找 SQL 语句执行效率低下的原因。

在 MySQL 中，可以通过以下命令查看慢查询日志的当前状态：

SHOW VARIABLES LIKE 'slow_query_log';

在 my.ini 配置文件中，可以通过以下配置来开启慢查询日志：

# 慢查询日志开启
slow_query_log = 1
# 慢查询日志时间阈值
long_query_time = 2

2.5.3 profile

profile 是 MySQL 提供的一种性能分析工具，可以查看 SQL 语句的执行时间。

# 查看是否支持 profile
select @@have_profiling;
# 开启 profile
set profiling = 1;

# 查看每一条 SQL 语句的执行时间
show profiles;
# 查看指定id的 SQL 语句的执行时间
show profile for query id;
# 查看指定id的SQL语句cpu消耗
show profile cpu for query id;

2.5.4 explain

explain 是 MySQL 提供的一种查询分析工具，可以查看 SQL 语句的执行计划。

语法：语句前加上 explain 关键字。

explain select * from user where name = '张三';

字段解释:

• id：查询的序列号，表示查询中执行 select 子句或操作表的顺序。id 越大，优先级越高。id 相同，执行顺序从上到下。
• select_type：查询的类型。（简单查询、联合查询、子查询等）
• type：访问类型，表示 MySQL 在表中找到所需行的方式，性能从最好到最差依次是：NULL(不查表) > system > const > eq_ref > ref > range > index > all
• possible_keys：可能使用到的索引
• key：实际使用的索引
• key_len：索引的长度

2.6 索引使用

2.6.1 最左前缀匹配原则

最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据，这样可以提高查询效率。

最左匹配原则会一直向右匹配，直到遇到范围查询（如 >、<）为止。对于 >=、<=、BETWEEN 以及前缀匹配 LIKE 的范围查询，不会停止匹配。
假设有一个联合索引 (column1, column2, column3)，其从左到右的所有前缀为 (column1)、(column1, column2)、(column1, column2, column3)（创建 1 个联合索引相当于创建了 3 个索引），包含这些列的所有查询都会走索引而不会全表扫描。我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据。

例如：有一个联合索引 (name, age)，查询条件为 age = 18 and name = ‘张三’，那么 MySQL 会使用索引来过滤数据。(只需要包含条件即可，和位置无关)如果查询条件为 age = 18，那么 MySQL 就不会使用索引，而是全表扫描。

2.6.2 索引失效

1. 联合索引中，出现范围查询(>, <)时，范围查询右侧的列索引失效。
   可以改成>=, <= 来避免索引失效。

2. 不要在索引列上进行运算操作，否则索引会失效。

select * from user where age + 1 = 18;

3. 字符串不加单引号，索引失效。

4. like 以 % 开头，索引失效。仅仅尾部加 % 不会失效。

5. 查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到；

6. IN 的取值范围较大时会导致索引失效，走全表扫描（NOT IN 和 IN 的失效场景相同）；

7. 如果 MySQL 估计使用全表扫描要比使用索引快，则不会使用索引；

2.6.3 sql 提示

在 SQL 语句中，可以使用 use index（建议 mysql 使用） 或 force index（强制使用） 或 ignore index（忽略） 来提示 MySQL 使用指定的索引。

2.6.4 覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为 覆盖索引（Covering Index）。不需要回表查询，可以减少 IO 次数，提高查询效率。

2.6.5 前缀索引

前缀索引是指对字符串类型的字段的前 n 个字符建立索引。前缀索引可以减少索引的存储空间，提高查询效率。

create index idx_user_name on user(name(n));

2.6.6 使用索引的建议

1. 尽可能的考虑建立联合索引而不是单列索引:
   因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗 B+ 树。如果一个表的字段过多，索引过多，那么当这个表的数据达到一个体量后，索引占用的空间也是很多的，且修改索引时，耗费的时间也是较多的。如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。

2. 适合建立索引的字段

• 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0、1、true、false 这样语义较为清晰的短值或短字符作为替代。
• 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段。
• 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
• 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
• 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。

3. 被频繁修改的索引慎重考虑建立索引
   虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。 如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。

4. 限制索引的数量
   索引并不是越多越好，建议单张表索引不超过 5 个！索引可以提高效率，同样可以降低效率。
   索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。

5. 字符串类型
   前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

3. sql 优化

3.1 插入数据优化

• 主键顺序插入：主键是聚集索引，插入数据时，会根据主键的顺序插入，这样可以减少磁盘的随机读写，提高插入效率。

• 手动控制事务

• 大量插入数据：可以使用 load data file 指令

3.2 主键优化

存在 页分裂和页合并的问题，所以主键的选择是非常重要的。

• 降低主键的长度
• 顺序插入，选择 auto_increment 自增主键
• 尽量不用 uuid 或者其他自然主键（过于随机）
• 尽量避免修改主键

3.3 order by 优化

• 根据排序字段建立合适的索引，多字段排序也遵循最左前缀原则
• 使用覆盖索引
• 注意索引的升降序规则
• 大数据排序时，可以适当增大排序缓冲区大小 sort_buffer_size(默认 256k)

3.4 group by 优化

• 分组操作可以通过索引提高效率
• 分组时索引也遵循最左前缀原则

3.5 limit 优化

分页查询时，通过创建覆盖索引能够较好提高性能，也可以通过覆盖索引+子查询优化

3.6 count 优化

自己计数
count(*)：专门优化，直接累加。
count(主键): 遍历，直接累加，不判断 null（主键不可能是 null）
count(字段)：有 not null 约束就不判断是否为 null，否则需要判断是否为 null
count(1)： 遍历整表，每行+1

3.7 update 优化

innoDB 行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

4. 锁

4.1 锁分类

全局锁：对整个数据库实例加锁，对于整个库的写操作，都会被锁住。
表级锁：对当前操作的表加锁，对于当前操作的表的写操作，都会被锁住。
行级锁：对当前操作的行加锁，对于当前操作的行的写操作，都会被锁住。

4.2 全局锁

全局锁是对整个数据库实例加锁，对于整个库的写操作，都会被锁住。全局锁的典型使用场景是做全库逻辑备份。

全局锁的加锁方法是：flush tables with read lock;，解锁方法是：unlock tables;

全局锁会阻塞整个库的读写操作，还有主从延迟问题

innoDB 中可以在备份时加上参数 –single-transaction 来不加锁备份

mysqldump -u root -p --single-transaction mydb > mydb.sql

4.3 表级锁

分为表锁、元数据锁、意向锁

4.3.1 表锁

• 表共享读锁，允许其他用户读，但不允许其他用户写
• 表独占写锁，不允许其他用户读，也不允许其他用户写
  加锁：lock tables 表名 read/write
  解锁：unlock tables

4.3.2 元数据锁（meta data lock）

元数据锁是对表结构做更改时加的锁，比如修改表结构、重命名表等操作。元数据锁是在表锁的基础上加的锁，保证了表结构的一致性。

4.3.3 意向锁

意向锁是为了解决表锁和行锁之间的冲突问题，当一个事务要对某一行加锁时，会先对整个表加意向锁，表锁和行锁之间的关系如下：

意向共享锁（IS）：由语句 select … lock in share mode 加的锁。与表锁 read 兼容，与表锁 write 互斥。
意向排他锁（IX）：由语句 insert、update、delete、select… for update 加的锁。与 read、write 都互斥，意向锁之间不互斥。

4.4 行级锁

4.4.1 行锁

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

S 和 S 兼容，其余组合都不兼容。

• 针对唯一索引检索时，对已存在的记录等值匹配时，自动优化成行锁。
• InnoDB 的行锁是针对于索引加的锁，不通过索引检索数据会升级为表锁。

4.4.2 间隙锁&临键锁

InnoDB 在默认情况下，在 REPEATABLE READ 事务隔离级别运行，使用 next-key 锁进行搜索和索引扫描，防止幻读。

• 索引上等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
• 索引上等值查询（普通索引），向右遍历到最后一个值不满足查询需求时，next-key-lock 退化为间隙锁
• 索引上范围查询（唯一索引），会访问到不满足条件的第一个值为止。