SQL語句執(zhí)行順序

設置大小寫不敏感

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

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

MySql 的用戶和權限管理

用戶管理： ?

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

-- 查看用戶和權限的相關信息
select host,user,password,select_priv,insert_priv,drop_priv from mysql.user

-- 修改當前用戶密碼
set password =password('1234');

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

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

-- 刪除用戶
drop user ouo;
-- 注意：刪除用戶時，不建議使用下面命令進行刪除，因為系統(tǒng)會有殘留信息保留
delete from user where user='ouo'?
flush privileges;

權限管理: ?

授予權限

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

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


-- 查看權限
show grants;

取消權限

revoke [權限 1,權限 2,…權限 n] on 庫名.表名 from 用戶名@用戶地址;

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

索引優(yōu)化

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

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

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

索引優(yōu)勢： ?

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

索引劣勢： ?

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

MySQL 索引

Btree

MySQL 使用的是 Btree 索引： ?

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

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

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

查找過程： ?

如果要查找數(shù)據(jù)項 29，那么首先會把磁盤塊 1 由磁盤加載到內(nèi)存，此時發(fā)生一次 IO，在內(nèi)存中用二分查找確定 29在 17 和 35 之間，鎖定磁盤塊 1 的 P2 指針，內(nèi)存時間因為非常短（相比磁盤的 IO）可以忽略不計，通過磁盤塊 1的 P2 指針的磁盤地址把磁盤塊 3 由磁盤加載到內(nèi)存，發(fā)生第二次 IO，29 在 26 和 30 之間，鎖定磁盤塊 3 的 P2 指針，通過指針加載磁盤塊 8 到內(nèi)存，發(fā)生第三次 IO，同時內(nèi)存中做二分查找找到 29，結束查詢，總計三次 IO。

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

?B+tree

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

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

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

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

B+樹的磁盤讀寫代價更低

B+樹的內(nèi)部結點并沒有指向關鍵字具體信息的指針。因此其內(nèi)部結點相對 B 樹更小。如果把所有同一內(nèi)部結點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數(shù)量也越多。一次性讀入內(nèi)存中的需要查找的關鍵字也就越多。相對來說 IO 讀寫次數(shù)也就降低了。

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

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

?聚簇索引和非聚簇索引

聚簇索引并不是一種單獨的索引類型，而是一種數(shù)據(jù)存儲方式。術語‘聚簇’表示數(shù)據(jù)行和相鄰的鍵值聚簇的存儲 在一起。
如下圖，左側的索引就是聚簇索引，因為數(shù)據(jù)行在磁盤的排列和索引排序保持一致。

聚簇索引的好處：

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

聚簇索引的限制：

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

Mysql 索引分類

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

-- 使用Alter命令:

-- 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為 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)?

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

單值索引

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

-- 在表創(chuàng)建時直接創(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)
);

-- 單獨創(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)
);

 單獨建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no

主鍵索引

設定為主鍵后數(shù)據(jù)庫會自動建立索引，innodb為聚簇索引。 ?

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

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

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

復合索引

即一個索引包含多個列。 ?

-- 隨表一起建索引：
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)
);

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

索引優(yōu)化

• 最佳左前綴法則

使用復合索引時，需遵循最左前綴法則（查詢從索引的最左前列開始并且不跳過索引中的列）。即過濾條件要使用索引必須按照索引建立時的順序，依次滿足，一旦跳過某個字段，索引后面的字段都無法被使用。

• 不要在索引列上做任何計算

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

• 索引列上不能有范圍查詢

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

• 盡量使用覆蓋索引

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

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

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

使用 union all 或者 union 來替代or示例：

假設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;

子查詢優(yōu)化

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

排序分組優(yōu)化

• 無過濾，不索引

where，limt 都相當于一種過濾條件，所以才能使用上索引。

• 順序錯，必排序

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

• 方向反，必排序

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

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

如果排序的字段，順序有差異，就需要將差異的部分，進行一次倒置順序，因此還是需要手動排序的。

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

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

最新評論