MySQL 高频面试题目_mysql高级面试题

一、一条SQL查询语句是如何执行的？

(1)客户端与数据库建立连接
(2)查询缓存,如果命中缓存，则立即返回存储在缓存中的数据。
(3)解析器先进行词法分析，将SQL语句打碎成一个个单词，根据构词规则识别单词中的关键字和非关键字，接着进行语法解析，判断是否满足MySQL的语句，接着生成语法树。如果语法错误则返回

(4)解释器是分析语法有没有错误，但是它无法知道数据库中有没有数据表和字段，预处理器根据MySQL规则进一步检查解析树是否合法。如检查表名，列名是否正确，是否有表权限等。
(5)查询优化器对解析树进行优化，然后生成不同的执行计划，然后选择一种最优的执行计划，MySQL里边使用的是基于开销的优化器，哪种执行计划开销最小就使用哪一种。(可以用EXPLAIN来查看执行计划，其能够看出多表关联查询，先查询哪一张表？执行查询的时候用到了什么索引)
(6)执行引擎根据执行计划调用存储引擎的API完成整个查询。

二、MyISAM，InnoDB，Memory三种存储引擎比较

1，MyISAM
应用范围较小，表级别的锁定限制了读/写的性能，因此在Web和数据仓库的配置中，它通常用于只读或以读为主的工作。
特点：支持表级别的锁(插入和更新会锁表)，不支持事务
拥有较高的插入（Insert）和查询（select）速度
存储了表的行数（Count速度更快）
表结构：.frm:存储表定义；myd(MYData):存储数据；MYI（MYIndex）存储引擎
适合：只读之类的数据分析的项目
(怎么快速向数据库插入100万条数据？我们有一种先用ISAM插入数据，然后用存储引擎修改为InnoDB的操作)

2，InnoDB
特点：支持事务，支持外键，因此数据的完整性、一致性更高。
支持行级别的锁和表级别的锁。
支持读写并发，写不阻塞读（MVCC）
特殊的索引存放方式，能够减少IO，提升查询效率
表结构：.frm存储表定义 idb:存储数据和索引，在同一文件中
适合：经常更新的表，存在并发读写或者有事务处理的业务系统。
3，Memory
特点：将所有的数据存储在RAM中，以便在需要快速查找非关键数据的环境中快速访问。
特点：把数据放在内存中，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失，只适合做临时表。
表结构：.frm存储表的定义，数据存储在内存中
适合：做临时表，将表中的数据存储到内存中

存储引擎选择：
如果对数据一致性要求比较高，需要事务支持，可以选择InnoDB
如果数据查询多更新少，对查询性能要求比较高，可以选择MyISAM
如果需要一个用于查询的临时表，可以选择Memory。

为什么MyIsam与InnoDB快？

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快

3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护
MVCC (Multi-Version Concurrency Control)多版本并发控制

InnoDB的四大特性

插入缓冲（insert buffer)
插入缓冲（Insert Buffer/Change Buffer）：提升插入性能，change buffering是insert buffer的加强，insert buffer只针对insert有效，change buffering对insert、delete、update(delete+insert)、purge都有效

只对于非聚集索引（非唯一）的插入和更新有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer 中，再按照一定的频率进行合并操作，再写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

使用插入缓冲的条件：

• 非聚集索引
• 非唯一索引

Change buffer是作为buffer pool中的一部分存在。Innodb_change_buffering参数缓存所对应的操作：(update会被认为是delete+insert)

innodb_change_buffering，设置的值有：inserts、deletes、purges、changes（inserts和deletes）、all（默认）、none。

all: 默认值，缓存insert, delete, purges操作
none: 不缓存
inserts: 缓存insert操作
deletes: 缓存delete操作
changes: 缓存insert和delete操作
purges: 缓存后台执行的物理删除操作

可以通过参数控制其使用的大小：
innodb_change_buffer_max_size，默认是25%，即缓冲池的1/4。最大可设置为50%。当MySQL实例中有大量的修改操作时，要考虑增大innodb_change_buffer_max_size

上面提过在一定频率下进行合并，那所谓的频率是什么条件？

1）辅助索引页被读取到缓冲池中。正常的select先检查Insert Buffer是否有该非聚集索引页存在，若有则合并插入。

2）辅助索引页没有可用空间。空间小于1/32页的大小，则会强制合并操作。

3）Master Thread 每秒和每10秒的合并操作。

二次写(double write)

Doublewrite缓存是位于系统表空间的存储区域，用来缓存InnoDB的数据页从innodb buffer pool中flush之后并写入到数据文件之前，所以当操作系统或者数据库进程在数据页写磁盘的过程中崩溃，Innodb可以在doublewrite缓存中找到数据页的备份而用来执行crash恢复。数据页写入到doublewrite缓存的动作所需要的IO消耗要小于写入到数据文件的消耗，因为此写入操作会以一次大的连续块的方式写入

在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write
doublewrite组成：
内存中的doublewrite buffer,大小2M。
物理磁盘上共享表空间中连续的128个页，即2个区（extend），大小同样为2M。
对缓冲池的脏页进行刷新时，不是直接写磁盘，而是会通过memcpy()函数将脏页先复制到内存中的doublewrite buffer，之后通过doublewrite 再分两次，每次1M顺序地写入共享表空间的物理磁盘上，在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer 中的页写入各个 表空间文件中，此时的写入则是离散的。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，innodb可以从共享表空间中的doublewrite中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

自适应哈希索引(ahi)

Adaptive Hash index属性使得InnoDB更像是内存数据库。该属性通过innodb_adapitve_hash_index开启，也可以通过—skip-innodb_adaptive_hash_index参数
关闭

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升

经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
哈希（hash）是一种非常快的等值查找方法，在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数，取决于B+树的高度，在生产环境中，B+树的高度一般3-4层，故需要3-4次的查询。

innodb会监控对表上个索引页的查询。如果观察到建立哈希索引可以带来速度提升，则自动建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。
AHI有一个要求，就是对这个页的连续访问模式必须是一样的。
例如对于（a,b）访问模式情况：
where a = xxx
where a = xxx and b = xxx

特点
　　1、无序，没有树高
　　2、降低对二级索引树的频繁访问资源，索引树高<=4，访问索引：访问树、根节点、叶子节点
　　3、自适应
3、缺陷
　　1、hash自适应索引会占用innodb buffer pool；
　　2、自适应hash索引只适合搜索等值的查询，如select * from table where index_col=‘xxx’，而对于其他查找类型，如范围查找，是不能使用的；
　　3、极端情况下，自适应hash索引才有比较大的意义，可以降低逻辑读。

预读(read ahead)
InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）
为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

线性预读（linear read-ahead）

方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_read_ahead_threshold，可以控制Innodb执行预读操作的时间。如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值，默认值为56，值越高，访问模式检查越严格
例如，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。你可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。
在没有该变量之前，当访问到extent的最后一个page的时候，Innodb会决定是否将下一个extent放入到buffer pool中。

随机预读（randomread-ahead）

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中，由于随机预读方式给Innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置innodb_random_read_ahead为ON。

InnoDB内存区域的各个缓冲区

redo log:主要节省的是随机写磁盘的IO消耗（转成顺序写）
change buffer:主要节省是随机读磁盘的IO消耗。

changebuffer的作用？
答：InnoDb的页和操作系统的页大小不一致，InnoDB页大小一般为16K，操作系统的页为4K，InnoDB的页写入到磁盘的时，一个页需要分四次写。

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写一部分的情况，比如只写了4K，就宕机了，这种情况叫做部分写失效（partial page write）,可能会导致数据丢失。因为写入一半，这个页已经损坏了，所以无法使用redo log进行恢复。所以提出了double write技术，其跟redo log一样，double write由两部分组成，一部分是内存中的double write，一个部分是磁盘上的double write。因为double write是顺序写入的，不会带来很大的开销。

Change buffer:
在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化，现在对delete和update也有效，叫做写缓冲(change buffer)。
它是一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。 change buffer记录到数据页的操作：在访问这个数据页的时候，或者通过后台线程，或者数据shut down、redo log写满时触发。如果数据库大部分索引都是非唯一性索引，并且业务是写多读少，不会在写数据后立即读取，就可以使用Change buffer（写缓存）。
Change buffer占Buffer pool的比例，默认25%。

Binlog
Binlog以时间的形式记录了所有的DDL和DML语句（因为它记录的是操作而不是数据值，属于逻辑日志），可以用来做主从复制和数据恢复。
跟redo log不一样，它的文件内容是可以追加的，没有固定大小操作，在开启了binlog功能的情况下，我们可以把binlog导出成SQL语句，把所有的操作重放一遍，来实现数据的恢复。

Redo log(重做日志)：因为刷脏不是实时的，缓存中的脏数据，数据库宕机或者重启，这些数据就会丢失。所以数据内存必须有一个持久化的措施。InnoDB把所有对页面的修改操作写入一个日志文件。如果有未同步到磁盘的数据，数据库在启动的时候，会从日志文件进行恢复操作。事务ACID中的持久性就是用Redolog来实现。
同样存储到磁盘，为何存储到日志里边，而不存储到dbfilie里边，因为记录日志是顺序IO，刷盘是随机IO，本质是集中存储和分散存储的区别，因此先把修改写入日志文件，在保证内存安全性的情况下，可以延迟刷盘时间，进而提升系统吞吐。

崩溃恢复的时候，如何判断事务是否需要提交?
1、binlog无记录，redolog无记录：在redolog写之前crash，恢复操作：回滚事务
2、Binlog无记录，redolog状态prepare:在binlog写完之前的crash,恢复操作：回滚事务
3、Binlog有记录，redolog状态prepare:在binlog写完提交事务之前的crash:t提交事务
4、Binlog有记录，redolog状态commit:正常完成的事务，不需要恢复

三、一条SQL更新语句是如何执行的？

更新过程：将数据读到内存的缓冲池 buffer pool中，当然InnoDB的数据数据页并不是都是在访问的时候才缓存到buffer pool,通过预读机制，也就是说，设计者认为访问某个page的数据的时候，相邻的一些page可能会很快被访问到，所以先把这些page放到buffer pool中缓存起来；当然预读取也会带来一些副作用，就是导致占用的内存空间更多，剩余的空闲页更少。如果buffer pool size不是很大，而预读取的数据很多，那么很有可能真正需要缓存的热点数据被预读取数据挤出buffer pool淘汰掉了，下次访问的时候又要先去磁盘。为了解决这个问题，使用LRU缓存，map+双向链表的数据结构，
（1）先存储到缓存log buffer中，默认情况下会事务提交成功，数据会自动同步到磁盘，
（2）记录redo log，并将这行记录状态设置为prepare，
（3）写入binlog,并且提交事务，
（4）将redo log里这个事务的相关状态标记为commit；两阶段提交能够保证数据的一致性。

四、解释一下什么是索引？

数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询，更新数据库表中的数据。类似一本书的目录。

InnoDB中索引的类型：
普通索引(Normal)：最普通的索引，没有任何的限制
唯一索引(Unique)：唯一所以要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引引用primay key创建。
全文索引(Fulltext):针对比较大的数据，比如我们存放的是消息内容，一篇几KB的数据的这种情况

五、B树与B+树的区别 (没有整理好，需要继续整理)

1、B树的路数比节点数多1，B+树的路数与节点数相同
2、B树中每个节点存储的是数据+键值+引用指针，B+ Tree的根节点和枝节点都不会存储数据，只有叶子节点存储数据。因为每个节点的页是16K，不存储data之后，可以存储更多的key值，从而减少IO次数。
3、•B+树的叶子结点都是相链的，因此对整棵树的便利只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
4、B树也有优点，其优点在于，由于B树的每一个节点都包含key和value，因此经常访问的元素可能离根节点更近，因此访问也更迅速。

备注信息：InnoDB存储引擎中有页（page）的概念，页是其磁盘管理的最小单位，在InnoDB存储引擎中默认每个页的大小为16K，B+树将每个节点的大小设置和页大小一样，都为16K，因此读取一个节点，只需要一次IO。
　　而B树中每个节点中不仅包含数据的key值，还会包含除了key值外的所有data值。而每一页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度很大，增大查询时的磁盘IO次数，进而影响查询效率。而B+树中，所有数据节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息， 这样可以大大加大每个节点存储的key值数量， 降低B+树的高度，减少查询时的IO次数。
　做一个推理：InnoDb表的存储引擎中一页的大小为16k，一般表的主键类型占用8个字节，指针占用8个字节，那么一页可以存放16kb/(8b*8b)=1000个键值（关键字），所以一个高度为3的B+树可以存储1000^3即10亿条记录。查询时只需要两次IO操作就可找到要查找的记录。大大节约了查询时间。

五、为什么不用红黑树？

红黑树也是BST，但是不是严格平衡的，通过变色和旋转来保持平衡。
必须满足5个约束：
1，节点分为红色或者黑色。
2，根节点必须是黑色的
3，叶子节点都是黑色的NULL节点
4，红色节点的两个子节点都是黑色(不允许两个相邻的红色节点)
5，从任意节点出发，到其每个叶子节点的路径包含相同数量的黑色节点

基于以上规则可以推导出：
从根节点到叶子节点的最长路径（红黑相间的路径）不大于最短路径（全节点的二倍）
为什么不用红黑树？1，只有两路 2，不够平衡

红黑树一般只放在内存里边用，例如：java的TreeMap，它可以用来实现一致性哈希。

六、MySQL中索引的数据结构——B树索引和Hash索引

Hash索引：以KV的形式检索数据，也就是说，它会根据索引字段生成hash码，指针指向数据（memory存储引擎可以使用Hash索引）

Hash索引的优点：(1) 时间复杂度O(1),查询速度比较快，因为Hash索引里边的顺序存储，所以不能用于排序。
(2)查询数据的时候要根据键值计算哈希吗，所以它只能支持等值查询，不支持范围查询，和排序，在有大量重复键值情况下，哈希索引的效率是极低的，因为存在所谓的哈希碰撞问题。

B+树索引

MyISAM: 其中包含三个配置文件 frm 表的结构和定义信息,myd 表的数据,myi 表的索引

MyISAM的B+Tree里边，叶子节点村粗的是数据文件对应额磁盘地址，所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。

非主键索引和主键索引数据的方式是没有任何区别的，一样是在索引文件中找到磁盘地址，然后到数据文件里边获取数据。

InnoDB:

在InnoDB的某个索引的叶子节点上，它直接存储了我们的数据。所以，在InnoDB中索引即数据，就是这个原因。

聚集索引的概念：就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一直的。
InnoDB组织数据的方式就是（聚集）索引组织表(clustered index organize table)。如果一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序。

InnoDB中，主键索引和辅助索引有主次之分，如果主键有索引，那么主键索引就是聚集索引。其他的索引统一叫做“二级索引”(secondary index)。

二级索引检索数据的流程是这样的：
当我们用Name索引查询一条纪律，它会在二级索引的叶子节点找到了Name=qingshan,拿到了主键值，也就是Id=1，然后再到主键索引的叶子节点拿到数据。为什么不存地址而是存储键值？因为地址会变化。

从这个角度来讲，因为主键索引比二级索引少扫描了一颗B+ Tree（避免了回表，），它的速度会快一点。

七、主键索引的选取与加索引的技巧

主键索引的选取：
1，如果我们定义了主键(Primary key )，那么InnoDB会选择主键作为聚集索引。
2，如果没有显示定义主键，则InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引。
3，如果也没有这样额唯一索引，则InnoDB会选择内置6自己长的RowID作为隐藏的聚集索引，他会随着行记录的写入而主键递增。

加索引的一些技巧：
1，列的离散度，(列的全部不同值)/所有数据的比例 简单的说列的重复值越多，离散度就越低；重复值越少，离散度就越高。 对于离散度高的，即使加了索引，查询消耗的时间更多了，在B+ Tree里边的重复值太多，MySQL的优化器发现走索引跟使用全表扫描差不多的时候，就算建了索引，也不一定会走索引。
对于离散度低的字段，不建议加索引
2，联合索引最左匹配
当我们创建三个字段的索引Index (name,age,address)，相当于创建了三个索引：
Index(a)
Index(a,b)
Index(a,b,c)

3，覆盖索引
回表： 非主键索引先通过索引找到主键索引的值，再通过主键值查询索引里边没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

在二级索引里边，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。
因为覆盖索引减少了IO次数，减少了数据的访问量，可以大大地提升查询效率。

4，索引条件下推
索引条件下推(Index Condition Pushdown)，5.6以后完善的功能，只适用于二级索引。ICP的目标是减少访问表的完整行的读数量从而减少I/O操作。

将 Index Filter 从 Server 层 Push Down 到了引擎层，减少了因回表产生的磁盘 I/O，也减少了与 Server 层的交互，提高了 SQL 执行效率

举一个例子：
Select * from student where age=22 and name like %明

对于MySQL 5.6之前，能够根据age=22能够查询出数据，name like %明 根据最左匹配原则索引无效，所以查询过程是这样的
（1）根据联合索引查出所有姓名wang的二级索引数据(3个主键值：)
（2）回表，到主键索引上查询所有符合条件的数据
（3）把这三条数据返回server层，在Server层过滤出以zi结尾的员工。

ICP是默认开启的，也就是说针对二级索引，只要能把条件下推给存储引擎，它就会下推，不需要我们干预。



索引的创建与使用：
1，在用于where判断order排序和join的(on)、group by的字段上创建索引
2，索引的根数不要过多——浪费空间，更新变慢
3，过长的字段，建立前缀索引

4，区分度低的字段，例如性别，不要建索引
5，频繁更新的值，不要作为主键或者索引——页分裂
6，随机无需的值，不建议作为索引，例如身份证，UUID——无序，分裂
7，组合索引把散列性高（区分度高）的值放在前面
8，创建复合索引，而不是修改单列索引

七、用不到索引的几种情况

1，索引列上使用函数(repalce\substr\concat\sum count avg)、表达式计算
(±*/)

2，字符串不加引号，出现隐式转换

3，like条件中前面带%
Where条件中 like abc%, like %2673%, like %888都用不到索引吗？

4，负向查询
NOT LIKE 不能：

！ = （<>） 和 NOT IN在某些情况下可以：

八、事务的特性

事务的特性分为
原子性：undo log roll back
隔离性：基于并发控制的锁+MVCC存储(多版本并发控制)
持久性：redo log(crash-safe) 双写缓冲(Double buffer)
一致性：上边的原子性，隔离性，持久性都是为了一致性

事务并发带来的问题：
事务并发的问题：
1，脏读

2，不可重复度

3，幻读

事务隔离级别：
读取未提交
读取已提交
可以重复读
序列化

MVCC核心思想：



MVCC的实现原理：
1、每一个事务在开启和查询的时候，都需要建立一个ReadView（一致性视图）

#锁

一个事务能够成功地给一张表加上表锁的前提，是没有其他任何一个事务已经锁定了这张表的任何一行数据。

加锁之前为了确定没有加上任意一行数据，需要遍历一张表的数据从头到尾部（全表扫描）。

任何一个事务加行锁的时候，会先加一 个表级别的意向锁，

决定能不能加表锁成功地标志 （意向锁），为了提升加表锁的效率。




MySQL中 行锁解决了不可重复读的问题，gap锁解决了幻读的问题。MySQL中默认使用邻键锁，Next key Lock =record Lock+gap Lock。

由于可重读的隔离级别下，默认采用Next-Key Locks，就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙，

九、查询速度慢的原因，如何解决？

数据库重启，能够释放资源，比如：连接，以及内存占用的资源
Mysql优化的维度：
（1）连接：增加服务端可用连接数，减少客户端可用连接数
（2）架构方面:减轻数据库的压力:redis缓存
（3）架构方面:集群,基于主从复制的读写分离
（4）架构方面:分库分表：垂直分库（业务主题），水平分库分表
(5) SQL和索引的优化
（6）表结构和存储引擎的优化(不同存储引擎适用于不同的存储引擎，选用能用的最小的结构，行政区号编码，卡号，建表； 表结构分区（天，月，年）商户信息表（基础信息表，联系信息表，结算信息表，附件信息表）)
（7）操作系统，MySQL配置参数，操作系统
(8)硬件：升级硬件型号
(9)业务层面的优化，例如：双十一期间，蚂蚁庄园用户饲料发放延迟(降级，用来保证最核心的业务；银行转账只能查看月份，双十一预售(分流 ))

（交易历史表，根据月份，建立12个分区）

十、具体SQL慢查询分析

1、MySQL会开启慢查询日志 slow query log记录，慢查询默认关闭
2，我们通常使用mysqldumpslow工具统计慢SQL信息
3，找到非常慢的SQL信息后，可以使用Explain来分析
Explain中有几个比较重要的字段

• Type
  对于主键索引或者唯一索引，只能查询一条数据
  myisame,memory的查询结果：type的种类为system ;
  InnoDB存储引擎的查询结果：type的种类为const
  
  我们至少要优化到range级别
• Possible_keys
  可能用到的索引，对于联合索引(A,B)来说，有可能两个都用到
• Key
  是最终使用的索引（用不到索引，但是优化器对于覆盖索引，索引下推，但是优化器最终优化后直接走索引）。
• Extra 额外的信息
  
  Useing Where存储引擎层返回的数据不全是客户端所需要的，所以需要在server层过滤后再返回客户端
  （A，B） Select B返回server层
  Using Index Condition：索引条件下推，本来是需要返回server层，但是在某些特殊情况下，下推到存储引擎，
  Using filesort: 不能直接用索引排序（order by id; ）
  Using temporary: 得到最终数据之前，数据需要放到临时表里边存储，再去做相应的过滤操作。 （Distinct字段，Group by操作；需要优化，为字段建立索引，或者联合索引）

对于翻页 Select语句如何优化，
Select * from user_innodb limit 90000,10; 0.3 s
Select * from user_innodb where id>90000 limit 10

SQL优化的目的，是为了用到索引

十一、SQL优化的案例分析

服务端状态分析：
如果出现连接变慢，查询被阻塞，无法获取连接的情况
1，重启！
2，Show processlist查看线程状态，连接数数量，连接时间，状态
3，查看锁的状态
4，Kill有问题的线程

对于具体的慢SQl：
一，分析查询基本情况
涉及到的表的表结构，字段的索引情况，每张表的数据量，查询的业务含义；这个非常重要，因为有的时候你会发现SQL根本没必要这么写，或者表设计有问题。
二，找出慢的原因
1，查看执行计划，分析SQL的执行情况，了解表访问顺序，访问类型，索引，扫描行数等信息。
2，如果总体的时间很长，不确定哪一个影响因素最大，通过条件的递减，顺序的调整，找出慢查询的主要原因，不断地尝试验证。
找到原因：比如是没有走索引引起的，还是关联查询引起的，还是order by引起的。
找到原因之后：
二，对症下药
1，创建索引或者联合索引
2，改写SQL，这里需要平时积累经验，例如：
1)使用小表驱动大表

为什么小表驱动大表比较优呢？
假设有n条记录的id索引,遍历搜寻单值的算法复杂度大概是O(lgn)，

设A表记录数m,B表记录数n,m>>n（远大于），两表的id上都有索引。
当A驱动B查询时，复杂度大概是O(mlgn);反之，B驱动A查询时，复杂度大概是O(nlgm)。
最后，比较下mlgn和nlgm的增长趋势，设m=an(a>>1)，有anlgn/(nlg(an)) = algn/(an),
最后归结为n^{a和an的量级，显然n}a>>an(可由无穷大的求导法则推测两者的增长速度),
所以O(mlgn)>O(nlgm),也就是小表驱动大表比较优。

2)用Join来代替子查询
(子查询需要建立临时表)
连接（JOIN）… 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作

3)Not exist 转换为left join is Null
MySQL优化–NOT EXISTS和LEFT JOIN方式差异

两种执行方式对比：

1、从执行计划来看，两个表都使用了索引，区别在于NOT EXISTS使用“DEPENDENT SUBQUERY”方式，而LEFT JOIN使用普通表关联的方式
2、从执行过程来看，LEFT JOIN方式主要消耗Sending data的上，在NOT EXISTS方式主要消耗在"executing"和“Sending data”两项上，受限于PROFILE只能记录100行结果，因此超过57万个"executing"和“Sending data”的组合项没有显示，虽然每次"executing"和“Sending data”的组合项消耗时间较少（约50毫秒），但由于执行次数较高，导致最终执行时间较长（50μs*578436=28921800us=28.92s）

如何在NOT EXISTS和LEFT JOIN中选择：
1、当外层数据较少时，子查询循环次数较少，使用NOT EXISTS并不会导致严重的性能问题，推荐使用NOT EXISTS方式。
2、当外层数据较大时，子查询消耗随外层数据量递增，查询性能较差，推荐使用LEFT JOIN方式

总结：
按照存在即合理是客观唯心主义的理论，NOT EXISTS以更直观地方式实现业务需求，在SQL复杂度上要远低于LEFT JOIN，且在生产执行计划时，NOT EXISTS方式相对更稳定些，LEFT JOIN可能会随统计信息变化而生产不同的执行计划。

4)Or 改为union (or进行全表扫描)
可以用 union 或 union all 代替 or ，以避免因为 or 引起的全表扫描

5)如果结果集允许重复的话，使用Union All代替Union
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
Union All：对两个结果集进行并集操作，包括重复行，不进行排序；
因为 UNION ALL 不去重，效率高于 UNION。

6)大偏移的limit，先过滤再排序

对于翻页 Select语句如何优化，
Select * from user_innodb limit 90000,10; 0.3 s
Select * from user_innodb where id>90000 limit 10

小结一下，在上述的执行过程中，造成 limit 大偏移量执行时间变久的原因有：

查询所有列导致回表
limit a, b会查询前a+b条数据，然后丢弃前a条数据
综合上述两个原因，MySQL 花费了大量时间在回表上，而其中a次回表的结果又不会出现在结果集中，这才导致查询时间变得越来越长。

如果SQL语句本身解决不了，就要上升到表结构和架构上
3，表结构(冗余，拆分，not null等)、架构优化(UAN存读写分离分库分表)
业务层面的优化