MySQL性能调优与架构设计【面试题三】

说说对SQL语句优化有哪些方法？（选择几条）

（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。

（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。

（3） 避免在索引列上使用计算

（4）避免在索引列上使用IS NULL和IS NOT NULL

（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

如何提高insert的性能？

答：有如下方法：

a)合并多条 insert 为一条，即： insert into t values(a,b,c), (d,e,f) ,

原因分析：主要原因是多条insert合并后日志量（MySQL的binlog和innodb的事务日志） 减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

b)修改参数bulk_insert_buffer_size， 调大批量插入的缓存；

c)设置 innodb_flush_log_at_trx_commit = 0 ，相对于 innodb_flush_log_at_trx_commit = 1 可以十分明显的提升导入速度；

（备注：innodb_flush_log_at_trx_commit
参数对 InnoDB Log 的写入性能有非常关键的影响。该参数可以设置为0，1，2，解释如下：

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file 的刷新或者文件系统到磁盘的刷新操作;

1：在每次事务提交的时候将log
buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步;

2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。）

d）手动使用事务

因为mysql默认是autocommit的，这样每插入一条数据，都会进行一次commit；所以，为了减少创建事务的消耗，我们可用手工使用事务，即START TRANSACTION;insert 。。,insert。。 commit；即执行多个insert后再一起提交；一般1000条insert 提交一次。

什么是覆盖索引？什么是回表查询？

InnoDb存储引擎有两大类索引聚集索引和普通（辅助/二级）索引，聚簇索引的叶子节点存储行记录，因此InnoDb必须要有聚簇索引且仅有一个聚簇索引，而普通索引的叶子节点只存储索引值和主键值，所以，通过聚簇索引一次性能获取所有列的数据，普通索引一般不行。

当我们SQL语句的中列无法在普通索引中获得时，就需要主键值到聚簇索引中获取相关的数据，这个过程就被称为回表。

而如果我们使用联合索引，使得SQL所需的所有列数据在这个索引上就能获得时，我们称为发生了索引覆盖或者覆盖索引。

什么是三星索引？

对于一个查询而言，一个三星索引，可能是其最好的索引。

如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。

一个查询相关的索引行是相邻的或者至少相距足够靠近的则获得一星；

如果索引中的数据顺序和查找中的排列顺序一致则获得二星；

如果索引中的列包含了查询中需要的全部列则获得三星。

三星索引在实际的业务中如果无法同时达到，一般我们认为第三颗星最重要，第一和第二颗星重要性差不多，根据业务情况调整这两颗星的优先度。

大表关联查询优化

一个6亿的表a，一个3亿的表b，通过tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引

select * from
a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。

select * from b
, (select tid from a limit 50000,200) a where b.id = a .tid;

[SELECT *] 和[SELECT 全部字段]有何优缺点？

1>.前者要解析数据字典，后者不需要

2>.结果输出顺序，前者与建表列顺序相同，后者按指定字段顺序。

3>.表字段改名，前者不需要修改，后者需要改

4>.后者可以建立索引进行优化，前者无法优化

5>.后者的可读性比前者要高

请概述下什么是MySQL的分区表

表分区，是指根据一定规则，将数据库中的一张表分解成多个更小的，容易管理的部分。从逻辑上看，只有一张表，但是底层却是由多个物理分区组成。

1、表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

2、表分区的好处？

1）存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据

2）优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

3）分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

3、分区表的限制因素

一个表最多只能有1024个分区

如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

分区表中无法使用外键约束

MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

4、分区表的类型

RANGE分区： 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区

LIST分区： 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。

HASH分区 ：这中模式允许通过对表的一个或多个列的Hash
Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。

KEY分区 ：上面Hash模式的一种延伸，这里的Hash
Key是MySQL系统产生的。

Column分区：需要和RANGE和List结合，支持字符串和日期的分区，也支持多列分区。

复合分区/子分区：分区之下还可以再分区。

5、在实际工作中用分区表比较少

1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

2）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么操作的，不太可控

3）分区表无论怎么分，都是在一台机器上，天然就有性能的上限。

说几条MySQL对SQL的执行做的优化手段

1、对SQL语句的优化，MySQL会对我们的SQL语句做重写，包括条件化简，比如常量传递、除没用的条件等等；还会将一些外连接转换为内连接，然后选择成本最低的方式执行；对IN子查询会进行物化、物化表转连接查询、转换为半连接等方式进行。

2、在SQL语句的执行过程中，MySQL引入了索引条件下推。比如where后面的多个搜索条件都使用到了一个二级索引列，这些搜索条件中虽然出现了索引列，有些却不能使用到索引，像like ‘%…’查询，MySQL为了避免不必要的回表，从二级索引取得的索引记录，先做条件判断，如果条件不满足，则该二级索引记录不会去回表，这样可以大量的减少回表操作的随机IO成本。

3、在回表操作上，因为每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。

4、MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，也可能在一个查询中使用到多个二级索引，称之为：索引合并，比如Intersection交集合并、Union索引合并等等。