大厂面试 "浅谈" 的数据库你系统学习过了吗？我们该如何将复杂的数据库原理去其糟糠取其精华，接下来，让我一起 "浅谈" 一下它的底层优化吧！
MySQL 优化
结构优化
1、回表
原因：基于InnoDB存储引擎下，由于存储的数据结构是聚簇索引B+树，查询数据没有主键时，用不到主键 索引，则需要添加非主键的普通索引（辅助索引），InnoDB在查询辅助索引时，由于是单独的辅助 B+树，叶子节点存储的是主键值，并以此来作为指向行的指针，再通过聚簇索引中的B+树检索到对 应的叶子节点，获取整行数据，这个过程叫做回表
回表就是先通过辅助索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主 键索引的查询需要多扫描一棵索引树。
- 比如：select * from merchandise where serial_no = '零食'
使用serial_no查询商品，即使用辅助索引进行查询，则会先检索辅助索引中的B+树的serial_no，找到对 应的叶子节点，获取主键值，然后再通过聚簇索引中的B+树检索到对应的叶子节点，然后获取整行数据, 需要查询两次B+树，影响查询效率，这种过程叫做回表
解决：假设只需要查询商品的名称、价格信息，有什么方式来避免回表呢？可以建立一个组合索引，即商 品编码、名称、价格作为一个组合索引。如果索引中存在这些数据，查询将不会再次检索主键索引， 从而避免回表
从辅助索引中查询得到记录，而不需要通过聚簇索引查询获得，MySQL中将其称为覆盖索引。使用覆盖索 引的好处很明显，不需要查询出包含整行记录的所有信息，因此可以减少大量的I/O操作
通常在InnoDB中，除了查询部分字段可以使用覆盖索引来优化查询性能之外，统计数量也会用到。例如， SELECT COUNT(*)时，如果不存在辅助索引，此时会通过查询聚簇索引来统计行数，如果此时正好存在 一个辅助索引，则会通过查询辅助索引来统计行数，减少I/O操作。
查询优化
1、模糊查询效率很低
原因：like本身效率就比较低，应该尽量避免查询条件使用like；对于like ‘%...%’（全模糊）这样的 条件，是无法使用索引的，会引起全表扫描，而在InnoDB存储引擎文章介绍中有说过，全表扫描会把内存 中缓冲池的热数据全部换一次血，因为InnoDB使用的是LRU算法，尽管做了一些优化，避免了全部换血， 把热点数据保留了下来，但也会有一定的影响，自然，全盘扫描的效率自然就很低，另外，由于匹配算法 的关系，模糊查询的字段长度越大，模糊查询效率越低
解决：1.尽量避免使用模糊查询，2.如果一定要用，不能使用全模糊，可以使用右模糊，即like ‘…%’， 是会使用索引的；3.左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index 的形式， 变化成 like ‘…%’；全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低数据库服务 器的负载考虑，尽可能地减少数据库模糊查询。
2、查询条件中含有is null的select语句执行慢
原因：Oracle 9i中，查询字段is null时单索引失效，引起全表扫描。
解决：SQL语法中使用NULL会有很多麻烦，最好索引列都是NOT NULL的；对于is null，可以建立组 合索引，nvl(字段,0),对表和索引analyse后，is null查询时可以重新启用索引查找,但是效率还不是 值得肯定；is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。
应尽量避免在where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引 逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者 使用一个特殊的值，如0，-1作为默认值。
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只 要这些列中有一列含有null，该列 就会从索引中排除。也就是说如果某列存在空值，即使对该列建 索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
此例可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num = 0;
3、查询条件中使用了不等于操作符（<>、!=）的select语句执行慢
原因：SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引
解决：通过把不等于操作符改成or，可以使用索引，避免全表扫描。
column<>'aaa' //全表扫描column<'aaa' OR column>'aaa' //使用索引
4、组合索引使用不当
查询条件中没有前导列，导致索引不起作用；
create index skip1 on emp5(job,empno); select count(*) from emp5 where empno=7900; //全表扫描 select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900; //使用组合索引
使用组合索引时，在排序时应按照组合索引中各列顺序进行排序（即使只有一个列需要排序），否则性能较差。
create index skip1 on emp5(job,empno,date); select job, empno from emp5 where job='manager'and empno='10' ORDER BY date desc; //性能较差select job, empno from emp5 where job='manager'and empno='10' ORDER BY job,empno,date desc; //使用组合索引
5、OR语句使用不当
OR语句连接的条件中包含的列没有全部建立索引。
例如：where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。
例如：where A=：1 or B=：2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。
使用or条件连接时，请先参考官方文档中—索引合并优化的说明
6、UPDATA语句updata了全部字段
如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
7、对于多张大数据量的表JOIN
原因：没有先分页，导致逻辑读很高；
解决方法：先分页再JOIN。
8、select count(*) from table；
这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
9、常用查询
对于反复执行的查询，WHERE子句中使用变量绑定可以降低解析时间，提高性能
10、group by优化
- 优化策略：先给分组字段建索引，再对该表分组、分组后再和其他表关联查询
- 优化理论：先利用索引将结果集快速最小化、然后再和其他表关联
12、选取最适用的字段属性
- MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用 VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用 MEDIUMINT而不是BIGIN来定义整型字段。 - 另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候， 数据库不用去比较NULL值。对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被 当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库 的性能
- 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加 存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比 较一次就够了。
13、使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个 步骤的查询工作。
14、使用联合(UNION)来代替手动创建的临时表
比如：
SELECT Name, Phone FROM client UNIONSELECT Name, BirthDate FROM author UNIONSELECT Name, Supplier FROM product
15、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是 所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来 完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变 得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成 功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至 会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功， 要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关 键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。
BEGIN; INSERT INTO salesinfo SET CustomerID = 14;UPDATE inventory SET Quantity = 11 WHERE item ='book';COMMIT;
事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一 种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。
16、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是 在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到 该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；’ 但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个 例子中事务的功能。
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item = 'book';UPDATE inventory SET Quantity = 11 WHERE Item ='book';UNLOCK TABLES;
17、复合索引
比如有一条语句是这样的：
select * from users where area='beijing' and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引， 所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将 带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、 (area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的 列放在最左边，依次递减。
18、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空 间和I/O操作。
19、排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些 列创建复合索引。
20、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而 like “aaa%”可以使用索引。
21、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01';
22、不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3 来代替。
23、存储过程和触发器
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存 储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
数据库配置优化
公共参数默认值：
max_connections = 151#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右sort_buffer_size = 2M、#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16Mopen_files_limit = 1024 #打开文件数限制，如果show global status like 'open_files'查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死
InnoDB参数默认值：
innodb_buffer_pool_size = 128M#索引和数据缓冲区大小，一般设置物理内存的60%-70%innodb_buffer_pool_instances = 1 #缓冲池实例个数，推荐设置4个或8个innodb_flush_log_at_trx_commit = 1 #关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。innodb_file_per_table = OFF#默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。innodb_log_buffer_size = 8M #日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M
MyISAM参数默认值：
key_buffer_size = 16M#索引缓存区大小，一般设置物理内存的30-40%read_buffer_size = 128K#读操作缓冲区大小，推荐设置16M或32Mquery_cache_type = ON#打开查询缓存功能query_cache_limit = 1M #查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖query_cache_size = 16M#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值
.................................................
数据库的相关资料，文档可以在mysql在线文档去查看：
英文文档：https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
