Mysql部分

索引

分类：

MySQL 可以按照四个角度来分类索引：

分类角度 分类类型
数据结构 B+tree 索引、Hash 索引、Full-text 索引
物理存储 聚簇索引（主键索引）、二级索引（辅助索引）
字段特性 主键索引、唯一索引、普通索引、前缀索引
字段个数 单列索引、联合索引

索引失效：

    1. 对索引列进行函数运算，包含聚合函数、隐式类型转换、对索引列进行计算
    2. or条件，前面有索引，后面没有索引
    3. like模糊查询 左侧以及左右模糊查询 like %xx 和like %xxx%
    4. 不符合最左匹配原则，联合索引失效。
    5. 数据分布区别度不大，mysql优化器自动选择是否使用索引。
    1
    2
    3
    4
    >1.联合索引 (a, b，c)，where条件是 a=2 and c < 1，能用到联合索引吗？
    >a会走，但是c不会，但是c会走索引下推
    >2.联合索引 (a, b，c)，where条件是 a=2 and c = 1，能用到联合索引吗？
    >a会走，但是c不会，但是c会走索引下推

  • 联合索引

    最左匹配原则

  • 前缀索引

    优点 说明
    🎯 降低索引项大小 字符串字段往往很长，只用前 N 个字符能显著减少每个索引项占用空间
    📦 增加每页可存索引数量 减少每条索引项的大小 → 单个页能存更多条目 → 降低 B+ 树高度
    ⚡ 提高查询效率 索引页更小、更浅，查询时更快，磁盘 I/O 更少
    💰 降低内存消耗 索引缓存（如 InnoDB 的 buffer pool）能缓存更多索引页
    限制 / 注意 说明
    ❌ 不能完全覆盖查询 由于只索引了前 N 个字符，不具备覆盖索引的能力（比如 SELECT email 无法只用索引完成）
    ❗ 可能存在大量重复前缀 如果前 N 个字符的区分度不高，会导致索引效果差甚至退化成全表扫描
    ❌ 不支持某些唯一约束 前缀索引不一定能确保唯一性，不能完全代替唯一索引（尤其是对邮箱、身份证号等全字段唯一的字段）
    🔍 查询时需匹配前缀 若 WHERE 条件不匹配前缀（如模糊查询 %xxx），索引会失效

锁

  • 全局锁

    1
    flush tables with read lock;

  • 表锁

    • 表共享读锁

      1
      2
      lock tables xxxx read;
      unlock tables;

      未释放之前，DQL都可以进行（其他客户端和自己都可以进行读操作）；DML置于阻塞状态（其他客户端和自己都不可以进行写操作）；不阻塞读，阻塞写。

    • 表独占写锁

      1
      2
      lock tables xxxx write;
      unlock tables;

      未释放之前，独占，只有自己可以执行DML、DQL，其他客户端两种都不可以。

    • 元数据锁（Meta data lock–MDL）

      访问一张表时，自动加锁。元数据锁保证数据表结构的一致性。避免DML、DDL的冲突。

      在 MySQL 5.5 中引入了 MDL，当对一张表进行增删改查的时候，加 MDL 读锁（共享）；当对表结构进行更改操作的时候，加 MDL 写锁（排他）。

      当一个事务中的CRUD代码未提交时，在另一个客户端对表结构进行修改，这个客户端会处于阻塞直至事务提交。

      对应 SQL 锁类型 说明
      lock tables xxx read / write SHARED_READ_ONLY / SHARED_NO_READ_WRITE
      select、select ... lock in share mode SHARED_READ 与 SHARED_READ、SHARED_WRITE兼容，与 EXCLUSIVE 互斥
      insert、update、delete、select ... for update SHARED_WRITE 与 SHARED_READ、SHARED_WRITE 兼容，与 EXCLUSIVE 互斥
      alter table ... EXCLUSIVE 与其他的 MDL 都互斥

    • 意向锁

      意向共享锁（IS）

      1
      select ..... lock in share mode;

      与表共享读锁兼容，与表独占写锁不兼容。

      意向排他锁（IX）

      1
      insert、 update、 delete、 select...for update

      与表共享读锁、表独占写锁都不兼容。意向锁之间不会互斥。

  • 行锁

    • 行锁Record Lock

      每次操作锁住对应的数据，锁粒度最小，发生冲突的概率最低，并发度最高，用在InnoDB中。锁定单行记录，防止其他事务对此行进行Update、delete操作，在RC（Read committed），RR（Repeatable read）隔离级别下都适用。

      • 共享锁（S）

        共享锁之间是兼容的，共享锁和排他锁之间互斥，阻止其他事务获得相同数据集的排他锁。

      • 排他锁（X）

        允许获取排他锁的事务执行更新语句，阻止其他事务获得相同数据集的共享锁和排他锁。

      SQL 行锁类型 说明
      INSERT … 排他锁 自动加锁
      UPDATE … 排他锁 自动加锁
      DELETE … 排他锁 自动加锁
      SELECT（正常） 不加任何锁
      SELECT ... LOCK IN SHARE MODE 共享锁 需要手动在 SELECT 之后加 LOCK IN SHARE MODE
      SELECT ... FOR UPDATE 排他锁 需要手动在 SELECT 之后加 FOR UPDATE
      • 默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，InnoDB使用 next-key锁进行搜索和索引扫描，以防止幻读。
        • 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。
        • InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，InnoDB会对表中所有记录加锁，升级为表锁。

    • 间隙锁Gap Lock：

      RR（Repeatable read）隔离级别下适用。锁定索引记录的间隙（不含该记录），确保索引记录间隙不变。防止产生幻读（记录数量前后不一致）。

      间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

    • 临键锁（Next-key Lock）

      RR（Repeatable read）隔离级别下适用，行锁和间隙锁的组合，同时锁住记录。

      • 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁 。例如，修改id为5的数据，但是id只有3和8，此时就会对3-8之间的加间隙锁（不包含3-8），直至事务提交。

        image-20250422220639906

      • 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。例如，age字段是普通索引，因为是非唯一索引，因此age=3这条记录的前面和后面都有可能继续插入age=3的列，因此会对其进行向右遍历，找到不满足查询需求的第一个记录，将3之前和之后这段间隙锁住。

        image-20250422222330201

      • 索引上的范围查询(唯一索引)–会访问到不满足条件的第一个值为止。例如: 大于等于19 的id有 19 ，25，执行

        1
        select * from stu where id >= 19 in share mode;

        此时加锁情况：共享锁S（对于19），临键锁（19-25之间的间隙，包含25不包含19），临键锁（25到正无穷的锁）

        间隙锁是左开右开、临键锁是左开右闭。

        1
        select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

        image-20250422215034269

事务