分享幾個簡單MySQL優化小妙招

SQL語句執行順序

設定大小寫不敏感

• 檢視大小寫是否敏感：show variables like '%lower_case_table_names%'; windows 系統預設大小寫不敏感，但是 linux 系統是大小寫敏感的。
• 設定大小寫不敏感：在 my.cnf 這個組態檔 [mysqld] 中加入 lower_case_table_names = 1 ，然後重啟伺服器。

注意：在設定屬性為大小寫不敏感前就需要將原來的資料庫和錶轉換為小寫，否則會找不到資料庫名。 ​

MySql 的使用者和許可權管理

使用者管理： ​

-- 建立使用者
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;
-- 注意：刪除使用者時，不建議使用下面命令進行刪除，因為系統會有殘留資訊保留
delete from user where user='ouo' 
flush privileges;

許可權管理: ​

授予許可權

grant 許可權 1,許可權 2,…許可權 n on 資料庫名稱.表名稱 to 使用者名稱@使用者地址 identified by '密碼';

-- 授予資料庫下所有表，所有許可權
grant all privileges on testDB.* to ahzoo@localhost identified by '123456';
-- 授予所有庫、表增刪改查許可權
grant select,insert,delete,drop on *.* to ahzoo@localhost identified by '123456';
-- 對網路使用者授權；@'%' 表示對非本地主機使用者授權，不包括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;

索引優化

在資料之外，資料庫系統還維護著滿足特定查詢演演算法的資料結構，這些資料結構以某種方式參照（指向）資料， 這樣就可以在這些資料結構上實現高階查詢演演算法。這種資料結構，就是索引。

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

左邊是資料表，一共有兩列七條記錄，最左邊的是資料記錄的實體地址。為了加快 Col2 的查詢，可以維護一個 右邊所示的二叉查詢樹，每個節點分別包含索引鍵值和一個指向對應資料記錄實體地址的指 針，這樣就可以運用 二叉查詢在一定的複雜度內獲取到相應資料，從而快速的檢索出符合條件的記錄。 一般來說索引本身也很大，不可能全部儲存在記憶體中，因此索引往往以索引檔案的形式儲存的磁碟上。

索引優勢： ​

• 提高資料檢索的效率，降低資料庫的IO成本。
• 通過索引列對資料進行排序，降低資料排序的成本，降低了CPU的消耗。

索引劣勢： ​

• 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案每次更新新增了索引列的欄位，都會調整因為 更新所帶來的鍵值變化後的索引資訊。
• 實際上索引也是一張表，該表儲存了主鍵與索引欄位，並指向實體表的記錄，所以索引列也是要佔用空間的。

MySQL 索引

Btree

MySQL 使用的是 Btree 索引： ​

一顆 b 樹，淺藍色的塊我們稱之為一個磁碟塊，可以看到每個磁碟塊包含幾個資料項（深藍色所示）和指標（黃色所示)，如磁碟塊 1 包含資料項 17 和 35，包含指標 P1、P2、P3，P1 表示小於 17 的磁碟塊，P2 表示在 17 和 35 之間的磁碟塊，P3 表示大於 35 的磁碟塊。

真實的資料存在於葉子節點即 3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非葉子節點只不儲存真實的資料，只儲存指引搜尋方向的資料項，如 17、35 並不真實存在於資料表中。

查詢過程： ​

如果要查詢資料項 29，那麼首先會把磁碟塊 1 由磁碟載入到記憶體，此時發生一次 IO，在記憶體中用二分查詢確定 29在 17 和 35 之間，鎖定磁碟塊 1 的 P2 指標，記憶體時間因為非常短（相比磁碟的 IO）可以忽略不計，通過磁碟塊 1的 P2 指標的磁碟地址把磁碟塊 3 由磁碟載入到記憶體，發生第二次 IO，29 在 26 和 30 之間，鎖定磁碟塊 3 的 P2 指標，通過指標載入磁碟塊 8 到記憶體，發生第三次 IO，同時記憶體中做二分查詢找到 29，結束查詢，總計三次 IO。

真實的情況是，3 層的 b+樹可以表示上百萬的資料，如果上百萬的資料查詢只需要三次 IO，效能提高將是巨大的，如果沒有索引，每個資料項都要發生一次 IO，那麼總共需要百萬次的 IO，顯然成本非常非常高。

​B+tree

B+Tree 與 B-Tree 的區別： ​

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

2、在 B-樹中，越靠近根節點的記錄查詢時間越快，只要找到關鍵字即可確定記錄的存在；而 B+樹中每個記錄的查詢時間基本是一樣的，都需要從根節點走到葉子節點，而且在葉子節點中還要再比較關鍵字。從這個角度看 B- 樹的效能好像要比 B+樹好，而在實際應用中卻是 B+樹的效能要好些。因為 B+樹的非葉子節點不存放實際的資料，這樣每個節點可容納的元素個數比 B-樹多，樹高比 B-樹小，這樣帶來的好處是減少磁碟存取次數。儘管 B+樹找到一個記錄所需的比較次數要比 B-樹多，但是一次磁碟存取的時間相當於成百上千次記憶體比較的時間，因此實際中B+樹的效能可能還會好些，而且 B+樹的葉子節點使用指標連線在一起，方便順序遍歷（例如檢視一個目錄下的所有檔案，一個表中的所有記錄等），這也是很多資料庫和檔案系統使用 B+樹的緣故。

為什麼 B+樹比 B-樹更適合實際應用中作業系統的檔案索引和資料庫索引： ​

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

B+樹的內部結點並沒有指向關鍵字具體資訊的指標。因此其內部結點相對 B 樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查詢的關鍵字也就越多。相對來說 IO 讀寫次數也就降低了。

B+樹的查詢效率更加穩定

由於非終結點並不是最終指向檔案內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查詢必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個資料的查詢效率相當。

​聚簇索引和非聚簇索引

聚簇索引並不是一種單獨的索引型別，而是一種資料儲存方式。術語‘聚簇’表示資料行和相鄰的鍵值聚簇的儲存 在一起。
如下圖，左側的索引就是聚簇索引，因為資料行在磁碟的排列和索引排序保持一致。

聚簇索引的好處：

按照聚簇索引排列順序，查詢顯示一定範圍資料的時候，由於資料都是緊密相連，資料庫不不用從多 個資料塊中提取資料，所以節省了大量的 io 操作。

聚簇索引的限制：

對於 mysql 資料庫目前只有 innodb 資料引擎支援聚簇索引，而 Myisam 並不支援聚簇索引。 由於資料物理儲存排序方式只能有一種，所以每個 Mysql 的表只能有一個聚簇索引。一般情況下就是 該表的主鍵。 為了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主鍵列儘量選用有序的順序 id，而不建議用 無序的 id，比如 uuid 這種

Mysql 索引分類

-- 建立
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)

-- 新增普通索引，索引值可出現多次:
ALTER TABLE tbl_name ADD INDEX index_name (column_list) 

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

單值索引

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

-- 在表建立時直接建立索引
CREATE TABLE customer (
  id INT(10) UNSIGNED AUTO_INCREMENT ,
  customer_no VARCHAR(200),
  customer_name VARCHAR(200), 
  PRIMARY KEY(id),  
  KEY (customer_name)
);

-- 單獨建立索引：

CREATE INDEX idx_customer_name ON customer(customer_name);

唯一索引

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

隨表一起建立：

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

主鍵索引

設定為主鍵後資料庫會自動建立索引，innodb為聚簇索引。 ​

-- 隨表建立
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);

索引優化

• 最佳左字首法則

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

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

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

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

執行mysql命令時應將可能做範圍查詢的欄位的索引順序放在最後。

• 儘量使用覆蓋索引

覆蓋索引：SQL 只需要通過索引就可以返回查詢所需要的資料，而不必通過二級索引查到主鍵之後再去查詢資料。即查詢列和索引列時不要使用 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;

子查詢優化

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

排序分組優化

• 無過濾，不索引

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

• 順序錯，必排序

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

• 方向反，必排序

如果可以用上索引的欄位都使用正序或者逆序，實際上是沒有任何影響的，無非將結果集調換順序。

-- 兩個排序方式都是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優化小妙招的文章就介紹到這了,更多相關MySQL優化小妙招內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！