面试集锦（六）数据库

最新推荐文章于 2022-07-29 21:27:01 发布

lamlados

最新推荐文章于 2022-07-29 21:27:01 发布

阅读量302

点赞数

分类专栏：面试文章标签：数据库 mysql

本文链接：https://blog.csdn.net/weixin_42415948/article/details/109107719

版权

面试专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据库

https://blog.csdn.net/justloveyou_/article/details/78308460

https://www.jianshu.com/p/400b707c4509

1.MySQL

1.1 基本架构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ytHgIMcb-1602775881661)(C:\Users\vocyd\AppData\Roaming\Typora\typora-user-images\image-20200908153503991.png)]

1.2 范式NF

Normal Form

第一范式：列不可分。
第二范式：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖，即需要有主键，并且非主键的列和主键不存在部分依赖而是完全依赖，一张表只描述一件事）。
第三范式：在2NF的基础上，无传递依赖（非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况），各个信息应该只在一个地方存储，不出现在多个表中。

1.3 事务

事务是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

ACID原则：

原子性 Atomicity：要么全部成功，要么全部失败。
一致性Consistency：事务的执行不能破坏数据库的完整性和一致性。
隔离性Isolation：并发的事务是互相隔离的，不能互相干扰。
持久性Durability：事务一旦提交，对数据的改变是持久化的保存的。

并发带来的问题：

脏读：一个事务读取了另一个事务未提交的数据；
不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。

隔离级别：

READ UNCOMMITTED：最低级别的隔离，通常又称为dirty read，它允许一个事务读取另一个事务还没commit的数据，这样可能会提高性能，但是会导致脏读问题；
READ COMMITTED：在一个事务中只允许对其它事务已经commit的记录可见，该隔离级别不能避免不可重复读问题；
REPEATABLE READ：MySQL默认隔离级别，在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback。但是，其他事务的insert/delete操作对该事务是可见的，也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库。
SERIALIZABLE：最高级别的隔离，只允许事务串行执行。

MySQL的事务支持（与引擎相关）：

MyISAM：不支持事务，用于只读程序提高性能；
InnoDB：支持ACID事务、行级锁、并发；
Berkeley DB：支持事务。

1.4 MySQL的引擎对比

MyISAM：

MySQL 5.5之前的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良。性能极佳，但却有一个显著的缺点：不支持事务处理。使用B+Tree作为索引结构，索引和数据文件是分离的，索引文件仅保存数据记录的地址。

InnoDB：

InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL。采用MVCC（Multi Version Concurrency Control，多版本并发控制）来支持高并发。基于聚簇索引建立。使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

具体地，MyISAM与InnoDB作为MySQL的两大存储引擎的差异主要包括：

存储结构：每个MyISAM在磁盘上存储成三个文件：第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)。InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
存储空间：MyISAM可被压缩，占据的存储空间较小，支持静态表、动态表、压缩表三种不同的存储格式。可以极大的减小磁盘占用，减少磁盘IO，但是不能编辑。InnoDB需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
可移植性、备份及恢复：MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，同时在备份和恢复时也可单独针对某个表进行操作。InnoDB免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
事务支持：MyISAM强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。InnoDB提供事务、外键等高级数据库功能，具有事务提交、回滚和崩溃修复能力。
AUTO_INCREMENT：在MyISAM中，可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，它可以根据前面几列进行排序后递增。InnoDB中必须包含只有该字段的索引，并且引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
表锁差异：MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的新能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
全文索引：MyISAM支持 FULLTEXT类型的全文索引；InnoDB不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
表主键：MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址。对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键（用户不可见），数据是主索引的一部分，附加索引保存的是主索引的值。
表的具体行数：MyISAM保存表的总行数，select count() from table;会直接取出出该值；而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
CURD操作：在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择。对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
外键：MyISAM不支持外键，而InnoDB支持外键。
查询性能：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。

1.5 MVCC机制

https://www.jianshu.com/p/d67f0329d3bf

MVCC: Multiversion Concurrency Control，翻译为多版本并发控制，其目标就是为了提高数据库在高并发场景下的性能。

MVCC最大的优势：读不加锁，读写不冲突。在读多写少的场景下极大的增加了系统的并发性能。

MySQL的事务是在存储引擎层实现的，MyISAM并不支持事务，所以InnoDB实现了MVCC的事务并发处理机制，只出现在RC和RR下。单纯的并发会出现问题：更新丢失、脏读、不可重复读、幻读。对于幻读，单纯的MVCC机制并不能解决幻读问题，InnoDB也是通过加间隙锁来防止幻读。解决并发带来的问题，最通常的就是加锁，但锁对于性能也是腰斩性的，所以MVCC就显得十分重要了。

在InnoDB中MVCC的实现通过两个重要的字段进行连接：DB_TRX_ID和DB_ROLL_PT，在多个事务并行操作某行数据的情况下，不同事务对该行数据的UPDATE会产生多个版本，数据库通过DB_TRX_ID来标记版本，然后用DB_ROLL_PT回滚指针将这些版本以先后顺序连接成一条 Undo Log 链。

DB_TRX_ID：事务id，6bytes，每处理一个事务，值自动加一。InnoDB中每个事务有一个唯一的事务ID叫做 transaction id。在事务开始时向InnoDB事务系统申请得到，是按申请顺序严格递增的每行数据是有多个版本的，每次事务更新数据时都会生成一个新的数据版本，并且把transaction id赋值给这个数据行的DB_TRX_ID。
DB_ROLL_PT：回滚指针，7bytes，指向当前记录的ROLLBACK SEGMENT 的undolog记录，通过这个指针获得之前版本的数据。该行记录上所有旧版本在 undolog 中都通过链表的形式组织。
DB_ROW_ID（隐含id，6bytes，由innodb自动产生）：如果声明了主键，InnoDB以用户指定的主键构建B+Tree，如果未声明主键，InnoDB 会自动生成一个隐藏主键，说的就是DB_ROW_ID。另外，每条记录的头信息（record header）里都有一个专门的bit（deleted_flag）来表示当前记录是否已经被删除。

如何实现高并发下RC和RR的隔离性？在MVCC机制下基于生成的Undo log链和一致性视图ReadView来实现。

要实现read committed在另一个事务提交之后其他事务可见和repeatable read在一个事务中SELECT操作一致，就是依靠ReadView，对于read uncommitted，直接读取最新值即可，而serializable采用加锁的策略通过牺牲并发能力而保证数据安全，因此只有RC和RR这两个级别需要在MVCC机制下通过ReadView来实现。

InnoDB为每一个事务构造了一个数组m_ids用于保存一致性视图生成瞬间当前所有活跃事务(开始但未提交事务)的ID，将数组中事务ID最小值记为低水位m_up_limit_id，当前系统中已创建事务ID最大值+1记为高水位m_low_limit_id。

通过生成一致性视图，操作遍历Undo log链，根据当前版本与一致性视图生成的id对比，若该版本在ReadView生成前就已经完成提交，则可以被当前事务访问。若该版本在ReadView生成之后才生成，不能被访问，根据当前版本指向上一版本的指针DB_ROLL_PT访问上一个版本，继续判断。若在生成id区间内，如果DB_TRX_ID与当前事务id相等则可以访问。

2. 索引

https://zhuanlan.zhihu.com/p/113917726

**索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据库表中数据。**索引的实现通常使用B TREE及其变种。索引加速了数据访问，因为存储引擎不会再去扫描整张表得到需要的数据；相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-adnlviTO-1602775881665)(C:\Users\vocyd\AppData\Roaming\Typora\typora-user-images\image-20200909092409077.png)]

2.1 索引底层选型

在数据结构中，最为常见的搜索结构就是二叉搜索树和AVL树（高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度）。然而，无论二叉搜索树还是AVL树，当数据量比较大时，都会由于树的深度过大而造成I/O读写过于频繁，进而导致查询效率低下，因此对于索引而言，多叉树结构成为不二选择。特别地，B Tree的各种操作能使B树保持较低的高度，从而保证高效的查找效率。

2.1.1 Hash表

把任意值通过哈希函数变换为固定长度的 key 地址，通过这个地址进行查询具体数据的数据结构。哈希函数可能对不同的 key 会计算出同一个结果，出现碰撞问题。

解决Hash碰撞问题的一个常见处理方式就是链地址法，即用链表把碰撞的数据接连起来。计算哈希值之后，还需要检查该哈希值是否存在碰撞数据链表，有则一直遍历到链表尾，直达找到真正的 key 对应的数据为止。哈希算法时间复杂度为 O(1)，检索速度非常快。

使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，要一次把所有数据找出来加载到内存，然后再在内存里筛选筛选目标范围内的数据，过于笨重。因此哈希索引不适合作为 MySQL 的底层索引的数据结构。

2.1.2 二叉查找树 BST

二叉查找树的时间复杂度是 O(lgN)，可以提供范围查找（左右节点有序）。

普通BST的缺点：

极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O(N)，检索性能急剧下降。不能直接用于实现 Mysql 底层索引。在数据库中，数据的自增是一个很常见的形式，比如一个表的主键是 id，而主键一般默认都是自增的，如果采取二叉树这种数据结构作为索引，那上面介绍到的不平衡状态导致的线性查找的问题必然出现。

AVL自平衡二叉查找树：在调整二叉树的形态上消耗的性能会更多。大量的顺序插入不会导致查询性能的降低。

缺点：每一个树节点只存储了一个数据，一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 id=7 这个数据我们就要进行磁盘 IO 三次，过于消耗时间。要尽可能减少磁盘 IO 的次数。

红黑树：是一颗会自动调整树形态的树结构，比如当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O(logN)），也就保证了查找效率不会明显减低。红黑树拥有不错的平均查找效率，也不存在极端的 O(N)情况。

缺点：当数据是顺序插入时，树的形态一直处于“右倾的趋势。从根本上上看，红黑树并没有完全解决二叉查找树这个问题。虽然这个“右倾”趋势远没有二叉查找树退化为线性链表那么夸张，但是数据库中的基本主键自增操作，主键一般都是数百万数千万的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗。

2.1.3 B Tree、B+ Tree

https://www.cnblogs.com/vincently/p/4526560.html

一棵m阶的B-Tree有如下特性：

每个结点最多m个子结点。
除了根结点和叶子结点外，每个结点最少有m/2（向上取整）个子结点。
如果根结点不是叶子结点，那根结点至少包含两个子结点。
所有的叶子结点都位于同一层。
每个结点都包含k个元素（关键字），这里m/2≤k<m，这里m/2向下取整。
每个节点中的元素（关键字）从小到大排列。
每个元素（关键字）字左结点的值，都小于或等于该元素（关键字）。右结点的值都大于或等于该元素（关键字）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XOUfNWzt-1602775881668)(C:\Users\vocyd\AppData\Roaming\Typora\typora-user-images\image-20200909095917769.png)]

但是考虑到磁盘 IO 读一个数据和读 100 个数据消耗的时间基本一致，那我们的优化思路就可以改为：尽可能在一次磁盘 IO 中多读一点数据到内存。这个直接反映到树的结构就是，每个节点能存储的 key 可以适当增加。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Ok9gCUb-1602775881670)(C:\Users\vocyd\AppData\Roaming\Typora\typora-user-images\image-20200909100004576.png)]

优点：优秀检索速度，时间复杂度：B 树的查找性能等于 O(h*logn)，其中 h 为树高，n 为每个节点关键词的个数；尽可能少的磁盘 IO，加快了检索速度；可以支持范围查找。

B 树和 B+树有什么不同呢？

B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。
B 树一个节点里存的是数据，而 B+树存储的是索引（地址），所以 B 树里一个节点存不了很多个数据，但是 B+树一个节点能存很多索引，B+树叶子节点存所有的数据。

优点：
- 由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
- B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。在数据库中基于范围的查询是非常频繁的，而B树只能中序遍历所有节点，效率太低。

通过 B 树和 B+树的对比可以看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，更具备效率。因此 MySQL 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

2.1.4 特性索引

自适应哈希索引

InnoDB其中一个特性，SHOW ENGINE INNODB STATUS可以查看当前自适应哈希索引使用情况。如果开启该索引，innodb会监控常用的等值查询，利用buffer pool在B+ Tree的结构上建一个Hash索引，提高查询效率。

查看当前自适应哈希索引：show variables like ‘%ap%hash_index’ 默认开启

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。查找条件使用 MATCH AGAINST，而不是普通的 WHERE。全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

空间数据索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。必须使用 GIS 相关的函数来维护数据。

2.2 索引的优缺点

优点：

大大加快数据的检索速度，这也是创建索引的最主要的原因；
加速表和表之间的连接；
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

缺点：

时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护和修改，这样就降低了数据的维护速度；
空间方面：索引需要占物理空间。

2.3 类别

聚簇索引（聚集索引）:

不是一种单独的索引类型，而是一种数据存储方式，InnoDB的聚簇索引就是指B Tree索引和数据行组成的数据存储方式。聚簇指的就是数据行和键值紧凑的存储在一起。

当表有聚簇索引时，它的数据行实际存放在索引的叶子页中，由于无法把数据行保存在两个不同地方，所以一个表只有一个聚簇索引，而且是主键，如果没设主键，InnoDB会隐式帮忙定义生成一个类似Row id的东西作为聚簇索引。覆盖索引可以模拟多个聚簇索引的情况。

辅助索引（二级索引、非聚集索引）：

在InnoDB中，除了基于主键创建的聚集索引，其它索引如唯一索引、普通索引（二者根据索引列的值的唯一性）都是辅助索引。

覆盖索引：

如果在一个查询中，一个索引包含（或者说覆盖）说要查询字段的值，我们就称之为覆盖索引，覆盖索引准确来说也不应该算是一种索引类型，而是一种提高性能的查询方式或者说工具。这也是为什么推荐使用select 索引字段 from table而不是select * from table ，select * 会导致没必要的回表。

如果一条SQL语句使用覆盖查询，只读取索引，可以极大减小数据访问量，不用再去查询数据行并返回数据行，一方面可以减少IO，也对缓存的性能有所提高，MySQL不用花更多的时间做数据拷贝，只需拷贝索引数据即可，且索引比数据更小，更容易放在内存中代价更小。对于B+ Tree，索引按顺序存储，顺序IO往往更快。

前缀索引：

当要索引的列字符很多时索引则会很大且变慢,使用前缀索引能有效减小索引文件大小，提高索引速度，适用于大字段。

语法：ALTER TABLE table_name ADD KEY(column_name(prefix_length));

比如：ALTER TABLE city ADD KEY(cityname(7));

联合索引（多列索引）：

由多列组成的的索引。遵循最左前缀规则。对where，order by，group by 都生效，在一些常用的组合查询场景中，建立联合索引可以很大程度提高查询性能。联合索引像一种结构的电话簿，电话簿首先按姓做排序，然后再按名排序，如果只知道姓可以很快定位到电话，只知道名就没啥帮助了。

注意：

联合索引也需要遵循最左匹配原则，这也是由于它的结构,比如建了联合索引（name,age）：①select * from stu where name=? ②select * from stu where name=? and age=? ③select * from stu where age=? and name=? （优化器会优化），这3条SQ都会走联合索引，但是select * from stu where age=?则不行，因此建议联合索引把经常查询的字段放在左边。
联合索引的数据只要有一列是null值就不生效。
联合索引有“最左前缀”原则，遇到范围查询（>.<.between）这样无效。
当创建(a,b, c)联合索引时，相当于创建了(a),(a,b),(a,b,c)三个索引，想要生效的话也只有这三个组合，但是很神奇的ac,ca（会有优化）也可以，原因在于有带a就会去走a单列索引，总结一下就是abc3个最左边的带头大哥(a)不能没，如果是bc是不行的。

三星索引：

如果与一个查询相关的索引行是相邻的，或者至少足够靠近的话，那这个索引就可以被标记上第一颗星；是一种理想的索引设计方式，很难达到。

索引将相关的记录放在一起，一星
索引的顺序和查找数据的顺序一致二星
索引包含要查询数据的全部列避免去聚簇索引进行一次随机的IO查询三星

2.4 更多问题

什么样的字段适合创建索引？

经常作查询选择的字段；
经常作表连接的字段；
经常出现在order by，group by，distinct 后面的字段。

什么情况下设置了索引但无法使用？

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配；
OR语句前后没有同时使用索引；
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；
对于多列索引，必须满足 最左匹配原则（eg：多列索引col1、col2和col3，则索引生效的情形包括 col1或col1，col2或col1，col2，col3）。

创建索引时需要注意什么？

非空字段：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
取值离散（变量各个取值之间的差异程度）大的字段：放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高。

主键、自增主键、主键索引与唯一索引概念区别？

主键：指字段唯一、不为空值的列；
主键索引：指的就是主键，主键是索引的一种，是唯一索引的特殊类型。创建主键的时候，数据库默认会为主键创建一个唯一索引；
自增主键：字段类型为数字、自增、并且是主键；
唯一索引：索引列的值必须唯一，但允许有空值。主键是唯一索引，这样说没错；但反过来说，唯一索引也是主键就错误了，因为唯一索引允许空值，主键不允许有空值，所以不能说唯一索引也是主键。

主键就是聚集索引吗？主键和索引有什么区别？

**主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引。**在SQLServer中，主键的创建必须依赖于索引，默认创建的是聚集索引，但也可以显式指定为非聚集索引。InnoDB作为MySQL存储引擎时，默认按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引。

3. 优化

数据库设计规范

3.1 SQL语句优化

https://www.cnblogs.com/williamjie/p/9132390.html

定位慢SQL：遇到慢查询时，可以在查询语句前加explain关键字定位慢查询的原因。

查看慢查询日志：MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。可以通过分析工具找出IO大的SQL以及发现未命中索引的SQL，对于这些SQL，都是我们优化的对象。

优化数据访问：

是否向数据库请求了不需要的数据（减小请求数据量）：
- 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
- 只返回必要的列：最好不要使用 SELECT * 语句。正确的做法是只取需要的列。
- 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。（一般缓存可用Redis）
MySQL是否在扫描额外的记录（减少服务器端扫描行数）：
- 通过慢日志中的响应时间、扫描的行数和返回的行数、访问类型来衡量查询开销。
- 使用索引。

重构查询方式：

切分查询：将一个大的查询切分为许多小的查询，每个小查询功能完全一样，返回一部分结果，我们只需重复执行小查询就行。如在进行删除时，如果删除后暂停一会儿，再执行下一次，可以大大降低服务器的影响，还可以大大减少删除时锁的持有时间。
分解关联查询：可将单条的多表关联查询分解为多条查询，对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。（将在数据库中做的关联查询，转移到了应用层）

优点：

让缓存的效率更高。对单表查询的结果，应用程序可以很方便的缓存，分解语句之后，我们可以高效的利用缓存来进行查询。
将查询分解之后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身的效率也会得到提升。按照ID顺序查询比随机的关联要更加的高效。（使用in()的方式代替关联查询的join…on…）
减少冗余记录的查询。在应用层做关联查询，对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复的访问某一部分的数据。

常见SQL优化：

https://blog.csdn.net/qq_35642036/article/details/82820129

优化insert语句：一次插入多值；
应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
应尽量避免在 where 子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描；
优化嵌套查询：子查询可以被更有效率的连接（Join）替代；
做关联查询的时候小表驱动大表,有时候子查询使用exist替代in；
避免大表全表查询，可用explain查看扫描行数，使用索引、limit等方式，如果多个字段频繁查询可考虑使用合并索引；
统计数量时使用count替代sum，count条件为true统计数量；
减少扫描行数,最理想的情况扫描行数=返回行数，这需要适用合适的索引。explain的rows可以查看扫描了多少行（注意只是预估可能并非实际扫描）。

3.2 索引优化

索引也并非是最好的解决方案，小表不适用索引，全表反而更快，当帮助存储引擎快速找到记录的好处大于额外工作时建索引，但是对于特大型的表，建索引和使用索引的代价也会增长，io次数变多，树太大，这个时候可以考虑使用分区。

索引优化策略：

最左前缀匹配原则；
主键外检一定要建索引；
优先考虑在常用在where和order by的字段上建索引；
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0 * 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键，尽量不要在重复数据多的列上建索引；
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)；
为较长的字符串使用前缀索引；
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可；
不要过多创建索引, 权衡索引个数与DML之间关系，DML也就是插入、删除数据操作。这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也需要进行调整重建
对于like查询，“%”不要放在前面，会导致索引失效；
尽量使用覆盖索引。
频繁用来查询的组合字段可考虑联合索引，即索引合并；
索引字段尽量用小的，比如无符号的int；
选择合适的索引，一些场景也可以考虑Hash比如只用于做唯一查询没涉及到范围查询等情况；
索引的长度尽可能短，可提高查询性能，特别是主键索引，如果主键索引过长，不但会导致查询性能变差，同时由于二级索引都带主键索引，也会导致占用更大的磁盘空间。

3.3 库表结构优化

数据库表结构的优化包括选择合适数据类型、表的范式的优化、表的垂直拆分和表的水平拆分等手段。

数据库是存在瓶颈的，这里的瓶颈有：

IO瓶颈，查询大量数据产生多次磁盘IO读写，请求数据太多导致网络IO带宽不够。
CPU瓶颈，在做复杂操作时可能会比较耗CPU。

选择合适数据类型：

使用较小的数据类型解决问题，占用的磁盘、内存、cpu缓存更少，而且处理需要的cpu周期也更少；（比如使用无符号 tinyint默认-128-127 ，使用unsigned可以存0-255）
使用简单的数据类型（MySQL处理int要比varchar容易）；
尽可能的使用not null 定义字段，如果查询结果包含null对MySQL来说不好优化，因为null列使索引计算和值比较变复杂；
尽量避免使用text类型，非用不可时最好考虑分表；
选择合适类型，比如datatime和timesmap都可以存时间，timesmap空间效率更高，并且会根据时区变化具有特殊的自动更新能力，另一方面因为时间范围比较小也会受到限制。如对于 varchar类型和char类型，varchar节约存储空间对性能帮助更大，合适的类型在做关联查询时可以提高性能；
使用枚举（enum）代替字符串类型，MySQL存储枚举很紧凑，会做一些压缩处理；但是注意的是双重性容易导致混乱，所以尽量避免枚举的时候使用数字，应用字符串；

表的范式的优化：

一般情况下，表的设计应该遵循三大范式。范式的表通常也比较小，执行操作更快。数据范式化的话，没有太多冗余，更新操作比较容易，只要修改更少数据即可。

使用分区优化：

按一定规则，同一个表不同的行记录被分配到不同的物理文件中，分区是将一个表或索引分解成更多更小可以管理的部分，每个区都是独立可管理的，每个区的聚簇索引和非聚簇索引都放在各自区的物理文件里。

分区类型：

range分区最为常用连续区间列值最好为整型，如果是日期最好也使用函数切换为整型
list 与range类似，区别在于list是集合，range是连续
hash分区基于给定的分区个数将数据分配到不到分区只能支持整数取模运算
key分区和hash差不多但是支持字符串基于列的md5值做hash运算可以使用字符串

注意：

MySQL如果存在主键和唯一键，分区列必须包含在其中；
分区字段不能为null，不然没法区分；
分区数不能超过1024；
不支持外键；
只能对数据表的整形做分区，如果不是通过分区函数转为整形；
分区表不影响自增列。

垂直分库、分表：

注意：

分库：将一个表的数据分到不同的库中，每个库可以在不同服务器上。
分表：在同一个库的不同表中。

把含有多个列的表拆分成多个表，解决表宽度问题，具体包括以下几种拆分手段：

把不常用的字段单独放在同一个表中；
把大字段独立放入一个表中；
把经常使用的字段放在一起；

这样做的好处是非常明显的，具体包括：拆分后业务清晰，拆分规则明确、系统之间整合或扩展容易、数据维护简单。

水平分库、分表：

表的水平拆分用于解决数据表中数据过大的问题，水平拆分每一个表的结构都是完全一致的。一般地，将数据平分到N张表中的常用方法包括以下两种：

对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值。
针对不同的hashID将数据存入不同的表中。

表的水平拆分会带来一些问题和挑战，包括跨分区表的数据查询、统计及后台报表的操作等问题，但也带来了一些切实的好处：

表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高数据松散度，表数据量在可控范围内可提高查询效率；
表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用；
需要把数据存放到多个数据库中，提高系统的总体可用性（分库，鸡蛋不能放在同一个篮子里），提高数据库并发能力，在不同服务器上，可横向扩容。

3.4 配置、硬件优化

操作系统配置的优化：增加TCP支持的队列数
MySQL配置文件优化：InnoDB缓存池设置（innodb_buffer_pool_size，推荐总内存的75%）和缓存池的个数（innodb_buffer_pool_instances）
CPU：核心数多并且主频高的
内存：增大内存
磁盘配置和选择：磁盘性能

4. 更多问题

4.1 什么是存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。存储过程具有以下特点：

存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率；
当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码；
减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小；
通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全。

4.2 drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别：

Delete用来删除表的全部或者一部分数据行，执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除， delete命令会触发这个表上所有的delete触发器；
Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器，TRUNCATE比delete更快，占用的空间更小；
Drop命令从数据库中删除表，所有的数据行，索引和权限也会被删除，所有的DML触发器也不会被触发，这个命令也不能回滚。

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

4.3 悲观锁与乐观锁

悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中一个非常基础的概念。

悲观锁：

悲观锁的特点是先获取锁，再进行业务操作，即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁。当数据库执行select … for update时会获取被select中的数据行的行锁，因此其他并发执行的select … for update如果试图选中同一行则会发生排斥（需要等待行锁被释放），因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用。

这里需要特别注意的是，不同的数据库对select… for update的实现和支持都是有所区别的，例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，MySQL就没有no wait这个选项。另外，MySQL还有个问题是: select… for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此，如果在MySQL中用悲观锁务必要确定使用了索引，而不是全表扫描。

乐观锁：

乐观锁的特点是先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过，若未被更新过，则更新成功；否则，失败重试。乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。

乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改。如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。

应用场景：

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

4.4 JDBC 对事务的支持

对于JDBC而言，每条单独的语句都是一个事务，即每个语句后都隐含一个commit。实际上，Connection 提供了一个auto-commit的属性来指定事务何时结束。当auto-commit为true时，当每个独立SQL操作的执行完毕，事务立即自动提交，也就是说，每个SQL操作都是一个事务；当auto-commit为false时，每个事务都必须显式调用commit方法进行提交，或者显式调用rollback方法进行回滚。auto-commit默认为true。

为了能够将多条SQL当成一个事务执行，必须首先通过Connection关闭auto-commit模式，然后通过Connection的setTransactionIsolation()方法设置事务的隔离级别，最后分别通过Connection的commit()方法和rollback()方法来提交事务和回滚事务。

4.5 一条SQL慢的原因

偶尔很慢：

数据库在刷新脏页（flush）

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。

刷脏页有下面4种场景（后两种不用太关注“性能”问题）：

**redo log写满了：**redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
**内存不够用了：**如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
**MySQL 认为系统“空闲”的时候：**这时系统没什么压力。
**MySQL 正常关闭的时候：**这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

拿不到锁

这个就比较容易想到了，我们要执行的这条语句，刚好这条语句涉及到的表，别人在用并且加锁了，我们拿不到锁，只能慢慢等待别人释放锁了。或者，表没有加锁但要使用到的某个一行被加锁了。如果要判断是否真的在等待锁，可以用show processlist这个命令来查看当前的状态。

一直很慢：

没用到索引
1. 没有索引，只能全表扫描。
2. 左边涉及运算或函数，没用上索引。
选错了索引

如：

select * from t where 100 < c and c < 100000;

主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而是存放主键字段的值。

系统在执行这条语句的时候，会进行预测：究竟是走 c 索引扫描的行数少，还是直接扫描全表扫描的行数少呢？显然，扫描行数越少当然越好了，因为扫描行数越少，意味着I/O操作的次数越少。

如果是扫描全表的话，那么扫描的次数就是这个表的总行数了，假设为 n；而如果走索引 c 的话，我们通过索引 c 找到主键之后，还得再通过主键索引来找我们整行的数据，也就是说，需要走两次索引。

预测：

系统是通过索引的区分度来判断的，一个索引上不同的值越多，意味着出现相同数值的索引越少，意味着索引的区分度越高。我们也把区分度称之为基数，即区分度越高，基数越大。所以呢，基数越大，意味着符合 100 < c and c < 10000 这个条件的行数越少。所以一个索引的基数越大，意味着走索引查询越有优势。
索引系统是通过遍历部分数据，也就是通过采样的方式，来预测索引的基数的。

所以，由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这便是导致 SQL 语句执行的很慢的原因。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-17FxJwtf-1602775881674)(C:\Users\vocyd\AppData\Roaming\Typora\typora-user-images\image-20200909144408232.png)]

5. NoSQL：Redis

Redis是一款基于内存的且支持持久化、高性能的Key-Value NoSQL 数据库，其支持丰富数据类型（string，list，set，sorted set，hash），常被用作缓存的解决方案。Redis具有以下显著特点：

速度快，因为数据存在内存中，类似于HashMap，HashMap的优势就是查找和操作的时间复杂度都是O(1)；
支持丰富数据类型，支持string，list，set，sorted set，hash；
支持事务，模型是单线程，操作都是原子性，所谓的原子性就是对数据的更改要么全部执行，要么全部不执行；
丰富的特性：可用于缓存，消息，按key设置过期时间，过期后将会自动删除。

Redis的所有数据都是保存到内存中的。

RDB：Redis DataBase，快照形式，定期把内存中当前时刻的数据保存到磁盘。Redis默认支持的持久化方案。

AOF形式：Append Only File。把所有对Redis数据库操作的命令，增删改操作的命令。保存到AOF文件中。数据库恢复时把所有的命令执行一遍即可。

lamlados

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
面试集锦（六）数据库

数据库https://blog.csdn.net/justloveyou_/article/details/78308460https://www.jianshu.com/p/400b707c45091.MySQL1.1 基本架构[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ytHgIMcb-1602775881661)(C:\Users\vocyd\AppData\Roaming\Typora\typora-user-images\image-2020090815
复制链接

扫一扫