事务四大特性(ACID)
- 原子性:是指事务是一个不可在分割的工作单位,事务中的操作要么发生,要么都不发生。
- 一致性:是指在事务开始之前和事务结束之后,数据库非完整性约束没有被破坏。即数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。
- 隔离性:多个事务并发访问,事务之间是隔离的,一个事务不应该影响其他事物运行效果。
事务之间相互影响:脏读、不可重复读、幻读、丢失更新- 脏读:一个数据读取了另一个事务未提交的数据,而该数据是有可能回滚的。
- 不可重复读:一个事务范围内两个相同的查询却返回了不同数据,是由查询时系统中其他事务修改的提交引起的。
- 幻读:指当事务不是独立执行时发生的一种现象。例如A事务修改表的全部数据行,同时B事务是插入一行新数据。那么以后操作发生A事务的用户会发现表中有未修改的数据行。
- 丢失更新:两个事务同时读取同一条记录,A先修改、B后修改(B不知道A改过),B提交将覆盖A的修改。
- 持久性:意味着在事务完成以后,该事务所对数据库所做的更改便持久的保存在数据库中,并不会被回滚。
数据库隔离级别、MySQL的默认级别
隔离级别 | 脏读 | 不可重复读 | 幻读 | 丢失更新 | 并发模型 | 更新冲突检测 |
---|---|---|---|---|---|---|
未提交读:Read Uncommitted | 是 | 是 | 是 | 是 | 悲观 | 否 |
已提交读:Read committed(MySQL默认级别) | 否 | 是 | 是 | 是 | 悲观 | 否 |
可重复读:Repeatable Read | 否 | 否 | 否 | 是 | 悲观 | 否 |
可串行读:Read Uncommitted | 否 | 否 | 否 | 否 | 悲观 | 否 |
InnoDB、MyISAM存储引擎的区别
类型 | 是否支持事务 | 锁级别 | 是否支持外键 | 支持索引 | 是否记录行数 | 是否为MySQL默认引擎 | 删除表操作 | 适用场景 | 对于自增长的字段 |
---|---|---|---|---|---|---|---|---|---|
InnoDB | 支持 | 支持行级别锁、批量更新是也支持表级别锁 | 支持 | 支持 B-tree、Full-text 等索引,不支持 Hash 索引 | 不记录行,执行count(*)要扫描全表计算行数 | MySQL5.5.5以后为默认引擎 | 一行行的删除、效率非常低 | 适合频繁修改以及安全性较高的操作 | 必须包含只有该字段的索引 |
MyISAM | 不支持 | 支持表级别锁 | 不支持 | 支持 B-tree、Full-text 等索引,不支持 Hash 索引 | 记录行数,执行count(*)直接返回,当语句中包含where时需要扫描全表计算行数 | 不是 | 重建表 | 适合查询以及插入为主的应用 | 可以和其他字段建立联合索引 |
查询语句不同元素(where、jion、limit、group by、having等)先后执行顺序
join > where > group by > having > limit
B-tree索引、hash索引的区别
类型 | 差别 |
---|---|
B-tree索引 | B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互连接。 B+树的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。 |
hash索引 | 哈希索引是采用一定的哈希算法,把键值换算成新的哈希值,一次哈希算法即可定位到相应的位置,速度非常块。 |
索引总结
索引分类
MySQL索引分类(索引不报含有null值的列,索引项可以为null(唯一索引、组合索引等),但是列中有null值就不会被包含在索引中)。
- 普通索引
或创建表时指定create index index_name on table(column);
create table( ..., index index_name column )
- 唯一索引:类似普通索引,索引列的值必须唯一(可以为空,和主键索引不同)
或创建表时指定create unique index index_name on table(column);
create table( ..., unique index_name column )
- 主键索引:特殊的唯一索引,不允许为空,只能有一个,一般是在建表时指定primary key(column)。
- 组合索引:在多个字段上创建索引,遵循最左前缀原则。
alter table t add index index_name(a,b.c);
- 全文索引:主要用来查询文本中的关键字,不是直接与索引中的值相比较,像是一个搜索引擎,配合match against使用,现在只有char、varchar、text上可以创建全文索引。(在数据量较大时,先将数据放在一张没有全文索引的表里,然后在利用create index穿件全文索引,比先生存索引再插入数据快得多)
何时不使用索引
- 表记录太少
- 数据重复且分布平均的字段(只有很少数据值的列)
- 经常插入、删除、修改的表要减少索引
- text、image等类型不建议建立索引,这些列的数据量大(假如text前10个字符唯一,也可以对text前10个字符建立索引)
- MySQL能估计出全表扫描比使用索引更快时,不使用索引
索引何时失效
- 组合索引未使用最左前缀,例如组合索引(A,B),where B=b不会使用索引
- like 未使用最左前缀,where A like ‘%China’
- 搜索一个索引而在另一个索引上做order by,where A=a order by B,只是用A上的索引,因为查询只使用一个索引
- or 会使索引失效。如果查询字段相同,也可以使用索引。例如where A=a1 or A=a2(生效),where A=a or B=b(失效)
- 如果列类型是字符,要使用引号。例如 where A=‘China’,否则索引失效(会进行类型转换)
- 在索引列上的操作,函数(upper()等)、or、!=、<>、in、not in等
聚集索引、非聚集索引的区别
- 聚集索引:表示表中存储的数据按照索引的顺序存储,检索效率比非聚集索引高,但对数据更新影响较大。
- 非聚集索引:表示数据存储子在一个地方,索引存储在另一个地方,索引带有指针指向数的存储位置,非聚集索引加索效率比聚集索引低,但对数据更新影响较小。
乐观锁、悲观锁
类型 | 悲观锁原理 | 如何实现 | 操作耗时 |
---|---|---|---|
乐观锁 | 指操作库(更新操作)时,认为此次操作不会导致冲突 在操作数据时,并不会进行任何其他的特殊处理(不加锁),而在进行更新时,再去判断是否有冲突 | 不是数据库自带的,需要自己实现 | 耗时较短 |
悲观锁 | 指操作库(更新操作)时,认为此次操作会导致冲突 所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作(类似 java 中的 synchronized) | 数据库自带,调用相关语句即可 | 耗时较长 |
MySQL的排他锁、共享锁
类型 | 原理 | 使用 | 举例 |
---|---|---|---|
排他锁 (Exclusive Lock,也叫X锁) | 表示对数据进行写操作。 如果一个事务对对象加了排他锁,其他事务就不能再给它加任何锁了 | 产生排他锁的sql: select * from ad_plan for update; | 某个顾客把试衣间从里面反锁了,其他顾客想要使用这个试衣间,就只有等待锁从里面给打开了 |
共享锁 (Shared Lock,也叫S锁) | 表示对数据进行读操作。 因此多个事务可以同时为一个对象加共享锁 | 产生共享锁的sql:select * from ad_plan lock in share mode; | 如果试衣间的门还没被锁上,顾客都能够同时进去参观 |
数据库设计的三大范式
- 第一范式:确保每列保持原子性
- 第二范式:确保表中的每列都和主键相关
- 第三范式:确保每列都和主键列直接相关,而不是间接相关
数据库的读写分离、主从赋值
- 可以通过 logbin 文件进行主从复制,修改配置文件
- 通过设置主从数据库实现读写分离,主数据库负责“写操作”,从数据库负责“读操作”
- 根据压力情况,从数据库可以部署多个提高“读”的速度,借此来提高系统总体的性能。
long_query怎么解决
慢查询日志:默认情况下,MySQL数据库是不开启慢查询日志的,long_query_time 的默认值为10(即10秒,通常设置为1秒),即运行10秒以上的语句是慢查询语句。
- log_slow_queries:旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_name-slow.log
- slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件 host_name-slow.log
- log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)
- slow_query_log:慢查询开启状态
- slow_query_log_file:慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)
- long_query_time:查询超过多少秒才记录。
内连接、外连接
- 内连接(Inner Join):只连接匹配的行
- 外连接(Outer Join)
- 左外连接(Left Outer Join或Left Join):包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行。
- 右外连接(Right Outer Join或Right Join): 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行。
- 全外连接(Full Outer Join或Full Join): 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。
MySQL 的三种锁级别
类型 | 开销 | 加索速度 | 是否会出现死锁 | 锁定粒度 | 发生冲突的概率 |
---|---|---|---|---|---|
行级锁 | 开销大 | 加锁慢 | 会出现死锁 | 锁定粒度最小 | 发生所冲突的概率最低,并发度最高 |
表级锁 | 开销小 | 加锁快 | 不出现死锁 | 锁定粒度最大 | 发生所冲突的概率最高,并发度最低 |
页面锁 | 开销在行级锁和表级锁之间 | 加锁速度在行级锁和表级锁之间 | 会出现死锁 | 锁定粒度在行级锁和表级锁之间 | 发生所冲突的概率中等,并发度一般 |
死锁判定原理和具体场景,死锁怎么解决
- 死锁(DeadLock):是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。
此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等的进程称为死锁进程。
表级锁不会产生死锁,所以解决死锁主要还是针对于最常用的InnoDB。 - 死锁的关键:两个(或以上)的Session加锁的顺序不一致。
- 解决死锁问题的关键就是:让不同的session加锁有次序。
- 死锁的解决办法
- 查出的线程杀死 kill
- 设置锁的超时时间
varchar、char的使用场景
- char:长度不可变
- 存储方式:对英文字符(ASCII)占用1个字节、对中文字符占用2个字节
- varchar:长度可变、以空间效率为首
- 存储方式:对英文字符(ASCII)占用2个字节、对中文字符占用2个字节
- 两者的存储数据都非unicode的字符数据。
MySQL 并发情况下怎么解决(通过事务、隔离级别、锁)
MySQL 高并发环境解决方案、分库、分表、分布式、增加二级缓存。
需求分析:互联网单位每天大量数据读取,写入,并发性高。
- 现有解决方式:水平分库分表,由单点分布到多点数据库中,从而降低单点数据库压力。
- 集群方案:解决DB宕机带来的单点DB不能访问问题。
- 读写分离策略:极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力。