mySQL面试题

MySQL面试题

1.InnoDB索引和MyISAM索引的区别是什么？

  1. InnoDB的索引的索引在存储的时候，他的索引是和数据是存储在一个文件里的，MyisAm的索引是另外的一个文件，和数据不是在一个文件里。
  2. 索引文件所对应的B+树的最叶子结点记录的数据有所区别，InnoDB里是存储的数据行的Id，而MyIsAm里存储的是指针，指向记录所在页的指针

2.一个表如果没有创建索引，还会去创建B+树么？

会，表创建时会默认创建一个聚簇索引树，没有Id也会默认生成一个rowid,然后生成聚簇索引树

3.MySQL的binlog有有几种录入格式？分别有什么区别？

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

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

4.InnoDB和MyisAm有什么区别？

主要有4个区别：

  1. 第一个是数据存储的方式不同，MyisAm里的数据和索引是分开存储的，而InnoDB是把索引和数据文件存放在一个文件里面。
  2. 第二个，对于事务的支持不同，MyisAm里不支持事务，而InnODB里支持事务
  3. 第三，对于锁的支持不同，MYisAm里只支持表锁，而InnoDB里可以根据不同的情况去支持行锁，表锁，间隙锁，临键锁。
  4. 第四，对外键的支持不同，MyisAm里不支持外键，而InnDB支持外键

5.InnoDB引擎的4大特性

  • 插入缓存，Insert buffer

索引数据存储在磁盘上，主键索引由于天然自增，无须磁盘的随机 I/O，只需不断追加即可。但普通索引大概率无序，默认情况下需要进行随机磁盘 I/O 操作，效率极差

为了解决普通索引插入效率低下的问题，InnoDB 存储引擎引入 Insert Buffer 的概念，对于普通索引（非聚集索引）不是直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓存池中，

如果在直接插入，否则先放入 Insert buffer 对象中，然后以一定频率和辅助索引页子节点进行合并操作，此时通常能将多个插入合并到一个操作中，提高插入性能

  • 二次写，double write

InnoDB 索引页一般 16KB 大小，而操作系统写文件以 4KB 为单位，这就导致同一页需要分四块分别写入。此时就存在写完一块系统崩溃或者断电等特殊情况，此时就导致写入数据不完整的问题

二次写就是为了解决该问题，double write 分为两部分，一部分 doublewrite buffer，其大小 2MB，另一部分是磁盘上共享表空间中连续的 128 个页，也是 2,B

每次刷盘时这样处理：先将脏数据写入 doublewrite buffer，doublewrite buffer 每次 1MB 写入共享表空间的磁盘上，完成以上两步后调用 fsync 函数，将数据同步到各个表空间

如果操作系统在将页写入磁盘的过程中崩溃，InnoDB 重启发现页数据损坏后，可以从共享表的 doublewrite 中找到副本，用于数据恢复

  • 自适应哈希索引，adaptive hash index

InnoDB 虽然主要使用 B+ 树作为索引结构，但在某些特殊场景下用到哈希索引。InnoDB 会监控对表上索引的查找，如果发现某个索引频繁被访问，则建立哈希索引。

InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引

  • 预读，read ahead

当 InnoDB 预计某些页很快就要被访问时，会异步加载对应页数据到缓冲池。该思路就类似空间局部性：如果某块内存被访问，那么它周围的内存大概率也会被访问。

InnoDB 采用两种预读算法提高 I/O 性能：线性预读 和 随机预读

线性预读：以块为单位，一块等于64页。如果某一块中的被顺序读取的页数超过预定值，则 InnoDB 将会异步的将下一块读取到 buffer pool 中

随机预读：以页为单位，当某一个块中的一些页在 buffer pool 中被发现时，InnoDB 会将该块中的剩余页一并读到 buffer pool 中，目前已废弃

6.什么是索引？

  • 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
  • 索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
  • 更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

7.索引有哪几种类型？

  • 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
  • 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
  • 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
  • 全文索引： 是目前搜索引擎使用的一种关键技术。

8.索引的数据结构（b树，hash）

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

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

查询方式：
主键索引区:PI(关联保存的时数据的地址)按主键查询,普通索引区:si(关联的id的地址,然后再到达上面的地址)。所以按主键查询,速度最快 
B+tree性质：

  • n棵子tree的节点包含n个关键字，不用来保存数据而是保存数据的索引。
  • 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
  • 所有的非终端结点可以看成是索引部分，结点中仅含其子树中的最大（或最小）关键字。
  • B+ 树中，数据对象的插入和删除仅在叶节点上进行。
  • B+树有2个头指针，一个是树的根节点，一个是最小关键码的叶节点。

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

9.创建索引的原则（重中之重）

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

  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. 尽量的扩展索引，不要新建索引。
  6. 定义有外键的数据列一定要建立索引。
  7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
  8. 对于定义为text、image和bit的数据类型的列不要建立索引。

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

  • 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。

你应该用0、一个特殊的值或者一个空串代替空值；

  • 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字
  • 段的离散程度高：索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

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

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

 

  • 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
  • 基于非唯一性索引的检索

12.什么是最左前缀原则？什么是最左匹配原则

　　顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，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的查询优化器会帮你优化成索引可以识别的形式


13.B树和B+树的区别

  1. 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
  2. B+树的叶子节点有一条链相连，而B树的叶子节点各自独立

14.使用B+树的好处

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

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

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

  • hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。
  • 对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。
    那么可以看出他们有以下的不同：
  1. hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。
  2. 而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。hash索引不支持使用索引进行排序，原理同上。
  3. hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。
  4. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
  5. hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳

定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

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

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

 

17.什么是聚簇索引？何时使用聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索
引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀
索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值何时使用聚簇索引与非聚簇索引


18.非聚簇索引一定会回表查询吗？

　　不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。
举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行selectage from employee where age < 20的查询时，在索引的叶子节点上，已经包含了
age信息，不会再次进行回表查询。

19.什么是数据库事务？

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

20.事务的四大特性(ACID)介绍一下?

https://www.cnblogs.com/hualou/p/12076017.html

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

21.什么是脏读？幻读？不可重复读？

  • 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
  • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据
  • 幻读(Phantom Read):一个事务在前后两次查询同一个范围的时候、后一次查询看到了前一次查询未看到的行.

22.什么是事务的隔离级别？MySQL的默认隔离级别是什么？

一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。

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

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

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默
认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

 

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

　　关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
　　MyISAM和InnoDB存储引擎使用的锁：行级锁，表级锁和页级锁对比

行级锁：行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁：页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

24.从锁的类别上分MySQL都有哪些锁呢？

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

  • 共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
  • 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁
    只可以加一个，他和其他的排他锁，共享锁都相斥。
    锁的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。他们的加锁开销从大到小，并发能力也是从大到小

25.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 锁定一个范围，包含记录本身

 

26.数据库的乐观锁和悲观锁是什么？怎么实现的？

  • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制
  • 乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：乐一般会使用版本号机制或CAS算法实现。

    两种锁的使用场景
    从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时
    候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

27.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要快

28.varchar与char的区别

char的特点

  • char表示定长字符串，长度是固定的；
  • 如果插入数据的长度小于char的固定长度时，则用空格填充
  • 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

varchar的特点

  • varchar表示可变长字符串，长度是可变的；插入的数据是多长，就按照多长来存储；
  • varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
  • 对于varchar来说，最多能存放的字符个数为65532总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体

情况还需具体来设计数据库才是妥当的做法。

 

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

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

id ：表示一个查询中各个子查询的执行顺序

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

select_type：每个子查询的查询类型，一些常见的查询类型。例如Simple,primary,subquery,union，derived等。

type：访问类型，可以看到是否走索引.此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all。

  • system、const：MySQL对查询的某部分进行优化并把其转化成一个常量（可以通过show warnings命令查看结果）。system是const的一个特例，表示表里只有一条元组匹配时为system。
  •  eq_ref：主键或唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。
  •   ref：相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。
  •  range：通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。
  •    index：扫描全索引拿到结果，一般是扫描某个二级索引，二级索引一般比较少，所以通常比ALL快一点。
  •     ALL：全表扫描，扫描聚簇索引的所有叶子节点。

possible_keys:此列显示在查询中可能用到的索引。如果该列为NULL，则表示没有相关索引，可以通过检查where子句看是否可以添加一个适当的索引来提高性能。

　　key:此列显示MySQL在查询时实际用到的索引。在执行计划中可能出现possible_keys列有值，而key列为null，这种情况可能是表中数据不多，

　　MySQL认为索引对当前查询帮助不大而选择了全表查询。如果想强制MySQL使用或忽视possible_keys列中的索引，在查询时可使用force index、ignore index。

 

key_len:  此列显示MySQL在索引里使用的字节数，通过此列可以算出具体使用了索引中的那些列。索引最大长度为768字节，当长度过大时，MySQL会做一个类似最左前缀处理，

将前半部分字符提取出做索引。当字段可以为null时，还需要1个字节去记录。

key_len计算规则：

  • 字符串：char(n)：n个数字或者字母占n个字节，汉字占3n个字
  • varchar(n)： n个数字或者字母占n个字节，汉字占3n+2个字节。+2字节用来存储字符串长度。

数字类型：

  • tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节

时间类型

  • date：3字节 timestamp：4字节 datetime：8字节

ref:  此列显示key列记录的索引中，表查找值时使用到的列或常量。常见的有const、字段名

rows: 此列是MySQL在查询中估计要读取的行数。注意这里不是结果集的行数。

 Extra:

此列是一些额外信息。常见的重要值如下：

1）Using index：使用覆盖索引（如果select后面查询的字段都可以从这个索引的树中获取，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值，这种情况一般可以说是用到了覆盖索引）。

2）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。

3）Using index condition：查询的列不完全被索引覆盖，where条件中是一个查询的范围。

4）Using temporary：MySQL需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的。

5）Using filesort：将使用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。

6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段时。

 

30.大表数据查询，怎么优化

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

31.超大分页怎么处理？

超大的分页一般从两个方向上来解决.
数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于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泄漏且连续被人恶意攻击.


32.主键使用自增ID还是UUID？

推荐使用自增ID，不要使用UUID。
因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索

引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内

存碎片，进而造成插入性能的下降。总之，在数据量大一些的情况下，用自增主键性能会好一些。关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索

引，如果没有唯一键，会生成一个隐式的主键

33.mysql索引失效的场景？

  1. 不符合最左匹配原则：

例如有一条sql:select * from table where a =1 and b = 1 and c = 1; 假如我们创建了一个(a,b,c)的索引，假如我们想用这个索引，则一定要有a字段的查询条件，因为a是索引的最左边字段

　　2.不正确的like查询：%写在左边会导致索引失效

例： select * from table where name like '%tom';

　　3.对索引列进行了函数计算:

例： select * from table where a + 1 = 2;

　　4.索引列进行了类型转换：表中是varchar类型，传进来的是数值会导致索引失效

　　5.不等于也会导致索引失效：

　　6.错误的order By 会导致索引失效：

　　7. or 连接查询条件会导致索引失效

　　8.select * 会导致索引失效，而select b 不会

　　9.范围查询的数据量过大也可能导致索引失效，Mysql预估全表扫描快的话，也不会走索引

　　10.条件中使用is null,或者is not null会导致索引失效

34.什么是MVCC?

https://www.bilibili.com/video/BV1AW4y1g75F/?spm_id_from=333.337.search-card.all.click&vd_source=912db34243165a8f1c8423819c1adaf4

 

 

 

 

 

 

 

 

 

 



 

 

 

 

 

 

 

 




 

posted @ 2023-03-23 16:32  杨阳洋^_^!  阅读(18)  评论(0)  编辑  收藏  举报