精华内容
下载资源
问答
  • mysql面试题

    2019-03-03 12:52:30
    mysql面试题,经典sql语句.
  • 史上最详细的一线大厂Mysql面试题详解,超级详细,供大家学习!!
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    MySQL 面试题 MySQL 涉及的内容非常非常非常多,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心: 重点的题目添加了【重点】前缀。 索引。 锁。 事务和隔离级别。 因为 MySQL 还会有部分内容和...

    MySQL 面试题

    MySQL 涉及的内容非常非常非常多,所以面试题也容易写的杂乱。当年,我们记着几个一定要掌握的重心:

    重点的题目添加了【重点】前缀。

    1. 索引。
    2. 锁。
    3. 事务和隔离级别。

    因为 MySQL 还会有部分内容和运维相关度比较高,所以本文我们分成两部分【开发】【运维】两部分。

    • 对于【开发】部分,我们需要掌握。
    • 对于【运维】部分,更多考验开发的知识储备情况,当然能回答出来是比较好的,特别是对于高级开发工程师、架构师等。

    开发

    为什么互联网公司一般选择 MySQL 而不是 Oracle?

    免费、流行、够用。

    ? 当然,这个回答要稍微润色下。不过一般,很少问这个问题了。

    数据库的三范式是什么?什么是反模式?

    艿艿:重点在于反模式的回答。实际开发中,不会严格遵守三范式。

    胖友直接看 《服务端指南 数据存储篇 | MySQL(07) 范式与反模式》

    MySQL 有哪些数据类型?

    MySQL 支持多种类型,大致可以分为三类:数值、日期/时间和字符串(字符)类型。具体可以看看 《MySQL 数据类型》 文档。

    • 正确的使用数据类型,对数据库的优化是非常重要的。

    ? MySQL 中 varchar 与 char 的区别?varchar(50) 中的 50 代表的涵义?

    • 1、varchar 与 char 的区别,char 是一种固定长度的类型,varchar 则是一种可变长度的类型。
    • 2、varchar(50) 中 50 的涵义最多存放 50 个字符。varchar(50) 和 (200) 存储 hello 所占空间一样,但后者在排序时会消耗更多内存,因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)。所以,实际场景下,选择合适的 varchar 长度还是有必要的。

    ? int(11) 中的 11 代表什么涵义?

    int(11) 中的 11 ,不影响字段存储的范围,只影响展示效果。具体可以看看 《MySQL 中 int 长度的意义》 文章。

    ? 金额(金钱)相关的数据,选择什么数据类型?

    • 方式一,使用 int 或者 bigint 类型。如果需要存储到分的维度,需要 *100 进行放大。
    • 方式二,使用 decimal 类型,避免精度丢失。如果使用 Java 语言时,需要使用 BigDecimal 进行对应。

    ? 一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 MySQL 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15?

    • 一般情况下,我们创建的表的类型是 InnoDB ,如果新增一条记录(不重启 MySQL 的情况下),这条记录的 ID 是18 ;但是如果重启 MySQL 的话,这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中,所以重启数据库或者对表 OPTIMIZE 操作,都会使最大 ID 丢失。
    • 但是,如果我们使用表的类型是 MyISAM ,那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面,重启 MYSQL 后,自增主键的最大 ID 也不会丢失。

    最后,还可以跟面试官装个 x ,生产数据,不建议进行物理删除记录。

    ? 表中有大字段 X(例如:text 类型),且字段 X 不会经常更新,以读为为主,请问您是选择拆成子表,还是继续放一起?写出您这样选择的理由

    • 拆带来的问题:连接消耗 + 存储拆分空间。

      如果能容忍拆分带来的空间问题,拆的话最好和经常要查询的表的主键在物理结构上放置在一起(分区) 顺序 IO ,减少连接消耗,最后这是一个文本列再加上一个全文索引来尽量抵消连接消耗。

    • 不拆可能带来的问题:查询性能。

      如果能容忍不拆分带来的查询性能损失的话,上面的方案在某个极致条件下肯定会出现问题,那么不拆就是最好的选择。

    实际场景下,例如说商品表数据量比较大的情况下,会将商品描述单独存储到一个表中。即,使用拆的方案。

    MySQL 有哪些存储引擎?

    MySQL 提供了多种的存储引擎:

    • InnoDB
    • MyISAM
    • MRG_MYISAM
    • MEMORY
    • CSV
    • ARCHIVE
    • BLACKHOLE
    • PERFORMANCE_SCHEMA
    • FEDERATED

    具体每种存储引擎的介绍,可以看看 《数据库存储引擎》

    ? 如何选择合适的存储引擎?

    提供几个选择标准,然后按照标准,选择对应的存储引擎即可,也可以根据 常用引擎对比 来选择你使用的存储引擎。使用哪种引擎需要根据需求灵活选择,一个数据库中多个表可以使用不同的引擎以满足各种性能和实际需求。使用合适的存储引擎,将会提高整个数据库的性能。

    1. 是否需要支持事务。

    2. 对索引和缓存的支持。

    3. 是否需要使用热备。

    4. 崩溃恢复,能否接受崩溃。

    5. 存储的限制。

    6. 是否需要外键支持。

      艿艿:目前开发已经不考虑外键,主要原因是性能。具体可以看看 《从 MySQL 物理外键开始的思考》 文章。

    目前,MySQL 默认的存储引擎是 InnoDB ,并且也是最主流的选择。主要原因如下:

    • 【最重要】支持事务。
    • 支持行级锁和表级锁,能支持更多的并发量。
    • 查询不加锁,完全不影响查询。
    • 支持崩溃后恢复。

    在 MySQL5.1 以及之前的版本,默认的存储引擎是 MyISAM ,但是目前已经不再更新,且它有几个比较关键的缺点:

    • 不支持事务。
    • 使用表级锁,如果数据量大,一个插入操作锁定表后,其他请求都将阻塞。

    艿艿:也就是说,我们不需要花太多力气在 MyISAM 的学习上。

    ? 请说明 InnoDB 和 MyISAM 的区别

    InnoDBMyISAM
    事务支持不支持
    存储限制64TB
    锁粒度行锁表锁
    崩溃后的恢复支持不支持
    外键支持不支持
    全文检索5.7 版本后支持支持

    更完整的对比,可以看看 《数据库存储引擎》「常用引擎对比」 小节。

    ? 请说说 InnoDB 的 4 大特性?

    艿艿:貌似我面试没被问过…反正,我是没弄懂过~~

    • 插入缓冲(insert buffer)
    • 二次写(double write)
    • 自适应哈希索引(ahi)
    • 预读(read ahead)

    ? 为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢?

    对于 SELECT COUNT(*) FROM table 语句,在没有 WHERE 条件的情况下,InnoDB 比 MyISAM 可能会慢很多,尤其在大表的情况下。因为,InnoDB 是去实时统计结果,会全表扫描;而 MyISAM 内部维持了一个计数器,预存了结果,所以直接返回即可。

    详细的原因,胖友可以看看 《高性能 MySQL 之 Count 统计查询》 博客。

    ? 各种不同 MySQL 版本的 Innodb 的改进?

    艿艿:这是一个选择了解的问题。

    MySQL5.6 下 Innodb 引擎的主要改进:

    1. online DDL
    2. memcached NoSQL 接口
    3. transportable tablespace( alter table discard/import tablespace)
    4. MySQL 正常关闭时,可以 dump 出 buffer pool 的( space, page_no),重启时 reload,加快预热速度
    5. 索引和表的统计信息持久化到 mysql.innodb_table_stats 和 mysql.innodb_index_stats,可提供稳定的执行计划
    6. Compressed row format 支持压缩表

    MySQL5.7 下 Innodb 引擎的主要改进:

    • 1、修改 varchar 字段长度有时可以使用

      这里的“有时”,指的是也有些限制。可见 《MySQL 5.7 online ddl 的一些改进》

    • 2、Buffer pool 支持在线改变大小

    • 3、Buffer pool 支持导出部分比例

    • 4、支持新建 innodb tablespace,并可以在其中创建多张表

    • 5、磁盘临时表采用 innodb 存储,并且存储在 innodb temp tablespace 里面,以前是 MyISAM 存储

    • 6、透明表空间压缩功能

    重点】什么是索引?

    索引,类似于书籍的目录,想找到一本书的某个特定的主题,需要先找到书的目录,定位对应的页码。

    MySQL 中存储引擎使用类似的方式进行查询,先去索引中查找对应的值,然后根据匹配的索引找到对应的数据行。

    ? 索引有什么好处?

    1. 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
    2. 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则正好降低了排序的成本。

    ? 索引有什么坏处?

    1. 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。
    2. 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

    ? 索引的使用场景?

    • 1、对非常小的表,大部分情况下全表扫描效率更高。

    • 2、对中大型表,索引非常有效。

    • 3、特大型的表,建立和使用索引的代价随着增长,可以使用分区技术来解决。

      实际场景下,MySQL 分区表很少使用,原因可以看看 《互联网公司为啥不使用 MySQL 分区表?》 文章。

      对于特大型的表,更常用的是“分库分表”,目前解决方案有 Sharding Sphere、MyCAT 等等。

    ? 索引的类型?

    索引,都是实现在存储引擎层的。主要有六种类型:

    • 1、普通索引:最基本的索引,没有任何约束。

    • 2、唯一索引:与普通索引类似,但具有唯一性约束。

    • 3、主键索引:特殊的唯一索引,不允许有空值。

    • 4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。

    • 5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。

    • 6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎。

      常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

    具体的使用,可以看看 《服务端指南 数据存储篇 | MySQL(03) 如何设计索引》

    ? MySQL 索引的“创建”原则?

    注意,是“创建”噢。

    • 1、最适合索引的列是出现在 WHERE 子句中的列,或连接子句中的列,而不是出现在 SELECT 关键字后的列。

    • 2、索引列的基数越大,索引效果越好。

      具体为什么,可以看看如下两篇文章:

    • 3、根据情况创建复合索引,复合索引可以提高查询效率。

      因为复合索引的基数会更大。

    • 4、避免创建过多的索引,索引会额外占用磁盘空间,降低写操作效率。

    • 5、主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用提高查询效率。

    • 6、对字符串进行索引,应该定制一个前缀长度,可以节省大量的索引空间。

    ? MySQL 索引的“使用”注意事项?

    注意,是“使用”噢。

    • 1、应尽量避免在 WHERE 子句中使用 !=<> 操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

      注意,column IS NULL 也是不可以使用索引的。

    • 2、应尽量避免在 WHERE 子句中使用 OR 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:SELECT id FROM t WHERE num = 10 OR num = 20

    • 3、应尽量避免在 WHERE 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 4、应尽量避免在 WHERE 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。

    • 5、不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    • 6、复合索引遵循前缀原则。

    • 7、如果 MySQL 评估使用索引比全表扫描更慢,会放弃使用索引。如果此时想要索引,可以在语句中添加强制索引。

    • 8、列类型是字符串类型,查询时一定要给值加引号,否则索引失效。

    • 9、LIKE 查询,% 不能在前,因为无法使用索引。如果需要模糊匹配,可以使用全文索引。

    关于这块,可以看看 《服务端指南 数据存储篇 | MySQL(04) 索引使用的注意事项》 文章,写的更加细致。

    ? 以下三条 SQL 如何建索引,只建一条怎么建?

    WHERE a = 1 AND b = 1
    WHERE b = 1
    WHERE b = 1 ORDER BY time DESC
    
    
    • 以顺序 b , a, time 建立复合索引,CREATE INDEX table1_b_a_time ON index_test01(b, a, time)
    • 对于第一条 SQL ,因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序,以匹配复合索引顺序。

    ? 想知道一个查询用到了哪个索引,如何查看?

    EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

    使用方法,在 SELECT 语句前加上 EXPLAIN 就可以了。 《MySQL explain 执行计划详细解释》

    【重点】MySQL 索引的原理?

    解释 MySQL 索引的原理,篇幅会比较长,并且网络上已经有靠谱的资料可以看,所以艿艿这里整理了几篇,胖友可以对照着看。

    下面,艿艿对关键知识做下整理,方便胖友回顾。

    几篇好一点的文章:

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

    《MySQL 索引原理》

    《深入理解 MySQL 索引原理和实现 —— 为什么索引可以加速查询?》

    MySQL 有哪些索引方法?

    在 MySQL 中,我们可以看到两种索引方式:

    什么是 B-Tree 索引?

    B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

    • 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么。
    • InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为 16 KB,可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K ,在 MySQL 中可通过如下命令查看页的大小:
    mysql> show variables like 'innodb_page_size';
    
    • 而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB 。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘 I/O 次数,提高查询效率。

    B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree,首先定义一条记录为一个二元组 [key, data] ,key 为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。

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

    1. 每个节点最多有 m 个孩子。
      • 除了根节点和叶子节点外,其它每个节点至少有 Ceil(m/2) 个孩子。
      • 若根节点不是叶子节点,则至少有 2 个孩子。
    2. 所有叶子节点都在同一层,且不包含其它关键字信息。
    3. 每个非叶子节点包含 n 个关键字信息(P0,P1,…Pn, k1,…kn)
      • 关键字的个数 n 满足:ceil(m/2)-1 <= n <= m-1
      • ki(i=1,…n) 为关键字,且关键字升序排序。
      • Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ,但都大于 k(i-1) 。

    B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:

    B-Tree 的结构

    • 每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的 key 和三个指向子树根节点的 point ,point 存储的是子节点所在磁盘块的地址。两个 key 划分成的三个范围域,对应三个 point 指向的子树的数据的范围域。
    • 以根节点为例,key 为 17 和 35 ,P1 指针指向的子树的数据范围为小于 17 ,P2 指针指向的子树的数据范围为 [17~35] ,P3 指针指向的子树的数据范围为大于 35 。

    模拟查找 key 为 29 的过程:

    • 1、根据根节点找到磁盘块 1 ,读入内存。【磁盘I/O操作第1次】
    • 2、比较 key 29 在区间(17,35),找到磁盘块 1 的指针 P2 。
    • 3、根据 P2 指针找到磁盘块 3 ,读入内存。【磁盘I/O操作第2次】
    • 4、比较 key 29 在区间(26,30),找到磁盘块3的指针P2。
    • 5、根据 P2 指针找到磁盘块 8 ,读入内存。【磁盘I/O操作第3次】
    • 6、在磁盘块 8 中的 key 列表中找到 eky 29 。

    分析上面过程,发现需要 3 次磁盘 I/O 操作,和 3 次内存查找操作。由于内存中的 key 是一个有序表结构,可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数,使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率。

    什么是 B+Tree 索引?

    B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用 B+Tree 实现其索引结构。

    从上一节中的 B-Tree 结构图中可以看到,每个节点中不仅包含数据的 key 值,还有 data 值。而每一个页的存储空间是有限的,如果 data 数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小,当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率。在 B+Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B+Tree 的高度。

    B+Tree 相对于 B-Tree 有几点不同:

    • 非叶子节点只存储键值信息。
    • 所有叶子节点之间都有一个链指针。
    • 数据记录都存放在叶子节点中。

    将上一节中的 B-Tree 优化,由于 B+Tree 的非叶子节点只存储键值信息,假设每个磁盘块能存储 4 个键值及指针信息,则变成 B+Tree 后其结构如下图所示:

    B+Tree 的结构

    • 通常在 B+Tree 上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有 22 条数据记录,看不出 B+Tree 的优点,下面做一个推算:

    • InnoDB 存储引擎中页的大小为 16KB,一般表的主键类型为 INT(占用4个字节) 或 BIGINT(占用8个字节),指针类型也一般为 4 或 8 个字节,也就是说一个页(B+Tree 中的一个节点)中大概存储 16KB/(8B+8B)=1K 个键值(因为是估值,为方便计算,这里的 K 取值为〖10〗^3)。也就是说一个深度为 3 的 B+Tree 索引可以维护10^3 *10^3 *10^3 = 10亿 条记录。
    • 实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree 的高度一般都在 2~4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

    B+Tree 有哪些索引类型?

    在 B+Tree 中,根据叶子节点的内容,索引类型分为主键索引非主键索引

    • 主键索引的叶子节点存的数据是整行数据( 即具体数据 )。在 InnoDB 里,主键索引也被称为聚集索引(clustered index)。
    • 非主键索引的叶子节点存的数据是整行数据的主键,键值是索引。在 InnoDB 里,非主键索引也被称为辅助索引(secondary index)。

    辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,需要进过两步:

    • 首先,InnoDB 存储引擎会遍历辅助索引找到主键。
    • 然后,再通过主键在聚集索引中找到完整的行记录数据。

    另外,InnoDB 通过主键聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个主键作为聚簇索引。

    再另外,可能有胖友有和艿艿的一样疑惑,在辅助索引如果相同的索引怎么存储?最终存储到 B+Tree 非子节点中时,它们对应的主键 ID 是不同的,所以妥妥的。如下图所示:

    相同的索引怎么存储

    聚簇索引的注意点有哪些?

    聚簇索引表最大限度地提高了 I/O 密集型应用的性能,但它也有以下几个限制:

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    什么是索引的最左匹配特性?

    当 B+Tree 的数据项是复合的数据结构,比如索引 (name, age, sex) 的时候,B+Tree 是按照从左到右的顺序来建立搜索树的。

    • 比如当 (张三, 20, F) 这样的数据来检索的时候,B+Tree 会优先比较 name 来确定下一步的所搜方向,如果 name 相同再依次比较 age 和 sex ,最后得到检索的数据。
    • 但当 (20, F) 这样的没有 name 的数据来的时候,B+Tree 就不知道下一步该查哪个节点,因为建立搜索树的时候 name 就是第一个比较因子,必须要先根据 name 来搜索才能知道下一步去哪里查询。
    • 比如当 (张三, F) 这样的数据来检索时,B+Tree 可以用 name 来指定搜索方向,但下一个字段 age 的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是 F 的数据了。

    这个是非常重要的性质,即索引的最左匹配特性。

    MyISAM 索引实现?

    MyISAM 索引的实现,和 InnoDB 索引的实现是一样使用 B+Tree ,差别在于 MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

    MyISAM 索引与 InnoDB 索引的区别?

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

    【重点】请说说 MySQL 的四种事务隔离级别?

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    • 1、插入速度严重依赖于插入顺序,按照主键的顺序插入是最快的方式,否则将会出现页分裂,严重影响性能。因此,对于 InnoDB 表,我们一般都会定义一个自增的 ID 列为主键。

      关于这一点,可能面试官会换一个问法。例如,为什么主键需要是自增 ID ,又或者为什么主键需要带有时间性关联。

    • 2、更新主键的代价很高,因为将会导致被更新的行移动。因此,对于InnoDB 表,我们一般定义主键为不可更新。

      MySQL 默认情况下,主键是允许更新的。对于 MongoDB ,其 主键是不允许更新的。

    • 3、二级索引访问需要两次索引查找,第一次找到主键值,第二次根据主键值找到行数据。

      当然,有一种情况可以无需二次查找,基于非主键索引查询,但是查询字段只有主键 ID ,那么在二级索引中就可以查找到。

    • 4、主键 ID 建议使用整型。因为,每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ,每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

    事务就是对一系列的数据库操作(比如插入多条数据)进行统一的提交或回滚操作,如果插入成功,那么一起成功,如果中间有一条出现异常,那么回滚之前的所有操作。

    这样可以防止出现脏数据,防止数据库数据出现问题。

    事务的特性指的是?

    指的是 ACID ,如下图所示:

    事务的特性

    1. 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
    2. 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束触发器级联回滚等。
    3. 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
    4. 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

    事务的并发问题?

    实际场景下,事务并不是串行的,所以会带来如下三个问题:

    • 1、脏读:事务 A 读取了事务 B 更新的数据,然后 B 回滚操作,那么 A 读取到的数据是脏数据。
    • 2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务 A 多次读取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致。
    • 3、幻读:系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级,但是系统管理员 B 就在这个时候插入了一条具体分数的记录,当系统管理员 A 改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

    小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

    MySQL 事务隔离级别会产生的并发问题?

    • READ UNCOMMITTED(未提交读):事务中的修改,即使没有提交,对其他事务也都是可见的。

      会导致脏读。

    • READ COMMITTED(提交读):事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

      会导致不可重复读。

      这个隔离级别,也可以叫做“不可重复读”。

    • REPEATABLE READ(可重复读):一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

      会导致幻读。

    • SERIALIZABLE(可串行化):强制事务串行执行。

    事务隔离级别脏读不可重复读幻读
    读未提交(read-uncommitted)
    读已提交(read-committed)
    可重复读(repeatable-read)是(x)
    串行化(serializable)
    • MySQL 默认的事务隔离级别为可重复读(repeatable-read) 。
    • 上图的 <X> 处,MySQL 因为其间隙锁的特性,导致其在可重复读(repeatable-read)的隔离级别下,不存在幻读问题。也就是说,上图 <X> 处,需要改成“否”!!!!
    • ? 记住这个表的方式,我们会发现它是自左上向右下是一个对角线。当然,最好是去理解。
    • 具体的实验,胖友可以看看 《MySQL 的四种事务隔离级别》

    【重点】请说说 MySQL 的锁机制?

    表锁是日常开发中的常见问题,因此也是面试当中最常见的考察点,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题。MySQL 的共享锁和排他锁,就是读锁和写锁。

    • 共享锁:不堵塞,多个用户可以同时读一个资源,互不干扰。
    • 排他锁:一个写锁会阻塞其他的读锁和写锁,这样可以只允许一个用户进行写入,防止其他用户读取正在写入的资源。

    ? 锁的粒度?

    • 表锁:系统开销最小,会锁定整张表,MyIsam 使用表锁。
    • 行锁:最大程度的支持并发处理,但是也带来了最大的锁开销,InnoDB 使用行锁。

    ? 什么是悲观锁?什么是乐观锁?

    1)悲观锁

    它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

    在悲观锁的情况下,为了保证事务的隔离性,就需要一致性锁定读。读取数据时给加锁,其它事务无法修改这些数据。修改删除数据时也要加锁,其它事务无法读取这些数据。

    2)乐观锁

    相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。

    而乐观锁机制在一定程度上解决了这个问题。乐观锁,大多是基于数据版本( Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

    什么是死锁?

    多数情况下,可以认为如果一个资源被锁定,它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时,其中每个进程拥有的锁都是其他进程所需的,由此造成每个进程都无法继续下去。简单的说,进程 A 等待进程 B 释放他的资源,B 又等待 A 释放他的资源,这样就互相等待就形成死锁。

    虽然进程在运行过程中,可能发生死锁,但死锁的发生也必须具备一定的条件,死锁的发生必须具备以下四个必要条件:

    • 互斥条件:指进程对所分配到的资源进行排它性使用,即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源,则请求者只能等待,直至占有资源的进程用毕释放。
    • 请求和保持条件:指进程已经保持至少一个资源,但又提出了新的资源请求,而该资源已被其它进程占有,此时请求进程阻塞,但又对自己已获得的其它资源保持不放。
    • 不剥夺条件:指进程已获得的资源,在未使用完之前,不能被剥夺,只能在使用完时由自己释放。
    • 环路等待条件:指在发生死锁时,必然存在一个进程——资源的环形链,即进程集合 {P0,P1,P2,•••,Pn} 中的 P0 正在等待一个 P1 占用的资源;P1 正在等待 P2 占用的资源,……,Pn 正在等待已被 P0 占用的资源。

    下列方法有助于最大限度地降低死锁:

    • 设置获得锁的超时时间。

      通过超时,至少保证最差最差最差情况下,可以有退出的口子。

    • 按同一顺序访问对象。

      这个是最重要的方式。

    • 避免事务中的用户交互。

    • 保持事务简短并在一个批处理中。

    • 使用低隔离级别。

    • 使用绑定连接。

    ? MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的??

    InnoDB 是基于索引来完成行锁。例如:SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE

    • FOR UPDATE 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么 InnoDB 将完成表锁,并发将无从谈起。

    【重要】MySQL 查询执行顺序?

    MySQL 查询执行的顺序是:

    (1)     SELECT
    (2)     DISTINCT <select_list>
    (3)     FROM <left_table>
    (4)     <join_type> JOIN <right_table>
    (5)     ON <join_condition>
    (6)     WHERE <where_condition>
    (7)     GROUP BY <group_by_list>
    (8)     HAVING <having_condition>
    (9)     ORDER BY <order_by_condition>
    (10)    LIMIT <limit_number>
    

    具体的,可以看看 《SQL 查询之执行顺序解析》 文章。

    【重要】聊聊 MySQL SQL 优化?

    可以看看如下几篇文章:

    另外,除了从 SQL 层面进行优化,也可以从服务器硬件层面,进一步优化 MySQL 。具体可以看看 《MySQL 数据库性能优化之硬件优化》

    编写 SQL 查询语句的考题合集

    MySQL 数据库 CPU 飙升到 500% 的话,怎么处理?

    当 CPU 飙升到 500% 时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。

    如果此时是 IO 压力比较大,可以使用 iostat 命令,定位是哪个进程占用了磁盘 IO 。

    如果是 mysqld 造成的,使用 show processlist 命令,看看里面跑的 Session 情况,是不是有消耗资源的 SQL 在运行。找出消耗高的 SQL ,看看执行计划是否准确, index 是否缺失,或者实在是数据量太大造成。一般来说,肯定要 kill 掉这些线程(同时观察 CPU 使用率是否下降),等进行相应的调整(比如说加索引、改 SQL 、改内存参数)之后,再重新跑这些 SQL。

    也可以查看 MySQL 慢查询日志,看是否有慢 SQL 。

    也有可能是每个 SQL 消耗资源并不多,但是突然之间,有大量的 Session 连进来导致 CPU 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等。

    ? 在 MySQL 服务器运行缓慢的情况下输入什么命令能缓解服务器压力?

    1)检查系统的状态

    通过操作系统的一些工具检查系统的状态,比如 CPU、内存、交换、磁盘的利用率,根据经验或与系统正常时的状态相比对,有时系统表面上看起来看空闲,这也可能不是一个正常的状态,因为 CPU 可能正等待IO的完成。除此之外,还应观注那些占用系统资源(CPU、内存)的进程。

    • 使用 sar 来检查操作系统是否存在 IO 问题。
    • 使用 vmstat 监控内存 CPU 资源。
    • 磁盘 IO 问题,处理方式:做 raid10 提高性能 。
    • 网络问题,telnet 一下 MySQL 对外开放的端口。如果不通的话,看看防火墙是否正确设置了。另外,看看 MySQ L是不是开启了 skip-networking 的选项,如果开启请关闭。

    2)检查 MySQL 参数

    • max_connect_errors
    • connect_timeout
    • skip-name-resolve
    • slave-net-timeout=seconds
    • master-connect-retry

    3)检查 MySQL 相关状态值

    • 关注连接数
    • 关注下系统锁情况
    • 关注慢查询(slow query)日志

    Innodb 的事务与日志的实现方式

    ? 有多少种日志?

    • redo 日志
    • undo 日志

    ? 日志的存放形式?

    • redo:在页修改的时候,先写到 redo log buffer 里面, 然后写到 redo log 的文件系统缓存里面(fwrite),然后再同步到磁盘文件(fsync)。
    • undo:在 MySQL5.5 之前,undo 只能存放在 ibdata* 文件里面, 5.6 之后,可以通过设置 innodb_undo_tablespaces 参数把 undo log 存放在 ibdata* 之外。

    ? 事务是如何通过日志来实现的,说得越深入越好

    艿艿:这个流程的理解还是比较简单的,实际思考实现感觉还是蛮复杂的。

    基本流程如下:

    • 因为事务在修改页时,要先记 undo ,在记 undo 之前要记 undo 的 redo, 然后修改数据页,再记数据页修改的 redo。 redo(里面包括 undo 的修改)一定要比数据页先持久化到磁盘。
    • 当事务需要回滚时,因为有 undo,可以把数据页回滚到前镜像的状态。
    • 崩溃恢复时,如果 redo log 中事务没有对应的 commit 记录,那么需要用 undo 把该事务的修改回滚到事务开始之前。如果有 commit 记录,就用 redo 前滚到该事务完成时并提交掉。

    MySQL binlog 的几种日志录入格式以及区别

    ? 各种日志格式的涵义

    binlog 有三种格式类型,分别如下:

    1)Statement

    每一条会修改数据的 SQL 都会记录在 binlog 中。

    • 优点:不需要记录每一行的变化,减少了 binlog 日志量,节约了 IO,提高性能。(相比 row 能节约多少性能与日志量,这个取决于应用的 SQL 情况,正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量,但是考虑到如果带条件的 update 操作,以及整表删除,alter 表等操作,ROW 格式会产生大量日志,因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的 IO 性能问题。)

    • 缺点:由于记录的只是执行语句,为了这些语句能在 slave 上正确运行,因此还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果。另外 MySQL 的复制,像一些特定函数功能,slave 可与 master 上要保持一致会有很多相关问题(如 sleep() 函数,last_insert_id(),以及 user-defined functions(udf) 会出现问题)。

    • 使用以下函数的语句也无法被复制:

      • LOAD_FILE()

      • UUID()

      • USER()

      • FOUND_ROWS()

      • SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)

        同时在 INSERT …SELECT 会产生比 RBR 更多的行级锁 。

    2)Row

    不记录 SQL 语句上下文相关信息,仅保存哪条记录被修改。

    • 优点:binlog 中可以不记录执行的 SQL 语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下的存储过程,或 function ,以及 trigger 的调用和触发无法被正确复制的问题。
    • 缺点:所有的执行的语句当记录到日志中的时候,都将以每行记录的修改来记录,这样可能会产生大量的日志内容,比如一条 Update 语句,修改多条记录,则 binlog 中每一条修改都会有记录,这样造成 binlog 日志量会很大,特别是当执行 alter table 之类的语句的时候,由于表结构修改,每条记录都发生改变,那么该表每一条记录都会记录到日志中。

    3)Mixedlevel

    是以上两种 level 的混合使用。

    • 一般的语句修改使用 Statement 格式保存 binlog 。
    • 如一些函数,statement 无法完成主从复制的操作,则采用 Row 格式保存 binlog 。

    MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式,也就是在 Statement 和 Row 之间选择 一种。

    新版本的 MySQL 中对 row level 模式也被做了优化,并不是所有的修改都会以 row level 来记录。

    • 像遇到表结构变更的时候就会以 Statement 模式来记录。
    • 至于 Update 或者 Delete 等修改数据的语句,还是会记录所有行的变更,即使用 Row 模式。

    ? 适用场景?

    在一条 SQL 操作了多行数据时, Statement 更节省空间,Row 更占用空间。但是, Row 模式更可靠。

    因为,互联网公司,使用 MySQL 的功能相对少,基本不使用存储过程、触发器、函数的功能,选择默认的语句模式,Statement Level(默认)即可。

    ? 结合第一个问题,每一种日志格式在复制中的优劣?

    • Statement 可能占用空间会相对小一些,传送到 slave 的时间可能也短,但是没有 Row 模式的可靠。
    • Row 模式在操作多行数据时更占用空间,但是可靠。

    所以,这是在占用空间和可靠之间的选择。

    如何在线正确清理 MySQL binlog?

    MySQL 中的 binlog 日志记录了数据中的数据变动,便于对数据的基于时间点和基于位置的恢复。但日志文件的大小会越来越大,占用大量的磁盘空间,因此需要定时清理一部分日志信息。

    # 首先查看主从库正在使用的binlog文件名称
    show master(slave) status
    
    # 删除之前一定要备份
    purge master logs before'2017-09-01 00:00:00'; # 删除指定时间前的日志
    purge master logs to'mysql-bin.000001'; # 删除指定的日志文件
    
    # 自动删除:通过设置binlog的过期时间让系统自动删除日志
    show variables like 'expire_logs_days'; # 查看过期时间
    set global expire_logs_days = 30; # 设置过期时间
    

    MySQL 主从复制的流程是怎么样的?

    MySQL 的主从复制是基于如下 3 个线程的交互(多线程复制里面应该是 4 类线程):

    • 1、Master 上面的 binlog dump 线程,该线程负责将 master 的 binlog event 传到 slave 。
    • 2、Slave 上面的 IO 线程,该线程负责接收 Master 传过来的 binlog,并写入 relay log 。
    • 3、Slave 上面的 SQL 线程,该线程负责读取 relay log 并执行。
    • 4、如果是多线程复制,无论是 5.6 库级别的假多线程还是 MariaDB 或者 5.7 的真正的多线程复制, SQL 线程只做 coordinator ,只负责把 relay log 中的 binlog 读出来然后交给 worker 线程, woker 线程负责具体 binlog event 的执行。

    ? MySQL 如何保证复制过程中数据一致性?

    • 1、在 MySQL5.5 以及之前, slave 的 SQL 线程执行的 relay log 的位置只能保存在文件( relay-log.info)里面,并且该文件默认每执行 10000 次事务做一次同步到磁盘, 这意味着 slave 意外 crash 重启时, SQL 线程执行到的位置和数据库的数据是不一致的,将导致复制报错,如果不重搭复制,则有可能会导致数据不一致。
      • MySQL 5.6 引入参数 relay_log_info_repository,将该参数设置为 TABLE 时, MySQL 将 SQL 线程执行到的位置存到 mysql.slave_relay_log_info 表,这样更新该表的位置和 SQL 线程执行的用户事务绑定成一个事务,这样 slave 意外宕机后,slave 通过 innodb 的崩溃恢复可以把 SQL 线程执行到的位置和用户事务恢复到一致性的状态。
    • 2、MySQL 5.6 引入 GTID 复制,每个 GTID 对应的事务在每个实例上面最多执行一次, 这极大地提高了复制的数据一致性。
    • 3、MySQL 5.5 引入半同步复制, 用户安装半同步复制插件并且开启参数后,设置超时时间,可保证在超时时间内如果 binlog 不传到 slave 上面,那么用户提交事务时不会返回,直到超时后切成异步复制,但是如果切成异步之前用户线程提交时在 master 上面等待的时候,事务已经提交,该事务对 master 上面的其他 session 是可见的,如果这时 master 宕机,那么到 slave 上面该事务又不可见了,该问题直到 5.7 才解决。
    • 4、MySQL 5.7 引入无损半同步复制,引入参 rpl_semi_sync_master_wait_point,该参数默认为 after_sync,指的是在切成半同步之前,事务不提交,而是接收到 slave 的 ACK 确认之后才提交该事务,从此,复制真正可以做到无损的了。
    • 5、可以再说一下 5.7 的无损复制情况下, master 意外宕机,重启后发现有 binlog 没传到 slave 上面,这部分 binlog 怎么办???分 2 种情况讨论, 1 宕机时已经切成异步了, 2 是宕机时还没切成异步??? 这个怎么判断宕机时有没有切成异步呢??? 分别怎么处理???

    ? MySQL 如何解决主从复制的延时性?

    5.5 是单线程复制,5.6 是多库复制(对于单库或者单表的并发操作是没用的),5.7 是真正意义的多线程复制,它的原理是基于 group commit, 只要 master 上面的事务是 group commit 的,那 slave 上面也可以通过多个 worker线程去并发执行。 和 MairaDB10.0.0.5 引入多线程复制的原理基本一样。

    ? 工作遇到的复制 bug 的解决方法?

    5.6 的多库复制有时候自己会停止,我们写了一个脚本重新 start slave 。

    ? 你是否做过主从一致性校验,如果有,怎么做的,如果没有,你打算怎么做?

    主从一致性校验有多种工具 例如 checksum、mysqldiff、pt-table-checksum 等。

    聊聊 MySQL 备份方式?备份策略是怎么样的?

    具体的,胖友可以看看 《MySQL 高级备份策略》 。主要有几个知识点:

    • 数据的备份类型

      • 【常用】完全备份

        这是大多数人常用的方式,它可以备份整个数据库,包含用户表、系统表、索引、视图和存储过程等所有数据库对象。但它需要花费更多的时间和空间,所以,一般推荐一周做一次完全备份。

      • 增量备份

        它是只备份数据库一部分的另一种方法,它不使用事务日志,相反,它使用整个数据库的一种新映象。它比最初的完全备份小,因为它只包含自上次完全备份以来所改变的数据库。它的优点是存储和恢复速度快。推荐每天做一次差异备份。

      • 【常用】事务日志备份

        事务日志是一个单独的文件,它记录数据库的改变,备份的时候只需要复制自上次备份以来对数据库所做的改变,所以只需要很少的时间。为了使数据库具有鲁棒性,推荐每小时甚至更频繁的备份事务日志。

      • 文件备份

        数据库可以由硬盘上的许多文件构成。如果这个数据库非常大,并且一个晚上也不能将它备份完,那么可以使用文件备份每晚备份数据库的一部分。由于一般情况下数据库不会大到必须使用多个文件存储,所以这种备份不是很常用。

    • 备份数据的类型

      • 热备份
      • 温备份
      • 冷备份
    • 备份工具

      • cp
      • mysqldump
      • xtrabackup
      • lvm2 快照

    MySQL 几种备份方式?

    MySQL 一般有 3 种备份方式。

    1)逻辑备份

    使用 MySQL 自带的 mysqldump 工具进行备份。备份成sql文件形式。

    • 优点:最大好处是能够与正在运行的 MySQL 自动协同工作,在运行期间可以确保备份是当时的点,它会自动将对应操作的表锁定,不允许其他用户修改(只能访问)。可能会阻止修改操作。SQL 文件通用方便移植。
    • 缺点:备份的速度比较慢。如果是数据量很多的时候,就很耗时间。如果数据库服务器处在提供给用户服务状态,在这段长时间操作过程中,意味着要锁定表(一般是读锁定,只能读不能写入数据),那么服务就会影响的。

    2)物理备份

    艿艿:因为现在主流是 InnoDB ,所以基本不再考虑这种方式。

    直接拷贝只适用于 MyISAM 类型的表。这种类型的表是与机器独立的。但实际情况是,你设计数据库的时候不可能全部使用 MyISAM 类型表。你也不可能因为 MyISAM 类型表与机器独立,方便移植,于是就选择这种表,这并不是选择它的理由。

    • 缺点:你不能去操作正在运行的 MySQL 服务器(在拷贝的过程中有用户通过应用程序访问更新数据,这样就无法备份当时的数据),可能无法移植到其他机器上去。

    3)双机热备份。

    当数据量太大的时候备份是一个很大的问题,MySQL 数据库提供了一种主从备份的机制,也就是双机热备。

    • 优点:适合数据量大的时候。现在明白了,大的互联网公司对于 MySQL 数据备份,都是采用热机备份。搭建多台数据库服务器,进行主从复制。

    数据库不能停机,请问如何备份? 如何进行全备份和增量备份?

    可以使用逻辑备份和双机热备份。

    • 完全备份:完整备份一般一段时间进行一次,且在网站访问量最小的时候,这样常借助批处理文件定时备份。主要是写一个批处理文件在里面写上处理程序的绝对路径然后把要处理的东西写在后面,即完全备份数据库。
    • 增量备份:对 ddl 和 dml 语句进行二进制备份。且 5.0 无法增量备份,5.1 后可以。如果要实现增量备份需要在 my.ini 文件中配置备份路径即可,重启 MySQL 服务器,增量备份就启动了。

    ? 你的备份工具的选择?备份计划是怎么样的?

    视库的大小来定,一般来说 100G 内的库,可以考虑使用 mysqldump 来做,因为 mysqldump 更加轻巧灵活,备份时间选在业务低峰期,可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小,压缩之后更小)。

    100G 以上的库,可以考虑用 xtrabackup 来做,备份速度明显要比 mysqldump 要快。一般是选择一周一个全备,其余每天进行增量备份,备份时间为业务低峰期。

    备份恢复时间是多长?

    物理备份恢复快,逻辑备份恢复慢。

    这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考:

    • 20G 的 2 分钟(mysqldump)
    • 80G 的 30分钟(mysqldump)
    • 111G 的 30分钟(mysqldump)
    • 288G 的 3 小时(xtrabackup)
    • 3T 的 4 小时(xtrabackup)

    逻辑导入时间一般是备份时间的 5 倍以上。

    备份恢复失败如何处理?

    首先在恢复之前就应该做足准备工作,避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错,再根据报错的提示来进行相应的调整。

    ? mysqldump 和 xtrabackup 实现原理?

    1)mysqldump

    mysqldump 是最简单的逻辑备份方式。

    • 在备份 MyISAM 表的时候,如果要得到一致的数据,就需要锁表,简单而粗暴。
    • 在备份 InnoDB 表的时候,加上 –master-data=1 –single-transaction 选项,在事务开始时刻,记录下 binlog pos 点,然后利用 MVCC 来获取一致的数据,由于是一个长事务,在写入和更新量很大的数据库上,将产生非常多的 undo ,显著影响性能,所以要慎用。
    • 优点:简单,可针对单表备份,在全量导出表结构的时候尤其有用。
    • 缺点:简单粗暴,单线程,备份慢而且恢复慢,跨 IDC 有可能遇到时区问题

    2)xtrabackup

    xtrabackup 实际上是物理备份+逻辑备份的组合。

    • 在备份 InnoDB 表的时候,它拷贝 ibd 文件,并一刻不停的监视 redo log 的变化,append 到自己的事务日志文件。在拷贝 ibd 文件过程中,ibd文件本身可能被写”花”,这都不是问题,因为在拷贝完成后的第一个 prepare 阶段,xtrabackup 采用类似于 Innodb 崩溃恢复的方法,把数据文件恢复到与日志文件一致的状态,并把未提交的事务回滚。
    • 如果同时需要备份 MyISAM 表以及 InnoDB 表结构等文件,那么就需要用 flush tables with lock 来获得全局锁,开始拷贝这些不再变化的文件,同时获得 binlog 位置,拷贝结束后释放锁,也停止对 redo log 的监视。

    如何从 mysqldump 产生的全库备份中只恢复某一个库、某一张表?

    具体可见 《MySQL 全库备份中恢复某个库和某张表以及 mysqldump 参数 –ignore-table 介绍》 文章。

    聊聊 MySQL 集群?

    艿艿:这块艿艿懂的少,主要找了一些网络上的资料。

    ? 对于简历中写有熟悉 MySQL 高可用方案?

    我一般先问他现在管理的数据库架构是什么,如果他只说出了主从,而没有说任何 HA 的方案,那么我就可以判断出他没有实际的 HA 经验。

    不过这时候也不能就是断定他不懂 MySQL 高可用,也许是没有实际机会去使用,那么我就要问 MMM 以及 MHA 以及 MM + keepalived 等的原理、实现方式以及它们之间的优势和不足了,一般这种情况下,能说出这个的基本没有。

    • MMM 那东西好像不靠谱,据说不稳定,但是有人在用的,和 mysql-router 比较像,都是指定可写的机器和只读机器。
    • MHA 的话一句话说不完,可以搜索下相关博客。

    聊聊 MySQL 安全?

    感兴趣的胖友,可以看看:

    MySQL 有哪些日志?

    • 错误日志:记录了当 mysqld 启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。

    • 二进制文件:记录了所有的 DDL(数据定义语言)语句和 DML(数据操纵语言)语句,不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。(定期删除日志,默认关闭)。

      就是我们上面看到的 MySQL binlog 日志。

    • 查询日志:记录了客户端的所有语句,格式为纯文本格式,可以直接进行读取。(log 日志中记录了所有数据库的操作,对于访问频繁的系统,此日志对系统性能的影响较大,建议关闭,默认关闭)。

    • 慢查询日志:慢查询日志记录了包含所有执行时间超过参数long_query_time(单位:秒)所设置值的 SQL 语句的日志。(纯文本格式)

      重要,一定要开启。

    另外,错误日志和慢查询日志的详细解释,可以看看 《MySQL 日志文件之错误日志和慢查询日志详解》 文章。

    聊聊 MySQL 监控?

    你是如何监控你们的数据库的?

    监控的工具有很多,例如 Zabbix ,Lepus ,我这里用的是 Lepus

    对一个大表做在线 DDL ,怎么进行实施的才能尽可能降低影响?

    使用 pt-online-schema-change ,具体可以看看 《MySQL 大表在线 DML 神器–pt-online-schema-change》 文章。

    另外,还有一些其它的工具,胖友可以搜索下。

    展开全文
  • MySQL面试题和答案

    2017-11-05 17:37:57
    MySQL 常见的面试题,应对面试,也可以总结知识点用。
  • MySQL面试题(含答案)
  • 强烈推荐MySQL面试题和答案(仅供参考)
  • # MySQL 面试题.md

    2020-08-04 22:29:15
    SQL指结构化查询语言,全称是 Structured Query Language。SQL 是用于访问和处理数据库的可以与数据库 交互的计算机语言。 SQL 是一种 ANSI(American National Standards Institute 美国国家标准化组织)标准 的...
  • MySQL面试题大全.pdf

    2020-04-15 09:52:53
    非常全面的MySQL基础知识,三万字、91道MySQL面试题。MySQL数据库cpu飙升到500%的话他怎么处理? 当 cpu 飙升到 500%时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,...
  • MySql面试题.doc

    2019-07-29 11:11:23
    MySql学习笔记以及面试题... 轻轻松松解决mysql的困惑... MySql学习的基本重点
  • mysql面试题及答案.docx

    2020-05-15 00:19:28
    mysql 面试题及答案 篇一 php 经典面试题及答案非常实用 目录 面试题 面试题 1 3 面试题 三 4 面试题 四 6 面试题 五 7 面试题 六 8 面试题 七 9 本面试题分为七套这些大部分都是本人被面试过的总结发出来 给大家...
  • 1、为什么要使用数据库? a.数据保存在内存 优点: 存取速度快 ...MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系
  • MySQL面试题_.pdf

    2020-12-02 16:12:35
    ACID是事物的四个特性。分别是 原⼦性 (Atomicity)、⼀致性(Consistency)、隔离性 (Isolation)、持久性(Durability) 。
  • MySQL数据库面试题(2020最新版)

    万次阅读 多人点赞 2020-03-10 17:20:40
    数据库三大范式是什么mysql有关权限的表都有哪几个MySQL的binlog有有几种录入格式?分别有什么区别?数据类型mysql有哪些数据类型引擎MySQL存储引擎MyISAM与InnoDB区别MyISAM索引与InnoDB索引的区别?InnoDB引擎的4...

    Java面试总结(2021优化版)已发布在个人微信公众号【技术人成长之路】,优化版首先修正了读者反馈的部分答案存在的错误,同时根据最新面试总结,删除了低频问题,添加了一些常见面试题,对文章进行了精简优化,欢迎大家关注!😊😊

    【技术人成长之路】,助力技术人成长!更多精彩文章第一时间在公众号发布哦!

    文章目录

    Java面试总结汇总,整理了包括Java基础知识,集合容器,并发编程,JVM,常用开源框架Spring,MyBatis,数据库,中间件等,包含了作为一个Java工程师在面试中需要用到或者可能用到的绝大部分知识。欢迎大家阅读,本人见识有限,写的博客难免有错误或者疏忽的地方,还望各位大佬指点,在此表示感激不尽。文章持续更新中…

    序号内容链接地址
    1Java基础知识面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104390612
    2Java集合容器面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104588551
    3Java异常面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104390689
    4并发编程面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104863992
    5JVM面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104390752
    6Spring面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104397516
    7Spring MVC面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104397427
    8Spring Boot面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104397299
    9Spring Cloud面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104397367
    10MyBatis面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/101292950
    11Redis面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/103522351
    12MySQL数据库面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104778621
    13消息中间件MQ与RabbitMQ面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104588612
    14Dubbo面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104390006
    15Linux面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104588679
    16Tomcat面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104397665
    17ZooKeeper面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104397719
    18Netty面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/104391081
    19架构设计&分布式&数据结构与算法面试题(2020最新版)https://thinkwon.blog.csdn.net/article/details/105870730

    数据库基础知识

    为什么要使用数据库

    数据保存在内存

    优点: 存取速度快

    缺点: 数据不能永久保存

    数据保存在文件

    优点: 数据永久保存

    缺点:1)速度比内存操作慢,频繁的IO操作。2)查询数据不方便

    数据保存在数据库

    1)数据永久保存

    2)使用SQL语句,查询方便效率高。

    3)管理数据方便

    什么是SQL?

    结构化查询语言(Structured Query Language)简称SQL,是一种数据库查询语言。

    作用:用于存取数据、查询、更新和管理关系数据库系统。

    什么是MySQL?

    MySQL是一个关系型数据库管理系统,由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一,在 WEB 应用方面,MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用,因为 MySQL 是开源免费的,并且方便扩展。

    数据库三大范式是什么

    第一范式:每个列都不可以再拆分。

    第二范式:在第一范式的基础上,非主键列完全依赖于主键,而不能是依赖于主键的一部分。

    第三范式:在第二范式的基础上,非主键列只依赖于主键,不依赖于其他非主键。

    在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

    mysql有关权限的表都有哪几个

    MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化。这些权限表分别user,db,table_priv,columns_priv和host。下面分别介绍一下这些表的结构和内容:

    • user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
    • db权限表:记录各个帐号在各个数据库上的操作权限。
    • table_priv权限表:记录数据表级的操作权限。
    • columns_priv权限表:记录数据列级的操作权限。
    • host权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

    MySQL的binlog有有几种录入格式?分别有什么区别?

    有三种格式,statement,row和mixed。

    • statement模式下,每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。由于sql的执行是有上下文的,因此在保存的时候需要保存相关的信息,同时还有一些使用了函数之类的语句无法被记录复制。
    • row级别下,不记录sql语句上下文相关信息,仅保存哪条记录被修改。记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作,会导致大量行的改动(比如alter table),因此这种模式的文件保存的信息太多,日志量太大。
    • mixed,一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。

    此外,新版的MySQL中对row级别也做了一些优化,当表结构发生变化的时候,会记录语句而不是逐行记录。

    数据类型

    mysql有哪些数据类型

    分类类型名称说明
    整数类型tinyInt很小的整数(8位二进制)
    smallint小的整数(16位二进制)
    mediumint中等大小的整数(24位二进制)
    int(integer)普通大小的整数(32位二进制)
    小数类型float单精度浮点数
    double双精度浮点数
    decimal(m,d)压缩严格的定点数
    日期类型yearYYYY 1901~2155
    timeHH:MM:SS -838:59:59~838:59:59
    dateYYYY-MM-DD 1000-01-01~9999-12-3
    datetimeYYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59
    timestampYYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC
    文本、二进制类型CHAR(M)M为0~255之间的整数
    VARCHAR(M)M为0~65535之间的整数
    TINYBLOB允许长度0~255字节
    BLOB允许长度0~65535字节
    MEDIUMBLOB允许长度0~167772150字节
    LONGBLOB允许长度0~4294967295字节
    TINYTEXT允许长度0~255字节
    TEXT允许长度0~65535字节
    MEDIUMTEXT允许长度0~167772150字节
    LONGTEXT允许长度0~4294967295字节
    VARBINARY(M)允许长度0~M个字节的变长字节字符串
    BINARY(M)允许长度0~M个字节的定长字节字符串
    • 1、整数类型,包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT,分别表示1字节、2字节、3字节、4字节、8字节整数。任何整数类型都可以加上UNSIGNED属性,表示数据是无符号的,即非负整数。
      长度:整数类型可以被指定长度,例如:INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的,它不会限制值的合法范围,只会影响显示字符的个数,而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
      例子,假定类型设定为INT(5),属性为UNSIGNED ZEROFILL,如果用户插入的数据为12的话,那么数据库实际存储数据为00012。

    • 2、实数类型,包括FLOAT、DOUBLE、DECIMAL。
      DECIMAL可以用于存储比BIGINT还大的整型,能存储精确的小数。
      而FLOAT和DOUBLE是有取值范围的,并支持使用标准的浮点进行近似计算。
      计算时FLOAT和DOUBLE相比DECIMAL效率更高一些,DECIMAL你可以理解成是用字符串进行处理。

    • 3、字符串类型,包括VARCHAR、CHAR、TEXT、BLOB
      VARCHAR用于存储可变长字符串,它比定长类型更节省空间。
      VARCHAR使用额外1或2个字节存储字符串长度。列长度小于255字节时,使用1字节表示,否则使用2字节表示。
      VARCHAR存储的内容超出设置的长度时,内容会被截断。
      CHAR是定长的,根据定义的字符串长度分配足够的空间。
      CHAR会根据需要使用空格进行填充方便比较。
      CHAR适合存储很短的字符串,或者所有值都接近同一个长度。
      CHAR存储的内容超出设置的长度时,内容同样会被截断。

      使用策略:
      对于经常变更的数据来说,CHAR比VARCHAR更好,因为CHAR不容易产生碎片。
      对于非常短的列,CHAR比VARCHAR在存储空间上更有效率。
      使用时要注意只分配需要的空间,更长的列排序时会消耗更多内存。
      尽量避免使用TEXT/BLOB类型,查询时会使用临时表,导致严重的性能开销。

    • 4、枚举类型(ENUM),把不重复的数据存储为一个预定义的集合。
      有时可以使用ENUM代替常用的字符串类型。
      ENUM存储非常紧凑,会把列表值压缩到一个或两个字节。
      ENUM在内部存储时,其实存的是整数。
      尽量避免使用数字作为ENUM枚举的常量,因为容易混乱。
      排序是按照内部存储的整数

    • 5、日期和时间类型,尽量使用timestamp,空间效率高于datetime,
      用整数保存时间戳通常不方便处理。
      如果需要存储微妙,可以使用bigint存储。
      看到这里,这道真题是不是就比较容易回答了。

    引擎

    MySQL存储引擎MyISAM与InnoDB区别

    存储引擎Storage engine:MySQL中的数据、索引以及其他对象是如何存储的,是一套文件系统的实现。

    常用的存储引擎有以下:

    • Innodb引擎:Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
    • MyIASM引擎(原本Mysql的默认引擎):不提供事务的支持,也不支持行级锁和外键。
    • MEMORY引擎:所有的数据都在内存中,数据的处理速度快,但是安全性不高。

    MyISAM与InnoDB区别

    MyISAMInnodb
    存储结构每张表被存放在三个文件:frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB
    存储空间MyISAM可被压缩,存储空间较小InnoDB的表需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
    可移植性、备份及恢复由于MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了
    文件格式数据和索引是分别存储的,数据.MYD,索引.MYI数据和索引是集中存储的,.ibd
    记录存储顺序按记录插入顺序保存按主键大小有序插入
    外键不支持支持
    事务不支持支持
    锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的)表级锁定行级锁定、表级锁定,锁定力度小并发能力高
    SELECTMyISAM更优
    INSERT、UPDATE、DELETEInnoDB更优
    select count(*)myisam更快,因为myisam内部维护了一个计数器,可以直接调取。
    索引的实现方式B+树索引,myisam 是堆表B+树索引,Innodb 是索引组织表
    哈希索引不支持支持
    全文索引支持不支持

    MyISAM索引与InnoDB索引的区别?

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

    InnoDB引擎的4大特性

    • 插入缓冲(insert buffer)

    • 二次写(double write)

    • 自适应哈希索引(ahi)

    • 预读(read ahead)

    存储引擎选择

    如果没有特别的需求,使用默认的Innodb即可。

    MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。

    Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键。比如OA自动化办公系统。

    索引

    什么是索引?

    索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。

    索引是一种数据结构。数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

    更通俗的说,索引就相当于目录。为了方便查找书中的内容,通过对内容建立索引形成目录。索引是一个文件,它是要占据物理空间的。

    索引有哪些优缺点?

    索引的优点

    • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
    • 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    索引的缺点

    • 时间方面:创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;
    • 空间方面:索引需要占物理空间。

    索引使用场景(重点)

    where

    img

    上图中,根据id查询记录,因为id字段仅建立了主键索引,因此此SQL执行可选的索引只有主键索引,如果有多个,最终会选一个较优的作为检索的依据。

    -- 增加一个没有建立索引的字段
    alter table innodb1 add sex char(1);
    -- 按sex检索时可选的索引为null
    EXPLAIN SELECT * from innodb1 where sex='男';
    

    img

    可以尝试在一个字段未建立索引时,根据该字段查询的效率,然后对该字段建立索引(alter table 表名 add index(字段名)),同样的SQL执行的效率,你会发现查询效率会有明显的提升(数据量越大越明显)。

    order by

    当我们使用order by将查询结果按照某个字段排序时,如果该字段没有建立索引,那么执行计划会将查询出的所有数据使用外部排序(将数据从硬盘分批读取到内存使用内部排序,最后合并排序结果),这个操作是很影响性能的,因为需要将查询涉及到的所有数据从磁盘中读到内存(如果单条数据过大或者数据量过多都会降低效率),更无论读到内存之后的排序了。

    但是如果我们对该字段建立索引alter table 表名 add index(字段名),那么由于索引本身是有序的,因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的,那么只用取出索引表某个范围内的索引对应的数据,而不用像上述那取出所有数据进行排序再返回某个范围内的数据。(从磁盘取数据是最影响性能的)

    join

    join语句匹配关系(on)涉及的字段建立索引能够提高效率

    索引覆盖

    如果要查询的字段都建立过索引,那么引擎会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会做全表扫描),这叫索引覆盖。因此我们需要尽可能的在select只写必要的查询字段,以增加索引覆盖的几率。

    这里值得注意的是不要想着为每个字段建立索引,因为优先使用索引的优势就在于其体积小。

    索引有哪几种类型?

    主键索引: 数据列不允许重复,不允许为NULL,一个表只能有一个主键。

    唯一索引: 数据列不允许重复,允许为NULL值,一个表允许多个列创建唯一索引。

    • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

    • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

    普通索引: 基本的索引类型,没有唯一性的限制,允许为NULL值。

    • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

    • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

    全文索引: 是目前搜索引擎使用的一种关键技术。

    • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

    索引的数据结构(b树,hash)

    索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。对于哈希索引来说,底层的数据结构就是哈希表,因此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。

    1)B树索引

    mysql通过存储引擎取数据,基本上90%的人用的就是InnoDB了,按照实现方式分,InnoDB的索引类型目前只有两种:BTREE(B树)索引和HASH索引。B树索引是Mysql数据库中使用最频繁的索引类型,基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是(B树)索引(实际是用B+树实现的,因为在查看表索引时,mysql一律打印BTREE,所以简称为B树索引)

    img

    查询方式:

    主键索引区:PI(关联保存的时数据的地址)按主键查询,

    普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快

    B+tree性质:

    1.)n棵子tree的节点包含n个关键字,不用来保存数据而是保存数据的索引。

    2.)所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。

    3.)所有的非终端结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字。

    4.)B+ 树中,数据对象的插入和删除仅在叶节点上进行。

    5.)B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点。

    2)哈希索引

    简要说下,类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法),将数据库字段数据转换成定长的Hash值,与这条数据的行指针一并存入Hash表的对应位置;如果发生Hash碰撞(两个不同关键字的Hash值相同),则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

    img

    索引的基本原理

    索引用来快速地寻找那些具有特定值的记录。如果没有索引,一般来说执行查询时遍历整张表。

    索引的原理很简单,就是把无序的数据变成有序的查询

    1. 把创建了索引的列的内容进行排序

    2. 对排序结果生成倒排表

    3. 在倒排表内容上拼上数据地址链

    4. 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据

    索引算法有哪些?

    索引算法有 BTree算法和Hash算法

    BTree算法

    BTree是最常用的mysql数据库索引算法,也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上,而且还可以用于like操作符,只要它的查询条件是一个不以通配符开头的常量, 例如:

    -- 只要它的查询条件是一个不以通配符开头的常量
    select * from user where name like 'jack%'; 
    -- 如果一通配符开头,或者没有使用常量,则不会使用索引,例如: 
    select * from user where name like '%jack'; 
    

    Hash算法

    Hash Hash索引只能用于对等比较,例如=,<=>(相当于=)操作符。由于是一次定位数据,不像BTree索引需要从根节点到枝节点,最后才能访问到页节点这样多次IO访问,所以检索效率远高于BTree索引。

    索引设计的原则?

    1. 适合索引的列是出现在where子句中的列,或者连接子句中指定的列
    2. 基数较小的类,索引效果较差,没有必要在此列建立索引
    3. 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间
    4. 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。

    创建索引的原则(重中之重)

    索引虽好,但也不是无限制的使用,最好符合一下几个原则

    1) 最左前缀匹配原则,组合索引非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

    2)较频繁作为查询条件的字段才去创建索引

    3)更新频繁字段不适合创建索引

    4)若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)

    5)尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

    6)定义有外键的数据列一定要建立索引。

    7)对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。

    8)对于定义为text、image和bit的数据类型的列不要建立索引。

    创建索引的三种方式,删除索引

    第一种方式:在执行CREATE TABLE时创建索引

    CREATE TABLE user_index2 (
    	id INT auto_increment PRIMARY KEY,
    	first_name VARCHAR (16),
    	last_name VARCHAR (16),
    	id_card VARCHAR (18),
    	information text,
    	KEY name (first_name, last_name),
    	FULLTEXT KEY (information),
    	UNIQUE KEY (id_card)
    );
    

    第二种方式:使用ALTER TABLE命令去增加索引

    ALTER TABLE table_name ADD INDEX index_name (column_list);
    

    ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

    其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。

    索引名index_name可自己命名,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

    第三种方式:使用CREATE INDEX命令创建

    CREATE INDEX index_name ON table_name (column_list);
    

    CREATE INDEX可对表增加普通索引或UNIQUE索引。(但是,不能创建PRIMARY KEY索引)

    删除索引

    根据索引名删除普通索引、唯一索引、全文索引:alter table 表名 drop KEY 索引名

    alter table user_index drop KEY name;
    alter table user_index drop KEY id_card;
    alter table user_index drop KEY information;
    

    删除主键索引:alter table 表名 drop primary key(因为主键只有一个)。这里值得注意的是,如果主键自增长,那么不能直接执行此操作(自增长依赖于主键索引):

    img

    需要取消自增长再行删除:

    alter table user_index
    -- 重新定义字段
    MODIFY id int,
    drop PRIMARY KEY
    

    但通常不会删除主键,因为设计主键一定与业务逻辑无关。

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

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

    使用索引查询一定能提高查询的性能吗?为什么

    通常,通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

    • 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
    • 基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
    • 基于非唯一性索引的检索

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

    关于索引:由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

    1. 所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟)
    2. 然后删除其中无用数据(此过程需要不到两分钟)
    3. 删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。
    4. 与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了。

    前缀索引

    语法:index(field(10)),使用字段值的前10个字符建立索引,默认是使用字段的全部内容建立索引。

    前提:前缀的标识度高。比如密码就适合建立前缀索引,因为密码几乎各不相同。

    实操的难度:在于前缀截取的长度。

    我们可以利用select count(*)/count(distinct left(password,prefixLen));,通过从调整prefixLen的值(从1自增)查看不同前缀长度的一个平均匹配度,接近1时就可以了(表示一个密码的前prefixLen个字符几乎能确定唯一一条记录)

    什么是最左前缀原则?什么是最左匹配原则

    • 顾名思义,就是最左优先,在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。
    • 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
    • =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

    B树和B+树的区别

    • 在B树中,你可以将键和值存放在内部节点和叶子节点;但在B+树中,内部节点都是键,没有值,叶子节点同时存放键和值。

    • B+树的叶子节点有一条链相连,而B树的叶子节点各自独立。

      img

    使用B树的好处

    B树可以在内部节点同时存储键和值,因此,把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

    使用B+树的好处

    由于B+树的内部节点只存放键,不存放值,因此,一次读取,可以在内存页中获取更多的键,有利于更快地缩小查找范围。 B+树的叶节点由一条链相连,因此,当需要进行一次全数据遍历的时候,B+树只需要使用O(logN)时间找到最小的一个节点,然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历,这会需要更多的内存置换次数,因此也就需要花费更多的时间

    Hash索引和B+树所有有什么区别或者说优劣呢?

    首先要知道Hash索引和B+树索引的底层实现原理:

    hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据。

    那么可以看出他们有以下的不同:

    • hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询。

    因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围。

    • hash索引不支持使用索引进行排序,原理同上。
    • hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
    • hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。
    • hash索引虽然在等值查询上较快,但是不稳定。性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差。而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低。

    因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

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

    • B树只适合随机检索,而B+树同时支持随机检索和顺序检索;
    • B+树空间利用率更高,可减少I/O次数,磁盘读写代价更低。一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。这样的话,索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针,只是作为索引使用,其内部结点比B树小,盘块能容纳的结点中关键字数量更多,一次性读入内存中可以查找的关键字也就越多,相对的,IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素;
    • B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束,越靠近根节点的记录查找时间越短,只要找到关键字即可确定记录的存在,其性能等价于在关键字全集内做一次二分查找。而在B+树中,顺序检索比较明显,随机检索时,任何关键字的查找都必须走一条从根节点到叶节点的路,所有关键字的查找路径长度相同,导致每一个关键字的查询效率相当。
    • B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起,只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的,而B树不支持这样的操作。
    • 增删文件(节点)时,效率更高。因为B+树的叶子节点包含所有关键字,并以有序的链表结构存储,这样可很好提高增删效率。

    B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,

    在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引。 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引。如果没有唯一键,则隐式的生成一个键来建立聚簇索引。

    当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询。

    什么是聚簇索引?何时使用聚簇索引与非聚簇索引

    • 聚簇索引:将数据存储与索引放到了一块,找到索引也就找到了数据
    • 非聚簇索引:将数据存储于索引分开结构,索引结构的叶子节点指向了数据的对应行,myisam通过key_buffer把索引先缓存到内存中,当需要访问数据时(通过索引访问数据),在内存中直接搜索索引,然后通过索引找到磁盘相应数据,这也就是为什么索引不在key buffer命中时,速度慢的原因

    澄清一个概念:innodb中,在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值

    何时使用聚簇索引与非聚簇索引

    img

    非聚簇索引一定会回表查询吗?

    不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询。

    举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询。

    联合索引是什么?为什么需要注意联合索引中的顺序?

    MySQL可以使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

    具体原因为:

    MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引,那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序。

    当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推。因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

    事务

    什么是数据库事务?

    事务是一个不可分割的数据库操作序列,也是数据库并发控制的基本单位,其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作,要么都执行,要么都不执行。

    事务最经典也经常被拿出来说例子就是转账了。

    假如小明要给小红转账1000元,这个转账会涉及到两个关键操作就是:将小明的余额减少1000元,将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃,导致小明余额减少而小红的余额没有增加,这样就不对了。事务就是保证这两个关键操作要么都成功,要么都要失败。

    事物的四大特性(ACID)介绍一下?

    关系性数据库需要遵循ACID规则,具体内容如下:

    事务的特性

    1. 原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
    2. 一致性: 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
    3. 隔离性: 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
    4. 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。

    什么是脏读?幻读?不可重复读?

    • 脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。
    • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新的原有的数据。
    • 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。

    什么是事务的隔离级别?MySQL的默认隔离级别是什么?

    为了达到事务的四大特性,数据库定义了4种不同的事务隔离级别,由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable,这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

    隔离级别脏读不可重复读幻影读
    READ-UNCOMMITTED
    READ-COMMITTED×
    REPEATABLE-READ××
    SERIALIZABLE×××

    SQL 标准定义了四个隔离级别:

    • READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
    • READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
    • REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生
    • SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读

    这里需要注意的是:Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

    事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC(多版本并发控制),通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

    因为隔离级别越低,事务请求的锁越少,所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):,但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ(可重读)**并不会有任何性能损失。

    InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别。

    对MySQL的锁了解吗

    当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制。

    就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用。

    隔离级别与锁的关系

    在Read Uncommitted级别下,读取数据不需要加共享锁,这样就不会跟被修改的数据上的排他锁冲突

    在Read Committed级别下,读操作需要加共享锁,但是在语句执行完以后释放共享锁;

    在Repeatable Read级别下,读操作需要加共享锁,但是在事务提交之前并不释放共享锁,也就是必须等待事务执行完毕以后才释放共享锁。

    SERIALIZABLE 是限制性最强的隔离级别,因为该级别锁定整个范围的键,并一直持有锁,直到事务完成。

    按照锁的粒度分数据库锁有哪些?锁机制与InnoDB锁算法

    在关系型数据库中,可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。

    MyISAM和InnoDB存储引擎使用的锁:

    • MyISAM采用表级锁(table-level locking)。
    • InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

    行级锁,表级锁和页级锁对比

    行级锁 行级锁是Mysql中锁定粒度最细的一种锁,表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小,但加锁的开销也最大。行级锁分为共享锁 和 排他锁。

    特点:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    表级锁 表级锁是MySQL中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

    特点:开销小,加锁快;不会出现死锁;锁定粒度大,发出锁冲突的概率最高,并发度最低。

    页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。所以取了折衷的页级,一次锁定相邻的一组记录。

    特点:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

    从锁的类别上分MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了

    从锁的类别上来讲,有共享锁和排他锁。

    共享锁: 又叫做读锁。 当用户要进行数据的读取时,对数据加上共享锁。共享锁可以同时加上多个。

    排他锁: 又叫做写锁。 当用户要进行数据的写入时,对数据加上排他锁。排他锁只可以加一个,他和其他的排他锁,共享锁都相斥。

    用上面的例子来说就是用户的行为有两种,一种是来看房,多个用户一起看房是可以接受的。 一种是真正的入住一晚,在这期间,无论是想入住的还是想看房的都不可以。

    锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁。

    他们的加锁开销从大到小,并发能力也是从大到小。

    MySQL中InnoDB引擎的行锁是怎么实现的?

    答:InnoDB是基于索引来完成行锁

    例: select * from tab_with_index where id = 1 for update;

    for update 可以根据条件来完成行锁锁定,并且 id 是有索引键的列,如果 id 不是索引键那么InnoDB将完成表锁,并发将无从谈起

    InnoDB存储引擎的锁的算法有三种

    • Record lock:单个行记录上的锁
    • Gap lock:间隙锁,锁定一个范围,不包括记录本身
    • Next-key lock:record+gap 锁定一个范围,包含记录本身

    相关知识点:

    1. innodb对于行的查询使用next-key lock
    2. Next-locking keying为了解决Phantom Problem幻读问题
    3. 当查询的索引含有唯一属性时,将next-key lock降级为record key
    4. Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内,而这会导致幻读问题的产生
    5. 有两种方式显式关闭gap锁:(除了外键约束和唯一性检查外,其余情况仅使用record lock) A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

    什么是死锁?怎么解决?

    死锁是指两个或多个事务在同一资源上相互占用,并请求锁定对方的资源,从而导致恶性循环的现象。

    常见的解决死锁的方法

    1、如果不同程序会并发存取多个表,尽量约定以相同的顺序访问表,可以大大降低死锁机会。

    2、在同一个事务中,尽可能做到一次锁定所需要的所有资源,减少死锁产生概率;

    3、对于非常容易产生死锁的业务部分,可以尝试使用升级锁定颗粒度,通过表级锁定来减少死锁产生的概率;

    如果业务处理不好可以用分布式事务锁或者使用乐观锁

    数据库的乐观锁和悲观锁是什么?怎么实现的?

    数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

    悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来,直到提交事务。实现方式:使用数据库中的锁机制

    乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来,通过version的方式来进行锁定。实现方式:乐一般会使用版本号机制或CAS算法实现。

    两种锁的使用场景

    从上面对两种锁的介绍,我们知道两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下(多读场景),即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。

    但如果是多写的情况,一般会经常产生冲突,这就会导致上层应用会不断的进行retry,这样反倒是降低了性能,所以一般多写的场景下用悲观锁就比较合适。

    视图

    为什么要使用视图?什么是视图?

    为了提高复杂SQL语句的复用性和表操作的安全性,MySQL数据库管理系统提供了视图特性。所谓视图,本质上是一种虚拟表,在物理上是不存在的,其内容与真实的表相似,包含一系列带有名称的列和行数据。但是,视图并不在数据库中以储存的数据值形式存在。行和列数据来自定义视图的查询所引用基本表,并且在具体引用视图时动态生成。

    视图使开发者只关心感兴趣的某些特定数据和所负责的特定任务,只能看到视图中所定义的数据,而不是视图所引用表中的数据,从而提高了数据库中数据的安全性。

    视图有哪些特点?

    视图的特点如下:

    • 视图的列可以来自不同的表,是表的抽象和在逻辑意义上建立的新关系。

    • 视图是由基本表(实表)产生的表(虚表)。

    • 视图的建立和删除不影响基本表。

    • 对视图内容的更新(添加,删除和修改)直接影响基本表。

    • 当视图来自多个基本表时,不允许添加和删除数据。

    视图的操作包括创建视图,查看视图,删除视图和修改视图。

    视图的使用场景有哪些?

    视图根本用途:简化sql查询,提高开发效率。如果说还有另外一个用途那就是兼容老的表结构。

    下面是视图的常见使用场景:

    • 重用SQL语句;

    • 简化复杂的SQL操作。在编写查询后,可以方便的重用它而不必知道它的基本查询细节;

    • 使用表的组成部分而不是整个表;

    • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限;

    • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

    视图的优点

    1. 查询简单化。视图能简化用户的操作
    2. 数据安全性。视图使用户能以多种角度看待同一数据,能够对机密数据提供安全保护
    3. 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

    视图的缺点

    1. 性能。数据库必须把视图的查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,即使是视图的一个简单查询,数据库也把它变成一个复杂的结合体,需要花费一定的时间。

    2. 修改限制。当用户试图修改视图的某些行时,数据库必须把它转化为对基本表的某些行的修改。事实上,当从视图中插入或者删除时,情况也是这样。对于简单视图来说,这是很方便的,但是,对于比较复杂的视图,可能是不可修改的

      这些视图有如下特征:1.有UNIQUE等集合操作符的视图。2.有GROUP BY子句的视图。3.有诸如AVG\SUM\MAX等聚合函数的视图。 4.使用DISTINCT关键字的视图。5.连接表的视图(其中有些例外)

    什么是游标?

    游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果,每个游标区都有一个名字。用户可以通过游标逐一获取记录并赋给主变量,交由主语言进一步处理。

    存储过程与函数

    什么是存储过程?有哪些优缺点?

    存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需要创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。

    优点

    1)存储过程是预编译过的,执行效率高。

    2)存储过程的代码直接存放于数据库中,通过存储过程名直接调用,减少网络通讯。

    3)安全性高,执行存储过程需要有一定权限的用户。

    4)存储过程可以重复使用,减少数据库开发人员的工作量。

    缺点

    1)调试麻烦,但是用 PL/SQL Developer 调试很方便!弥补这个缺点。

    2)移植问题,数据库端代码当然是与数据库相关的。但是如果是做工程型项目,基本不存在移植问题。

    3)重新编译问题,因为后端代码是运行前编译的,如果带有引用关系的对象发生改变时,受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。

    4)如果在一个程序系统中大量的使用存储过程,到程序交付使用的时候随着用户需求的增加会导致数据结构的变化,接着就是系统的相关问题了,最后如果用户想维护该系统可以说是很难很难、而且代价是空前的,维护起来更麻烦。

    触发器

    什么是触发器?触发器的使用场景有哪些?

    触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码,当触发某个事件时,自动执行这些代码。

    使用场景

    • 可以通过数据库中的相关表实现级联更改。
    • 实时监控某张表中的某个字段的更改而需要做出相应的处理。
    • 例如可以生成某些业务的编号。
    • 注意不要滥用,否则会造成数据库及应用程序的维护困难。
    • 大家需要牢记以上基础知识点,重点是理解数据类型CHAR和VARCHAR的差异,表存储引擎InnoDB和MyISAM的区别。

    MySQL中都有哪些触发器?

    在MySQL数据库中有如下六种触发器:

    • Before Insert
    • After Insert
    • Before Update
    • After Update
    • Before Delete
    • After Delete

    常用SQL语句

    SQL语句主要分为哪几类

    数据定义语言DDL(Data Ddefinition Language)CREATE,DROP,ALTER

    主要为以上操作 即对逻辑结构等有操作的,其中包括表结构,视图和索引。

    数据查询语言DQL(Data Query Language)SELECT

    这个较为好理解 即查询操作,以select关键字。各种简单查询,连接查询等 都属于DQL。

    数据操纵语言DML(Data Manipulation Language)INSERT,UPDATE,DELETE

    主要为以上操作 即对数据进行操作的,对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

    数据控制功能DCL(Data Control Language)GRANT,REVOKE,COMMIT,ROLLBACK

    主要为以上操作 即对数据库安全性完整性等有操作的,可以简单的理解为权限控制等。

    超键、候选键、主键、外键分别是什么?

    • 超键:在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
    • 候选键:是最小超键,即没有冗余元素的超键。
    • 主键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。
    • 外键:在一个表中存在的另一个表的主键称此表的外键。

    SQL 约束有哪几种?

    SQL 约束有哪几种?

    • NOT NULL: 用于控制字段的内容一定不能为空(NULL)。
    • UNIQUE: 控件字段内容不能重复,一个表允许有多个 Unique 约束。
    • PRIMARY KEY: 也是用于控件字段内容不能重复,但它在一个表只允许出现一个。
    • FOREIGN KEY: 用于预防破坏表之间连接的动作,也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
    • CHECK: 用于控制字段的值范围。

    六种关联查询

    • 交叉连接(CROSS JOIN)
    • 内连接(INNER JOIN)
    • 外连接(LEFT JOIN/RIGHT JOIN)
    • 联合查询(UNION与UNION ALL)
    • 全连接(FULL JOIN)
    • 交叉连接(CROSS JOIN)
    SELECT * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN C)#没有任何关联条件,结果是笛卡尔积,结果集会很大,没有意义,很少使用内连接(INNER JOIN)SELECT * FROM A,B WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON A.id=B.id多表中同时符合某种条件的数据记录的集合,INNER JOIN可以缩写为JOIN
    

    内连接分为三类

    • 等值连接:ON A.id=B.id
    • 不等值连接:ON A.id > B.id
    • 自连接:SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

    外连接(LEFT JOIN/RIGHT JOIN)

    • 左外连接:LEFT OUTER JOIN, 以左表为主,先查询出左表,按照ON后的关联条件匹配右表,没有匹配到的用NULL填充,可以简写成LEFT JOIN
    • 右外连接:RIGHT OUTER JOIN, 以右表为主,先查询出右表,按照ON后的关联条件匹配左表,没有匹配到的用NULL填充,可以简写成RIGHT JOIN

    联合查询(UNION与UNION ALL)

    SELECT * FROM A UNION SELECT * FROM B UNION ...
    
    • 就是把多个结果集集中在一起,UNION前的结果为基准,需要注意的是联合查询的列数要相等,相同的记录行会合并
    • 如果使用UNION ALL,不会合并重复的记录行
    • 效率 UNION 高于 UNION ALL

    全连接(FULL JOIN)

    • MySQL不支持全连接
    • 可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用
    SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id
    

    表连接面试题

    有2张表,1张R、1张S,R表有ABC三列,S表有CD两列,表中各有三条记录。

    R表

    ABC
    a1b1c1
    a2b2c2
    a3b3c3

    S表

    CD
    c1d1
    c2d2
    c4d3
    1. 交叉连接(笛卡尔积):

    select r.*,s.* from r,s

    ABCCD
    a1b1c1c1d1
    a2b2c2c1d1
    a3b3c3c1d1
    a1b1c1c2d2
    a2b2c2c2d2
    a3b3c3c2d2
    a1b1c1c4d3
    a2b2c2c4d3
    a3b3c3c4d3
    1. 内连接结果:

      select r.*,s.* from r inner join s on r.c=s.c

    ABCCD
    a1b1c1c1d1
    a2b2c2c2d2
    1. 左连接结果:

      select r.*,s.* from r left join s on r.c=s.c

    ABCCD
    a1b1c1c1d1
    a2b2c2c2d2
    a3b3c3
    1. 右连接结果:

      select r.*,s.* from r right join s on r.c=s.c

    ABCCD
    a1b1c1c1d1
    a2b2c2c2d2
    c4d3
    1. 全表连接的结果(MySql不支持,Oracle支持):

      select r.*,s.* from r full join s on r.c=s.c

    ABCCD
    a1b1c1c1d1
    a2b2c2c2d2
    a3b3c3
    c4d3

    什么是子查询

    1. 条件:一条SQL语句的查询结果做为另一条查询语句的条件或查询结果

    2. 嵌套:多条SQL语句嵌套使用,内部的SQL查询语句称为子查询。

    子查询的三种情况

    1. 子查询是单行单列的情况:结果集是一个值,父查询使用:=、 <、 > 等运算符
    -- 查询工资最高的员工是谁? 
    select  * from employee where salary=(select max(salary) from employee);   
    
    1. 子查询是多行单列的情况:结果集类似于一个数组,父查询使用:in 运算符
    -- 查询工资最高的员工是谁? 
    select  * from employee where salary=(select max(salary) from employee);    
    
    1. 子查询是多行多列的情况:结果集类似于一张虚拟表,不能用于where条件,用于select子句中做为子表
    -- 1) 查询出2011年以后入职的员工信息
    -- 2) 查询所有的部门信息,与上面的虚拟表中的信息比对,找出所有部门ID相等的员工。
    select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    
    
    -- 使用表连接:
    select d.*, e.* from  dept d inner join employee e on d.id = e.dept_id where e.join_date >  '2011-1-1'  
    

    mysql中 in 和 exists 区别

    mysql中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高,这种说法其实是不准确的。这个是要区分环境的。

    1. 如果查询的两个表大小相当,那么用in和exists差别不大。
    2. 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。
    3. not in 和not exists:如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引;而not extsts的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。

    varchar与char的区别

    char的特点

    • char表示定长字符串,长度是固定的;

    • 如果插入数据的长度小于char的固定长度时,则用空格填充;

    • 因为长度固定,所以存取速度要比varchar快很多,甚至能快50%,但正因为其长度固定,所以会占据多余的空间,是空间换时间的做法;

    • 对于char来说,最多能存放的字符个数为255,和编码无关

    varchar的特点

    • varchar表示可变长字符串,长度是可变的;

    • 插入的数据是多长,就按照多长来存储;

    • varchar在存取方面与char相反,它存取慢,因为长度不固定,但正因如此,不占据多余的空间,是时间换空间的做法;

    • 对于varchar来说,最多能存放的字符个数为65532

    总之,结合性能角度(char更快)和节省磁盘空间角度(varchar更小),具体情况还需具体来设计数据库才是妥当的做法。

    varchar(50)中50的涵义

    最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order by col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中, 50 代表字节数,现在代表字符数。

    int(20)中20的涵义

    是指显示字符的长度。20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;

    不影响内部存储,只是影响带 zerofill 定义的 int 时,前面补多少个 0,易于报表展示

    mysql为什么这么设计

    对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

    mysql中int(10)和char(10)以及varchar(10)的区别

    • int(10)的10表示显示的数据的长度,不是存储数据的大小;chart(10)和varchar(10)的10表示存储数据的大小,即表示存储多少个字符。

      int(10) 10位的数据长度 9999999999,占32个字节,int型4位
      char(10) 10位固定字符串,不足补空格 最多10个字符
      varchar(10) 10位可变字符串,不足补空格 最多10个字符

    • char(10)表示存储定长的10个字符,不足10个就用空格补齐,占用更多的存储空间

    • varchar(10)表示存储10个变长的字符,存储多少个就是多少个,空格也按一个字符存储,这一点是和char(10)的空格不同的,char(10)的空格表示占位不算一个字符

    FLOAT和DOUBLE的区别是什么?

    • FLOAT类型数据可以存储至多8位十进制数,并在内存中占4字节。
    • DOUBLE类型数据可以存储至多18位十进制数,并在内存中占8字节。

    drop、delete与truncate的区别

    三者都表示删除,但是三者有一些差别:

    DeleteTruncateDrop
    类型属于DML属于DDL属于DDL
    回滚可回滚不可回滚不可回滚
    删除内容表结构还在,删除表的全部或者一部分数据行表结构还在,删除表中的所有数据从数据库中删除表,所有的数据行,索引和权限也会被删除
    删除速度删除速度慢,需要逐行删除删除速度快删除速度最快

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

    UNION与UNION ALL的区别?

    • 如果使用UNION ALL,不会合并重复的记录行
    • 效率 UNION 高于 UNION ALL

    SQL优化

    如何定位及优化SQL语句的性能问题?创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?

    对于低性能的SQL语句的定位,最重要也是最有效的方法就是使用执行计划,MySQL提供了explain命令来查看语句的执行计划。 我们知道,不管是哪种数据库,或者是哪种数据库引擎,在对一条SQL语句进行执行的过程中都会做很多相关的优化,对于查询语句,最重要的优化方式就是使用索引。 而执行计划,就是显示数据库引擎对于SQL语句的执行的详细情况,其中包含了是否使用索引,使用什么索引,使用的索引的相关信息等

    在这里插入图片描述

    执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;

    • id相同执行顺序由上至下。
    • id不同,id值越大优先级越高,越先被执行。
    • id为null时表示一个结果集,不需要使用它查询,常出现在包含union等查询语句中。

    select_type 每个子查询的查询类型,一些常见的查询类型。

    idselect_typedescription
    1SIMPLE不包含任何子查询或union等查询
    2PRIMARY包含子查询最外层查询就显示为 PRIMARY
    3SUBQUERY在select或 where字句中包含的查询
    4DERIVEDfrom字句中包含的查询
    5UNION出现在union后的查询语句中
    6UNION RESULT从UNION中获取结果集,例如上文的第三个例子

    table 查询的数据表,当从衍生表中查数据时会显示 x 表示对应的执行计划id partitions 表分区、表创建的时候可以指定通过那个列进行表分区。 举个例子:

    create table tmp (
        id int unsigned not null AUTO_INCREMENT,
        name varchar(255),
        PRIMARY KEY (id)
    ) engine = innodb
    partition by key (id) partitions 5;
    

    type(非常重要,可以看到有没有走索引) 访问类型

    • ALL 扫描全表数据
    • index 遍历索引
    • range 索引范围查找
    • index_subquery 在子查询中使用 ref
    • unique_subquery 在子查询中使用 eq_ref
    • ref_or_null 对Null进行索引的优化的 ref
    • fulltext 使用全文索引
    • ref 使用非唯一索引查找数据
    • eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

    possible_keys 可能使用的索引,注意不一定会使用。查询涉及到的字段上若存在索引,则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

    key 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL。

    TIPS:查询中若使用了覆盖索引(覆盖索引:索引的数据覆盖了需要查询的所有数据),则该索引仅出现在key列表中

    key_length 索引长度

    ref 表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

    rows 返回估算的结果集数目,并不是一个准确的值。

    extra 的信息非常丰富,常见的有:

    1. Using index 使用覆盖索引
    2. Using where 使用了用where子句来过滤结果集
    3. Using filesort 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化。
    4. Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册
    【推荐】SQL性能优化的目标:至少要达到 range 级别,要求是ref级别,如果可以是consts最好。 
    说明: 
    1) consts 单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。 
    2) ref 指的是使用普通的索引(normal index)。 
    3) range 对索引进行范围检索。 
    反例:explain表的结果,type=index,索引物理文件全扫描,速度非常慢,这个index级别比较range还低,与全表扫描是小巫见大巫。
    

    SQL的生命周期?

    1. 应用服务器与数据库服务器建立一个连接

    2. 数据库进程拿到请求sql

    3. 解析并生成执行计划,执行

    4. 读取数据到内存并进行逻辑处理

    5. 通过步骤一的连接,发送结果到客户端

    6. 关掉连接,释放资源

      在这里插入图片描述

    大表数据查询,怎么优化

    1. 优化shema、sql语句+索引;
    2. 第二加缓存,memcached, redis;
    3. 主从复制,读写分离;
    4. 垂直拆分,根据你模块的耦合度,将一个大的系统分为多个小的系统,也就是分布式系统;
    5. 水平切分,针对数据量大的表,这一步最麻烦,最能考验技术水平,要选择一个合理的sharding key, 为了有好的查询效率,表结构也要改动,做一定的冗余,应用也要改,sql中尽量带sharding key,将数据定位到限定的表上去查,而不是扫描全部的表;

    超大分页怎么处理?

    超大的分页一般从两个方向上来解决.

    • 数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
    • 从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

    解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

    在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种.

    【推荐】利用延迟关联或者子查询优化超多分页场景。 
    
    说明:MySQL并不是跳过offset行,而是取offset+N行,然后返回放弃前offset行,返回N行,那当offset特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。 
    
    正例:先快速定位需要获取的id段,然后再关联: 
    
    SELECT a.* FROM1 a, (select id from1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
    

    mysql 分页

    LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

    mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 
    

    为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:

    mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last. 
    

    如果只给定一个参数,它表示返回最大的记录行数目:

    mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行 
    

    换句话说,LIMIT n 等价于 LIMIT 0,n。

    慢查询日志

    用于记录执行时间超过某个临界值的SQL日志,用于快速定位慢查询,为我们的优化做参考。

    开启慢查询日志

    配置项:slow_query_log

    可以使用show variables like ‘slov_query_log’查看是否开启,如果状态值为OFF,可以使用set GLOBAL slow_query_log = on来开启,它会在datadir下产生一个xxx-slow.log的文件。

    设置临界时间

    配置项:long_query_time

    查看:show VARIABLES like 'long_query_time',单位秒

    设置:set long_query_time=0.5

    实操时应该从长时间设置到短的时间,即将最慢的SQL优化掉

    查看日志,一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log

    关心过业务系统里面的sql耗时吗?统计过慢查询吗?对慢查询都怎么优化过?

    在业务系统中,除了使用主键进行的查询,其他的我都会在测试库上测试其耗时,慢查询的统计主要由运维在做,会定期将业务中的慢查询反馈给我们。

    慢查询的优化首先要搞明白慢的原因是什么? 是查询条件没有命中索引?是load了不需要的数据列?还是数据量太大?

    所以优化也是针对这三个方向来的,

    • 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写。
    • 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引。
    • 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表。

    为什么要尽量设定一个主键?

    主键是数据库确保数据行在整张表唯一性的保障,即使业务上本张表没有主键,也建议添加一个自增长的ID列作为主键。设定了主键之后,在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

    主键使用自增ID还是UUID?

    推荐使用自增ID,不要使用UUID。

    因为在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降。

    总之,在数据量大一些的情况下,用自增主键性能会好一些。

    关于主键是聚簇索引,如果没有主键,InnoDB会选择一个唯一键来作为聚簇索引,如果没有唯一键,会生成一个隐式的主键。

    字段为什么要求定义为not null?

    null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。

    如果要存储用户的密码散列,应该使用什么字段进行存储?

    密码散列,盐,用户身份证号等固定长度的字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率。

    优化查询过程中的数据访问

    • 访问数据太多导致查询性能下降
    • 确定应用程序是否在检索大量超过需要的数据,可能是太多行或列
    • 确认MySQL服务器是否在分析大量不必要的数据行
    • 避免犯如下SQL语句错误
    • 查询不需要的数据。解决办法:使用limit解决
    • 多表关联返回全部列。解决办法:指定列名
    • 总是返回全部列。解决办法:避免使用SELECT *
    • 重复查询相同的数据。解决办法:可以缓存数据,下次直接读取缓存
    • 是否在扫描额外的记录。解决办法:
    • 使用explain进行分析,如果发现查询需要扫描大量的数据,但只返回少数的行,可以通过如下技巧去优化:
    • 使用索引覆盖扫描,把所有的列都放到索引中,这样存储引擎不需要回表获取对应行就可以返回结果。
    • 改变数据库和表的结构,修改数据表范式
    • 重写SQL语句,让优化器可以以更优的方式执行查询。

    优化长难的查询语句

    • 一个复杂查询还是多个简单查询
    • MySQL内部每秒能扫描内存中上百万行数据,相比之下,响应数据给客户端就要慢得多
    • 使用尽可能小的查询是好的,但是有时将一个大的查询分解为多个小的查询是很有必要的。
    • 切分查询
    • 将一个大的查询分为多个小的相同的查询
    • 一次性删除1000万的数据要比一次删除1万,暂停一会的方案更加损耗服务器开销。
    • 分解关联查询,让缓存的效率更高。
    • 执行单个查询可以减少锁的竞争。
    • 在应用层做关联更容易对数据库进行拆分。
    • 查询效率会有大幅提升。
    • 较少冗余记录的查询。

    优化特定类型的查询语句

    • count(*)会忽略所有的列,直接统计所有列数,不要使用count(列名)
    • MyISAM中,没有任何where条件的count(*)非常快。
    • 当有where条件时,MyISAM的count统计不一定比其它引擎快。
    • 可以使用explain查询近似值,用近似值替代count(*)
    • 增加汇总表
    • 使用缓存

    优化关联查询

    • 确定ON或者USING子句中是否有索引。
    • 确保GROUP BY和ORDER BY只有一个表中的列,这样MySQL才有可能使用索引。

    优化子查询

    • 用关联查询替代
    • 优化GROUP BY和DISTINCT
    • 这两种查询据可以使用索引来优化,是最有效的优化方法
    • 关联查询中,使用标识列分组的效率更高
    • 如果不需要ORDER BY,进行GROUP BY时加ORDER BY NULL,MySQL不会再进行文件排序。
    • WITH ROLLUP超级聚合,可以挪到应用程序处理

    优化LIMIT分页

    • LIMIT偏移量大的时候,查询效率较低
    • 可以记录上次查询的最大ID,下次查询时直接根据该ID来查询

    优化UNION查询

    • UNION ALL的效率高于UNION

    优化WHERE子句

    解题方法

    对于此类考题,先说明如何定位低效SQL语句,然后根据SQL语句可能低效的原因做排查,先从索引着手,如果索引没有问题,考虑以上几个方面,数据访问的问题,长难查询句的问题还是一些特定类型优化的问题,逐一回答。

    SQL语句优化的一些方法?

    • 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
    • 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num is null
    -- 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
    select id from t where num=
    
    • 3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。
    • 4.应尽量避免在 where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
    select id from t where num=10 or num=20
    -- 可以这样查询:
    select id from t where num=10 union all select id from t where num=20
    
    • 5.in 和 not in 也要慎用,否则会导致全表扫描,如:
    select id from t where num in(1,2,3) 
    -- 对于连续的数值,能用 between 就不要用 in 了:
    select id from t where num between 1 and 3
    
    • 6.下面的查询也将导致全表扫描:select id from t where name like ‘%李%’若要提高效率,可以考虑全文检索。
    • 7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
    select id from t where num=@num
    -- 可以改为强制查询使用索引:
    select id from t with(index(索引名)) where num=@num
    
    • 8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
    select id from t where num/2=100
    -- 应改为:
    select id from t where num=100*2
    
    • 9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
    select id from t where substring(name,1,3)=’abc’
    -- name以abc开头的id应改为:
    select id from t where name like ‘abc%
    • 10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    数据库优化

    为什么要优化

    • 系统的吞吐量瓶颈往往出现在数据库的访问速度上
    • 随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢
    • 数据是存放在磁盘上的,读写速度无法和内存相比

    优化原则:减少系统瓶颈,减少资源占用,增加系统的反应速度。

    数据库结构优化

    一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

    需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

    将字段很多的表分解成多个表

    对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。

    因为当一个表的数据量很大时,会由于使用频率低的字段的存在而变慢。

    增加中间表

    对于需要经常联合查询的表,可以建立中间表以提高查询效率。

    通过建立中间表,将需要通过联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询。

    增加冗余字段

    设计数据表时应尽量遵循范式理论的规约,尽可能的减少冗余字段,让数据库设计看起来精致、优雅。但是,合理的加入冗余字段可以提高查询速度。

    表的规范化程度越高,表和表之间的关系越多,需要连接查询的情况也就越多,性能也就越差。

    注意:

    冗余字段的值在一个表中修改了,就要想办法在其他表中更新,否则就会导致数据不一致的问题。

    MySQL数据库cpu飙升到500%的话他怎么处理?

    当 cpu 飙升到 500%时,先用操作系统命令 top 命令观察是不是 mysqld 占用导致的,如果不是,找出占用高的进程,并进行相关处理。

    如果是 mysqld 造成的, show processlist,看看里面跑的 session 情况,是不是有消耗资源的 sql 在运行。找出消耗高的 sql,看看执行计划是否准确, index 是否缺失,或者实在是数据量太大造成。

    一般来说,肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降),等进行相应的调整(比如说加索引、改 sql、改内存参数)之后,再重新跑这些 SQL。

    也有可能是每个 sql 消耗资源并不多,但是突然之间,有大量的 session 连进来导致 cpu 飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等

    大表怎么优化?某个表有近千万数据,CRUD比较慢,如何优化?分库分表了是怎么做的?分表分库了有什么问题?有用到中间件么?他们的原理知道么?

    当MySQL单表记录数过大时,数据库的CRUD性能会明显下降,一些常见的优化措施如下:

    1. 限定数据的范围: 务必禁止不带任何限制数据范围条件的查询语句。比如:我们当用户在查询订单历史的时候,我们可以控制在一个月的范围内。;
    2. 读/写分离: 经典的数据库拆分方案,主库负责写,从库负责读;
    3. 缓存: 使用MySQL的缓存,另外对重量级、更新少的数据可以考虑使用应用级别的缓存;

    还有就是通过分库分表的方式进行优化,主要有垂直分表和水平分表

    1. 垂直分区:

      根据数据库里面数据表的相关性进行拆分。 例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。

      简单来说垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。 如下图所示,这样来说大家应该就更容易理解了。

      img

      垂直拆分的优点: 可以使得行数据变小,在查询时减少读取的Block数,减少I/O次数。此外,垂直分区可以简化表的结构,易于维护。

      垂直拆分的缺点: 主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。此外,垂直分区会让事务变得更加复杂;

      垂直分表

      把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中

      img

      适用场景
      • 1、如果一个表中某些列常用,另外一些列不常用
      • 2、可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数
      缺点
      • 有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差
      • 对于应用层来说,逻辑算法增加开发成本
      • 管理冗余列,查询所有数据需要join操作
    2. 水平分区:

      保持数据表结构不变,通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中,达到了分布式的目的。 水平拆分可以支撑非常大的数据量。

      水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。举个例子:我们可以将用户信息表拆分成多个用户信息表,这样就可以避免单一表数据量过大对性能造成影响。

      数据库水平拆分

      水品拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题,但由于表的数据还是在同一台机器上,其实对于提升MySQL并发能力没有什么意义,所以 水平拆分最好分库

      水平拆分能够 支持非常大的数据量存储,应用端改造也少,但 分片事务难以解决 ,跨界点Join性能较差,逻辑复杂。

      《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片,因为拆分会带来逻辑、部署、运维的各种复杂度 ,一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片,尽量选择客户端分片架构,这样可以减少一次和中间件的网络I/O。

      水平分表:

      表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询次数

      img

      适用场景
      • 1、表中的数据本身就有独立性,例如表中分表记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用。
      • 2、需要把数据存放在多个介质上。
      水平切分的缺点
      • 1、给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION操作
      • 2、在许多数据库应用中,这种复杂度会超过它带来的优点,查询时会增加读一个索引层的磁盘次数

      下面补充一下数据库分片的两种常见方案:

      • 客户端代理: 分片逻辑在应用端,封装在jar包中,通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
      • 中间件代理: 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

    分库分表后面临的问题

    • 事务支持 分库分表后,就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务,将付出高昂的性能代价; 如果由应用程序去协助控制,形成程序逻辑上的事务,又会造成编程方面的负担。

    • 跨库join

      只要是进行切分,跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。 分库分表方案产品

    • 跨节点的count,order by,group by以及聚合函数问题 这些是一类问题,因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案:与解决跨节点join问题的类似,分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行,因此很多时候它的速度要比单一大表快很多。但如果结果集很大,对应用程序内存的消耗是一个问题。

    • 数据迁移,容量规划,扩容等问题 来自淘宝综合业务平台团队,它利用对2的倍数取余具有向前兼容的特性(如对4取余得1的数对2取余也是1)来分配数据,避免了行级别的数据迁移,但是依然需要进行表级别的迁移,同时对扩容规模和分表数量都有限制。总得来说,这些方案都不是十分的理想,多多少少都存在一些缺点,这也从一个侧面反映出了Sharding扩容的难度。

    • ID问题

    • 一旦数据库被切分到多个物理结点上,我们将不能再依赖数据库自身的主键生成机制。一方面,某个分区数据库自生成的ID无法保证在全局上是唯一的;另一方面,应用程序在插入数据之前需要先获得ID,以便进行SQL路由. 一些常见的主键生成策略

    UUID 使用UUID作主键是最简单的方案,但是缺点也是非常明显的。由于UUID非常的长,除占用大量存储空间外,最主要的问题是在索引上,在建立索引和基于索引进行查询时都存在性能问题。 Twitter的分布式自增ID算法Snowflake 在分布式系统中,需要生成全局UID的场合还是比较多的,twitter的snowflake解决了这种需求,实现也还是很简单的,除去配置信息,核心代码就是毫秒级时间41位 机器ID 10位 毫秒内序列12位。

    • 跨分片的排序分页

      般来讲,分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候,我们通过分片规则可以比较容易定位到指定的分片,而当排序字段非分片字段的时候,情况就会变得比较复杂了。为了最终结果的准确性,我们需要在不同的分片节点中将数据进行排序并返回,并将不同分片返回的结果集进行汇总和再次排序,最后再返回给用户。如下图所示:

      在这里插入图片描述

    MySQL的复制原理以及流程

    主从复制:将主数据库中的DDL和DML操作通过二进制日志(BINLOG)传输到从数据库上,然后将这些日志重新执行(重做);从而使得从数据库的数据与主数据库保持一致。

    主从复制的作用

    1. 主数据库出现问题,可以切换到从数据库。
    2. 可以进行数据库层面的读写分离。
    3. 可以在从数据库上进行日常备份。

    MySQL主从复制解决的问题

    • 数据分布:随意开始或停止复制,并在不同地理位置分布数据备份
    • 负载均衡:降低单个服务器的压力
    • 高可用和故障切换:帮助应用程序避免单点失败
    • 升级测试:可以用更高版本的MySQL作为从库

    MySQL主从复制工作原理

    • 在主库上把数据更高记录到二进制日志
    • 从库将主库的日志复制到自己的中继日志
    • 从库读取中继日志的事件,将其重放到从库数据中

    基本原理流程,3个线程以及之间的关联

    :binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;

    :io线程——在使用start slave 之后,负责从master上拉取 binlog 内容,放进自己的relay log中;

    :sql执行线程——执行relay log中的语句;

    复制过程

    img

    Binary log:主数据库的二进制日志

    Relay log:从服务器的中继日志

    第一步:master在每个事务更新数据完成之前,将该操作记录串行地写入到binlog文件中。

    第二步:salve开启一个I/O Thread,该线程在master打开一个普通连接,主要工作是binlog dump process。如果读取的进度已经跟上了master,就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志中。

    第三步:SQL Thread会读取中继日志,并顺序执行该日志中的SQL事件,从而与主数据库中的数据保持一致。

    读写分离有哪些解决方案?

    读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读(如果对slave执行写操作,那么show slave status将会呈现Slave_SQL_Running=NO,此时你需要按照前面提到的手动同步一下slave)。

    方案一

    使用mysql-proxy代理

    优点:直接实现读写分离和负载均衡,不用修改代码,master和slave用一样的帐号,mysql官方不建议实际生产中使用

    缺点:降低性能, 不支持事务

    方案二

    使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。
    如果采用了mybatis, 可以将读写分离放在ORM层,比如mybatis可以通过mybatis plugin拦截sql语句,所有的insert/update/delete都访问master库,所有的select 都访问salve库,这样对于dao层都是透明。 plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题, 也就是不支持事务, 所以我们还需要重写一下DataSourceTransactionManager, 将read-only的事务扔进读库, 其余的有读有写的扔进写库。

    方案三

    使用AbstractRoutingDataSource+aop+annotation在service层决定数据源,可以支持事务.

    缺点:类内部方法通过this.xx()方式相互调用时,aop不会进行拦截,需进行特殊处理。

    备份计划,mysqldump以及xtranbackup的实现原理

    (1)备份计划

    视库的大小来定,一般来说 100G 内的库,可以考虑使用 mysqldump 来做,因为 mysqldump更加轻巧灵活,备份时间选在业务低峰期,可以每天进行都进行全量备份(mysqldump 备份出来的文件比较小,压缩之后更小)。

    100G 以上的库,可以考虑用 xtranbackup 来做,备份速度明显要比 mysqldump 要快。一般是选择一周一个全备,其余每天进行增量备份,备份时间为业务低峰期。

    (2)备份恢复时间

    物理备份恢复快,逻辑备份恢复慢

    这里跟机器,尤其是硬盘的速率有关系,以下列举几个仅供参考

    20G的2分钟(mysqldump)

    80G的30分钟(mysqldump)

    111G的30分钟(mysqldump)

    288G的3小时(xtra)

    3T的4小时(xtra)

    逻辑导入时间一般是备份时间的5倍以上

    (3)备份恢复失败如何处理

    首先在恢复之前就应该做足准备工作,避免恢复的时候出错。比如说备份之后的有效性检查、权限检查、空间检查等。如果万一报错,再根据报错的提示来进行相应的调整。

    (4)mysqldump和xtrabackup实现原理

    mysqldump

    mysqldump 属于逻辑备份。加入–single-transaction 选项可以进行一致性备份。后台进程会先设置 session 的事务隔离级别为 RR(SET SESSION TRANSACTION ISOLATION LEVELREPEATABLE READ),之后显式开启一个事务(START TRANSACTION /*!40100 WITH CONSISTENTSNAPSHOT */),这样就保证了该事务里读到的数据都是事务事务时候的快照。之后再把表的数据读取出来。如果加上–master-data=1 的话,在刚开始的时候还会加一个数据库的读锁(FLUSH TABLES WITH READ LOCK),等开启事务后,再记录下数据库此时 binlog 的位置(showmaster status),马上解锁,再读取表的数据。等所有的数据都已经导完,就可以结束事务

    Xtrabackup:

    xtrabackup 属于物理备份,直接拷贝表空间文件,同时不断扫描产生的 redo 日志并保存下来。最后完成 innodb 的备份后,会做一个 flush engine logs 的操作(老版本在有 bug,在5.6 上不做此操作会丢数据),确保所有的 redo log 都已经落盘(涉及到事务的两阶段提交

    概念,因为 xtrabackup 并不拷贝 binlog,所以必须保证所有的 redo log 都落盘,否则可能会丢最后一组提交事务的数据)。这个时间点就是 innodb 完成备份的时间点,数据文件虽然不是一致性的,但是有这段时间的 redo 就可以让数据文件达到一致性(恢复的时候做的事

    情)。然后还需要 flush tables with read lock,把 myisam 等其他引擎的表给备份出来,备份完后解锁。这样就做到了完美的热备。

    数据表损坏的修复方式有哪些?

    使用 myisamchk 来修复,具体步骤:

    • 1)修复前将mysql服务停止。
    • 2)打开命令行方式,然后进入到mysql的/bin目录。
    • 3)执行myisamchk –recover 数据库所在路径/*.MYI

    使用repair table 或者 OPTIMIZE table命令来修复,REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。 OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用了OPTIMIZE TABLE命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)

    展开全文
  • 高级web开发求职必备mysql面试知识点
  • mysql面试题.zip

    2020-01-15 12:00:16
    mysql面试题.zip
  • 2021年MySQL面试题55道

    万次阅读 多人点赞 2021-03-25 00:05:52
    文章目录前言面试题系列文章传送门MySQL面试题内容1. 简述在MySQL数据库中引擎MyISAM和InnoDB的区别?2. MySQL中有哪几种锁?3. 什么是通用SQL函数?4. MySQL中有哪些不同的表格?5. CHAR和VARCHAR的区别?6. MySQL...

    文章目录


    前言

    作为应届生,处于找工作中ing。今年2月份刚刚整理出来的面试题,时间比较赶就没有按照模块分类排序了。总而言之,顺序比较乱,希望大家耐着性子 看。如果实在介意,评论告知,我会视情况作修改的。另外如果大家觉得我找的答案不够清晰,欢迎私信或者评论只出,我看到都会去修改的!


    面试题系列文章传送门

    2021年后端开发面试题整理合集


    MySQL面试题内容

    1. 简述在MySQL数据库中引擎MyISAM和InnoDB的区别?

    1. 存储结构方面:MyISAM每个MyISAM在磁盘上存储成三个文件.frm文件存储表定义、数据文件的扩展名为.MYD、索引文件的扩展名是.MYI;InnoDB所有的表都保存在同一个数据文件中,表的大小只受限于操作系统文件的大小,一般为2GB。
    2. 存储空间方面:MyISAM可被压缩,存储空间较小。支持三种不同的存储格式:静态表、动态表、压缩表;InnoDB需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
    3. 事务支持方面:MyISAM强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持;InnoDB提供事务支持事务。
    4. 表锁差异方面:MyISAM只支持表级锁;InnoDB支持事务和行级锁。
    5. 表主键:MyISAM允许没有任何索引和主键的表存在,索引都是保存行的地址。InnoDB如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键。
    6. 增删改查操作方面:如果执行大量的SELECT,MyISAM是更好的选择。如果执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。
    7. 外键方面:MyISAM不支持;InnoDB支持。

    2. MySQL中有哪几种锁?

    表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
    页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

    3. 什么是通用SQL函数?

    CONCAT(A, B): 连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。
    FORMAT(X, D): 格式化数字 X 到 D 有效数字。
    CURRDATE():CURRTIME():返回当前日期或时间。
    NOW():将当前日期和时间作为一个值返回。
    MONTH():DAY(),YEAR(),WEEK(),WEEKDAY() :从日期值中提取给定数据。
    HOUR():MINUTE(),SECOND() : 从时间值中提取给定数据。
    DATEDIFF(A,B):确定两个日期之间的差异,通常用于计算年龄
    SUBTIMES(A,B):确定两次之间的差异。
    FROMDAYS(INT): 将整数天数转换为日期值。

    4. MySQL中有哪些不同的表格?

    MyISAM、Heap、Merge、INNODB、ISAM

    5. CHAR和VARCHAR的区别?

    CHAR 和 VARCHAR 类型在存储和检索方面有所不同
    CHAR 列长度固定为创建表时声明的长度,长度值范围是1到255当CHAR值被存储时,它们被用空格填充到特定长度,检索 CHAR 值时需删除尾随空格。

    6. MySQL中InnoDB支持的四种事务隔离级别名称,以及逐级之间的区别?

    read uncommited :读到未提交数据
    read committed:脏读,不可重复读
    repeatable read:可重复读
    serializable :串行事务

    7. myisamchk是用来做什么的?

    它用来压缩 MyISAM 表,这减少了磁盘或内存使用。

    8. 主键和候选键有什么区别?

    表格的每一行都由主键唯一标识,一个表只有一个主键,主键也是候选键。按照惯例,候选键可以被指定为主键,并且可以用于任何外键引用。

    9. 如果一个表有一列定义为TIMESTAMP,将发生什么?

    每当行被更改时,时间戳字段将获取当前时间戳。

    10. 列设置为AUTO INCREMENT时,如果在表中达到最大值,会发生什么情况?

    它会停止递增,任何进一步的插入都将产生错误,因为密钥已被使用。

    11. MySQL 数据库作发布系统的存储,一天五万条以上的增量,预计运维三年,怎么优化?

    设计良好的数据库结构,允许部分数据冗余,尽量避免 join 查询,提高效率。
    选择合适的表字段数据类型和存储引擎,适当的添加索引。
    MySQL 库主从读写分离。
    找规律分表,减少单表中的数据量提高查询速度。
    添加缓存机制,比如 memcached,apc 等。
    不经常改动的页面,生成静态页面。
    书写高效率的SQL。比如SELECT * FROM TABEL改为SELECT field_1,field_2, field_3 FROM TABLE.

    12. 你怎么看到为表格定义的所有索引?

    索引是通过以下方式为表格定义的:SHOW INDEX FROM ;

    13.MySQ 支持事务吗?

    在缺省模式下,MySQL是autocommit模式的,所有的数据库更新操作都会即时提交,所以在缺省情况下,MySQL是不支持事务的。但是如果你的MySQL表类型是使用 nnoDB Tables或BDB tables的话,你的MySQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MySQL允许在非autocommit模式,在非autocommit模式下,你必须使用COMMIT来提交你的更改,或者用ROLLBACK来回滚你的更改。

    14. 如何在 Unix 和 MySQL 时间戳之间进行转换?

    UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令
    FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令

    15. 列对比运算符是什么?

    在 SELECT 语句的列比较中使用=,<>,<=,<,> =,>,<<,>>,<=>,AND,OR 或 LIKE 运算符。

    16. LIKE 声明中的%和_是什么意思?

    %对应于 0 个或更多字符,_只是 LIKE 语句中的一个字符。

    17. MySQL_fetch_array 和 MySQL_fetch_object 的区别是什么?

    MySQL_fetch_array(): 将结果行作为关联数组或来自数据库的常规数组返回。
    MySQL_fetch_object :从数据库返回结果行作为对象。

    18. 怎样才能找出最后一次插入时分配了哪个自动增量?

    LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值,并且不需要指定表名称。

    19. MySQL 如何优化 DISTINCT?

    DISTINCT 在所有列上转换为 GROUP BY,并与 ORDER BY 子句结合使用。SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

    20. 实践中如何优化 MySQL?

    最好是按照以下顺序优化:
    SQL 语句及索引的优化
    数据库表结构的优化
    系统配置的优化
    硬件的优化

    21. 优化数据库的方法?

    选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置 NOTNULL,例如’省份’、’性别’最好适用枚举类型ENUM
    使用连接(JOIN)来代替子查询
    适用联合(UNION)来代替手动创建的临时表
    事务处理
    锁定表、优化事务处理
    适用外键,优化锁定表
    建立索引
    优化查询语句

    22. NOW()和 CURRENT_DATE()有什么区别?

    NOW()命令用于显示当前年份,月份,日期,小时,分钟和秒。
    CURRENT_DATE()仅显示当前年份,月份和日期。

    23. 简单描述 MySQL 中索引、主键、唯一索引、联合索引的区别,对数据库的性能有什么影响(从读写两方面)?

    索引是一种特殊的文件,包含着对数据表里所有记录的引用指针。索引可以极大的提高数据的查询速度,但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件。
    普通索引的唯一任务是加快对数据的访问速度,允许被索引的数据列包含重复的值。
    如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字 UNIQUE把它定义为一个唯一索引,唯一索引可以保证数据记录的唯一性。
    主键是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字PRIMARY KEY来创建。
    联合索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引。

    24. 什么是非标准字符串类型?

    TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

    25. BLOB 和 TEXT 有什么区别?

    BLOB 是一个二进制对象,可以容纳可变数量的数据。TEXT 是一个不区分大小写的 BLOB。
    BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写,对 TEXT 值不区分大小写。

    26. 什么是锁?

    数据库是一个多用户使用的共享资源,当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。基本锁类型:锁包括行级锁和表级锁

    27. MySQL 有关权限的表都有哪几个?

    MySQL 服务器通过权限表来控制用户对数据库的访问,权限表存放在 MySQL 数据库里,由 MySQL_install_db 脚本初始化。这些权限表分别 user,db,table_priv,columns_priv 和 host。

    28. MySQL 里记录货币用什么字段类型?

    NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型,这在 SQL92 标准允许。他们被用于保存值,该值的准确精度是极其重要的值,例如与金钱有关的数据。当声明一个类是这些类型之一时,精度和规模的能被(并且通常是)指定。
    例如(不用背,辅助理解的):
    salary DECIMAL(9,2)
    在这个例子中,9(precision)代表将被用于存储值的总的小数位数,而 2(scale)代
    表将被用于存储小数点后的位数。
    因此,在这种情况下,能被存储在 salary 列中的值的范围是从-9999999.99 到
    9999999.99。

    29. MyISAM 表格将在哪里存储,并且还提供其存储格式?

    每个 MyISAM 表格以三种格式存储在磁盘上:·“.frm”文件存储表定义·数据文件具有“.MYD”(MYData)扩展名索引文件具有“.MYI”(MYIndex)扩展名

    30. 列的字符串类型可以是什么?

    SET、BLOB、ENUM、CHAR、TEXT

    31. 完整性约束包括哪些?

    数据完整性是指数据的精确和可靠性,分为以下四类:
    实体完整性:规定表的每一行在表中是惟一的实体。
    域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括取值范围、精度等规定。
    参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。
    用户定义的完整性:用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。与表有关的约束:包括非空约束(NOT NULL)和表约束(PRIMARY KEY、foreign key、check、UNIQUE等等) 。

    32. 什么情况下设置了索引但无法使用?

    以“%”开头的 LIKE 语句,模糊匹配
    OR 语句前后没有同时使用索引
    数据类型出现隐式转化(如 varchar 不加单引号的话可能会自动转换为 int 型)

    33. SQL注入漏洞产生的原因?如何防止?

    SQL注入产生的原因:不注意规范书写sql语句和对特殊字符进行过滤,导致客户端可以通过全局变量POST和GE 提交一些sql语句正常执行。
    防止SQL注入的方式:
    Sql语句书写尽量不要省略双引号和单引号。
    过滤掉sql语句中的一些关键词:update、insert、delete、select、 * 。
    提高数据库表和字段的命名技巧,对一些重要的字段根据程序的特点命名,取不易被猜到的。
    对于常用的方法加以封装,避免直接暴漏SQL语句

    34. 索引的底层实现原理和优化?

    索引的底层实现原理是B+树,优化是经过优化的 B+树。主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

    35. 数据库中的事务是什么?

    事务是作为一个单元的一组有序的数据库操作,事务的特性在Spring框架中可分为ACID。如果组中的所有操作都成功,则认为事务成功,即使只有一个操作失败,事务也不成功。如果所有操作完成,事务则提交,其修改将作用于所有其他数据库进程。如果一个操作失败,则事务将回滚,该事务所有操作的影响都将取消。

    36. MyISAM Static 和 MyISAM Dynamic 有什么区别?

    在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXT,BLOB 等字段,以适应不同长度的数据类型。MyISAM Static 在受损情况下更容易恢复。

    37. 如何显示前 50 行?

    在 MySQL 中,使用以下代码查询显示前 50 行:SELECT * FROM LIMIT 0 , 50;

    38. 如何为表中得字段选择合适得数据类型?

    优先考虑数字类型,其次是日期或者二进制类型,最后是字符串类型
    对于同级别的数据类型,应该优先选择占用空间小的数据类型

    39. 索引的目的是什么?

    快速访问数据表中的特定信息,提高检索速度
    创建唯一性索引,保证数据库表中每一行数据的唯一性。
    加速表和表之间的连接
    使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间

    40. 什么是存储过程?用什么来调用?

    存储过程是一个预编译的 SQL 语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL,使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。

    41. 解释 MySQL 外连接、内连接与自连接的区别?

    交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。
    内连接则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中,即内连接只连接匹配的行。
    外连接的结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中的所有数据行,这三种情况依次称之为左外连接,右外连接和全外连接。
    左外连接也称左连接,左表为主表,左表中的所有记录都会出现在结果集中。对于那些在右表中并没有匹配的记录仍然要显示,右边对应的那些字段值以NULL 来填充。
    右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。
    左连接和右连接可以互换,MySQL 目前还不支持全外连接。

    42. 什么情况下不宜建立索引?

    对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。对于一些特殊的数据类型,不宜建立索引,比如文本字段(text)等

    43. SQL 语言包括哪几部分?每部分都有哪些操作关键字?

    SQL 语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询(DQL)
    数据定义(DDL)的关键字主要有:Create Table、Alter Table、Drop Table、Craete/Drop Index等
    数据操纵(DML)的关键字主要有:Select 、insert、update、delete,
    数据控制(DCL)的关键字主要有:grant、revoke
    数据查询(DQL)的关键字主要有:select

    44. 为数据表建立索引的原则有哪些?

    在最频繁使用的、用以缩小查询范围的字段上建立索引。在频繁使用的、需要排序的字段上建立索引

    45. 索引对数据库系统的负面影响是什么?

    创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加。
    索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间。
    当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。

    46. Myql 中的事务回滚机制概述?

    事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,事务回滚是指将该事务已经完成的对数据库的更新操作撤销。比如要同时修改数据库中表A和表B时,如果表A和B不是一个事务的话,当表A修改完而表B修改过程中出现了异常而没能修改。此时就只有表B是未修改之前的状态,而表A已经被修改完毕。如果把表A和B设定为一个事务的时候,当表A修改完,表B修改出现异常而没能修改,表A和B都要回到未修改的状态,这就是所谓的事务回滚

    47. 可以使用多少列创建索引?

    任何标准表最多可以创建 16 个索引列。

    48. 游标是什么?

    游标:是对查询出来的结果集作为一个单元来有效的处理,可以定在该单元中的特定行,从结果集的当前行检索一行或多行,可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

    49. 锁的优化策略?

    读写分离
    分段加锁
    减少锁持有的时间
    多个线程尽量以相同的顺序去获取资源不能将锁的粒度过于细化,不然可能会出现线程的加锁和释放次数过多,反而效率不如一次加一把大锁。

    50. 什么是基本表?什么是视图?

    基本表:是本身独立存在的表,在SQL中一个关系就对应一个表。
    视图:是一种虚拟的表,具有和物理表相同的功能,对视图的修改不影响基本表。可以进行增,改,查,操作,视图通常是有一个表或者多个表的行或列的子集。相比多表查询,视图使得获取数据更容易。

    51. 如何通俗地理解三个范式,说说范式化设计优缺点?

    第一范式(1NF):是对属性的原子性约束,要求属性具有原子性,不可再分解;
    第二范式(2NF):是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;
    第三范式(3NF):是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。。
    范式化的优点: 可以尽量得减少数据冗余,使得更新快,体积小。
    缺点:对于查询需要多个表进行关联,减少写操作的效率和增加读操作的效率,更难进行索引优化

    52. 试述视图的优点?

    视图能够简化用户的操作
    视图使用户能以多种角度看待同一数据;
    视图为数据库提供了一定程度的逻辑独立性;
    视图能够对机密数据提供安全保护。

    53. 说说对 SQL 语句优化有哪些方法?

    Where子句中:where表之间的连接必须写在其他 Where 条件之前,那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾和HAVING 最后。
    用EXISTS替代IN,用NOT EXISTS替 NOT IN。
    避免在索引列上使用计算、IS NULL和IS NOT NULL
    对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
    应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
    应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

    54. 主键、外键和索引的区别?

    定义上:主键–唯一标识一条记录,不能有重复的,不允许为空外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值索引–该字段没有重复值,但可以有一个空值
    作用上:主键–用来保证数据完整性外键–用来和其他表建立联系用的。索引–是提高查询排序的速度
    个数上:主键只能有一个;外一个表可以有多个外键;一个表可以有多个唯一索引

    55. 你可以用什么来确保表格里的字段只接受特定范围里的值?

    Check 限制,它在数据库表格里被定义,用来限制输入该列的值。触发器也可以被用来限制数据库表格里的字段能够接受的值,但是这种办法要求触发器在表格里被定义,这可能会在某些情况下影响到性能。


    有问题欢迎私信/评论指出,谢谢您的观看,希望对您有帮助哦!
    https://lianghongbin.blog.csdn.net/

    展开全文
  • MySQL面试题

    2018-12-14 21:18:50
    MySQL面试题,覆盖面重要的知识点,希望对您有所帮助。
  • 文章目录1、MySQL中有哪几种锁?2、MySQL中有哪些不同的表格?3、简述在MySQL数据库中MyISAM和InnoDB的区别4、MySQL中InnoDB支持的四种事务隔离级别名称,以及逐级之间的区别?5、CHAR和VARCHAR的区别?6、主键和...

    文章目录

    最近面试的小伙伴很多,对此我整理了一份Java面试题手册:基础知识、JavaOOP、Java集合/泛型面试题、Java异常面试题、Java中的IO与NIO面试题、Java反射、Java序列化、Java注解、多线程&并发、JVM、Mysql、Redis、Memcached、MongoDB、Spring、SpringBoot、SpringCloud、RabbitMQ、Dubbo、MyBatis、ZooKeeper、数据结构、算法、Elasticsearch、Kafka、微服务、Linux等等。可以分享给大家学习。【持续更新中】

    完整版Java面试题地址:【2021最新版】Java面试真题汇总

    序号内容地址链接
    1【2021最新版】JavaOOP面试题总结https://blog.csdn.net/m0_48795607/article/details/115288673
    2【2021最新版】Java基础面试题总结https://blog.csdn.net/m0_48795607/article/details/115485109
    3【2021最新版】多线程&并发面试题总结https://blog.csdn.net/m0_48795607/article/details/115489616
    4【2021最新版】JVM面试题总结https://blog.csdn.net/m0_48795607/article/details/115555086
    5【2021最新版】Redis面试题总结https://blog.csdn.net/m0_48795607/article/details/115642129
    6【2021最新版】Memcached面试题总结https://blog.csdn.net/m0_48795607/article/details/115664662
    7【2021最新版】MongoDB面试题总结https://blog.csdn.net/m0_48795607/article/details/115672336
    8【2021最新版】Spring面试题总结https://blog.csdn.net/m0_48795607/article/details/115738909
    9【2021最新版】Spring Boot面试题总结https://blog.csdn.net/m0_48795607/article/details/115771307
    10【2021最新版】Spring Cloud面试题总结https://blog.csdn.net/m0_48795607/article/details/115917190
    11【2021最新版】RabbitMQ面试题总结https://blog.csdn.net/m0_48795607/article/details/116064045
    12【2021最新版】Dubbo面试题总结https://blog.csdn.net/m0_48795607/article/details/116237861
    13【2021最新版】MyBatis面试题总结https://blog.csdn.net/m0_48795607/article/details/116427170
    14【2021最新版】ZooKeeper面试题总结https://blog.csdn.net/m0_48795607/article/details/116458096
    15【2021最新版】数据结构面试题总结https://blog.csdn.net/m0_48795607/article/details/116461620
    16【2021最新版】算法面试题总结https://blog.csdn.net/m0_48795607/article/details/116461620
    17【2021最新版】Elasticsearch面试题总结https://blog.csdn.net/m0_48795607/article/details/116656094
    18【2021最新版】Kafka面试题总结https://blog.csdn.net/m0_48795607/article/details/116659584
    19【2021最新版】微服务面试题总结https://blog.csdn.net/m0_48795607/article/details/116662109
    20【2021最新版】Linux面试题总结https://blog.csdn.net/m0_48795607/article/details/116798880

    1、MySQL中有哪几种锁?

    答:

    1、表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。

    2、行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

    3、页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

    2、MySQL中有哪些不同的表格?

    答:

    共有5种类型的表格:

    1、MyISAM

    2、Heap

    3、Merge

    4、INNODB

    5、MISAM

    3、简述在MySQL数据库中MyISAM和InnoDB的区别。

    4、MySQL中InnoDB支持的四种事务隔离级别名称,以及逐级之间的区别?

    答:

    1、read uncommited :读到未提交数据

    2、read committed:脏读, 不可重复读

    3、repeatable read:可重读

    4、serializable :串行事物

    5、CHAR和VARCHAR的区别?

    答:

    1、CHAR 和 VARCHAR 类型在存储和检索方面有所不同

    2、CHAR列长度固定为创建表时声明的长度,长度值范围是1到255当CHAR值被存储时,它们被用空格填充到特定长度,检索CHAR值时需删除尾随空格。

    6、主键和候选键有什么区别?

    答:

    表格的每一行都由主键唯一标识,一个表只有一个主键。

    主键也是候选键。按照惯例,候选键可以被指定为主键,并且可以用于任何外键引用。

    7、myisamchk是用来做什么的?

    8、如果一个表有一列定义为TIMESTAMP,将发生什么?

    9、你怎么看到为表格定义的所有索引?

    答:

    索引是通过以下方式为表格定义的:

    SHOW INDEX FROM

    10、LIKE声明中的%和_是什么意思?

    答:

    如何在Unix 和MySQL时间戳之间进行转换?UNIX_TIMESTAMP是从MySQL时间戳转换为Unix时间戳的命令FROM_UNIXTIME是从Unix时间戳转换为MySQL时间戳的命令。

    11、列对比运算符是什么?

    答:

    在SELECT语句的列比较中使用=,<>,<=,<,> =,>,<<,>>,<=>,AND,OR或LIKE运算符。

    12、BLOB和TEXT有什么区别?

    13、MySQL_fetch_array和MySQL_fetch_object的区别是什么?

    答:

    以下是MySQL_fetch_array和MySQL_fetch_object 的区别:

    MySQL_fetch_array( )– 将结果行作为关联数组或来自数据库的常规数组返回。

    MySQL_fetch_object–从数据库返回结果行作为对象。

    14、MyISAM表格将在哪里存储,并且还提供其存储格式?

    答:

    每个MyISAM表格以三种格式存储在磁盘上:

    “frm” 文件存储表定义。

    数据文件具有“MYD”( MYData)。

    扩展名索引文件具有“MYI”( MYIndex)扩展名。

    15、MySQL如何优化DISTINCT?

    答:

    DISTINCT在所有列上转换为GROUP BY,并与ORDER BY子句结合使用。SELECT DISTINCT t1.aFROM t1,t2 where t1.a=t2.a;

    16、如何显示前50行?

    答:

    在MySQL中,使用以下代码查询显示前50行:SELECT*FROM TABLE LIMIT 0,50;

    17、可以使用多少列创建索引?

    答:

    任何标准表最多可以创建16个索引列。

    18、NOW()和 CURRENT_DATE()有什么区别?

    答:
    NOW()命令用于显示当前年份,月份,日期,小时,分钟和秒。CURRENT_DATE() 仅显示当前年份,月份和日期。

    19、什么是非标准字符串类型?

    答:

    1、TINYTEXT

    2、TEXT

    3、MEDIUMTEXT

    4、LONGTEXT

    20、什么是通用SQL函数?

    答:

    1、CONCAT(A, B) –连接两个字符串值以创建单个字符串输出。通常用于将两个或多个字段合并为一个字段。

    2、FORMAT(X, D)- 格式化数字 X 到 D 有效数字。

    3、CURRDATE(), CURRTIME()- 返回当前日期或时间。

    4、NOW()–将当前日期和时间作为一个值返回。

    5、MONTH(),DAY( ),YEAR(),WEEK(),WEEKDAY()–从日期值中提取给定数据。

    6、HOUR(),MINUTE(),SECOND()–从时间值中提取给定数据。

    7、DATEDIFF( A, B)–确定两个日期之间的差异, 通常用于计算年龄。

    8、SUBTIMES( A, B)–确定两次之间的差异。

    9、FROMDAYS( INT –将整数天数转换为日期值。

    21、MySQL支持事务吗?

    答:

    在缺省模式下,MySQL是autocommit模式的,所有的数据库更新操作都会即时提交, 所以在缺省情况下,MySQL是不支持事务的。

    但是如果你的 MySQL 表类型是使用InnoDB Tables或BDB tables的话, 你的MySQL就可以使用事务处理,使用SETAUTOCOMMIT=0就可以使MySQL允许在非autocommit模式,在非autocommit模式下,你必须使用COMMIT 来提交你的更改,或者用ROLLBACK来回滚你的更改。

    22、MySQL里记录货币用什么字段类型好?

    答:

    NUMERIC和DECIMAL类型被MySQL实现为同样的类型,这在SQL92标准允许。他们被用于保存值, 该值的准确精度是极其重要的值,例如与金钱有关的数据。当声明一个类是这些类型之一时,精度和规模的能被(并且通常是)指定。

    例如:

    salary DECIMAL(9,2)在这个例子中,9(precision)代表将被用于存储值的总的小数位数,而2(scale)代表将被用于存储小数
    点后的位数。因此,在这种情况下,能被存储在 salary 列中的值的范围是从-9999999.99到9999999.99。

    23、MySQL有关权限的表都有哪几个?

    答:

    MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在MySQL数据库里,由MySQL_install_db脚本初始化。这些权限表分别user,db,table_priv,columns_priv和host 。

    24、列的字符串类型可以是什么?

    答:

    字符串类型是:

    1、SET

    2、BLOB

    3、ENUM

    4、CHAR

    5、TEXT

    25、MySQL数据库作发布系统的存储,一天五万条以上的增量, 预计运维三年,怎么优化?

    答:

    1、设计良好的数据库结构,允许部分数据冗余,尽量避免join查询, 提高效率。

    2、选择合适的表字段数据类型和存储引擎,适当的添加索引。

    3、MySQL库主从读写分离。

    4、找规律分表,减少单表中的数据量提高查询速度。

    5、添加缓存机制, 比如memcached,apc等。

    6、不经常改动的页面,生成静态页面。

    7、书写高效率的SQL。比如SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROMTABLE

    26、锁的优化策略

    答:

    1、读写分离

    2、分段加锁

    3、减少锁持有的时间

    4、多个线程尽量以相同的顺序去获取资源不能将锁的粒度过于细化,不然可能会出现线程的加锁和释放次数过多,反而效率不如一次加一把大锁。

    27、索引的底层实现原理和优化

    答:

    B+树,经过优化的B+树主要是在所有的叶子结点中增加了指向下一个叶子节点的指针, 因此InnoDB建议为大部分表使用默认自增的主键作为主索引。

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

    答:

    1、以“%” 开头的 LIKE 语句,模糊匹配

    2、OR语句前后没有同时使用索引

    3、数据类型出现隐式转化( 如varchar不加单引号的话可能会自动转换为int型)

    29、实践中如何优化MySQL?

    答:

    最好是按照以下顺序优化:

    1、SQL 语句及索引的优化

    2、数据库表结构的优化

    3、系统配置的优化

    4、硬件的优化

    30、优化数据库的方法

    答:

    1、选取最适用的字段属性,尽可能减少定义字段宽度,尽量把字段设置NOTNULL,例如’ 省份’、’ 性 别’ 最好适用ENUM

    2、使用连接(JOIN)来代替子查询

    3、适用联合(UNION)来代替手动创建的临时表

    4、事务处理

    5、锁定表、优化事务处理

    6、适用外键,优化锁定表

    7、建立索引

    8、优化查询语句

    31、简单描述MySQL中,索引,主键,唯一索引,联合索引的区别,对数据库的性能有什么影响(从读写两方面)

    答:

    索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分), 它们包含着对数据表里所有记录的引用指针。

    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

    普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值, 在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说,唯一索引可以保证数据记录的唯一性。

    主键,是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字PRIMARY KEY 来创建。

    索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引,这就是联合索引。

    索引可以极大的提高数据的查询速度, 但是会降低插入、删除、更新表的速度, 因为在执行这些写操作时,还要操作索引文件。

    32、数据库中的事务是什么?

    答:

    事务( transaction) 是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功,则认为事务成功,即使只有一个操作失败,事务也不成功。如果所有操作完成,事务则提交,其修改将作用于所有其他数据库进程。如果一个操作失败, 则事务将回滚,该事务所有操作的影响都将取消。

    事务特性:

    1、原子性:即不可分割性,事务要么全部被执行, 要么就全部不被执行。

    2、一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

    3、隔离性。在事务正确提交之前,不允许把该事务对数据的任何改变提供给任何其他事务

    4、持久性。事务正确提交后,其结果将永久保存在数据库中,即使在事务提交后有了其他故障,事务的处理结果也会得到保存。或者这样理解:事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组,如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态, 或者是上有个节点。为了确保要么执行,要么不执行,就可以使用事务。要将有组语句作为事务考虑,就需要通过ACID测试,即原子性,一致性,隔离性和持久性。

    33、SQL注入漏洞产生的原因?如何防止?

    答:

    SQL注入产生的原因:程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤,导致客户端可以通过全局变量POST 和 GET提交一些sql语句正常执行。防止SQL注入的方式:

    开启配置文件中的magic_quotes_gpc和magic_quotes_runtime设置执行sql语句时使用addslashes进行sql语句转换Sql语句书写尽量不要省略双引号和单引号。

    过滤掉sql语句中的一些关键词:update、insert、delete、select、* 。

    提高数据库表和字段的命名技巧,对一些重要的字段根据程序的特点命名,取不易被猜到的。

    34、为表中得字段选择合适得数据类型

    答:

    字段类型优先级:整形>date,time>enum,char>varchar>blob,text优先考虑数字类型,其次是日期或者二进制类型, 最后是字符串类型,同级别得数据类型,应该优先选择占用空间小的数据类型。

    35、存储时期

    答:

    Datatime:以 YYYY-MM-DD HH:MM:SS格式存储时期时间,精确到秒,占用8个字节得存储空间,datatime类型与时区无关

    Timestamp:以时间戳格式存储占用4个字节,范围小 1970-1-1到2038-1-19,显示依赖于所指定得时区,默认在第一个列行的数据修改时可以自动得修改timestamp列得值

    Date:( 生日)占用得字节数比使用字符串datatime.int储存要少,使用date只需要3个字节,存储日期月份,还可以利用日期时间函数进行日期间得计算Time:存储时间部分得数据

    注意:不要使用字符串类型来存储日期时间数据( 通常比字符串占用得储存空间小, 在进行查找过滤可以利用日期得函数)使用int存储日期时间不如使用 timestamp 类型

    36、对于关系型数据库而言,索引是相当重要的概念,请回答有关索引的几个问题:

    答:

    有关索引的几个问题:

    1、索引的目的是什么?

    快速访问数据表中的特定信息, 提高检索速度创建唯一性索引, 保证数据库表中每一行数据的唯一性。加速表和表之间的连接使用分组和排序子句进行数据检索时, 可以显著减少查询中分组和排序的时间。

    2、索引对数据库系统的负面影响是什么?

    负面影响:

    创建索引和维护索引需要耗费时间, 这个时间随着数据量的增加而增加;索引需要占用物理空间, 不光是表需要占用数据空间, 每个索引也需要占用物理空间;当对表进行增、删、改、的时候索引也要动态维护,这样就降低了数据的维护速度。

    3、为数据表建立索引的原则有哪些?

    在最频繁使用的、用以缩小查询范围的字段上建立索引。 在频繁使用的、需要排序的字段上建立索引。

    4、什么情况下不宜建立索引?

    对于查询中很少涉及的列或者重复值比较多的列,不宜建立索引。对于一些特殊的数据类型,不宜建立索引,比如文本字段( text)等。

    37、解释MySQL外连接、内连接与自连接的区别

    答:

    先说什么是交叉连接: 交叉连接又叫笛卡尔积,它是指不使用任何条件,直接将一个表的所有记录和另一个表中的所有记录一一匹配。内连接 则是只有条件的交叉连接,根据某个条件筛选出符合条件的记录,不符合条件的记录不会出现在结果集中, 即内连接只连接匹配的行。

    外连接 其结果集中不仅包含符合连接条件的行,而且还会包括左表、右表或两个表中的所有数据行,这三种情况依次称之为左外连接,右外连接,和全外连接。左外连接,也称左连接,左表为主表,左表中的所有记录都会出现在结果集中,对于那些在右表中并没有匹配的记录,仍然要显示,右边对应的那些字段值以NULL来填充。

    右外连接,也称右连接,右表为主表,右表中的所有记录都会出现在结果集中。左连接和右连接可以互换,MySQL目前还不支持全外连接。

    38、Myql中的事务回滚机制概述

    答:

    事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,是一个不可分割的工作单位,事务回滚是指将该事务已经完成的对数据库的更新操作撤销。要同时修改数据库中两个不同表时,如果它们不是一个事务的话,当第一个表修改完,可能第二个表修改过程中出现了异常而没能修改,此时就只有第二个表依旧是未修改之前的状态,而第一个表已经被修改完毕。

    而当你把它们设定为一个事务的时候,当第一个表修改完,第二表修改出现异常而没能修改,第一个表和第二个表都要回到未修改的状态, 这就是所谓的事务回滚。

    39、SQL语言包括哪几部分?每部分都有哪些操作关键字?

    答:

    SQL语言包括数据定义(DDL)、数据操纵(DML),数据控制(DCL)和数据查询( DQL)四个部分。

    数据定义:Create Table,Alter Table,Drop Table, Craete/Drop Index

    等数据操纵:Select,insert,update,delete,

    数据控制: grant,revoke 数据查询:select

    40、完整性约束包括哪些?

    答:

    数据完整性(Data Integrity)是指数据的精确(Accuracy)和可靠性(Reliability)。

    分为以下四类:

    1、实体完整性:规定表的每一行在表中是惟一的实体。

    2、域完整性:是指表中的列必须满足某种特定的数据类型约束,其中约束又包括 取值范围、精度等规定。

    3、参照完整性:是指两个表的主关键字和外关键字的数据应一致,保证了表之间的数据的一致性,防止了数据丢失或无意义的数据在数据库中扩散。

    4、用户定义的完整性:不同的关系数据库系统根据其应用环境的不同,往往还需 要一些特殊的约束条件。用户定义的完整性即是针对某个特定关系数据库的约束条件,它反映某一具体应用必须满足的语义要求。

    5、与表有关的约束:包括列约束(NOT NULL( 非空约束))和表约束(PRIMARY KEY、foreign key、check、UNIQUE) 。

    41、什么是锁?

    答:

    数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时,在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性。

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前,先向系统发出请求,对其加锁。加锁后事务就对该数据对象有了一定的控制,在该事务释放锁之前,其他的事务不能对此数据对象进行更新操作。

    基本锁类型:锁包括行级锁和表级锁。

    42、什么叫视图?游标是什么?

    答:

    视图是一种虚拟的表,具有和物理表相同的功能。可以对视图进行增,改,查,操作,视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易,相比多表查询。

    游标:是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

    43、什么是存储过程?用什么来调用?

    答:
    存储过程是一个预编译的 SQL 语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次 SQL,使用存储过程比单纯 SQL 语句执行要快。可以用一个命令对象来调用存储过程。

    44、如何通俗地理解三个范式?

    答:

    第一范式:1NF 是对属性的原子性约束,要求属性具有原子性,不可再分解;

    第二范式:2NF 是对记录的惟一性约束,要求记录有惟一标识,即实体的惟一性;

    第三范式:3NF 是对字段冗余性的约束,即任何字段不能由其他字段派生出来,它要求字段没有冗余。

    范式化设计优缺点:

    优点:

    可以尽量得减少数据冗余,使得更新快,体积小。

    缺点:对于查询需要多个表进行关联,减少写得效率增加读得效率,更难进行索引优化。

    反范式化:

    优点:可以减少表得关联,可以更好得进行索引优化。

    缺点:数据冗余以及数据异常,数据得修改需要更多的成本。

    45、什么是基本表?什么是视图?

    答:
    基本表是本身独立存在的表,在SQL中一个关系就对应一个表。 视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中,是一个虚表。

    46、试述视图的优点?

    答:

    (1) 视图能够简化用户的操作

    (2) 视图使用户能以多种角度看待同一数据;

    (3) 视图为数据库提供了一定程度的逻辑独立性;

    (4) 视图能够对机密数据提供安全保护

    47、NULL是什么意思

    答:

    NULL这个值表示 UNKNOWN(未知):它不表示“”(空字符串)。对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个NULL值进行比较,并在逻辑上希望获得一个答案。使用IS NULL来进行NULL判断

    48、主键、外键和索引的区别?

    答:

    主键、外键和索引的区别

    定义:

    主键–唯一标识一条记录,不能有重复的,不允许为空外键–表的外键是另一表的主键, 外键可以有重复的, 可以是空值索引–该字段没有重复值,但可以有一个空值

    作用:

    主键–用来保证数据完整性外键–用来和其他表建立联系用的索引–是提高查询排序的速度

    个数:

    主键–主键只能有一个外键–一个表可以有多个外键索引–一个表可以有多个唯一索引

    49、你可以用什么来确保表格里的字段只接受特定范围里的值?

    答:

    Check限制,它在数据库表格里被定义,用来限制输入该列的值。触发器也可以被用来限制数据库表格里的字段能够接受的值,但是这种办法要求触发器在表格里被定义,这可能会在某些情况下影响到性能。

    50、说说对SQL语句优化有哪些方法?(选择几条)

    答:

    1、Where 子句中:where表之间的连接必须写在其他Where条件之前,那些可以过滤掉最大数量记录的条件必须写在Where 子句的末尾HAVING最后。

    2、用EXISTS替代IN、用NOT EXISTS替代NOT IN。

    3、 避免在索引列上使用计算

    4、避免在索引列上使用IS NULL和IS NOT NULL

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

    6、应尽量避免在where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描

    7、应尽量避免在where子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描

    51、唯一索引比普通索引快吗, 为什么?

    答:

    唯一索引不一定比普通索引快, 还可能慢。

    1. 查询时, 在未使用limit 1的情况下, 在匹配到一条数据后, 唯一索引即返回, 普通索引会继续匹配下一条数据, 发现不匹配后返回. 如此看来唯一索引少了一次匹配, 但实际上这个消耗微乎其微。

    2. 更新时, 这个情况就比较复杂了. 普通索引将记录放到change buffer中语句就执行完毕了,而对唯一索引而言, 它必须要校验唯一性, 因此, 必须将数据页读入内存确定没有冲突, 然后才能继续操作. 对于写多读少的情况, 普通索引利用change buffer有效减少了对磁盘的访问次数, 因此普通索引性能要高于唯一索引。

    52、MySQL由哪些部分组成, 分别用来做什么

    答:

    1. Server

    连接器: 管理连接, 权限验证

    分析器: 词法分析, 语法分析

    优化器: 执行计划生成, 索引的选择

    执行器: 操作存储引擎, 返回执行结果

    1. 存储引擎: 存储数据, 提供读写接口

    53、MySQL查询缓存有什么弊端, 应该什么情况下使用, 8.0版本对查询缓存有什么变更

    答:

    查询缓存可能会失效非常频繁, 对于一个表, 只要有更新, 该表的全部查询缓存都会被清空. 因此对于频繁更新的表来说, 查询缓存不一定能起到正面效果,对于读远多于写的表可以考虑使用查询缓存8.0版本的查询缓存功能被删了。

    54、MyISAM和InnoDB**的区别有哪些?

    答:

    InnoDB支持事务, MyISAM不支持。

    InnoDB支持行级锁, MyISAM支持表级锁。

    InnoDB支持多版本并发控制(MVVC), MyISAM不支持。

    InnoDB支持外键, MyISAM不支持。

    MyISAM支持全文索引, InnoDB部分版本不支持(但可以使用Sphinx插件)。

    55、MySQL怎么恢复半个月前的数据?

    答:

    通过整库备份+binlog进行恢复. 前提是要有定期整库备份且保存了binlog日志。

    56、MySQL事务的隔离级别, 分别有什么特点?

    答:

    1. 读未提交(RU): 一个事务还没提交时, 它做的变更就能被别的事务看到。

    2. 读提交(RC): 一个事务提交之后, 它做的变更才会被其他事务看到。

    3. 可重复读(RR): 一个事务执行过程中看到的数据, 总是跟这个事务在启动时看到的数据是一致的. 当然在可重复读隔离级别下, 未提交变更对其他事务也是不可见的。

    4. 串行化(S): 对于同一行记录, 读写都会加锁. 当出现读写锁冲突的时候, 后访问的事务必须等前一个事务执行完成才能继续执行。

    57、做过哪些MySQL索引相关优化?

    答:

    尽量使用主键查询: 聚簇索引上存储了全部数据, 相比普通索引查询, 减少了回表的消耗。

    MySQL5.6之后引入了索引下推优化, 通过适当的使用联合索引, 减少回表判断的消耗。

    若频繁查询某一列数据, 可以考虑利用覆盖索引避免回表,联合索引将高频字段放在最左边。

    58、简要说一下数据库范式

    答:

    第一范式: 属性不可再分

    第二范式:在一范式的基础上,要求数据库表中的每个实例或行必须可以被惟一地区分,通常需要为表加上一个列,以存储各个实例的惟一标识,这个惟一属性列被称为主关键字或主键。

    第三范式:在二范式的基础上, 要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

    所以第三范式具有如下特征:

    1). 每一列只有一个值

    2). 每一行都能区分

    3). 每一个表都不包含其

    他表已经包含的非主关键字信息

    59、一千万条数据的表, 如何分页查询?

    答:

    数据量过大的情况下,limit offset 分页会由于扫描数据太多而越往后查询越慢,可以配合当前页最后一条ID进行查询,SELECT * FROM T WHERE id > #{ID} LIMIT #{LIMIT} 。当然,这种情况下ID必须是有序的,这也是有序ID的好处之一。

    60、订单表数据量越来越大导致查询缓慢, 如何处理?

    答:

    分库分表. 由于历史订单使用率并不高, 高频的可能只是近期订单, 因此, 将订单表按照时间进行拆分, 根据数据量的大小考虑按月分表或按年分表. 订单ID最好包含时间(如根据雪花算法生成), 此时既能根据订单ID直接获取到订单记录, 也能按照时间进行查询。

    61、Mysql的技术特点是什么?

    答:

    Mysql 数据库软件是一个客户端或服务器系统,其中包括:支持各种客户端程序和库的多 线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理工具。

    62、Heap表是什么?

    答:

    HEAP表存在于内存中,用于临时高速存储。

    BLOB或TEXT字段是不允许的。

    只能使用比较运算符=<,>,=>,= <。

    HEAP表不支持AUTO_INCREMENT。

    索引不可为NULL。

    63、Mysql服务器默认端口是什么?

    答:

    Mysql服务器的默认端口是3306。

    64、与Oracle相比,Mysql有什么优势?

    答:

    Mysql是开源软件,随时可用,无需付费。

    Mysql是便携式的

    带有命令提示符的GUI。

    使用 Mysql查询浏览器支持管理

    65、如何区分FLOAT和DOUBLE?

    答:

    以下是FLOAT和 DOUBLE的区别:

    浮点数以8位精度存储在FLOAT中,并且有四个字节。 浮点数存储在DOUBLE中,精度为18位,有八个字节。

    66、区分CHAR_LENGTH和LENGTH?

    答:

    CHAR_LENGTH是字符数,而LENGTH是字节数。Latin 字符的这两个数据是相同的, 但是对于Unicode和其他编码,它们是不同的。

    67、请简洁描述Mysql中InnoDB支持的四种事务隔离级别名 称,以及逐级之间的区别?

    答:

    SQL标准定义的四个隔离级别为:

    1.read uncommited :读到未提交数据

    2.read committed:脏读,不可重复读

    3.repeatable read:可重读

    4.serializable :串行事物

    在这里插入图片描述

    68、在Mysql中ENUM的用法是什么?

    答:

    ENUM是一个字符串对象,用于指定一组预定义的值,并可在创建表时使用。

    Createtablesize(nameENUM('Smail,‘Medium’,‘Large’)。

    69、如何定义REGEXP?

    答:

    REGEXP 是模式匹配,其中匹配模式在搜索值的任何位置。

    70、CHAR和VARCHAR的区别?

    答:

    以下是CHAR和VARCHAR的区别:

    CHAR和 VARCHAR类型在存储和检索方面有所不同

    CHAR列长度固定为创建表时声明的长度,长度值范围是1到255当CHAR值被存储时,它们被用空格填充到特定长度,检索 CHAR值时需删除尾随空格。

    71、列的字符串类型可以是什么?

    答:

    字符串类型是:

    SET

    BLOB

    ENUM

    CHAR

    TEXT

    VARCHAR

    72、如何获取当前的Mysql版本?

    答:

    SELECT VERSION();用于获取当前Mysql的版本。

    73、Mysql中使用什么存储引擎?

    答:

    存储引擎称为表类型,数据使用各种技术存储在文件中。

    技术涉及:

    1.Storage mechanism

    2.Locking levels

    3.Indexing

    4.Capabilities and functions。

    74、Mysql驱动程序是什么?

    答:

    以下是 Mysql 中可用的驱动程序:

    1.PHP驱动程序

    2.JDBC 驱动程序

    3.ODBC 驱动程序

    4.CWRAPPER

    5.PYTHON 驱动程序

    6.PERL 驱动程序

    7.RUBY 驱动程序

    8.CAP11PHP 驱动程序

    9.Ado.net5.mxj

    75、TIMESTAMP在UPDATE CURRENT_TIMESTAMP数据类型上做什么?

    答:

    创建表时TIMESTAMP列用Zero更新。只要表中的其他字段发生更改,UPDATE CURRENT_TIMESTAMP修饰符就将时间戳字段更新为当前时间。

    76、主键和候选键有什么区别?

    答:

    表格的每一行都由主键唯一标识,一个表只有一个主键。 主键也是候选键。按照惯例,候选键可以被指定为主键,并且可以用于任何外键引用。

    77、如何使用Unix shell登录Mysql?

    答:

    我们可以通过以下命令登录: # [mysql dir]/bin/mysql -h hostname -u -p

    78、myisamchk是用来做什么的?

    答:

    它用来压缩MyISAM表,这减少了磁盘或内存使用。

    79、MYSQL数据库服务器性能分析的方法命令有哪些?

    答:

    80、如何控制HEAP表的最大尺寸?

    答:

    Heal表的大小可通过称为max_heap_table_size的Mysql配置变量来控制。

    81、MyISAM Static和MyISAM Dynamic有什么区别?

    答:

    在MyISAM Static上的所有字段有固定宽度。动态MyISAM表将具有像TEXT,BLOB等字段,以适应不同长度的数据类型。点击这里有一套最全阿里面试题总结。 MyISAM Static在受损情况下更容易恢复。

    82、federated表是什么?

    答:

    federated表,允许访问位于其他服务器数据库上的表。

    83、如果一个表有一列定义为TIMESTAMP,将发生什么?

    答:

    每当行被更改时,时间戳字段将获取当前时间戳。

    84、列设置为AUTO INCREMENT时,如果在表中达到最大值,会发生什么情况?

    答:

    它会停止递增,任何进一步的插入都将产生错误,因为密钥已被使用。

    答:
    LAST_INSERT_ID将返回由Auto_increment 分配的最后一个值,并且不需要指定表名称。

    85、怎样才能找出最后一次插入时分配了哪个自动增量?

    答:

    LAST_INSERT_ID将返回由Auto_increment分配的最后一个值,并且不需要指定表名称。

    86、你怎么看到为表格定义的所有索引?

    答:

    索引是通过以下方式为表格定义的: SHOW INDEX FROM ;

    87、LIKE声明中的%和_是什么意思?

    答:

    %对应于0个或更多字符,_只是 LIKE语句中的一个字符。

    88、如何在Unix和Mysql时间戳之间进行转换?

    答:

    UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的命令FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令。

    89、列对比运算符是什么?

    答:

    在SELECT语句的列比较中使用=,<>,<=,<,> =,>,<<,>>,<=>,AND,OR 或LIKE 运算符。

    90、我们如何得到受查询影响的行数?

    答:

    行数可以通过以下代码获得: SELECT COUNT(user_id)FROM users;

    91、Mysql 查询是否区分大小写?

    答:

    不区分SELECT VERSION(), CURRENT_DATE; SeLect version(), current_date; seleCt vErSiOn(), current_DATE; 所有这些例子都是一样的,Mysql不区分大小写。

    92、LIKE和REGEXP操作有什么区别?

    答:

    LIKE和REGEXP运算符用于表示^和%。

    SELECT * FROM employee WHERE emp_name REGEXP “^b”;

    SELECT * FROM employee WHERE emp_name LIKE “%b”;

    93、BLOB和TEXT有什么区别?

    答:

    BLOB是一个二进制对象,可以容纳可变数量的数据。

    有四种类型的

    BLOB-TINYBLOB

    BLOB

    MEDIUMBLOB

    LONGBLOB

    它们只能在所能容纳价值的最大长度上有所不同。

    TEXT是一个不区分大小写的BLOB。

    四种TEXT类型

    TINYTEXT

    TEXT

    MEDIUMTEXT

    LONGTEXT

    它们对应于四种BLOB类型,并具有相同的最大长度和存储要求。 BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写,对TEXT值不区分大小写。

    94、mysql_fetch_array和mysql_fetch_object的区别是什么?

    答:

    以下是mysql_fetch_array和mysql_fetch_object的区别:mysql_fetch_array() - 将结果行作为关联数组或来自数据库的常规数组返回。mysql_fetch_object -从数据库返回结果行作为对象。

    95、我们如何在mysql中运行批处理模式?

    答:

    以下命令用于在批处理模式下运行:

    mysql;

    mysql mysql.out

    96、MyISAM表格将在哪里存储,并且还提供其存储格式?

    答:

    每个MyISAM 表格以三种格式存储在磁盘上:“frm”文件存储表定义 数据文件具有“MYD”(MYData)扩展名索引文件具有“MYI”(MYIndex)扩展名

    97、Mysql中有哪些不同的表格?

    答:

    共有5种类型的表格:

    1.MyISAM

    2.Heap

    3.Merge

    4.INNODB

    5.ISAM

    MyISAM是Mysql的默认存储引擎。

    98、ISAM是什么?

    答:

    ISAM简称为索引顺序访问方法。它是由IBM开发的,用于在磁带等辅助存储系统上存储和检索数据。

    99、InnoDB是什么?

    答:

    lnnoDB是一个由Oracle公司开发的Innobase Oy事务安全存储引擎。

    100、Mysql如何优化DISTINCT?

    答:

    DISTINCT在所有列上转换为GROUP BY,并与ORDER BY子句结合使用。 SELECT DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;

    101、如何输入字符为十六进制数字?

    答:

    如果想输入字符为十六进制数字,可以输入带有单引号的十六进制数字和前缀(X),或者 只用(Ox)前缀输入十六进制数字。 如果表达式上下文是字符串,则十六进制数字串将自动转换为字符串。

    102、如何显示前50行?

    答:

    在Mysql中,使用以下代码查询显示前50行:

    SELECT*FROM

    LIMIT 0,50;

    103、可以使用多少列创建索引?

    答:

    任何标准表最多可以创建16个索引列。

    104、NOW()和CURRENT_DATE()有什么区别?

    答:

    NOW()命令用于显示当前年份,月份,日期,小时,分钟和秒。 CURRENT_DATE()仅显示当前年份,月份和日期。

    105、什么样的对象可以使用CREATE语句创建?

    答:

    以下对象是使用CREATE语句创建的:

    1.DATABASE

    2.EVENT

    3.FUNCTION

    4.INDEX

    5.PROCEDURE

    6.TABLE

    7.TRIGGER

    8.USER

    9.VIEW

    106、Mysql表中允许有多少TRIGGERS?

    答:

    在 Mysql表中允许有六个触发器,如下:

    1.BEFORE INSERT

    2.AFTER INSERT

    3.BEFORE UPDATE

    4.AFTER UPDATE

    5.BEFORE DELETE

    6.AFTER DELETE

    107、什么是非标准字符串类型?

    答:

    以下是非标准字符串类型:

    1.TINYTEXT

    2.TEXT

    3.MEDIUMTEXT

    4.LONGTEXT

    108、什么是通用SQL函数?

    答:

    CONCAT(A, B) -连接两个字符串值以创建单个字符串输出。通常用于将两个或多个 字段合并为一个字段。

    FORMAT(X, D)-格式化数字 X 到 D 有效数字。

    CURRDATE(), CURRTIME()- 返回当前日期或时间。

    NOW()-将当前日期和时间作为一个值返回。

    MONTH(),DAY(),YEAR(),WEEK(),WEEKDAY() - 从日期值中 提取给定数据。

    HOUR(),MINUTE(),SECOND()- 从时间值中提取给定数据。

    DATEDIFF(A,B)-确定两个日期之间的差异,通常用于计算年龄。

    SUBTIMES(A,B)-确定两次之间的差异。

    FROMDAYS(INT)-将整数天数转换为日期值。

    109、解释访问控制列表

    答:

    ACL(访问控制列表)是与对象关联的权限列表。这个列表是Mysql服务器安全模型的基 础,它有助于排除用户无法连接的问题。 Mysql将ACL(也称为授权表)缓存在内存中。当用户尝试认证或运行命令时,Mysql会按照预定的顺序检查ACL的认证信息和权限。

    111、MYSQL支持事务吗?

    答:

    在缺省模式下,MYSQL是autocommit模式的,所有的数据库更新操作都会即时提交, 所以在缺省情况下,mysql是不支持事务的。

    但是如果你的MYSQL表类型是使用InnoDBTables或BDB tables的话,你的MYSQL就可以使用事务处理 , 使用SETAUTOCOMMIT=0就可以使MYSQL允许在非autocommit模式,在非autocommit模式下,你必须使用COMMIT来提交你的更改,或者用ROLLBACK来回滚你的更改。示例如下:START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summmary=@A WHERE type=1; COMMIT。

    112、mysql里记录货币用什么字段类型好

    答:

    NUMERIC和DECIMAL类型被Mysql实现为同样的类型,这在SQL92标准允许。他们被用于保存值,该值的准确精度是极其重要的值,例如与金钱有关的数据。当声明一个类是 这些类型之一时,精度和规模的能被(并且通常是)指定;点击这里有一套最全阿里面试题总结。

    例如:

    salary DECIMAL(9,2) 在这个例子中,9(precision)代表将被用于存储值的总的小数位数,而2(scale)代表将被用于存储小数点后的位数。因 此,在这种情况下,能被存储在salary列中的值的范围是从-9999999.99到9999999.99。

    在ANSI/ISO SQL92中,句法DECIMAL§等价于DECIMAL(p,0)。 同样,句法DECIMAL等价于DECIMAL(p,0),这里实现被允许决定值p。Mysql当前不 支持DECIMAL/NUMERIC数据类型的这些变种形式的任一种。 这一般说来不是一个严重的问题,因为这些类型的主要益处得自于明显地控制精度和规模的 能力。 DECIMAL和NUMERIC值作为字符串存储,而不是作为二进制浮点数,以便保存那些值的小数精度。一个字符用于值的每一位、小数点(如果 scale>0)和“-”符号(对于负值)。

    如果scale是0,DECIMAL和NUMERIC值不包含小数点或小数部分。DECIMAL和NUMERIC值得最大的范围与DOUBLE一样,但是对于一个给定的DECIMAL或NUMERIC列,实际的范围可由制由给定列的precision或scale限制。 当这样的列赋给了小数点后面的位超过指定scale所允许的位的值,该值根据scale四舍五入。当一个DECIMAL或NUMERIC列被赋给了其大小超过指定(或缺省的)precision和scale隐含的范围的值,Mysql存储表示那个范围的相应的端点值。

    113、MYSQL数据表在什么情况下容易损坏?

    答:

    服务器突然断电导致数据文件损坏。 强制关机,没有先关闭mysql服务等。

    114、mysql有关权限的表都有哪几个?

    答:

    Mysql服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化。这些权限表分别user,db,table_priv,columns_priv和host。

    115、Mysql 中有哪几种锁?

    答:

    MyISAM支持表锁,InnoDB支持表锁和行锁,默认为行锁 表级锁:开销小,加锁快,不会出现死锁。锁定粒度大,发生锁冲突的概率最高,并发量最 低行级锁:开销大,加锁慢,会出现死锁。锁力度小,发生锁冲突的概率小,并发度最高。

    总结

    该面试题答案解析完整文档获取方式:Mysql面试题总结

    展开全文
  • 数据库 DBA
  • mysql 面试题及答案 篇一 php 经典面试题及答案非常实用 >目录 面试题 一 . . 1 面试题 二 . . 3 面试题 三 . . 4 面试题 四 . . 6 面试题 五 . . 7 面试题 六 . . 8 面试题 七 .
  • 【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题【BAT必备】MySQL面试题...
  • mysql 面试题汇总

    万次阅读 多人点赞 2019-01-24 22:42:47
      为了能够在面试回答中优雅而不失体面回答面试考点,该文章借鉴了不同平台对知识点的描述。 如有侵权请联系我 文章的不足和错误请指正,好的建议也不要吝啬,我都会采纳并更正 您的点赞是我持续更新的动力 蓝字...
  • MySQL面试题全集

    千次阅读 2020-06-26 16:59:20
    这篇文章源自于对秋招面试点的复习,在阿里数据大佬的提点下,总结MySQL面试知识点主要有以下几点,以后要是想到还会不定时更新,其中知识大部分来自于书本、视频和优秀博客的总结(文章超长预警)。 1 事务 ...
  • Mysql经典面试题答案

    2018-09-18 10:52:24
    Mysql经典面试题答案,富含很多bat面试题,经典实用。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 94,246
精华内容 37,698
关键字:

mysql面试题

mysql 订阅