打卡Day1 数据库索引及优化 - 编程导航 | 一线开发者编程经验和技术实战分享

数据库认识

数据库设计三范式
1. 对属性的原子性，要求属性具有原子性，不可再分解
2. 对记录的唯一性，要求记录有唯一标识，即实体的唯一性，不存在部分依赖
3. 建立在第二范式之上，必须满足第二范式，非主键属性之间不存在函数依赖

数据库的事物

特性ACID
1. 原子性（Atomicity，或称不可分割性）
2. 一致性（Consistency）
3. 隔离性（Isolation，又称独立性）
4. 持久性（Durability）

事务隔离级别

隔离级别	脏读(Dirty Read)	不可重复读(Non-repeatable Read)	幻读(Phantom)	描述
读未提交 (Read Uncommitted)	可能	可能	可能	允许一个事务读取另一个事务未提交的数据，隔离级别最低
读已提交 (Read Committed)	不可能	可能	可能	一个事务只能读取已经提交的数据，是大多数数据库的默认隔离级别
可重复读 (Repeatable Read)	不可能	不可能	可能	在同一事务中多次读取同样的数据结果一致，MySQL的默认级别
串行化 (Serializable)	不可能	不可能	不可能	最高隔离级别，完全串行化执行，性能最差

索引

索引是数据库中一种特殊的数据结构，用于提高数据库表的查询效率，可以理解为是一种排好序的快速查找数据结构。它类似于书籍的目录，可以帮助数据库系统快速定位和访问数据，而不需要进行全表扫描。

索引类型
- 唯一索引：表上一个字段或者多个字段的组合建立的索引，这些字段组合起来能够确定唯一，允许存在空值（只允许存在一条空值）
- 非唯一索引：表上一个字段或者多个字段的组合建立的索引，可以重复，不需要唯一
- 主键索引：（主索引）根据主键pk_clolum（length）建立索引，不允许重复，不允许空值
- 聚合索引：表中记录的物理顺序与键值的索引顺序相同
- 非聚合索引：表中记录的物理顺序与键值的索引顺序无关
- 全文索引：在某个字段设置全文索引后，根据特定语法查找满足条件的字段;
- 普通索引：用表中的普通列构建的索引，没有任何限制
- 组合索引：用多个列组合构建的索引，但是在使用过程中有诸多规则，遵循最左前缀原则，顺序至关重要
- Hash索引（Memory存储引擎）是通过索引列的值计算出hashCode,之后在相应的物理位置存取索引列的值，由于hashCode的唯一性，因此Hash索引不能进行范围查找或者是顺序查找
索引优缺点
- 索引的优点：
  - 大大加快数据的检索速度
  - 通过索引列对数据进行排序，降低数据排序的成本
  - 在使用分组和联合查询时，可以显著减少查询中分组和排序的时间
- 索引的缺点：
  - 创建和维护索引需要耗费额外的存储空间
  - 在进行增删改操作时，需要同时维护索引，会降低写操作的性能
  - 索引过多会导致优化器在选择索引时需要耗费更多的时间
索引的底层实现通常采用B+树结构，它能够保持数据稳定有序，并且方便按一定范围快速查找。

索引创建

可以创建多个普通索引，多个唯一索引，多个候选索引，一个主索引

▼
sql
复制代码
--MySQL创建索引
--主键索引
ALTER TABLE tableName ADD PRIMARY KEY(filedName);

--唯一索引
CREATE UNIQUE INDEX indexName on tableName(fieldName[, fieldName ...])

--普通索引
CREATE INDEX indexName on tableName(fieldName[, fieldName ...])

-------------------------------------------------------------------------
--Oracle创建索引
--创建普通索引
CREATE INDEX PNN_GMA_APR_IDX_01 ON PNN_GMA_APR_T(
    PNN_PRC_NBR,
    PNN_APR_SID,
    PNN_APR_STA
) TABLESPACE TBS_CUR_IDX ONLINE;

--创建唯一索引 
CREATE UNIQUE INDEX PNN_GMA_APR_IDX_PK on PNN_GMA_APR_T(
PNN_APR_PID
) GLOBAL TABLESPACE TBS_CUR_IDX ONLINE;
ALTER TABLE PNN_GMA_APR_T ADD CONSTRAINT PNN_GMA_APR_IDX_PK PRIMARY KEY(
PNN_APR_PID
) USING INDEX PNN_GMA_APR_IDX_PK ENABLE NOVALIDATE;
ALTER TABLE PNN_GMA_APR_T MODIFY CONSTRAINT PNN_GMA_APR_IDX_PK ENABLE VALIDATE;

--唯一索引创建解释
CREATE UNIQUE INDEX PNN_GMA_APR_IDX_PK on PNN_GMA_APR_T(PNN_APR_PID) GLOBAL TABLESPACE TBS_CUR_IDX ONLINE; 这条语句创建了一个名为PNN_GMA_APR_IDX_PK的唯一索引，该索引基于PNN_GMA_APR_T表的PNN_APR_PID字段。GLOBAL TABLESPACE TBS_CUR_IDX表示这个索引存储在全局表空间TBS_CUR_IDX中，ONLINE表示这个索引在创建后立即可用。
ALTER TABLE PNN_GMA_APR_T ADD CONSTRAINT PNN_GMA_APR_IDX_PK PRIMARY KEY(PNN_APR_PID) USING INDEX PNN_GMA_APR_IDX_PK ENABLE NOVALIDATE; 这条语句将PNN_GMA_APR_IDX_PK索引添加到PNN_GMA_APR_T表上，作为主键约束。ENABLE NOVALIDATE表示在添加约束时，不验证表中已有的数据是否满足这个约束。
ALTER TABLE PNN_GMA_APR_T MODIFY CONSTRAINT PNN_GMA_APR_IDX_PK ENABLE VALIDATE; 这条语句修改了PNN_GMA_APR_T表上的PNN_GMA_APR_IDX_PK主键约束，ENABLE VALIDATE表示在修改约束时，要验证表中已有的数据是否满足这个约束

适合建索引的情形
- 每张表都需要创建主键索引
  
  为什么？底层索引树需要使用主键索引做聚簇，并且推荐使用自增ID做为主键为什么是推荐自增ID做主键？因为索引在底层实现里是有序的，如果是非自增ID，容易导致索引节点分裂重排。主键不能是UUID吗？理论上可以用UUID，但是UUID占用的字节长度为36，比8字节的bigint大了4倍，每个节点能存储的索引数量从1170降到390左右，三层B+树只能存储39039016=240万条，比使用bigint少了一个数量级，所以uuid做主键不仅仅占空间，还会让索引树变高，查询性能也会受影响，不推荐使用。
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系应当建立索引
- 频繁更新的字段不适合创建索引—更新不止需要更新记录还要更新索引
- 条件用不到的字段不创建索引
- 查询中排序的字段，尽量包含在索引当中
- 查询中统计或分组的字段，尽量包含在索引中
不适合建索引的情形
- 数据量太小的情况，有无索引区别不大
- 经常做增删改的表—建索引虽然提高了查询效率，但更新性能大大降低
- 数据大量重复且分布平均的字段，不适合当索引
指定索引的方式
- ORACLE
  1. /*+ INDEX(表名，索引名) */
  2. /**+ LEADING(小表) USE_NL(大表) INDEX(大表大表的索引）*/ 工作方式是从一张表中读取数据，访问另一张表（通常是索引）来做匹配，nested loops适用的场合是当一个关联表比较小的时候，效率会更高。小表一般称为驱动表，可以是小表，也可以是小结果集；大表一般称为inner表，要求有索引。
  3. /*+ USE_HASH(大表A , 大表B ) */ 工作方式是将一个大表（通常是小一点的那个表）做hash运算，将列数据存储到hash列表中，从另一个表中抽取记录，做hash运算，到hash 列表中找到相应的值，做匹配
- MySQL
  
  USE INDEX
- TDSQL
  
  以上两种指定索引的方式都可以使用
导致索引失效的几种情况
- 没有where子句
- 使用IS NULL或IS NOT NULL
- where子句中使用函数
- 使用like做左模糊查询（如果查询结果字段也在索引中的话，索引也是可以生效的）
- where子句中使用不等于操作
- 等于和范围索引不会被合并使用
- 比较不匹配的数据类型，varchar类型会被隐式转换
- 索引不符合最左匹配原则

索引的数据结构

MySQL

所选数据结构 B+树，用多叉树尽量保证磁盘I/O操作最少
- 非叶子节点只存储key和指针，不存数据，页号节点大小默认为16kb，能存储更多的索引项（可存储上千个），内存缓存的索引也更多，磁盘访问少
- 树矮，由于第一个特性，3层的树高即可存储两千多万数据，查找任意数据时最多仅需3次磁盘IO。
  
  储存数据容量计算假设主键是bigint占8字节，指针占6字节，那么单个非叶子节点可存储的索引数量是161024 / 14 ，假设每条数据记录占1kb，那单个叶子节点可存的记录数是16条，三层树高的B+树即可存11701170*16 = 2190万条；
- 叶子节点使用双向链表串联，不用从根节点查找，范围查询时为顺序IO，速度比随机IO快。
为什么不选择其他数据结构
1. 哈希表等值查询O(1) 比B+树O(log n)块
  - 不支持范围查询，只能全表扫
  - 不支持排序，得查出全部结果再排序
  - 存在hash冲突
2. 红黑树/AVL树
  - 二叉树在数据量大时树高上涨快，同样的数据量树高比B+树多得多，磁盘IO增多查询很慢
3. B树
  - 非叶子节点会存储全部数据，每个节点存储的数据量较少，导致相同数据量下也会有更大的层高，磁盘IO更多导致查询更慢
  - 叶子节点间无关联，范围查询时需要回到上层节点再往下查找

ORACLE

所选数据结构B*树

执行计划

MySQL

explain SQL statement
开启慢查询日志 show variable like '%slow_query_log%; set global slow_query_log = 1

执行计划分析

字段	含义	重要说明
id	查询标识符	SQL执行顺序的标识，id值越大优先级越高，id相同则从上往下执行
select_type	查询类型	SIMPLE(简单查询), PRIMARY(主查询), SUBQUERY(子查询), DERIVED(派生表查询)UNION(联合查询),UNION RESULT(联合查询结果)
table	表名	显示这一行数据是关于哪张表的
type	访问类型	system > const > eq_ref > ref > range > index > ALL (从左到右性能降低) system:表只有一条数据 const:主键索引或唯一索引，只匹配一行数据 eq_ref:唯一性索引扫描 ref:非唯一性索引扫描 range:范围扫描 index:全索引扫描 all:全表扫描
possible_keys	可能使用的索引	显示可能应用在这张表上的索引
key	实际使用的索引	实际使用的索引，如果为NULL则没有使用索引
key_len	索引使用的字节数	可能的最大长度，并非实际使用长度，越少越好
ref	被使用的索引字段
rows	扫描行数	预计要读取的行数
filtered	过滤百分比	表示符合查询条件的数据百分比
Extra	额外信息	包含不适合在其他列中显示但很重要的额外信息 using filesort 说明使用外部索引排序，并不是使用表内索引排序 using temporary 使用临时表，性能损耗大 using index 使用覆盖索引，避免回表 using where 使用查询 using index condition 使用索引下推，减少回表

Oracle

explain plan for SQL statement;
SELECT * FROM TABLE (dbms_xplan.display());

执行计划分析

类别	参数/列名/操作	说明	示例/备注
显示格式	`BASIC`	仅显示操作ID和名称	`FORMAT=>'BASIC'`
	`TYPICAL`	默认格式，显示成本、谓词等	优化器标准输出
	`ALL`	显示分区、别名、投影等完整信息	详细分析使用
	`ADVANCED`	ALL格式+OUTLINE数据	深度调试使用
基本列	Id	操作序号，决定执行顺序	从0开始，子操作缩进显示
	Operation	操作类型（如TABLE ACCESS）	见下方操作类型详解
	Name	对象名称（表/索引）	`EMPLOYEES`、`PK_EMP_ID`
	Rows (E-Rows)	预估返回行数（基数）	对比A-Rows判断统计准确性
	Bytes	预估返回字节数
	Cost (%CPU)	优化器估算成本值	成本越低越好
	Time	预估执行时间
	Pstart/Pstop	分区访问起止编号	分区表范围扫描时使用
扩展统计	Starts	操作执行次数	`Starts>1`可能效率低
	A-Rows	实际返回行数	`SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST'))`
	A-Time	实际耗时（含子操作）
	Buffers	逻辑读块数
	Reads	物理读块数
	Writes	物理写块数
	OMem/1Mem	最优/一次传递内存需求
	Used-Mem	实际PGA内存使用
	Used-Tmp	临时表空间使用量
计算公式	缓冲区命中率	`(Buffers-Reads)/Buffers*100`	理想值>95%
	基数准确率	`A-Rows/E-Rows`	接近1为准确
	每执行行数	`A-Rows/Starts`	判断执行效率
表访问	TABLE ACCESS FULL	全表扫描	适合大批量数据，小表可接受
	TABLE ACCESS BY INDEX ROWID	索引回表	需搭配索引扫描
	TABLE ACCESS SAMPLE	采样扫描	统计分析使用
索引扫描	INDEX UNIQUE SCAN	唯一索引扫描	最高效，返回0-1行
	INDEX RANGE SCAN	索引范围扫描	最常用，支持范围查询
	INDEX FULL SCAN	索引全扫描	避免回表，覆盖索引
	INDEX FAST FULL SCAN	索引快速全扫描	类似全表扫，读索引块
	INDEX SKIP SCAN	跳跃扫描	复合索引前导列选择性低
连接方式	NESTED LOOPS	嵌套循环连接	驱动表小，被驱动表有索引
	HASH JOIN	哈希连接	大数据集，需内存支持
	SORT MERGE JOIN	排序合并连接	已排序数据或需排序输出
	CARTESIAN JOIN	笛卡尔积	应避免，检查连接条件
谓词类型	access	访问谓词（定位数据）	出现在索引扫描、连接操作
	filter	过滤谓词（筛选数据）	访问后过滤，消耗CPU
并行执行	IN-OUT	数据流方向	`P->S`, `P->P`, `S->P`
	PQ Distrib	分布方式	HASH, RANGE, BROADCAST
	TQ	表队列标识	并行进程间通信
	PCWC/PCWP	并行合并操作	与子/父操作合并执行
关键判断	Starts>1且A-Rows小	可能存在低效操作	如NESTED LOOPS被驱动表重复扫描
	Buffers/A-Rows过大	逻辑读效率低	检查索引或过滤条件
	E-Rows与A-Rows差异大	统计信息过时	需收集统计信息
	驱动表过大	NESTED LOOPS性能差	考虑改为HASH JOIN

什么是覆盖索引？是指二级索引中已经包含查询所需要的字段，直接从二级索引取数据，不用再回表查询。减少I/O操作，减少内存占用，查询更快什么是回表？回表是指使用二级索引查询时，因为二级索引中只存索引列的值和主键值，拿不到其他字段的数据，当需要查询其他字段时，就需要拿到主键再去聚簇索引中查询一遍数据行，这个过程就叫回表。什么是索引下推？把部分查询条件下推到存储引擎层，在引擎层就把不符合条件的数据过滤掉，减少回表查询

锁机制

读锁/共享锁（S锁）阻塞写，不阻塞读
- 表加S锁上锁的线程，不允许再对库的其他表进行操作，只允许读当前表；其他线程不允许对加锁的表进行更新，会线程等待。
- 手动添加共享锁 select * from tableName where id = 1 in share mode;
写锁/排它锁（X锁）阻塞写和读
- 表加X锁上锁的线程，可以对当前表进行操作；其他线程不允许对表操作，即使读也会线程等待
- 行加X锁当前行只允许一个线程操作，其他线程被阻塞，允许读
间隙锁Next-Key锁
- 使用范围条件请求共享锁或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内并不存在的间隙记录，也会进行加锁
注意事项
- 索引失效会导致行锁升级为表锁
分析行锁
- show status like ‘innodb_row_lock%’;
- innodb_row_lock_current_waits 当前等待锁定的数量
- innodb_row_lock_time 系统启动到现在锁定的总时间
- innodb_row_lock_time_avg 平均等待时间
- innodb_row_lock_time_max 最大等待时间
- innodb_row_lock_waits 等待总次数