Fork me on GitHub

数据库——秋招复习笔记

数据库

[TOC]

一条sql的执行过程

Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和提取。其架构模式是插件式的,支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB,它从MySQL 5.5.5版本开始成为了默认存储引擎。

  1. 客户端发送一条查询给服务器。
  2. 检查查询缓存,之前执行过会以key是查询的语句+value是查询的结果的形式缓存在内存中。如果命中了缓存则直接返回结果。否则进入下一阶段。(不过因为查询缓存的失效非常频繁,8.0删除了查询缓存这个功能。)
  3. 分析器做两件事:一、“词法分析”识别SQL里面的字符串分别代表什么。二、.”语法分析”判断SQL语句是否满足MySQL语法。
  4. 优化器决定使用哪个索引、各个表的连接顺序,然后生成对应的执行计划。
  5. 存储引擎的API来执行查询。(默认引擎是InnoDB)
  6. 结果集返回给客户端。

查询优化器

一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。

在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。

这个成本最低的方案就是所谓的执行计划。优化过程大致如下:

  • 1、根据搜索条件,找出所有可能使用的索引
  • 2、计算全表扫描的代价
  • 3、计算使用不同索引执行查询的代价
  • 4、对比各种执行方案的代价,找出成本最低的那一个

索引失效

可能是因为优化器错误判断,判断是依据采样统计+计算回表的代价。有时候当少量的非聚集索引和大量的聚集索引可能优化器会选错聚集索引,因为它把非聚集索引的回表代价也计算进去,可以强制指定索引或修改语句诱导优化器再或者直接删除误用索引。

连接池

MyISAM和InnoDB区别

MySQL 5.5版本后默认的存储引擎为InnoDB。

不同点

innoDB MyISAM
聚集索引叶子节点保存了完整的数据记录 叶子结点记录的是地址(非聚集索引)
事务 不支持事务
行锁、表锁 只支持表锁
外键 不支持外键
崩溃后的安全恢复 不支持崩溃后的安全恢复
MVCC 不支持MVCC
不支持全文索引(5.6后支持) 支持全文索引

两者的对比:

  1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking),而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
  2. 是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行速度比InnoDB类型更快,但是不提供事务支持。但是InnoDB 提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  3. 是否支持外键: MyISAM不支持,而InnoDB支持。
  4. 是否支持MVCC :仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTEDREPEATABLE READ 两个隔离级别下工作
  5. InnoDB采用聚集的方式,每张表按照主键的顺序进行存放。如果没有主键,InnoDB会为每一行生成一个6字节的ROWID并以此为主键;MyISAM可以不指定主键和索引
  6. InnoDB没有保存表的总行数,因此查询行数时会遍历整表;而MyISAM有一个变量存储可表的总行数,查询时可以直接取出该值《MySQL高性能》上面有一句话这样写到:

不要轻易相信“MyISAM比InnoDB快”之类的经验之谈,这个结论往往不是绝对的。在很多我们已知场景中,InnoDB的速度都可以让MyISAM望尘莫及,尤其是用到了聚簇索引,或者需要访问的数据都可以放入内存的应用。

一般情况下我们选择 InnoDB 都是没有问题的,但是某些情况下你并不在乎可扩展能力和并发能力,也不需要事务支持,也不在乎崩溃后的安全恢复问题的话,选择MyISAM也是一个不错的选择。但是一般情况下,我们都是需要考虑到这些问题的。

数据字段的类型

索引

索引数据结构

常见的MySQL主要有两种结构:Hash索引和B+ Tree索引,我们使用的是InnoDB引擎,默认的是B+树

hash索引和btree索引区别

  1. 范围
  2. 排序
  3. 大量Hash值相等
  4. 联合索引并不能更省时间
  5. 不能避免表扫描

    1).Hash 索引仅仅能满足”=”,”IN”和”<=>”查询,不能使用范围查询。
    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。
    2).Hash 索引无法被用来避免数据的排序操作。
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;
    3).Hash 索引不能利用部分索引键查询。
    对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。
    4).Hash 索引在任何时候都不能避免表扫描。
    前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
    5).Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
    对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下

B+树和B-树的区别?

相关的树

  • 二叉查找/搜索/排序树
  • 完全二叉树
  • 平衡二叉树AVL=二叉查找+完全二叉树

区别:b+树非叶子结点仅仅用于索引,叶子结点存储数据。叶子结点是链接起来的,方便做范围统计。,而B树每一个结点都存储Key和value。B+非叶子节点并不存储行数据,是为了能存储更多索引键,从而降低B+树的高度,进而减少IO次数。

b+

  1. 因为矮胖,磁盘读写代价低,更适合用于数据库和操作系统的文件系统中。

  2. 效率更稳定,所有查询必须从根到叶子

  3. 范围查询

InnoDB的B+ Tree可能存储的是整行数据,也有可能是主键的值

聚集索引与非聚集索引的区别?

  • 在 InnoDB 里,索引B+ Tree的叶子节点存储了整行数据的是主键索引,也被称之为聚簇索引。而索引B+ Tree的叶子节点存储了主键的值的是非主键索引,也被称之为非聚簇索引
  • 聚集索引决定了表的物理排序顺序,一个表只能有一个物理排列顺序,所以一个表一定有并且只能有一个聚集索引
  • 聚集索引更快,因为主键索引树的叶子节点直接就是我们要查询的整行数据了。而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再进行一次查询,所以非主键索引包含两次查找。通过覆盖索引也可以只查询一次。

innoDB每个表都需要有一个聚集索引,它的选择为

  • 如果定义了主键,那么InnoDB会选择其作为聚集索引;
  • 如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引;
  • 如果也没有这样的唯一索引,则InnoDB会选择隐含的聚集索引

索引基础四连问

哪些情况适合建立索引?

  1. 在经常要搜索的列上
  2. 经常出现在where后面的列上
  3. 在作为主键、外键的列上
  4. 经常需要排序、分组和联合操作的字段建立索引

哪些情况不适合建立索引?

  1. like “%xxx”、not in , !=、OR
  2. 对列进行函数运算的情况(如 where md5(password) = “xxxx”)
  3. 查询中很少使用的字段
  4. 数值太少的字段
  5. 唯一性差的字段
  6. 更新频繁的字段
  7. ext和blob等大字段不适合建立索引

索引什么时候失效

  1. 模糊查询中,通配符在最前面时,即LIKE ‘%abc’这样不能命中索引
  2. 使用了not in, <>,!=则不会命中索引。注:<>是不等号
  3. innoDB引擎下,若使用OR,只有前后两个列都有索引才能命中(执行查询计划,type是index_merge),否则不会使用索引。
  4. ·对列进行函数运算的情况(如 where md5(password) = “xxxx”)
  5. 联合索引中,遇到范围查询时,其后的索引不会被命中。或者没有遵循最左匹配原则。
  6. 存了数字的char或varchar类型,常见的如用字符串表示的手机号,在查询时不加引号,则不会命中(如where phone=‘13340456789’能命中,where phone=13340456789不能命中)
  7. 当数据量小时,MySQL发现全表扫描反而比使用索引查询更快时不会使用索引。
  8. 优化器错误的选择了索引

联合索引

为什么要使用联合索引?

MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。

推荐阅读这篇博客

减少开销。建了一个(a,b,c)的联合索引,相当于建了(a),(a,b),(a,b,c)三个索引

覆盖索引。减少了随机IO操作。同样的有复合索引(a,b,c),如果有如下的sql: select a,b,c from table where a=1 and b = 1。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作

效率高。索引列越多,通过索引筛选出的数据越少。比如有1000W条数据的表,有如下sql:select from table where a = 1 and b =2 and c = 3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w 条数据,然后再回表从100w条数据中找到符合b=2 and c= 3的数据,然后再排序,再分页;如果是复合索引,通过索引筛选出1000w 10% 10% *10%=1w,然后再排序、分页。

最左匹配原则

建了一个(a,b,c)的联合索引,那么实际等于建了(a),(a,b),(a,b,c)三个索引,但是有时在条件查询时只会匹配到a或者(a, b)而不会匹配到(a, b, c)。下面的例子

1
SELECT * FROM table WHERE a = 1 AND c = 3;

使用了索引a,c不走索引

1
SELECT * FROM table WHERE a = 1 AND b < 2 AND c = 3;

使用到了索引(a,b),c不走索引

建立联合索引(a, b ,c),所以索引是按照a -> b -> c的顺序进行排序的。a-b-c这样的索引是先找a,然后在范围里面找b,再在范围内找c。 所以上面的语句里的c 会分散在很多个b里面且不是排序的,所以没办法走索引。

举个例子比如(a, b)联合索引,先按a排序再按b排序,得到

(1,1)->(1, 2)->(2, 1) (2, 4)->(3, 1)->(3, 2)

如果执行select a from table where b=2,就没有使用到(a, b)这个联合索引,因为b的值1,2,1,4,1,2显然不是排序的。

具体来说:MySQL会从左开始一直向右匹配直到遇到范围查询(>,<,BETWEEN,LIKE)就停止匹配。

比如: a = 1 AND b = 2 AND c > 3 AND d = 4,如果建立 (a,b,c,d)顺序的索引,使用了索引(a, b, c),但是d是没有走索引的

如果建立(a,b,d,c)的索引,则可以命中索引(a, b, c, d),其中a,b,d的顺序可以任意调整。

等于(=)和in 可以乱序。比如,索引是KEY idx_id_name(seckill_id,name)

explain select * from seckill where name=’1000元秒杀iphone6’ and seckill_id=1000会走索引。

如何建立复合索引,可以使sql语句能尽可能匹配到索引?

  • 等于条件的索引放在前面(最左),范围查询放在后面。 a = 1 AND b = 2 AND c > 3 AND d = 4,建立(a, b, d, c)就是不错的选择;
  • 先过滤后排序(ORDER BY)如SELECT * FROM t WHERE c = 100 and d = ‘xyz’ ORDER BY b建立(c, d, b)联合索引就是不错的选择
  • 对于索引列的查询,一般不建议使用LIKE操作,像LIKE ‘%abc’这样的不能命中索引;不过LIKE ‘abc%’可以命中索引。

https://www.jb51.net/article/81875.htm)

其他

主键和唯一索引的区别?

  • 主键一定是唯一索引,唯一索引不一定是主键。
  • 主键是一种约束,唯一索引是索引,一种数据结构。
  • 一个表中可以有多个唯一索引,但只能有一个主键。
  • 主键不允许空值,唯一索引允许。
  • 主键可以做为外键,唯一索引不行;

COUNT(列名)和COUNT(*)区别

COUNT()和COUNT(1)没区别。COUNT(列名)和COUNT()区别在于前者不会统计列为NULL的数据,后者会统计。

事务

事务隔离级别?

脏读 不可重复读 幻读
读未提交 Y Y Y
读已提交 N Y Y
可重复读 N N Y
串行化 N N N

事务acid性

  1. l 原子性(Atomicity):要么全不做要么全做。
  2. l 一致性(Consistency)::从一个状态到另一个状态。比如A向B转了钱,转账前后钱的总数不变。
  3. l 隔离性(Isolation):并发事务直接不会彼此干扰,多个并发事务之间的数据相互隔离。比如事务A和事务B都修改同一条记录,这条记录就会被重复修改或者后者会覆盖前者的修改记录。
  4. 持久性
  1. l 脏读:读到另一个事务回滚的数据
  2. l 不可重复读 在一个事务第一次搜索和第二次搜索结果不一样。
  3. l 幻读 搜索一共4条记录,修改所有记录的余额时,另一个事务插入一条记录,结果变成修改5条记录.幻读只是重点强调了读取到了之前读取没有获取到的记录。

不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

  1. l 未提交读-避免更新丢失
  2. l 已提交读-避免脏读
  3. l 可重复读-避免不可重复读默认
  4. l 串行化-避免幻读

一般来说,数据库隔离级别不一样,可能出现的并发问题也不同。级别最高的是串行化,所有问题都不会出现。但是在并发下性能极低,可重复读会只会导致幻读。

所以一般使用MySQL默认的可重复读即可。MVCC(多版本并发控制)使用undo_log使得事务可以读取到数据的快照(某个历史版本),从而实现了可重复读。MySQL采用Next-Key Lock算法,对于索引的扫描不仅是锁住扫描到的索引,还锁住了这些索引覆盖的范围,避免了不可重复读和幻读的产生。

可重复读是如何实现的?MVCC(多版本并发控制)?

MVCC(多版本并发控制)multiversion concurrency control的简称,也就是多版本并发控制。

版本链

对该记录每次更新后,都会将旧值放到一条undo日志中,就算是该记录的一个旧版本,旧版本会被roll_pointer(回滚指针)属性连接成一个链表,我们把这个链表称之为版本链,版本链的头节点就是当前记录最新的值。

如果某个版本的数据对当前事务不可见的话,那就顺着版本链往下找,直到如果最后一个版本也不可见的话,那么就意味着该条记录对该事务完全不可见,查询结果就不包含该记录。

备注:为了支持MVCC,对于delete mark操作来说,仅仅是在记录上打一个删除标记,并没有真正将它删除掉。

read view

InnoDB MVCC使用的内部快照的意思。在不同的隔离级别下,事务启动时(有些情况下,可能是SQL语句开始时)看到的数据快照版本可能也不同

  • READ UNCOMMITTED由于可以读到未提交事务修改过的记录,所以直接读取记录的最新版本就好了;
  • READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView
  • REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView,之后的查询操作都重复使用这个ReadView就好了。这就叫快照读。其余update、insert、delete操作是是当前读,即先当前读,然后把返回的数据加排他锁,之后执行update。所以,mvvc不能根本上解决幻读的情况
  • SERIALIZABLE使用加锁的方式来访问记录

READ COMMITTDREPEATABLE READ`区别:生成ReadView的时机不同,可重复读只需要读到以提交的数据,而可重复读要求第一次读和之后的读都是一样。

mysql中怎么解决幻读问题?

多版本并发控制 MVCC(读)和 临键锁 Next-Key Lock(写)共同解决了幻读问题。

临键锁(Next-Key Lock)。是记录锁(Record Lock)和间隙锁(Gap Lock)的组合,既封锁了”缝隙”,又封锁了索引本身。

死锁

死锁是指两个或两个以上的事务在执行过程中,因争夺锁资源而造成的一种互相等待的现象,若无外力作用两个事务都无法推进,这样就产生了死锁。

四个必要条件

四个条件缩写”一保夺环“

  1. 互斥条件:即任何时刻,一个资源只能被一个进程使用。其他进程必须等待。
  2. 请求和保持条件:即当资源请求者在请求其他的资源的同时保持对原有资源的占有且不释放。
  3. 不剥夺条件:资源请求者不能强制从资源占有者手中夺取资源,资源只能由资源占有者主动释放。
  4. 环路等待条件:比如A占有B在等待的资源(B等待A释放),B占有A在等待的资源(A等待B释放)。多个进程循环等待着相邻进程占用着的资源。

避免死锁可以通过破环四个必要条件之一。

解决死锁的方法:

加锁顺序保持一致。不同的加锁顺序很可能导致死锁,比如哲学家问题:A先申请筷子1在申请筷子2,而B先申请筷子2在申请筷子1,最后谁也得不到一双筷子(同时拥有筷子1和筷子2)

超时,为其中一个事务设置等待时间,若超过这个阈值事务就回滚,另一个等待的事务就能得以继续执行。比如可重入锁的超时等待

等待图(wait-for gragh)进行深度优先搜索,如果图中有环路就说明存在死锁。数据库检测出死锁,则选择一个牺牲者放弃事务,一般选择回滚undo量最小的事务。

数据库中锁?

数据库

MySQL锁总结

分库分表

经验之谈:单表数据量200万、单库并发每秒 1000 (最多2000就一定要分库分表了)

原因

并发、磁盘、SQL

分库分表前 分库分表后
并发支撑情况 MySQL 单机部署,扛不住高并发 MySQL从单机到多机,能承受的并发增加了多倍
磁盘使用情况 MySQL 单机磁盘容量几乎撑满 拆分为多个库,数据库服务器磁盘使用率大大降低
SQL 执行性能 单表数据量太大,SQL 越跑越慢 单表数据量减少,SQL 执行效率明显提升

水平切分

水平切分又称为 Sharding,它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时,Sharding 是必然的选择,它可以将数据分布到集群的不同节点上,从而缓存单个数据库的压力。

策略

  • 哈希取模:hash(key) % N;较常用,平均分配每个库的数据量和请求压力,但扩容需要数据迁移,之前的数据需要重新计算 hash 值重新分配到不同的库或表。
  • 范围:可以是 ID 范围也可以是时间范围;比较简单,给每个月都准备一个库。但容易产生热点问题,大量的流量都打在最新的数据上。
  • 映射表:使用单独的一个数据库来存储映射关系。

问题?

1. 事务问题

使用分布式事务来解决,比如 XA 接口。

2. 连接

可以将原来的连接分解成多个单表查询,然后在用户程序中进行连接。

3. ID 唯一性

  • 使用全局唯一 ID(GUID)
  • 为每个分片指定一个 ID 范围
  • 分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

垂直切分

垂直切分是将一张表按列切分成多个表,通常是按照列的关系密集程度进行切分,也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

因为数据库有查询缓存的,字段越少,缓存越多。

一个大表拆开,订单表、订单支付表、订单商品表。

常用的分库分表中间件

中间件可以根据你指定的某个字段值,比如说 userid,**自动路由到对应的库上去,然后再自动路由到对应的表里去。

Sharding-jdbc

当当开源的,属于 client 层方案,支持分库分表、读写分离、分布式 id 生成、柔性事务(最大努力送达型事务、TCC 事务)。

这种 client 层方案的优点在于不用部署,运维成本低,不需要代理层的二次转发请求,性能很高,但是如果遇到升级啥的需要各个系统都重新升级版本再发布,各个系统都需要耦合 Sharding-jdbc 的依赖;

Mycat

基于 Cobar 改造的,属于 proxy 层方案,支功能完善,非常火。但比于 Sharding jdbc 来说,年轻一些,。

Mycat 这种 proxy 层方案的缺点在于需要部署,自己运维一套中间件,运维成本高,但是好处在于对于各个项目是透明的,如果遇到升级之类的都是自己中间件那里搞就行了。

两者的主要区别:独立部署、二次转发、耦合度。

乐观锁

1
2
3
4
5
6
ProductStock product = query("SELECT * FROM tb_product_stock WHERE product_id=#{productId}", productId);
if (product.getNumber() > 0) {
updateCnt = update("UPDATE tb_product_stock SET number=number-1 WHERE product_id=#{productId} AND number=#{number}", productId, product.getNumber());
if(updateCnt > 0){ //更新库存成功
return true;
}

使用乐观锁更新库存的时候不加锁,当提交更新时需要判断数据是否已经被修改(AND number=#{number}),只有在 number等于上一次查询到的number时 才提交更新。

SQL语句

内连接为空的值是不会显示出来的 ,但是外连接会
group by会按这个值分组,自然这个值就没有重复了。比如班级名

select * from produce p join categoryc

select * from produce p join categoryc in p.produceId=c.categoryId

select * from produce p left join categoryc in p.produceId=c.categoryId

其他

慢sql

分析:开慢日志定位

使用explain看是不是全表扫描没走索引,extra字段如果是使用“文件排序”或“临时表”

尽量让它走索引,可以的话就改搜索字段,不能的话只能加索引了。如果是联合索引,要遵循最左匹配原则。

3)如何实现HashMap顺序存储:可以参考LinkedHashMap的底层实现;

如何防止SQL注入?

使用PrepareStatement,可以防止sql注入攻击,sql的执行需要编译,注入问题之所以出现,是因为用户填写 sql语句参与了编译。使用PrepareStatement对象在执行sql语句时,会分为两步,第一步将sql语句 “运送” 到mysql上预编译,再回到java端拿到参数运送到mysql端。预先编译好,也就是**SQL**引擎会预先进行语法分析,产生语法树,生成执行计划,也就是说,后面你输入的参数,无论你输入的是什么,都不会影响该语法结构了**。用户填写的sql语句,就不会参与编译,只会当做参数来看。

数据库设计的三大范式?

1NF 不可再分,一个字段里只放一条信息

2NF, 主键之一就可以完全决定一个属性(表:学号、课程号、姓名、学分;课程号就完全可以决定学分,而学分会相同重复。插入删除异常,有学生才有学分信息,这显然不合理)

3NF不存在传递依赖,主属性不直接决定非主属性(表: 学号, 姓名, 年龄, 学院名称, 学院电话。学号决定学院,学院直接决定学院电话)

MySQL索引背后的数据结构及算法原理