MySQL高性能SQL語句的編寫和優化

下文給大家帶來有關MySQL高性能SQL語句的編寫和優化內容，相信大家一定看過類似的文章。我們給大家帶來的有何不同呢？一起來看看正文部分吧，相信看完MySQL高性能SQL語句的編寫和優化你一定會有所收獲。

環境與數據庫

因為個人愛好，我的系統是Linux mint19；數據庫版本是MySQL 5.7.23-0ubuntu0.18.04.1 (Ubuntu)。簡單介紹一下查看MySQL版本的方式

# 1.連接服務端，會直接輸出Server版本
ckmike@ckmikePC:~$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

#2.進入云服務器端后使用命令查看
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Connection id:      7
Current database:   
Current user:       root@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.23-0ubuntu0.18.04.1 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         2 days 13 hours 58 min 13 sec

Threads: 1  Questions: 53  Slow queries: 0  Opens: 112  Flush tables: 1  Open tables: 104  Queries per second avg: 0.000
--------------

mysql> 

#3.使用系統預定義函數查看
mysql> select version()
    -> ;
+-------------------------+
| version()               |
+-------------------------+
| 5.7.23-0ubuntu0.18.04.1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> 

#4.使用Linux下查看
ckmike@ckmikePC:~$ mysql --version
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

#5.Linux借助安裝工具查看版本

rpm -qa|grep mysql

當然如果是使用yum安裝的同樣可以用yum查看
yum list installed|grep mysql

備注：SQL是一種標準化的查詢語言，拋開各個數據庫廠商實現的不同，原理上都是相同的，但根據廠商實現的不同，獲取相同結果集會有一些不一樣的寫法，比如說MySQL實現分頁使用的是limit，而oracle是rownum,SQLServer是用在2005版本之前用top,后面使用row_number()。因為SQL的優化肯定是要針對廠商實現進行，我這里選MySQL,我使用最多最熟練，其次是oracle，而SQLServer除了在大學用，未曾在工作中使用。MySQL的流行以及為啥流行就不用我多說了。

數據庫基礎鞏固

在說SQL性能優化之前，我們首先需要先掌握數據庫基礎知識，因為任何SQL都是跑在數據庫引擎之上的，及好比講解Linux命令和Shell語言一樣，它們都是在某個內核或者某個引擎上的。

數據庫語言分類

根據操作對象和操作粒度的不同，把數據庫語言分為四大類：

DQL(Data Query Language)

數據庫查詢語言：這個就不用我多說了吧。

DDL（Data Definition Language）

數據庫定義語言：關系模式的定義、修改、刪除。

DML（Data Manipulation Language）

數據庫操作語言：元祖數據的插入、修改、刪除。

DCL（Data Control Language）

數據庫控制語言：權限的授權與回收，事務的回滾與提交等。
不管是MySQL，oracle，還是SQLServer以及其他想PSQL,紅狐貍都是由這些組成的一個產品。就類似安卓系統是一個標準，但安卓系統的各種發現版本各不相同，但核心都是依照安卓系統的標準來的，只是各個發行版本各自實現了一些不一樣的交互效果不一樣的特性，但本質都是一個標準。所以我們了解數據庫這套標準是非常重要的，不管產品如何變，但內部核心標準是不會變的，不同的只是因為實現不同而寫法不一，僅此而已。

說明：對于開發SQL的人來說，她關鍵在于DQL、DDL、DML。而優化也正好是這些部分。

基礎概念

關系：簡單來說就是一張二維表。
元祖：簡單來說就是二維表的一行，也就是一條記錄。
屬性：簡單說就是一個列，代表了一個屬性，比如說：男女（sex）

備注：這是個人簡單的理解，其實專業的定義在我看來是十分拗口的，所以我拷貝那些理論了，但請不清楚的一定要掌握，理解透徹，我的描述可能會誤導你。

集合與代數操作

數學課又要開始了，好，我這里就不講那些枯燥的數據理論。通俗的講集合就是一類具有共同屬性的元素組成，比如說人是一個集合概念：都有眼睛、鼻子、嘴巴、頭、軀干、腳等各種器官與組織組成，這是生物上的組成，還有比如說有：性別、姓名、年齡、身高、體重等屬性組成。而一個人就是人這個集合中的一個元素。在結合基礎概念是不是就是集合類似與一張二維表（關系），而具體的人則是一個元祖。

說道集合必然需要提到集合的運算操作：并、差、笛卡爾積、選擇、投影。
延續上面的人這個集合來簡單說并操作：
并運算：比如說中國人=（（北京人）,（天津人）,......(臺灣人),(香港人),(澳門人))等各個省份人的合計。當然在這里根據定義的不同其實可以包括一些移民的華人，但我這里是按照國籍，戶籍來分的。就是把所有集合的元素集合在一起就是并運算。

其他操作如果不懂的可以自行補習。篇幅關系不再贅述。

范式

這個概念可能很多人已經不記得了，但只要你涉及數據庫就一定會用到。所以這個的重要性是可想而知的。如果不知道的可到百度數據庫范式進行了解與補習。

DQL

DQL是我們工作中最為常見，使用最為頻繁的語言，比如說查看報表。
關鍵字:select

DDL

DDL是關系定義、修改、刪除的語言，包括創建數據庫、表、索引、觸發器、存儲過程、函數。
關鍵字:create

DML

DML是操作元祖的語言，包括元祖的刪除、插入、修改
關鍵字:insert、update、delete

說道這里很多基礎的知識點我們已經過了一遍，但是像一些分組、函數、排序呀我就不在這里說了，后面說道了我們再繼續詳細講解。

聚焦SQL優化

上面所寫都不是今天的重點，但確實是非常重要的基礎。SQL性能的優化其實就是針對DQL語句來的，不管你刪除、更新都是要先定位到元祖，所以我們常常說的SQL性能優化其實就是針對查詢語句的部分進行優化的。

那么影響SQL查詢性能的點有那些呢？我們接下來一個一個羅列。

搜索列加上索引

索引是提升搜索速度最直接最有效的方法，但切忌濫用索引，因為索引對更新，刪除有負面影響，同時索引也很吃資源。

切忌使用*

我剛接觸數據庫時，我的大學老師（人稱段龍王）就跟我們說：切忌使用select * from這樣的語句,特別是在子查詢中。
我們可以來做個實驗。我現在有一張表user，里面有大概一百多萬條數據。

# 統計條數，他們都是第一次執行，沒有緩存過
mysql> select count(id) from user;
+-----------+
| count(id) |
+-----------+
|   1050506 |
+-----------+
1 row in set (0.17 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  1050506 |
+----------+
1 row in set (0.19 sec)

mysql> 

感覺效果不明顯，有時候count(*)還比count(id)更快。我想這個應該跟count()函數有關系，那么我們直接查詢整個表看看。
mysql>select * from user;
| 1050504 | ckmike699997 |   18 | 17996        | 2019-05-11 |
| 1050505 | ckmike699998 |   18 | 11907        | 2019-05-11 |
| 1050506 | ckmike699999 |   18 | 17726        | 2019-05-11 |
| 1050507 | ckmike700000 |   18 | 1563         | 2019-05-11 |
+---------+--------------+------+--------------+------------+
1050506 rows in set (0.61 sec)
mysql>select name from user;
| ckmike699998 |
| ckmike699999 |
| ckmike700000 |
+--------------+
1050506 rows in set (0.35 sec)

之所以不要使用的原因是耗費資源，在網絡傳送上不必要的字段不用查出來，數據包能多小就多小，這是一個點。但如果在所有自動都要的情況下也盡量不要使用。如果在子查詢中就更是如此了，會成倍成倍的放大消耗時間。這樣的語句一定不要使用!

join（left,inner,right）關聯

通常關聯都是PK\FK進行關聯，所以關聯字段加上索引，且盡量不要使用函數。

where 查詢字段優化

1.查詢字段盡可能加上索引
2.where查詢字段時僅能不適用函數，因為會使得索引失效，進行全表掃描。
3.where把可以確定更小結果集、可以更快掃選結果集的查詢字段放在前面
4.盡量避免使用like
5.不可在“=”左邊進行函數、算術運算或其他表達式運算，可能無法正確使用索引。
6.盡可能不用!=、<> ，會導致全表掃描
7.如果是一個復合索引的字段，那么順序要按照索引定義屬性來，否則無法保證索引生效
8.如果使用了變量@這類的會導致索引失效

like切忌使用‘%%’

like '%%'會使得索引失效，從而進行全表掃描，這是非常致命的。而索引的有點完全無法發揮，但卻保留了索引的痛點。那么是不是就不能使用like呢？當然不是，我們可以使用like 'abdc%',這樣索引的功能生效，又可以使用like,這是一種折中的處理方式，使用like是低效，
但需求有時候是強硬的。

子查詢結果集優先過濾

限制子查詢結果集的大小是非常有效的，盡量把過濾條件放在子查詢中的where語句中，而不是放在外部過濾。

in與exists合理使用

in是使用的是內外表的hash連接，而exists的使用的是loop循環遍歷字表。
那么這里就存在一個集合大小影響性能的問題了，如果兩個集合差不多大，其實兩個都差不多，則內表大的用exists，內表小的用in:

表A（小表），表B（大表）1：

select * from A where cc in (select cc from B)
效率低，用到了A 表上cc 列的索引；

select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B 表上cc 列的索引。

not in 與not exists合理使用

查詢語句使用了not in 那么內外表都進行全表掃描，沒有用到索引；而not exists 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists 都比not in 要快。

避免使用OR操作符

如果一個字段有索引，一個字段沒有索引，將導致引擎放棄使用索引而進行全表掃描，這個時候我們可以拆分成兩個或者多個結果集進行union。

對于上文關于MySQL高性能SQL語句的編寫和優化，大家覺得是自己想要的嗎？如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。