3万字聊聊什么是MySQL

  • A+
所属分类：面试

整篇的写作大概就是这样思路。一篇带你熟悉MySQL！篇幅很大，建议先关注，收藏！

整个篇幅的知识点全部缩减。为面试系列打造！如果想具体深入研究，请关注公众号浏览其他文章即可！

图片

1. SQL是如何执行的

1.1 查询

平时我们都是关注SQL是如何执行的，但是有没有了解整个MySQL的结构呢？这里我们介绍一下

整个MySQL主要分两层。

  • Service
  • 存储引擎层

这两层主要由四部分构成

  • 连接器
  • 分析器
  • 优化器
  • 执行器

图片

连接器： 校验用户身份信息，校验当前用户的SQL语句权限，管理SQL连接的通道

分析器： 词法分析，语法分析。用于处理客户端的SQL语句，分析处理完之后写入缓存，如果下次命中的话直接返回提高查询效率。

优化器： 生成执行计划，索引选择（这里可以完美解释我上面抛出的SQL执行问题）

执行器： 调用操作存储引擎，捞取数据。

大概介绍了MySQL每一种结构发挥的作用。这里扩展一下大家一直说的长连接和短连接的优化！

**长连接：**建立连接之后，如果客户端的有请求操作则一种使用同一个连接进行交互处理

**短连接：**建立连接之后，并且客户端执行完自己的需求之后，就关闭了连接。

长短连接总结： 数据库建立连接这个过程是比较复杂的，所以建立尽量减少使用短连接的方式，也就是尽量使用长连接。但是长连接是比较容易涨内存的。也会被系统误认为内存占用过大强行杀死。

优化方案： 可以采用定期断开长连接的方法优化。还可以通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证。

1.2 修改

首先要介绍两个日志。redo log 和 binlog。

redolog充当于我们平时生活中的记事本，备忘录。

binlog充当于阎王殿的生死簿。

数据库中也是一样。当进行数据修改操作时，不会立即修改到磁盘。如果是立即修改的话，对磁盘IO影响是比较大的。所以平时的修改操作都会先写到redo log 中，等系统认为不忙的时候再更新到binlog中。

区别

  1. redolog是innodb引擎层特有的，binlog 是Server层自带的。
  2. redolog是物理日志，binlog是逻辑日志
  3. redolog记录在某个数据页上做了什么修改，binlog记录这个语句的原始逻辑。
  4. redolog循环写，binlog追加写

修改流程

update vip set name=‘欢少的成长之路’ where ID=2
  1. 先查找ID=2 这一行数据。查找方式有两种，一种是直接从表中取，另一种就是如果缓存中存在就直接走缓存
  2. 取到之后，直接修改name=欢少的成长之路 写入新行
  3. 新行更新到内存中
  4. 写入redolog，并且当前处于prepare阶段
  5. 写入binlog
  6. commit提交事务

这里面可以跟面试官介绍一下两阶段提交，第四步和第五步为什么要分开写入呢？这个就是两阶段提交的精髓

目的是为了让数据保持一致 如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致，下面我们举例论证一下。

  • 先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启，仍然能够把数据恢复回来，所以恢复后这一行 name 的值是 欢少的成长之路。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 name 的值就是 欢少个人业务，与原库的值不同。
  • 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 name 的值是 欢少个人业务。但是 binlog 里面已经记录了把 name 从 欢少个人业务 改成 欢少的成长之路 这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 欢少的成长之路，与原库的值不同。

1.3 总结

上述就是一个SQL如何执行的介绍了，从查询与修改分别介绍了流程以及涉及到的知识点。最重要的就是两阶段提交了。如果面试官问你的话来个反证法差不多就过关啦

2. 索引结构

2.1 是什么

索引结构是MySQL最底层的数据处理结构了。主要分五块

  • 哈希
  • 链表
  • 二叉树
  • B树
  • B+树

哈希

这个就是通过hash 算法，把每个数据都hash出一串key之后，然后存在数据页的某一个位置。如果出现了相同的key就是发生了哈希碰撞。这个后续在算法章节会详细介绍一下。

这里MySQL解决方案是采用链表+哈希的方式共同存储在一个槽中。

链表

链表是一种物理存储单元上非连续、非顺序的存储结构，数据元素的逻辑顺序是通过链表中的指针链接次序实现的。

二叉树

二叉树是一种树形结构，每个节点只有两颗子节点。它是一种最简单且最重要的树。二叉树的递归定义为：二叉树是一棵空树，或者是一棵由一个根节点和两棵互不相交的，分别称作根的左子树和右子树组成的非空树；左子树和右子树又同样都是二叉树

红黑树

红黑树是一种 特定类型的二叉树，它是在计算机科学中用来组织数据比如数字的块的一种结构。若一棵二叉查找树是红黑树，则它的任一子树必为红黑树。

红黑树是一种平衡二叉查找树的变体，它的左右子树高差有可能大于 1，所以红黑树不是严格意义上的平衡二叉树（AVL），但对之进行平衡的代价较低， 其平均统计性能要强于 AVL 。

由于每一棵红黑树都是一棵二叉排序树，因此，在对红黑树进行查找时，可以采用运用于普通二叉排序树上的查找算法，在查找过程中不需要颜色信息。

B+树

B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找

2.2 优缺点

哈希

  • **优点：**哈希索引在解决单值查询的时候是非常快的，时间复杂度是O(1)。

  • **缺点：**但是不支持范围查询，所以当前MySQL中也应用到了哈希索引，但是并不是默认索引。

链表

  • **优点：**上面我们介绍到了，它是非连续的，非顺序的。所以在进行数据修改操作时，无需找到当前节点的前后进行移动操作。直接把数据修改了之后，把指针指向最新节点就好了。
  • 缺点： 如果当一个链表过于庞大的话，我们查询数据时，要一个一个遍历。链表没有数组那种的高效查询，也没有树形结构的对半查询。

二叉树

  • 优点： 查询数据时，采用对半查找。查询效率非常高。解决了链表留下来的难题。
  • 缺点： 当一个数出现持续递增时，会有倾斜的状态，比如 0 1 2 3 4 5 6 。这样就是一个倾斜树，查询效率与链表相当。不符合MySQL的大数据存储

红黑树

  • 优点： 一定程序上解决了二叉树偏移的问题，但是问题解决的不够根本
  • 缺点： 出现了层级较多这个问题。层级较多会影响查询性能。

B+树

  • 优点： 在B树的基础上作了优化，也是红黑树之后的一个进化版。主要优化点就是数据节点的自旋。在插入时，当节点树大于某一个限制后会自动自旋，变成另一个节点树。而且具有排序的功能。节点与节点之间有连接关系，这是对查询非常有利的。

2.3 总结

第二部分，大概介绍了MySQL的五个索引结构。从概念，到优缺点的介绍。通过优缺点为切入点进行分析了MySQL为什么会以B+树为默认索引结构的原因。

3. 内部索引

3.1 聚簇索引

概念

聚簇索引也是主键索引。一个表只能有一个聚簇索引。当表有聚簇索引时，数据行是保存在索引的叶子页的。

优点

数据访问更快，数据都保存在一棵树上，可以避免为了查询其他列进行回表操作。

缺点

聚簇索引极大的提高了IO密集型应用的性能，但是如果都放入内存中，访问的顺序就没那么必要了。聚簇索引也就失去了优势。

更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。

基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次分裂操作。页分裂会导致表占用更多的磁盘空间。

聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

3.2 非聚簇索引

概念

非聚簇索引也是二级索引。

叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置再去磁盘查找数据，这就有点类似一本书的目录，比如要找到第三章第一节，那就现在目录里面查找，找到对应的页码后再去对应的页码看文章。

优缺点

非聚簇索引在做查找时，往往需要二次查询。第一次查找到主键值，再通过主键值找到数据行对应的数据页，再通过数据页中的Page Directory找到数据行。

一个表中可以存在多个非聚簇索引。

如果主键比较大的话，那非聚簇索引将会变得更大，因为非聚簇索引的叶子节点存储的是主键值，过长的主键值，会导致非叶子节点占用更多的物理空间

3.3 普通索引

最基本的索引，没有任何限制，是我们经常使用到的索引。他的任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引

3.4 唯一索引

与普通索引类似，不同的是，唯一索引的列值必须唯一，但允许为空值。主键索引是特殊的唯一索引，不允许有空值。

3.5 联合索引

将几个列作为一条索引进行检索，使用最左匹配原则。举一个用户登录的例子。可以把登录账号和登录密码设为联合索引。这样可以提供性能的同时，节省索引的维护成本。

3.6 索引下推

索引下推是在MySQL5.6引入的优化。可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

3.7 总结

第三部分大概介绍了MySQL内部索引的概念，优缺点，应用等。面试中常问的就是聚簇索引与非聚簇索引的区别。所以这里也是把这两块写的最详细的地方。

4. 日志

4.1 错误日志

MySQL错误日志是记录MySQL 运行过程中较为严重的警告和错误信息，以及MySQL每次启动和关闭的详细信息。错误日志的命名通常为hostname.err

通过如下SQL，可以找到错误日志的位置。

show variables like '%log_error%';

错误日志如果不清理或删除，那么它会一直增长。在MySQL 5.5.7之前，可以通过mysqladmin –uroot –p flush-logs命令删除错误日志。MySQL 5.5.7以及之后，只能通过下面方式来归档、备份错误日志

shell> mv host_name.err host_name.err-old
 
shell> mysqladmin -u root -p flush-logs
 
shell> mv host_name.err-old backup-directory

错误日志可以任意命名。只需要在/etc/my.cnf配置文件中，添加了参数log_error=/u02/mysql/mysql.err，重新启动MySQL即可。

4.2 查询日志

MySQL的查询日志记录了所有MySQL数据库请求的信息。无论这些请求是否得到了正确的执行。默认文件名为hostname.log。默认情况下MySQL查询日志是关闭的。生产环境，如果开启MySQL查询日志，对性能还是有蛮大的影响的

不常用就不做过多介绍了

4.3 慢日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

慢查询日志涉及的重要参数：

  • slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
  • long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询日志涉及的重要工具：

  • mysqldumpslow

常用指令

mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

-s 是表示按照何种方式排序，

c: 访问计数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t 是top n的意思，即为返回前面多少条的数据

后面是目录

more： 另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

4.4 redolog 重做日志

提到redolog，肯定是要聊到redo log buffer 和redo log file。前者是日志的缓存，是易失性的。后者是日志文件，是持久性的。

写入机制

redo log buffer 要做的是一个事务在插入一条数据的时候，需要先写入日志。但是又不能在还没有提交事务的时候直接写到redo log文件中。这个日志的临时存放处就是redo log buffer。真正在写入redo log文件的过程是在commit这一步完成的。（执行一个SQL语句也是一个事务）

如果还没等到commit这一步，主要会有两种可能

  1. MySQL宕机了，这份缓冲区日志丢失了也就丢失了，也不会有什么损失。
  2. 持久化到磁盘了！

接着持久化磁盘

图片

  • redo log buffer：物理上这是MySQL的进程内存
  • FS page cache：写入到磁盘，但是还没有进行持久化。物理上是page cache文件系统。
  • hard disk，这个就是持久化到磁盘了
  • 图中的红色区域是内存操作，不涉及到磁盘IO。所以性能的非常快的。write也是非常快的
  • 图中的黄色部分。fsync的速度就慢了很多。因为持久化到磁盘

写入策略

redo log buffer的写入策略，是由innodb_flush_log_at_trx_commit

  • 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  • 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
  • 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

刷新策略

redo log buffer的刷新策略，是由innodb_log_buffer_size 控制的。

  • redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。

（注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。）

  • 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘

（假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是 1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。）

组提交机制

日志逻辑序列号，简称LSN。LSN是单调递增的。用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。

图片

如上图所述，

  • trx1是最先到达的，会被选为这组的leader。
  • 等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
  • trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
  • 这时候 trx2 和 trx3 就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

4.5 binlog 归档日志

写入机制

binlog写入日志这个是比较简单的。提到binlog，必然提到binlog cache。那么binlog cache是什么？

binlog cache是一个二进制日志文件的缓冲区，他是由一个参数 binlog_cache_size 控制大小的缓冲区。

一个事务在执行是时候是不允许被拆开的，因此无论事务多大，都是要一次性保存执行的。那么这个就涉及到了binlog cache 的保存问题。如果所占的内存大小超过了这个binlog_cache_size 参数的设定。就会采用暂存到磁盘。事务在提交的时候，会先把binlog cache里的数据写入到binlog中，并清空binlog cache数据。

图片

由上图我们可以得知每个binlog cache是由单独的一个线程享有的。也就是说多个线程带着多个binlog cache写入binlog file是非常快的，因为并没有涉及到磁盘IO的开销。

当进行到了fsync的时候，才是将数据持久化到磁盘操作。这个时候才会占用磁盘IO，也就是我们常说的IOPS。

何时write？何时fsync？

主要由sync_binlog控制的。

  • 当它等于0时，每次提交事务都只 write，不 fsync
  • 当它等于1时，每次提交事务都会执行 fsync
  • 当它大于1时， 每次提交事务都 write，但累积 N 个事务后才 fsync

因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。

但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

组提交

binlog也是可以组提交的。主要分成两部分

  • 先把 binlog 从 binlog cache 中写到磁盘上的 binlog 文件；
  • 调用 fsync 持久化。

图片

如上图所述，可以看第二步。

如果多个事务都已经write了（也就是说写入到redo log buffer了），再到第四步的时候就可以一起持久化到磁盘了。不是提升IOPS的这个优化过程嘛！

不过通常情况下第 3 步执行得会很快，所以 binlog 的 write 和 fsync 间的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果你想提升 binlog 组提交的效果，可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。这两个只要有一个满足条件就会调用 fsync。

  • binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 fsync;
  • binlog_group_commit_sync_no_delay_count 参数，表示累积多少次以后才调用 fsync。

WAL机制主要得益于

  • redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；
  • 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

4.6 undolog 回滚日志

undo log主要有两个作用：回滚和多版本控制(MVCC)

在数据修改的时候，不仅记录了redo log，还记录undo log，如果因为某些原因导致事务失败或回滚了，可以用undo log进行回滚

undo log主要存储的也是逻辑日志，比如我们要insert一条数据了，那undo log会记录的一条对应的delete日志。我们要update一条记录时，它会记录一条对应相反的update记录。

这也应该容易理解，毕竟回滚嘛，跟需要修改的操作相反就好，这样就能达到回滚的目的。因为支持回滚操作，所以我们就能保证：“一个事务包含多个操作，这些操作要么全部执行，要么全都不执行”。【原子性】

因为undo log存储着修改之前的数据，相当于一个前版本，MVCC实现的是读写不阻塞，读的时候只要返回前一个版本的数据就行了。

5. 跳表，回表

5.1 为什么

跳表

跳表 也是为了 快速查找 而提出的一种数据结构

我们在链表中查询数据的时候，时间复杂度是O(n)，为了解决效率问题，跳表就产生了。它本质上是一种多级链表，通过增加数据的冗余来换取查找的时间复杂度，属于空间换时间的思想。不过呢，其实空间也不会消耗太多，因为冗余的只是节点指针。

优点分析

  • 相比红黑树来说，跳表实现简单，你面试的时候是可以手写出来的，而且插入和删除的操作也不难。红黑树里面大量的自旋操作常常让人迷惑。
  • 数据是自排序的，这点和MYSQL里面的B+树很像，默认是从小到大排序的。利用这一点就是快速进行范围查找，而不用真正地排序。

链表，跳表比较

图片图片

查询流程

如果要在这里面找 21

  • 链表：过程为 3→ 6 → 7 → 9 → 12 → 17 → 19 → 21 。
  • 跳表：6→9→17→21

跳表的主要思想就是这样逐渐建立索引，加速查找与插入。从最上层开始，如果key小于或等于当层后继节点的key,则平移一位；如果key更大，则层数减1，继续比较。最终一定会到第一层

插入流程

图片图片

先确定该元素要占据的层数 K（采用丢硬币的方式，这完全是随机的）。

然后在 Level 1 ... Level K 各个层的链表都插入元素。

用Update数组记录插入位置，同样从顶层开始，逐层找到每层需要插入的位置，再生成层数并插入。

例子：插入 119， K = 2

删除流程

与插入类似

回表

回表这里我们举一个常见的例子。从刚接触代码起，我们就已经开始写登录注册了。那么我们登录的时候账号，密码是如何设置的呢？

数据量小还好，一旦数据量起来的肯定是要添加索引的。问题来了，索引如何建立！

如果只给账号设置索引的话就碰到了回表操作。

MySQL底层是B+树。如果给账号设置索引的话，账号这个字段就成了一个节点树。而我们查询的时候会查询账号+密码。密码不在这颗树上，所以就需要回表去查询密码这个字段然后拼凑在一起。

5.2 如何避免

回表意味着增加磁盘IO的开销，所以避免回表也是优化MySQL的一种方式。还是举登录这个例子，账号密码属于高频查询。给账号+密码创建一个联合索引就可以避免回表了。

剩下的就根据各自的业务场景需求啦。比如软件设计师官网的登录。利用的是身份证+密码。每一种都不一样。

6. 主键自增ID

主键自增也是高频面试话题，今天我们就来介绍一下，使用MySQL自增ID可以节省我们多少成本，ID为什么会不连续，上限的一系列问题吧。

6.1 优缺点

  • 使用主键自增ID比UUID节省一半磁盘空间
  • 范围like查询，自增ID性能优于UUID。(不过有缓存的情况下，双方性能相差不大)
  • 写入测试，自增ID是UUID的4倍

6.2 实现原理

存放位置

自增值是保存在表结构定义里的，实际上表结构定义是存放在后缀名为.frm 文件中，但不会保存自增值。

自增值的保存策略

  • MyISAM 引擎的自增值保存在数据文件中。
  • innodb5.7及之前的版本，自增值都是保存在内存中，没有持久化每次重启后，第一次打开表的时候都会去找自增值的最大值然后将最大值+1作为当前的自增值ID。
  • innodb8.0版本，将自增值保存在了redolog中，重启的时候依靠redolog恢复重启之前的值

自增值修改机制

如果一个字段为自增字段，在插入一行数据的时

  1. 如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
  2. 如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。
  • 如果要插入的值小于自增值，那么这个表的自增值不变
  • 如果要插入的值大于或等于自增值，就需要把当前自增值修改为新的自增值

自增值生成算法是：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。默认值都是 1

自增值的修改时机

CREATE TABLE `t` (
  `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;

假设表中存在一条（1，1，1）数据。如果在插入一条（null，1，1）。执行流程如下

  1. 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
  2. InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2，然后把2代入 （2,1,1）
  3. 然后再把自增值改成3
  4. 执行插入操作，因为c是唯一索引。所以插入（2,1,1）会报错。

这个时候问题就出来了，2的值没插进去，自增值也没有被改回去，就形成了不连续的情况

还有一种情况就是事务问题。介绍完2个机制1个策略了。直接快刀斩乱麻不啰嗦了。

为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

  • 事务A在执行一个插入语句的时候会申请一个ID值，此时如果申请一个3，那么这时自增后的值为4
  • 事务B也在执行一个插入语句也申请了一个ID值，此时拿到了ID为4，同时主键自增为5

上面是两个事务在执行插入语句，如果其中一个事务执行失败或者进行了回滚。那么就缺失了一条产生了一条空隙。原本ID为4的数据没有了。而且自增值也是5，也没有改回4。

6.3 ID不连续的问题

  1. 插入不成功自增值没有被改回去，导致不连续
  2. 事务回滚问题

6.4 为什么MySQL没有把ID改回去

MySQL之所以没有把ID改回去是因为考虑性能问题！

我们可以举两个反例。如果要退回去的话，肯定要判断退回去的这个ID是否存在。那么如何判断，肯定是要查表的。

每次在申请ID之前，先判断表ID是否存在的话，性能是大打折扣。本来申请ID是一个很快的操作，现在还要去主键索引上判断ID是否存在。

性能： 敢削我性能我锤死你。

还要一种情况就是，完成一个事务提交确认无误之后，再释放锁。这样的话虽然可以保证安全性，但是锁的粒度太大，系统并发能力大大下降。

所以业务方面进行一个平衡，还是选择了性能，没有把ID改回去。

6.6 自增锁

这里可以跟面试官简单的介绍一些历史

5.0版本

系统采用的就是我们上面介绍的比较安全的，并发度偏低的方法。一个语句申请了自增锁，会等语句执行结束之后才释放。

5.1.22版本

对比5.0版本，这里引进了一个策略innodb_autoinc_lock_mode

  • 如果设置为0，采用的自增锁策略就是等语句执行之后释放，属于5.0版本的策略
  • 如果设置为1，insert语句申请之后会立马释放。如果是inser-select 还是要等语句执行完成之后再释放的。
  • 如果设置为2，所有申请的自增锁，申请后立即释放，增加并发度

分析一下insert-select的问题

大家可以回想一下写入日志的事务。如果binlog格式为statement，那么他们如何记录？

如果insert-select采用申请后立即释放的方案。多个数据不过申请主键ID，会出现两个事务交叉的形式。也就是说一个事务的ID数据不是连续的。

一旦两个事务同时执行，要么先写事务A，要么先写事务B。无论哪一种binlog拿去从库执行的时候，insert-select恢复出来的时候ID是连续的，这个库的数据就出现了不一致的情况。

至今MySQL引用的是5.1.22的版本

6.7 ID上限后如何处理

这个知识点也是大厂比较常问的一个话题。因为大厂的数据量是比较大的，的确会遇到这样的场景。

主键ID自增上限后，就会出现覆盖掉原数据的情况。上线是4294967295，近43亿。

从这个角度看，我们还是应该在 InnoDB 表中主动创建自增主键。因为，表自增 id 到达上限后，再插入数据时报主键冲突错误，是更能被接受的。

毕竟覆盖数据，就意味着数据丢失，影响的是数据可靠性；报主键冲突，是插入失败，影响的是可用性。而一般情况下，可靠性优先于可用性。

一个表中没有主键的话，MySQL会默认建立一个隐藏字段，这个字段就是row_id。默认情况下建立的主键ID都是8个字节的，这个row_id是6个字节的。

7. 存储引擎

7.1 innodb与myisam索引区别

  • MyISAM 是非事务的存储引擎，适合用于频繁查询的应用。表锁，不会出现死锁，适合小数据，小并发。
  • innodb是支持事务的存储引擎，合于插入和更新操作比较多的应用，设计合理的话是行锁（最大区别就在锁的级别上），适合大数据，大并发。

7.2 为什么采用innodb为默认索引

  • innodb支持事务
  • innodb比myisam支持更大的锁粒度，支持并发

8. 索引失效

  • like查询以 % 开头

  • <>

  • OR语句前后没有同时使用索引

  • 数据类型出现隐式转换

  • 使用is null函数时，不能利用索引，只能全表扫描。（其他函数也要注意）

  • SQL中有or，也会失效

注意varchar就必须加单引号，如果不加就会误认为int类型，虽然查询效果是一致的。但是索引失效了，增加了查询性能，也多消耗了磁盘IO的开销。

9. 事务

9.1 是什么

什么是事务呢？事务就是银行的需求一样，如果在执行过程中断电或者不符合条件的情况被停止执行，则已经执行的sql语句全部回滚。也就是说 事务操作过程要不全部成功，要不全部失败！事务ACID的特性可以确保银行不会弄丢你的钱

9.2 ACID

  • 原子性：要不全部成功，要不全部失败，不可能只执行其中一部分操作，这就是事务的原子性
  • 一致性：一致性主要体现在数据一致性，事务最终没有提交，事务所修改的数据不会保存在数据库中
  • 隔离性：当前事务执行的修改在最终提交之前，对其他事务是不可见的。
  • 持久性：一旦事务提交，将修改的数据持久化到数据库中就算数据库断电崩溃也不会丢失。

9.3 MVCC实现原理

MVCC是多版本并发控制。通过保存数据在某一个时间点的快照来实现的。也就是说不管需要执行多长时间。每次事务执行的数据都是一致的。相反！　根据事务开始时间的不同选择的快照也是不同的，所以每个事务对同一张表，同一个时刻看到的数据有可能是不一样的。（如果没有这一方面的概念听起来可能有点迷惑）

多版本并发控制实现的不同，典型的实现有乐观锁并发控制与悲观锁并发控制。

MVCC通过每行记录后面保存两个隐藏的列来实现的，一个是保存行的创建时间，一个是保存行的过期时间。存储的不是时间值，而是系统的版本号。每开始一个新的事务，系统版本号会自动增加。事务开始时刻的系统版本号也就是事务的版本号，用来查询到每行记录的版本号进行对比。

优点：保存这两个额外的系统版本号的好处就是 操作数据的时候不需要单独上锁，这样设计使得数据操作很简单，性能也很好。并且也能保证只会读取到符合标准的行。 缺点：每行记录都需要额外的存储空间，需要做更多的检查行的操作，以及额外的维护工作

MVCC只在repertable read（可重复读）和read committed（提交读）两种隔离级别下工作。其他两种隔离级别都和mvcc不兼容！

Tip：read uncommitted总是读取最新的数据行，而不符合当前事务版本的数据行。serializable则会对所有读取的行都加锁

9.4 事务隔离级别

通过set transaction isolationlevel //设置隔离级别，设置隔离级别会在下一个事务开始的时候生效

  • read uncommitted（未提交读）：事务中的修改即使没有提交对其他事务都是可见的，也可以称为脏读，这个级别会导致很多问题，从性能上来说不会比其他隔离级别好太多，但缺乏其他隔离级别的很多好处。除非真的有特定的需求，一般很少用
  • reda committed（提交读）：大多数数据库默认的都是read committed，但是MySQL默认的不是这个！一个事务从执行到提交前，其他事务都是不可见的，有时候也可以叫不可重复读，因为两次执行同样的查询可能会得到不一样的查询结果
  • repeatable read（可重复读）：repeatable read解决了read committed脏读的问题，这个隔离级别也是MySQL默认的隔离级别。该级别保证了同一个事务多次执行可以读取同样的数据，但是有个缺陷就是存在幻读！幻读就是当事务在某个范围内读取数据时，这时另一个事务在这个范围插入了数据，当读取的事务再次读取该范围时会产生幻行。通过多版本并发控制（MVCC）解决了幻读的问题。
  • serializable（可串行化）：这是最高的隔离级别，它通过强制事务在从串行上执行，避免了前面说的幻读问题，简单来说就在在读取数据时加一个锁，这就暴露了另一个问题，大量的加锁会导致出现争锁超时的问题。只有特定的需求情况下或者可以接收没有并发的情况下才考虑这种隔离级别。

9.5 事务日志

事务日志这里常问的大概就是 redo log，undo log。具体的 我就不过多介绍了，写在 第四模块了。这里再提一遍，只是提升一下面试官问到事务的时候，绝对会问到事务日志的。建议多看看！

10. 幻读

10.1 是什么,为什么会有

这种从事务开启到事务结束，如果同一个数据看到不同的结果。我们就称为 幻读。

下面我们举一个例子

事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据 称为幻读

10.2 还能想到哪些

脏读

脏读是在未提交读隔离级别下容易遇到的问题。事务中的修改即使没有提交对其他事务都是可见的，也可以称为脏读

10.3 解决方案

如果是新插入的数据可以采用间隙锁的方式解决幻读的问题。

如果是修改一个数据的话可以采用加锁的方式解决幻读的问题。

隔离级别为，串行化的情况下，幻读是不存在的。因为串行化锁的是整个表。

具体的实现，原理，方式会在11模块介绍MySQL的锁

11. 锁（未做）

11.1 全局锁

命令

11.4 间隙锁

11.5 读写锁

11.6 共享锁

11.7 排他锁

11.8 意向锁

11.9 元数据锁

11.2 表级锁

11.3 行级锁

12. 最左匹配原则

众所周知，MySQL是满足最左匹配原则的。也是面试高频的一个点，一般会让你介绍一下什么是最左匹配原则以及最左匹配原则的应用技巧。下面我们来介绍一下。

假设组合索引为A，B，C。我们分情况一一介绍

  • A，A一起使用完全满足最左匹配原则
  • A，B一起使用完全满足最左匹配原则
  • B，A一起使用完全满足最左匹配原则
  • A，B，C一起使用完全满足最左匹配原则
  • A，C一起使用 部分 满足最左匹配原则
  • B，C一起使用 不 满足最左匹配原则

根据上述情况我们总结一下，最左原则。即：SQL语句中的对应条件的先后顺序无关。只要出现最左侧的索引树就为最左匹配原则。在explain执行计划中，可以通过key这一列查看是否命中，是否符合最左匹配原则。

个人建议：这里说一下题外话，建议每写一个SQL我们要保存有走执行计划的习惯，如果没命中索引，就把SQL优化一下，时间一长，慢慢的就对SQL优化有了简单的认识，再配上一些理论，你的个人实力绝对会上一层楼的！

13. 如何保证MySQL主从同步

MySQL的主从同步问题，这里我们可以跟面试官介绍一下，binlog的三种格式问题，就是因为这三种格式的存在才保证了MySQL的主从同步问题。

命令参数

binlog_format=‘row’

statement

首先就是第一种statement。记录的是大概的信息，几乎是我们的执行信息，我们看不到具体的逻辑是什么。所以如果同步到从库上，很容易会发现数据不一致的情况。

这里格式的优点就是，记录日志比较简洁，占用空间较小，但是风险较大，一旦数据丢失无法找到相应的数据。

row

第二种就是row格式的binlog日志。这种格式的优点就是，日志丰富，只要有row格式的binlog日志，想干什么操作都可以，丢的数据也可以随时（一般是15天）找回来。唯一的缺点就是日志过于丰富，内存占用过大，如果是在线上的话，磁盘写完之后，风险也是比较大的。需要做一些特殊处理。

比如日志定期备份转移，设置一个失效时间。保存15天内的数据，15天外的一概不管！

mixed

这里格式的出现，是上述的结合体，为什么这么说呢？mixed格式，使用了statement格式的优点，同时也使用了row格式的优点。

我想很多读者会感到比较疑惑，世上岂会有十全十美的事呢？下面我们详细介绍一下。

mixed会多做一个判断，他会判断，这个binlog会不会引起数据不一致这个问题。如果会引起，那么就采用row格式的。如果不会引起，那么就采用statement格式的日志。

主从同步

主库，从库在做数据一致性同步的时候主要依靠的就是binlog日志，如果在我们做操作时，日志保存的比较详细，那么就足矣可以保证主从一致性问题。

这里我们扩展两个问题

①：主从切换时的数据安全性问题。有一个A库和B库，客户端一开始访问的是A库，这个时候做了主从切换，主库从A切换到了B。（数据同步线程具有超级管理员权限）客户端访问B库的这一过程中，如果把从库设置成readonly模式

  • 可以防止其他运营的类的查询语句的误操作。造成数据不一致的问题。
  • 可以防止A和B在切换的时候也会有一些逻辑性的BUG问题

②：主从同步的循环复制问题。节点 A 上更新了一条语句，然后再把生成的 binlog 发给节点 B，节点 B 执行完这条更新语句后也会生成 binlog。（我建议你把参数 log_slave_updates 设置为 on，表示备库执行 relay log 后生成 binlog）。那么，如果节点 A 同时是节点 B 的备库，相当于又把节点 B 新生成的 binlog 拿过来执行了一次，然后节点 A 和 B 间，会不断地循环执行这个更新语句，也就是循环复制了。解决方案如下：

  • 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
  • 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
  • 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。

这里不做详细介绍了，为面试打造！详细的技术点看其他文章。

14. MySQL高可用主要体现在哪些

讲到高可用的话，肯定少不了两个策略。可靠性优先策略，可用性优先策略。

面试官问的话肯定要先从源头说起。比如为什么会有高可用，你可以聊一下从硬件的问题，大事务的问题，大表DDL的问题，从库复制能力的问题。这些一系列的问题导致延时，为了高可用的考虑才引进了两个策略。下面介绍了这两个策略是什么。

可靠性优先策略

  • 判断从库B的seconds_behind_master 是否小于某个值，如果大于某个值的话延迟太大会影响业务数据的，所以一定要小于某个值的时候才可以继续下一步
  • 把主库A改成只读状态，readonly改为true
  • 再判断seconds_behind_master的值，直到这个值变成0为止。（因为只有延时足够低，数据才足够安全）
  • 把从库B改成读写状态，也就是把readonly改为flase
  • 最后把业务的请求都打到B上

这里的第二步把主库A改成了只读模式，这是不可用的时间，这段时间都是不可写的，有数据的话只能等待。

可用性优先策略

与可靠性优先策略对比，唯一的区别就是。这里不等同步完成之后再切换过去和状态修改。而是直接把一系列步骤一次性到位。这样是比较危险的，主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，我都建议你使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

具体的策略根据业务来定！ 听的不是很明白的 群里讨论

15. 分布式事务主键ID

前段时间公司上了一套热门方案，分库分表，读写分离，一主多从这类技术栈。考虑到了全局性的唯一ID的问题。我们采用的是雪花算法进行生成唯一ID。

图片img

  • 第一个部分，是 1 个 bit：0，这个是无意义的。
  • 第二个部分是 41 个 bit：表示的是时间戳。
  • 第三个部分是 5 个 bit：表示的是机房 id，10001。
  • 第四个部分是 5 个 bit：表示的是机器 id，1 1001。
  • 第五个部分是 12 个 bit：表示的序号，就是某个机房某台机器上这一毫秒内同时生成的 id 的序号，0000 00000000。

这个算法可以保证，一个机房的一台机器上，在同一毫秒内，生成了一个唯一的 id。可能一个毫秒内会生成多个 id，但是有最后 12 个 bit 的序号来区分开来。

15.1 优点

（1）高性能高可用：生成时不依赖于数据库，完全在内存中生成。

（2）容量大：每秒中能生成数百万的自增ID。

（3）ID自增：存入数据库中，索引效率高。

15.2 缺点

依赖与系统时间的一致性，如果系统时间被回调，或者改变，可能会造成id冲突或者重复。

15.3 体量考虑

真实开发过程中，除了一线互联网大厂会有那么多的机器，估计我们不会接触那么多的机器，我们可以改进算法，生成18个bit的ID就够我们使用的了。

这里生成多少位的，取决于公司的体量吧

17. 分库分表

当数据的体量达到一定级别之后，代码优化，已经达不到真实的性能要求了。下一步就可以考虑分库分表了。

我见过很多人不管什么问题，上来就分库分表是不对的。微信公众号也有很多篇文章的标题也是比较搞笑的《老大让我优化数据库，我上来就分库分表，他过来就是一jio》

下面可以介绍一下分库分表下的两种拆分以及何时拆分

17.1 水平拆分

水平拆分，主要拆的一个数据量级的问题。如果一个表中的数据超过500万行，那么就可以考虑进行拆分了。水平拆分的方式类似于医院男女科一样。

来了100个人报名。50个男，50个女。50个男肯定选择男科报名，50个女选择女科报名。有可能例子不恰当大概的意思差不多。

如果表中有原数据，可以采用把ID取模处理。偶数去A表，基数去B表。这个例子应该比较经典吧。

水平拆分的优点：

  • 表关联基本能够在数据库端全部完成。不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；
  • 应用程序端整体架构改动相对较少； 事务处理相对简单；
  • 只要切分规则能够定义好，基本上较难遇到扩展性限制；

水平切分的缺点：

  • 切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则
  • 后期数据的维护难度有所增加，人为手工定位数据更困难；
  • 应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

17.2 垂直拆分

就是根据不同的业务进行拆分的，拆分成不同的数据库，比如会员数据库、订单数据库、支付数据库、消息数据库等，垂直拆分在大型电商项目中使用比较常见。

优点：拆分后业务清晰，拆分规则明确，系统之间整合或扩展更加容易。

缺点：部分业务表无法join，跨数据库查询比较繁琐(必须通过接口形式通讯(http+json))、会产生分布式事务的问题,提高了系统的复杂度。举栗子：不可能出现，在订单服务中，订单服务直接连接会员服务的数据库这种情况。

17.3 拆分解决方案

我这里用的是mycat中间件进行拆分。mycat支持10种分片策略

  • 1、求模算法

  • 2、分片枚举

  • 3、范围约定

  • 4、日期指定

  • 5、固定分片hash算法

  • 6、通配取模

  • 7、ASCII码求模通配

  • 8、编程指定

  • 9、字符串拆分hash解析

详细的就不介绍了，我会选择一篇分库分表详细的介绍一下。大概的就是这些了。

18. MySQL刷脏页机制

内存上的数据和磁盘上的数据页的内容一致时，称为 “干净页”。

内存上的数据和磁盘上的数据页的内容不一致时，称为 “脏页”。

MySQL刷脏页的这个机制，会遇到查询卡顿的情况。为什么这么说呢，我们举一个场景吧。我老家里是开超市的，刷脏页的这个机制就好比我们家的账本，如果在超市营业期间，有人来赊账，我们就可以直接把赊账信息填写在那种临时记事本上，等晚上下班了再把数据转移到超市大赊账本上。

那么如果这段期间，临时记事本用光了，就必须停下手里的工作把临时记事本上的数据全部转移到大赊账本之后，再进行下面的操作，在转移的过程中几乎是属于卡顿情况的。

刷脏页是怎么刷的

首页你要告诉MySQL当前的计算机能刷多少的IO能力，这样innodb才能使出吃奶的力气进行刷脏页上的数据，这样也算是性能最大化吧。这个值不能过高也不能过低，过高的导致查询性能过低，如果过低就导致，刷脏页的数据跟不上添加的数据。最终影响系统的使用性能。

刷脏页比例如何设置

涉及的参数是 innodb_io_capacity，innodb_max_dirty_pages_pct

第二个参数是控制刷脏页的比例，默认值为75，也就是75%。

假设脏页比例为M，我们范围是从0-100开始计算的，innodb每次写入的时候都有一个序号，这个序号跟checkpoint之间的差值我们设为N。N会算出一个范围0-100之间的数据。然后再根据F2（N）算法继续计算，这个算法比较复杂，我们只需要能说出N越大，算出来的值就越大就好了。

然后用F1（M）和 F2（N）取一个最大值假设为R，之后引擎就可以按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的

扩展一个参数

innodb_flush_neighbors。MySQL在刷脏页时，会有一个连坐机制，当前面那个参数为1时，就会启用连坐机制，如果为0时，就不会启用连坐机制。

这个连坐机制是什么呢？如果刷一个脏页为AA，AA的旁边的数据页也是脏页，那么刷到AA的同时会把旁边的BB也一起刷掉。

具体的计算比较复杂，建议一笔带过，大概的流程能说出来就可以了。具体的技术文章在我公众号中。

19. 删除数据，表空间大小不变

这个问题应该是被问烂了。这个问题如果学过C语言的时候应该会更容易理解一些。

MySQL中删除数据是采用删除标记的方式。并不是直接删除对应的数据，所以给你的感觉数据的确没有了，但是数据页中仍然存在那块数据内存。

这里扩展一下空间复用的问题。

如果删除的那个数据是在300-700之间，并且插入的那个值的ID也是 300-700之间时，才会去复用这个空间，如果不是这个范围的就不会复用此空间。只有同时删除一整页数据的时候，下一次才会百分之百的复用，这样的几率还是比较小的。

如果不是百分之百的复用那么就会存在一种空洞的现象！我们复现一下，一条1 - 5的记录中，1,2,5被复用了，3,4没有被复用，这种情况就是空洞。

插入也会造成空洞，空洞的主要影响就是数据不紧凑，从而造成查询性能变慢。

解决方案

  1. 重建表
  2. 重新刷新表索引

20. 200G数据，100G内存会不会OOM

答案肯定是不会OOM的

首先我们介绍一下，当我们查询200G的数据的流程问题。

  • 获取一行，写到 net_buffer 中。这块内存的大小是由参数 net_buffer_length 定义的，默认是 16k。
  • 重复获取行，直到 net_buffer 写满，调用网络接口发出去。
  • 如果发送成功，就清空 net_buffer，然后继续取下一行，并写入 net_buffer。
  • 如果发送函数返回 EAGAIN 或 WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。

从上述流程中我们可以得到，一个查询在发送过程中，占用的 MySQL 内部的内存最大就是 net_buffer_length 这么大，并不会达到 200G；socket send buffer 也不可能达到 200G（默认定义 /proc/sys/net/core/wmem_default），如果 socket send buffer 被写满，就会暂停读数据的流程。

综上所述：MySQL查询是 边读边发 的！

21. 系统每天早上重启一下，不然就提示连接数据库失败

22. count(*) 那些问题你了解吗

23. 大数据表你是如何操作的

24. 线上故障排查思路

  1. 首先检查服务器上最大的CPU耗用，内存占用等线程问题。如果是Java的tomcat或者数据库服务。
  2. 先检查Tomcat的日志文件，锁定是Java代码问题还是数据库的服务问题
  3. 如果是Java问题那么就可以直接锁定Tomcat日志了
  4. 如果是数据库的服务问题那么就可以把主要精力锁定在数据库上了
  5. 我们继续第三步扩展一下，找到Tomcat内日志的详细信息进行锁定更小范围性的查询。
  6. 继续第四步扩展一下，锁定到数据库上的话还是比较麻烦的，我们首先看看有没有死锁，大事务，耗时SQL，慢查询日志信息寻找是否这些原因导致数据库宕机等问题

以上内容是公司这边出问题了，我接触到的一些浅的知识，后续将继续维护更新！

25. 如何快速的复制一张表

26. 要不要使用分区表

27. insert语句锁怎么那么多

28. 29条SQL语句性能调优方案

  • 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
  • 应尽量避免在 where 子句中对字段进行 null 值判断，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默 认值。
  • 应尽量避免在 where 子句中使用!=或<>操作符， MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。
  • 应尽量避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行全表扫描， 可以 使用UNION合并查询：select id from t where num=10 union all select id from t where num=20
  • in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了：Select id from t where num between 1 and 3
  • 如果在 where 子句中使用参数，也会导致全表扫描。
  • 应尽量避免在 where 子句中对字段进行表达式操作，应尽量避免在where子句中对字段进行函数操作
  • 索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
  • 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
  • 尽可能的使用 varchar/nvarchar 代替 char/nchar ， 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
  • 最好不要使用”“返回所有：select from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
  • 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
  • 使用表的别名(Alias)：当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
  • 使用“临时表”暂存中间结果，简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
  • 常见的简化规则如下：不要有超过5个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
  • 用OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
  • 在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数。
  • 尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。
  • 当服务器的内存够多时，配制线程数量 = 最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 = 最大连接数+5，严重的损害服务器的性能。
  • 尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。
  • 当有一批处理的插入或更新时，用批量插入或批量更新，绝不会一条条记录的去更新!
  • 在所有的存储过程中，能够用SQL语句的，我绝不会用循环去实现! (例如：列出上个月的每一天，我会用connect by去递归查询一下，绝不会去用循环从上个月第一天到最后一天)
  • sql语句用大写，因为oracle 总是先解析sql语句，把小写的字母转换成大写的再执行。
  • 别名的使用，别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。
  • 避免使用临时表，除非却有需要，否则应尽量避免使用临时表，相反，可以使用表变量代替;大多数时候(99%)，表变量驻扎在内存中，因此速度比临时表更快，临时表驻扎在TempDb数据库中，因此临时表上的操作需要跨数据库通信，速度自然慢。
  • 查询缓冲并不自动处理空格，因此，在写SQL语句时，应尽量减少空格的使用，尤其是在SQL首和尾的空格(因为，查询缓冲并不自动截取首尾空格)。
  • 我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。
  • 当只要一行数据时使用 LIMIT 1
  • 选择合适的索引结构，往往能提升很高的性能。对症下药嘛！

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: