MySQL数据库基础总结

SQL

1.分页

limit 子句

# 在所有的查询结果中，返回前5行记录。
SELECT prod_name FROM products LIMIT 5;
# 在所有的查询结果中，从第5行开始，返回5行记录。
SELECT prod_name FROM products LIMIT 5,5;

优化limit 分页：
在偏移量很大时， 例如 LIMIT 90000,20，这样的查询。MySQL需要查询90020条记录，然后只返回最后的20条，前面的90000条记录被抛弃。

SELECT * FROM pms_spu_info ORDER BY id DESC LIMIT 800000,10;  # 0.766s

# 利用索引覆盖， 查询800001条索引树的id 然后 利用索引从第800001位置开始查， 查10条数据就欧克
SELECT * FROM pms_spu_info  
WHERE id > (SELECT id FROM pms_spu_info  LIMIT 800000, 1)
ORDER BY ID DESC
LIMIT 10;  # 0.234

# 使用BETWEEN  AND 更快
SELECT * FROM pms_spu_info  
WHERE id BETWEEN 1000021 AND 1000030 #0.031

一个注意点就是 LIMIT 的offset不要太大

2.函数

常用的聚合函数有COUNT()、AVG()、SUM()、MAX()、MIN()
LEFT RIGHT INNER 表关联： LEFT JOIN ON   
DISTINCT 去重
GROUP BY 分组， 多行变多列用GROUP BY
UNION 拼接， 多列变多行用 UNION    UNION效率高于 UNION ALL， UNION ALL不会合并重复的记录行

3.WHERE和HAVING有什么区别？

WHERE是一个约束声明，使用WHERE约束来自数据库的数据，WHERE是在结果返回之前起作用的，WHERE中不能使用聚合函数。
HAVING是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，在HAVING中只能使用分组字段和聚合函数
从性能的角度来说，HAVING子句中如果使用了分组字段作为过滤条件，应该替换成WHERE子句。因为WHERE可以在执行分组操作和计算聚合函数之前过滤掉不需要的数据，性能会更好。

4.SQL注入的理解

SQL注入的原理是将SQL代码伪装到输入参数中，传递到服务器解析并执行的一种攻击手法。也就是说，在一些对SERVER端发起的请求参数中植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应参数，同时也将一些SQL注入攻击的“SQL”拼接起来，导致会执行一些预期之外的操作。
举例：
有一个登录功能，客户传来用户名和密码 ls 和 123456，我们可能执行这个SQL语句：

SELECT * FROM user WHERE username = 'ls' AND password = '123456

SQL中会将#及–以后的字符串当做注释处理，如果我们使用 ’ or 1=1 # 作为用户名参数，那么服务端构建的SQL语句就如下：

SELECT * FROM user WHERE username = ' ' or 1=1 # AND password = '123456

而#会忽略后面的语句，而1=1属于常等型条件，因此这个SQL将查询出所有的登录用户
如果输入 ’ or 1=1;delete * from users; #， 就会删除全表，很危险

如何解决SQL注入

1. 严格的参数校验，不该有的特殊字符进行校验
2. SQL预编译，变量采用占位符进行占位，当将绑定的参数传到MySQL服务器，MySQL服务器对参数进行编译，即填充到相应的占位符的过程中，做了转义操作。

索引

1. 说说对索引的理解

索引是数据库表中对一列或者多列的数据进行排序的一种数据结构，单独存储再磁盘上，包含这对数据库表里记录的指针。可以加快数据检索。
索引是在存储引擎中实现的，底层数据结构有B+树和Hash表， MyISAM和InnoDB存储引擎索引为B+树， Memory是Hash表索引。

加入索引有很多好处，比如：

1. 通过创建唯一索引，可以保证数据库每一行数据的唯一性
2. 可以大大加快查询速度， 加速表和表之间的连接
3. 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间

加入索引也有坏处：索引也需要占据磁盘空间，创建和维护索引都要耗费时间，并且数据量越大耗费时间也越多

2. 索引分类

主键索引、唯一索引、普通索引、组合索引、全文索引

3.怎么判断要不要加索引，加了索引一定会用吗？

要不要加索引：

1. 如果唯一性是某个数据本身的特征的时候，就指定唯一索引， 唯一索引确保数据唯一性和完整性，不会有空值，可以提高查询速度

2. 频繁进行查询、排序或者分组的列上建立索引，如果待排序的列有多个，可以建组合索引

3. 定义有外键的数据一定要加索引

加了索引一定会用吗？索引失效
不一定，

1. 使用组合索引的时候，没有遵守最左前缀原则
2. 在索引列上做计算、函数、类型转换
3. MySQL使用 不等于（!= 或 <>） 的时候 可能不走索引
4. Like 通配符开头： %abc
5. 字符串不加单引号 （发生了索引列隐式的转换）
6. 如果条件中 有 or， 只要一个字段没索引，其他字段有索引也不会用
   如果一个字段没有索引，那么就会造成 全表扫描 + 索引扫描 + 结果聚合；因此效率不如直接全表扫描；dbms 会直接放弃索引；
7. 数据量少全表扫描比索引快，就不会走索引

4. 那些字段不适合创建索引？

1. 频繁更新的字段
2. Where 条件中用不到的字段
3. 数据比较少的表
4. 不能有效区分数据的列不适合做索引，例如性别
5. 参与计算的列不适合做索引

5. 索引的实现原理

MyISAM引擎使用B+树作为索引结构，叶子节点的data域存放的是数据记录的地址， 主索引和辅助索引结构上没区别。

InnoDB引擎 也是使用B+树作为索引结构，不过InnoDB数据文本本身就是索引文件，表数据本身就是按B+树组织的一个索引结构，叶子节点保存了完整的数据记录，这个索引的key就是主键。换句话说InnoDB的主键索引是聚簇索引，其他索引也在节点存的是主键的值。 所以用辅助索引如果没有达到索引覆盖的条件，就得回表查询。InnoBD要求表必须有主键，如果没有显式的指定，MySQL会系统自动选择一个可以唯一标识数据的列作为主键，如果不存在这样的列就为表自动生成一个隐含字段作为主键，这个字段长为6个字节，类型为长整型（BIGINT）

6. MySQL为什么用B+树

B+树是由B树和索引顺序访问方法演化来的， 很适合用来做存储。
B树是一种自平衡树，维护有序数据并允许以对数时间进行增删改查。 可以有多个子节点，这样树高较低，减少磁盘IO次数。
B树内部节点和叶子节点同时存放键和值。把频繁访问的数据放在靠近根节点的地方可以大大提高数据的查询效率。

B+树内部节点都是键，不放值。这样一次读取可以在内存中读取更多的键，可以使得B+树更加低。
B+树的叶子节点存放键和值， 查询效率稳定，所有关键字查询都必须走一条根节点到叶子节点的路，每个关键字路径长度相同。
B+树的叶子节点用一条链相连。这样如果进行范围遍历或者全数据遍历的时候，只需找到最小的节点然后通过链顺序遍历即可。

7.联合索引存储结构是什么？

联合索引存储结构也是B+数据，每个节点存的键值数量大于等于2个。 查询中只有使用了这些字段的最左边的字段时，索引才能被使用。也就是得遵循最左匹配原则，

事务

1. 说一说对数据库事务的了解？

事务可以由一条或者一组SQL语句组成， 一个事务中的操作，要么都执行，要么都不执行。事务是数据库并发控制的基本单位，执行结果不必须是从一种一致性状态到另一种一致性状态。
事务需要遵循ACID特性。 原子性、一致性、隔离性、持久性

2. ACID特性怎么实现的？

原子性实现原理： undo log
原子性是要么都成功，要么都不成功。实现原子性关键在于能够撤销已经执行的 SQL 语句。 InnoDB是靠 undo log来实现回滚的、当数据库进行修改的时候，InnoDB会生成对应的undo log， insert操作对应生成一个delete的undo log。update 对应一个相反的 update 的 undo log
如果事务执行失败或者调用rollback，导致事务需要回滚，就利用undo log 中的信息将数据回滚到修改前的样子。

持久性实现原理 ：redo log，Buffer Pool、double write buffer
如果每次写数据都要进行磁盘IO，效率很低，所以InnoDB提供了缓存Buffer Pool、Buffer Pool中有磁盘部分数据页的映射，作为访问数据库的缓冲，如果数据库读取数据时，会首先从Buffer Pool中读，如果没有就从磁盘中读取后放入Buffer Pool。写数据的时候会首先写入Buffer Pool， 然后Buffer Pool会定期刷新到磁盘中（刷脏页）
Buffer Pool大大提高了读写数据的效率，但是如果MySQL突然宕机，Buffer Pool中的数据还没来得及刷新到磁盘，导致数据丢失，于是引入redo log。 数据修改时除了修改Buffer Pool中的数据还会在 redo log记录此次操作。事务提交时，刷盘，持久化redo log。 如果MySQL发生宕机，重启时读取redo log 中数据，对数据进行恢复即可。 MySQL先写redo log 在写 Buffer Pool。

文件系统对大数据页的修改一般不是原子操作，如果刷脏页的时候，MySQL突然宕机了，可能出现对物理数据页的部分写入。就是部分写问题，此时物理页已经发生了损坏，不能再用redo log 解决, 所以InnoDB引入了 double write buffer ，刷脏页之前先把这个页写到磁盘另一个位置，再写入应该写的位置，如果数据页出现损坏，可以通过该页的副本还原该页，然后再redo log 重做

先写 redo log 再写Buffer Pool， redo log记录物理页的修改，追加操作，顺序IO 很快， 事务提交就能持久化redo log。 Buffer Pool 就定期刷脏，刷脏会有 double write buffer 。一旦宕机，Buffer Pool中的数据没了，就用 redo log 来恢复数据，如果有物理页损坏了，就用 double write buffer 中的页恢复.

隔离性实现原理 ：
隔离性追求的是并发情形下事务间互不干扰。写操作InnoDB通过加锁来保证隔离性。 读操作 InnoDB 通过 MVCC 保证隔离性

事务在修改数据钱，需要获取相应的锁，事务操作期间这部分数据是锁定的，其他事务要想修改数据就得等着当前事务提交或回滚释放锁
按照锁的粒度，锁可以分为表锁和行锁。 表锁锁表，并发性能差，行锁并发性能好，但是如果要锁的数据很多表锁可以节省资源。
MyIsam只支持表锁，而InnoDB 同时支持表锁和行锁

InnoDB默认隔离级别是可重复读，能解决脏读、不可重复读、幻读等问题。 使用的是MVCC多版本并发控制协议。最大的优点是读不加锁，读写不冲突，并发性能好。
MVCC主要基于以下技术和数据结构：

1. 隐藏列： InnoDB每行数据都有隐藏列，隐藏列包含本行数据的事务 id 、指向 undo log 的指针等
2. 基于undo log 的版本链： 每行数据的隐藏列包含了指向undo log 的指针， undo log 也包含了更早版本的undo log指针， 形成版本链
3. ReadView： 通过隐藏列和版本链， MySQL 可以将数据恢复到指定版本，但是具体恢复到哪个版本需要根据ReadView来确定。
   ReadView是指事务A在某一时刻给整个事务系统打快照，之后再读数据就比较事务 id 和快照， 从而判断该ReadView是否可见。

一致性实现原理：
一致性是事务追求的最终目标。 原子性、持久性、隔离性都是为了保证数据库状态的一致性。除了数据库层面保障，应用层面也得保障：

3. 事务隔离级别

隔离级别	脏读	不可重复度	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复读	×	×	√
串行化	×	×	×

1. 脏读： 事务A读到事务B没提交的数据
2. 不可重复读： 事务A两次读不一样，因为事务B中间修改了数据并提交了
3. 幻读：事务A两次根据某个条件查数据，数据结果条数不同。不可重复读是数据变了， 幻读是条数变了

4. MySQL事务隔离级别怎么实现的

读未提交： 不加锁
读已提交： 通过行锁，未提交的时候读不到。 InnoDB底层使用MVCC总是读取最新一份的快照数据
可重复读： 使用Next-Key Lock算法实现行锁， 不允许读取事务过程中已提交的数据。 该算法包含间隙锁，会锁定一个范围，解决幻读
串行化： 为每个读取操作加一个共享锁，读占用了锁，对一致性的非锁定读不在支持。

锁

1. 了解数据库中的锁吗？

锁机制用于管理对共享资源的并发访问。以InnoDB引擎为例。
行锁类型：

1. 共享锁：允许事务读一行数据， 读读共享
2. 排他锁：允许事务删除或更新一行数据。想获取排他锁必须等待其他事务释放行上的共享锁。 读写、写写互斥

锁的粒度：

3. 意向共享锁： 事务想要获取一张表中某几行的共享锁，得先获取意向锁
4. 意向排他锁： 事务香获取一张表中某几行得排他锁，得先获取意向排他锁

锁的算法：

1. Record Lock“ 单个行记录上的锁
2. Gap Lock： 间隙锁，锁定一个范围，但不包含记录本身，防止多个事务将记录插入同一范围，这会导致幻读产生
3. Next-Key Lock：= Record Lock + Gap Lock, 锁定一个范围，包括锁定记录本身

死锁：
死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去

解决死锁最简单的方法是超时，即超时后一个事务回滚，另一个等待的事务就能继续进行

InnoDB采用了wait-for graph（等待图）的方式来进行死锁检测， 等待图通过锁的信息链表、事务等待链表构造出一张图，如果这个图存在回路就代表发生死锁，每个事务请求锁并发生等待时，都会判断等待图中是否存在回路，如果存在回路说明有死锁，InnoDB通常会回滚undo 量最小的事务。

锁升级：
锁升级指的是降低锁的粒度。比如1000个行锁升级为一个页锁，或者页锁升级为表锁， InnoDB不存在锁升级的情况

2. InnoDB中行级锁是怎么实现的？

InnoDB行级锁是通过给索引上的索引项加锁来实现的，如果没用的索引则加的是表锁

3. 数据库在什么情况下会发生死锁

优化

1.说说你对数据库优化的理解

MySQL数据库优化是多方面的，原则就是减少系统瓶颈，减少资源占用，增加系统反应速度。
优化文件系统提高磁盘IO速度，优化操作系统调度策略提高MySQL负载能力， 优化表结构、索引、查询语句等使查询响应更快

针对查询，可以通过使用索引或使用连接代替子查询的方式来提高查询速度
针对慢查询，可以通过分析慢查询日志，来发现引起慢查询的原因，从而有针对的进行优化
针对插入，可以通过禁用索引、禁用检查等方式来提高插入速度，插入完数据再启用索引和检查
针对数据库结构，可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化

2. 如何优化MySQL的查询

①使用索引：
如果不使用索引，查询语句会全表扫描，数据量很大的情况下会很慢，查询语句可以根据索引快速定位到待查询记录，减少查询的记录数。

②优化子查询：
子查询就是SELECT 语句的嵌套，一个查询结果作为另一个查询语句的条件，子查询可以一次性完成很多逻辑上需要多个步骤的才能完成的SQL操作，使用灵活，但是执行效率不高。 执行子查询的时候MySQL会先给内层查询建立一个临时表，然后再外层查询语句从临时表里查询记录。查询完毕再撤销这些临时表。因此子查询速度要慢，数据越多越慢。
使用连接查询 JOIN 来代替子查询，不需要建立临时表，速度比子查询快。如果查询中用到索引，性能会更高。

3. 怎么插入数据才能更高效？

影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。

• 禁用唯一性检查， 数据导入后再开启唯一性检查
• 禁用外键检查、数据导入完再恢复
• 禁用事务自动提交，数据导入完再开启
• 使用load的指令，或者一次性插入多条数据
• 导入的数据尽量按主键的顺序排列

4. 表中包含了几千万条数据该怎么办？

按以下顺序优化：

1. 优化SQL 和 索引
2. 增加redis缓存
3. 读写分离、采用主从复制、或者主主复制
4. 垂直拆分，根据模块耦合度将大系统分为多个小系统，比如会员数据库、订单数据库、支付数据库、消息数据库等
5. 水平拆分，把一张表拆分到不同的数据库中进行存储，或者把一张表拆分成很多张小表。

5. MySQL的慢查询优化有了解吗？

优化MySQL的慢查询步骤：

1. 开启慢查询日志
   MySQL中慢查询日志默认是关闭的，启动慢查询日志，配置指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询记录就会被记录到慢查询日志文件中
2. 分析慢查询日志
   分析慢查询日志， 用 explain 关键字来分析sql慢查询语句

常见的慢查询优化：

1. 索引没有起作用的情况
2. 优化数据库结构： 对于字段比较多的表，有些字段使用频率低可以分离出来形成新表。
   对于需要经常联合查询的表，可以建立中间表，将联合查询改为对中间表查询
3. 分解关联查询： 对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联
4. 优化Limit分页： 当偏移量非常大的时候，会舍弃掉偏移量的数据。优化此类查询最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。这样做效率会大大提升。

6. 说说你对 explain 的了解

explain + 查询语句， 用来分析查询语句。重要关注 type、 key、 key_len、 rows、 Extra

type:

Extra:

7. 百万级别或以上数据如何删除

由于索引需要额外的维护成本， 因为索引文件是单独存在的文件， 所以当我们对数据的增加， 修改， 删除， 都会产生额外的对索引文件的操作， 这些操作需要消耗额外的 IO，会降低增/改/删的执行效率。 所以， 在我们删除数据库百万级别数据的时候， 查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟） ， 然后删除其中无用数据（此过程需要不到两分钟） ， 删除完成后重新创建索引（约十分钟左右）

其他

1. 数据库设计三大范式

第一范式： 原子列
第二范式：在第一范式的基础上，非主属性必须完全依赖于主属性（消除部分依赖）
第三范式：在第二范式的基础上，非主属性之间不存在相互依赖（消除传递依赖）

2. 说说你对MySQL引擎的了解

MySQL 5.5 之后 InnoDB 是默认引擎， 之前是MyISAM。
InnoDB 支持事务、外键、MVCC、行锁、聚簇索引
MyISAM专门存了count(*)，有三个文件：表定义文件，数据文件，索引文件

3. 说说你对MySQL日志的了解

1. binlog 二进制日志： 记录所有修改数据库的操作，以二进制的形式记录在日志文件中，还包括每条语句执行时间、耗费资源、事务信息

2. redo log 重做日志： 用来实现事务的持久性，包括redo log buffer 和 redo log file。 redo log 保证事务持久性。
   数据库页修改是随机IO， 比较慢，所以引入Pool buffer ，查询先查Pool buffer ，没有的话查数据库，再把对于页放入Pool buffer ，修改的时候也是直接修改Pool buffer ， 然后后台定期进行把修改过的Pool buffer 刷入磁盘称为刷脏页。Pool buffer内的文件是易失的，所以引入 redo log，如果Pool buffer 数据丢失可以用redo log 进行恢复，redo log是顺序读写，很快，所以每次修改先写redo log 然后写Pool buffer中页，事务提交或者每隔 1s 持久化 redo log。 如果刷脏页得时候出现部分写问题，MySQL引入了 double write 解决

3. undo log 回滚日志：回滚日志记录事务行为，实现事务的原子性， undo log 还可以实现 MVCC 中快照读

4. 错误日志、查询日志、慢查询日志

4. 谈谈你对MVCC的了解

InnoDB默认隔离级别是RR，可重复读。 使用MVCC解决了 脏读、不可重复读、幻读等问题。MVCC多版本并发控制协议，最大的优点就是读不加锁， 因此读写性能好，并发性能高。实现MVCC主要基于：隐藏列、undo log 版本链、 ReadView读视图 三大技术。

隐藏列： InnoDB中每行数据都有隐藏列，隐藏列包含本行数据的事务 id 指向undo log 的指针等， 而每条 undo log 也会指向更早版本的 undo log ，从而形成一条版本链。 通过隐藏列和版本链，MySQL可以将数据恢复到指定版本。但是具体要恢复到哪个版本，需要有ReadView来确定。所谓 ReadView， 就是事务在某时刻给事务系统打快照，之后再进行读操作时，会将读取到的数据中的事务 id 和快照比较，从而该数据对 ReadView 是否可见，即对事务A是否可见

5. MySQL主从同步是如何实现的？

复制（replication）是MySQL数据库提供的一种高可用高性能的解决方案
这个同步不是完全实时的，复制工作原理就是从服务器两个线程， 一个是I/O线程读取主服务器的二进制日志，保存为中继日志；
另一个是SQL线程，执行中继日志。

6. 索引覆盖、回表查询、索引下推、索引失效

索引覆盖，只查索引列，不用回表
回表， 非主键索引，叶子节点存的是索引和主键
索引下推：没有索引下推的时候，存储引擎检索到数据，返回给MySQL服务器，然后服务器判断是否符合条件。
举例 如果是联合索引 (name, age) 查询以姓张的8岁小朋友的所有信息。MySQL通过走索引，获取所有姓张的，然后回表查到所有姓张的人的信息1000万条。 返回给MySQL服务器，服务器剔除其中年龄不等于8的800万条。这个速度很慢，相当于进行的800万次无效的回表查询。
有了索引下推，还是这个联合索引，找到姓张的索引之后，直接判断年领是不是8，是才回表，否则跳过。相当于只需回表200万次。
这个例子还能优化， 之前是扫描联合索引1000万行，回表200万次。加入虚拟列，first_name， 建立联合索引（first_name, age）
这样查first_name = ‘张’ and age = 8 只需要扫描联合索引200万行，回表200万次即可

索引失效：不满足最左、 函数或计算、不等号（可能）、 模糊查询%放前面、发生隐式转换（字符串不加引号，数字加引号了）、OR不全是索引， 数据量少。

!= 是可以走索引的，但是实际情况可能不走。首先如果数据量少本身就不会走索引，而是全表扫描。 还有一点和返回的数据集量有关，如果返回的数据量大于整表的 20% 就不会走索引，而是选择全表扫描。 如果不等于这个值，得到的结果很少，是会走索引的。