除了增刪改查你對MySQL還了解多少？

MySQL授權遠端連線

👉遠端連線

👉授權

👉常見許可權表

建立使用者、授權#

• 建立使用者格式：create user 使用者名稱@ip地址 identified by '密碼';
• 授權：grant all on *.* To 使用者名稱@'ip地址';

`grant select,create on 資料庫名.表名 To 使用者名稱@ip地址；`

# 建立使用者createuser root@'192.168.11.%' identified  by'123456';# 這樣root@192.168.11.% 這個網段的使用者可以登入createuser hans@'192.168.11.161' identified by'123456';# 這樣只允許hans使用者登入createuser li@'%' identified by'123456';# 所有li使用者都可登入# 刪除使用者dropuser root@'192.168.11.%';

# 授權grantallon*.*To hans@'192.168.11.161';grantselect,createon oldboy_test.*To hans@'192.168.11.161';# 檢視授權show grants for hans@'192.168.11.161';# 重新整理許可權表flush privileges;

客戶端與伺服器連線的過程

我們知道MySQL在配置好環境變數後，直接mysql -p xx -u xx -h xx就登入了，不需要先啟動服務端，再啟動客戶端這麼繁瑣，但凡涉及到服務端和客戶端就會涉及到通訊問題，客戶端程序向伺服器程序傳送請求並得到回覆的過程本質上是一個程序間通訊的過程！那麼MySQL的通訊方式？？是什麼？？？

TCP/IP#

在我們實際使用資料庫的過程中，大機率伺服器和客戶端不會在一臺機器上，那麼他們之間就得透過網路來通訊，MySQL採用TCP作為伺服器和客戶端之間的網路通訊協議。我們知道MySQL登入的命令可以攜帶多個引數，在我們有許可權遠端登入的情況下，透過-P和-h來指定埠和域名；

ps：協議不清楚可以百度或者看一下俺的部落格哈哈哈

👉網路程式設計理論 – HammerZe – 部落格園 (cnblogs.com)

我們都知道MySQL伺服器的預設埠為3306，之後就在這個埠號上等待客戶端程序進行連線（MySQL伺服器會預設監聽3306埠）

如果埠被佔用了，可以在啟動伺服器的時候透過-P指定引數：

mysqld -P3307

禁止各客戶端使用TCP/IP網路進行通訊：

mysqld --skip-networking

mysql -h127.0.0.1 -uroot -pEnter password:ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)

命名管道和共享記憶體#

如果是Windows使用者，客戶端和服務端連線可以使用：

• 命名管道
• 共享記憶體

使用這兩種方式連線需要新增引數：

• 使用命名管道來進行程序間通訊：需要在啟動伺服器程式的命令中加上--enable-named-pipe引數，然後在啟動客戶端程式的命令中加入--pipe或者--protocol=pipe引數；
• 使用共享記憶體來進行程序間通訊：需要在啟動伺服器程式的命令中加上--shared-memory引數，在成功啟動伺服器後，共享記憶體便成為本地客戶端程式的預設連線方式，不過我們也可以在啟動客戶端程式的命令中加入--protocol=memory引數來顯式的指定使用共享記憶體進行通訊；

注意

• 不過需要注意的是，使用共享記憶體的方式進行通訊的伺服器程序和客戶端程序必須在同一臺Windows主機中
• 命名管道和共享記憶體是Windows作業系統中的兩種程序間通訊方式

Unix域套接字檔案#

使用此連線方式的前提是伺服器和客戶端程序都在同一類Unix的機器上，我們才可以使用Unix域套接字檔案來進行通訊；

啟動客戶端程式的時候指定的主機名為localhost，或者指定了--protocol=socket的啟動引數，那伺服器程式和客戶端程式之間就可以透過Unix域套接字檔案來進行通訊了；

MySQL伺服器程式預設監聽的Unix域套接字檔案路徑為/tmp/mysql.sock，客戶端程式也預設連線到這個Unix域套接字檔案；

# 修改預設路徑，啟動時指定路徑mysqld --socket=/tmp/a.txt

然後登入的時候伺服器監聽的就是tmp下的a.txt檔案，如何登入？

mysql -hlocalhost -uroot --socket=/tmp/a.txt -p1234

查詢最佳化

因為我們寫的MySQL語句執行起來效率可能並不是很高，MySQL的最佳化程式會對我們的語句做一些最佳化，如外連線轉換為內連線、表示式簡化、子查詢等，最後最佳化的結果就是生成一個執行計劃，這個執行計劃表明了應該使用哪些索引進行查詢，表之間的連線順序是啥樣的，我們可以透過EXPLAIN語句來設定執行計劃；

MySQL中走與不走索引的情況彙總

轉自：https://cloud.tencent.com/developer/article/1666887

在MySQL中，並不是你建立了索引，並且你在SQL中使用到了該列，MySQL就肯定會使用到那些索引的，有一些情況很可能在你不知不覺中，你就“成功的避開了”MySQL的所有索引；

多種情況說明如下：

索引列參與計算#

如果where條件中age列中使用了計算，則不會使用該索引。如果需要計算，千萬不要計算到索引列，想方設法讓其計算到表示式的另一邊去；

SELECT `sname` FROM `t_stu` WHERE `age`=20;       -- 會使用索引SELECT `sname` FROM `t_stu` WHERE `age`+10=30;    -- 不會使用索引！！因為所有索引列參與了計算SELECT `sname` FROM `t_stu` WHERE `age`=30-10;    -- 會使用索引

索引列使用了函式#

同樣的道理，索引列使用了函式，一樣會導致相同的後果

SELECT`sname`FROM`stu`WHEREconcat(`sname`,'abc') ='Jaskeyabc';   -- 不會使用索引,因為使用了函式運算,原理與上面相同SELECT`sname`FROM`stu`WHERE`sname`=concat('Jaskey','abc');      -- 會使用索引

索引列使用了Like %XXX#

SELECT * FROM`houdunwang`WHERE`uname`LIKE'字首%' -- 走索引SELECT * FROM`houdunwang`WHERE`uname`LIKE'%字尾' -- 掃描全表，不走索引

所以當需要搜尋email列中.com結尾的字串而email上希望走索引時候,可以考慮資料庫儲存一個反向的內容reverse_email

SELECT * FROM`table`WHERE`reverse_email`LIKEREVERSE('%.com'); -- 走索引

　　注：以上如果你使用REVERSE(email) = REVERSE(’%.com’)，一樣得不到你想要的結果，因為你在索引列email列上使用了函式，MySQL不會使用該列索引同樣的，索引列上使用正則表示式也不會走索引。

字串列與數字直接比較#

這是一個坑，假設有一張表,裡面的a列是一個字元char型別,且a上建立了索引,你用它與數字型別做比較判斷的話：

CREATETABLE`t1` (`a`char(10));SELECT * FROM`t1`WHERE`a`='1' -- 走索引SELECT * FROM`t2`WHERE`a`=1 -- 字串和數字比較，不走索引！

但是如果那個表那個列是一個數字型別，拿來和字元型別的做比較，則不會影響到使用索引

CREATETABLE`t2` (`b` int);SELECT * FROM`t2`WHERE`b`='1' -- 雖然b是數字型別，和'1'比較依然走索引

但是，無論如何，這種額外的隱式型別轉換都是開銷，而且由於有字元和數字比就不走索引的情況，故建議避免一切隱式型別轉換

儘量避免 OR 操作#

select * from dept where dname='jaskey' or loc='bj' or deptno=45--如果條件中有or,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有欄位,都必須建立索引

所以除非每個列都建立了索引，否則不建議使用OR，在多列OR中，可以考慮用UNION 替換

select * from dept where dname='jaskey' unionselect * from dept where loc='bj' unionselect * from dept where deptno=45

ORDER BY 操作#

在ORDER BY操作中，排序的列同時也在WHERE中時，MYSQL將無法使用索引；

MySQL索引通常是被用於提高WHERE條件的資料行匹配或者執行聯結操作時匹配其它表的資料行的搜尋速度。

MySQL也能利用索引來快速地執行ORDER BY和GROUP BY語句的排序和分組操作。

透過索引最佳化來實現MySQL的ORDER BY語句最佳化：

1、ORDER BY的索引最佳化。如果一個SQL語句形如：#

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]這個欄位上建立索引就可以實現利用索引進行order by 最佳化。

2、WHERE + ORDER BY的索引最佳化，形如：#

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]; 建立一個聯合索引(columnX,sort)來實現order by 最佳化。

注意：如果columnX對應多個值，如下面語句就無法利用索引來實現order by的最佳化 SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多個欄位ORDER BY#

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; 建立索引(uid,x,y)實現order by的最佳化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By不能使用索引來最佳化排序的情況 * 對不同的索引鍵做 ORDER BY ：(key1,key2分別建立索引) SELECT * FROM t1 ORDER BY key1, key2;

* 在非連續的索引鍵部分上做 ORDER BY：(key_part1,key_part2建立聯合索引;key2建立索引) SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

* 同時使用了 ASC 和 DESC：(key_part1,key_part2建立聯合索引) SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

* 用於搜尋記錄的索引鍵和做 ORDER BY 的不是同一個：(key1,key2分別建立索引) SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

* 如果在WHERE和ORDER BY的欄位上應用表示式(函式)時，則無法利用索引來實現order by的最佳化 SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特別提示:#

1>mysql一次查詢只能使用一個索引。如果要對多個欄位使用索引，建立複合索引。 2>在ORDER BY操作中，MySQL只有在排序條件不是一個查詢條件表示式的情況下才使用索引。

Offset Limit 操作#

存在效能問題的方式

SELECT * FROM myTable ORDERBY`id`LIMIT1000000, 30

寫出這樣SQL語句的人肯定心裡是這樣想的：MySQL資料庫會直接定位到符合條件的第1000000位，然後再取30條資料。然而，實際上MySQL不是這樣工作的。

LIMIT 1000000, 30 的意思是：掃描滿足條件的1000030行，扔掉前面的1000000行，然後返回最後的30行。

mysql 的 limit 給分頁帶來了極大的方便，但資料偏移量一大，limit 的效能就急劇下降。

以下是兩條查詢語句，都是取10條資料，但效能就相去甚遠

所以不能簡單的使用 limit 語句實現資料分頁。

探究#

為什麼 offset 偏大之後 limit 查詢會變慢？這需要了解 limit 操作是如何運作的，以下面這句查詢為例：

select*from table_name limit 10000,10

這句 SQL 的執行邏輯是 1.從資料表中讀取第N條資料新增到資料集中 2.重複第一步直到 N = 10000 + 10 3.根據 offset 拋棄前面 10000 條數 4.返回剩餘的 10 條資料

顯然，導致這句 SQL 速度慢的問題出現在第二步！這前面的 10000 條資料完全對本次查詢沒有意義，但是卻佔據了絕大部分的查詢時間！如何解決？首先我們得了解為什麼資料庫為什麼會這樣查詢。

首先，資料庫的資料儲存並不是像我們想象中那樣，按表按順序儲存資料，一方面是因為計算機儲存本身就是隨機讀寫，另一方面是因為資料的操作有很大的隨機性，即使一開始資料的儲存是有序的，經過一系列的增刪查改之後也會變得凌亂不堪。所以資料庫的資料儲存是隨機的，使用 B+Tree， Hash 等方式組織索引。所以當你讓資料庫讀取第 10001 條資料的時候，資料庫就只能一條一條的去查去數。

第一次最佳化#

根據資料庫這種查詢的特性，就有了一種想當然的方法，利用自增索引（假設為id）：

select*from table_name where (id >=10000) limit 10

由於普通搜尋是全表搜尋，適當的新增 WHERE 條件就能把搜尋從全表搜尋轉化為範圍搜尋，大大縮小搜尋的範圍，從而提高搜尋效率。

這個最佳化思路就是告訴資料庫：「你別數了，我告訴你，第10001條資料是這樣的，你直接去拿吧。」

但是！！！你可能已經注意到了，這個查詢太簡單了，沒有任何的附加查詢條件，如果我需要一些額外的查詢條件，比如我只要某個使用者的資料，這種方法就行不通了。

可以見到這種思路是有侷限性的，首先必須要有自增索引列，而且資料在邏輯上必須是連續的，其次，你還必須知道特徵值。

如此苛刻的要求，在實際應用中是不可能滿足的。

第二次最佳化#

說起資料庫查詢最佳化，第一時間想到的就是索引，所以便有了第二次最佳化：先查找出需要資料的索引列（假設為 id），再透過索引列查找出需要的資料。

Select*From table_name Where id in (Select id From table_name where ( user= xxx )) limit 10000, 10;select*from table_name where( user= xxx ) limit 10000,10