mysql部分知识-优快云博客

为什么选择了B+树

B+树非叶子节点不存在数据只存索引，B树非叶子节点存储数据

B+树使用双向链表串连所有叶子节点，区间查询效率更高，因为所有数据都在B+树的叶子节点，但是B树则需要通过中序遍历才能完成查询范围的查找。

B+树每次都必须查询到叶子节点才能找到数据，而B树查询的数据可能不在叶子节点，也可能在，这样就会造成查询的效率的不稳定

B+树查询效率更高，因为B+树矮更胖，高度小，查询产生的I/O最少。

索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

使用索引的原因提高检索数据的速度

缺点索引本身也要占空间

4、索引的结构及调用方式

mysql的使用多的索引结构是b+和hash

B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

索引的调用方式是自动的

sql优化的方式：

----------------------------------------
--大部分是根据索引优化
----------------------------------------

## 1. 插入数据
insert : 批量插入、手动控制事务、主键顺序插入
大批量插入 : load data local infile

## 2. 主键优化
主键长度尽量短、顺序插入  AUTO_INCREMENT(主键自增) ~~UUID~~(不要使用) 

## 3. order by 优化
using index : 直接通过索引返回数据， 性能高
using filesort : 需要将返回的结果在排序缓冲区排序

## 4. grop by 优化
索引，多字段分组满足最左前缀法则

## 5. limit 优化
覆盖索引 + 子查询

## 6. count 优化
性能 : count(字段) < count(主键 id ) < count(2) ~= count(*)

## 7. update 优化
尽量根据主键/索引字段进行数据更新

索引优化的方式：

尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
为经常排序分组联合操作的列设计联合索引
为数据量大查询速度比较慢的表建立索引
对索引为空的列进行限制列为空则索引无效复合索引有一列为空则无效设计时列为not null 或默认值
控制索引数量索引并不是越多越好
删除不使用，少使用的索引

如何判断哪条sql需要优化（慢查询）

查看慢查询日志。

设置慢日志的查询时间。

使用profile查询每一条sql的耗时情况。

行锁、表锁、乐观锁、悲观锁以及每个锁大致的运用场景｜

1. 行锁 (Row-Level Locking)
定义：行锁是在操作过程中对单个数据行进行锁定。它允许其他事务同时访问同一表中的其他行。
优点：
提高并发性能，因为多个事务可以同时修改不同的行。
减少锁定冲突，提高系统的吞吐量。
缺点：
锁定开销较大，因为需要维护更多的锁信息。
适用场景：
高并发读写操作的应用场景。
数据库中数据更新频繁且涉及的数据行较少的情况。
例如，在一个电商系统中，用户购买商品时，只需要锁定该用户的订单记录，而不是整个订单表。
2. 表锁 (Table-Level Locking)
定义：表锁是在操作过程中对整个表进行锁定。一旦某个事务获取了表锁，其他事务将无法对该表进行任何操作，直到锁被释放。
优点：
实现简单，管理成本低。
在某些情况下（如批量操作）可以提供更好的性能。
缺点：
并发性能较差，因为会阻塞其他事务对表的操作。
适用场景：
读多写少的应用场景。
批量操作，如数据导入或导出。
例如，在进行数据备份或数据迁移时，可以使用表锁来确保数据的一致性。
3. 乐观锁 (Optimistic Locking)
定义：乐观锁假设在事务处理期间，数据不会发生冲突。因此，它不会在读取数据时加锁，而是在提交更新时检查数据是否被其他事务修改过。
实现方式：
版本号：在表中增加一个版本号字段，每次更新时版本号递增。更新时检查版本号是否与预期一致。
时间戳：使用时间戳字段来记录最后更新的时间。更新时检查时间戳是否与预期一致。
优点：
提高并发性能，减少锁的竞争。
适用于读多写少的场景。
缺点：
如果冲突频繁发生，会导致大量的重试操作。
适用场景：
读多写少的应用场景。
例如，在论坛系统中，用户评论的更新频率较低，可以使用乐观锁来减少锁的开销。
4. 悲观锁 (Pessimistic Locking)
定义：悲观锁假设在事务处理期间，数据可能会发生冲突。因此，它会在读取数据时加锁，以防止其他事务修改数据。
实现方式：
使用 SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 语句来显式地锁定数据。
优点：
确保数据的一致性，避免脏读、不可重复读和幻读等问题。
缺点：
并发性能较差，因为会阻塞其他事务的读写操作。
适用场景：
写多读少的应用场景。
对数据一致性要求较高的场景。
例如，在银行系统中，转账操作需要确保账户余额的一致性，可以使用悲观锁来防止并发更新导致的问题。
总结
行锁：适用于高并发读写操作，数据更新频繁且涉及的数据行较少的情况。
表锁：适用于读多写少的应用场景，批量操作，如数据导入或导出。
乐观锁：适用于读多写少的应用场景，通过版本号或时间戳来检测冲突。
悲观锁：适用于写多读少的应用场景，对数据一致性要求较高的情况，通过显式锁定来防止并发问题。
选择合适的锁机制取决于具体的应用需求和业务场景。在实际应用中，可能需要结合多种锁机制来达到最佳的性能和一致性。