发布日期:
2024-06-13
文章字数:
2.2k
阅读时长:
7 分
阅读次数:
- 事务的四个特性ACID(Atomicity、Consistency、Isolation、Durability) ：原子性、一致性、隔离性和持久性。
- 原子性: 指构成事务的所有操作,要么全部成功,要么全部失败.
- 一致性: 指事务执行前和执行之后,数据始终处于一致的状态.
- 隔离性: 指并发事务在执行的过程中,事务之间互不 干扰.
- 持久性: 指事务提交完成之后,此事务针对数据的操作会持久话到数据库,不会发生更改.
- 事务的隔离级别：读未提交，读已提交，可重复读，串行化
- 数据库的默认隔离级别：mysql（可重复读）；oracle（读已提交）
- 并发问题：脏读，不可重复读，幻读
- 事务的七种传播级别：PROPAGATION_REQUIRED；PROPAGATION_SUPPORTS；PROPAGATION_MANDATORY；PROPAGATION_REQUIRES_NEW；PROPAGATION_NOT_SUPPORTED；PROPAGATION_NEVER；PROPAGATION_NESTED
- 悲观锁（每次在拿数据的时候都会上锁，适合写多场景）：
select * from account where name="Erica" for update
- 乐观锁（版本号，适合读多写少场景）：
xxx version=1
- join 连接方式
- left join: A和B的交集再并上A的所有数据；左外连接
- right join: A和B的交集再并上B的所有数据；右外连接
- inner join: A和B的交集；内连接
- full join: A和B的并集；外连接
- SQL 优化
- 选取最适用的字段属性。表中的字段长度设置的尽可能小
- 使用 join 来代替子查询
- 执行 SELECT 子句时尽量避免使用 “*”
- 删全表时用 TRUNCATE 语句替代 DELETE 语句
- 利用 DECODE 函数，其能够在单个查询中内联处理多个条件分支。DECODE（条件，值1，值2）
- 建立索引，用于创建索引的列尽量是 where 子句查询使用的列
- 不要用 in，not in，用 exists，not exists 、between and 来代替
- 避免使用 != 或者 like。避免使用 is null 可以设置默认值 0
- 避免在 where 子句中等号左边使用公式 例如：where num/2=100
- 使用表的别名，减少解析的时间
- 不要创建不必要的索引。索引需要额外的磁盘空间，并降低写操作的性能
MySQL
- InnoDB 和 MyIsam 的区别
- InnoDB 支持自增主键，关键字 auto_increment；MyISAM 不支持
- InnoDB 支持外键，而 MyISAM 不支持
- InnoDB 支持事务，MyISAM 不支持
- InnoDB 支持行级锁，可以支持高并发，而 MyISAM 支持表级锁
- MyISAM 的
select count(*) 更快，因为 MyISAM 内部维护了一个计数器，可以直接调取
- 索引：普通索引；主键索引；唯一索引；组合索引；全文索引
- 创建数据库索引
- 数据量超过三百，理论上应创建索引
- 经常与其他表链接的表，在链接字段应创建索引 on 两边的字段，都要建立索引
- 经常出现在where子句中的字段，尤其是大表，应创建索引
- 索引应创建在选择性高，重复度低的字段上
- 索引应该建立在小字段上，对于大的文本甚至超长字段，尽量不建立索引
- 多个字段组合一起查询的用复合索引
- 查询中排序的字段，应该创建索引（ B + tree 有顺序）
- 统计或者分组字段，应该创建索引
- 创建索引的方式
- 使用 CREATE INDEX 语句
create index index_name on t_dept(name);
- 使用 CREATE TABLE 语句
- 使用 ALTER TABLE 语句
alter table items add index index_name(name);
- 索引失效
- like 查询以 % 开头
- 使用 in 、not in 、is null 关键字 （in 太多值；in 包含了 null 值；in 包含了子查询都会使索引失效）
- 如果使用 or（or 条件中的每个列都加上索引才能不失效）
- 索引字段的值不能有 null 值
- 在索引的列上使用表达式或者函数会使索引失效
- 复合索引未使用左列字段
- Mysql 索引方式：B+ 树 hash 索引
- 哈希索引支持等值查询，但是不支持范围查询
- 哈希索引不支持组合索引的最左前缀匹配规则
- 哈希索引不支持排序
- 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题
- B+ 树索引具有范围查找和前缀查找的能力，对于有 N 节点的B树，检索一条记录的复杂度为 O(LogN)。相当于二分查找
- 二叉树
- 二叉查找树：不平衡时相当于链表；高度太高时导致查找效率的不稳定
- 平衡二叉树：二叉查找树的基础上，要求每个节点的左右子树的高度差不能超过 1
- B 树：平衡二叉树的 基础上，从磁盘中读取数据时，都是按照磁盘块（页）来读取的，并不是一条一条的读
- B+ 树：InnoDB 存储引擎中的 B+ 树索引
- B 树的基础上，非叶子节点上是不存储数据的，仅存储键值（在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB）
- 如果不存储数据，那么就会存储更多的键值，相应的树的阶数(节点的子节点树)就会更大，查找数据进行磁盘的 IO 次数就会减少，查询效率更高
- 使得范围查找，排序查找，分组查找以及去重查找变得异常简单
- InnoDB 使用的是聚簇（聚集）索引：因为 InnoDB 的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）
- 如果没有显式指定，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键
- 如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整形
- 聚簇索引存储：对于行数据和主键B+树会存储在一起，对于辅助键B+树只会存储辅助键和主键
- 将主键组织到一棵 B+ 树中，而行数据就储存在叶子节点上，若使用 “where id = 14” 这样的条件查找主键，则按照 B+ 树的检索算法即可查找到对应的叶节点，之后获得行数据
- 若对 Name 列进行条件搜索，则需要两个步骤：
- 第一步在辅助索引 B+ 树中检索 Name，到达其叶子节点获取对应的主键
- 第二步使用主键在主索引 B+ 树种再执行一次 B+ 树检索操作，最终到达叶子节点即可获取整行数据
- 聚合索引：创建多列索引时，根据业务需求，where 子句中使用最频繁的一列放在最左边(命中率高)，因为 MySQL 索引查询会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
- 组合索引的生效原则：从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用
- 如果创建了复合索引(A, B, C)，就相当于创建了(A, B, C)、(A, B)和(A)三个索引，这被称为复合索引前导列特性，因此在创建复合索引时应该将最常用作查询条件的列放在最左边，依次递减
- MVCC（Multi-Version Concurrency Control）多版本并发控制：在修改数据时不阻塞其他事务的读操作，而且不用加锁，提高数据库并发性能，解决脏读，幻读，不可重复读等事务隔离问题
- 当前读：像 select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读
- 它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
- 快照读：不加锁的 select 操作就是快照读，即不加锁的非阻塞读，快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
- 基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC,可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
- 执行计划：使用 explain 命令查看 query 语句的性能（
EXPLAIN select * from user_feedback;）
