数据库事务 数据库索引 数据库优化

数据库三大范式

• 第一范式：原子性，每列都不可再分。举例：省市&地址，地址不够具体，无法按照省、市进行分类和查找

• 第二范式：唯一性，不存在部分依赖，每张表中都只能包含“一种”信息，举例：一张表中存在学生、课程和分数三个属性，存在冗余，可以分为学生表、课程表和成绩表三个表

• 第三范式：直接性，不存在传递依赖，每一列都应该和主键直接相关，不能包含其它表中的非主键列的信息。举例：一张表中存在学生、学校、学校地址三个属性，学校地址和学校直接相关，不和学生直接相关，存在冗余

存储过程

• 存储过程是指完成一个特定功能的sql语句集，经过编译之后存放在数据库中，用户根据过程名称和参数调用相应的过程。

• 存储过程是可编程的函数，在数据库中创建并保存，由sql语句和控制语句组成。想在不同应用程序或平台执行相同的函数或者封装特定功能的时候是非常有用的，是面向对象的一种模拟实现。

• 优点

  • 提高sql的灵活性和表达能力：程序过程是sql语句和控制语句的结合

  • 标准的组件化编程：一次编译，到处调用

  • 提高执行效率：与批处理相比，只需要一次编译和一次优化，执行效率更快

  • 较少网络运输量：只需要传输过程语句，不需要传输数据库操作结果

  • 安全保证：通过限制存储过程的执行权限来保证安全性

数据库事务&ACID&隔离级别

• 数据库事务：事务是并发控制的基本单位，由一系列的操作指令构成

• 事务的四大特性：ACID

  • 原子性（Atomicity）：事务中包含的所有操作要么全部执行成功，要么执行失败回滚

  • 一致性（Cinsistency）：事务执行前和执行后都必须处于一致性的状态

  • 隔离性（Isolation）：一个事务在执行过程中，不能被其它事务干扰，多个并发事务之间要相互隔离

  • 持久性（Durability）：已提交的事务的操作对数据库中的数据的影响是永久的

• 不考虑隔离性可能带来的问题

  • 丢失更新：多个事务同时修改一条数据，后面事务的修改结果会覆盖前面事务的修改

  • 脏读：一个事务处理过程中读取了另外一个未提交事务中的数据

  • 不可重复读：一个事务处理过程中需要多次读相同的内容，多次读内容时读取了另外一个已提交事务更新后的数据，导致前后读取的数据不一致

  • 幻读：一个事务的操作需要多次读取相同的数据，多次读的过程中读取到了另一个事务已提交的新插入后的数据，导致读取的数据数量不同

• 隔离级别

  • Read Uncommited：读取未提交的内容，可能导致脏读、不可重复读、幻读。读的时候不加锁，写的时候加行级写锁。

  • Read Committed：读取提交的内容，可能导致不可重复读、幻读；大多数数据库默认的隔离级别。读的时候加行级读锁，读完该行释放，写的时候加行级写锁，事务结束后释放。

  • Repeated Read：可重读，可能导致幻读；MySQL默认的隔离级别；可以通过多版本并发控制解决幻读的问题。读的时候加行级读锁，事务结束后释放，写的时候加行级写锁，事务结束后释放。

  • Serializable：序列化，最高的隔离级别，强制事务排序，在每个数据行上加上共享锁，解决幻读问题；可能导致大量的超时现象和锁竞争。读的时候加表级读锁，事务结束后释放，写的时候加表级写锁，事务结束后释放。

锁机制

• 按粒度划分

  • 行级锁：开销大、加锁慢、会导致死锁、加锁粒度最小、锁冲突概率最低、并发度最高

  • 表级锁：开销小、加锁快、不会导致死锁、加锁粒度最大、锁冲突概率最高、并发度最低

  • 页级锁：各项性能介于行级锁和表级锁之间，会发生死锁

• 按级别划分

  • 读锁：阻塞写锁，不会阻塞读锁

  • 写锁：同时阻塞读锁和写锁

• InnoDB存储引擎使用行锁和表锁，行级锁机制依赖与索引，默认使用行级锁

  MyISAM存储引擎使用表级锁

索引

• 索引是建立在数据表列上的数据结构，可以用来存储特定列的值并排序

• 优点是加快检索速率，并且可以保证数据的唯一性，加速表的连接等等；缺点是索引的建立和维护需要时间开销，同时也会占用物理内存，因此需要进行范围查找、需要排序、需要频繁查找以及主键的列建立索引提高查询和检索的效率

• 常见的索引的数据结构：B+树、hash索引

• InnoDB查询引擎的索引和MyISAM查询引擎的索引

  • InnoDB的主索引中数据文件本身是索引文件，可以提供非常快速的主键查找性能；辅助索引中数据文件是主键，需要再根据主索引表进行内容查找，所以主键不要设置太大而且最好自增，方便B+树的顺序查找

  • MyISAM的主索引和辅助索引差别不大，数据和索引是分开的，叶子结点的数据域存储的是指向实际数据的地址

• 索引类型：普通索引、唯一索引（值必须唯一）、主键索引（会为主键自动创建索引）、全文索引、组合索引（最左前缀）

• 联合索引最左前缀原则

  • 联合索引中区分度最高的要放最左边

  • 存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置

• 不建议使用索引的情况

  • 记录数量比较少

  • 索引的选择性比较低

• 优化策略：使用前缀索引（短索引）

  • 短索引可以提高查询速度，节省磁盘空间和IO操作，减少索引文件的维护开销

• 磁盘I/O分析：

  • 索引在内存，O(logn)时间忽略不计

  • 用二叉树存储，

  • 用B+树存储，，通常InnoDB的B+树为2~4层，一般情况下根节点常驻内存，因此需要1~3次磁盘IO

视图

• 在基本表上建立的表，是对基本表的一种抽象和在逻辑意义上建立的新关系

• 优点：方便操作；提高安全性，只能查询和修改能看到的数据；逻辑上的独立性，屏蔽了真实表结构带来的影响

• 缺点：性能下降；无法通过复杂的视图修改表中的数据

分区

• 某个表数据量太大，查询速度会变慢，因此将表的文件分割成为许多小块

• 这样可以分别在多个磁盘上存储，对某些时间相关的应用，可以按时间分区

分库分表

• 垂直分表和水平分表联合使用的方式

读写分离

• 读写分离解决的是数据库的写入影响了数据库查询效率的问题

• 读写分离的实现基础是主从复制，主数据库执行写操作，从数据库执行读操作，然后主数据库利用主从复制将自身数据的改变同步到从数据库中

• 可以部署多个从服务器提高读的速度，同时提供了冗余数据的功能，服务器宕机可以尽快恢复

explain执行计划

• 可以模拟优化器执行SQL查询语句，分析select语句或表结构的性能瓶颈，得到select查询效率低下的原因，从而进行改进

• 字段信息：ID、查询类型（普通查询/联合查询/子查询）、表、索引、索引长度、访问类型（const/ref/range）

show profiles

• 查询最近执行的SQL语句的运行状态，包括状态和耗时等

通用查询日志分析和慢查询日志分析

• 慢查询日志：执行时间超过指定阈值的SQL语句，可能是表太大或者没有建立索引导致的

数据库优化

• 优化SQL语句

  • 通用查询日志和慢查询日志分析定位执行效率低的SQL语句

  • explain执行计划分析低效的SQL执行计划

  • show profiles分析SQL执行情况

  • 写统一的SQL语句；尽量使用索引，避免做全表的搜索；尽量使用别名；尽量避免大事务操作/尽量避免向客户端返回大量数据

• 索引优化：使用短前缀

• 尽量少join；尽量少排序；尽量避免select*；尽量用join代替子查询；尽量少or；尽量避免类型转换

• 表结构优化：字符类型；数字类型；时间类型等

存储引擎

InnoDB

• 聚簇索引，使用B+树作为索引结构，主索引的叶子结点即数据文件，因此基于主键查找速度非常快，辅助索引的叶子结点是主键，因此使用辅助索引进行查找需要先查找到主键，在基于主索引查找，需要两次查找，由于辅助索引存储的内容是主键，因此基于InnoDB的数据库表的主键应该小一些并且最好自增
• 提供事务支持，包括事务回滚、提交和ACID特性，以及提供系统崩溃后的修复
• 默认使用行级锁，当查询命中索引时使用行级锁，当查询未命中索引时使用表级锁，因为行级锁并没有锁到物理表上，而是锁到了索引上
• 提供MVCC的行级锁，提高并发操作性能，它的CPU效率基本高于其它所有的基于磁盘的关系型数据库存储引擎，是MySQL默认的存储引擎
• 提供外键支持
• 不使用一个单独的变量保存表的具体行数，因为不同事物看到的表的数量是不同的

MyISAM

• 非聚簇索引，使用B+树作为索引结构，数据文件和叶子结点分开存储，叶子结点存储的是指向数据节点的指针，主索引和辅助索引没有太大的区别
• 不支持事务
• 使用表级锁完成并发控制
• 不提供外键支持
• 用一个单独变量保存表的总行数，加速select*操作

适用场景

• InnoDB适用于并发要求高，写密集和需要事务支持以及外键支持的表
• MyISAM适用于读密集且不需要事务支持的表，适用于主从分离数据库中的从数据库

SQL注入

SQL注入是一种将SQL代码添加到输入参数中，传递到服务器解析并执行的一种攻击手法

预防SQL注入

• 严格检查输入变量的类型和格式，如对字符串变量使用正则表达式进行过滤
• 过滤和转义特殊字符
• 利用MySQL的预编译机制，一次编译多次执行，提高效率，保证安全性

on和where

on和where都是筛选条件，on在表连接的过程中筛选，对left join来说无论on的条件是否为真，都会返回左表中的内容，where是对表连接之后得到的临时表进行筛选

group by和having

group by是分组，having是按照分组后的结果进行统计和过滤

char和varchar

char长度固定，存取速度更快，varchar长度可变，节省空间，但是存取速度更慢一些

SQL语句

select* from items where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(createtime)