Mysql数据库性能调优面试大全经典分析_数据库调优 面试题

1、为啥要死磕Mysql

Mysql作为一款大众免费开源的关系型数据库软件，受到国内很多“穷屌丝”企业的热烈欢迎，看一下目前最新数据库排行，Mysql排在第二位，仅此于Oracle，看来被甲骨文收购后，市场地位上升了很多；既然常用，咱们完全可以精通起来，相关的其他产品都是相通的，例如PG数据库，近期也比较火。

既然myql在企业中这么受欢迎，当然在面试中，企业的招聘JD中会经常提及，接下来，重点从面试的角度，带大家如何从容应对面试种种苛刻的问题。

2、面试与被面试角色的互换

相信各位读者，这些年应该或多或少的参加了一些技术面试，在这里，先说说我自己这些年的面试经历：

 最初，作为一名初级岗位的程序猿,经常会被一些索引,ddl,dml，字段定义，SQL，函数等相关的基础知识点。但是随着这些年培训机构的兴起，刚毕业的大学生都在探讨红黑树，hash链表底层原理的内容；反观在职场工作多年的程序猿，默默的在“啃老”，没有与时俱进。

15年左右，参加过一次面试，被提及一个开放型的问题，“使用Mysql过程中，都可以从哪些角度调优？”

我当时夸夸其谈，重点从索引创建，如何避免索引失效，SQL书写的注意事项等方面来回答，面试自我感觉良好，其实只是一个初级偏上的程序猿；现在回首往事，如果让我再来一次，我可以做的更好；

当然这些年，我也从事一些技术面试工作，数据库优化，JVM调优，多线程并发都是面试的热点问题。

面试中可以造火箭，面试完，其实自己就是个螺丝钉，哈哈。。。。

另外如果各位在这些年中，也参与过一些面试，可以看看我思考的角度是否全面，欢迎留言，互相学习，共同成长。

以下是我这些年，面试和被面试，以及持续学习过程中，慢慢积累的心得体会，分享给大家，希望对大家有帮助，指亮迷茫中的你。

3、如何做mysql数据库性能调优

上面啰嗦了这么多,切入正题，咱们依旧从这个开放性的问题来谈起，带大家以一种结构化的思维来这个开放性的问题：

3.1、从硬件角度思考

如果不考虑成本的情况下，数据库所在的主机硬盘应该采用固态硬盘（SSD）替代机械硬盘，固态硬盘IO读取速度比机械硬盘快很多。

故障举栗：

在主从集群部署的框架下面，主节点负责增删改操作，从节点负责查询，从节点从主节点获取数据变更数据（binlog）

从节点所在机器采用固态硬盘，当业务高峰时，主从数据不一致的情况时有发生，例如客人下了一个订单，但是查询查不到数据。

3.2、从数据库部署架构上，采用集群架构，读写分离的形式

这样部署的好处大概有以下几点：

1、采用多个副本机制，当主库不可用，可以从从库中选择一台作为主，减少数据库宕机造成的影响。

2、主从架构可以水平扩展，提升数据库集群整体的并发响应能力。

缺点：

数据冗余

3.3、从整体的应用架构出发

数据库作为各个应用系统的最宝贵资源，一旦驾崩了，后果很难预估，所以从整体的应用架构出发，咱们可以尝试降低数据库的压力，常规的骚操作，无非是在数据库层上，架设一层缓存，缓存一些频繁检索的热点数据，常规的可以采用redis,ehcache，更夸张的可以使用jvm内存，但是笔者不建议，不方便应用的水平扩展。

当然架设了缓存，需要防范缓存的穿透（恶意请求），缓存的雪崩等等一些问题。

3.4、从连接数据库环节说起

为了避免频繁的创建数据库连接，降低连接带来的IO开销，可以采用池化技术，例如老牌的c3p0,阿里的druid连接池，初始化一批连接。当应用需要连接时，直接从池中获取已经创建好的连接，当然池化技术也带来了额外问题，客户端对连接关闭处理的不好，导致连接泄露，白白占用了宝贵的连接资源。

3.5、从数据库服务端，SQL执行流程说起（知识铺垫）

SQL提交到服务器涉及以下几个环节：

1、连接器：连接管理、权限验证

2、分析器：词法分析、语义分析

3、优化器：优化执行过程（基于成本分析、规则分析）

4、执行器：跟存储引擎交互，Mysql5.5版本开始，采用innodb作为默认的存储引擎。

这一块的知识点作为后续讲解SQL性能调优承上启下使用，先做个铺垫。

3.6、数据库索引

谈到索引，带大家从以下几个角度回顾一下对应的知识点：

3.6.1索引的作用

索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。

3.6.2索引的数据结构

学习一样技能，咱们先去尝试接受他，然后再尝试扪心自问，为啥要这样设计？我们知道Mysql中索引的存储结构采用B+树，经常有以下一个面试题：

经典面试题：为什么mysql索引用B+树而不用哈希表？？？

回答上面这个问题之前，咱们 先来学习一下树相关的数据结构：

推荐一款数据结构学习的网址：Data Structure Visualization

常见的有二叉查找树、AVL树、B树、B+，大家可以基于上面的网址，把玩一下各个数据结构，随意添加删除节点，观察树的变化。

<一>二叉查找树：

树的数据结构特征，左子节点小于父节点，右子节点大于父节点，所以二叉树有个致命的问题，容易演变成线性链表：

                                         

链表的深度，决定了数据检索的IO次数，每个节点存储了左子树，右子树的指针，以及data，假设此时检索5，需要检索IO5次。

 <二>AVL树，平衡二叉查找树

它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。而这里提到的高度差，就是我们下面会引入的平衡因子：BF（balance factor） 

 可以结合上面的网址，自己演示一下，当持续输入1,2,3,4,5，树的结构并非是一颗线性树，通过自身的旋转，满足平衡二叉查找树的特征。

这时检索5，IO的次数是3，性能明显提升了。

咱们日常大表的数据量至少能够达到百万级，如果按照平衡二叉查找树存放数据，树的深度依然很深，导致IO检索次数依旧很高，性能低下。

此处需要分享个概念：叶的概念：

innodb是数据和索引存放在一个文件中，每个节点存放的数据单位（page），默认为16kb，按照

假设一条记录，以及节点左右子节点的指针大小为30byte    16*1024/30   =546记录

在这里插播一条面试点：表的列字段类型应该是定义为int ,还是varchar?

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。
 

引出来接下来要聊的知识点,多路平衡二叉查找树

 <三>B树  多路平衡二叉查找树

 基于多路二叉查找树的思想，如果非叶子节点只存放聚焦索引值以及左右子节点的指针，把行数据单独存储在叶子节点中，这样非叶子节点可以存放更多的索引数据。

<四> B+树，作为B树的升级版本

B+树遵从 左节点 < 父节点 < 右节点；最底层叶子节点严格按照从小到大顺序排列。

这样看B+树天生是为了范围查询而设计的。

写到这里，大家应该能知道mysql索引为啥不用hash索引，hash索引是将数据散列，针对范围检索时，只能一个个查找，显然不符合性能要求。

3.6.3索引的分类

索引分类：主键索引、唯一索引、普通索引、全文索引、组合索引

1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值

 ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');

2、唯一索引：用来建立索引的列的值必须是唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE INDEX index_name('col');

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col');

4、全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col');

 -----mysql的全文索引，其实在实际场景中大家很少使用，elasticsearch在这方面有天然的优势

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3');

面试问题：一张表会不会没有主键索引？？

在mysql的技术文档里面有如下文字，可以回答这个问题：

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

如果没有主动设置主键，就会选一个不包含NULL的第一个唯一索引列作为主键列，并把它用作一个聚集索引。如果没有这样的索引就会使用行号生成一个聚集索引，把它当做主键，这个行号6bytes，自增。可以用select _rowid from table来查询。

咱们在学习innodb事务的隔离级别时，我们知道建一张表，MySQL会给咱们建几个隐藏的列，其中一列就是rowid.  MVCC章节中详细来聊这一块。

3.6.4索引的失效

这个点，面试中经常会被问到，即使高级程序猿，也会不小心犯错，例如检索条件中，针对索引字段做隐式转换、使用函数、模糊检索‘%XXX%’，会导致全表检索。

组合索引没有没有依照顺序列出检索条件，中间又跳过某个字段的现象，导致索引没有办法正常使用，索引覆盖知识点重点来阐述。

其实上面罗列的这些，还是要深入理解索引的存储结构，理解了这一块，自然就会明白为啥不能走索引，B+数据检索的时候，需要有个明确的值匹配过程，而不是让MySQL无从查找，就比如查找 姓名为“%三” 的员工列表，遇到这种非礼的要求，mysql只能全表检索。

面试问题，in操作是否会导致索引失效？？

例如：select    * from  temp    id  in（xx,xx,xx,xx,xx）；

in的查询可以使用到索引，但是in中枚举的数据达到一定数量后，MySQL优化引擎会认为走索引和全表扫描没有区别，会走全表扫描。这个阈值是多少，有待考证！！

3.6.5索引的调优

索引的调优，说白了就是在日常写SQL的过程中，需要关注3.6.4章节中会导致索引失效的注意事项

3.6.6回表、索引覆盖、索引下推

日常我们建表的，可能一张表不止一个索引，除了主键索引外，我们还会建个普通索引，组合索引，唯一索引，来个面试题：

面试题：

1、是不是索引越多越好呢？

      当然不是越多越好，首先一个列是否适合创建索引，要看离散度的取值：

      count(distinct(column_name)) : count(*)//列的重复数据越多，分子越小，离散度越小，范围检索时，SQL执行引擎，会觉得全表扫描和走索引没啥区别，干脆全表扫描吧。

       其次，要结合业务的需要，可以将频繁需要组合在一起的查询的列，建成联合索引，只要符合最左匹配的要求，依旧可以发挥索引的作用。

2、聚集索引和非聚集索引的关系？以及非聚集索引在内存的存放形式是咋样的？

      我们支持聚集索引和行数据绑定在一起，所以可以这样区分聚集索引和非聚集索引，聚集是将索引和行数据绑定在一起，而非聚集索引的叶子节点维护了聚集索引值，可以根据叶子节点找到聚集索引，进而可以检索到对应的行数据。专业术语称之为“回表”

OK，由上面的面试题，引入正题，先来说说回表，咱们先来张图：

索引覆盖

先来个面试题，日常咱们在写SQL 的时候，有没有会像下面这样写么？这样写有啥不好

select * from temp   where name ='XXX'//这样写，估计会被暴打一顿，各家互联网公司研发军规里面，明确禁止这么写法，想想有啥缺点呢？

如果此时我们只需要检索id，那么以下两种写法有啥本质的区别？

select id,age  from temp where name='Jason'  //name,age是一个组合索引

select *  from temp where name='Jason'

我们知道非聚集索引的数据存放，叶子节点存放主键的值，此时根据name检索，可以走索引，压根不用回表，省去了后续的IO操作，性能可以提升很多。

索引下推

索引下推，主要是mysql查询引擎自身层面上，在5.6版本做的优化

我们继续以上面的SQL为例

select id，name,age,sex  from   temp where name='Jason'  and age=15 //name,age是一个组合索引

上面这条SQL，需要额外查询sex,需要回表查询，在5.6版本之前，MySQL的查询引擎是这样来执行，检索条件name='Jason'发现可以命中索引，所以忽略了age=15的这个条件.

假设库中有两条记录的name='Jason'，则在非聚集索引树上会找到两个主键id，根据两个分别回表查询。根据查询结果，再根据age=15这个条件来过滤。这样效率显然低了很多，因为多了一次回表操作。

  大家可以尝试关闭索引下推，看一下执行计划：

set optimizer_switch='index_condition_pushdown=off';

3.7、SQL执行计划（SQL调优工具）

 explain  SQL
            id  select_type table  type  possible_keys  key   key_len   ref   rows   Extra    

涉及字段含义：

id：执行顺序号，值越大，越先执行

select_type：查询类型，普通，联合，子查询等；

    simple：简单查询

    primary：主查询

    subquery：子查询

    drived（衍生）：from 列表中包含的查询

    union：联合查询，union之后的

    union：联合查询的结果查询

table：涉及的表

type：访问类型，即数据是怎么获取到的

    system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

    const：只需匹配一行数据，如：where primary_key=x

    ref：非唯一性索引扫描，匹配多行

    range：范围匹配，如between、in

    index：取索引列，从索引文件读取

    all：全表扫描

possible_keys：查询字段包含的索引

key：使用的索引

key_len：索引中使用的字节数

rows：找到所需的记录所需要读取的行数,这个数值只是估算值，不是特别的精确。

extra：额外信息

    Using temporary：使用临时表保存中间结果，group by

    Using filesort：对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说mysql无法利用索引完成的排序操作称为“文件排序”

    Using index：使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高 

    Using where ： 表示服务器将存储引擎返回行后再应用where过滤条件

    Impossible WHERE：不可达的查询
 

3.8、垂直拆库、拆表

很多同学读到这个章节可能会有些懵，数据库性能调优，咋讲到垂直拆库，拆表了呢？？？

垂直拆库：

其实这块的知识点，是结合系统架构演进而来的，当一个电商网站系统，流量暴增后，需要拆分售前，售后系统，有些时候图块，简单把应用层代码拷贝一份，就可以拆分成两个系统，但是数据库还是公用一个数据库，由此带来的恶梦就此而来：

A系统操作公共表，导致锁表，影响B系统对公共表的使用，最终影响B系统业务的流转。这个时候必须得拆库，简单点，就是把数据库拷贝一份，rename数据库名称即可。

拆表：

3.9、水平分库、分表

水平分库，分表，相信大家日常经常遇到，oracle里面分区表做得特别好，mysql也有分区得概念，但是貌似不咋的。

虽然我们在表上创建了相关得索引，依照B+树得特点，确实提升了检索速度，但是当表数据上千万、上亿，即使有索引，查询起来也特别得慢。

此时大家或许会问，压死索引得最后一根稻草是啥呢，数据量达到多少，索引效率会下降，根据博主得经验，当表数据量超过7百万得时候，就应该考虑分库、分表了。

常见得分库分表中间件，咱们常用mycat shardingjdbc

其实也许再过个三五年，分库、分表也许很难再接触到了，这是为啥呢？

现在中小企业都在上云，一旦上云了之后，存储空间就不是问题了，更多得由云厂商来负责运维，包括分库，分表以及数据迁移。

4.0、事务相关知识点

4.0.1事务的特征

      事务的特征有四种（ACID）： 原子性、一致性、隔离性、持久性；                                                原子性，一个事务中的操作序列，例如转账服务：A账户扣减，B账户增加金额，必须在一个事务中完成，要么全部成功，要么全部失败，这是一个原子原子操作，不可拆分的业务单元。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

     隔离性，A事务和B事务，互不影响，在SQL92标准中，定义了四种事务的隔离级别，供各个数据库厂商来实现， 隔离性的问题（脏读、不可重复读、幻读）。

      持久性，在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

      一致性，在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏；事务的原子性，隔离性，持久性为一致性提供了保障。

4.0.2事务的隔离级别

咱们看一下SQL92标准中关于事务隔离级别的定义： http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

        __Table_9-SQL-transaction_isolation_levels_and_the_three_phenomena_

         _Level__________________P1______P2_______P3________________________

        | READ UNCOMMITTED     | Possib|e Possib|e Possible                |
        |                      |       |        |                          |
        | READ COMMITTED       | Not   | Possibl| Possible                 |
                                 Possible

        | REPEATABLE READ      | Not   | Not    | Possible                 |
        |                      | Possib|e Possib|e                         |
        |                      |       |        |                          |
        | SERIALIZABLE         | Not   | Not    | Not Possible             |
        |______________________|_Possib|e_Possib|e_________________________|
        |                      |       |        |                          |
        |Note: The exclusion of|these p|enomena |or SQL-transactions ex-   |
         ecuting at isolation level SERIALIZABLE is a consequence of the
         requirement that such transactions be serializable.
                关于p1,p2,p3的问题描述：

        The isolation level specifies the kind of phenomena that can occur
         during the execution of concurrent SQL-transactions. The following
         phenomena are possible:

         1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
            transaction T2 then reads that row before T1 performs a COMMIT.
            If T1 then performs a ROLLBACK, T2 will have read a row that was
            never committed and that may thus be considered to have never
            existed.

         2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-
            transaction T2 then modifies or deletes that row and performs
            a COMMIT. If T1 then attempts to reread the row, it may receive
            the modified value or discover that the row has been deleted.

         3) P3 ("Phantom"): SQL-transaction T1 reads the set of rows N
            that satisfy some <search condition>. SQL-transaction T2 then
            executes SQL-statements that generate one or more rows that
            satisfy the <search condition> used by SQL-transaction T1. If
            SQL-transaction T1 then repeats the initial read with the same
            <search condition>, it obtains a different collection of rows.
  我转换成以下表格，大家应该比较熟悉了：

  从以上表格，我们可以看出  读未提交，啥问题都没有解决；读已提交阶段，存在不可重复读，幻读问题；可重复读阶段，存在幻读问题，序列化解决了所有问题，但是牺牲了并发，显然不可取。

4.0.3MVCC

Multi-Version Concurrent Control ,简称MVCC，在mysql 中，5.5以后的版本默认采用Innodb存储引擎，在众多的存储引擎中，只有两款支持事务，其中一款就是Innodb,Innodb的MVCC+行锁（间隙锁、临键锁）解决了幻读问题。

借助于表得几个隐藏字段，例如事务db_trx_id，结合以下三种隔离级别，看一下MVCC是如何解决胀读、不可重复、以及幻读问题

  1、读未提交：

   2、读已提交：

   3、可重复读：

4.0.4锁

知识普及：

myisam存储引擎锁的粒度是表锁，innodb存储引擎锁的粒度是行锁，本章节重点聊一下innodb存储引擎中的临键锁 Next-Key Lock+间隙锁Gap Lock

 mysql 的客户端工具，建议使用Navicat 15 for MySQL，每个查询窗口是一个数据库连接；sqlyog是所有的查询窗口公用的一个连接，所以不太好操作。

      间隙锁针对范围的事务操作，范围区间没有命中结果，例如下面的参考语句 id>1 and id<5  数据库中没有记录，在RR事务隔离级别中，为了防止幻读，采用了锁住（1，5）区间，预防在事务期间，数据插入，产生幻读的情况。

      行锁Record Lock   命中了数据库的唯一一条记录，例如 where id=5,精确匹配到了一条记录，这个我们称为行锁。

       临键锁类似间隙锁，在对范围的事务操作中，范围区间有命中记录，这时会按照下图中拆分，来锁住对应的区间，预防幻读的情况的发生。

      

4.1、学习电子书：

   需要电子书的，可以留言