精华内容
下载资源
问答
  • mysql面试必会6题经典
    千次阅读
    2021-01-28 08:40:35

    delete from salary where not exists (

    select wid from worker where worker.wid = salary.wid)

    a. 直接创建索引和间接创建索引

    直接创建:使用sql语句创建

    间接创建:定义主键约束或者唯一性键约束,可以间接创建索引,主键默认为唯一索引。

    b. 普通索引和唯一性索引

    普通索引:

    CREATE INDEX mycolumn_index ON mytable (myclumn)

    唯一性索引:保证在索引列中的全部数据是唯一的,对聚簇索引和非聚簇索引都可以使用:

    CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

    c. 单个索引和复合索引

    单个索引:索引建立语句中仅包含单个字段,如上面的普通索引和唯一性索引创建示例。

    复合索引:又叫组合索引,在索引建立语句中同时包含多个字段:

    CREATE INDEX name_index ON username(firstname, lastname)

    其中firstname为前导列。

    d. 聚簇索引和非聚簇索引(聚集索引,群集索引)

    聚簇索引:物理索引,与基表的物理顺序相同,数据值的顺序总是按照顺序排列:

    CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW

    其中WITH ALLOW_DUP_ROW表示允许有重复记录的聚簇索引。

    非聚簇索引:

    CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)

    索引默认为非聚簇索引。

    a. 对于复合索引,把使用最频繁的列做为前导列(索引中第一个字段)。如果查询时前导列不在查询条件中则该复合索引不会被使用。如:

    create unique index PK_GRADE_CLASS on student (grade, class)

    select * from student where class = 2 未使用到索引

    select * from dept where grade = 3 使用到了索引

    b. 避免对索引列进行计算,对where子句列的任何计算如果不能被编译优化,都会导致查询时索引失效。

    c. 比较值避免使用NULL

    d. 多表查询时要注意是选择合适的表做为内表。连接条件要充份考虑带有索引的表、行数多的表,内外表的选择可由公式:外层表中的匹配行数*内层表中每一次查找的次数确定,乘积最小为最佳方案。实际多表操作在被实际执行前,查询优化器会根据连接条件,列出几组可能的连接方案并从中找出系统开销最小的最佳方案。

    e. 查询列与索引列次序一致。

    f. 用多表连接代替EXISTS子句。

    g. 把过滤记录数最多的条件放在最前面。

    h. 善于使用存储过程,它使sql变得更加灵活和高效。

    update B set B.value =

    (select A.value from A where A.key = B.key)

    where B.id in(select B.id from B, A where B.key = A.key);

    事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组,如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态,或者是上有个节点。为了确保要么执行,要么不执行,就可以使用事务。要将有组语句作为事务考虑,就需要通过ACID测试,即原子性,一致性,隔离性和持久性。

    在所以的DBMS中,锁是实现事务的关键,锁可以保证事务的完整性和并发性。与现实生活中锁一样,它可以使某些数据的拥有者,在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

    select * from EMPLOYEE where FIRST_NAME not in ('John','Roy');

    游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

    数据库一般的操作是集合操作,也就是对由WHERE子句和选择列确定的结果集作集合操作,游标是提供的一个非集合操作的途径。一般情况下,游标实现的功能往往相当于客户端的一个循环实现的功能,所以,大部分情况下,我们把游标功能搬到客户端。

    游标是把结果集放在服务器内存,并通过循环一条一条处理记录,对数据库资源(特别是内存和锁资源)的消耗是非常大的,所以,我们应该只有在没有其他方法的情况下才使用游标。

    select cid, cname, score, (case when score<60 then 'fail' else 'pass' end) as mark from course;

    更多相关内容
  • 50条面试必会SQL语句

    2017-10-27 12:16:24
    50条面试必会SQL语句,基本涵盖常用的增删改查,每次面试前看一看
  • Mysql经典面试题汇总

    千次阅读 2020-07-13 21:16:38
    答: mysql的ACID是指事务的原子性,一致性,隔离性,持久性。其中原子性是基于 Redo/undo log机制来实现的, Redolog记录的是事务更新后的记录值,undo log记录的是事务更新后的记录,更新失败后,就可以根据undo ...

    1.ACID 是什么?

           答:  mysql的ACID是指事务的 原子性,一致性,隔离性,持久性。 其中原子性是基于 Redo/undo log机制来实现的, Redo log 记录的是事务更新后的记录值,undo log 记录的是事务更新后的记录,更新失败后,就可以根据undo log来进行回滚。

    原子性:   一个事务必须视为一个不可分割的最小工作单元,整个事务里的操作要么全部执行成功,要么全部失败。

    一致性:   事务总是从一个一致性状态转换到另一个一致性状态。

    隔离性:  一个事务所做的修改,在最终提交之前,其他事务是不可见的。

    持久性:  事务里的所有操作最终会写入到数据库里。

    2. mysql的索引类型包含哪几种?

     1) fulltext 索引

          适用于大文件text类型以及大小超过text类型的字段。 

     2) NORMAL普通索引

          使用经常使用的字段或者当关联表的一些字段。

     3) UNIQUE 唯一性索引

            当有多个字段一起表示唯一时,可以使用唯一性索引进行约束,比如用户表里的, 用户名和账号需要唯一并且不为空,那么就将 这2个字段设置一个唯一性的索引。

            需要注意的是,联合字段做唯一性索引时,做为索引的字段必须为非空,否则会出现唯一性索引失效的情况,例如a、b、c三个字段联合做唯一性索引,c允许为空,当出现如下情况时,mysql也是允许的,当c允许为null时联合的唯一性索引失效。

    abc
    21null
    21null

    3. mysql的explain关键字用法?

          一般用于在查询前添加explain,用于解释查询出来的语句包含的信息。

    4. mysql的binLog是什么? 

          binlog是mysql用于数据收集和数据恢复的日志管理工具。

    5. 你知道mysql的最左索引匹配规则嘛?

         答:  当有多个字段共同做为 索引时,如(a,b,c),如果说只用到了b,c那么最左索引匹配就会认为该索引无效,如果用到了a,c或者a,b,那么索引就会生效。

    6.  mysql的锁有哪几种?

         答: 根据加锁的范围,可分为全局锁(全库逻辑备份)、表级锁(表锁(表记录)、元数据锁MDL(修改表结构))、行锁。

    全局锁: 

            给整个数据库加一个全局的读锁,表示只有当前线程能够读取到该数据库,其他线程不能进行读取和修改。

          格式:   Full tables with read lock

    表级锁: 

         表锁和元数据锁(MDL)

          格式:   lock tables 表名 read;

        设置表锁后,不能对该表进行修改操作。 

    行锁:   

         两段锁、 一致性非锁定读(MVCC多版本并发控制, innodb会用快照的形式保存历史信息)、一致性锁定读(可以解决幻读问题)。

         一致性锁定读:

           select .. for update    

    共享锁

            多个不同的事务对于同一个共享资源使用一个共享锁。

           select ... lock in share mode

            可以通过加一致性锁定读,当2次查询期间有insert语句过来的时候,会进入到阻塞状态,直至读取完毕后,再insert。

    • Record Lock:单个行记录上的锁
    • Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
    • Next-Key Lock:Gap Lock+Record Lock,锁定一个范围,并且锁定记录本身

    表锁和行锁的应用场景

            表级锁使用场景以查询为主,行级锁适用于事务要求较高的系统。

    7. mysql常用命令?

    1) 查看系统参数

             使用命令 show variables like "%params%"   ;

             ①检测mysql检测死锁机制是否开启5.7:  

    show variables like "%innodb_deadlock_detect%"

      该命令表示如果开启死锁检测机制,那么出现死锁,会随机自动释放一个在死锁的事务。

         

            ② 查询binlog变量

     show variables like "%binlog%";

    2)查询数据库事务的隔离级别

           select @@transaction_isolation     mysql默认的事务隔离级别为: 可重复读。

          

    3) explain命令, 解释sql

          例如可以查看查询的语句是否用到索引

    8. mysql的MYISAM存储引擎和Innodb存储引擎有什么区别,哪个性能好?

            答:   区别:

                   1) myisam存储引擎不支持事务,对于不需要事务的操作时,myisam的查询和插入的性能比innodb快的多。

                   2)  myisam 不支持外键,innodb支持外键。

                   3) myisam 只支持表级锁,Innodb支持表级锁(表锁和元数据锁)和行锁(两阶段锁、一致性非锁定读(MVCC)、一致性锁定读(select … for update,select … lock in share mode))。

    9.mysql为什么用B+树做索引?

         答: 

         1) B+树是一种排序好的数据结构。

          2)与普通的二叉树相比,B+树只在叶子节点做存储,在同样的内存下,B+树叶子能够存储更多的节点数量,遍历起来更快速方便。

          3) B+树是一一种平衡的多叉树,能够做更少的磁盘I/O, 另外叶子节点也可以连接起来,加快遍历。

    10.mysql的 sql执行顺序是怎么样的?

        答:   如: select * from A  left join B on A.c=B.d  where A.e="1" order by A.g;

             第一步先将A表和B表做笛卡尔乘积。

             第二步再通过on将上面的笛卡尔乘积的结果集进行第一次筛选。

             第三步 将A表多余的行,即没有匹配到B表的行接入到上面筛选的On结果集后,空的用null填充。

             第四步用where对结果集进行筛选。

             第五步再通过order by 对结果集作排序。

    11.数据库的事务隔离级别有哪几种方式?分别有什么优缺点? 

    1. 读未提交,一个事务能够读取到另一个事务未提交的记录。   

    2. 读已提交,事务只能读取到已经提交的记录, 会出现不可重复读的问题,即同一个事务多次读取到的结果是不相同的。

    3. 可重复读,即同一个事务,多次查询读取的记录是 相同的,但是会出现幻读的问题,即在读取结果的一瞬间,另外一个事务突然向数据库里插入了一条记录,那么前面的事务好像多读取了一条记录的问题。可用mvcc解决幻读的问题,多版本控制。

    4.可串行化,是数据库的隔离的最高级别,不会出现脏读、不可重复读、幻读的问题,但性能比较差。

    12. mysql 的check用法? 

          答:   给字段添加一个约束, 在建表的时候通过check(字段>1) 给字段的值限制范围,即限制字段能为什么值。

    13. mysql的binlog日志默认存放在哪儿?

               1) 查看日志文件路径

                 show master status

       

        

            2)在my.ini文件中 查看bin_log文件

        we

    14. mysql 自增主键出现不连续的原因?

    答: mysql自增主键不连续的情况原因有三个: 

         1) 唯一键冲突

         2) 事务回滚

         3) insert...select的时候会出现主键id每次双倍分配导致主键id跳过的问题

          参考博客

     insert into ..select .. 

    CREATE TABLE `t1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `c` int(11) DEFAULT NULL,
      `d` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `c` (`c`)
    ) ENGINE=InnoDB;
    
    insert into t1 values(null, 1,1);
    insert into t1 values(null, 2,2);
    insert into t1 values(null, 3,3);
    insert into t1 values(null, 4,4);
    create table t2 like t1;
    insert into t2(c,d) select c,d from t1;
    insert into t2 values(null, 5,5);
    
    select * from t2;
    

     查询结果为:

    15.  mysql的慢查询默认时间为多长? 

            答:  10 S

           

    16. 聚簇索引和非聚簇索引的区别是什么? mysql 为什么用自增的id适合作为主键?

            答:  聚簇索引在Mysql里是唯一的,索引和数据存储在一起,非聚簇索引指索引和数据没有放一起,类如书本后的附录目录。

                   因为mysql 的自增id , 正好是mysql的聚簇索引的数据的物理存放顺序和索引存放顺序是一致的。只要索引是相邻的,那么在磁盘上的位置也是相邻的,如果不是自增的情况,那么会出现更多次的磁盘io情况。

    展开全文
  • MySQL 精选 60 道面试题(含答案)

    千次阅读 2022-03-19 00:39:17
    金三银四到了,给大家整理一些数据库必知必会面试题。基础相关1、关系型和非关系型数据库的区别?关系型数据库的优点容易理解,因为它采用了关系模型来组织数据。可以保持数据的一致性。数据更新的开...
    金三银四到了,给大家整理一些数据库必知必会的面试题。

    基础相关

    1、关系型和非关系型数据库的区别?

    关系型数据库的优点

    • 容易理解,因为它采用了关系模型来组织数据。

    • 可以保持数据的一致性。

    • 数据更新的开销比较小。

    • 支持复杂查询(带 where 子句的查询)

    非关系型数据库(NOSQL)的优点

    • 无需经过 SQL 层的解析,读写效率高。

    • 基于键值对,读写性能很高,易于扩展

    • 可以支持多种类型数据的存储,如图片,文档等等。

    • 扩展(可分为内存性数据库以及文档型数据库,比如 Redis,MongoDB,HBase 等,适合场景:数据量大高可用的日志系统/地理位置存储系统)。

    2、详细说一下一条 MySQL 语句执行的步骤

    Server 层按顺序执行 SQL 的步骤为:

    • 客户端请求 -> 连接器(验证用户身份,给予权限)

    • 查询缓存(存在缓存则直接返回,不存在则执行后续操作)

    • 分析器(对 SQL 进行词法分析和语法分析操作)

    • 优化器(主要对执行的 SQL 优化选择最优的执行方案方法)

    • 执行器(执行时会先看用户是否有执行权限,有才去使用这个引擎提供的接口)-> 去引擎层获取数据返回(如果开启查询缓存则会缓存查询结果)

    索引相关

    3、MySQL 使用索引的原因?

    根本原因

    • 索引的出现,就是为了提高数据查询的效率,就像书的目录一样。

    • 对于数据库的表而言,索引其实就是它的“目录”。

    扩展

    • 创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    • 帮助引擎层避免排序和临时表

    • 将随机 IO 变为顺序 IO,加速表和表之间的连接。

    4、索引的三种常见底层数据结构以及优缺点

    三种常见的索引底层数据结构:分别是哈希表、有序数组和搜索树。

    • 哈希表这种适用于等值查询的场景,比如 memcached 以及其它一些 NoSQL 引擎,不适合范围查询。

    • 有序数组索引只适用于静态存储引擎,等值和范围查询性能好,但更新数据成本高。

    • N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。

    • 扩展(以 InnoDB 的一个整数字段索引为例,这个 N 差不多是 1200。棵树高是 4 的时候,就可以存 1200 的 3 次方个值,这已经 17 亿了。考虑到树根的数据块总是在内存中的,一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。其实,树的第二层也有很大概率在内存中,那么访问磁盘的平均次数就更少了。)

    5、索引的常见类型以及它是如何发挥作用的?

    根据叶子节点的内容,索引类型分为主键索引和非主键索引。

    • 主键索引的叶子节点存的整行数据,在InnoDB里也被称为聚簇索引。

    • 非主键索引叶子节点存的主键的值,在InnoDB里也被称为二级索引。

    6、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么?

    • InnoDB 存储引擎:B+ 树索引的叶子节点保存数据本身,其数据文件本身就是索引文件。

    • MyISAM 存储引擎:B+ 树索引的叶子节点保存数据的物理地址,叶节点的 data 域存放的是数据记录的地址,索引文件和数据文件是分离的。

    7、InnoDB 为什么设计 B+ 树索引?

    两个考虑因素:

    • InnoDB 需要执行的场景和功能需要在特定查询上拥有较强的性能。

    • CPU 将磁盘上的数据加载到内存中需要花费大量时间。

    为什么选择 B+ 树:

    • 哈希索引虽然能提供O(1)复杂度查询,但对范围查询和排序却无法很好的支持,最终会导致全表扫描。

    • B 树能够在非叶子节点存储数据,但会导致在查询连续数据可能带来更多的随机 IO。

    • 而 B+ 树的所有叶节点可以通过指针来相互连接,减少顺序遍历带来的随机 IO。

    • 普通索引还是唯一索引?

      由于唯一索引用不上 change buffer 的优化机制,因此如果业务可以接受,从性能角度出发建议你优先考虑非唯一索引。

    8、什么是覆盖索引和索引下推?

    覆盖索引:

    • 在某个查询里面,索引 k 已经“覆盖了”我们的查询需求,称为覆盖索引。

    • 覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

    索引下推:

    • MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

    9、哪些操作会导致索引失效?

    • 对索引使用左或者左右模糊匹配,也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。原因在于查询的结果可能是多个,不知道从哪个索引值开始比较,于是就只能通过全表扫描的方式来查询。

    • 对索引进行函数/对索引进行表达式计算,因为索引保持的是索引字段的原始值,而不是经过函数计算的值,自然就没办法走索引。

    • 对索引进行隐式转换相当于使用了新函数。

    • WHERE 子句中的 OR语句,只要有条件列不是索引列,就会进行全表扫描。

    10、字符串加索引

    • 直接创建完整索引,这样可能会比较占用空间。

    • 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引。

    • 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题。

    • 创建 hash 字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。

    日志相关

    11、MySQL 的 change buffer 是什么?

    • 当需要更新一个数据页时,如果数据页在内存中就直接更新;而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中。

    • 这样就不需要从磁盘中读入这个数据页了,在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

    • 注意唯一索引的更新就不能使用 change buffer,实际上也只有普通索引可以使用。

    • 适用场景:

      - 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

    - 反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。

    12、MySQL 是如何判断一行扫描数的?

    • MySQL 在真正开始执行语句之前,并不能精确地知道满足这个条件的记录有多少条。

    • 而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度。

    13、MySQL 的 redo log 和 binlog 区别?

    e7e761f690aa227cc9ab98da1815587e.png


    14、为什么需要 redo log?

    • redo log 主要用于 MySQL 异常重启后的一种数据恢复手段,确保了数据的一致性。

    • 其实是为了配合 MySQL 的 WAL 机制。因为 MySQL 进行更新操作,为了能够快速响应,所以采用了异步写回磁盘的技术,写入内存后就返回。但是这样,会存在 crash后 内存数据丢失的隐患,而 redo log 具备 crash safe 的能力。

    15、为什么 redo log 具有 crash-safe 的能力,是 binlog 无法替代的?

    第一点:redo log 可确保 innoDB 判断哪些数据已经刷盘,哪些数据还没有

    • redo log 和 binlog 有一个很大的区别就是,一个是循环写,一个是追加写。也就是说 redo log 只会记录未刷盘的日志,已经刷入磁盘的数据都会从 redo log 这个有限大小的日志文件里删除。binlog 是追加日志,保存的是全量的日志。

    • 当数据库 crash 后,想要恢复未刷盘但已经写入 redo log 和 binlog 的数据到内存时,binlog 是无法恢复的。虽然 binlog 拥有全量的日志,但没有一个标志让 innoDB 判断哪些数据已经刷盘,哪些数据还没有。

    • 但 redo log 不一样,只要刷入磁盘的数据,都会从 redo log 中抹掉,因为是循环写!数据库重启后,直接把 redo log 中的数据都恢复至内存就可以了。

    第二点:如果 redo log 写入失败,说明此次操作失败,事务也不可能提交

    • redo log 每次更新操作完成后,就一定会写入日志,如果写入失败,说明此次操作失败,事务也不可能提交。

    • redo log 内部结构是基于页的,记录了这个页的字段值变化,只要crash后读取redo log进行重放,就可以恢复数据。

    • 这就是为什么 redo log 具有 crash-safe 的能力,而 binlog 不具备。

    16、当数据库 crash 后,如何恢复未刷盘的数据到内存中?

    根据 redo log 和 binlog 的两阶段提交,未持久化的数据分为几种情况:

    • change buffer 写入,redo log 虽然做了 fsync 但未 commit,binlog 未 fsync 到磁盘,这部分数据丢失。

    • change buffer 写入,redo log fsync 未 commit,binlog 已经 fsync 到磁盘,先从 binlog 恢复 redo log,再从 redo log 恢复 change buffer。

    • change buffer 写入,redo log 和 binlog 都已经 fsync,直接从 redo log 里恢复。

    17、redo log 写入方式?

    redo log包括两部分内容,分别是内存中的日志缓冲(redo log buffer)和磁盘上的日志文件(redo log file)。

    MySQL 每执行一条 DML 语句,会先把记录写入 redo log buffer(用户空间) ,再保存到内核空间的缓冲区 OS-buffer 中,后续某个时间点再一次性将多个操作记录写到 redo log file(刷盘) 。这种先写日志,再写磁盘的技术,就是WAL

    56874ba988c3ed868d0699c3bade0030.png

    可以发现,redo log buffer写入到redo log file,是经过OS buffer中转的。其实可以通过参数innodb_flush_log_at_trx_commit进行配置,参数值含义如下:

    • 0:称为延迟写,事务提交时不会将redo log buffer中日志写入到OS buffer,而是每秒写入OS buffer并调用写入到redo log file中。

    • 1:称为实时写,实时刷”,事务每次提交都会将redo log buffer中的日志写入OS buffer并保存到redo log file中。

    • 2:称为实时写,延迟刷。每次事务提交写入到OS buffer,然后是每秒将日志写入到redo log file。

    18、redo log 的执行流程?

    我们来看下Redo log的执行流程,假设执行的 SQL 如下:

    update T set a =1 where id =666

    2efc57332e576b999841ed5a438be62c.png

    1. MySQL 客户端将请求语句 update T set a =1 where id =666,发往 MySQL Server 层。

    2. MySQL Server 层接收到 SQL 请求后,对其进行分析、优化、执行等处理工作,将生成的 SQL 执行计划发到 InnoDB 存储引擎层执行。

    3. InnoDB 存储引擎层将a修改为1的这个操作记录到内存中。

    4. 记录到内存以后会修改 redo log 的记录,会在添加一行记录,其内容是需要在哪个数据页上做什么修改

    5. 此后,将事务的状态设置为 prepare ,说明已经准备好提交事务了。

    6. 等到 MySQL Server 层处理完事务以后,会将事务的状态设置为 commit,也就是提交该事务。

    7. 在收到事务提交的请求以后,redo log 会把刚才写入内存中的操作记录写入到磁盘中,从而完成整个日志的记录过程。

    19、binlog 的概念是什么,起到什么作用, 可以保证 crash-safe 吗?

    • binlog 是归档日志,属于 MySQL Server 层的日志。可以实现主从复制数据恢复两个作用。

    • 当需要恢复数据时,可以取出某个时间范围内的 binlog 进行重放恢复。

    • 但是 binlog 不可以做 crash safe,因为 crash 之前,binlog 可能没有写入完全 MySQL 就挂了。所以需要配合 redo log 才可以进行 crash safe。

    20、什么是两阶段提交?

    MySQL 将 redo log 的写入拆成了两个步骤:prepare 和 commit,中间再穿插写入binlog,这就是"两阶段提交"。

    206f1a0a918daf64f61aa05f186f5ec9.png

    而两阶段提交就是让这两个状态保持逻辑上的一致。redolog 用于恢复主机故障时的未更新的物理数据,binlog 用于备份操作。两者本身就是两个独立的个体,要想保持一致,就必须使用分布式事务的解决方案来处理。

    为什么需要两阶段提交呢?

    • 如果不用两阶段提交的话,可能会出现这样情况

    • 先写 redo log,crash 后 bin log 备份恢复时少了一次更新,与当前数据不一致。

    • 先写 bin log,crash 后,由于 redo log 没写入,事务无效,所以后续 bin log 备份恢复时,数据不一致。

    • 两阶段提交就是为了保证 redo log 和 binlog 数据的安全一致性。只有在这两个日志文件逻辑上高度一致了才能放心的使用。

    在恢复数据时,redolog 状态为 commit 则说明 binlog 也成功,直接恢复数据;如果 redolog 是 prepare,则需要查询对应的 binlog事务是否成功,决定是回滚还是执行。

    21、MySQL 怎么知道 binlog 是完整的?

    一个事务的 binlog 是有完整格式的:

    • statement 格式的 binlog,最后会有 COMMIT;

    • row 格式的 binlog,最后会有一个 XID event。

    22、什么是 WAL 技术,有什么优点?

    WAL,中文全称是 Write-Ahead Logging,它的关键点就是日志先写内存,再写磁盘。MySQL 执行更新操作后,在真正把数据写入到磁盘前,先记录日志

    好处是不用每一次操作都实时把数据写盘,就算 crash 后也可以通过redo log 恢复,所以能够实现快速响应 SQL 语句。

    23、binlog 日志的三种格式

    binlog 日志有三种格式

    • Statement:基于SQL语句的复制((statement-based replication,SBR))

    • Row:基于行的复制。(row-based replication,RBR)

    • Mixed:混合模式复制。(mixed-based replication,MBR)

    Statement格式

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

    • 优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。

    • 缺点:由于记录的只是执行语句,为了这些语句能在备库上正确运行,还必须记录每条语句在执行的时候的一些相关信息,以保证所有语句能在备库得到和在主库端执行时候相同的结果。

    Row格式

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

    • 优点:binlog 中可以不记录执行的 SQL 语句的上下文相关的信息,仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。不会出现某些特定情况下的存储过程、或 function、或trigger的调用和触发无法被正确复制的问题。

    • 缺点:可能会产生大量的日志内容。

    Mixed格式

    实际上就是 Statement 与 Row 的结合。一般的语句修改使用 statment 格式保存 binlog,如一些函数,statement 无法完成主从复制的操作,则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式。

    24、redo log日志格式

    5e89b97f32b993fc0cbbfcea7e61827e.png

    redo log buffer (内存中)是由首尾相连的四个文件组成的,它们分别是:ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

    • write pos 是当前记录的位置,一边写一边后移,写到第 3 号文件末尾后就回到 0 号文件开头。

    • checkpoint 是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。

    • write pos 和 checkpoint 之间的是“粉板”上还空着的部分,可以用来记录新的操作。

    • 如果 write pos 追上 checkpoint,表示“粉板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把 checkpoint 推进一下。

    • 有了 redo log,当数据库发生宕机重启后,可通过 redo log将未落盘的数据(check point之后的数据)恢复,保证已经提交的事务记录不会丢失,这种能力称为crash-safe

    25、原本可以执行得很快的 SQL 语句,执行速度却比预期的慢很多,原因是什么?如何解决?

    原因:从大到小可分为四种情况

    • MySQL 数据库本身被堵住了,比如:系统或网络资源不够。

    • SQL 语句被堵住了,比如:表锁,行锁等,导致存储引擎不执行对应的 SQL 语句。

    • 确实是索引使用不当,没有走索引。

    • 表中数据的特点导致的,走了索引,但回表次数庞大。

    解决:

    • 考虑采用 force index 强行选择一个索引

    • 考虑修改语句,引导 MySQL 使用我们期望的索引。比如把“order by b limit 1” 改成 “order by b,a limit 1” ,语义的逻辑是相同的。

    • 第三种方法是,在有些场景下,可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。

    • 如果确定是索引根本没必要,可以考虑删除索引。

    26、InnoDB 数据页结构

    一个数据页大致划分七个部分

    • File Header:表示页的一些通用信息,占固定的38字节。

    • page Header:表示数据页专有信息,占固定的56字节。

    • inimum+Supermum:两个虚拟的伪记录,分别表示页中的最小记录和最大记录,占固定的26字节。

    • User Records:真正存储我们插入的数据,大小不固定。

    • Free Space:页中尚未使用的部分,大小不固定。

    • Page Directory:页中某些记录的相对位置,也就是各个槽对应的记录在页面中的地址偏移量。

    • File Trailer:用于检验页是否完整,占固定大小 8 字节。

    数据相关

    27、MySQL 是如何保证数据不丢失的?

    • 只要redolog 和 binlog 保证持久化磁盘就能确保MySQL异常重启后回复数据

    • 在恢复数据时,redolog 状态为 commit 则说明 binlog 也成功,直接恢复数据;如果 redolog 是 prepare,则需要查询对应的 binlog事务是否成功,决定是回滚还是执行。

    28、误删数据怎么办?

    DBA 的最核心的工作就是保证数据的完整性,先要做好预防,预防的话大概是通过这几个点:

    • 权限控制与分配(数据库和服务器权限)

    • 制作操作规范

    • 定期给开发进行培训

    • 搭建延迟备库

    • 做好 SQL 审计,只要是对线上数据有更改操作的语句(DML和DDL)都需要进行审核

    • 做好备份。备份的话又分为两个点 (1)如果数据量比较大,用物理备份 xtrabackup。定期对数据库进行全量备份,也可以做增量备份。(2)如果数据量较少,用 mysqldump 或者 mysqldumper。再利用 binlog 来恢复或者搭建主从的方式来恢复数据。定期备份binlog 文件也是很有必要的

    • 如果发生了数据删除的操作,又可以从以下几个点来恢复:

    • DML 误操作语句造成数据不完整或者丢失。可以通过 flashback,美团的 myflash,也是一个不错的工具,本质都差不多

    • 都是先解析 binlog event,然后在进行反转。把 delete 反转为insert,insert 反转为 delete,update前后 image 对调。

    • 所以必须设置binlog_format=row 和 binlog_row_image=full,切记恢复数据的时候,应该先恢复到临时的实例,然后在恢复回主库上。

    • DDL语句误操作(truncate和drop),由于DDL语句不管 binlog_format 是 row 还是 statement ,在 binlog 里都只记录语句,不记录 image 所以恢复起来相对要麻烦得多。

    • 只能通过全量备份+应用 binlog 的方式来恢复数据。一旦数据量比较大,那么恢复时间就特别长

    • rm 删除:使用备份跨机房,或者最好是跨城市保存。

    29、drop、truncate 和 delete 的区别

    • DELETE 语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

    • TRUNCATE TABLE  则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

    • drop语句将表所占用的空间全释放掉。

    • 在速度上,一般来说,drop> truncate > delete。

    • 如果想删除部分数据用 delete,注意带上 where 子句,回滚段要足够大;

    • 如果想删除表,当然用 drop;如果想保留表而将所有数据删除,如果和事务无关,用 truncate 即可;

    • 如果和事务有关,或者想触发 trigger,还是用 delete;如果是整理表内部的碎片,可以用 truncate 跟上 reuse stroage,再重新导入/插入数据。

    30、在 MySQL 中有两个 kill 命令

    • 一个是 kill query + 线程 id,表示终止这个线程中正在执行的语句

    • 一个是 kill connection + 线程 id,这里 connection 可缺省,表示断开这个线程的连接

    kill 不掉的原因

    • kill命令被堵了,还没到位

    • kill命令到位了,但是没被立刻触发

    • kill命令被触发了,但执行完也需要时间

    31、如何理解 MySQL 的边读边发

    • 如果客户端接受慢,会导致 MySQL 服务端由于结果发不出去,这个事务的执行时间会很长。

    • 服务端并不需要保存一个完整的结果集,取数据和发数据的流程都是通过一个 next_buffer 来操作的。

    • 内存的数据页都是在 Buffer_Pool中操作的。

    • InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法,使用链表实现,实现上,按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。

    32、MySQL 的大表查询为什么不会爆内存?

    • 由于 MySQL 是边读变发,因此对于数据量很大的查询结果来说,不会再 server 端保存完整的结果集,所以,如果客户端读结果不及时,会堵住 MySQL 的查询过程,但是不会把内存打爆。

    • InnoDB 引擎内部,由于有淘汰策略,InnoDB 管理 Buffer_Pool 使用的是改进的 LRU 算法,使用链表实现,实现上,按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。对冷数据的全扫描,影响也能做到可控制。

    33、MySQL 临时表的用法和特性

    • 只对当前session可见。

    • 可以与普通表重名。

    • 增删改查用的是临时表。

    • show tables 不显示普通表。

    • 在实际应用中,临时表一般用于处理比较复杂的计算逻辑。

    • 由于临时表是每个线程自己可见的,所以不需要考虑多个线程执行同一个处理时临时表的重名问题,在线程退出的时候,临时表会自动删除。

    34、MySQL 存储引擎介绍(InnoDB、MyISAM、MEMORY)

    • InnoDB 是事务型数据库的首选引擎,支持事务安全表 (ACID),支持行锁定和外键。MySQL5.5.5 之后,InnoDB 作为默认存储引擎

    • MyISAM 基于 ISAM 的存储引擎,并对其进行扩展。它是在 Web、数据存储和其他应用环境下最常用的存储引擎之一。MyISAM 拥有较高的插入、查询速度,但不支持事务。在 MySQL5.5.5 之前的版本中,MyISAM 是默认存储引擎

    • MEMORY 存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问。

    35、都说 InnoDB 好,那还要不要使用 MEMORY 引擎?

    • 内存表就是使用 memory 引擎创建的表

    • 为什么我不建议你在生产环境上使用内存表。这里的原因主要包括两个方面:锁粒度问题;数据持久化问题。

    • 由于重启会丢数据,如果一个备库重启,会导致主备同步线程停止;如果主库跟这个备库是双 M 架构,还可能导致主库的内存表数据被删掉。

    36、如果数据库误操作, 如何执行数据恢复?

    数据库在某个时候误操作,就可以找到距离误操作最近的时间节点的bin log,重放到临时数据库里,然后选择误删的数据节点,恢复到线上数据库。

    主从备份相关

    37、MySQL 是如何保证主备同步?

    主备关系的建立:

    • 一开始创建主备关系的时候,是由备库指定的,比如基于位点的主备关系,备库说“我要从binlog文件A的位置P”开始同步,主库就从这个指定的位置开始往后发。

    • 而主备关系搭建之后,是主库决定要发给数据给备库的,所以主库有新的日志也会发给备库。

    MySQL 主备切换流程:

    • 客户端读写都是直接访问A,而节点B是备库,只要将A的更新都同步过来,到本地执行就可以保证数据是相同的。

    • 当需要切换的时候就把节点换一下,A的节点B的备库

    一个事务完整的同步过程:

    • 备库B和主库A建立来了长链接,主库A内部专门线程用于维护了这个长链接。

    • 在备库B上通过changemaster命令设置主库A的IP端口用户名密码以及从哪个位置开始请求binlog包括文件名和日志偏移量

    • 在备库B上执行start-slave命令备库会启动两个线程:io_thread和sql_thread分别负责建立连接和读取中转日志进行解析执行

    • 备库读取主库传过来的binlog文件备库收到文件写到本地成为中转日志

    • 后来由于多线程复制方案的引入,sql_thread演化成了多个线程。

    38、什么是主备延迟

    主库和备库在执行同一个事务的时候出现时间差的问题,主要原因有:

    • 有些部署条件下,备库所在机器的性能要比主库性能差。

    • 备库的压力较大。

    • 大事务,一个主库上语句执行10分钟,那么这个事务可能会导致从库延迟10分钟。

    39、为什么要有多线程复制策略?

    • 因为单线程复制的能力全面低于多线程复制,对于更新压力较大的主库,备库可能是一直追不上主库的,带来的现象就是备库上seconds_behind_master值越来越大。

    • 在实际应用中,建议使用可靠性优先策略,减少主备延迟,提升系统可用性,尽量减少大事务操作,把大事务拆分小事务。

    40、MySQL 的并行策略有哪些?

    • 按表分发策略:如果两个事务更新不同的表,它们就可以并行。因为数据是存储在表里的,所以按表分发,可以保证两个 worker 不会更新同一行。缺点:如果碰到热点表,比如所有的更新事务都会涉及到某一个表的时候,所有事务都会被分配到同一个 worker 中,就变成单线程复制了。

    • 按行分发策略:如果两个事务没有更新相同的行,它们在备库上可以并行。如果两个事务没有更新相同的行,它们在备库上可以并行执行。显然,这个模式要求 binlog 格式必须是 row。缺点:相比于按表并行分发策略,按行并行策略在决定线程分发的时候,需要消耗更多的计算资源。

    41、MySQL的一主一备和一主多从有什么区别?

    在一主一备的双 M 架构里,主备切换只需要把客户端流量切到备库;而在一主多从架构里,主备切换除了要把客户端流量切到备库外,还需要把从库接到新主库上。

    42、主库出问题如何解决?

    • 基于位点的主备切换:存在找同步位点这个问题

    • MySQL 5.6 版本引入了 GTID,彻底解决了这个困难。那么,GTID 到底是什么意思,又是如何解决找同步位点这个问题呢?

    • GTID:全局事务 ID,是一个事务在提交的时候生成的,是这个事务的唯一标识;它由两部分组成,格式是:GTID=server_uuid:gno

    • 每个 MySQL 实例都维护了一个 GTID 集合,用来对应“这个实例执行过的所有事务”。

    • 在基于 GTID 的主备关系里,系统认为只要建立主备关系,就必须保证主库发给备库的日志是完整的。因此,如果实例 B 需要的日志已经不存在,A’就拒绝把日志发给 B。

    43、MySQL 读写分离涉及到过期读问题的几种解决方案?

    • 强制走主库方案

    • sleep 方案

    • 判断主备无延迟方案

    • 配合 semi-sync 方案

    • 等主库位点方案

    • GTID 方案。

    • 实际生产中,先客户端对请求做分类,区分哪些请求可以接受过期读,而哪些请求完全不能接受过期读;然后,对于不能接受过期读的语句,再使用等 GTID 或等位点的方案。

    44、MySQL的并发链接和并发查询有什么区别?

    • 在执行show processlist的结果里,看到了几千个连接,指的是并发连接。而"当前正在执行"的语句,才是并发查询。

    • 并发连接数多影响的是内存,并发查询太高对CPU不利。一个机器的CPU核数有限,线程全冲进来,上下文切换的成本就会太高。

    • 所以需要设置参数:innodb_thread_concurrency 用来限制线程数,当线程数达到该参数,InnoDB就会认为线程数用完了,会阻止其他语句进入引擎执行。

    性能相关

    45、短时间提高 MySQL 性能的方法

    • 第一种方法:先处理掉那些占着连接但是不工作的线程。或者再考虑断开事务内空闲太久的连接。kill connection + id

    • 第二种方法:减少连接过程的消耗:慢查询性能问题在 MySQL 中,会引发性能问题的慢查询,大体有以下三种可能:索引没有设计好;SQL 语句没写好;MySQL 选错了索引(force index)。

    46、为什么 MySQL 自增主键 ID 不连续?

    • 唯一键冲突

    • 事务回滚

    • 自增主键的批量申请

    • 深层次原因是:MySQL 不判断自增主键是否存在,从而减少加锁的时间范围和粒度,这样能保持更高的性能,确保自增主键不能回退,所以才有自增主键不连续。

    • 自增主键怎么做到唯一性?自增值加1来通过自增锁控制并发。

    47、InnoDB 为什么要用自增 ID 作为主键?

    • 自增主键的插入模式,符合递增插入,每次都是追加操作,不涉及挪动记录,也不会触发叶子节点的分裂。

    • 每次插入新的记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。

    • 而有业务逻辑的字段做主键,不容易保证有序插入,由于每次插入主键的值近似于随机

    • 因此每次新纪录都要被插到现有索引页得中间某个位置, 频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,写数据成本较高。

    48、如何最快的复制一张表?

    • 为了避免对源表加读锁,更稳妥的方案是先将数据写到外部文本文件,然后再写回目标表

    • 一种方法是,使用 mysqldump 命令将数据导出成一组 INSERT 语句

    • 另一种方法是直接将结果导出成.csv 文件。MySQL 提供语法,用来将查询结果导出到服务端本地目录:select * from db1.t where a>900 into outfile '/server_tmp/t.csv';得到.csv 导出文件后,你就可以用下面的 load data 命令将数据导入到目标表 db2.t 中:load data infile '/server_tmp/t.csv' into table db2.t;

    • 物理拷贝:在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法,可以通过导出 + 导入表空间的方式,实现物理拷贝表的功能。

    49、grant 和 flush privileges语句

    • grant语句会同时修改数据表和内存,判断权限的时候使用的内存数据,因此,规范使用是不需要加上 flush privileges 语句。

    • flush privileges 语句本身会用数据表的数据重建一份内存权限数据,所以在权限数据可能存在不一致的情况下再使用。

    50、要不要使用分区表?

    • 分区并不是越细越好。实际上,单表或者单分区的数据一千万行,只要没有特别大的索引,对于现在的硬件能力来说都已经是小表了。

    • 分区也不要提前预留太多,在使用之前预先创建即可。比如,如果是按月分区,每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区,要及时的 drop 掉。

    51、join 用法

    • 使用 left join 左边的表不一定是驱动表

    • 如果需要 left join 的语义,就不能把被驱动表的字段放在 where 条件里面做等值判断或不等值判断,必须都写在 on 里面

    • 标准的 group by 语句,是需要在 select 部分加一个聚合函数,比如select a,count(*) from t group by a order by null;

    52、MySQL 有哪些自增ID?各自场景是什么?

    • 表的自增 ID 达到上限之后,在申请值不会变化,进而导致联系插入数据的时候报主键冲突错误。

    • row_id 达到上限之后,归 0 在重新递增,如果出现相同的 row_id 后写的数据会覆盖之前的数据。

    • Xid 只需要不在同一个 binlog 文件出现重复值即可,理论上会出现重复值,但概率极小可忽略不计。

    • InnoDB 的 max_trx_id 递增值每次 MySQL 重启会保存起来。

    • Xid 是由 server 层维护的。InnoDB 内部使用 Xid,就是为了能够在 InnoDB 事务和 server 之间做关联。但是,InnoDB 自己的 trx_id,是另外维护的。

    • thread_id 是我们使用中最常见的,而且也是处理得最好的一个自增 id 逻辑了。使用了insert_unique算法

    53、Xid 在 MySQL 内部是怎么生成的呢?

    MySQL 内部维护了一个全局变量 global_query_id,每次执行语句(包括select语句)的时候将它赋值给 Query_id,然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句,那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。

    而 global_query_id 是一个纯内存变量,重启之后就清零了。所以你就知道了,在同一个数据库实例中,不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件,这就保证了,同一个 binlog 文件里,Xid 一定是惟一的。

    锁相关

    54、说一下 MySQL 的锁

    • MySQL 在 server 层 和 存储引擎层 都运用了大量的锁

    • MySQL server 层需要讲两种锁,第一种是MDL(metadata lock) 元数据锁,第二种则 Table Lock 表锁。

    • MDL 又名元数据锁,那么什么是元数据呢,任何描述数据库的内容就是元数据,比如我们的表结构、库结构等都是元数据。那为什么需要 MDL 呢?

    • 主要解决两个问题:事务隔离问题;数据复制问题

    • InnoDB 有五种表级锁:IS(意向读锁);IX(意向写锁);S(读);X(写);AUTO-INC

    • 在对表进行select/insert/delete/update语句时候不会加表级锁

    • IS和IX的作用是为了判断表中是否有已经被加锁的记录

    • 自增主键的保障就是有 AUTO-INC 锁,是语句级别的:为表的某个列添加 AUTO_INCREMENT 属性,之后在插⼊记录时,可以不指定该列的值,系统会⾃动为它赋上单调递增的值。

    • InnoDB 4 种行级锁

    • RecordLock:记录锁

    • GapLock:间隙锁解决幻读;前一次查询不存在的东西在下一次查询出现了,其实就是事务A中的两次查询之间事务B执行插入操作被事务A感知了

    • Next-KeyLock:锁住某条记录又想阻止其它事务在改记录前面的间隙插入新纪录

    • InsertIntentionLock:插入意向锁;如果插入到同一行间隙中的多个事务未插入到间隙内的同一位置则无须等待

    • 行锁和表锁的抉择

      • 全表扫描用行级锁

    55、什么是幻读?

    值在同一个事务中,存在前后两次查询同一个范围的数据,第二次看到了第一次没有查询到的数据。

    幻读出现的场景:

    • 事务的隔离级别是可重复读,且是当前读。

    • 幻读指新插入的行。

    幻读带来的问题:

    • 对行锁语义的破坏

    • 破坏了数据一致性

    解决:

    • 加间隙锁,锁住行与行之间的间隙,阻塞新插入的操作。

    • 带来的问题:降低并发度,可能导致死锁。

    其它为什么系列

    56、为什么 MySQL 会抖一下?

    • 脏页会被后台线程自动 flush,也会由于数据页淘汰而触发 flush,而刷脏页的过程由于会占用资源,可能会让你的更新和查询语句的响应时间长一些。

    57、为什么删除了表,表文件的大小还是没变?

    • 数据项删除之后 InnoDB 某个页 page A 会被标记为可复用。

    • delete 命令把整个表的数据删除,结果就是,所有的数据页都会被标记为可复用。但是磁盘上,文件不会变小。

    • 经过大量增删改的表,都是可能是存在空洞的。这些空洞也占空间所以,如果能够把这些空洞去掉,就能达到收缩表空间的目的。

    • 重建表,就可以达到这样的目的。可以使用 alter table A engine=InnoDB 命令来重建表。

    58、count(*)实现方式以及各种 count 对比

    • 对于 count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。

    • 对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。单看这两个用法的差别的话,你能对比出来,count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。

    • 对于 count(字段) 来说:如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。也就是前面的第一条原则,server 层要什么字段,InnoDB 就返回什么字段。

    • 但是 count * 是例外,并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是 null,按行累加。

    • 所以结论是:按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(※),所以建议尽量使用 count(*)。

    59、orderby 排序内部原理

    • MySQL 会为每个线程分配一个内存(sort-buffer)用于排序该内存大小为 sort_buffer_size;

    • 如果排序的数据量小于 sort_buffer_size,排序就会在内存中完成;

      内部排序分为两种

    • 全字段排序:到索引树上找到满足条件的主键ID根据主键ID去取出数据放到sort_buffer然后进行快速排序

    • rowid排序:通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据

    • 如果数据量很大,内存中无法存下这么多,就会使用磁盘临时文件来辅助排序,称为外部排序;

    • 外部排序,MySQL会分为好几份单独的临时文件来存放排序后的数据,一般是磁盘文件中进行归并,然后将这些文件合并成一个大文件;

    60、如何高效的使用 MySQL 显式随机消息

    • 随机取出 Y1,Y2,Y3之后,算出Ymax,Ymin

    • 得到id集后算出Y1、Y2、Y3对应的三个id 最后 select * from t where id in (id1, id2, id3) 这样扫描的行数应该是C+Ymax+3

      mysql> select count(*) into @C from t;
      set @Y1 = floor(@C * rand());
      set @Y2 = floor(@C * rand());
      set @Y3 = floor(@C * rand());
      Ymax = max(Y1,Y2,Y3)
      Ymin = min(Y1,Y2,Y3)
      select id from t limit Ymin,(Ymax - Ymin)

    持续更新中。

    参考:

    • 极客时间《MySQL实战 45 讲》

    • https://www.nowcoder.com/discuss/744934?type=1&order=0&pos=25&page=1&ncTraceId=&channel=-1&source_id=discuss_tag_nctrack

    今天的唠嗑就到这里了。

    我是磊哥,我们下期再见。

    e87c06307af1c8441e2a17426cc285c7.gif

    往期推荐

    9a08951a819e87ecb6fd2945e920b14c.png

    Java夺命21连问!(附答案)


    6e5b7734081e8bf77b0a7cc055091e67.png

    为什么Spring需要三级缓存解决循环依赖,而不是二级缓存?


    b631ea25996b976c4f8e8d4f273be372.png

    线程池是如何执行的?拒绝策略有哪些?


    3998d90fe9e2157ac6cbfcfa783fef9c.gif

    展开全文
  • 面试准备:数据库常见面试题汇总

    千次阅读 2020-12-29 12:11:53
    文章目录1.简单解释数据库三范式?2.不满足数据库三范式可能出现什么情况?...6.MySQL中int(11)与int(3)的区别?7.date,datetime和timestamp数据类型有什么区别?8.union 与union all的区别9.各种join的区别?10....

    文章目录

    1.简单解释数据库三范式?

    2.不满足数据库三范式可能会出现什么情况?

    3.解释脏读、不可重复读,幻读,更新丢失

    4. Mysql提供了哪几种事务隔离级别?

    5.MySQL中varchar与char的区别?

    6.MySQL中int(11)与int(3)的区别?

    7.date,datetime和timestamp数据类型有什么区别?

    8.union 与union all的区别

    9.各种join的区别?

    10.drop,delete与truncate的区别?

    11.MySQL有哪几种索引?

    12.简要说明InnoDB事务是如何通过日志来实现的?

    13.简述Mysql Innodb引擎和MyIASM引擎的区别?什么时候选择MyIASM?

    14.sql执行慢的原因有哪些,如何进行sql优化?

    15.视图的作用,视图可以更改么?

    16. 说一说MySQL中的锁机制

    17. 数据库事务四大特性?

    18.ACID、BASE和CAP?

    19.MySQL如何获取当前日期?

    20.Mysql驱动程序是什么?

    21.Innodb引擎有什么特性?

    22.索引对性能有哪些影响?

    23.二进制日志(binlog)的作用?与redo log的区别?

    24.InnoDB的行锁/表锁?

    25.什么是MVCC ?

    26. sql 语句在 MySQL 中的执行流程

    1.简单解释数据库三范式?

    第一范式就是数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值。

    第二范式要求实体的属性完全依赖于主关键字(即不存在部分依赖)。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体

    第三范式要求一个数据库表中不包含已在其它表中已包含的非主关键字信息(即消除传递依赖)。

    例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。

    更多知识点,参考:高性能Mysql——范式与反范式。

    2.不满足数据库三范式可能会出现什么情况?

    数据冗余(想修改一个属性,就要更新多行数据)

    插入异常(想要插入数据,结构因为表设计的问题,导致不能成功插入)

    删除异常(只想删除其中的某些数据 ,结果把不该删的也删了)

    更新异常(想更新一条数据,结果工作量大,还容易出错)

    3.解释脏读、不可重复读,幻读,更新丢失

    脏读(Dirty read):在一个事务中读取到另一个事务已经修改但没有提交的数据。

    例如,事务A对数据进行了修改,但是还没有提交,这时事务B读取这个数据,然后事务A回滚,那么事务B取的数据无效。不符合一致性。

    解决办法:把数据库的事务隔离级别调整到READ_COMMITTED

    不可重复读(NonRepeatable Read):不能读到相同的数据内容,事务A读取到了事务B已经提交的修改数据(即一个事务范围内两个相同的查询却返回了不同数据)。

    例如事务A先读取数据,然后事务B对该同一数据修改并提交,那么事务A再次读取该数据时,由于事务B对该数据的修改,事务A两次读到的的数据可能是不一样的。不符合隔离性。

    解决办法:把数据库的事务隔离级别调整到REPEATABLE_READ

    幻读(Phantom Read):事务在插入已经检查过不存在的记录时,惊奇的发现这些数据已经存在了(针对的insert操作) 。

    在事务A查询结束后,事务B往User表中插入了一条id为1的数据。此时,由于事务A查询到id为1的用户不存在,因此插入1条id为1的数据,报错:主键冲突。不符合隔离性。

    解决办法:把数据库的事务隔离级别调整到SERIALIZABLE_READ

    更新丢失(Update lose):两个事务同时操作相同数据,后提交的事务会覆盖先提交的事务处理结果。

    解决办法:乐观锁

    4. Mysql提供了哪几种事务隔离级别?

    MySQL数据的四种隔离级别:

    ① Serializable (串行化):可避免脏读、不可重复读、幻读的发生。

    ② Repeatable read (可重复读):可避免脏读、不可重复读的发生。

    ③ Read committed (读已提交):可避免脏读的发生。

    ④ Read uncommitted (读未提交):最低级别,任何情况都无法保证。

    通过SELECT @@transaction_isolation;(mysql 5.7以下是select @@tx_isolation)可以查看事务隔离级别:

    隔离界别为可重复读,那么我写一个脏读和可重复读的事务:

    # 事务A

    begin;

    select * from orders where id="190827F4AK12R30H";

    commit;

    # 事务B

    begin;

    UPDATE orders SET left_msg="new message" where id="190827F4AK12R30H";

    select sleep(10);

    commit;

    查询结果是:尽管事务B修改了left_msg为new message,但是事务A的查询结果是old message。

    # 事务A

    begin;

    select * from orders where id="190827F4AK12R30H";

    select sleep(10);

    select * from orders where id="190827F4AK12R30H";

    commit;

    # 事务B

    begin;

    UPDATE orders SET left_msg="new message" where id="190827F4AK12R30H";

    commit;

    查询结果是:尽管事务B修改了left_msg为new message,但是两次事务A的查询结果都是 old message。

    最后试试幻读:

    # 事务A

    begin;

    select * from stu where id=1;

    select sleep(10);

    insert into stu(id,name,age) values (1,"xiaoming",13);

    commit;

    # 事务B

    begin;

    insert into stu(id,name,age) values (1,"xiaohong",15);

    commit;

    运行结果:

    可把事务级别改成串行set session transaction isolation level SERIALIZABLE;。

    使用set session transaction isolation level REPEATABLE READ;改回来

    5.MySQL中varchar与char的区别?

    CHAR和VARCHAR最大的不同就是一个是固定长度,一个是可变长度。

    CHAR存储定长数据很方便,CHAR字段上的索引效率级高,比如定义 char(10),那么不论你存储的数据是否达到了10个字节,都要占去10个字节的空间,不足的自动用空格填充(并且如果存储的char类型的字符串后面有空格的话,innodb会忽略)。

    VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则 使用两个字节)。

    一般来说,CHAR比VARCHAR更快,因为CHAR是固定长度的,而VARCHAR需要增加一个长度标识,处理时需要多一次运算。但是有例外,参考:MySQL Innodb数据库性能实践——VARCHAR vs CHAR,但是如果使用的是Innodb引擎的话,推荐使用varchar代替char。特别是字符串的平均长度比最大长度要小很多的情况;当然,如果你的字符串本来就很短或者字符串长度固定,例如只有10个字符,那么就优先选CHAR了。

    6.MySQL中int(11)与int(3)的区别?

    当我们在选择使用int的类型的时候,不论是int(3)还是int(11),它在数据库里面存储的都是4个字节的长度。

    如果int的值为10

    int(11)显示结果为00000000010

    int(3)显示结果为010

    就是显示的长度不一样而已,但都是占用四个字节的空间。

    类型长度,参考:https://www.runoob.com/mysql/mysql-data-types.html。

    更多知识点,参考高性能Mysql——Schema与数据类型优化。

    7.date,datetime和timestamp数据类型有什么区别?

    一个完整的日期格式如下:YYYY-MM-DD HH:MM:SS[.fraction],它可分为两部分:date部分和time部分,其中,date部分对应格式中的“YYYY-MM-DD”,time部分对应格式中的“HH:MM:SS[.fraction]”。对于date字段来说,它只支持date部分,如果插入了time部分的内容,它会丢弃掉该部分的内容,并提示一个warning。

    timestamp和datetime的不同点:

    DATETIME 的日期范围是 1001——9999 年;TIMESTAMP 的时间范围是 1970——2038 年

    DATETIME 存储时间与时区无关;TIMESTAMP 存储时间与时区有关,显示的值也依赖于时区

    DATETIME 的存储空间为 8 字节;TIMESTAMP 的存储空间为 4 字节

    DATETIME 的默认值为 null;TIMESTAMP 的字段默认不为空(not null),默认值为当前时间(CURRENT_TIMESTAMP)

    8.union 与union all的区别

    union 在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排 序运算,删除重复的记录再返回结果。 union all 则会显示重复结果,只是简单的两个结果合并并返回.所以效率比union高,在保证没有重复数据的情况下用union all.

    9.各种join的区别?

    参考:https://blog.csdn.net/weter_drop/article/details/84729822

    10.drop,delete与truncate的区别?

    DROP语句:

    直接删掉表。drop语句将表所占用的空间全释放掉。

    TRUNCATE语句:

    删除表中数据,再插入时自增长id又从1开始 。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小。 并且不会把单独的删除操作记录记入日志保存,删除行是不能恢复的。

    DELETE语句:

    删除表中数据,可以加where字句,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。DELETE操作不会减少表或索引所占用的空间。

    delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

    truncate、drop是DLL(data define language),操作立即生效,原数据不放到 rollback segment中,不能回滚。

    11.MySQL有哪几种索引?

    MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。

    对于哈希索引来说,底层的数据结构就是哈希表,如果哈希冲突很多的话,一些索引维护操作的代价也会很高。

    聚集索引是基于B+树的,非叶节点只做根据主键的索引,而由叶节点来保存主键记录的数据或者指针,这样使得B+树每个非叶子节点所能保存的关键字大大增加,使得B+树层级更少,IO操作也更少。B+树叶子节点的关键字从小到大有序排列,天然具备排序功能;左边的数据会有一个向右的指针,使得全节点遍历更快。

    如果没有主键被定义,那么该表的第一个唯一非空索引被作为聚集索引。

    如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引。

    非聚集索引和聚集索引差不多,通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据。

    参考:高性能Mysql——创建高性能的索引。

    参考:其他索引方式

    12.简要说明InnoDB事务是如何通过日志来实现的?

    参考:高性能Mysql——InnoDB事务是如何通过日志来实现的?

    13.简述Mysql Innodb引擎和MyIASM引擎的区别?什么时候选择MyIASM?

    两者的对比:

    是否支持行级锁 : MyISAM 只有表级锁(table-level locking),而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。

    是否支持外键: MyISAM不支持,而InnoDB支持。

    是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行速度比InnoDB类型更快,但是不提供事务支持。但是InnoDB 提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    是否支持MVCC :仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

    MyISAM读性能要优于InnoDB,除了针对索引的update操作,MyISAM的写性能可能低于InnoDB,其他操作MyISAM的写性能也是优于InnoDB的,而且可以通过分库分表来提高MyISAM写操作的速度

    MyISAM的索引和数据是分开的,而且索引是压缩的,而InnoDB的索引和数据是紧密捆绑的,没有使用压缩,所以InnoDB的体积比MyISAM庞大

    MyISAM引擎索引结构的叶子节点的数据域,存放的并不是实际的数据记录,而是数据记录的地址。索引文件与数据文件分离,这样的索引称为“非聚簇索引”。其检索算法:先按照B+Tree的检索算法检索,找到指定关键字,则取出对应数据域的值,作为地址取出数据记录。

    InnoDB引擎索引结构的叶子节点的数据域,存放的就是实际的数据记录。这样的索引被称为“聚簇索引”,一个表只能有一个聚簇索引。

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

    选择哪种搜索引擎,应视具体应用而定

    ①**如果是读多写少的项目,**可以考虑使用MyISAM,MYISAM索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MYISAM拥有全文索引的功能,这可以极大地优化LIKE查询的效率。

    ②如果你的应用程序一定要使用事务,毫无疑问你要选择INNODB引擎

    ③如果是用MyISAM的话,merge引擎可以大大加快应用部门的开发速度,他们只要对这个merge表做一些select count(*)操作,非常适合大项目总量约几亿的rows某一类型(如日志,调查统计)的业务表。

    14.sql执行慢的原因有哪些,如何进行sql优化?

    一、导致SQL执行慢的原因

    1、硬件问题。如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了等。

    2、没有索引或者索引失效。(一般在互联网公司,DBA会在半夜把表锁了,重新建立一遍索引,因为当你删除某个数据的时候,索引的树结构就不完整了。所以互联网公司的数据做的是假删除,一是为了做数据分析,二是为了不破坏索引 )

    3、数据过多(分库分表)

    4、服务器调优及各个参数设置(调整my.cnf)

    二、分析原因时,一定要找切入点

    1、先观察,开启慢查询日志,设置相应的阈值(比如超过3秒就是慢SQL),在生产环境跑上个一天过后,看看哪些SQL比较慢。

    2、Explain和慢SQL分析。比如SQL语句写的烂,索引没有或失效,关联查询太多(有时候是设计缺陷或者不得以的需求)等等。

    3、Show Profile是比Explain更近一步的执行细节,可以查询到执行每一个SQL都干了什么事,这些事分别花了多少秒。

    4、找DBA或者运维对MySQL进行服务器的参数调优。

    解析:

    (1)explain出来的各种item的意义

    id:每个被独立执行的操作的标志,表示对象被操作的顺序。一般来说, id 值大,先被执行;如果 id 值相同,则顺序从上到下。

    select_type:查询中每个 select 子句的类型。

    table:名字,被操作的对象名称,通常的表名(或者别名),但是也有其他格式。

    partitions:匹配的分区信息。

    type:join 类型。

    possible_keys:列出可能会用到的索引。

    key:实际用到的索引。

    key_len:用到的索引键的平均长度,单位为字节。

    ref:表示本行被操作的对象的参照对象,可能是一个常量用 const 表示,也可能是其他表的

    key 指向的对象,比如说驱动表的连接列。

    rows:估计每次需要扫描的行数。

    filtered:rows*filtered/100 表示该步骤最后得到的行数(估计值)。

    extra:重要的补充信息。

    (2)profile的意义以及使用场景

    Profile 用来分析 sql 性能的消耗分布情况。当用 explain 无法解决慢 SQL 的时候,需要用profile 来对 sql 进行更细致的分析,找出 sql 所花的时间大部分消耗在哪个部分,确认 sql的性能瓶颈。

    (3)explain 中的索引问题

    Explain 结果中,一般来说,要看到尽量用 index(type 为 const、 ref 等, key 列有值),避免使用全表扫描(type 显式为 ALL)。比如说有 where 条件且选择性不错的列,需要建立索引。

    被驱动表的连接列,也需要建立索引。被驱动表的连接列也可能会跟 where 条件列一起建立联合索引。当有排序或者 group by 的需求时,也可以考虑建立索引来达到直接排序和汇总的需求。

    参考:高性能Mysql——创建高性能的索引

    15.视图的作用,视图可以更改么?

    视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询;不包含任何列或数据。使用视图可以简化复杂的sql操作,隐藏具体的细节,保护数据;视图创建后,可以使用与表相同的方式利用它们。

    视图不能被索引,也不能有关联的触发器或默认值,如果视图本身内有order by 则对视图再次order by将被覆盖。

    创建视图:create view XXX as XXXXXXXXXXXXXX;

    对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等,是可以对其更新的,对视图的更新将对基表进行更新;但是视图主要用于简化检索,保护数据,并不用于更新,而且大部分视图都不可以更新。

    16. 说一说MySQL中的锁机制

    按粒度分:

    表级锁:粒度最大的一种锁,表示对当前操作的整张表加锁。开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

    行级锁:粒度最小的一种锁,表示只针对当前操作的行进行加锁。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高

    页级锁:粒度介于行级锁和表级锁中间的一种锁。开销、加锁时间和并发度界于表锁和行锁之间;会出现死锁

    按操作分:

    读锁(共享锁):针对同一份数据,多个读取操作可以同时进行,不互相影响

    写锁(排它锁):当前写操作没有完成前,会阻断其他写锁和读锁

    行级死锁

    17. 数据库事务四大特性?

    原子性(Atomic):一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚到事务开始前的状态,就像这个事务从来没有执行过一样

    一致性(Consistency):在事务开始之前和事务结束以后, 数据库的完整性没有被破坏

    隔离性(Isolation):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)

    持久性(Durability):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失

    18.ACID、BASE和CAP?

    参考:DDBS BASE

    参考:DDBS CAP

    19.MySQL如何获取当前日期?

    SELECT CURRENT_DATE();

    20.Mysql驱动程序是什么?

    驱动程序主要帮助编程语言与 MySQL 服务端进行通信,如果连接、关闭、传输指令与数据等

    21.Innodb引擎有什么特性?

    插入缓冲(insert buffer)

    二次写(double write)

    自适应哈希索引(ahi)

    预读(read ahead)

    插入缓冲(insert buffer)解释:

    对于聚簇索引,当执行插入操作时,id列会自动增长,页中行记录按id顺序存放,不需要随机读取其它页的数据。因此,在这样的情况下,插入操作效率很高。

    对于非聚簇索引,可能叶子节点的插入不再有序,这时就需要离散访问非聚集索引页,插入性能变低。

    Innodb是怎么解决这个性能变低的情况呢?也就是采用插入缓冲。

    对于非聚集类索引的插入和更新操作,不是每一次都直接插入到索引页中,而是先插入到内存中。具体做法是:如果该索引页在缓冲池中,直接插入;否则,先将其放入插入缓冲区中,再以一定的频率和索引页合并,这时,就可以将同一个索引页中的多个插入合并到一个IO操作中,大大提高写性能。

    插入缓冲的启用需要满足一下两个条件:

    1)索引是非聚簇索引

    2)索引不适合唯一的 。

    如果辅助索引是唯一的,就不能使用该技术,原因很简单,因为如果这样做,整个索引数据被切分为2部分,无法保证唯一性。

    二次写(double write)的解释:

    想象这么一个场景,当数据库正在从内存向磁盘写一个数据页时,数据库宕机,从而导致这个页只写了部分数据,这就是部分写失效,它会导致数据丢失。这时是无法通过redo log恢复的,因为重做日志记录的是对页的物理修改,如果页本身已经损坏,重做日志也无能为力。

    从上面分析我们知道,在部分写失效的情况下,我们在应用重做日志之前,需要原始页的一个副本,两次写就是为了解决这个问题。

    其原理是这样的:

    1)当刷新缓冲池脏页时,并不直接写到数据文件中,而是先拷贝至内存中的两次写缓冲区。

    2)接着从两次写缓冲区分两次写入磁盘共享表空间中,每次写入1MB

    3)待第2步完成后,再将两次写缓冲区写入数据文件

    下面是它的原理图。

    这样就可以解决上文提到的部分写失效的问题,因为在磁盘共享表空间中已有数据页副本拷贝,如果数据库在页写入数据文件的过程中宕机,在实例恢复时,可以从共享表空间中找到该页副本,将其拷贝覆盖原有的数据页,再应用重做日志即可。

    自适应哈希索引(ahi)

    哈希索引是一种非常快的等值查找方法(注意:必须是等值,哈希索引对非等值查找方法无能为力),它查找的时间复杂度为常量,InnoDB采用自适用哈希索引技术,它会实时监控表上索引的使用情况,如果认为建立哈希索引可以提高查询效率,则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引。

    之所以该技术称为“自适应”是因为完全由InnoDB自己决定,不需要DBA人为干预。它是通过缓冲池中的B+树构造而来,且不需要对整个表建立哈希索引,因此它的数据非常快。

    预读

    参考:https://www.cnblogs.com/geaozhang/p/7397699.html

    22.索引对性能有哪些影响?

    优点:

    减少数据库服务器需要扫描的数据量

    帮助数据库服务器避免排序和临时表

    将随机 I/O 变顺序I/O

    提高查询速度

    唯一索引,能保证数据的唯一性

    缺点:

    索引的创建和维护耗时随着数据量的增加而增加

    对表中数据进行增删改时,索引也要动态维护,降低了数据的维护速度

    增大磁盘占用

    23.二进制日志(binlog)的作用?与redo log的区别?

    用于复制,在主从复制中,从库利用主库上的binlog进行重播,实现主从同步。

    用于数据库的基于时间点的还原。

    区别:

    redo log是在InnoDB存储引擎层产生,而binlog是MySQL数据库的上层产生的,并且二进制日志不仅仅针对INNODB存储引擎,MySQL数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

    两种日志记录的内容形式不同。MySQL的binlog是逻辑日志,其记录是对应的DDL和DML语句。而innodb存储引擎层面的重做日志是物理日志。

    两种日志与记录写入磁盘的时间点不同,二进制日志只在事务提交完成后进行一次写入。而innodb存储引擎的重做日志在事务进行中不断地被写入,并日志不是随事务提交的顺序进行写入的。

    binlog可以作为恢复数据使用,主从复制搭建,redo log作为异常宕机或者介质故障后的数据恢复使用。

    binlog不是循环使用,在写满或者重启之后,会生成新的binlog文件,redo log是循环使用。

    24.InnoDB的行锁/表锁?

    mysql的行锁是通过索引加载的,即是行锁是加在索引响应的行上的。

    要是对应的SQL语句没有走索引,则会全表扫描,此时取而代之的是表锁。

    表锁:不会出现死锁,发生锁冲突几率高,并发低。(表锁总是一次性获得所需的全部锁,要么全部满足,要么全部等待。所以不会产生死锁。)

    行锁:会出现死锁,发生锁冲突几率低,并发高。

    行锁分 共享锁 和 排它锁。

    共享锁又称:读锁。当一个事务对某几行上读锁时,允许其他事务对这几行进行读操作,但不允许其进行写操作,也不允许其他事务给这几行上排它锁,但允许上读锁。

    select math from zje where math>60 lock in share mode;

    排它锁又称:写锁。当一个事务对某几个上写锁时,不允许其他事务写,但允许读。更不允许其他事务给这几行上任何锁。包括写锁。

    select math from zje where math >60 for update;

    25.什么是MVCC ?

    MVCC全称是: Multiversion concurrency control,多版本控制: 指的是一种提高并发的技术。

    一般我们认为MVCC有下面几个特点(也就是乐观锁的一种实现):

    每行数据都存在一个版本,每次数据更新时都更新该版本

    修改时Copy出当前版本, 然后随意修改,各个事务之间无干扰

    保存时比较版本号,如果成功(commit),则覆盖原记录, 失败则放弃copy(rollback)

    就是每行都有版本号,保存时根据版本号决定是否成功,听起来含有乐观锁的味道, 因为这看起来正是,在提交的时候才能知道到底能否提交成功

    而InnoDB实现MVCC的方式是:

    事务以排他锁的形式修改原始数据

    把修改前的数据存放于undo log,通过回滚指针与主数据关联

    修改成功(commit)啥都不做,失败则恢复undo log中的数据(rollback)

    MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。

    参考:MVCC

    26. sql 语句在 MySQL 中的执行流程

    参考:高性能Mysql——一条SQL语句在Mysql中是如何执行的?

    展开全文
  • MySQL常见面试题

    千次阅读 2021-05-13 17:29:40
    如果也没有这样的唯一索引,则InnoDB选择内置6字节长的ROWID作为隐含的聚集索引。 为避免Mysql为我们维护隐性的索引,故而InnoDB表要建主键。 为什么推荐使用整型索引呢? 在定位某一个索引时,需要进行多次的数据...
  • MySQL面试必会100道

    千次阅读 2019-04-08 18:08:19
    要求:每两个同学一组,一个口头考,一个上机实战作答,每5个为一组,完成后换位 1.开启MySQL服务 2.检测端口是否运行 3.为MySQL设置密码或者修改密码 4.登陆MySQL数据库 5.查看当前数据库的字符集 6.查看...
  • 因为 MyISAM 表把自增主键的最大 ID 记录到数据文件里,重启 MySQL 自增主键的最大ID 也不会丢失 (2)如果表的类型是 InnoDB,那么是 15 InnoDB 表只是把自增主键的最大 ID 记录到内存中,所以重启数据库或者是...
  • MySQL面试常见题目(六)

    千次阅读 2021-11-08 09:14:06
    原文地址:MySQL面试常见题目(六) 1、字段为何需定义NOTNULL? NULL占用更多字节,且NULL有很多坑。 2、如需存储用户密码散列,应使用什么字段? 应使用char,可节省空间且提高检索效率。 3、如何优化大...
  • linux运维必会Mysql企业面试题.pdflinux运维必会Mysql企业面试题.pdflinux运维必会Mysql企业面试题.pdflinux运维必会Mysql企业面试题.pdflinux运维必会Mysql企业面试题.pdflinux运维必会Mysql企业面试题.pdflinux...
  • linux运维必会Mysql企业面试题.docxlinux运维必会Mysql企业面试题.docxlinux运维必会Mysql企业面试题.docxlinux运维必会Mysql企业面试题.docxlinux运维必会Mysql企业面试题.docxlinux运维必会Mysql企业面试题....
  • MySql常考面试 每个人找工作面试必会题目, 囊括了基本用法及常见考题, 初学者掌握其中技巧 mysql关键字 固定搭配 解题思路
  • mysql面试必会基础(一)

    万次阅读 2016-04-09 12:25:02
    本博客摘自《MySQL必知必会》,对基础问题进行总结。 1.检索数据 (1)SELECT columnname FROM tablename;返回结果不一定和插入顺序相同,结果是无序的 (2)去掉重复的值 SELECT DISTINCT columnname FROM ...
  • 个人在面试中被问到以及收集网上的高频Mysql面试题,希望可以帮助到备战求职的同学。
  • Mysql面试题(100+)

    千次阅读 2022-06-27 06:07:42
    6、数据库索引的原理,为什么要用B+树,为什么不用二叉树? 7、聚集索引与非聚集索引的区别 8、limit1000000加载很慢的话,你是怎么解决的呢? 9、如何选择合适的分布式主键方案呢? 10、事务的隔离级别有哪些?...
  • 10道经典MySQL面试题

    千次阅读 2018-12-10 20:19:37
    truncate清除表数据并重置id从1开始,delete就只删除记录,drop可以用来删除表或数据库并且将表所占用的空间全部释放 truncate和delete只删除数据不删除表的结构。drop语句将删除表的结构被依赖的约(constra...
  • 经过两次整理,更有逻辑更易记忆的MySQL基础知识及面试问题总结, 有用可以收藏❤️,我相信这个早晚能帮到你
  • mysql经典面试题(有答案)

    千次阅读 2018-02-03 23:17:24
    经典题目 1、MySQL的复制原理以及流程 基本原理流程,3个线程以及之间的关联; 2、MySQL中myisam与innodb的区别,至少5点 (1)、问5点不同; (2)、innodb引擎的4大特性 (3)、2者selectcount(*)哪个更快,为什么...
  • 说结论:面试非常有用,在实际工作中,作用不大…八股文绝大部分都是理论...当你真正在项目中需要使用到底层技术的时候,去学习比较有感触…说白了,八股文就是互联网面试的必备技能,对于实际工作中,那roi太低了…
  • 大家都知道,无论你面试任何一个厂MySQL基本上是面试必问的。经过无数次面经过后我总结了以下108道MySQL常见的面试,望对大家有帮助!​ 目录 数据库 1. MySQL 索引使用有哪些注意事项呢?2. MySQL 遇到过...
  • 面试常问必备之MySQL面试5510
  • 面试必备的10道MySQL题

    2021-01-19 07:56:48
    MySQL 事务,是我们去面试中高级开发经常被问到的问题,很多人虽然经常使用 MySQL,SQL 语句也写得很溜,但是面试的时候,被问到这些问题,总是不知从何说起。下面我们先来了解一下什么是 MySQL事务,再给大家分享...
  • 面试数据分析遇到的SQL

    千次阅读 2020-12-29 12:11:47
    「1」说在前面 数据存放在数据库里,以表的形式分门别类。... 更多 SQL 面试题会不断补充,还是基于这 4 张表; 希望投身数据浪潮的盆友,可以看这篇回答:3个月拿到数据分析offer~ 欢迎沟通,接受正向交流~
  • 网上的运维面试题文章有非常多,在我的博客中也有一些是这些年运维人员面试面试题,之前一些经典面试题我已经整理到专栏《运维面试宝典》里,这个专栏里的面试技巧可以说永远不会过时,而且我每隔一段时间进行...
  • MYSQL简单练习,包含基本的增删查改,各种函数,使用场景。多熟练,掌握基本工作需求。
  • MySQL高级面试题

    千次阅读 2021-11-13 08:17:13
    一、MySQL逻辑架构 1.Connectors Connectors,指的是不同语言中与SQL的交互。 2. 连接层 最上层是客户端的连接服务,采用 TCP/IP,该层有一个 线程池 ,每一个连接从线程池中获取线程,省去了创建和销毁线程的...
  • 6、主键和候选键有什么区别?7、myisamchk 是用来做什么的?8、如果一个表有一列定义为TIMESTAMP,将发生什么?9、你怎么看到为表格定义的所有索引?10、LIKE 声明中的%和是什么意思?% 对应于 0 个或更多字符,...
  • MySQL面试题大全,MySQL刷的那些面试(2021版) 注意:文末附本套MySQL面试题的参考答案 1、什么是SQL? 2、什么是MySQL? 3、数据库三大范式是什么? 4、mysql有关权限的表都有哪几个? 5、MySQL的binlog...
  • 6、主键和候选键有什么区别?7、myisamchk是用来做什么的?8、如果一个表有一列定义为TIMESTAMP,将发生什么?9、你怎么看到为表格定义的所有索引?10、LIKE声明中的%和\_是什么意思?11、列对比运算符是什么?12、...
  • 2022年MySQL最新面试题

    千次阅读 2022-04-02 15:14:56
    最近整理一份关于MySQL常见面试题的,也根据自己的经验, 标注一些出现的概率,最高5颗★出现的概率最高。比如这样: 百万级别或以上的数据如何删除 出现概率: ★★★ 一般来讲在面试当中, 关于数据库相关的面试题...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 38,634
精华内容 15,453
热门标签
关键字:

mysql面试必会6题经典

mysql 订阅
友情链接: CapPlus.rar