数据库之优化

目录

一、简述数据库优化

二、简述如何优化MySQL查询

1、使用索引

2、优化子查询

三、简述如何插入数据更加高效

四、表中包含几千万条数据该如何优化

五、简述MySQL的慢查询优化

六、简述对explain的理解

七、explain有哪些关注内容

---

一、简述数据库优化

        MySQL数据优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I/O的读写速度；通过优化操作系统调度策略，通过MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。

        1、针对查询，我们可以通过使用索引、使用连接代替子查询的方式来提高查询速度。

        2、针对慢查询，我们可以通过分析慢查询日志，来发现引起慢查询的原因，从而有针对性的进行优化。

        3、针对插入，我们可以通过禁用索引、禁用检查等方式来提高插入速度，在插入之后再启用索引和检查。

        4、针对数据库结构，我们可以通过将字段很多的表拆分成多张表、增加中间表、增加冗余字段等方式进行优化。

二、简述如何优化MySQL查询

1、使用索引

        如果查询时没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询记录，从而减少查询的记录数，达到提高查询速度的目的。

        索引可以提高查询的速度，但并不是使用带有索引的字段查询时索引都会起作用。有几种特殊情况，在这些情况下有可能使用带有索引的字段查询时索引并没有起作用。

        ①使用LIKE关键字的查询语句

        在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一位置时索引才会起作用。

        ②使用多列索引的查询语句

        MySQL可以为多个字段创建索引。一个索引可以包括16个字段，对于多列索引，只有查询条件中使用了这些字段中的第一个字段时索引才会被使用。

        ③使用OR关键字的查询语句

        查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询账才使用索引。否则，查询将不适用索引。

2、优化子查询

        使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

        子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

        在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引，性能更好。

三、简述如何插入数据更加高效

        影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。针对这些情况，可以分别进行优化。

        对于MyISAM引擎的表，常见的优化方法如下：

        1、禁用索引

        对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入记录的速度。为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕之后再开启索引。对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。

        2、禁用唯一性检查

        插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响，可以在插入记录前禁用唯一性检查，等到记录插入完毕以后再开启。

        3、使用批量插入

        插入多条记录时，可以使用一条INSERT语句插入一条记录，也可以使用一条INSERT语句插入多条记录。使用一条INSERT语句插入多条记录的情形如下，而这种方式的插入速度更快：

INSERT INFO fruits VALUES
('x1', '101', 'mongo2', '5.7'),
('x2', '101', 'mongo3', '5.7'),
('x3', '101', 'mongo4', '5.7');

        4、使用LOAD DATA INFILE批量导入

        当需要批量导入数据时，如果能用LOAD DATA INDILE 语句，就尽量使用。因为LOAD DATA INFILE 语句导入数据的速度比INSERT语句块。

        对于InnoDB引擎的表，常见的优化方法如下：

        1、禁用唯一性检查

        插入数据之前执行set unique_checks=0来禁止对唯一索引的检查，数据导入完成之后再运行set unique_checks=1。这个和MyISAM引擎的使用方法一样。

        2、禁用外键检查

        插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。

        3、禁用自动提交

        插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。

四、表中包含几千万条数据该如何优化

        建议按照以下顺序进行优化：

        1、优化SQL和索引；

        2、增加缓存，如memcached、redis；

        3、读写分离，可以采用主从复制，也可以采用主主复制；

        4、使用MySQL自带的分区表，这对应用是透明的，无需改代码，但SQL语句是要针对分区表做优化的；

        5、做垂直拆分，即根据模块的耦合度，将一个大的系统分为多个小的系统；

        6、做水平拆分，要选择一个合理的sharding key，为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要修改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表。

五、简述MySQL的慢查询优化

        优化MySQL的慢查询，可以按照以下步骤进行：

        1、开启慢查询日志

        MySQL中慢查询日志默认是关闭的，可以通过配置文件my.ini或者my.cnf中的log-slow-queries选项打开，也可以在MySQL服务启动的时候用--log-slow-queries[=file_name]启动慢查询日志。

        启动慢查询日志时，需要在my.ini或者my.cnf文件中配置long_query_time选项指定记录阈值，如果某条查询语句的查询时间超过了这个值，这个查询过程将被记录到慢查询日志文件中。

        2、分析慢查询日志

        直接分析MySQL慢查询日志，利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句。

        3、常见慢查询优化

        ①索引没起作用的情况

        在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用，只有“%”不在第一个位置时，索引才会起作用。

        MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第一个字段时索引才会被使用。

        查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

        ②优化数据库结构

        对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

        对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

        ③分解关联查询

        很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效。

        ④优化LIMIT分页

        当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是MySQL需要查询10020条然后只返回最后20条，前面的10000条记录都会被舍弃，这样的代价很高，优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

六、简述对explain的理解

        MySQL中提供了EXPLAIN语句和DESCRIBE语句，来分析查询语句，EXPLAIN语句的基本语法如下：

EXPLAIN [EXTENDED] SELECT select_options

        使用EXTENED关键字，EXPLAIN语句将产生附加信息。执行该语句，可以分析EXPLAIN后面SELECT语句的执行情况，并且能够分析出所查询表的一些特征。下面对查询结果进行解释：

        1、id。SELECT识别符。这是SELECT的查询序列号。

        2、select_type。表示SELECT语句的类型。

        3、table。表示查询的表。

        4、type。表示表的连接类型。

        5、possible_keys。给出了MySQL在搜索数据记录时可选用的各个索引。

        6、key。是MySQL实际选用的索引。

        7、key_len。给出索引按字节计算的长度，key_len数值越小，表示越快。

        8、ref。给出关联关系中另一个数据表里的数据列名。

        9、rows。是MySQL在执行这个查询时预计会从这个数据表里面读出的数据行的个数。

        10、Extra。提供了与关联操作有关的信息。

扩展阅读：

        DESCRIBE语句的使用方法与EXPLAIN语句是一样的，分析结果也是一样的，并且可以缩写成DESC。

        DESCRIBE语句的语法形式如下：

DESCRIBE SELECT select_options

七、explain有哪些关注内容

        主要关注下表中的内容：

列名	备注
type	本次查询表连接类型，从这里可以看到本次查询大概的效率
key	最终选择的索引，如果没有索引的话，本次查询效率通常很差
key_len	本次查询用于结果过滤的索引实际长度
rows	预计需要扫描的记录数，预计需要扫描的记录数越小越好
Extra	额外附加信息，主要确认是否出现Using filesort、Using temporary这两种情况

        其中，type包含以下几种结果，从上到下依次是最差到最好：

类型	备注
ALL	执行full table scan，这是最差的一种方式
index	执行full index scan，并且可以通过索引完成结果扫描且直接从索引中取得想要的结果数据，也就是可以避免回表，比ALL略好，因为索引文件通常比全部数据要小
range	利用索引进行范围查询，比index略好
index_subquery	子查询中可以用到索引
unique_subquery	子查询中可以用到唯一索引，效率比index_subquery更高些
index_merge	可以利用index merge特性用到多个索引，提高查询效率
ref_or_null	表连接类型是ref，但进行扫描的索引列中可能包含NULL值
fulltext	全文检索
ref	基于索引的等值查询，或者表间等值连接
eq_ref	表连接时基于主键或非NULL的唯一索引完成扫描，比ref略好
const	基于主键或唯一索引唯一值查询，最多返回一条结果，比eq_ref略好
system	查询对象表只有一行数据，这是最好的情况

         另外，Extra列需要注意以下的几种情况：

关键字	备注
Using filesort	将用外部排序而不是按照索引顺序排列结果，数据较少时从内存排序，否则需要在磁盘完成排序，代价非常高，需要添加合适的索引
Using temporary	需要创建一个临时表来存储结果，这通常发生在对没有索引的列进行GROUP BY时，或者ORDER BY里的列不都在索引里，需要添加合适的索引
Using index	表示MySQL使用覆盖索引避免全表扫描，不需要再到表中进行二次查找数据，这是比较好的结果之一。注意不要和type中的index类型混淆
Using where	通常是进行了全表/全索引扫描后再用WHERE子句完成结果过滤，需要添加合适的索引
Impossible WHERE	对Where子句判断的结果总是false而不能选择任何数据，例如where 1=0，无需过多关注
Select tables optimized away	使用某些聚合函数来访问存在索引的某个字段时，优化器会通过索引值直接一次定位到所需要的数据行完成整个查询。例如MIN()\MAX()，这种也是比较好的结果之一