Mysql知识点整理

索引相关

abcd联合索引搜索ba会走索引么

会，重排

 

索引的底层实现是B+树，为何不采用红黑树，B树?

（1）：B+Tree非叶子节点只存储键值信息，降低B+Tree的高度，所有叶子节点之间都有一个链指针，数据记录都存放在叶子节点中

（2）：红黑树这种结构，h明显要深的多，效率明显比B-Tree差很多

（3）：B+树也存在劣势，由于键会重复出现，因此会占用更多的空间。但是与带来的性能优势相比，空间劣势往往可以接受，因此B+树的在数据库中的使用比B树更加广泛

 

索引失效条件

（1）：条件是or，如果还想让or条件生效，给or每个字段加个索引

（2）：like开头%

（3）：如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不会使用索引

（4）：where中索引列使用了函数或有运算

 

B+树的优点？

充分利用空间局部性原理，适合磁盘存储。树的高度很低，能够在存储大量数据情况下，进行较少的磁盘IO。能够很好支持单值，范围查询，有序性查询。

索引和数据分开存储，让更多的索引存储在内存中。

非叶子节点只能存储索引，叶子节点才存储数据。叶子节点是按照大小排序的，比较便于查找。范围查询更好。

所有的搜索都会在叶子节点中终结。

 

索引的分类？

 

数据结构角度

B+树索引

 

Hash索引[拉链法解决冲突]

优点

哈希算法时间复杂度为O(1)

缺点

哈希索引只支持等值比较查询 不支持范围查询

 Hash索引无法被用来避免数据的排序操作

 Hash索引不能利用部分索引键查询

 Hash索引在任何时候都不能避免表扫描

 Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高

 

Full-Text全文索引

full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型。full-text主要是用来代替like "%***%"效率低下的问题

 

R-Tree索引

r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。相对于b-tree，r-tree的优势在于范围查找.

 

从物理存储角度

 

聚集索引（clustered index）

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

简单来说 innodb 的索引和数据是在一起的 ，myism的索引和数据是分开的

 

非聚集索引（non-clustered index）

也叫辅助索引（secondary index）聚集索引和非聚集索引都是B+树结构

 

从逻辑角度

 

主键索引

主键索引是一种特殊的唯一索引，不允许有空值

 

普通索引或者单列索引

每个索引只包含单个列，一个表可以有多个单列索引

 

多列索引（复合索引、联合索引）

复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合唯一索引或者非唯一索引

 

空间索引

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建为什么MySQL 索引中用B+tree，不用B-tree 或者其他树，为什么不用 Hash 索引

 

 

B树与B+树的区别？

B+树查询时间复杂度固定是logn，B树查询复杂度最好是 O(1)。

B+树相邻接点的指针可以大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。

B+树更适合外部存储，也就是磁盘存储。由于内节点无 data 域，每个节点能索引的范围更大更精确

注意这个区别相当重要，是基于（1）（2）（3）的，B树每个节点即保存数据又保存索引，所以磁盘IO的次数很少，B+树只有叶子节点保存，磁盘IO多，但是区间访问比较好。

 

为什么不用B树做索引的数据结构？

B+树与B树相比，有以下优势：更少的IO次数：B+树的非叶节点只包含键，而不包含真实数据，因此每个节点存储的记录个数比B数多很多（即阶m更大），因此B+树的高度更低，访问时所需要的IO次数更少。此外，由于每个节点存储的记录数更多，所以对访问局部性原理的利用更好，缓存命中率更高。更适于范围查询：在B树中进行范围查询时，首先找到要查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可。更稳定的查询效率：B树的查询时间复杂度在1到树高之间(分别对应记录在根节点和叶节点)，而B+树的查询复杂度则稳定为树高，因为所有数据都在叶节点。

 

为什么用B+数做索引的数据结构？

高度相对固定，有效控制io次数。

所有叶子节点形成了一个有序链表，更加便于范围查找。

B+树更适合外部存储，由于内节点无data域，一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确，也意味着B+树单次磁盘IO的信息量大于B树，IO效率更高。

 

为什么MySQL数据库要用B+树存储索引？而不用红黑树、Hash、B树？

红黑树：如果在内存中，红黑树的查找效率比B树更高，但是涉及到磁盘操作，B树就更优了。因为红黑树是二叉树，数据量大时树的层数很高，从树的根结点向下寻找的过程，每读1个节点，都相当于一次IO操作，因此红黑树的I/O操作会比B树多的多。

hash 索引：如果只查询单个值的话，hash 索引的效率非常高。但是 hash 索引有几个问题：1）不支持范围查询；2）不支持索引值的排序操作；3）不支持联合索引的最左匹配规则。

B树索引：B树索相比于B+树，在进行范围查询时，需要做局部的中序遍历，可能要跨层访问，跨层访问代表着要进行额外的磁盘I/O操作；另外，B树的非叶子节点存放了数据记录的地址，会导致存放的节点更少，树的层数变高。

 

什么是回表查询？

InnoDB 中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。

而对于普通索引，叶子节点存储的是 key + 主键值，因此需要再走一次主键索引，通过主键索引找到行记录，这就是所谓的回表查询，先定位主键值，再定位行记录。

 

走普通索引，一定会出现回表查询吗？

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

很容易理解，有一个 user 表，主键为 id，name 为普通索引，则再执行：select id, name from user where name = 'joonwhee' 时，通过name 的索引就能拿到 id 和 name了，因此无需再回表去查数据行了。

为什么一个节点为1页就够了？

Innodb中，B+树中的一个节点存储的内容是：

非叶子节点：key + 指针

叶子节点：数据行（key 通常是数据的主键）

对于叶子节点：我们假设1行数据大小为1k（对于普通业务绝对够了），那么1页能存16条数据。

对于非叶子节点：key 使用 bigint 则为8字节，指针在 MySQL 中为6字节，一共是14字节，则16k能存放 16 * 1024 / 14 = 1170个。那么一颗高度为3的B+树能存储的数据为：1170 * 1170 * 16 = 21902400（千万级）。

所以在 InnoDB 中B+树高度一般为3层时，就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次 IO 操作即可查找到数据。千万级别对于一般的业务来说已经足够了，所以一个节点为1页，也就是16k是比较合理的。

 

主键与索引相互作用的过程？

如果一个主键被定义，则该主键作为密集索引

若没有定义主键，则该表的第一个或唯一非空作为密集索引

若不满足以上条件，innodb内部会生成一个隐藏主键

 

聚簇索引与非聚簇索引的区别？

都是B+树的数据结构，

• 聚簇索引：

存储与索引放到了一起，并且是按照一定的顺序组织的，找到索引就找到了数据，数据的物理存放数据与索引的顺序是一致的 即：只要索引是相邻的，那么对应的数据也一定是相邻的。

聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC）和余下的列

• 非聚簇索引：

叶子节点不存储数据，存储的是数据行地址，也就是说根据索引找到数据行的位置，再去磁盘查找数据，类似于书的目录，根据目录找到了页数，再去查找数。

MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。

从MYISAM存储的物理文件我们能看出，MYISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的。

 

存储引擎

（1）：Myiasm是mysql默认的存储引擎，不支持数据库事务，行级锁，外键；插入更新需锁表，效率低，查询速度快，Myisam使用的是非聚集索引

（2）：innodb 支持事务，底层为B+树实现，适合处理多重并发更新操作，普通select都是快照读，快照读不加锁。InnoDb使用的是聚集索引

 

Mysql有哪些日志？

• Binlog

记录了对数据库所有的更改。 查询日志 记录了来自客户端的所有语句 慢查询日志 这里记录了所有响应时间超过阈值的SQL语句，这个阈值我们可以自己设置，参数为long_query_time，其默认值为10s，且默认是关闭的状态，需要手动的打开。

•  Error Log 

 错误日志文件，错误日志文件记录了MySQL启动，运行，关闭等记录，同时包含一些警告信息，当发现MySQL有异常的时候，应该第一时间查看错误日志文件。 SHOW VARIABLES LIKE 'log_error' 

•  Slow Log 

慢查询日志可以监控执行超过指定时间的SQL，从而记录到日志中，默认情况下MySQL并不启动慢查询日志，用户需要手工将这个参数设置为ON

 

SHOW VARIABLES LIKE 'log_slow_queries'; //查询是否开启慢查询日志

 

ShOW VARIABLES LIKE 'long_query_time'; //查询慢日志的阈值，默认10s

 

SHOW VARIABLES LIKE 'log_queries_not_using_indexes'; //记录所有没有使用索引的SQL

 

SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes'; //设置没有记录索引的SQL的运行次数阈值，只有超过这个阈值才记录

 

SHOW VARIABLES LIKE 'log_output'; //看出日志输出格式

• 查询日志 

查询日志记录了所有对MySQL数据库的所有请求信息，无论这些请求是否得到了正确的执行。 

• Binary Log 

二进制日志记录了MySQL数据库执行的所有的更改操作，但是不包括SELECT和SHOW等操作。通过二进制日志，可以达到以下几种功能： 恢复：通过二进制日志恢复数据 复制：在主从同步的时候，通过二进制日志，将主数据库信息同步到从数据库中 审计：通过二进制日志，可以统计操作，查看是否存在SQL注入

 

InnoDB日志

• 1 Redo Log 重做日志，用于记录事务操作的变化，且记录的是修改之后的值。不管事务是否提交都会记录下来。例如在更新数据时，会先将更新的记录写到Redo Log中，再更新缓存中页中的数据。然后按照设置的更新策略，将内存中的数据刷回磁盘。
• 2 Undo Log 记录的是记录的事务开始之前的一个版本，可用于事务失败之后发生的回滚。 Redo Log记录的是具体某个数据页上的修改，只能在当前Server使用，而Binlog可以理解为可以给其他类型的存储引擎使用。这也是Binlog的一个重要作用，那就是主从复制，另外一个作用是数据恢复。

 

什么是页？

页，是InnoDB中数据管理的最小单位。当我们查询数据时，其是以页为单位，将磁盘中的数据加载到缓冲池中的。同理，更新数据也是以页为单位，将我们对数据的修改刷回磁盘。每页的默认大小为16k，每页中包含了若干行的数据。

用户自己的存储的数据会按照对应的行格式存在User Records中。实际上，新生成的页面是没有User Records的，只有当我们第一次插入数据时，才会从Free Space划一个记录大小的空间给User Records。当Free Space用完之后，就意味着当前的数据页也使用完了。

每一页的数据，可以通过FileHeader中的上一下和下一页的数据，页与页之间可以形成双向链表。因为在实际的物理存储上，数据并不是连续存储的。你可以把他理解成G1的Region在内存中的分布。

而一页中所包含的行数据，行与行之间则形成了单向链表。我们存入的行数据最终会到User Records中，当然最初User Records并不占据任何存储空间。随着我们存入的数据越来越多，User Records会越来越大，Free Space的空间会越来越小，直到被占用完，就会申请新的数据页。

User Records中的数据，是按照主键id来进行排序的，当我们按照主键来进行查找时，会沿着这个单向链表一直往后找

 

存储文件有哪些？

MyISAM 物理文件结构为：

.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

.MYD (MYData) 文件：MyISAM 存储引擎专用，用于存储MyISAM 表的数据

.MYI (MYIndex)文件：MyISAM 存储引擎专用，用于存储MyISAM 表的索引相关信息

InnoDB 物理文件结构为：

.frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等

.ibd 文件或 .ibdata 文件： 这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。

独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件

共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）

 

 

数据库事务说是如何实现的?

（1）：通过预写日志方式实现的，redo和undo机制是数据库实现事务的基础

（2）：redo日志用来在断电/数据库崩溃等状况发生时重演一次刷数据的过程，把redo日志里的数据刷到数据库里，保证了事务 的持久性（Durability）

（3）：undo日志是在事务执行失败的时候撤销对数据库的操作，保证了事务的原子性

 

Mysql是否解决了幻读，是如何解决的？

Mysql的默认隔离级别是Repeatable read（可重复读），这种隔离级别下会产生幻读问题，Mysql通过锁机制及多版本控制解决了幻读现象的发生，主要手段如下：

快照读事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的快照数据。普通的 select 就是快照读。当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。

当前读在 InnoDB 中，默认为 Repeatable 级别，InnoDB 中使用一种被称为 next-key locking 的策略来避免幻读（phantom）现象的产生。主要采用next-key锁，即行锁和gap锁来控制。如select * from user where id =1 for update;如果有id为1的记录则会被加排它（X）锁,如果不存在，则会加上next-key锁，此时插入记录是会排出异常的，以此解决幻读问题。

 

事物的隔离级别有哪些？

READ-UNCOMMITTED(读未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

 

快照读与当前读的区别？

1. 快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。

SELECT * FROM table ...;

2. 当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。

INSERT;

UPDATE;

DELETE;

 

多版本控制如何实现的？

undo log

undo log有两个作用：提供回滚和多个行版本控制(MVCC)。undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

insert undo log – 记录insert

update undo log – 记录update和delete,undo 是逻辑记录，记录了每一行修改的值（前后项）。

• 实现原理

而MVVC则引入了另外一种并发控制，它让读写操作互不阻塞，每一个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回，由此解决了事务的竞争条件。

多版本控制的核心是数据快照，而 InnoDB 则是通过 undo log 来存储数据快照。InnoDB 通过 undo log 保存了已更改行的旧版本的信息的快照。

InnoDB 的内部实现中为每一行数据增加了三个隐藏列用于实现 MVCC 。DB_ROW_ID: 行标识（隐藏单调自增id）DB_TRX_ID: 插入或更新行的最后一个事务的事务标识符。（删除视为更新，将其标记为已删除）DB_ROLL_PTR:写入回滚段的撤消日志记录（若行已更新，则撤消日志记录包含在更新行之前重建行内容所需的信息）根据事物标识符及撤销记录决定读取的快照版本。

 

事物的处理过程？

1 记录redo和undo log文件，确保日志在磁盘上的变化

2 更新数据库记录

3 提交事务 redo写入 commit记录

 

主从复制的原理

（1）：主库db的更新事件（update、insert、delete）被写到binlog

（2）：主库创建一个binlog dump thread线程，把binlog的内容发送到从库

（3）：从库创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log.

（4）：从库还会创建一个SQL线程，从relay log里面读取内容写入到slave的db.

 

 

Explain字段语义

 

[☛ 重点：]

type

Index all 需要优化

Extra

 

 

信息比较明确的字段

 

id

SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

 

Table

查询的是哪个表

 

partitions

匹配的分区

 

possible_keys

表示 MySQL 在查询时，可能使用到的索引。即使有些索引出现在 possible_key 中，但是并不表示此索引一定会被 MySQL 使用到。MySQL 在查询时具体使用到那些索引，与 key 和你写的 SQL 有关。

 

key_len

表示查询优化器使用了索引的字节数。这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到。

 

ref

哪个字段或常数与 key 一起被使用

 

Filtered

表示此查询条件所过滤的数据的百分比

 

比较重要的字段信息

 

select_type

 

SIMPLE： 表示此查询不包含 UNION 查询或子查询

 

PRIMARY： 表示此查询是最外层的查询

 

UNION： 表示此查询是 UNION 的第二或随后的查询

 

DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询

 

UNION RESULT： UNION 的结果

 

SUBQUERY： 子查询中的第一个 SELECT

 

DEPENDENT SUBQUERY： 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果 最常见的应该是 SIMPLE，当我们的查询 SQL 里面没有 UNION 查询或者子查询的时候，那么通常就是 SIMPLE 类型。

 

Type

type 字段比较重要，它提供了判断查询是否高效的重要依据。通过 type 字段，我们可以判断此次查询是全表扫描，还是索引扫描等。 通常来说, 不同的 type 类型的性能关系如下: ALL < index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描，因此在相同的查询条件下，它是速度最慢的。 而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引，因此比 ALL 类型的稍快。 后面的几种类型都是利用了索引来查询数据，因此可以过滤部分或大部分数据，因此查询效率就比较高了。

 

system 表中只有一条数据，这个类型是特殊的 const 类型。

 

const 针对主键或唯一索引的等值查询扫描，最多只返回一行数据，const 查询速度非常快，因为它仅仅读取一次即可。

eq_ref 此类型通常出现在多表的 join 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常是 =，查询效率较高.

 

range 表示使用索引范围查询，通过索引字段范围获取表中部分数据记录，这个类型通常出现在 =、 <>、 >、 >=、 <、 <=、 IS NULL、 <=>、 BETWEEN、 IN 操作中。 当 type 是 range 时，那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。

 

index 表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据。 index 类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index。

 

ALL 表示全表扫描，这个类型的查询是性能最差的查询之一。通常来说，我们的查询不应该出现 ALL 类型的查询，因为这样的查询在数据量大的情况下，对数据库的性能是巨大的灾难。 如一个查询是 ALL 类型查询，那么一般来说可以对相应的字段添加索引来避免。

 

 

key

此次查询中确切使用到的索引.

 

 

rows

rows 也是一个重要的字段。MySQL 查询优化器根据统计信息，估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏，原则上 rows 越少越好。

 

 

Extra

 

这个也比较重要

 

Using filesort 当 Extra 中有 Using filesort 时，表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 Using filesort，都建议优化去掉，因为这样的查询 CPU 资源消耗大。

 

Using index 覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能不错.

 

Using temporary 查询有使用临时表，一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。

 

Using where 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示 MySQL 服务器将在存储引擎检索行后再进行过滤。

 

 

为什么使用B+树呢？

因为b+树的高度固定，可以有效的控制io次数，并且在一个页中可以存储更多的索引值。

 

什么是mvcc?

MVCC 是无锁操作的一种实现方式,MVCC 主要又是依靠 Read View 来实现的.MySQL 会根据规则来判断版本链中的哪个版本（记录）是在事务中可见的.DB_TRX_ID：该列表示此记录的事务 IDDB_ROLL_PTR：该列表示一个指向回滚段的指针，实际就是指向该记录的一个版本链DB_ROW_ID：记录的 ID，如果有指定主键，那么该值就是主键。如果没有主键，那么就会使用定义的第一个唯一索引。如果没有唯一索引，那么就会默认生成一个值。READ COMMITTED 是在每次执行 select 操作时都会生成一次 Read View。REPEATABLE READ 只有在第一次执行 select 操作时才会生成 Read View，后续的 select 操作都将使用第一次生成的 Read View。

 

会不会走索引的问题？

abc建立索引相当于a，ab，abc建立索引，如果查询bc虽然显示走索引，但是type是index,相当于全索引扫描，如果查询正确顺序则type为ref，是正确匹配的索引。如果执行cba等查询，虽然顺序不同，会自动重排走索引。

 

建立索引原则？

尽量选择区分度高的字段，首先考虑where和orderby的字段上

 

不会走索引的情况？

1 Like的参数以通配符开头时,like ‘%test%’，不使用索引,like ‘test%’，使用索引

2 where条件不符合最左前缀原则时

3 使用！= 或 <> 操作符时

4 索引列参与计算,where语句中有数学运算或者函数。

5 对字段进行null值判断,如select * from t_credit_detail where Flistid is null ;

6 or操作符必须每个字段都建立索引

 

为什么建议使用自增主键？

由于主键使用了聚簇索引，如果主键是自增id，那么对应的数据也会相邻地存放在磁盘上，写入性能较高。如果是uuid等字符串形式，频繁的插入会使innodb频繁地移动磁盘块，写入性能就比较低了。[这就是用自增主键的好处]

 

mysql如何解决幻读问题？

Mysql的默认隔离级别是Repeatable read（可重复读），这种隔离级别下会产生幻读问题，Mysql通过锁机制及多版本控制解决了幻读现象的发生，主要手段如下：

快照读事务每次取数据的时候都会取创建版本小于当前事务版本的数据，以及过期版本大于当前版本的快照数据。普通的 select 就是快照读。当执行select操作是innodb默认会执行快照读，会记录下这次select后的结果，之后select 的时候就会返回这次快照的数据，即使其他事务提交了不会影响当前select的数据，这就实现了可重复读了。

当前读在 InnoDB 中，默认为 Repeatable 级别，InnoDB 中使用一种被称为 next-key locking 的策略来避免幻读（phantom）现象的产生。主要采用next-key锁，即行锁和gap锁来控制。如select * from user where id =1 for update;如果有id为1的记录则会被加排它（X）锁,如果不存在，则会加上next-key锁，此时插入记录是会排出异常的，以此解决幻读问题。

 

B+树的特性？

1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；

2.不可能在非叶子结点命中；

3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；

4.更适合文件索引系统；

 

为什么用B+数做索引的数据结构？

磁盘IO是一种比较消耗性能的操作。

B+树高度相对固定，有效控制io次数。

所有叶子节点形成了一个有序链表，更加便于范围查找。

B+树更适合外部存储，由于内节点无data域，一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确，也意味着B+树单次磁盘IO的信息量大于B树，IO效率更高。

 

char 和 varchar的区别？

char是固定长度，varchar长度可变.char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。存储时，前者不管实际存储数据的长度，直接按 char 规定的长度分配存储空间；而后者会根据实际存储的数据分配最终的存储空间.

 

int(11)最大长度是多少？

这个11代表显示宽度,与该整数需要的存储空间的大小都没有关系,最大值还是21亿

 

什么是快照读什么是当前读？

1. 快照读

MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。SELECT * FROM table ...;

 

2. 当前读

MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。INSERT;UPDATE;DELETE;

在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。SELECT * FROM table WHERE ? lock in share mode;SELECT * FROM table WHERE ? for update;

 

设置慢查询

如果不改配置文件，重启会失效

 

对主键索引或唯一索引会用gap锁么

如果where条件全部命中，不会用gap锁只会加纪录锁

如果where条件部分命中或者全不命中，会加gap锁