MySQL優化（超完整版）（一）

一、 MySQL的優化

? 前言：
? ? MySQL數據庫的優化模塊：
? ? ? - 數據庫的設計—三大范式
? ? ? - 數據庫的索引：唯一索引、主鍵索引、聚合索引、復合索引、默認索引
? ? ? - SQL優化
? ? ? - 分庫分表
? ? ? - 讀寫分離：提升IO性能
? ? ? - 存儲過程優化
? ? ? - 對MySQL配置進行優化（my.ini）
? ? ? - 定時清理碎片

1. 數據庫的設計

（1） 什么是三大范式？

? ? 為了建立冗余較小、結構合理的數據庫，設計數據庫時必須遵循一定的規則。在關系型數據庫中這種規則就稱為范式。三大范式包括：
? ? - 1NF：屬性的原子性，要求屬性具有原子性，不可分解。
? ? - 2NF：對記錄的唯一性，表中記錄是唯一的（通常通過主鍵來實現）。
? ? - 3NF：是對字段冗余性的約束，要求字段沒有冗余。

（2） 1NF

-- 案例
create table `user`(
id int,
name varchar(10),
address varchar(10)
);
insert into `user` values(1,’zs’,’上海市浦東新區’)；
此時這里就針對address這個字段，有了可分割性，可將將address分為：-市-區。
此時表的設計就不遵循1NF。

（3） 2NF

-- 案例

create table `emp`(
id int,
name varchar(10),
age int(10),
salary float(8,4)
這里id是員工的編號，每一個編號唯一確定一個員工，員工的充值和工位號也是通過這個id確定，此時用id來作為emp表的主鍵就不禁合理，因為主鍵一般是不做業務操作的，主鍵的作用就是唯一標識一行。
);

（4） 3NF

-- 案例
create table student(
stu_id int(10),
stu_name varchar(30),
class_id int(10),
class_name varchar(30)
);
Insert into student values(1,’zs’,1,’一班’);
Insert into student values(2,’ls’,1,’一班’);
Insert into student values(3,’ww’,1,’一班’);
Insert into student values(4,’wb’,1,’一班’);
這里我們發現，class_id和class_name字段大量的冗余，不遵循第3NF，這里我們需要將這張表拆分：student 表和 class表
create table student(
stu_id int(10),
class_id int(10),
stu_name varchar(30)
);
create table `class`(
class_id int(10),
class_name varchar(30)
);
然后對兩張表設置外鍵關聯。

2. 慢查詢介紹及定位

（1） 定位慢查詢

? ? 慢查詢的定義：MySQL規定，只要10s內，沒有按照規則的時間返回結果，就是慢查詢類型，然后MySQL會將這些語句存儲到慢查詢日志中。
可以通過命令查看

-- 使用show status查看MySQL服務器狀態信息
mysql>show status

-- mysql 運行了多長時間
show status like 'uptime';

-- 當前窗口 數據庫的查詢次數
show status like 'com_select';

-- 當前窗口 數據庫的插入次數
show  status like 'com_insert';

-- 當前窗口 數據庫的更新次數
show  status like 'com_update';

-- 當前窗口，數據庫的刪除次數
show  status like 'com_delete';

-- 查看試圖連接到MySQL(不管是否連接成功)的連接數
show status like 'connections';

-- 查看當前打開的連接的數量。
show status like 'threads_connected';

-- 顯示慢查詢數量 **********************
show status like 'slow_queries';

注意：這里默認的是session，表示的時當前會話，如果想查詢全局的需要：

show global status like ‘’;

（2） 慢查詢案例演示

①慢查詢時間設置

--查詢慢查詢時間
show variables like 'long_query_time';
--修改慢查詢時間（臨時）
set long_query_time=1; ---但是重啟mysql之后，long_query_time依然是my.ini中的值。

②建表、造數據

/*部門表*/
create
 table
  dept(
   deptno mediumint unsigned not null default 0,
   /*編號*/
   dname varchar(20) not null default "",
   /*名稱*/
   loc varchar(13) not null default "" /*地點*/
  ) ENGINE = MyISAM default CHARSET = utf8;

/*員工表*/
create
 table
  emp(
   empno mediumint unsigned not null default 0,
   /*編號*/
   ename varchar(20) not null default "",
   /*名字*/
   job varchar(9) not null default "",
   /*工作*/
   mgr mediumint unsigned not null default 0,
   /*上級編號*/
   hiredate date not null,
   /*入職時間*/
   sal decimal(
    7,
    2
   ) not null,
   /*薪水*/
   comm decimal(
    7,
    2
   ) not null,
   /*紅利*/
   deptno mediumint unsigned not null default 0 /*部門編號*/
  ) ENGINE = MyISAM default CHARSET = utf8;
/*薪水*/
create
 table
  salgrade(
   grade mediumint unsigned not null default 0,
   losal decimal(
    17,
    2
   ) not null,
   hisal decimal(
    17,
    2
   ) not null
  ) ENGINE = MyISAM default CHARSET = utf8;

--插入數據
INSERT INTO salgrade VALUES(1,700,1200);
INSERT INTO salgrade VALUES(2,1201,1400);
INSERT INTO salgrade VALUES(3,1401,2000);
INSERT INTO salgrade VALUES(4,2001,3000);
INSERT INTO salgrade VALUES(5,3001,9999);

③批量生成100W條數據

--生成隨機字符
create
 function rand_string(
  n int
 ) returns varchar(255) #該函數會返回一個字符串
 begin #chars_str定義一個變量 chars_str,類型是 varchar(100),默認值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set
return_str = concat( return_str, substring( chars_str, floor( 1 + rand()* 52 ), 1 ));
set
i = i + 1;
end while;
return return_str;
end

--生成隨機數
create FUNCTION rand_num()
RETURNS int(5)
BEGIN
 DECLARE i int default 0;
 set i =floor(10+RAND()*500);
 return i;
END

--編寫存儲過程，插入數據
delimiter // 
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit設置成0
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end // 
 delimiter ;

-- 執行
call insert_emp (100001,10000000);

④設置MySQL記錄慢查詢日志
首先將MySQL服務關閉：

進入mysql/bin下執行一下命令：
[mysql5.5 可以在my.ini指定]（安全模式啟動，數據庫將操作寫入日志，以備恢復）

$ mysqld.exe --safe-mode  --slow-query-log 

然后在my.ini配置文件中有這么一行：

在這個目錄下，會生成相應的慢查詢記錄。
#5.7版本自動開啟：

然后我們設置慢查詢時間為：1s

set long_query_time=1;

用剛剛造出來的數據執行一個慢查詢:

select * from emp where ename = 'aDNehz';

查看慢查詢日志：

通過日志，我們就能定位到具體的是哪一條語句查詢慢。

3. MySQL的索引

索引的概述

? ?索引用來快速的查詢那些具有特定值的記錄。所有的MySQL索引都是以B+樹的形式保存的。如果沒有索引，執行查詢時MySQL必須從第一個記錄開始，進行全表掃描，直至找到合適的記錄。表里的記錄越多，這個操作越耗時。如果作為搜索條件的列上已經創建了索引，MySQL無需掃描任何記錄即可迅速得到目標記錄所在的位置。如果表有1000個記錄，通過索引查找記錄至少要比順序掃描記錄快100倍。

（1） 主鍵索引

? ?主鍵索引是一種唯一性索引，但是它必須指定“PRIMARY KEY”。主鍵一般在創建表的時候指定，并且一張表只能有一個主鍵。

#創建主鍵索引：
默認情況下，MySQL會為主鍵自動添加主鍵索引。
也可以后期添加主鍵：
Alter table table_name add primary key(field_name);
#刪除主鍵：
Alter table table_name drop primary key; 

#查看索引
show index from table_name;
show keys from table_name;

（2） 全文索引

? ? 全文索引一般用于查詢文本或者長內容而建立。

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

#全文索引的使用方法
-- 錯誤使用方法
select * from articles where body like '%mysql%';

-- 建立全文索引
alter table articles add  FULLTEXT index fulltext_article(body);
-- 正確使用方法
select * from articles where match (body) against('mysql');

使用全文索引的注意事項：
? ? - MySql自帶的全文索引只能用于數據庫引擎為MYISAM的數據表，如果是其他數據引擎，則全文索引不會生效.
? ? - MySQL不能對中文進行全文索引，只適用于英文.
? ? - 使用全文索引，只能用固定的語法：match(字段名)… against(關鍵字).
? ? - MySQL全文索引所能找到的默認最小長度為4個字符，并且如果查詢的字符串包含停止詞（常見字符），那么該停止詞將會被忽略。

（3） 唯一索引

? ?這種索引的所有值都只能出現一次，即必須唯一。
默認的，在創建表時指定字段為唯一時，自動為其創建唯一索引。

#創建唯一索引：
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；
ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；
例：
create table ddd(id int primary key auto_increment , name varchar(32) unique);
create unique index uniq_index_name on ddd(name);
alter table ddd add unique uniq_index_name (name);

注意：unique字段可以為null，可以有多個null，但是如果是字符串的話只能有一個為‘’。

（4） 普通索引

? ? 普通索引（由關鍵字KEY或INDEX定義的索引）的唯一任務是加快對數據的訪問速度。因此，應該只為那些最經常出現在查詢條件（WHEREcolumn=）或排序條件（ORDERBYcolumn）中的數據列創建索引。只要有可能，就應該選擇一個數據最整齊、最緊湊的數據列（如一個整數類型的數據列）來創建索引。

創建方式：   create index 索引名 on 表 (列1,列名2);

-- 案例
-- 未建立索引時查詢 1.45s
select * from emp where ename ='MFPkFv';
-- 創建普通索引
create index index_ename on emp (ename);
-- 查詢 4ms
select * from emp where ename ='MFPkFv';

（5） 索引實現原理

? ?MySQL數據庫的索引，是數據庫管理中的一個排序的數據結構，以便于協助快速查詢，更數據庫表中數據。MySQL中的索引實現方式就是：B+樹索引。

? ?上圖展示了一種可能的索引方式。左邊是數據表，一共有兩列七條記錄，最左邊的是數據記錄的物理地址（注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的）。為了加快 Col2 的查找，可以維護一個右邊所示的二叉查找樹，每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針，這樣就可以運用二叉查找在 O(log2n)的復雜度內獲取到相應數據。
不同的存儲引擎使用的索引：

? ? b-/+樹索引的性能分析：先從 B-Tree 分析，根據 B-Tree 的定義，可知檢索一次最多需要訪問 h 個節點。數據庫系統的設計者巧妙利用了磁盤預讀原理，將一個節點的大小設為等于一個頁，這樣每個節點只需要一次 I/O 就可以完全載入。為了達到這個目的，在實際實現 B-Tree 還需要使用如下技巧：
? ?每次新建節點時，直接申請一個頁的空間，這樣就保證一個節點物理上也存儲在一個頁里，加之計算機存儲分配都是按頁對齊的，就實現了一個 node 只需一次 I/O。B-Tree 中一次檢索最多需要 h-1 次 I/O（根節點常駐內存），漸進復雜度為 O(h)=O(logdN)。一般實際應用中，出度 d 是非常大的數字，通常超過 100，因此 h 非常小（通常不超過 3）。
? ?而紅黑樹這種結構，h 明顯要深的多。由于邏輯上很近的節點（父子）物理上可能很遠，無法利用局部性，所以紅黑樹的 I/O 漸進復雜度也為 O(h)，效率明顯比 B-Tree 差很多。
? ?綜上所述，用 B-Tree 作為索引結構效率是非常高的。
? ? B+ 樹非葉節點中存放的關鍵碼并不指示數據對象的地址指針，非也節點只是索引部分。所有的葉節點在同一層上，包含了全部關鍵碼和相應數據對象的存放地址指針，且葉節點按關鍵碼從小到大順序鏈接。如果實際數據對象按加入的順序存儲而不是按關鍵碼次數存儲的話，葉節點的索引必須是稠密索引，若實際數據存儲按關鍵碼次序存放的話，葉節點索引時稀疏索引。
? ?B+ 樹有 2 個頭指針，一個是樹的根節點，一個是最小關鍵碼的葉節點。
所以 B+ 樹有兩種搜索方法：
? ?一種是按葉節點自己拉起的鏈表順序搜索。
? ?一種是從根節點開始搜索，和 B 樹類似，不過如果非葉節點的關鍵碼等于給定值，搜索并不停止，而是繼續沿右指針，一直查到葉節點上的關鍵碼。所以無論搜索是否成功，都將走完樹的所有層。
B+ 樹中，數據對象的插入和刪除僅在葉節點上進行。

綜上所述，兩種排序的不同之處在于：
? ? - B 樹中同一鍵值不會出現多次，并且它有可能出現在葉結點，也有可能出現在非葉結點中。而 B+ 樹的鍵一定會出現在葉結點中，并且有可能在非葉結點中也有可能重復出現，以維持 B+ 樹的平衡。
? ? - 因為 B 樹鍵位置不定，且在整個樹結構中只出現一次，雖然可以節省存儲空間，但使得在插入、刪除操作復雜度明顯增加。B+ 樹相比來說是一種較好的折中。
? ? - B 樹的查詢效率與鍵在樹中的位置有關，最大時間復雜度與 B+ 樹相同(在葉結點的時候)，最小時間復雜度為 1(在根結點的時候)。而 B+ 樹的時候復雜度對某建成的樹是固定的。可以掃描2的次方。

（6） 索引的優勢與劣勢

? 優勢：
? ? - 創建索引可以大大提高系統性能
? ? - 大大加快對數據的檢索速度
? ? - 加速表和表之間的連接
? ? - 對使用分組和排序子句進行數據檢索時，同樣可以顯著減少查詢中分組和排序的時間
? 劣勢：
? ? - 建立索引耗時：創建索引和維護索引要耗費時間，這種時間隨著數據量的增加而增加
? ? - 占用一定的物理內存：索引需要占物理空間，除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。
? ? - DML操作效率變低： 當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了數據的維護速度。

（7） 建立索引的時機

? 應該建立索引的字段：
? ? - 查詢作為查詢條件字段應該創建索引
? ? - 經常用在連接的字段可以建立索引
? ? - 經常需要根據范圍進行搜索的列上創建索引
? ? - 經常需要排序的列上創建索引
? 不應建立索引的字段：
? ? - 那些在查詢中很少使用或者參考的列不應該創建索引
? ? - 對于那些只有很少數據值的列也不應該增加索引，例如：性別、是否已婚等等
? ? - 對于那些定義為 text, image 和 bit 數據類型的列不應該增加索引
? ? - 修改性能遠遠大于檢索性能時，不應該創建索引

（8） 索引使用的注意事項

① 聯合索引的失效場景

-- 給dept新增數據：
create PROCEDURE insert_dept(in start int(10),in max_num int(10))
BEGIN
 declare i int DEFAULT 0;
 set autocommit=0;
 REPEAT
 set i=i+1;
 insert into dept values ((start+i),rand_string(10),rand_string(8));
 UNTIL i =max_num
 end REPEAT;
  commit;
END

-- 執行
call insert_dept(100,10);

-- 創建主鍵索引
alter table dept add primary key (deptno);
-- 創建一個聯合索引
alter table dept add index my_ind (dname,loc); //  dname 左邊的列,loc就是右邊的列
在聯合索引中，以dept為例：
-- 索引不失效
explain select * from dept where dname ='fuHagHPcRc';
-- 索引不失效
explain select * from dept where dname ='fuHagHPcRc' and loc = 'KtPSTRAc';
-- 索引失效
explain select * from dept where  loc = 'KtPSTRAc';

綜上案例得出：創建的多列索引，如果不是使用第一部分，則不會創建索引。

② 模糊查詢時like,索引不會失效，但是如果like中有‘%xxx%’，則索引失效，但是%放在后面索引不會失效，例“xxx%”，但是%放在前面會失效，例：“%xxx”。
③ 如果條件中有or，及時其中有帶索引字段，也不會使用索引
④ 如果類型為字符串，那一定要在條件中將數據使用引號引用起來。否則不使用索引
⑤ 如果MySQL的全表掃描比使用索引快，則不使用索引。

#查看索引的使用率：
show status like 'handler_read%';

handler_read_key：這個值越高越好，越高表示使用索引查詢到的次數。
handler_read_rnd_next：這個值越高，說明查詢低效。

4. SQL語句優化技巧

（1） group by 的默認排序

? ? 使用group by 分組查詢是，默認分組后，還會排序，可能會降低速度，在group by 后面增加 order by null 就可以防止排序。

-- 案例
-- 8.188s
select * from emp  group by deptno;

 -- 6.200s
select * from emp  group by deptno order by null;

這是因為在group by后默認使用排序：
explain select * from emp  group by deptno;

（2） 有些情況下，使用連接來代替子查詢

原因：使用join，MySQL不需要在內存中創建臨時表。

-- 7.924s
select * from dept, emp where dept.deptno=emp.deptno;

-- 8.8s
select * from dept left join emp on dept.deptno=emp.deptno;

（3） 查詢優化時，盡量避免全表掃描

（4） 盡量避免在where 子句后面對字段進行null值判斷，否則將導致引擎放棄使用索引而進行全表掃描（解決，對null值進行default 設置）

（5） 在對where子句進行判斷時，盡量不要使用xx=，例：>=、<=,比如>=18 就寫成>17

（6） 能用between 就不要用in

二、 補充內容

1. MySQL的存儲引擎

（1） MySQL引擎介紹

? ? MySQL使用的存儲引擎有三種：myisam / innodb/ memory。
? ? Myisam存儲：如果表對事務要求不高，同時是以查詢和添加為主的，我們考慮使用myisam存儲引擎. ,比如 bbs 中的 發帖表，回復表。
? ? innodb存儲：對事務要求高，保存的數據都是重要數據，我們建議使用INNODB,比如訂單表，賬號表。
? ? Memory：基于內存存儲，類似于Redis。

（2） myisam引擎與innodb引擎的區別

? ?- 事物安全（MyISAM不支持事務，INNODB支持事務）
? ?- 查詢和添加速度（MyISAM批量插入速度快）
? ?- 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
? ?- 鎖機制（MyISAM時表鎖，innodb是行鎖）
? ?- 外鍵機制（MyISAM 不支持外鍵， INNODB支持外鍵）
Ps：Memory 存儲，比如我們數據變化頻繁，不需要入庫，同時又頻繁的查詢和修改，我們考慮使用memory, 速度極快。

（3） myisam使用的注意事項

? ?如果我們在建表的時候指定的是myisam引擎，那么在我們刪除表數據時，默認只是邏輯刪除，而真正的物理存儲的數據文件是不會刪除的。
? ?接下來由下圖，為大家講解一下MySQL數據存放的方式：
? ?在my.ini文件中有：datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data 這個配置就是本地系統存放MySQL數據文件的目錄。

進入具體的數據庫：（test）

這其中：
? ?.frm結尾的表示表的結構文件
? ?.MYD結尾的表示數據文件
? ?.MYI結尾的表示索引文件
#然后我們演示如果使用的是myisam引擎，數據如何刪除：

-- 建表，并指定引擎為myisam
create table test100(id int unsigned ,name varchar (22)) engine=myisam;
-- 插入數據
insert into test100 values(1,'aaaaa');
insert into test100 values(2,'bbbb');
insert into test100 values(3,'ccccc');
-- 批量增加數據
insert into test100 select id,name from test100;
-- 刪除部分數據
delete from test100 where id =2;

此時我們查看test100.MYD 發現這個文件的大小根本沒有變化。

--這時我們必須清除碎片：
optimize table test100;  

之后test100.MYD大小就會改變了，數據真正意義上的刪除了。

2. MySQL的定時備份

編寫備份腳本，然后通過crontab 定時執行。

-- 語法：
mysqldump –u -賬號 –密碼 數據庫 [表名1 表名2..]  > 文件路徑
--例：
mysqldump -u -root root test > d:\temp.sql
--載入數據,在MySQL
mysql>source /path/temp.sql

小編在下篇文章給大家MySQL如何分庫分表，MySQL負載均衡版的讀寫分離。

博文地址：https://blog.51cto.com/14048416/2432938

絕對沒有水貨！！！！！！！！！