52.整理MySQL

为什么要使用数据库?

数据如果保存在内存:存取速度快,但数据不能永久保存

数据保存在文件:数据永久保存,速度比内存操作慢,频繁IO,查询数据也不方便

数据保存在数据库:数据永久保存,使用sql语句查询很方便,管理数据方便

数据库的三大范式?

列不可分,非主键列完全依赖于主键,非主键列不依赖于其他非主键,这三大范式是递进关系,比如第二范式需要在第一范式的基础上

MyISAM对于Innodb引擎的区别?

	MyISAM	Innodb
存储空间	可以被压缩,存储空间小	需要更多的内存
可移植性	以文件形式存储,在跨平台中转移数据方便	需要拷贝数据文件,备份binlog等
外键	不支持	支持
事务	不支持	支持
哈希索引	不支持	支持
全文索引	支持	不支持(5.6以后支持)
索引实现方式	B+,非聚簇索引	B+,聚簇索引
锁	表锁	表锁和行锁
select语句/count操作	更高效(count维护了一个计数器)
insert,update,delete		更高效

MyISAM:适用于以读写插入为主的应用程序,比如博客系统

Innodb:更新(删除)操作频率高,或者要保证数据完整性,并发量高,支持事务和外键,比如电商后台

MyISAM和InnoDB索引的区别?

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
• MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
• InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引(即遵循最左查询原则)会非常高效。

索引是什么?优缺点?使用场景?

索引是特殊的文件,包含着对数据表里所有记录的引用指针,相当于目录

优点:加快数据检索速度,在查询过程中,提高性能

缺点:创建和维护索引需要耗费时间,具体的,当对表中数据进行增加,删除和修改的时候,索引也需要动态的维护,降低了效率;索引还需要占用额外的空间

使用场景:

• where语句中使用的列
• order by使用的列
• join连接 on所使用的列
• 覆盖索引

索引的类型?

• 主键索引:列不重复且不为null,只能有一个
• 唯一索引:列不重复,但允许为null,可以有多个
• 普通索引:列可重复,允许为null,可以多个
• 全文索引:MYISAM引擎使用,用于替代效率较低的like模糊匹配操作(innodb后续引进)
• 组合索引(覆盖索引):最常使用的限制条件放在最左边建立的多列索引,利于提高效率

索引的数据结构?

B树索引(实际是使用b+树实现):主键索引时,一次检索直接拿到数据,非普通索引查询时,拿到主键id,再拿到具体数据(二次检索)

哈希索引:使用hash算法,将数据库字段数据转换成定长的hash值,使用拉链法解决hash冲突,只能进行等值比较,不能使用like,=,>=等符号,但是等值比较的检索效率远远大于b树索引

创建索引的原则?

• 最左前缀匹配原则:带头大哥不能死,中间兄弟不能断,范围查询后面的全部寄
• 比较频繁查询的字段才创建索引
• 更新频繁的字段不适合创建索引
• 不能有效区分数据的列,比如性别不创建索引
• 尽量扩展索引,而不是新建索引,比如表中有a的索引,现在要加(a,b)索引,那么只需要修改之前的索引
• 定义有外键的数据列一定要建立索引
• 对于定义为text,image,bit的类型数据列不要建立索引

最左前缀:并不是说对于(a,b,c)复合索引,一定要使用where a=1 and b=2 and c=3,where中a,b,c三个位置交换也没有问题,因为sql查询优化器会将我们的语句优化,此外应该把最常使用的条件作为索引的左列

为什么数据库使用b+树而不是b树?

b树只适合随机检索,而b+树支持随机检索和顺序检索

b+树空间利用率高,可以减少io次数,磁盘读写代价更低,因为它的内部节点作为索引使用,使得其能容纳的节点中的关键字数量更多

b+树的查询效率更加稳定,因为所有的数据都要在叶子节点才能找到

b+树的叶子节点包含所有关键字,并以有序链表结构存储,可以提高增删效率

b+树对于范围查询,只需找到范围的一边,然后进行有序遍历链表即可

聚簇索引和非聚簇索引?非聚簇索引是否一定要回表查询?

聚簇索引:数据和索引放一起,找到了索引也就找到了数据

非聚簇索引:数据和索引分开,索引结构的叶子节点指向了数据对应行

在innodb中,在聚簇索引之上创建的索引叫辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引,唯一索引这些,存储的不是行的物理位置,而是主键的值

非聚簇索引不一定会回表查询,如果查询语句要求的字段都是索引的内容,就不必要再进行回表查询例如select age from employee where age < 20;

mysql中in和exists的区别?

in语句把外表和内表做hash连接,exists语句对外表循环,每次循环再对内表进行查询

如果两个表大小相当,那么使用两者差别不大

如果两个表中一个小,一个大,此时子查询表大的使用exists,小的使用in

not in和not exists:如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引,not exists的子查询可以用到表上的索引,所以无论哪个表大not exists都比not in要快

varchar 和char 的区别?

char表示定长字符串,长度固定,varchar长度可变

char中插入的数据长度小于其固定长度时,使用空格填充

char存取速度很快,varchar则慢一些

char能存放的字符数为255,varchar最多存放字符数为65532

varchar(50)中50的含义?int(20)中20的含义

最多存放50个字符,显示字符的长度为20

drop,delete,truncate的区别

drop:把表连同数据全部删除,不可回滚,速度最快

truncate:表结构依然存在,删除表中所有数据,不可回滚

delete:删除数据行,可以回滚,速度最慢

where字句的优化?索引什么时候会失效

对查询进行优化,应该避免全表扫描,首先考虑在where和order by涉及的列上建立索引

避免where字句中的null值判断,使用<>操作符,使用参数引擎会放弃使用索引进行全表扫描

避免在where字句中使用or来连接条件,且前后没有同时使用索引,会进行全表扫描

在连续少量数据的时候,能不用in与not in就不用比如in (1,2,3)这样的条件

类似于like "%吴%"这样的条件也会导致全表扫描

where语句的=号左边使用函数,算数符号操作会导致无法正确使用索引

大表优化?垂直拆分和水平拆分?

• 将字段很多的表分解成多个表(垂直拆分和水平拆分)
• 增加中间表用于联合查询
• 增加冗余字段,提高查询字段

垂直拆分:包括垂直分表和垂直分库(缺点在于主键冗余,需要管理冗余列,事务的处理变得复杂,仍然存在单表数据量过大的问题)

• 垂直分表:将一个表按照字段分成多个表,每个表存一部分的字段,一般会把常用字段放到一个表,不常用的放到另一个表,其优点在于可以更好的提升热门数据的查询效率,避免IO竞争减少锁表的概率(数据量小,读取时间也变小了)
• 垂直分库:按照业务模块的不同,将表拆分到不同数据库

 水平拆分:包括水平分表和水平分库(缺点在于分片事务一致性难以解决,数据扩展难度大,不易维护,跨节点的join查询问题逻辑复杂)

• 水平分表:同一个数据库内,把一个表的数据按照规则拆分到多个表中,其优点在于解决了单表数据量过大的问题,避免了IO竞争减少了锁表的概率
• 水平分库:把一个表的数据按照规则拆分到不同的数据库中,不同的数据库可以放到不同的服务器上

Innodb存储引擎的执行过程?

• MySQL执行器根据执行计划调用存储引擎的api查询数据
• 存储引起先从缓存池buffer pool中查询数据,如果没有就去磁盘查询,查询到了之后会将其放入缓存池中
• 在数据加载到buffer pool的同时,这条数据的原始记录会被保存到undo日志文件中
• innodb会在buffer pool中执行更新操作
• 更新后的数据会被记录在redo log buffer中
• 事务在提交的时候会同时做以下三件事情
• 1)将redo log buffer 中的数据刷入redo log文件
• 2)将本次操作记录到bin log文件
• 3)将bin log文件的名字和更新内容在bin log 的位置记录到redo log中,同时在redo log最后添加commit标记
• 使用一个后台线程将buffer pool中更新后的数据刷盘

MySQL架构的Server层的执行过程?

连接器:负责和客户端建立连接,获取权限,维持和管理连接

查询缓存:优先在缓存中进行查询,如果查到了就直接返回,查不到就去数据库查

解析器/分析器:对要执行的sql进行词法解析,对语义进行校验

优化器:经过一系列运算得出执行计划,包括选择使用哪个索引

执行器:调用接口完成sql的执行

ACID的详细解释

原子性:通过undolog来实现,当事务执行失败,利用undolog记录的原始数据修改回之前的状态

隔离性:Innodb的隔离性通过MVCC和锁的机制来实现

• MVCC:多版本并发控制,可以解决一些幻读问题,但是无法解决当前读的幻读,需要通过临键锁来解决
• 锁机制:事务在修改数据库之前,需要先获得相应的锁,获得锁的事务才可以修改数据,该事务操作期间,这部分的数据是锁定的,其他事务无法在此期间修改数据

• SELECT: 只能查找事务ID小于等于当前事务ID的版本和删除版本大于等于当前事务ID的版本
• INSERT：InnoDB为新插入的每一行保存当前事务编号作为行版本号。
• DELETE：InnoDB为删除的每一行保存当前事务编号作为行删除标识。
• UPDATE：InnoDB为插入一行新记录，保存当前事务编号作为行版本号，同时保存当前事务编号到原来的行作为行删除标识。

• 排它锁解决脏读：在读已提交的隔离级别下，事务A只有在对数据修改时才加排它锁，但直到事务 commit 时才释放锁。因此，同时进行的事务B希望读取同一行数据时，会被事务A的排它锁堵塞，所以解决了脏读的问题
• 共享锁解决不可重复读：在可重复读的隔离级别下，除了执行读已提交的排它锁方式，还会在读取一行数据时，为这行数据添加共享锁直至事务 commit。例如，事务A读取ID=1这一行数据，然后为ID=1添加共享锁，事务B同时希望update ID=1，此时获取写锁失败，因此在事务A执行完之前，没有其他任何事务可以对ID=1这一行做修改，因此解决了重复读的问题
• 临键锁解决幻读

持久性:靠redo log实现,执行sql时会保存已执行sql语句到一个redo log文件,为了提高效率,会先将数据写入redo log buffer的缓存区,写入过程如下:数据写入数据库时,执行过程会先写入redo log buffer,在redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中,这一过程称为刷盘

redo log buffer的使用可以大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时redo log buffer中修改的数据在内存还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。为了确保事务的持久性，在当事务提交时，会调用fsync接口对redo log进行刷盘 ，刷新频率由 innodb_flush_log_at_trx_commit变量来控制的：

•     0：表示不刷入磁盘；
•     1：事务每次提交的时候，就把缓冲池中的数据刷新到磁盘中；
•     2：提交事务的时候，把缓冲池中的数据写入磁盘文件对应的操作系统缓存里去，而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

一致性:包括数据的完整性和业务的一致性,该性质通过其他三个性质来保证

• 数据的完整性： 实体完整性、列完整性（如字段的类型、大小、长度要符合要求）、外键约束等

• 业务的一致性：例如在银行转账时，不管事务成功还是失败，双方钱的总额不变。

主键使用自增ID还是UUID?

自增ID:

• 优点:字段相较于UUID会小很多,数据库自动编号利于检索,无需担心主键重复问题
• 缺点:容易被其他人查到业务量,数据迁移时合表困难,高并发场景下竞争自增锁会降低数据库的吞吐能力

UUID:

• 优点:唯一标识不用考虑重复问题,在拆分,合并的时候也能保持全局唯一性,可以在应用层生成从而提高吞吐能力,无需担心业务量泄露
• 缺点:UUID是随机生成的,所以会发生随机IO,影响插入速度;占用空间大,建立的索引越多造成的影响越大;UUID之间比较大小比自增ID慢,影响查询速度

一般情况下,MYSQL推荐使用自增ID,因为MySQL的Innodb引擎主键索引是聚簇索引,如果主键是自增ID,其叶子节点主需要按顺序往后排就可以了,UUID是随机生成的,插入的时候会导致数据移动,产生内存碎片,导致插入性能的下降

InnoDB的行锁?

行锁的类型分为高能共享锁和排他锁,他们是通过给索引上的索引项加锁实现的,只有通过索引检索数据才能使用行锁,不然使用的都是表锁

• select ... for update:添加排他锁
• select.. lock in share mode:添加共享锁

在InnoDB中为了解决幻读引入了临键锁(=间隙锁+记录锁),根据索引划分为一个个左开右闭的区间,在进行范围查询的时候,如果命中索引且能检索到数据,会锁住所在区间和它的下一个区间

当记录存在时,使用临键锁:

当记录不存在时,临键锁退化为间隙锁:

 当记录存在且是唯一索引时,临键锁退化成记录锁:

当为等值查询,且记录存在,且不是唯一索引时,会增加记录锁并且在前后区间增加间隙锁

MySQL的主从复制?

流程

1. master服务器在执行sql语句后,记录在binlog二进制文件中
2. slave端的IO线程连接上master端,请求从指定bin log日志文件的指定pos开始复制之后的日志内容
3. master端在接收到来自slave端的IO线程请求之后,通知负责复制进程的IO线程,根据slave端IO线程的请求信息,读取指定binlog日志指定pos之后的日志信息,然后返回给slave端的IO线程,返回信息还包含binlog文件名和pos位置(binlog dump thread)
4. slave端的IO线程在接收到master的IO返回的信息后,将接收到的binlog日志内容依次写入到slave端的relay log文件的最末端,并且将读取到的binlog文件名和pos位置记录到master-info文件中,以便下一次同步的时候能告诉master从哪个位置开始进行数据同步(slave I/O thread)
5. slave端的sql线程在检测到relay log文件中新增内容后,就马上开始解析文件的内容,然后还原成在master端执行的sql语句并且执行,保证数据的一致性(slave sql thread)

优点

• 读写分离,通过动态增加从服务器拉提高数据库的性能,在主服务器上执行写入和更新,在从服务器上执行读功能
• 提高数据安全,因为数据已经复制到从服务器,所以可以在从服务器上备份而不破坏主服务器的数据

视图是什么?和普通的表有什么区别?

视图是一种虚拟存在的表,视图并不在数据库中实际存在,行与列数据来自定义视图的查询中使用的表,并且使在使用视图时动态生成的,通俗的将,视图就是一条select语句执行后返回的结果集

视图对于普通的表优势如下:

• 简单:不关心后面对应表的结构,关联条件和筛选条件,对用户来说已经是过滤好的符合条件的结果集
• 安全:使用视图的用户只能访问他们被允许查询的结果集
• 数据独立:一旦视图结构确定了,可以屏蔽表结构变化对用户的影响

存储过程和函数是什么?

存储过程和函数是实现经过编译并存储在数据库中的一段sql语句集合,调用存储过程和函数可以简化开发人员的工作,减少数据在数据库和应用服务器之间的传输,两者的区别在于存储过程没有返回值,函数有返回值

mysql的体系结构?

连接层:一些客户端和链接服务,包含本地sock通信和类似于tcp/ip的通信

服务层:sql接口,缓存的查询,sql的分析和优化,内置函数的执行

引擎层:负责mysql中数据的存储和提取

存储层:将数据存储在文件系统之上,完成与存储引擎的交互

sql优化的步骤?

查看sql执行频率

定位低效率执行的sql

• 慢查询日志:slow-query-log=on,设定long-query-time,指定slow-query-log-file
• show precesslist:查看mysql正在进行的线程,包括线程状态,是否锁表等

使用explain语句进行状态分析

如何对like "%xxx"的字句进行优化?

可以在数据库增加冗余列,比如mobile字段的冗余列reverse_mobile,在此字段是mobile字段的倒序,对'%xxx'的查找用不到索引但是对于'xxx%'的查询是可以用到索引的