MySQL索引、锁和优化
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)
- 快照读(不加锁)
