下文給大家?guī)碛嘘PMySQL高性能SQL語句的編寫和優(yōu)化內(nèi)容，相信大家一定看過類似的文章。我們給大家?guī)淼挠泻尾煌?？一起來看看正文部分吧，相信看完MySQL高性能SQL語句的編寫和優(yōu)化你一定會有所收獲。

員工經(jīng)過長期磨合與沉淀，具備了協(xié)作精神，得以通過團隊的力量開發(fā)出優(yōu)質(zhì)的產(chǎn)品。創(chuàng)新互聯(lián)堅持“專注、創(chuàng)新、易用”的產(chǎn)品理念，因為“專注所以專業(yè)、創(chuàng)新互聯(lián)網(wǎng)站所以易用所以簡單”。公司專注于為企業(yè)提供成都網(wǎng)站建設、做網(wǎng)站、微信公眾號開發(fā)、電商網(wǎng)站開發(fā)，小程序設計，軟件按需策劃設計等一站式互聯(lián)網(wǎng)企業(yè)服務。

環(huán)境與數(shù)據(jù)庫

因為個人愛好，我的系統(tǒng)是Linux mint19；數(shù)據(jù)庫版本是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.使用系統(tǒng)預定義函數(shù)查看
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是一種標準化的查詢語言，拋開各個數(shù)據(jù)庫廠商實現(xiàn)的不同，原理上都是相同的，但根據(jù)廠商實現(xiàn)的不同，獲取相同結果集會有一些不一樣的寫法，比如說MySQL實現(xiàn)分頁使用的是limit，而oracle是rownum,SQLServer是用在2005版本之前用top,后面使用row_number()。因為SQL的優(yōu)化肯定是要針對廠商實現(xiàn)進行，我這里選MySQL,我使用最多最熟練，其次是oracle，而SQLServer除了在大學用，未曾在工作中使用。MySQL的流行以及為啥流行就不用我多說了。

數(shù)據(jù)庫基礎鞏固

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

數(shù)據(jù)庫語言分類

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

DQL(Data Query Language)

數(shù)據(jù)庫查詢語言：這個就不用我多說了吧。

DDL（Data Definition Language）

數(shù)據(jù)庫定義語言：關系模式的定義、修改、刪除。

DML（Data Manipulation Language）

數(shù)據(jù)庫操作語言：元祖數(shù)據(jù)的插入、修改、刪除。

DCL（Data Control Language）

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

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

基礎概念

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

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

集合與代數(shù)操作

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

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

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

范式

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

DQL

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

DDL

DDL是關系定義、修改、刪除的語言，包括創(chuàng)建數(shù)據(jù)庫、表、索引、觸發(fā)器、存儲過程、函數(shù)。
關鍵字:create

DML

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

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

聚焦SQL優(yōu)化

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

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

搜索列加上索引

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

切忌使用*

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

# 統(tǒng)計條數(shù)，他們都是第一次執(zhí)行，沒有緩存過
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()函數(shù)有關系，那么我們直接查詢整個表看看。
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)

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

join（left,inner,right）關聯(lián)

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

where 查詢字段優(yōu)化

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

like切忌使用‘%%’

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

子查詢結果集優(yōu)先過濾

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

in與exists合理使用

in是使用的是內(nèi)外表的hash連接，而exists的使用的是loop循環(huán)遍歷字表。
那么這里就存在一個集合大小影響性能的問題了，如果兩個集合差不多大，其實兩個都差不多，則內(nèi)表大的用exists，內(nèi)表小的用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 那么內(nèi)外表都進行全表掃描，沒有用到索引；而not exists 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists 都比not in 要快。

避免使用OR操作符

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

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