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

MySQL授權(quán)遠程連接

遠程連接

授權(quán)

常見權(quán)限表

創(chuàng)建用戶、授權(quán)#

? 創(chuàng)建用戶格式：create user 用戶名@ip地址 identified by '密碼';

? 授權(quán)：grant all on *.* To 用戶名@'ip地址';

  `grant select,create on 數(shù)據(jù)庫名.表名 To 用戶名@ip地址；`

# 創(chuàng)建用戶

createuserroot@'192.168.11.%'identified by'123456';

# 這樣root@192.168.11.%這個網(wǎng)段的用戶可以登錄



createuserhans@'192.168.11.161'identifiedby'123456';

# 這樣只允許hans用戶登錄



createuserli@'%'identifiedby'123456';

# 所有l(wèi)i用戶都可登錄





# 刪除用戶

dropuserroot@'192.168.11.%';

# 授權(quán)

grantallon*.*Tohans@'192.168.11.161';

grantselect,createonoldboy_test.*Tohans@'192.168.11.161';



# 查看授權(quán)

showgrantsforhans@'192.168.11.161';

# 刷新權(quán)限表

flush privileges;

客戶端與服務(wù)器連接的過程

我們知道MySQL在配置好環(huán)境變量后，直接mysql -p xx -u xx -h xx就登錄了，不需要先啟動服務(wù)端，再啟動客戶端這么繁瑣，但凡涉及到服務(wù)端和客戶端就會涉及到通信問題，客戶端進程向服務(wù)器進程發(fā)送請求并得到回復的過程本質(zhì)上是一個進程間通信的過程！那么MySQL的通信方式？？是什么？？？

TCP/IP#

在我們實際使用數(shù)據(jù)庫的過程中，大概率服務(wù)器和客戶端不會在一臺機器上，那么他們之間就得通過網(wǎng)絡(luò)來通信，MySQL采用TCP作為服務(wù)器和客戶端之間的網(wǎng)絡(luò)通信協(xié)議。我們知道MySQL登錄的命令可以攜帶多個參數(shù)，在我們有權(quán)限遠程登錄的情況下，通過-P和-h來指定端口和域名；

ps：協(xié)議不清楚可以百度或者看一下俺的博客哈哈哈

網(wǎng)絡(luò)編程理論 - HammerZe - 博客園 (cnblogs.com)

我們都知道MySQL服務(wù)器的默認端口為3306，之后就在這個端口號上等待客戶端進程進行連接（MySQL服務(wù)器會默認監(jiān)聽3306端口）

如果端口被占用了，可以在啟動服務(wù)器的時候通過-P指定參數(shù)：

mysqld-P3307

禁止各客戶端使用TCP/IP網(wǎng)絡(luò)進行通信：

mysqld --skip-networking

mysql -h127.0.0.1-uroot -p

Enter password:



ERROR2003(HY000): Can't connect to MySQL server on '127.0.0.1' (61)

命名管道和共享內(nèi)存#

如果是Windows用戶，客戶端和服務(wù)端連接可以使用：

?命名管道

?共享內(nèi)存

使用這兩種方式連接需要添加參數(shù)：

? 使用命名管道來進行進程間通信： 需要在啟動服務(wù)器程序的命令中加上--enable-named-pipe參數(shù)，然后在啟動客戶端程序的命令中加入--pipe或者--protocol=pipe參數(shù)；

? 使用共享內(nèi)存來進行進程間通信： 需要在啟動服務(wù)器程序的命令中加上--shared-memory參數(shù)，在成功啟動服務(wù)器后，共享內(nèi)存便成為本地客戶端程序的默認連接方式，不過我們也可以在啟動客戶端程序的命令中加入--protocol=memory參數(shù)來顯式的指定使用共享內(nèi)存進行通信；

注意

?不過需要注意的是，使用共享內(nèi)存的方式進行通信的服務(wù)器進程和客戶端進程必須在同一臺Windows主機中

?命名管道和共享內(nèi)存是Windows操作系統(tǒng)中的兩種進程間通信方式

Unix域套接字文件#

使用此連接方式的前提是服務(wù)器和客戶端進程都在同一類Unix的機器上，我們才可以使用Unix域套接字文件來進行通信；

啟動客戶端程序的時候指定的主機名為localhost，或者指定了--protocol=socket的啟動參數(shù)，那服務(wù)器程序和客戶端程序之間就可以通過Unix域套接字文件來進行通信了；

MySQL服務(wù)器程序默認監(jiān)聽的Unix域套接字文件路徑為/tmp/mysql.sock，客戶端程序也默認連接到這個Unix域套接字文件；

# 修改默認路徑，啟動時指定路徑

mysqld --socket=/tmp/a.txt

然后登錄的時候服務(wù)器監(jiān)聽的就是tmp下的a.txt文件，如何登錄？

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

查詢優(yōu)化

因為我們寫的MySQL語句執(zhí)行起來效率可能并不是很高，MySQL的優(yōu)化程序會對我們的語句做一些優(yōu)化，如外連接轉(zhuǎn)換為內(nèi)連接、表達式簡化、子查詢等，最后優(yōu)化的結(jié)果就是生成一個執(zhí)行計劃，這個執(zhí)行計劃表明了應(yīng)該使用哪些索引進行查詢，表之間的連接順序是啥樣的，我們可以通過EXPLAIN語句來設(shè)置執(zhí)行計劃；

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

轉(zhuǎn)自：https://cloud.tencent.com/developer/article/1666887

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

多種情況說明如下：

索引列參與計算#

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

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;  -- 會使用索引

索引列使用了函數(shù)#

同樣的道理，索引列使用了函數(shù)，一樣會導致相同的后果

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

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結(jié)尾的字符串而email上希望走索引時候,可以考慮數(shù)據(jù)庫存儲一個反向的內(nèi)容reverse_email

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

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

字符串列與數(shù)字直接比較#

這是一個坑，假設(shè)有一張表,里面的a列是一個字符char類型,且a上建立了索引,你用它與數(shù)字類型做比較判斷的話：

CREATETABLE`t1`(`a`char(10));

SELECT*FROM`t1`WHERE`a`='1'-- 走索引

SELECT*FROM`t2`WHERE`a`=1-- 字符串和數(shù)字比較，不走索引！

但是如果那個表那個列是一個數(shù)字類型，拿來和字符類型的做比較，則不會影響到使用索引

CREATETABLE`t2`(`b`int);

SELECT*FROM`t2`WHERE`b`='1'-- 雖然b是數(shù)字類型，和'1'比較依然走索引

但是，無論如何，這種額外的隱式類型轉(zhuǎn)換都是開銷，而且由于有字符和數(shù)字比就不走索引的情況，故建議避免一切隱式類型轉(zhuǎn)換

盡量避免 OR 操作#

select *fromdept where dname='jaskey'or loc='bj'or deptno=45

--如果條件中有or,即使其中有條件帶索引也不會使用。換言之,就是要求使用的所有字段,都必須建立索引

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

select *fromdept where dname='jaskey'union

select *fromdept where loc='bj'union

select *fromdept where deptno=45

ORDER BY 操作#

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

MySQL索引通常是被用于提高WHERE條件的數(shù)據(jù)行匹配或者執(zhí)行聯(lián)結(jié)操作時匹配其它表的數(shù)據(jù)行的搜索速度。

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

通過索引優(yōu)化來實現(xiàn)MySQL的ORDER BY語句優(yōu)化：

1、ORDER BY的索引優(yōu)化。如果一個SQL語句形如：#

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]這個欄位上建立索引就可以實現(xiàn)利用索引進行order by 優(yōu)化。

2、WHERE + ORDER BY的索引優(yōu)化，形如：#

SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]; 建立一個聯(lián)合索引(columnX,sort)來實現(xiàn)order by 優(yōu)化。

注意：如果columnX對應(yīng)多個值，如下面語句就無法利用索引來實現(xiàn)order by的優(yōu)化 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)實現(xiàn)order by的優(yōu)化,比建立(x,y,uid)索引效果要好得多。

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

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

* 同時使用了 ASC 和 DESC：(key_part1,key_part2建立聯(lián)合索引) 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的欄位上應(yīng)用表達式(函數(shù))時，則無法利用索引來實現(xiàn)order by的優(yōu)化 SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特別提示:#

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

Offset Limit 操作#

存在性能問題的方式

SELECT*FROMmyTableORDERBY`id`LIMIT1000000,30

寫出這樣SQL語句的人肯定心里是這樣想的：MySQL數(shù)據(jù)庫會直接定位到符合條件的第1000000位，然后再取30條數(shù)據(jù)。 然而，實際上MySQL不是這樣工作的。

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

mysql 的 limit 給分頁帶來了極大的方便，但數(shù)據(jù)偏移量一大，limit 的性能就急劇下降。

以下是兩條查詢語句，都是取10條數(shù)據(jù)，但性能就相去甚遠

所以不能簡單的使用 limit 語句實現(xiàn)數(shù)據(jù)分頁。

探究#

為什么 offset 偏大之后 limit 查找會變慢？這需要了解 limit 操作是如何運作的，以下面這句查詢?yōu)槔?/p>

select*fromtable_name limit10000,10

這句 SQL 的執(zhí)行邏輯是 1.從數(shù)據(jù)表中讀取第N條數(shù)據(jù)添加到數(shù)據(jù)集中 2.重復第一步直到 N = 10000 + 10 3.根據(jù) offset 拋棄前面 10000 條數(shù) 4.返回剩余的 10 條數(shù)據(jù)

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

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

第一次優(yōu)化#

根據(jù)數(shù)據(jù)庫這種查找的特性，就有了一種想當然的方法，利用自增索引（假設(shè)為id）：

select*fromtable_namewhere(id>=10000) limit10

由于普通搜索是全表搜索，適當?shù)奶砑?WHERE 條件就能把搜索從全表搜索轉(zhuǎn)化為范圍搜索，大大縮小搜索的范圍，從而提高搜索效率。

這個優(yōu)化思路就是告訴數(shù)據(jù)庫：「你別數(shù)了，我告訴你，第10001條數(shù)據(jù)是這樣的，你直接去拿吧。」

但是！！！你可能已經(jīng)注意到了，這個查詢太簡單了，沒有任何的附加查詢條件，如果我需要一些額外的查詢條件，比如我只要某個用戶的數(shù)據(jù) ，這種方法就行不通了。

可以見到這種思路是有局限性的，首先必須要有自增索引列，而且數(shù)據(jù)在邏輯上必須是連續(xù)的，其次，你還必須知道特征值。

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

第二次優(yōu)化#

說起數(shù)據(jù)庫查詢優(yōu)化，第一時間想到的就是索引，所以便有了第二次優(yōu)化：先查找出需要數(shù)據(jù)的索引列（假設(shè)為 id），再通過索引列查找出需要的數(shù)據(jù)。

Select*Fromtable_nameWhereidin(SelectidFromtable_namewhere(user=xxx )) limit10000,10;

select*fromtable_namewhere(user=xxx ) limit10000,10

相比較結(jié)果是（500w條數(shù)據(jù)）：第一條花費平均耗時約為第二條的 1/3 左右。

同樣是較大的 offset，第一條的查詢更為復雜，為什么性能反而得到了提升？

這涉及到 mysql 主索引的數(shù)據(jù)結(jié)構(gòu) b+Tree ，這里不展開，基本原理就是：

? 子查詢只用到了索引列，沒有取實際的數(shù)據(jù)，所以不涉及到磁盤IO，所以即使是比較大的 offset 查詢速度也不會太差。

? 利用子查詢的方式，把原來的基于 user 的搜索轉(zhuǎn)化為基于主鍵（id）的搜索，主查詢因為已經(jīng)獲得了準確的索引值，所以查詢過程也相對較快。

第三次優(yōu)化#

在數(shù)據(jù)量大的時候 in 操作的效率就不怎么樣了，我們需要把 in 操作替換掉，使用 join 就是一個不錯的選擇

select * from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)

至此 limit 在查詢上的優(yōu)化就告一段落了。如果還有更好的優(yōu)化方式，歡迎留言告知

最終優(yōu)化#

技術(shù)上的優(yōu)化始終是有天花板的，業(yè)務(wù)的優(yōu)化效果往往更為顯著。

比如在本例中，因為數(shù)據(jù)的時效性，我們最終決定，只提供最近15天內(nèi)的操作日志，在這個前提下，偏移值 offset 基本不會超過一萬，這樣一來，即使是沒有經(jīng)過任何優(yōu)化的 sql，其執(zhí)行效率也變得可以接受了，所以優(yōu)化不能局限于技術(shù)層面，有時候?qū)π枨筮M行一下調(diào)整，可能會達到意想不到的效果

鏈接：https://www.cnblogs.com/48xz/p/16046540.html