mysql 面试题

斜上方45度
143 阅读14分钟

时间戳日期格式化 函数

# 日期格式化
SELECT DATE_FORMAT('2021-09-21 15:55:25','%Y-%m-%d');
	2021-09-21
#时间戳转日期
select FROM_UNIXTIME(1589857200);
	2020-05-19 11:00:00
select FROM_UNIXTIME(1589857200,'%Y-%m-%d %H:%i:%s');
	2020-05-19 11:00:00
# 日期转时间戳
select UNIX_TIMESTAMP('2020-05-19')
	1589817600

#时间戳转日期然后在格式化

SELECT DATE_FORMAT(FROM_UNIXTIME(1589857200),'%Y-%m-%d %H:%i:%s')
	2020-05-19 11:00:00

sql 书写顺序

select ：指定查看结果集中的那个列或列的计算结果
join
from ：从哪张表查询数据

where ：筛选过滤条件

group by ：将上面过滤出的数据，进行分组

having ：对上面已经分组的数据进行过滤的条件

order by ：对查询到的结果进行排序。升序：asc，降序：desc

sql 统计 函数

AVG (平均)
COUNT (计数)
MAX (最大值)
MIN (最小值)
SUM (求合)

sql 常见子查询

select

子查询都可以出现在哪里呢？
select
..(select).
from
..(select).
where
..(select).

mysql时间戳日期格式化 函数

 # 日期格式化
    SELECT DATE_FORMAT('2021-09-21 15:55:25','%Y-%m-%d'); 2021-09-21
 #时间戳转日期
     select FROM_UNIXTIME(1589857200); 2020-05-19 11:00:00 
     select FROM_UNIXTIME(1589857200,'%Y-%m-%d %H:%i:%s'); 2020-05-19 11:00:00
 # 日期转时间戳
     select UNIX_TIMESTAMP('2020-05-19') 1589817600
 #时间戳转日期然后在格式化
     SELECT DATE_FORMAT(FROM_UNIXTIME(1589857200),'%Y-%m-%d %H:%i:%s') 2020-05-19 11:00:00

** 连接查询的分类？**

   内连接：
        等值连接
        非等值连接
        自连接
   外连接：
        左外连接（左连接）
        右外连接（右连接）

    全连接

约束包括哪些？

 非空约束：not null
唯一性约束: unique
主键约束: primary key （简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，最新版支持）

什么是事务？

一个事务其实就是一个完整的业务逻辑。 是一个最小的工作单元。不可再分。

什么是一个完整的业务逻辑？

假设转账，从A账户向B账户中转账10000.
将A账户的钱减去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。

以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。 这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

事务包括4个特性？

A：原子性
    说明事务是最小的工作单元。不可再分。

C：一致性
    所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，
    以保证数据的一致性。

I：隔离性
    A事务和B事务之间具有一定的隔离。
    教室A和教室B之间有一道墙，这道墙就是隔离性。
    A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？

D：持久性
    事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据
    保存到硬盘上！

** 重点研究一下事务的隔离性！！！**

A教室和B教室中间有一道墙，这道墙可以很厚，也可以很薄。这就是事务的隔离级别。
这道墙越厚，表示隔离级别就越高。

事务和事务之间的隔离级别有哪些呢？4个级别

    读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》
        什么是读未提交？
            事务A可以读取到事务B未提交的数据。
        这种隔离级别存在的问题就是：
            脏读现象！(Dirty Read)
            我们称读到了脏数据。
        这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

    读已提交：read committed《提交之后才能读到》
        什么是读已提交？
            事务A只能读取到事务B提交之后的数据。
        这种隔离级别解决了什么问题？
            解决了脏读的现象。
        这种隔离级别存在什么问题？
            不可重复读取数据。
            什么是不可重复读取数据呢？
                在事务开启之后，第一次读到的数据是3条，当前事务还没有
                结束，可能第二次再读取的时候，读到的数据是4条，3不等于4
                称为不可重复读取。

        这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
        oracle数据库默认的隔离级别是：read committed

    可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》
        什么是可重复读取？
            事务A开启之后，不管是多久，每一次在事务A中读取到的数据
            都是一致的。即使事务B将数据已经修改，并且提交了，事务A
            读取到的数据还是没有发生改变，这就是可重复读。
        可重复读解决了什么问题？
            解决了不可重复读取数据。
        可重复读存在的问题是什么？
            可以会出现幻影读。
            每一次读取到的数据都是幻象。不够真实！
         
        早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！
        读到的是假象。不够绝对的真实。

        mysql中默认的事务隔离级别就是这个！！！！！！！！！！！

    序列化/串行化：serializable（最高的隔离级别）
        这是最高隔离级别，效率最低。解决了所有的问题。
        这种隔离级别表示事务排队，不能并发！
        synchronized，线程同步（事务同步）
        每一次读取到的数据都是最真实的，并且效率是最低的。

什么是索引： 索引是提高mysql查询的一种数据结结构

索引的优点缺点是什么： 优点提高检索速度 缺点占用空间，维护索引消耗数据库资源,对表的数据进行增删改的时候会降低速度

索引分类：

主键索引： 主键索引为聚簇索引，当创建表的时候主键会自动创建个主键索引，表中只能有一个主键索引

单值索引：单列索引 普通索引 一个索引包含一列 ，一个表中可以有很多个单值索引可以为空

唯一索引： 索引不能重复，可以为空 但是只能存在一个null

复合索引： 一个索引包含多个列

  最左侧匹配原则，  
    name age bir .最左前缀原则.
    mysql引擎在查询为了更了更好利用索引在查询过程中会动态调整查询宁段顺序以便利用索引
    
    能否利用索引
    name bir age     可以
    name age bir  可以
    name age bir肯定可以
    age bir 不行
    bir age name 可以
    age bir  不行

全文索引： 不怎么常用

b+tree与 b-tree

B+Tree是在B-Tree基础上的一种优化， 从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

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

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

image.png

  • 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的高度一般都在24层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要13次磁盘I/O操作。

聚簇索引和非聚簇索引

聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

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

ce9bedd0dc9013e14e5f450e2149704bef5.jpg

InnoDB中

  • InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

  • 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

  • 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId） 来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。 MYISAM

  • MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

59066cb190ec7579c34e2cd77a1f47e8b68.jpg 使用聚簇索引的优势

  • 问题: 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

  • 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

  • 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

聚簇索引需要注意什么?

  • 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
  • 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id

  • 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。 什么情况下无法利用索引呢?

    1. 查询语句中使用LIKE关键字 在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

  • 2.查询语句中使用多列索引 多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。

  • 3.查询语句中使用OR关键字 查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

innodb与myisam区别 mysql innodb的表由.frm .ibd 组成，ibd：存了每个表的元数据，包括表结构的定义等；frm：存了每个表的元数据，包括表结构的定义等；

myisam，表有.frm .MYD .MYI 组成

（1）*.frm--表定义，是描述表结构的文件。

（2）*.MYD--"D"数据信息文件，是表的数据文件。

（3）*.MYI--"I"索引信息文件，是表数据文件中任何索引的数据树。

avatar
文章
阅读
粉丝