除了增刪改查你對(duì)MySQL還了解多少？

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

遠(yuǎ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ù)庫(kù)名.表名 To 用戶名@ip地址；`

# 創(chuàng)建用戶

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

# 這樣root@192.168.11.%這個(gè)網(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就登錄了，不需要先啟動(dòng)服務(wù)端，再啟動(dòng)客戶端這么繁瑣，但凡涉及到服務(wù)端和客戶端就會(huì)涉及到通信問題，客戶端進(jìn)程向服務(wù)器進(jìn)程發(fā)送請(qǐng)求并得到回復(fù)的過程本質(zhì)上是一個(gè)進(jìn)程間通信的過程！那么MySQL的通信方式？？是什么？？？

TCP/IP#

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

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

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

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

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

mysqld-P3307

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

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ù)：

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

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

注意

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

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

Unix域套接字文件#

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

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

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

# 修改默認(rèn)路徑，啟動(dòng)時(shí)指定路徑

mysqld --socket=/tmp/a.txt

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

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

查詢優(yōu)化

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

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

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

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

多種情況說明如下：

索引列參與計(jì)算#

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

SELECT`sname`FROM`t_stu`WHERE`age`=20;   -- 會(huì)使用索引

 SELECT`sname`FROM`t_stu`WHERE`age`+10=30;  -- 不會(huì)使用索引！！因?yàn)樗兴饕袇⑴c了計(jì)算

 SELECT`sname`FROM`t_stu`WHERE`age`=30-10;  -- 會(huì)使用索引

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

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

SELECT`sname`FROM`stu`WHEREconcat(`sname`,'abc') ='Jaskeyabc';  -- 不會(huì)使用索引,因?yàn)槭褂昧撕瘮?shù)運(yùn)算,原理與上面相同

SELECT`sname`FROM`stu`WHERE`sname`=concat('Jaskey','abc');   -- 會(huì)使用索引

索引列使用了Like %XXX#

SELECT*FROM`houdunwang`WHERE`uname`LIKE'前綴%'-- 走索引

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

所以當(dāng)需要搜索email列中.com結(jié)尾的字符串而email上希望走索引時(shí)候,可以考慮數(shù)據(jù)庫(kù)存儲(chǔ)一個(gè)反向的內(nèi)容reverse_email

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

注：以上如果你使用REVERSE(email) = REVERSE(’%.com’)，一樣得不到你想要的結(jié)果，因?yàn)槟阍谒饕衑mail列上使用了函數(shù)，MySQL不會(huì)使用該列索引 同樣的，索引列上使用正則表達(dá)式也不會(huì)走索引。

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

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

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

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

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

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

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,即使其中有條件帶索引也不會(huì)使用。換言之,就是要求使用的所有字段,都必須建立索引

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

select *fromdept where dname='jaskey'union

select *fromdept where loc='bj'union

select *fromdept where deptno=45

ORDER BY 操作#

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

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

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

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

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

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

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

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

注意：如果columnX對(duì)應(yīng)多個(gè)值，如下面語句就無法利用索引來實(shí)現(xiàn)order by的優(yōu)化 SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

3、WHERE+ 多個(gè)字段ORDER BY#

SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; 建立索引(uid,x,y)實(shí)現(xiàn)order by的優(yōu)化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By不能使用索引來優(yōu)化排序的情況 * 對(duì)不同的索引鍵做 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;

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

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

* 如果在WHERE和ORDER BY的欄位上應(yīng)用表達(dá)式(函數(shù))時(shí)，則無法利用索引來實(shí)現(xiàn)order by的優(yōu)化 SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特別提示:#

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

Offset Limit 操作#

存在性能問題的方式

SELECT*FROMmyTableORDERBY`id`LIMIT1000000,30

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

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

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

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

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

探究#

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

select*fromtable_name limit10000,10

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

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

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

第一次優(yōu)化#

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

select*fromtable_namewhere(id>=10000) limit10

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

這個(gè)優(yōu)化思路就是告訴數(shù)據(jù)庫(kù)：「你別數(shù)了，我告訴你，第10001條數(shù)據(jù)是這樣的，你直接去拿吧?！?/p>

但是?。。∧憧赡芤呀?jīng)注意到了，這個(gè)查詢太簡(jiǎn)單了，沒有任何的附加查詢條件，如果我需要一些額外的查詢條件，比如我只要某個(gè)用戶的數(shù)據(jù) ，這種方法就行不通了。

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

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

第二次優(yōu)化#

說起數(shù)據(jù)庫(kù)查詢優(yōu)化，第一時(shí)間想到的就是索引，所以便有了第二次優(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ù)）：第一條花費(fèi)平均耗時(shí)約為第二條的 1/3 左右。

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

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

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

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

第三次優(yōu)化#

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

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)化效果往往更為顯著。

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

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