MySQL数据库
MySQL常用数据类型
- char: 存放定长文本,如 身份证号
- varchar: 存放小型变长文本,如 家庭住址
- text: 存放大型变长文本,如 新闻内容
- tinyint: 存放较小的整数,bool值
- int: 存放较大的整数,如记录项的id
- datetime: 存放日期时间,如用户注册时间
- enum: 存放枚举值,如男,女
char和varchar的区别?
char是定长的,不足的位数会用空格补全
varchar是变长的,存取速度比char慢一些
varchar和text的区别?
text只能建立前缀索引,text不能有默认值,指定text的长度是没有作用的
一条SQL语句是如何执行的?
SQL查询语句的执行过程:
- 首先通过 连接器 管理客户端连接,验证客户端权限
- 之后 分析器 进行语法分析,判断查询的SQL字段是否存在
- 然后 优化器 计算 不同索引方案的成本,选择成本最低的方案作为执行计划
- 最后执行器操作存储引擎提供的读写接口,返回结果给客户端
如果是SQL更新语句,还要再把修改记录到 redo log 和 binlog
什么是MVCC?
MVCC,多版本并发控制,只适用于 读提交 和 可重复读 两种隔离级别,
每一条记录都有自己的版本链,不同的事务 可以并发访问 相同记录
采用MVCC,读的时候不会加锁,所以读写不冲突,极大地提升了系统的并发性能
什么是ReadView?
一致性视图,它可以用来读取 记录的MVCC版本链 中的历史版本
在 读提交 下, 每一次select都会生成一个ReadView, 可以读到已提交事务修改的数据
在 可重复读 下, 只在第一次select时生成一个ReadView, 所以能保证重复读
快照读和当前读
- 快照读: 读到的是记录的历史版本,不用加锁
- 普通的select语句就是快照读
- 当前读: 读到的是记录的最新版本,会加锁
- select … lock in share mode (加S锁)
- select … for update (加X锁)
- insert,update,delete (加X锁)
什么是事务?
事务是一种机制,它要保证一组数据库操作要么全部成功,要么全部失败
事务的四大特性(ACID)
-
原子性(Atomicity): 事务是一个不可分割的整体,所包含的操作要么全做,要么一个也不做
-
一致性(Consistency): 事务 开始前 和 结束后,数据的总量是不变的
-
隔离性(Isolation): 多个事务并发执行时,彼此之间互不干扰
-
持久性(Durability): 事务一旦提交,所有的修改都会在磁盘中保存下来.
四大特性中,有了原子性,隔离性 才能 保证一致性
MySQL怎么保证原子性?
利用Innodb引擎的undo log(回滚日志),它记录了回滚一个操作必需的内容
比如,当你update一条数据的时候,就需要这条记录的原始值,回滚的时候,把这条记录再update为原始值
MySQL怎么保证持久性?
保证持久性,其实很简单,只用把该事务在内存中修改的全部页面刷新到磁盘就可以了,但是,这些页面可能并不相邻,需要进行很多随机IO,对于传统的机械硬盘就会特别慢,所以,为了提升效率,设计MySQL的大叔就引入了redo log,它只用来保存事务对数据页所做的修改,redo log文件是追加写,顺序IO速度很快. 在系统空闲时, MySQL会自动根据redo log的内容更新磁盘上的数据页
MySQL怎么保证隔离性?
MySQL提供了四种隔离级别,隔离级别越高的,并发度越低,
这四种隔离级别在修改时都会对记录行加X锁,不会发生脏写现象,都保证了最基本的隔离性
-
读未提交 会读取到未提交事务修改的最新记录,隔离性最差,并发性最好
-
读提交 和 可重复读 读取记录时会生成ReadView,在记录的MVCC版本链中找到未被 其它事务修改的原始记录,隔离性和并发性都不错
-
串行化,每次select读取记录都会上S锁,会读取最新的记录,隔离性最好
我们可以根据需要选择不同的隔离级别
事务的并发可能导致哪些问题?
-
脏写: 一个事务 修改了 另一个未提交事务 修改的数据
-
脏读: 一个事务 读到了 另一个未提交事务 修改的数据
-
不可重复读: 一个事务多次用相同语句读取记录时, 后读取的值 与 之前的值 不一样
-
幻读: 一个事务多次用相同语句读取记录时, 后读取时读到了之前没有读到的记录
四种事务隔离级别
- 读未提交 (read uncommitted): 隔离级别最低,一个事务还未提交时,它做的变更就能被别的事务看到.
- 读提交 (read committed): 一个事务提交后,它做的变更才能被别的事务看到. 解决了脏读
- 怎么解决脏读的呢? 每次读都会生成一个ReadView,会顺着记录的MVCC版本链去读第一个已提交事务的历史版本
- 可重复读 (repeatable read): 一个事务执行过程中读取的数据,总是和这个事务第一次读取的数据相同. 解决了不可重复读
- 怎么解决不可重复读的呢? 只有第一次读才会生成一个ReadView,这样就算后续有事务提交也不会影响查询结果
- 如果第二次读之前执行过一条范围写命令,而这个范围刚好包含了另一事务插入的新记录,幻读仍然可能发生,因为写命令是当前读,可以在当前读的范围内加S锁来避免插入数据,从而避免幻读
- 串行化 (serializable): 隔离级别最高,使用普通的select语句会对整个搜索范围加S锁,这样就阻止了其它事务对此范围进行写操作,解决了幻读
无论是哪种隔离级别,写同一行记录时都会加X锁,所以都能解决脏写问题,
设置四种事务隔离级别是“舍弃 一部分隔离性 换取 更好的并发性能”,所以严格地说,只有串行化才满足绝对的隔离性
InnoDB和MyISAM的区别
-
InnoDB支持事务,外键,行锁和表锁
-
MyISAM不支持事务,外键和行锁,只支持表锁,不适用于并发量大的业务,但是它查询速度比InnoDB快
哪些数据结构可以用于查询?
- 哈希表: 可以精确查询,但不支持范围查询
- 二叉查找树(BST): 存在时间复杂度退化为O(n)的情况
- 平衡二叉查找树(AVL): 解决了BST时间复杂度退化问题,但由于树高,查询的IO次数多,也不适合存大规模数据
- 多路平衡查找树(B树): 一个结点可存储多个元素,减少了IO次数,但范围查询需要中序遍历,效率低
- B树的变体(B+树): 非叶子结点不存储数据,因此能存放更多的目录项记录,需要的IO次数更少,且支持范围查询
什么是索引?
索引是一种能在数据表中完成高效搜索的数据结构,一般基于B+树实现,
索引的优点是可以加快查询速度,缺点是更新数据时效率低,因为要同时更新索引
MySQL的索引类型
-
聚簇索引: 以主键值的大小作为记录的排序规则,在叶子结点中存储的记录包含表中所有的列
-
二级索引: 以索引列的大小作为记录的排序规则,在叶子结点中存储的记录是 索引列+主键列
聚簇索引查询的过程
每个索引都对应一棵B+树,所有的用户数据都存在B+树的叶子结点,通过索引查找记录时,从B+树的根结点开始一层层向下搜索,直到找到该记录所在的数据页,
然后在页目录中通过二分法查找中间槽对应的主键值,快速定位到记录所在的组,再在组内依次遍历直到找到主键值等于搜索值的记录
为什么索引要用B+树?
- B+树支持范围查询,因为它用双向链表连接所有叶结点,顺序遍历即可
- B+树IO次数更少,因为它的非叶结点不存放数据,所以能存放更多目录项记录,结构更加矮胖,可以很快定位到数据页
- B+树查询效率稳定,因为它所有数据都在叶结点,每个数据的查询效率基本相同
索引越多越好吗?
不,索引要尽可能少
比如我们向表中插入一个记录,实际上是先将记录插入到聚簇索引对应的B+树,再插入到每个二级索引对应的B+树,
索引越多的话B+树越多,插入时要进行的IO操作就越多,会严重影响性能
另一方面,MySQL优化器会耗费更多的时间计算不同索引方案成本
什么样的字段适合创建索引?
-
经常需要用作条件查询,分组,排序的字段
-
经常用作表连接的字段
-
非空,没有大量的重复值 (否则可能要执行多次回表操作)
索引什么情况下会失效?
-
以%开头的like语句
-
查询语句的条件类型与数据表字段的类型不匹配
-
使用联合索引时,不满足最左匹配原则
MySQL慢查询怎么解决?
慢查询通常是缺少索引,或者 索引不合理 导致的
在mysql的配置文件中,开启慢查询日志,查找一下是哪些语句执行的这么慢,
适当加一些必要的,合理的索引, 删除冗余的索引
为什么主键要使用自增的整数?
因为MySQL会自动为主键添加索引,而索引要实现高效检索和范围查询,需要保证索引的有序性
如果不使用自增的整数,每次插入数据还要把它放到合适的位置上,还有可能造成页分裂,
而主键采用自增的整数时,插入记录时往后追加即可,效率更高
什么是内连接,外连接?
内连接: 只会把驱动表和被驱动表中都有的记录添加到结果集
外连接: 在被驱动表中没有找到驱动表中的记录,也仍然以NULL值添加到结果集
驱动表 和 被驱动表
当连接查询没有where条件时,左连接查询时,前面的表是驱动表,后面的表是被驱动表,右连接查询时相反,内连接查询时,哪张表的数据较少,哪张表就是驱动表
当连接查询有where条件时,带where条件的表是驱动表,否则是被驱动表
on 和 where的区别?
它们都是用于添加过滤条件
on子句一般只用于内外连接的条件,其它情况下都应该使用where
having 和 where的区别?
- where语句指定行的条件,having语句指定组的条件
- where语句在GROUP BY语句之前;having语句在GROUP BY语句之后
- where语句不能使用聚合函数,而having可以
表级锁 行级锁
从 锁的粒度 来分,主要有 表级锁 和 行级锁
-
表级锁: 一般是存储引擎不支持行锁时才使用
-
行级锁: 当SQL语句对记录进行读写操作时,可以选择对记录加行锁
- 正经记录锁: 最常用的行级锁,仅仅把一条记录锁上
- gap锁: 会锁住 该记录与上一记录的间隙
- next-key锁: 会锁住 该记录 和 该记录与上一记录的间隙
- 加S型next-key锁: select * from table where id>1 and id<=6 lock in share mode;
- 加X型next-key锁: select * from table where id>1 and id<=6 lock for update;
- 插入意向锁: 表明有事务想在某个间隙插入新记录,但现在处于等待状态
共享锁 独占锁 意向锁
从 锁的模式 来分,主要有 共享锁,独占锁,意向锁
- 共享锁 (Share): 简称S锁,行级或表级,其它事务只能读,不能写
- 哪些语句会自动加S锁: 无
- 怎么手动加S锁:
- 对读取的记录行加S锁: select … lock in share mode;
- 对整张表加S锁: lock tables t read;
- 独占锁 (Exclusive): 简称X锁,行级或表级,其它事务既不能读,也不能写
- 哪些语句会自动加X锁: update,delete,insert
- 怎么手动加X锁:
- 对读取的记录行加X锁: select … for update;
- 对整张表加X锁: lock tables t write;
- 共享意向锁: 简称IS锁,是表级锁,仅用来判断表中是否有记录上了S锁
- 独占意向锁: 简称IX锁,是表级锁,仅用来判断表中是否有记录上了X锁
- 自增锁: 用于给auto_increment字段递增赋值,只作用于单个插入语句,插入完成后立刻释放
注: 普通的select语句不加任何锁,根据 隔离级别的不同,会读取记录的最新版本 或 MVCC版本链记录
悲观锁 乐观锁
从 锁的风格 来分,分为 悲观锁 和 乐观锁
- 悲观锁: 读取记录的时候就加锁,直到事务结束才释放锁
# 原生SQL
begin;
select stock from tb_sku where id=1 for update;
update tb_sku set stock=2 where id=1;
commit;
# ORM
sku = SKU.objects.select_for_update().get(id=1)
sku.stock=2
sku.save()
- 乐观锁: 读取记录的时候不加锁,只有更新记录的时候才加锁,而且更新的时候要判断在此期间记录是否被修改
# 原生SQL
select stock from tb_sku where id=1; # 假设这里查到库存为7
# 判断下单数量 是否大于 库存
update tb_sku set stock=2 where id=1 and stock=7;
# ORM
SKU.objects.filter(id=1,stock=7).update(stock=2)
长事务的危害
- 锁住的记录数量过多,容易造成大量的死锁
- 执行时间长,容易造成主从延迟,因为主库必须等事务执行完才写入binlog
- 事务回滚时间长
如何解决?
- 不要使用长事务,使用limit分为多个短事务
- 把最可能影响并发度的语句往后放
死锁和死锁检测
死锁出现的原因: 事务A和事务B,都要同时更新某数据表的记录1和记录2,更新时都会上行锁,若事务A先对记录1上锁,事务B先对记录2上锁,这样的话,事务A就要等待事务B提交才能对记录2进行操作,而事务B也要等待事务A提交才能对记录1进行操作,最终的结果就是事务A和B互相等待对方提交,最终一个都没能提交
Innodb默认开启了 主动死锁检测,会主动回滚死锁链中的某一个事务,让其它事务得以执行
MySQL优化手段
-
建立覆盖索引,减少回表操作
-
避免使用select *,只写需要的字段
-
使用limit分页,限制单次查询的数量
-
进行分表操作,一个表中字段过多时,可以把不常用的字段拆分到多个子表中
-
进行分库操作,主库用于写数据,其它从库用于读数据,分摊读写压力
什么是回表操作? 有什么影响?
回表是指找到二级索引记录后,根据该记录的主键值到聚簇索引对应的B+树中找到该记录对应的所有列的过程
每一个二级索引记录都要执行回表操作才能找到完整的记录,每次回表进行的都是随机IO,当记录较多时回表操作开销很大,有时效率甚至不如全表扫描
索引条件下推
把查询中与索引有关的搜索条件下推到存储引擎中判断,而不是在server层中判断,
它只对二级索引有效,这样做可以减少回表次数,从而减少IO次数
redo log 和 bin log的区别
- redo log 是Indodb引擎特有的; binlog所有引擎都能用
- redo log记录的是"在某个数据页上做了什么修改"; binlog记录的是原始的SQL语句
- redo log是循环写的,后面的内容会覆盖以前的内容, 它用于保证事务的持久性; bin log是追加写入,写到一定大小会切换下一个,所以它可以用于存档和主从同步
普通索引 和 唯一索引 的区别
-
唯一索引可以保证插入的数据不重复
-
进行范围查询时, 在找到第一个满足条件的记录后,
- 唯一索引会直接返回;
- 普通索引会顺序查找,直到碰到第一个不满足条件的为止
-
如果要更新的记录所在的数据页不在内存中,
- 唯一索引要先把数据页加载到内存
- 普通索引会先把更新操作记录到change buffer里,等下次访问数据页再更新到数据页
drop,truncate,delete的区别
- drop 删除数据表
- truncate 删除数据表中的所有记录,再插入时自增长id又从1开始
- delete 删除数据表中的部分记录
数据库设计三范式
- 1NF: 列是原子的,不可被分解为多个列
- 2NF: 非主键列必须完全依赖于主键列
- 3NF: 非主键列不能存在传递依赖