数据库详解

数据库详解

基础知识：

元组：表中的每行

码：表中的列

数据库范式

第一范式：表中的字段不可以再分割

第二范式：非主属性全部依赖于主键

第三范式：解决了数据冗余过大，消除非主属性对码的传递函数依赖

Drop、Delete与truncate区别

drop：直接把表删掉

truncate：清空数据

delete：删除数据，where 列名=值

truncate和drop属于DDL（数据定义语言） 不放到rollback segment中，不能回滚

delete语句是DML（数据库操作语言），会放到rollback segment中，事务提交之后才生效

SQL语法

插入数据：

insert into 向表中插入新数据

insert into user values(10,'root','root')

Order By 排序

group by 分组

Char和VarChar的区别

Char是定长字符串，Varchar是变长字符串

char会在右边填充空格达到指定的长度

varchar再存储时，使用1或2个额外字符记录字符串的长度，检索时不需要处理

varchar（100）和varchar(10)的区别

都是变长类型，varchar(100)可以满足更大范围的字符存储需求，而varchar(10)存储超过10个字符时，选哟修改表结构

占用的磁盘存储空间时一样的

varchar(100)会消耗更多的内存，这是因为varchar类型在内存操作时，会分配固定大小的内存块来保存值，例如在排序时，varchar(100)按照100这个长度来进行，会消耗更多的内存

DECIMAL和FLOAT/DOUBLE的区别

DECIMAL是定点数，FLOAT/DOUBLE是浮点数，DECIMAL可以存储精确的小数，FLOAT/DOUBLE只能存储近似的小数值

NULL和’'的区别

NULL代表一个不确定的值，两个NULL也不一定相等，但是使用DISTINCT,GROUP BY ,ORDER BY时，NULL被认为是相等的

''的长度是0，不占用空间，NULL需要占用空间

NULL会影响聚合函数的结果，Count(*)会统计NULL，Count(列名)会忽略NULL值

查询NULL值时，必须使用IS NULL 或者 IS NOT NULL来判断，而不能使用=、!=等比较符号

Boolean类型表示

TINYINT(1)

MYSQL基础架构

Client->连接器（管理连接，权限验证）- >查询缓存（命中，直接返回，8.0后溢出）->分析器（语法分析，词法分析）->优化器（执行计划生成、索引选择）->执行器（操作引擎，返回结果）->存储数据

• **连接器:**身份认证和权限相关

• **查询缓存:**执行查询语句时,会先查询缓存

• **分析器:**如果没有命中缓存,SQL会经过分析器

• **优化器:**按照MYSQL认为最优的方案去执行

• **执行器:**执行语句，然后从存储引擎返回数据

MySQL存储引擎

show ENGINES可以查看Mysql支持的所有存储引擎

SHOW VARIABLES LIKE ‘%storage_engine%’ 查看当前Mysql默认的存储引擎

存储引擎架构

插件式架构：支持多种存储引擎，存储引擎式基于表的，而不是数据库

MyISAM和InnoDB有什么区别

	MyISAM	InnoDB
是否支持行级锁	只支持表级锁（锁住整张表）	默认行级锁
是否支持事务	不支持	支持（四种事务隔离等级）
是否支持外键	不支持	支持（一般不建议在数据库层面使用外键）
支持数据库崩溃后的安全恢复	不支持	支持（依赖于redo log）
是否支持MVCC	不支持	支持（行级锁的升级）
索引实现	B+树（索引文件和数据是分离的）	B+树（数据文件本身就是按照B+树组织的一个索引结构，树的叶子节点data保存了完整的数据）
性能	弱	innodb的读写能力随着CPU核数呈线性增长

MyIASM和InnoDB如何选择

大多数情况下使用InnoDB存储引擎，但是在某些读密集的情况下，使用MyISAM也是合适的。日常开发中，不需要使用MyISAM作为存储引擎

MYSQL索引

索引含义:一种用于快速查询和检索数据的数据结构,本质可以堪称时一种排序好的数据结构

索引底层结构选择

B树和B+树

• B树的所有节点既存放键也存放数据,而B+树只有叶子节点存放key和data,其他内节点只存放key
• B树的叶子节点都是独立的,B+树的叶子节点有一条引用链指向与他相邻的叶子节点
• B树的检索过程相当于对范围内的每个节点的关键字做二分查找,可能还没到叶子节点,检索就结束了,而B+树就很稳定,任何查找都是从根节点到叶子节点的过程
• 在B树进行范围查询时,首先要找到查的下限,然后进行中序遍历,直到找到查找的上限,而B+树的范围查询,只需要对链表进行遍历就行
• MyIASM和InnoDB引擎对B+树实现的区别
  • MyISAM引擎中,B+叶子节点的data域存放的是数据记录的地址,非聚簇索引
  • InnoDB引擎中,数据文件本身就是索引文件,聚簇索引

索引类型总结

按数据结构划分

• BTree索引,MySQL中默认和最常用的索引类型,只有叶子节点存储value,非叶子节点只有指针和key
• 哈希索引:类似键值对
• 全文索引:
• RTree索引

按底层存储方式划分

• 聚簇索引:索引结构和数据存放在一起的索引,InnoDB中的主键索引就属于聚簇索引
• 非聚簇索引:索引结构和树分开存放的索引

按照应用维度划分:

• 主键索引:加速查询+列值唯一(不为NULL),表中只有一个
• 普通索引:仅加速查询
• 唯一索引:加速查询+列值唯一(可以为NULL)
• 覆盖索引:一个索引包含所需查询的字段的值
• 联合索引:多列值组成的一个索引,专门用于组合搜索,其效率大于索引合并
• 全文索引:对全文的内容进行分词和搜索,目前只有CHAR、Varchar,TEXT列上可以建全文索引

主键索引

一张表只能有一个主键,不能为NULL

二级索引

二级索引的叶子节点存储的数据就是主键,通过二级索引可以找到主键的位置

聚簇索引

索引结构和数据一起存放的索引,并不是一种单独的索引类型.InnoDB中的主键索引就属于聚簇索引

B+树的非叶子节点存储索引,叶子节点存储索引和索引对应的数据

优点:

• 查询速度很快
• 对排序查找和范围查找优化

缺点:

• 依赖于有序的数据
• 更新代价大

非聚簇索引

索引结构和数据分开的索引,并不是一种单独的索引类型,二级索引就属于非聚簇索引,MYISAM中,不管主键还是非主键,使用的都是非聚簇索引

优点:

• 更新代价小

缺点:

• 依赖于有序的数据
• 可能会二次查询(覆盖索引就不需要回表)

覆盖索引和联合索引

覆盖索引

如果一个索引包含所需要查询的字段的值,称之为覆盖索引

联合索引

使用表中的多个字段创建索引,也称组合索引

alter table cus_order add index id_score_name(score,name)

最左前缀匹配原则

在使用联合索引时,MYSQL会根据索引中的字段顺序,从左到右依次查询条件中去匹配如果查询条件与联合索引中最左侧字段相匹配,就会用这个字段过滤一批数据,遇到范围查询如(>,<)会停止匹配,在使用联合匹配时,可以将区分度高的字段放在最左边

索引注意事项

• 被频繁更新的字段应该谨慎建立索引
• 限制每张表上的索引数量(5个以下)
• 尽可能建立联合索引而不是单列索引
• 注意避免冗余索引
• 字符串类型的字段使用前缀索引
• 避免索引失效
  • 创建了组合索引,但是查询未遵循最左匹配原则
  • 在索引列上进行计算
  • 以%开头的LIKE查询
  • 查询条件中使用OR,且OR的前后条件中有一个列没有索引
  • 发生隐式转换

判断语句是否走了索引

EXPLAIN语句

MySQL查询缓存

执行查询语句时，先查缓存，8.0版本后移除，这个功能不太实用

在my.cnf中开启MYSQL缓存

开启缓存后在同样的查询条件和数据情况下，会直接在缓存中返回结果

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁

MYSQL日志

MYSQL日志主要包括错误日志,查询日志,慢查询日志,事务日志,二进制日志几大类

redo log

是InnoDB存储引擎独有的,让MYSQL有了崩溃恢复的能力

Mysql会把硬盘一页的数据加载出来,放入到bufferpool中,更新表的时候,也是在buffer pool里更新,然后刷盘到redo log文件里,理想情况下,事务提交一次就会进行刷盘,实际上是根据刷盘策略来进行的

刷盘时机

1. 事务提交时

   1. 有三种刷盘
   2. 0:每次事务提交时不进行刷盘
   3. 1:每次事务提交就刷盘
   4. 2:每次提交只把redo log内容写入page cache

2. log buffer空间不足时

3. 事务日志缓冲区满

4. Checkpoint检查点

5. 后台刷新线程

   1. InnoDB存储引擎有一个后台线程,每隔1秒,就会把redo log buffer中的内容写到系统缓存中,然后调用fsync刷盘

6. 正常关闭服务

日志文件组

硬盘上存储的redo log 日志文件不止一个,而是以一个日志文件组的形式出现的,采用环形数组形式,write pos 和checkpoint

binlog

redo log是物理日志,记录的内容是在某个数据页做了什么修改,属于innodb存储引擎

而binlog是逻辑日志,记录的内容是语句的原始逻辑,属于MySQL Server层

不管用什么引擎,都会产生binlog日志

数据库的数据备份、主备、主主、主从都离不开binlog

记录格式:

• statement:记录的是SQL语句原文
• row:通过mysqlbinlog工具解析出来,如果now_time = now()会存在数据不一致问题
• mixed:将上面两种形式结合起来

binlog的写入机制

事务执行过程中,先把日志写到binlog cache,等事务提交的时候,再把binlog cache 写到binlog文件中(page cache),调用fsync方法,将缓存写入磁盘中

fsync的时机可以用参数控制

• 0表示每次提交事务只write,由系统自己判断什么时候执行fsync

• 设置为1,每次事务提交都会执行

• 设置为N,累积N个事务后提交

两阶段提交

• redo log 让innodb有了崩溃恢复能力
• binlog保证了mysql集群架构的一致性

如果写入了redo log,但是在写入binlog时mysql重启,从表和主表会不一致,这时候就要用两阶段提交,在写入redo log时是prepare阶段,事务提交时,commit redo log

undo Log

保证事务的原子性，回滚日志会先于数据持久化到磁盘上

MYSQL事务

事务的ACID：

原子性：事务是最小的执行单位，不能分割，要么全部完成，要么都失败

一致性：执行事务前后,数据保持一致

隔离性:一个用户的事务,不被其他事务所干扰

持久性:一个事务被提交之后,对数据库中数据的改变是持久的

并发事务带来的问题:

脏读:一个事务读取数据并修改了数据,此时数据没有提交,但是这个修改对其他事务是可见的,这时另一个事务读取了这个数据,事务1发生了回滚,数据并没有提交到数据库,第二个事务读到的就是脏数据

丢失修改:第一个事务读取一个数据时,另一个事务也访问了该数据,那么在第一个事务修改了这个数据后,第二个事务也修改了这个数据,第一个事务的修改就丢失了

不可重复读:指一个事务内多次读同一个数据,这个事务还没有结束时,另一个事务也访问了该数据,在第一个事务两次读取数据之间,读取的数据不一样

幻读:与不可重复度类似,第一个事务读取了几行数据,另一个并发事务插入了一些数据,第一个事务在查询时,发现多了一些原本不存在的记录,就像发生了幻觉一样

解决方法:
	1. 使用串行化隔离等级
	2. 给整张表加锁
	3. 使用临建锁

不可重复读和幻读的区别:

• 不可重复度的中i但时内容修改或记录减少
• 幻读的重点在于记录新增,比如执行同一条查询语句,返现查到增加的记录

幻读可以看作不可重复读的一种特殊情况,单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样

delete和udpate操作时,可以直接对记录加锁

insert操作,需要依赖间隙锁

并发事务的控制方式

锁+MVCC

锁:MYSQL主要通过读写锁来实现并发控制

MVCC:多版本并发控制方法,依赖于:隐藏字段、read view、undo log

• undo log:用于记录某行数据的多个版本的数据
• read view和隐藏字段：用来判断当前版本数据的可见性

MVCC具体实现：

1. 读操作:
   • 事务会先找符合条件的数据行,并选择符合其事务开始时间的数据版本进行读取
   • 如果某个数据行有多个版本,事务会选择不晚于其开始时间的最新版本,确保事务只读取在它开始之前意见存在的数据
   • 快照读
2. 写操作
   • 事务会为要修改的数据创建一个新的版本,并将修改后的数据写入新版本
   • 新版本的数据会带有当前事务的版本号
   • 原始版本的数据仍然在,供其他事务快照读
3. 事务提交和回滚
   • 当一个事务提交时,所作的修改会成为数据库的最新版本,并且对其他事务可见
   • 当一个事务回滚时,所作的修改会被撤销,对其他事务不可见
4. 版本回收
   • 防止数据库中的版本无限增长,MVCC会定期进行版本的回收,删除不需要的旧版本数据,从而释放空间

一致性非锁定读:通常做法加一个版本号或者时间戳字段,更新数据时版本号+1

**锁定读:**读取的数据时最新版本

INNODB对MVCC的实现：

• 隐藏字段
  • 在内部，InnoDB为每行数据添加了三个隐藏字段
    • DB_TRX_ID，表示最后一次插入或更新该行的事务id
    • DB_ROLL_PTR:回滚指针，指向该行的undo log
    • DB_ROW_ID：如果没有设置主键且没有唯一非空索引时，InnoDB会使用该id来生成聚簇索引
• ReadView
  • 主要是用来做可见性判断
    • m_low_limit_id：目前出现的最大的事务ID+1，大于这个ID的事务对该数据版本均不可见
    • m_up_limit_id：小于这个ID的事务对该版本都不可见
    • m_ids：活跃事务ID列表
• RR和RC隔离级别下MVCC的差别
  • RC隔离级别下，每次select查询前都会生成一个read view
  • 在RR隔离级别下，只在事务开始后第一次select数据前生成一个read view

SQL定义了哪些事务隔离级别

• 读取未提交：允许读取尚未提交的数据变更，导致脏读、幻读或不可重复读
• 读取已提交：允许读取事务已经提交的数据，可以组织脏读，但是幻读和不可重复读仍有可能发生
• 可重复读：对同一字段的多次读取结果都是一致的，除非数据被事务本身修改，可以阻止脏读和不可重复读，但仍有可能发生幻读
• 可串行化：最高的隔离级别，所有的事务依次逐个执行，可以防止脏读、不可重复读和幻读

MYSQL的隔离级别基于什么实现

基于锁和MVCC机制共同实现

MYSQL锁

表级锁和行级锁

• 表级锁：MYSQL中粒度最大的一种锁，针对非索引字段加的锁，对当前操作的整张表加锁，高并发下效率极低
• 行级锁：MYSQL中粒度最小的一种锁，针对索引字段加的锁，但是开销大，加锁慢，会出现死锁

行级锁的使用注意事项

InnoDB的行锁是针对索引字段加的锁，当我们执行UPDATE、DELETE语句时，如果WHERE没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有记录进行加锁

InnoDB有哪几类行锁

• 记录锁：单个记录上的锁
• 间隙锁：锁定一个范围，不包括记录本身
• 临键锁（Next-Key Lock）：锁定一个范围，包括记录本身，主要目的是为了解决幻读问题

innodb默认使用临建锁，但是如果操作的索引是唯一索引或主键，会降级为Record Lock，仅锁住索引本身

共享锁和排他锁

• 共享锁（S锁）:又称读锁，事务在读取记录时获取共享锁，允许多个事务同时获取 FOR SHARE
• 排他锁（X锁）：又称写锁，独占锁 FOR UPDATE

意向锁

如果用到表锁的话，如何判断表中有没有行锁，一行一行遍历性能太差，需要一个意向锁来判断是否可以对某个表使用表锁

• 意向共享锁（IS锁）：加共享锁时，必须先取得该表的IS锁
• 意向排他锁（IX锁）：加排他锁时，先获取IX锁

当前读和快照读

• 快照读：单纯的select语句
• 当前读：（一致性锁读）给记录加X锁或者S锁

自增锁

MYSQL性能优化

MYSQL如何存储IP地址

将IP地址转换成整型数据存储，MYSQL提供了两种方法来处理IP地址

INET_ATON 把IP转为无符号整型

INET_NTOA 把整型的IP转为地址

有哪些常见的SQL优化手段

具体实现

如何分析SQL的性能

使用EXPLAIN命令来分析SQL的执行计划

explain select from 

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

MYSQL高性能优化规范

尽量控制单表数据量的大小,建议控制在500万以内

索引规范

单表索引不超过5个

禁止使用全文索引

禁止给表中的每一列都单独建索引

隐式转换造成索引失效

1. 查询的字段类型和数据库中实际存储的数据类型不一样
2. 当where查询操作符左边为数值类型时,发生了隐式转换,那么对效率影响不大,但是不推荐
3. 当where操作符左边为字符串类型时发生隐式转换,会导致索引失效
4. 字符串转换为数值类型时,非数字开头的字符串会转化为0,以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果