MySQL

1.索引

  • 定义: 索引是存储引擎用于快速找到记录的一种数据结构
  • 作用: 索引可快速访问数据库表中的特定信息，索引是对一列或多列值进行排序的数据结构
  • 实现: 索引是在存储引擎层实现，不是服务层，不同的存储引擎具有不同的索引类型和实现

索引优化是对查询性能优化最有效的手段(索引能轻易将查询性能提高几个数量级)

2.索引的类型

  • B+Tree索引
  • 哈希索引
  • 全文索引
  • 空间数据索引
2.1 B+Tree索引

MySQL多数存储引擎的默认类型

利用索引，不再需要建立全表扫描来获取需要的数据

B+Tree索引：

  • 全键值查找
  • 键值范围查找
  • 键前缀值查找

最左前缀查找

B+Tree相比于BTree的优势

  • B+Tree非叶子结点只存储索引，B+Tree的高度更低，IO次数更少
  • B+Tree叶子节点前后管理，更加方便范围查询，结果都在叶子节点，查询效率稳定
  • B+Tree有利于对数据扫描，避免BTree的回溯扫描
2.2 哈希索引

哈希索引基于哈希表实现，哈希索引查找的速度非常快

哈希索引无法实现排序

CRC32(column) 计算哈希

3.索引的优点

  • 可以大大较少服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 索引可以将随机I/O变成顺序I/O

索引的原则:

  • 索引不是越多越好，维护索引需要空间和时间
  • 频繁更新的数据，不宜建立索引
  • 数据量较少的表(数据量超大)的表没必要建立索引
  • 重复率小的列建立索引
  • 数据具有唯一性，建立索引
  • 频繁group by ，order by的列，建立索引
  • 经常用于查询条件的字段建议生成索引

4.高性能索引

正确的创建和使用索引是实现高性能查询的基础

4.1独立的列

索引不能是表达式的一部分，不能是函数的参数

  • 无法启动列索引
SELECT actor_id FROM actor WHERE actor_id + 1 = 5;
  • 可以启动actor_id的索引
SELECT actor_id FROM actor WHERE actor_id  = 4;
4.2 前缀索引和索引选择性

对于很长的字符列，建立索引会让索引变得大且慢

  • 1.找到合适的索引字符长度
  • 2.创建前缀索引
ALTER TABLE city_demo ADD KEY (city(7));   # 索引字符长度为7
4.3多列索引

常见的错误:为每个列创建独立的列

4.4选择合适的索引列顺讯
  • 对于多个索引列，可先查看列对应数据基数
SELECT sum(staff_id = 2),SUM(customer_id = 584) FROM payment;

# SUM(staff_id = 2):7972
# SUM(customer_id = 584) :30

SELECT COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
       COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
       COUNT(*)
FROM payment;

选择性较高的作为索引列的第一列，应该将 customer_id 放到前面

5.查询性能优化

除了建立最好的索引，还需要合理的设计查询

优化三驾马车，需要齐头并进

  • 查询优化
  • 索引优化
  • 库表结构优化
5.1 慢查询基础

查询性能低下最基本的原因是访问的数据太多，大部分可以通过减少访问的数据量方式进行优化

  • 1.确认应用程序是否在检索大量超过需要的数据（过多的行，过多的列）
  • 2.确认MySQL服务器是否存在分析大量超过需要的数据行

请求不需要的数据

扫描额外的记录

  • 响应时间 = 服务时间 + 排队时间
  • 扫描的行数
  • 返回的行数
5.2 重构查询方式

复杂查询 vs 简单查询

切分查询

DELETE FROM messages WHERE created < DATA_SUB(NOW(),INTERVAL 3 MONTH);

# 优化
# 对大量删除数据进行切分，防止服务器一次性完成大量工作
# 锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞小且重要的查询
row_affected = 0
do{
	 row_affected = do_query(
     "DELETE FROM messages WHERE created < DATA_SUB(NOW(),INTERVAL 3 MONTH) LIMIE 10000")
}while row_affected > 0
5.3 分解关联查询
  • 缓存的效率更高
  • 分解成单个查询，可以减少锁的竞争
  • 应用层关联，更加容易对数据库进行拆分(做到高性能和可扩展)
  • 减少冗余记录的查询
  • 引用层实现哈希关联代替MySQL的嵌套循环关联(IN 的方式代替关联查询)
# 查询当前会话的Last_query_cost的值来计算当前查询的成本
SHOW STATUS LIKE 'Last_query_cost';

6.主从复制

主要涉及三个线程

  • binlog线程 - 负责将主服务器上的数据更改写入二进制日志(Binary log)中
  • I/O线程 - 负责从主服务器读取二进制日志，并写入从服务器的中继日志(Relay log)
  • SQL线程 - 负责读取中继日志，解析出主服务器已经执行的数据更改并在从服务器中重放(Replay)

读写分离提高性能

  • 主服务器处理写操作
  • 从服务器处理读操作

主从服务器负责各自的读和写，极大程度缓解了锁的争用

从服务器可以使用MyISAM,提升查询性能以及节约系统开销

增加冗余，提高可用性

实现：

  • 增加反向代理(Reverse Proxy)，代理服务器接受应用层传来的读写请求，决定转发道那个服务器

7.MySQL语句执行流程

Server层

  • 连接器
  • 查询缓存(MySQL 8.0版本后取消)
  • 分析器
  • 优化器
  • 执行器

存储引擎层

  • 数据的存储和读取

8.MySQL中的锁

  • 悲观锁
  • 乐观锁

间隙锁: Gap Lock

行锁只能锁住行，如果在记录之间插入数据就无法解决，引入间隙锁[左开右闭区间]

间隙锁+行锁 = NextKeyLock

  • 当前读(加悲观锁的实现，SELECT FOR UPDATE ,DELETE)
  • 快照读(不加锁)