mysql备忘录

网址
https://mp.weixin.qq.com/mp/appmsgalbum?__biz=MzAwNDA2OTM1Ng==&action=getalbum&album_id=1343708196397187073&scene=173&from_msgid=2453141466&from_itemidx=1&count=10#wechat_redirect&scene=178&subscene=&sessionid=svr_62988d1ae81&enterid=1606829461

基础

  1. UNION与UNION ALL的区别?
    如果使用UNION ALL,不会合并重复的记录行
    效率 UNION 高于 UNION ALL
  2. 窗口函数
    <窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
    专用窗口函数,包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
  3. TRUNCATE、DELETE 、DROP的区别?
    ① DELETE 在 InnoDB 中,DELETE其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。
    delete 操作是一行一行执行删除的,并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚(rollback)和重做操作,生成的大量日志也会占用磁盘空间。
    ② TRUNCATE 执行后立即生效,无法找回;truncate table table_name 立刻释放磁盘空间 。
    ③ DROP 执行后立即生效,无法找回;truncate table table_name 立刻释放磁盘空间 。
    可以这么理解,一本书,delete是把目录撕了,truncate是把书的内容撕下来烧了,drop是把书烧了。
    一般而言,速度:drop > truncate > delete

索引

  1. 主键索引:唯一且不为空。一个表只能有一个主键索引,但是可以有多个唯一索引。
    联合索引:遵循前缀原则
  2. 只要列涉及到运算,MySQL就不会使用索引。比如 SELECT FROM users WHERE YEAR(adddate) < 2007;
  3. B+tree 索引树高度影响因素:索引列值过长 , 数据量级,数据类型
  4. 聚簇索引:按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据
  5. 索引的数据结构?
    索引数据结构有哈希表、完全平衡二叉树、B树、B+树。
    Hash不支持范围查询,二叉树树高很高,B+树是B树的升级版,会有指针指向下一个节点的叶子节点。
    B+Tree相对于B-Tree的不同:B+Tree非叶子节点只存储键值信息, 数据记录都存放在叶子节点中。所以B+Tree的高度可以被压缩到特别的低。
  6. 回表 覆盖索引
    如果在我们建立的索引上就已经有我们需要的字段,就不需要回表了,在电商里面也是很常见的,我们需要去商品表通过各种信息查询到商品id,id一般都是主键,可能sql类似这样:
select itemId from itemCenter where size between 1 and 6
  1. 最左匹配原则是什么?
    联合索引会一直向右匹配直到遇到范围查询就停止匹配。
  2. explain你记得哪些字段,分别有什么含义?
    table(查询操作的表,可精确到问题表)、type(索引类型)、possible_keys(可能会走的索引)、key(真正走的索引)、key_len(联合索引覆盖长度)、rows、Extra
    ① type :索引类型
    all:全表扫描,不用任何索引
    index:全索引扫描 —> 把整个索引树扫描一遍
    range:索引范围扫描
    —> 辅助索引> < >= <= LIKE IN OR
    —> 特殊情况:主键 <> NOT IN
    ref:非唯一性索引,辅助索引等值查询
    eq_ref:针对多表连接中,非驱动表连接条件是主键或唯一键
    const( system):聚簇索引等值查询
  3. change buffer 是什么?使用场景是?
    ① 当更新一个数据页时, 如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InooDB会将这些更新操作缓存在change buffer中,在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行change buffer中与这个页有关的操作,通过这种方式就能保证这个数据逻辑的正确性。
    ② 唯一索引的更新就不能使用change buffer,实际上也只有普通索引可以使用。
    ③ 对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时change buffer的使用效果最好,这种业务模型常见的就是账单类、日志类的系统。

引擎

  1. MyISAM索引与InnoDB索引的区别?
    InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
    InnoDB的主键索引的叶子节点存储着行数据, 因此主键索引非常高效;MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
  2. InnnoDB的特性
    AHI:帮助InnoDB快速读取索引页。加快索引读取的所读。相当与索引的索引。
    change buffer:临时缓冲辅助索引需要的数据更新。当我们需要查询新insert 的数据,会在内存中进行merge(合并)操作,此时辅助索引就是最新的。
    MVCC:多版本并发控制。MVCC就是用同一份数据临时保留多版本的方式的方式,实现并发控制
    事务
    行锁
    自动故障恢复
  3. InnoDB引擎体系结构
    myt.frm:存放数据字典信息(列的定义和属性)
    myt.ibd:独立表空间文件,存放数据行和索引信息
    ibdata1:共享表空间文件,存放数字字典信息,undo logs、double write、change buffer磁盘区域
  4. InnoDB 和 MyISAM 的区别
    InnoDB支持事务,MyISAM不支持;
    InnoDB数据存储在共享表空间,MyISAM数据存储在文件中;
    InnoDB支持行级锁,MyISAM只支持表锁;
    InnoDB支持崩溃后的恢复,MyISAM不支持;
    InnoDB支持外键,MyISAM不支持;
    InnoDB不支持全文索引,MyISAM支持全文索引;
  5. 锁机制
    共享锁:不堵塞,多个用户可以同时读一个资源,互不干扰。
    排他锁:一个写锁会阻塞其他的读锁和写锁 ,这样可以只允许一个用户进行写入,防止其他用户读取正在写入的资源。
    表锁、页锁、行锁、间隙锁

事务

  1. 事务的ACID特性:A(原子性)、C(一致性)、I(隔离性)、D(持久性)
  2. redo logs:在MySQL出现Crash异常宕机时,提供前滚功能(CSR)
    undo logs:在rollback时,会将数据恢复到修改之前的状态。回滚
  3. 数据库并发会引起的问题:脏读 、不可重复读、幻读
    脏读:A事务读取B事务尚未提交的更改数据,并在这个数据基础上操作。如果B事务回滚,那么A事务读到的数据根本不是合法的,称为脏读
    不可重复读:A事务读取了B事务已经提交的更改(或删除)数据。比如A事务第一次读取数据,然后B事务更改该数据并提交,A事务再次读取数据,两次读取的数据不一样 update
    幻读:A事务读取了B事务已经提交的新增数据。注意和不可重复读的区别,这里是新增,不可重复读是更改(或删除) insert
  4. 事务的隔离级别:
    ① read-uncommitted
    读未提交,一个事务还没提交时,它做的变更就能被别的事务看到,即允许读取未提交的数据,会导致脏读、不可重复读、幻读的问题
    ② read-committed
    读已提交,一个事务提交之后,它做的变更才会被其他事务看到,这里的读指的是一致性非锁定读 ,即每次都读最新的快照数据,不加共享锁,解决了脏读问题,但仍会导致不可重复读、幻读问题。
    读操作事务要等待这个更新操作事务提交后才能读取数据,可以解决脏读问题。但在这个事例中,出现了一个事务范围内两个相同的查询却返回了不同数据,这就是不可重复读。
    ③ repeatable-read
    可重复读,MySQL的默认隔离级别,总是会在事务开启的时候读取最新提交的行版本,并将该行版本一直持有到事务结束,未提交变更对其他事务也是不可见的,解决了不可重复读问题,但仍有可能发生幻读问题
    ④ serializable
    可串行化,这种隔离级别不会造成任何并发问题,但并发性能极低
  5. binglog:记录的是变更类型的SQL语句,不记录查询语句
    记录格式:
    statement: 可读性较高(直接存储语句),日志量少,但是不够严谨
    row:可读性低(需要借助工具分析),日志量大,足够严谨
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页