SQL語(yǔ)句執(zhí)行順序

設(shè)置大小寫(xiě)不敏感

• 查看大小寫(xiě)是否敏感：show variables like '%lower_case_table_names%'; windows 系統(tǒng)默認(rèn)大小寫(xiě)不敏感，但是 linux 系統(tǒng)是大小寫(xiě)敏感的。
• 設(shè)置大小寫(xiě)不敏感：在 my.cnf 這個(gè)配置文件 [mysqld] 中加入 lower_case_table_names = 1 ，然后重啟服務(wù)器。

注意：在設(shè)置屬性為大小寫(xiě)不敏感前就需要將原來(lái)的數(shù)據(jù)庫(kù)和表轉(zhuǎn)換為小寫(xiě)，否則會(huì)找不到數(shù)據(jù)庫(kù)名。 ?

MySql 的用戶(hù)和權(quán)限管理

用戶(hù)管理： ?

-- 創(chuàng)建用戶(hù)
create user ahzoo identified by '123456';?

-- 查看用戶(hù)和權(quán)限的相關(guān)信息
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user

-- 修改當(dāng)前用戶(hù)密碼
set password =password('1234');

-- 修改其他用戶(hù)密碼
update mysql.user set password=password('123456') where user='ouo';
-- 所有通過(guò)user表的操作，都必須使用下面命令才能生效
flush privileges;

-- 修改用戶(hù)名
update mysql.user set user='ahzoo' where user='ouo';
flush privileges;

-- 刪除用戶(hù)
drop user ouo;
-- 注意：刪除用戶(hù)時(shí)，不建議使用下面命令進(jìn)行刪除，因?yàn)橄到y(tǒng)會(huì)有殘留信息保留
delete from user where user='ouo'?
flush privileges;

權(quán)限管理: ?

授予權(quán)限

grant 權(quán)限 1,權(quán)限 2,…權(quán)限 n on 數(shù)據(jù)庫(kù)名稱(chēng).表名稱(chēng) to 用戶(hù)名@用戶(hù)地址 identified by '密碼';

-- 授予數(shù)據(jù)庫(kù)下所有表，所有權(quán)限
grant all privileges on testDB.* to ahzoo@localhost identified by '123456';
-- 授予所有庫(kù)、表增刪改查權(quán)限
grant select,insert,delete,drop on *.* to ahzoo@localhost identified by '123456';
-- 對(duì)網(wǎng)絡(luò)用戶(hù)授權(quán)；@'%' 表示對(duì)非本地主機(jī)用戶(hù)授權(quán)，不包括localhost
grant all privileges on *.* to ouo@'%' identified by '123456'


-- 查看權(quán)限
show grants;

取消權(quán)限

revoke [權(quán)限 1,權(quán)限 2,…權(quán)限 n] on 庫(kù)名.表名 from 用戶(hù)名@用戶(hù)地址;

revoke all privileges on testDB.* from ahzoo@localhost;

索引優(yōu)化

在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)系統(tǒng)還維護(hù)著滿(mǎn)足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)， 這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。

下圖就是一種可能的索引方式示例：

左邊是數(shù)據(jù)表，一共有兩列七條記錄，最左邊的是數(shù)據(jù)記錄的物理地址。為了加快 Col2 的查找，可以維護(hù)一個(gè) 右邊所示的二叉查找樹(shù)，每個(gè)節(jié)點(diǎn)分別包含索引鍵值和一個(gè)指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指 針，這樣就可以運(yùn)用 二叉查找在一定的復(fù)雜度內(nèi)獲取到相應(yīng)數(shù)據(jù)，從而快速的檢索出符合條件的記錄。 一般來(lái)說(shuō)索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤(pán)上。

索引優(yōu)勢(shì)： ?

• 提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫(kù)的IO成本。
• 通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。

索引劣勢(shì)： ?

• 雖然索引大大提高了查詢(xún)速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加了索引列的字段，都會(huì)調(diào)整因?yàn)?更新所帶來(lái)的鍵值變化后的索引信息。
• 實(shí)際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的。

MySQL 索引

Btree

MySQL 使用的是 Btree 索引： ?

一顆 b 樹(shù)，淺藍(lán)色的塊我們稱(chēng)之為一個(gè)磁盤(pán)塊，可以看到每個(gè)磁盤(pán)塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示)，如磁盤(pán)塊 1 包含數(shù)據(jù)項(xiàng) 17 和 35，包含指針 P1、P2、P3，P1 表示小于 17 的磁盤(pán)塊，P2 表示在 17 和 35 之間的磁盤(pán)塊，P3 表示大于 35 的磁盤(pán)塊。

真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如 17、35 并不真實(shí)存在于數(shù)據(jù)表中。

查找過(guò)程： ?

如果要查找數(shù)據(jù)項(xiàng) 29，那么首先會(huì)把磁盤(pán)塊 1 由磁盤(pán)加載到內(nèi)存，此時(shí)發(fā)生一次 IO，在內(nèi)存中用二分查找確定 29在 17 和 35 之間，鎖定磁盤(pán)塊 1 的 P2 指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P(pán)的 IO）可以忽略不計(jì)，通過(guò)磁盤(pán)塊 1的 P2 指針的磁盤(pán)地址把磁盤(pán)塊 3 由磁盤(pán)加載到內(nèi)存，發(fā)生第二次 IO，29 在 26 和 30 之間，鎖定磁盤(pán)塊 3 的 P2 指針，通過(guò)指針加載磁盤(pán)塊 8 到內(nèi)存，發(fā)生第三次 IO，同時(shí)內(nèi)存中做二分查找找到 29，結(jié)束查詢(xún)，總計(jì)三次 IO。

真實(shí)的情況是，3 層的 b+樹(shù)可以表示上百萬(wàn)的數(shù)據(jù)，如果上百萬(wàn)的數(shù)據(jù)查找只需要三次 IO，性能提高將是巨大的，如果沒(méi)有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次 IO，那么總共需要百萬(wàn)次的 IO，顯然成本非常非常高。

?B+tree

B+Tree 與 B-Tree 的區(qū)別： ?

1、B-樹(shù)的關(guān)鍵字和記錄是放在一起的，葉子節(jié)點(diǎn)可以看作外部節(jié)點(diǎn)，不包含任何信息；B+樹(shù)的非葉子節(jié)點(diǎn)中只有關(guān)鍵字和指向下一個(gè)節(jié)點(diǎn)的索引，記錄只放在葉子節(jié)點(diǎn)中。

2、在 B-樹(shù)中，越靠近根節(jié)點(diǎn)的記錄查找時(shí)間越快，只要找到關(guān)鍵字即可確定記錄的存在；而 B+樹(shù)中每個(gè)記錄的查找時(shí)間基本是一樣的，都需要從根節(jié)點(diǎn)走到葉子節(jié)點(diǎn)，而且在葉子節(jié)點(diǎn)中還要再比較關(guān)鍵字。從這個(gè)角度看 B- 樹(shù)的性能好像要比 B+樹(shù)好，而在實(shí)際應(yīng)用中卻是 B+樹(shù)的性能要好些。因?yàn)?B+樹(shù)的非葉子節(jié)點(diǎn)不存放實(shí)際的數(shù)據(jù)，這樣每個(gè)節(jié)點(diǎn)可容納的元素個(gè)數(shù)比 B-樹(shù)多，樹(shù)高比 B-樹(shù)小，這樣帶來(lái)的好處是減少磁盤(pán)訪(fǎng)問(wèn)次數(shù)。盡管 B+樹(shù)找到一個(gè)記錄所需的比較次數(shù)要比 B-樹(shù)多，但是一次磁盤(pán)訪(fǎng)問(wèn)的時(shí)間相當(dāng)于成百上千次內(nèi)存比較的時(shí)間，因此實(shí)際中B+樹(shù)的性能可能還會(huì)好些，而且 B+樹(shù)的葉子節(jié)點(diǎn)使用指針連接在一起，方便順序遍歷（例如查看一個(gè)目錄下的所有文件，一個(gè)表中的所有記錄等），這也是很多數(shù)據(jù)庫(kù)和文件系統(tǒng)使用 B+樹(shù)的緣故。

為什么 B+樹(shù)比 B-樹(shù)更適合實(shí)際應(yīng)用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫(kù)索引： ?

B+樹(shù)的磁盤(pán)讀寫(xiě)代價(jià)更低

B+樹(shù)的內(nèi)部結(jié)點(diǎn)并沒(méi)有指向關(guān)鍵字具體信息的指針。因此其內(nèi)部結(jié)點(diǎn)相對(duì) B 樹(shù)更小。如果把所有同一內(nèi)部結(jié)點(diǎn)的關(guān)鍵字存放在同一盤(pán)塊中，那么盤(pán)塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多。一次性讀入內(nèi)存中的需要查找的關(guān)鍵字也就越多。相對(duì)來(lái)說(shuō) IO 讀寫(xiě)次數(shù)也就降低了。

B+樹(shù)的查詢(xún)效率更加穩(wěn)定

由于非終結(jié)點(diǎn)并不是最終指向文件內(nèi)容的結(jié)點(diǎn)，而只是葉子結(jié)點(diǎn)中關(guān)鍵字的索引。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點(diǎn)到葉子結(jié)點(diǎn)的路。所有關(guān)鍵字查詢(xún)的路徑長(zhǎng)度相同，導(dǎo)致每一個(gè)數(shù)據(jù)的查詢(xún)效率相當(dāng)。

?聚簇索引和非聚簇索引

聚簇索引并不是一種單獨(dú)的索引類(lèi)型，而是一種數(shù)據(jù)存儲(chǔ)方式。術(shù)語(yǔ)‘聚簇’表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲(chǔ) 在一起。
如下圖，左側(cè)的索引就是聚簇索引，因?yàn)閿?shù)據(jù)行在磁盤(pán)的排列和索引排序保持一致。

聚簇索引的好處：

按照聚簇索引排列順序，查詢(xún)顯示一定范圍數(shù)據(jù)的時(shí)候，由于數(shù)據(jù)都是緊密相連，數(shù)據(jù)庫(kù)不不用從多 個(gè)數(shù)據(jù)塊中提取數(shù)據(jù)，所以節(jié)省了大量的 io 操作。

聚簇索引的限制：

對(duì)于 mysql 數(shù)據(jù)庫(kù)目前只有 innodb 數(shù)據(jù)引擎支持聚簇索引，而 Myisam 并不支持聚簇索引。 由于數(shù)據(jù)物理存儲(chǔ)排序方式只能有一種，所以每個(gè) Mysql 的表只能有一個(gè)聚簇索引。一般情況下就是 該表的主鍵。 為了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主鍵列盡量選用有序的順序 id，而不建議用 無(wú)序的 id，比如 uuid 這種

Mysql 索引分類(lèi)

-- 創(chuàng)建
CREATE [UNIQUE] INDEX [indexName] ON table_name(column))
-- 刪除?
DROP INDEX [indexName] ON tableName;
-- 查看?
SHOW INDEX FROM tableName;

-- 使用Alter命令:

-- 該語(yǔ)句添加一個(gè)主鍵，這意味著索引值必須是唯一的，且不能為 NULL:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)?

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

-- 添加普通索引，索引值可出現(xiàn)多次:
ALTER TABLE tbl_name ADD INDEX index_name (column_list)?

--該語(yǔ)句指定了索引為 FULLTEXT ，用于全文索引:
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

單值索引

即一個(gè)索引只包含單個(gè)列，一個(gè)表可以有多個(gè)單列索引。 ?

-- 在表創(chuàng)建時(shí)直接創(chuàng)建索引
CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name VARCHAR(200),?
? PRIMARY KEY(id), ?
? KEY (customer_name)
);

-- 單獨(dú)創(chuàng)建索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引

索引列的值必須唯一，但允許有空值。 ?

隨表一起創(chuàng)建：

CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name
?? ?VARCHAR(200),?
? PRIMARY KEY(id),?
? KEY (customer_name),?
? UNIQUE (customer_no)
);

 單獨(dú)建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no

主鍵索引

設(shè)定為主鍵后數(shù)據(jù)庫(kù)會(huì)自動(dòng)建立索引，innodb為聚簇索引。 ?

-- 隨表創(chuàng)建
CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name
?? ?VARCHAR(200),?
? PRIMARY KEY(id)
);

-- 單獨(dú)建主鍵索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no)

-- 刪除建主鍵索引：
ALTER TABLE customer drop PRIMARY

復(fù)合索引

即一個(gè)索引包含多個(gè)列。 ?

-- 隨表一起建索引：
CREATE TABLE customer (
? id INT(10) UNSIGNED AUTO_INCREMENT ,
? customer_no VARCHAR(200),
? customer_name
?? ?VARCHAR(200),?
? PRIMARY KEY(id),?
? KEY (customer_name),?
? UNIQUE (customer_name),?
? KEY (customer_no,customer_name)
);

-- 單獨(dú)建索引：
CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

索引優(yōu)化

• 最佳左前綴法則

使用復(fù)合索引時(shí)，需遵循最左前綴法則（查詢(xún)從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列）。即過(guò)濾條件要使用索引必須按照索引建立時(shí)的順序，依次滿(mǎn)足，一旦跳過(guò)某個(gè)字段，索引后面的字段都無(wú)法被使用。

• 不要在索引列上做任何計(jì)算

索引列上做【計(jì)算、函數(shù)、(自動(dòng)\手動(dòng))類(lèi)型轉(zhuǎn)換】等操作時(shí)，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。

• 索引列上不能有范圍查詢(xún)

執(zhí)行mysql命令時(shí)應(yīng)將可能做范圍查詢(xún)的字段的索引順序放在最后。

• 盡量使用覆蓋索引

覆蓋索引：SQL 只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù)，而不必通過(guò)二級(jí)索引查到主鍵之后再去查詢(xún)數(shù)據(jù)。即查詢(xún)列和索引列時(shí)不要使用 select *…而是使用select a,b,c….。

• 1、使用不等于(!= 或者<>)時(shí)，有時(shí)會(huì)無(wú)法使用索引會(huì)導(dǎo)致全表掃描。
• 2、字段的 is null 可以用到索引 而 is not null 不會(huì)使用索引。
• 3、不能使用前綴進(jìn)行模糊匹配：

... like '%a%' ?√... like '%a' ?? ?√... like 'a%' ?? ?×

使用 union all 或者 union 來(lái)替代or示例：

假設(shè)abc為索引

-- 索引被使用：
where a = 3;
where a = 3 and b = 5;
where a = 3 and b = 5 and c = 4;

-- 索引未被使用：
where a <> 3;
where abs(a) =3;
where b = 3;
where b = 3 and c = 4;
where c = 4;

-- 使用到a索引，但是未使用b、c索引
where a = 3 and c = 5；
where a = 3 and b > 4 and c = 5;
where a is null and b is not null;

子查詢(xún)優(yōu)化

在范圍判斷時(shí)，盡量不要使用 not in 和 not exists，使用 left join on xxx i。

排序分組優(yōu)化

• 無(wú)過(guò)濾，不索引

where，limt 都相當(dāng)于一種過(guò)濾條件，所以才能使用上索引。

• 順序錯(cuò)，必排序

where 兩側(cè)列的順序可以變換，效果相同，但是 order by 列的順序不能隨便變換。

• 方向反，必排序

如果可以用上索引的字段都使用正序或者逆序，實(shí)際上是沒(méi)有任何影響的，無(wú)非將結(jié)果集調(diào)換順序。

-- 兩個(gè)排序方式都是desc：
select * from mytest where name='ahzoo' order by deptid desc, name desc

如果排序的字段，順序有差異，就需要將差異的部分，進(jìn)行一次倒置順序，因此還是需要手動(dòng)排序的。

-- 兩個(gè)排序方式相反，一個(gè)是降序一個(gè)是升序
select * from mytest where name='ahzoo' order by deptid desc, name asc

到此這篇關(guān)于分享幾個(gè)簡(jiǎn)單MySQL優(yōu)化小妙招的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化小妙招內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論