自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

盤點(diǎn)開(kāi)發(fā)中那些常用的MySQL優(yōu)化

作者：佚名
數(shù)據(jù)庫(kù) MySQL
很多時(shí)候數(shù)據(jù)庫(kù)的性能是由于不合適（是指效率不高，可能會(huì)導(dǎo)致鎖表等）的SQL語(yǔ)句造成，本篇博文只是介紹簡(jiǎn)單的SQL優(yōu)化

 1、大批量插入數(shù)據(jù)優(yōu)化

（1）對(duì)于MyISAM存儲(chǔ)引擎的表，可以使用：DISABLE KEYS 和 ENABLE KEYS 用來(lái)打開(kāi)或者關(guān)閉 MyISAM 表非唯一索引的更新。 

  1. ALTER TABLE tbl_name DISABLE KEYS;  
  2. loading the data  
  3. ALTER TABLE tbl_name ENABLE KEYS; 

（2）對(duì)于InnoDB引擎，有以下幾種優(yōu)化措施：

① 導(dǎo)入的數(shù)據(jù)按照主鍵的順序保存：這是因?yàn)镮nnoDB引擎表示按照主鍵順序保存的，如果能將插入的數(shù)據(jù)提前按照排序好自然能省去很多時(shí)間。

比如bulk_insert.txt文件是以表user主鍵的順序存儲(chǔ)的，導(dǎo)入的時(shí)間為15.23秒 

  1. mysql> load data infile 'mysql/bulk_insert.txt' into table user;  
  2. Query OK, 126732 rows affected (15.23 sec)  
  3. Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0 

沒(méi)有按照主鍵排序的話，時(shí)間為：26.54秒 

  1. mysql> load data infile 'mysql/bulk_insert.txt' into table user;  
  2. Query OK, 126732 rows affected (26.54 sec)  
  3. Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0 

② 導(dǎo)入數(shù)據(jù)前執(zhí)行SET UNIQUE_CHECKS=0，關(guān)閉唯一性校驗(yàn)，帶導(dǎo)入之后再打開(kāi)設(shè)置為1：校驗(yàn)會(huì)消耗時(shí)間，在數(shù)據(jù)量大的情況下需要考慮。

③ 導(dǎo)入前設(shè)置SET AUTOCOMMIT=0，關(guān)閉自動(dòng)提交，導(dǎo)入后結(jié)束再設(shè)置為1：這是因?yàn)樽詣?dòng)提交會(huì)消耗部分時(shí)間與資源，雖然消耗不是很大，但是在數(shù)據(jù)量大的情況下還是得考慮。

2、INSERT的優(yōu)化

（1）盡量使用多個(gè)值表的 INSERT 語(yǔ)句，這種方式將大大縮減客戶端與數(shù)據(jù)庫(kù)之間的連接、關(guān)閉等消耗。（同一客戶的情況下），即：

  1. INSERT INTO tablename values(1,2),(1,3),(1,4) 

實(shí)驗(yàn)：插入8條數(shù)據(jù)到user表中（使用navicat客戶端工具） 

  1. insert into user values(1,'test',replace(uuid(),'-',''));  
  2. insert into user values(2,'test',replace(uuid(),'-',''));  
  3. insert into user values(3,'test',replace(uuid(),'-',''));  
  4. insert into user values(4,'test',replace(uuid(),'-',''));  
  5. insert into user values(5,'test',replace(uuid(),'-',''));  
  6. insert into user values(6,'test',replace(uuid(),'-',''));  
  7. insert into user values(7,'test',replace(uuid(),'-','')); 
  8. insert into user values(8,'test',replace(uuid(),'-','')); 

得到反饋： 

  1. [SQL] insert into user values(1,'test',replace(uuid(),'-',''));  
  2. 受影響的行: 1  
  3. 時(shí)間: 0.033s  
  4. [SQL]   
  5. insert into user values(2,'test',replace(uuid(),'-',''));  
  6. 受影響的行: 1  
  7. 時(shí)間: 0.034s  
  8. [SQL]   
  9. insert into user values(3,'test',replace(uuid(),'-',''));  
  10. 受影響的行: 1  
  11. 時(shí)間: 0.056s  
  12. [SQL]   
  13. insert into user values(4,'test',replace(uuid(),'-',''));  
  14. 受影響的行: 1  
  15. 時(shí)間: 0.008s  
  16. [SQL]   
  17. insert into user values(5,'test',replace(uuid(),'-',''));  
  18. 受影響的行: 1  
  19. 時(shí)間: 0.008s  
  20. [SQL]   
  21. insert into user values(6,'test',replace(uuid(),'-',''));  
  22. 受影響的行: 1  
  23. 時(shí)間: 0.024s  
  24. [SQL]   
  25. insert into user values(7,'test',replace(uuid(),'-',''));  
  26. 受影響的行: 1  
  27. 時(shí)間: 0.004s  
  28. [SQL]   
  29. insert into user values(8,'test',replace(uuid(),'-',''));  
  30. 受影響的行: 1  
  31. 時(shí)間: 0.004s 

總共的時(shí)間為0.171秒，接下來(lái)使用多值表形式： 

  1. insert into user values  
  2. (9,'test',replace(uuid(),'-','')),  
  3. (10,'test',replace(uuid(),'-','')),  
  4. (11,'test',replace(uuid(),'-','')),  
  5. (12,'test',replace(uuid(),'-','')),  
  6. (13,'test',replace(uuid(),'-','')),  
  7. (14,'test',replace(uuid(),'-','')),  
  8. (15,'test',replace(uuid(),'-','')),  
  9. (16,'test',replace(uuid(),'-','')); 

得到反饋： 

  1. [SQL] insert into user values  
  2. (9,'test',replace(uuid(),'-','')),  
  3. (10,'test',replace(uuid(),'-','')),  
  4. (11,'test',replace(uuid(),'-','')),  
  5. (12,'test',replace(uuid(),'-','')),  
  6. (13,'test',replace(uuid(),'-','')),  
  7. (14,'test',replace(uuid(),'-','')),  
  8. (15,'test',replace(uuid(),'-','')),  
  9. (16,'test',replace(uuid(),'-',''));  
  10. 受影響的行: 8  
  11. 時(shí)間: 0.038s 

得到時(shí)間為0.038，這樣一來(lái)可以很明顯節(jié)約時(shí)間優(yōu)化SQL

（2）如果在不同客戶端插入很多行，可使用INSERT DELAYED語(yǔ)句得到更高的速度，DELLAYED含義是讓INSERT語(yǔ)句馬上執(zhí)行，其實(shí)數(shù)據(jù)都被放在內(nèi)存的隊(duì)列中。并沒(méi)有真正寫入磁盤。LOW_PRIORITY剛好相反。

（3）將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（InnoDB引擎是在同一個(gè)表空間的）。

（4）如果批量插入，則可以增加bluk_insert_buffer_size變量值提供速度（只對(duì)MyISAM有用）

（5）當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，使用LOAD DATA INFILE，通常比INSERT語(yǔ)句快20倍。

3、GROUP BY的優(yōu)化

在默認(rèn)情況下，MySQL中的GROUP BY語(yǔ)句會(huì)對(duì)其后出現(xiàn)的字段進(jìn)行默認(rèn)排序（非主鍵情況），就好比我們使用ORDER BY col1,col2,col3…所以我們?cè)诤竺娓暇哂邢嗤校ㄅcGROUP BY后出現(xiàn)的col1,col2,col3…相同）ORDER BY子句并沒(méi)有影響該SQL的實(shí)際執(zhí)行性能。

那么就會(huì)有這樣的情況出現(xiàn)，我們對(duì)查詢到的結(jié)果是否已經(jīng)排序不在乎時(shí)，可以使用ORDER BY NULL禁止排序達(dá)到優(yōu)化目的。下面使用EXPLAIN命令分析SQL。Java知音公眾號(hào)內(nèi)回復(fù)“面試題聚合”，送你一份面試題寶典

在user_1中執(zhí)行select id, sum(money) form user_1 group by name時(shí)，會(huì)默認(rèn)排序（注意group by后的column是非index才會(huì)體現(xiàn)group by的排序，如果是primary key，那之前說(shuō)過(guò)了InnoDB默認(rèn)是按照主鍵index排好序的） 

  1. mysql> select*from user_1;  
  2. +----+----------+-------+  
  3. | id | name     | money |  
  4. +----+----------+-------+  
  5. |  1 | Zhangsan |    32 |  
  6. |  2 | Lisi     |    65 |  
  7. |  3 | Wangwu   |    44 |  
  8. |  4 | Lijian   |   100 |  
  9. +----+----------+-------+  
  10. 4 rows in set 

不禁止排序，即不使用ORDER BY NULL時(shí)：有明顯的Using filesort。

當(dāng)使用ORDER BY NULL禁止排序后，Using filesort不存在

4、ORDER BY 的優(yōu)化　　

MySQL可以使用一個(gè)索引來(lái)滿足ORDER BY 子句的排序，而不需要額外的排序，但是需要滿足以下幾個(gè)條件：

（1）WHERE 條件和OREDR BY 使用相同的索引：即key_part1與key_part2是復(fù)合索引，where中使用復(fù)合索引中的key_part1

  1. SELECT*FROM user WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; 

（2）而且ORDER BY順序和索引順序相同：

  1. SELECT*FROM user ORDER BY key_part1, key_part2; 

（3）并且要么都是升序要么都是降序：

  1. SELECT*FROM user ORDER BY key_part1 DESC, key_part2 DESC; 

但以下幾種情況則不使用索引：

（1）ORDER BY中混合ASC和DESC： 

  1. SELECT*FROM user ORDER BY key_part1 DESC, key_part2 ASC; 

（2）查詢行的關(guān)鍵字與ORDER BY所使用的不相同，即WHERE 后的字段與ORDER BY 后的字段是不一樣的 

  1. SELECT*FROM user WHERE key2 = ‘xxx’ ORDER BY key1; 

（3）ORDER BY對(duì)不同的關(guān)鍵字使用，即ORDER BY后的關(guān)鍵字不相同 

  1. SELECT*FROM user ORDER BY key1, key2; 

5、OR的優(yōu)化

當(dāng)MySQL使用OR查詢時(shí)，如果要利用索引的話，必須每個(gè)條件列都使獨(dú)立索引，而不是復(fù)合索引（多列索引），才能保證使用到查詢的時(shí)候使用到索引。

比如我們新建一張用戶信息表user_info 

  1. mysql> select*from user_info;  
  2. +---------+--------+----------+-----------+  
  3. | user_id | idcard | name     | address    |  
  4. +---------+--------+----------+-----------+  
  5. |       1 | 111111 | Zhangsan | Kunming   |  
  6. |       2 | 222222 | Lisi     | Beijing   |  
  7. |       3 | 333333 | Wangwu   | Shanghai  |  
  8. |       4 | 444444 | Lijian   | Guangzhou |  
  9. +---------+--------+----------+-----------+  
  10. 4 rows in set 

之后創(chuàng)建ind_name_id(user_id, name)復(fù)合索引、id_index(id_index)獨(dú)立索引，idcard主鍵索引三個(gè)索引。 

  1. mysql> show index from user_info;  
  2. +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
  3. | Table     | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 
  4. +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
  5. | user_info |          0 | PRIMARY     |            1 | idcard      | A         |           4 | NULL     | NULL   |      | BTREE      |         |               | 
  6. | user_info |          1 | ind_name_id |            1 | user_id     | A         |           4 | NULL     | NULL   |      | BTREE      |         |               | 
  7. | user_info |          1 | ind_name_id |            2 | name        | A         |           4 | NULL     | NULL   | YES  | BTREE      |         |               | 
  8. | user_info |          1 | id_index    |            1 | user_id     | A         |           4 | NULL     | NULL   |      | BTREE      |         |               | 
  9. +-----------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 
  10. 4 rows in set 

測(cè)試一：OR連接兩個(gè)有單獨(dú)索引的字段，整個(gè)SQL查詢才會(huì)用到索引(index_merge)，并且我們知道OR實(shí)際上是把每個(gè)結(jié)果最后UNION一起的。 

  1. mysql> explain select*from user_info where user_id=1 or idcard='222222';  
  2. +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 
  3. | id | select_type | table     | partitions | type        | possible_keys                | key                 | key_len | ref  | rows | filtered | Extra                                              | 
  4. +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 
  5. |  1 | SIMPLE      | user_info | NULL       | index_merge | PRIMARY,ind_name_id,id_index | ind_name_id,PRIMARY | 4,62    | NULL |    2 |      100 | Using sort_union(ind_name_id,PRIMARY); Using where | 
  6. +----+-------------+-----------+------------+-------------+------------------------------+---------------------+---------+------+------+----------+----------------------------------------------------+ 
  7. 1 row in set 

 測(cè)試二：OR使用復(fù)合索引的字段name，與沒(méi)有索引的address，整個(gè)SQL都是ALL全表掃描的 

  1. mysql> explain select*from user_info where name='Zhangsan' or address='Beijing';  
  2. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    43.75 | Using where |  
  6. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

交換OR位置并且使用另外的復(fù)合索引的列，也是ALL全表掃描： 

  1. mysql> explain select*from user_info where address='Beijing' or user_id=1;  
  2. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+ 
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |    43.75 | Using where |  
  6. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

6、優(yōu)化嵌套查詢

使用嵌套查詢有時(shí)候可以使用更有效的JOIN連接代替，這是因?yàn)镸ySQL中不需要在內(nèi)存中創(chuàng)建臨時(shí)表完成SELECT子查詢與主查詢兩部分查詢工作。但是并不是所有的時(shí)候都成立，最好是在on關(guān)鍵字后面的列有索引的話，效果會(huì)更好！

比如在表major中major_id是有索引的： 

  1. select * from student u left join major m on u.major_id=m.major_id where m.major_id is null; 

而通過(guò)嵌套查詢時(shí)，在內(nèi)存中創(chuàng)建臨時(shí)表完成SELECT子查詢與主查詢兩部分查詢工作，會(huì)有一定的消耗 

  1. select * from student u where major_id not in (select major_id from major); 

7、使用SQL提示

SQL提示（SQL HINT）是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，就是往SQL語(yǔ)句中加入一些人為的提示來(lái)達(dá)到優(yōu)化目的。下面是一些常用的SQL提示：

（1）USE INDEX：使用USE INDEX是希望MySQL去參考索引列表，就可以讓MySQL不需要考慮其他可用索引，其實(shí)也就是possible_keys屬性下參考的索引值 

  1. mysql> explain select* from user_info use index(id_index,ind_name_id) where user_id>0;  
  2. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |      100 | Using where |  
  6. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set  
  8. mysql> explain select* from user_info use index(id_index) where user_id>0;  
  9. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  10. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  11. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  12. |  1 | SIMPLE      | user_info | NULL       | ALL  | id_index      | NULL | NULL    | NULL |    4 |      100 | Using where |  
  13. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  14. 1 row in set 

（2）IGNORE INDEX忽略索引

我們使用user_id判斷，用不到其他索引時(shí)，可以忽略索引。即與USE INDEX相反，從possible_keys中減去不需要的索引，但是實(shí)際環(huán)境中很少使用。 

  1. mysql> explain select* from user_info ignore index(primary,ind_name_id,id_index) where user_id>0;  
  2. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |  
  6. +----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

（3）FORCE INDEX強(qiáng)制索引

比如where user_id > 0，但是user_id在表中都是大于0的，自然就會(huì)進(jìn)行ALL全表搜索，但是使用FORCE INDEX雖然執(zhí)行效率不是最高（where user_id > 0條件決定的）但MySQL還是使用索引。 

  1. mysql> explain select* from user_info where user_id>0;  
  2. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  3. | id | select_type | table     | partitions | type | possible_keys        | key  | key_len | ref  | rows | filtered | Extra       |  
  4. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | ALL  | ind_name_id,id_index | NULL | NULL    | NULL |    4 |      100 | Using where |  
  6. +----+-------------+-----------+------------+------+----------------------+------+---------+------+------+----------+-------------+  
  7. 1 row in set 

之后強(qiáng)制使用獨(dú)立索引id_index(user_id)： 

  1. mysql> explain select* from user_info force index(id_index) where user_id>0;  
  2. +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+  
  3. | id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |  
  4. +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+  
  5. |  1 | SIMPLE      | user_info | NULL       | range | id_index      | id_index | 4       | NULL |    4 |      100 | Using index condition |  
  6. +----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+  
  7. 1 row in set 

總結(jié)

（1）很多時(shí)候數(shù)據(jù)庫(kù)的性能是由于不合適（是指效率不高，可能會(huì)導(dǎo)致鎖表等）的SQL語(yǔ)句造成，本篇博文只是介紹簡(jiǎn)單的SQL優(yōu)化

（2）其中有些優(yōu)化在真正開(kāi)發(fā)中是用不到的，但是一旦出問(wèn)題性能下降的時(shí)候需要去一一分析。 

 

責(zé)任編輯：龐桂玉 來(lái)源： Java知音
MySQL優(yōu)化數(shù)據(jù)庫(kù)
相關(guān)推薦

2022-03-27 20:52:41

Chrome插件開(kāi)發(fā)

2021-09-04 07:56:44

Pythonos模塊

2015-09-08 14:42:17

Android性能優(yōu)化

2021-08-30 10:25:48

JavaScript進(jìn)階操作前端

2021-10-09 07:10:31

JavaScript對(duì)象Python

2021-08-26 10:25:04

JavaScript進(jìn)階操作 前端

2023-06-01 07:48:03

Solidjsx??React?

2023-03-17 10:03:51

服務(wù)器編輯器vscode

2021-08-11 21:46:47

MySQL索引join

2012-03-22 10:26:17

開(kāi)源云計(jì)算

2020-08-12 15:00:55

MYSQL優(yōu)化數(shù)據(jù)庫(kù)

2014-12-17 14:41:21

云計(jì)算互聯(lián)網(wǎng)混合云

2017-04-20 14:58:16

2022-01-11 06:53:23

IPO開(kāi)發(fā)容器

2022-03-22 07:38:00

SQL語(yǔ)句MySQL

2022-06-20 05:40:25

數(shù)據(jù)庫(kù)MySQL查詢

2015-04-13 17:39:11

移動(dòng)IM開(kāi)發(fā)

2017-06-27 14:48:51

開(kāi)發(fā)設(shè)計(jì)程序員

2021-07-05 10:00:31

JavaStack基礎(chǔ)

2014-03-27 11:34:09

C#優(yōu)化性能優(yōu)化
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)