SQL -优化部分总结+Mysql5.6性能优化

偏执的太偏执、 2022-04-08 10:55 408阅读 0赞

1。你必须选择记录条数最少的表作为基础表.  
(from 是从前往后检索的，所以要最少记录的表放在最前面)  
2。采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些 可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。同时在链接的表中能过滤的就应该先进行过滤。  
（where是从后往前检索，所以能过滤最多数据的条件应放到最后。）  
3。SELECT子句中避免使用 ‘ \* ‘  
4。尽量多使用COMMIT  
5。计算记录条数时候，第一快：count(索引列)，第二快：cout(\*)  
6。用Where子句替换HAVING子句  
7。通过内部函数提高SQL效率  
8。使用表的别名(Alias)  
9。用EXISTS替代IN  
10。用NOT EXISTS替代NOT IN  
11。用表连接替换EXISTS  
12。用索引提高效率  
13。避免在索引列上使用计算（此条包括在select后面 where后面等任何地方，因为在索引列上计算会导致索引失效）  
14。避免在索引列上使用NOT（在索引列使用not会导致索引失效）  
15。用>=替代>  
16。用UNION替换OR (适用于索引列)  
17。用IN来替换OR  
18。避免在索引列上使用IS NULL和IS NOT NULL  
19。总是使用索引的第一个列  
20。用UNION-ALL 替换UNION ( 如果有可能的话)  
21。ORDER BY 子句只在两种严格的条件下使用索引.  
22。避免改变索引列的类型  
23。需要当心的WHERE子句  
24。避免使用耗费资源的操作（带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY）  
25。在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。

\---------------------Mysql5.6性能优化-------------------------------------------

1.  目标  
     了解什么是优化  
     掌握优化查询的方法  
     掌握优化数据库结构的方法  
     掌握优化MySQL服务器的方法
2.  什么是优化？  
     合理安排资源、调整系统参数使MySQL运行更快、更节省资源。  
     优化是多方面的，包括查询、更新、服务器等。  
     原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。
3.  数据库性能参数  
     使用SHOW STATUS语句查看MySQL数据库的性能参数  
    • SHOW STATUS LIKE 'value‘  
     常用的参数：  
    • Slow\_queries 慢查询次数  
    • Com\_(CRUD) 操作的次数  
    • Uptime 上线时间
4.  查询优化  
    4.1. EXPLAIN  
    在MySQL中可以使用EXPLAIN查看SQL执行计划，用法：EXPLAIN SELECT \* FROM tb\_item  
    ![在这里插入图片描述][20181210170829167.png]  
    4.2. 结果说明  
    4.2.1. id  
    SELECT识别符。这是SELECT查询序列号。这个不重要。  
    4.2.2. select\_type  
    表示SELECT语句的类型。

有以下几种值：  
1、 SIMPLE  
表示简单查询，其中不包含连接查询和子查询。  
2、 PRIMARY  
表示主查询，或者是最外面的查询语句。  
![在这里插入图片描述][20181210170851890.png]  
3、 UNION  
表示连接查询的第2个或后面的查询语句。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70]  
4、 DEPENDENT UNION  
UNION中的第二个或后面的SELECT语句，取决于外面的查询。  
5、 UNION RESULT  
连接查询的结果。  
6、 SUBQUERY  
子查询中的第1个SELECT语句。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 1]  
7、 DEPENDENT SUBQUERY  
子查询中的第1个SELECT语句，取决于外面的查询。  
8、 DERIVED  
SELECT(FROM 子句的子查询)。  
4.2.3. table  
表示查询的表。  
4.2.4. type（重要）  
表示表的连接类型。  
以下的连接类型的顺序是从最佳类型到最差类型：

1、 system  
表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。  
2、 const  
数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的。  
![在这里插入图片描述][2018121017113934.png]  
3、 eq\_ref  
mysql手册是这样说的:“对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY”。eq\_ref可以用于使用=比较带索引的列。  
![在这里插入图片描述][20181210171154180.png]  
4、 ref  
查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。  
![在这里插入图片描述][20181210171206433.png]  
5、 ref\_or\_null  
该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况。

6、 index\_merge  
该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key\_len包含了使用的索引的最长的关键元素。  
7、 unique\_subquery  
该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary\_key FROM single\_table WHERE some\_expr)  
unique\_subquery是一个索引查找函数,可以完全替换子查询,效率更高。  
8、 index\_subquery  
该联接类型类似于unique\_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key\_column FROM single\_table WHERE some\_expr)  
9、 range  
只检索给定范围的行,使用一个索引来选择行。  
![在这里插入图片描述][20181210171227512.png]  
10、 index  
该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。  
11、 ALL  
对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）  
4.2.5. possible\_keys  
指出MySQL能使用哪个索引在该表中找到行。  
如果该列为NULL，说明没有使用索引，可以对该列创建索引来提供性能。

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][2018121016460114.png]  
![在这里插入图片描述][20181210164609536.png]  
26。查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][20181210164715612.png]

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。  
1子查询虽然很灵活，但是执行效率并不高。  
2执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表 ，所以，子查询的速度会受到一定的影响。  
优化：  
可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

4.2.6. key  
显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。  
可以强制使用索引或者忽略索引：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 5]

4.2.7. key\_len  
显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

注意：key\_len是确定了MySQL将实际使用的索引长度。

4.2.8. ref  
显示使用哪个列或常数与key一起从表中选择行。  
4.2.9. rows  
显示MySQL认为它执行查询时必须检查的行数。  
4.2.10. Extra  
该列包含MySQL解决查询的详细信息  
• Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。  
• Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。  
• range checked for each record (index map: \#):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。  
• Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。  
• Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。  
• Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。  
• Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。  
• Using sort\_union(…), Using union(…), Using intersect(…):这些函数说明如何为index\_merge联接类型合并索引扫描。  
• Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

4。数据库结构优化  
一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。  
5.1. 将字段很多的表分解成多个表  
对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。  
因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

5.2. 增加中间表  
对于需要经常联合查询的表，可以建立中间表以提高查询效率。  
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。  
5.3. 增加冗余字段  
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。  
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。  
注意：  
冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

1.  插入数据的优化  
    插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。  
    插入数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别：  
    [http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html][http_www.cnblogs.com_panfeng412_archive_2011_08_16_2140364.html]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 6]  
    6.1.1. 禁用索引  
    对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：  
ALTER TABLE table\_name DISABLE KEYS

开启索引语句：  
ALTER TABLE table\_name ENABLE KEYS

对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。  
6.1.2. 禁用唯一性检查  
唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE\_CHECKS = 0;

开启唯一性检查的语句：SET UNIQUE\_CHECKS = 1;  
6.1.3. 批量插入数据  
插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 7]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 8]  
**第二种方式的插入速度比第一种方式快。**

6.1.4. 使用LOAD DATA INFILE  
当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。  
nnoDB  
6.2.1. 禁用唯一性检查  
用法和MyISAM一样。  
6.2.2. 禁用外键检查  
插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign\_key\_checks = 0;  
开启：SET foreign\_key\_checks = 1;  
6.2.3. 禁止自动提交  
插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提供插入速度。

禁用：SET autocommit = 0;  
开启：SET autocommit = 1;  
7. 服务器优化  
7.1. 优化服务器硬件  
服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

需要从以下几个方面考虑：  
1、 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。  
2、 配置高速磁盘，比如SSD。  
3、 合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。  
4、 配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

7.2. 优化MySQL的参数  
通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

MySQL的配置参数都在my.conf或者my.ini文件的\[mysqld\]组中，常用的参数如下：  
![在这里插入图片描述][20181210170147704.png]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 9]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 10]

[20181210170829167.png]: /images/20220406/87ebb5d2ceee4bf083972c0950c9874a.png
[20181210170851890.png]: /images/20220406/7ff8d795a0904f35806a6a8b8b6b353d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70]: /images/20220406/d61ebdbb0d2d44579c6d635c48bee7c6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 1]: /images/20220406/dfa7d6e69760409587ec608a947d8662.png
[2018121017113934.png]: /images/20220406/9de0d10120de4303b6bfac9c376c6559.png
[20181210171154180.png]: /images/20220406/6b223c2eabc642a9b950a90a99685454.png
[20181210171206433.png]: /images/20220406/7276998224bf4fb6a66de07170b2af59.png
[20181210171227512.png]: /images/20220406/768e4c75e2ea45cb943e3245e5be5e71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 2]: /images/20220406/01aacd6713214ca287754a8bc9168a60.png
[2018121016460114.png]: /images/20220406/284e07e575f04b86ac8703893735f9de.png
[20181210164609536.png]: /images/20220406/e12c280fdcdb418f921ace106184c589.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 3]: /images/20220406/a524071caecc46ef85ffd623cc6b244d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 4]: /images/20220406/a0c1d12bef9540d7a5607c4903a0c660.png
[20181210164715612.png]: /images/20220406/37b2d936515642a48d3a55fa07333579.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 5]: /images/20220406/833dad9c51bf4bc18a92613919a21cd2.png
[http_www.cnblogs.com_panfeng412_archive_2011_08_16_2140364.html]: http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 6]: /images/20220406/cf7e5a14b8324bd2b2bc7cedde114a91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 7]: /images/20220406/ddcc083b16794149bc1fda8155dfc356.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 8]: /images/20220406/9acc4e13f24c4fa988d8ee1f87771659.png
[20181210170147704.png]: https://img-blog.csdnimg.cn/20181210170147704.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20181210170204999.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM1MTUyMDM3_size_16_color_FFFFFF_t_70 10]: /images/20220406/66de626b5fe04436b993980f713a54b3.png