MySQL優(yōu)化（超完整版）（一）

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

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

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

然后我們設(shè)置慢查詢時間為：1s

set long_query_time=1;

用剛剛造出來的數(shù)據(jù)執(zhí)行一個慢查詢:

select * from emp where ename = 'aDNehz';

查看慢查詢?nèi)罩荆?br/>
通過日志，我們就能定位到具體的是哪一條語句查詢慢。

3. MySQL的索引

索引的概述

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

（1） 主鍵索引

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

#創(chuàng)建主鍵索引：
默認(rèn)情況下，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） 全文索引

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

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');

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

（3） 唯一索引

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

#創(chuàng)建唯一索引：
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） 普通索引

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

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

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

（5） 索引實(shí)現(xiàn)原理

? ?MySQL數(shù)據(jù)庫的索引，是數(shù)據(jù)庫管理中的一個排序的數(shù)據(jù)結(jié)構(gòu)，以便于協(xié)助快速查詢，更數(shù)據(jù)庫表中數(shù)據(jù)。MySQL中的索引實(shí)現(xiàn)方式就是：B+樹索引。

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

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

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

（6） 索引的優(yōu)勢與劣勢

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

（7） 建立索引的時機(jī)

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

（8） 索引使用的注意事項(xiàng)

① 聯(lián)合索引的失效場景

-- 給dept新增數(shù)據(jù)：
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

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

-- 創(chuàng)建主鍵索引
alter table dept add primary key (deptno);
-- 創(chuàng)建一個聯(lián)合索引
alter table dept add index my_ind (dname,loc); //  dname 左邊的列,loc就是右邊的列
在聯(lián)合索引中，以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';

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

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

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

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

4. SQL語句優(yōu)化技巧

（1） group by 的默認(rèn)排序

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

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

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

這是因?yàn)樵趃roup by后默認(rèn)使用排序：
explain select * from emp  group by deptno;

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

原因：使用join，MySQL不需要在內(nèi)存中創(chuàng)建臨時表。

-- 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） 查詢優(yōu)化時，盡量避免全表掃描

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

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

（6） 能用between 就不要用in

二、 補(bǔ)充內(nèi)容

1. MySQL的存儲引擎

（1） MySQL引擎介紹

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

（2） myisam引擎與innodb引擎的區(qū)別

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

（3） myisam使用的注意事項(xiàng)

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

進(jìn)入具體的數(shù)據(jù)庫：（test）

這其中：
? ?.frm結(jié)尾的表示表的結(jié)構(gòu)文件
? ?.MYD結(jié)尾的表示數(shù)據(jù)文件
? ?.MYI結(jié)尾的表示索引文件
#然后我們演示如果使用的是myisam引擎，數(shù)據(jù)如何刪除：

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

此時我們查看test100.MYD 發(fā)現(xiàn)這個文件的大小根本沒有變化。

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

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

2. MySQL的定時備份

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

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

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

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

絕對沒有水貨?。。。。。。。。?/p>