「MYSQL」相关笔记

索引结构

二叉树

• 特点：查询效率较高，有序
  

缺点：有可能蜕变成链表，不平衡，顺序插入，会形成一个链表，查询性能大大降低，大数据量情况下，层级较深，检索速度慢

解决：升级红黑树

B 树：每个节点都存储数据

B+ 树：叶子节点存储数据（包含所有元素），叶子节点会形成链表

Mysql优化为双向链表

Hash索引

• 特点：
  • Hash索引只能用于对等比较（=，in），不支持范围查询
  • 无法利用索引完成排序操作
  • 查询效率高，通常一次检索就足够了，效率通常高于B+树（未产生Hash冲突时）

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键所建立的索引	默认自动创建，只能一个	PRIMARY
唯一索引	避免同一个表中某一列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个	无
全文索引	全索引查找的时文本中的关键词，不是比较索引中的值	可以有多个	FULLTEXT

根据索引的存储形式分为以下两种

• 聚簇索引：索引叶子节点存储行数据，必须有，而且只有一个，不需要回表

• 非聚簇索引：索引和数据分开存储，其关联了聚簇索引的ID，可以存在多个，查询非索引列会出现回表

索引使用

• 最左前缀法则：尽量从创建索引的顺序使用索引，跳跃使用会导致索引失效，如果全部都使用无关顺序都生效
• 范围查询：范围查询后面的索引失效尽量使用(>= <=)
• 索引列计算：不要在索引列进行计算，否则会导致索引失效
• 字符串：不加单引号，会导致隐式转换，导致索引失效
• 模糊查询：%在前会导致索引失效
• or条件：or 有一侧没有索引会导致索引失效
• 数据分布影响：IS_NULL、IS_NOT_NULL

索引优化原则

• 插入数据
  • 少数据量插入：批量插入、手动控制事务、主键顺序插入
  • 大批量优化：load data local infile
• 主键优化
  • 主键长度尽量短、顺序优化、主键自增
    • 索引节点的：页分裂、页合并
• order by优化
  • using index：直接通过索引返回数据，性能高
  • using filesort：需要将返回的结果在排序缓冲区排序
    • 尽量使用索引排序指定索引的：asc、desc
• group by优化
  • 采用索引，多字段分组满足最左前缀法则
• limit 优化
  • 覆盖索引、子查询
• count 优化
  • count(*) :最优
• update优化
  • 默认是行锁，更新条件尽量采用索引字段否则会升级到表锁

Mysql中的锁

锁的分类

• 全局锁：锁数据库全部的表
• 表级锁：锁当前表，粒度最小
• 行级锁：锁当前操作行

全局锁

对整个数据库实例加锁，加锁之后处于只读状态

使用场景：做全库的逻辑备份，对所有的表进行锁定，保证数据的完整性，使用 mysqldump 工具

锁表语句：flush tables with read lock
备份语句：mysqldump -uroot -p1234 tablename > 文件名.sql
解锁语句：unlock tables

问题：

• 如果在主库上备份，那么备份期间都能执行更新，业务操作基本就停摆了
• 如果在从库备份，那么备份期间从库就不能执行主库同步过来的二进制日志(binlog)，导致主从延迟

表级锁

每次操作都锁住整张表。锁的粒度大、发生锁的冲突比较高，并发度低

表级锁的分类

• 表锁
  • 语法：
    • 加锁：lock tables 表名... read / write
    • 释放锁：unlock tables / 客户端断开连接
  • 表共享读锁
    • 阻塞其他客户端的写
  • 表共享写锁
    • 阻塞其他客户端读与写
• 元数据锁（MDL）
  • SHARED_READ、SHARED_WRITE 兼容
    • 增删改：SHARED_WRITE
    • 查：SHARED_READ
  • EXCLUSIVE：互斥，修改表结构会加

查询锁语法

SELECT OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION from performance_schema.metadata_locks

• 意向锁
  • 意向共享锁IS：与表共享锁兼容，与表共享写锁互斥
  • 意向排他锁IX：与表读写锁都互斥。意向锁之间不互斥

行级锁

• 行锁：锁定单个记录的锁，防止其他事务对此进行update、delete。RC｜RR隔离级别都支持
• 间隙锁：锁定 索引记录间隙（不含当前记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert、产生幻读。在RR隔离级别下支持
• 临键锁：行锁和间隙锁组合，同时锁数据和数据前面的间隙，RR隔离级别下生效

分类：

• 共享锁：允许一个事务读，阻止其他事务获得相同的数据集的排他锁
• 排他锁：持有锁的事务更新数据，阻止其他事务获取排他锁、共享锁

SQL	行锁类型	说明
INSERT	排他锁	自动加锁
UPDATE	排他锁	自动加锁
DELETE	排他锁	自动加锁
SELECT	不加锁
SELECT …LOCK IN SHARE MODE	共享锁	手动添加
SELECT …FOR UPDATE	排他锁	手动添加

MVVC原理

• 第一步生成版本链
  • Innodb存储引擎会为每行记录隐式创建三个字段DB_TRX_ID、DB_ROLLING_PTR、DB_ROW_ID
  • 第一次修改某条记录时，T_ID会自增1，同时PTR指向历史版本行地址， 后面修改以此类推
  • 当有新事务要来读取数据时
  • 首先创建当前读的ReadView，其内部有4个核心属性m_ids活动事务集合、min_tid、max_tid、creator_t_id
    • 读取到undo_log中的版本链中每一条的T_ID和如下条件进行比对
      • 条件一：版本事务ID == 当前读视图本身的 tid 可读
      • 条件二：版本事务ID < min_tid 可读
      • 条件三：版本事务id > max_id 不可读
      • 条件四：版本事务id 是否在 min_Id 和 max_tid 之间，并且不在活动事务集合当中可读

内容来自：黑马MYSQL讲解高级篇中