mysql锁+索引

mysql锁

按锁的粒度分类

• 表级锁（Table - level locks）
  • 特点：对整张表进行锁定，实现简单，加锁和释放锁的速度快，但并发度较低。当一个事务对表加表级锁后，其他事务对该表的读写操作都可能被阻塞。
  • 应用场景：适用于执行大量数据的批量操作，如 ALTER TABLE 、LOCK TABLES 等语句。
  • 类型：
    • 共享锁（Shared Locks，简称 S 锁）：也叫读锁，多个事务可以同时获取一个表的共享锁来读取数据，但不能同时对该表进行写操作。
    • 排他锁（Exclusive Locks，简称 X 锁）：也叫写锁，一个事务获取排他锁后，其他事务不能再获取该表的任何锁，直到持有排他锁的事务释放锁。
• 行级锁（Row - level locks）
  • 特点：只锁定表中的某一行数据，并发度高，但加锁和释放锁的开销相对较大。InnoDB 存储引擎支持行级锁。
  • 应用场景：适用于高并发的 OLTP（联机事务处理）系统，对同一表中的不同行进行并发读写操作。
  • 类型：
    • 共享锁（S 锁）：允许事务读取一行数据，多个事务可以同时对同一行加共享锁。
    • 排他锁（X 锁）：阻止其他事务读取和修改该行数据。
    • 意向锁（Intention Locks）：包括意向共享锁（IS 锁）和意向排他锁（IX 锁），用于表示事务在表级别上的意图。意向锁是表级别的锁，主要用于在表级锁和行级锁之间进行协调，提高加锁操作的效率。
• 页级锁（Page - level locks）
  • 特点：锁定粒度介于表级锁和行级锁之间，锁定的是数据页。它的开销和并发度也介于两者之间。BDB 存储引擎支持页级锁，InnoDB 存储引擎主要使用行级锁和表级意向锁，基本不涉及页级锁。

共享锁区别

• 表级共享锁：你给整个笔记本加了一把 “只读锁”，自己可以看，别人也能拿过去一起看，但谁都不能在笔记本上涂改（写操作）。哪怕别人只想改其中一个人的信息，也得等你把锁解开。
  • 不过，别人可以随便看或改其他行（比如第 6 行李四的信息），互不影响。
  • 行级共享锁：你只给笔记本里某一行（比如第 5 行，张三的信息）加了 “只读锁”，你可以看张三的信息，别人也能看张三的信息（也可以加行级共享锁）。但别人想改张三的信息就不行，得等你解锁。
  • 表级共享锁 “管得宽”，整个表都受限制，适合全表读但不写的场景；
    行级共享锁 “管得细”，只限制某一行，其他行该咋操作咋操作，适合多个人同时操作不同行的场景。

排他锁区别

                表级排他锁：
你把整个笔记本锁起来了，钥匙只有你有。这时候：

• 你既能看笔记本里的内容，也能随便涂改（读写都行）。
• 其他人既不能看，也不能改，连碰都碰不了，只能等你把锁打开。

                行级排他锁：
你只锁了笔记本里的某一行（比如第 5 行张三的信息），钥匙只有你有。这时候

• 你能看、能改这一行的内容。
• 其他人既不能看这一行，也不能改这一行，得等你解锁。
• 但其他人可以随便看、随便改其他行（比如第 6 行李四的信息），互不影响。

区别核心：
表级排他锁是 “一锅端”，整个表都被你独占；
行级排他锁是 “精准打击”，只独占某一行，其他行大家该咋用咋用。

意向锁：

假设你要给笔记本的某一行加锁（行锁），但在加行锁之前，系统会先在整本笔记本的封面上贴一个小标签，标签上写着 “我打算锁某一行哦”—— 这个标签就是意向锁。

具体来说：

• 如果你想给某一行加共享锁（读锁），系统会先加一个 “意向共享锁”（IS 锁）的标签。
• 如果你想给某一行加排他锁（写锁），系统会先加一个 “意向排他锁”（IX 锁）的标签。

这么做的目的是提高效率：
比如有人想给整个笔记本加表锁时，不用逐行检查有没有行锁，只看封面上的标签就知道：“哦，里面已经有人打算锁行了，我暂时加不了表锁”，省去了逐行排查的麻烦。

简单说，意向锁就是 “提前打招呼”，告诉系统 “我接下来要锁某一行”，避免后续操作做无用功

按锁的使用方式分类

• 乐观锁（Optimistic Locks）
  • 原理：它假设多用户并发的事务在处理时不会彼此干扰，只有在提交数据更新时，才会正式对数据的冲突与否进行检测。一般通过在表中增加一个版本号（version）字段或时间戳（timestamp）字段来实现。在更新数据前，先读取数据的版本号，在提交更新时，将读取到的版本号与数据库中当前记录的版本号进行比较，如果相同则更新，并将版本号加 1；如果不同则说明数据已被其他事务修改，放弃本次更新或进行其他处理。
  • 应用场景：适用于读多写少，冲突概率较低的场景。
• 悲观锁（Pessimistic Locks）
  • 原理：它假设多用户并发的事务在处理时会彼此干扰，所以在数据处理前，先对数据进行加锁，确保在自己处理数据的过程中，其他事务无法对数据进行修改。前面提到的共享锁、排他锁都属于悲观锁的范畴。
  • 应用场景：适用于写多读少，数据冲突概率较高的场景。

乐观锁和悲观锁区别：

悲观锁：假设一定会冲突，先下手为强

        就像你去图书馆借书，担心别人也会借这本，所以拿到书后直接把它锁在自己的座位上（全程独占），直到你看完还回去，别人才能碰。
        核心逻辑：认为并发操作时肯定会出现冲突，所以在操作数据前，先 “上锁” 防止别人修改，确保自己操作时数据不会被干扰。
        例子：

• 数据库里的行级排他锁、表级排他锁，就是典型的悲观锁。比如你修改一条订单数据时，先给这行加排他锁，别人既不能读也不能改，直到你改完提交。
• Java 中的synchronized关键字、ReentrantLock，也是悲观锁的实现。

        适合场景：写操作频繁、冲突概率高的场景（比如秒杀时修改库存），避免频繁重试带来的开销。

乐观锁：假设不会冲突，事后再检查

        还是借书的例子，你觉得大概率没人和你抢，所以直接拿书去看，看完准备还的时候，才检查一下：“这段时间有没有人动过这本书？” 如果没人动过，就正常还；如果被人改过，就重新拿一本再看。
        核心逻辑：认为并发操作时冲突很少发生，所以操作时不加锁，而是在提交修改前，检查数据是否被别人动过。如果没被修改，就正常提交；如果被修改了，就放弃或重试。
        例子：

• 数据库中常用 “版本号” 实现乐观锁：给每条数据加一个version字段，读取时记下版本号，修改后提交时，检查当前版本号是否和读取时一致。一致就更新（同时版本号 + 1），不一致就重试。
  比如：update 商品 set 库存=10, version=version+1 where id=1 and version=5（如果 version 还是 5，说明没人改，就更新）。
• Java 中的AtomicInteger（原子类），通过 CAS（比较并交换）机制实现乐观锁。

适合场景：读操作频繁、冲突概率低的场景（比如商品详情查询），避免加锁带来的性能损耗。

总结

1. 行锁（Row Lock）

• 锁定范围：只锁表中实际存在的某一行数据。
• 例子：当你更新 id=20 的记录时，InnoDB 会给这一行加行锁。这时：
  • 其他事务不能修改 id=20 这一行（会被阻塞）。
  • 但可以修改 id=10、id=30 或其他行，互不影响。

2. 间隙锁（Gap Lock）

• 锁定范围：锁的是两个记录之间的 “空隙”（不包含实际记录本身），防止其他事务在这个空隙中插入新数据。
• 例子：表中已有 id=10、20、30，当你操作 id>10 and id<20 的范围时（比如查询或更新），InnoDB 可能会对 10~20 之间的间隙加锁。这时：
  • 其他事务不能在这个间隙中插入 id=15 这样的新记录（会被阻塞）。
  • 但不影响已存在的 10、20 行的修改（如果没加行锁的话）。

3. 临键锁（Next-Key Lock）

• 锁定范围：行锁 + 间隙锁的组合，既锁实际存在的某一行，又锁这一行前面的间隙。
• 例子：表中已有 id=10、20、30，当你操作 id=20 时，临键锁会锁定 id=20 这一行，以及 10~20 之间的间隙。这时：
  • 其他事务不能修改 id=20 这一行。
  • 也不能在 10~20 之间插入新记录（比如 id=15）。
• 这是 InnoDB 默认的行级锁算法（当查询条件是范围或非唯一索引时会触发），目的是防止幻读（同一事务中，两次查询出现不同的新数据）。

总结

简单说：行锁锁 “已有行”，间隙锁锁 “空隙”，临键锁是两者的结合，是 InnoDB 解决幻读的核心机制。

mysql索引

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的

1.什么时候建立

1.针对于数据量较大且查询比较频繁的表，建立索引。

2.针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

2.建立什么索引

1.尽量选择区分度高的列作为索引，尽量建立唯一索引 （区分度越高，使用索引的效率越高）

2.字符串类型的字段的长度较长，建立前缀索引。

3.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

索引结构组织：

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

3. 各自的优点

B + 树的优点

1. 支持范围查询和排序：
   由于叶子节点有序且链表连接，能高效处理 where id > 10 and id < 100 这类范围查询，以及 order by 排序操作（直接遍历叶子节点链表）。
2. 查询稳定：
   无论数据位置如何，查询复杂度都是 O (log n)，不会因数据分布不均导致性能波动。
3. 适合大数据量：
   多叉结构减少了树的高度（通常 3-4 层），减少磁盘 IO 次数，适合磁盘存储的数据库场景。
4. 支持联合索引：
   可基于多个字段构建索引（如 (name, age)），通过前缀匹配高效查询。

哈希（Hash）的优点

1. 
   精确查询速度快：
   理想情况下，一次哈希计算就能定位数据，比 B + 树的多层查询更快，适合高频次的精确匹配（如 where id = 123）。
2. 实现简单：
   数据结构和查询逻辑简单，内存开销较小（相比 B + 树的复杂结构）。

4. 适用场景

• B + 树：
  适合需要范围查询、排序、联合索引的场景，是关系型数据库（如 MySQL、PostgreSQL）的默认索引结构（如 InnoDB 的主键索引）。

• 哈希（Hash）：
  适合仅需精确查询的场景，如 NoSQL 数据库（如 Redis 的哈希表）、内存数据库中的临时索引，或某些特定业务的高频精确查询。

MySQL目前提供了以下4种索引结构：

• BTREE 索引:最常见的索引类型，大部分索引都支持"B树"索引。
• HASH 索引：只有Memory引擎支持，使用场景简单 。
• R-tree 索引（空间索引）:空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引）:全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

• MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

  索引	InnoDB引擎	MyISAM引擎	Memory引擎
  BTREE索引	支持	支持	支持
  HASH 索引	不支持	不支持	支持
  R-tree 索引	不支持	支持	不支持
  Full-text	5.6版本之后支持	支持	不支持

常见索引类型

1）主键索引 :是数据库中一种特殊的索引，用于唯一标识表中的每一条记录。具有唯一性、非空性、自动索引（定义主键自动添加索引）、优化查询、数据完整性等特性

2）单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

3）唯一索引 ：索引列的值必须唯一，但允许有空值

4）复合索引 ：即一个索引包含多个列，与单值索引（只在一个列上创建）不同，复合索引能够提高基于多个列的查询效率

索引语法

1.创建索引

示例 ：为city表中的city_name字段创建单值索引;

create index idx_city_name on city(city_name);

1.普通索引（INDEX） 普通索引是最基本的索引类型，用于加速数据的检索。

创建语法：CREATE INDEX 索引名 ON 表名(字段名);

示例：CREATE INDEX idx_name ON employees (last_name);

2.唯一索引（UNIQUE INDEX） 唯一索引确保索引列中的所有值都唯一。它不仅加速查询，还可以保证数据的唯一性。

创建语法：CREATE UNIQUE INDEX 索引名 ON 表名 (字段名);

示例：CREATE UNIQUE INDEX idx_email ON users (email);

3.主键索引（PRIMARY KEY） 主键索引是唯一索引的一种，它不仅确保唯一性，还作为表的主键标识每一行数据。每个表只能有一个主键索引。

创建语法：ALTER TABLE表名 ADD PRIMARY KEY (字段名);

示例：ALTER TABLE employees ADD PRIMARY KEY (employee_id);

4.全局唯一索引（UNIQUE KEY） 类似于唯一索引，但在某些情况下可以在不同的表或分区中进行分布式唯一性检查。

创建语法：CREATE UNIQUE INDEX 索引名 ON 表名 (字段名);

示例：CREATE UNIQUE INDEX idx_username ON users (username);

5.复合索引（COMPOSITE INDEX） 复合索引是在多个列上创建的索引，用于加速基于多个列的查询。

创建语法：CREATE INDEX 索引名 ON 表名 (字段1, 字段2, ...);

示例：CREATE INDEX idx_name_age ON employees (last_name, age);

6.全文索引（FULLTEXT INDEX） 全文索引用于加速对文本数据的全文搜索（如在 TEXT 或 VARCHAR 列上进行搜索）。仅支持 MyISAM 和 InnoDB(5.7之后版本) 存储引擎。

创建语法：CREATE FULLTEXT INDEX 索引名 ON 表名 (字段名);

示例：CREATE FULLTEXT INDEX idx_description ON articles (description);

7.空间索引（SPATIAL INDEX） 空间索引用于加速对空间数据的查询，如地理位置数据。仅支持 MyISAM 存储引擎。

创建语法：CREATE SPATIAL INDEX 索引名ON 表名 (geometry_column);

括号里的 geometry_column 指的是表中存储空间地理数据的列，这个列的数据类型必须是 MySQL 支持的空间数据类型（如 GEOMETRY、POINT、LINESTRING、POLYGON 等）。

如果你的表 places 中有一个 location 列，类型是 POINT（用于存储经纬度坐标），那么创建空间索引时，括号里就填这个列名：
示例：CREATE SPATIAL INDEX idx_location ON places (location);

8.哈希索引（HASH INDEX） 哈希索引是 MEMORY 存储引擎专用的索引类型，使用哈希算法进行索引。适用于等值查询，但不支持范围查询。

创建语法：CREATE INDEX 索引名 USING HASH ON 表名 (字段名);

示例：CREATE INDEX idx_name_hash USING HASH ON employees (last_name);

9.位图索引（BITMAP INDEX） 位图索引不是 MySQL 的内置索引类型，但在某些数据库系统中（如 Oracle）有类似的实现，主要用于处理低基数（少量唯一值）的列。

创建索引的其他注意事项：

索引选择：根据查询的特点选择合适的索引类型。例如，范围查询使用 B 树索引效果更好，而全文搜索则使用全文索引。

索引维护：创建索引会增加数据插入、更新和删除的开销。应根据实际需求平衡查询性能和写入性能。

覆盖索引：创建覆盖索引（即索引中包含所有查询列）可以显著提高查询性能。

索引管理 查看索引：可以使用 SHOW INDEX 或 SHOW KEYS 语句查看表中的索引。

SHOW INDEX FROM table_name;

删除索引：可以使用 DROP INDEX 语句删除不再需要的索引。

DROP INDEX index_name ON table_name;

优化索引：定期使用 ANALYZE TABLE 和 OPTIMIZE TABLE 命令优化表的索引。

2.查看索引

                        show 索引名  from  表名;

示例：查看city表中的索引信息；

show index from city\G;

3.删除索引

                        DROP INDEX 索引名 ON 表名;

示例 ：想要删除city表上的索引idx_city_name，可以操作如下：

drop index idx_city_name on city;

4.修改索引使用ALTER命令

# 添加一个主键，索引值必须是唯一的，且不能为NULL
alter  table  tb_name  add  primary  key(column_list);# 创建唯一索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
alter  table  tb_name  add  unique index_name(column_list);# 添加普通索引， 索引值可以重复。
alter  table  tb_name  add  index index_name(column_list);# 该语句指定了索引为FULLTEXT， 用于全文索引
alter  table  tb_name  add  fulltext  index_name(column_list);