史上最全的mysql數(shù)據(jù)庫優(yōu)化方法，軟件測試人手必備！ 史上最全的mysql數(shù)據(jù)庫優(yōu)化方法，軟件測試

1.?課程介紹
??1.介紹什么是mysql優(yōu)化
??2.mysql優(yōu)化方法
??3.Mysql索引的使用
??4.分表技術(shù)
2.?mysql優(yōu)化概述
概述: 前面我們學(xué)習(xí)了頁面靜態(tài)化和redis,它們是通過不操作mysql數(shù)據(jù)庫達到提速目的。但是某些功能是一定要操作數(shù)據(jù)庫的，這就要求我們必須對mysql本身進行優(yōu)化。
mysql數(shù)據(jù)庫優(yōu)化的常見方法:
1.?表的設(shè)計要合理(滿足3NF) 3范式
2.?創(chuàng)建適當(dāng)索引[主鍵索引|唯一索引|普通索引|全文索引|空間索引]
3.?對SQL語句優(yōu)化---->定位慢查詢(explain)
4.?使用分表技術(shù)(重點【水平分表，垂直分表】), 分區(qū)技術(shù)(了解)
5.?讀寫分離(配置)
6.?創(chuàng)建適當(dāng)存儲過程，函數(shù)，觸發(fā)器
7.?對my.ini優(yōu)化，優(yōu)化配置
8.?軟件硬件升級
3.?表的設(shè)計滿足3NF
概述: 目前我們的表的設(shè)計，最高級別的范式是"6NF",對PHP程序員而言，我們的表滿足3NF即可。
3.1.?1NF
所謂1NF,就是
(1)?指表的屬性(列)具有原子性， 即表的列的不能再分了。

?
(2)?不能有重復(fù)的列
?
特殊
(1)?只要是關(guān)系型數(shù)據(jù)庫，就天然的滿足1NF
(2)?常見數(shù)據(jù)庫
關(guān)系型數(shù)據(jù)庫(mysql, oracle, sql server,informix, db2 , postgres)
非關(guān)系型數(shù)據(jù)(Nosql類型的數(shù)據(jù)庫由Redis, MongoDB)
3.2.?2NF
所謂2NF,就是指我們的表中不能有完全重復(fù)的一條記錄（行）.一般情況下通過設(shè)置一個主鍵來搞定，而且該主鍵是自增的。
3.3.?3NF(外鍵)
所謂3NF就是指，如果列的內(nèi)容可以被推導(dǎo)(顯式推導(dǎo)，隱式推導(dǎo))出，那么我們就不要單獨的用一列存放。
舉例:下面是滿足3NF

?
3.4.?反3NF
在通常情況下，我們的表的設(shè)計要嚴格的遵守3NF,但也有例外。有時為了提高查詢的效率，我們需要違反3NF。舉例:

?
4.?構(gòu)建海量表,定位慢查詢
為了講解這個優(yōu)化，我們需要構(gòu)建一個海量表(8000000),而且每條數(shù)據(jù)不一樣。
4.1.?構(gòu)建海量表步驟
(1)?創(chuàng)建一個測試數(shù)據(jù)庫

?
(2)?創(chuàng)建表
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 ;
加入數(shù)據(jù): dept.sql

?
#創(chuàng)建表EMP雇員
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 ;
加入數(shù)據(jù):emp.sql
#工資級別表
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;
加入數(shù)據(jù): salgrade.sql
4.2.?海量表帶來的問題
看一個案例

?
?
4.3.?先使用索引來搞定
l?給empno段添加主鍵索引
alter table emp add primary key (empno);

?
一個表(存儲引擎是MyISAM),對應(yīng)三個文件 xx.frm 表結(jié)構(gòu) xx.MYD 數(shù)據(jù)文件 xx.MYI 索引文件
l?通過測試看效果

?
l?刪除emp表的主鍵索引
alter table emp drop primary key
4.4.?如何定位慢查詢(slow query)
介紹: 在默認情況下，mysql 是不會記錄慢查詢的，所以我們在測試時，可以指定mysql記錄慢查詢.
開啟慢查詢的兩種方法:
l?啟動時，這樣啟動
cmd>bin/mysqld.exe --safe-mode --slow-query-log?
?
或者是
在my.ini的[mysqld]下添加一下代碼并且重啟
log-slow-queries = D:/server/mysql/mysqlslowquery.log(注意斜杠)
注：mysql5.6版本slow-query-log-file
long_query_time = 1 指定超過1秒算慢查詢
l?為了測試，我們修改 long_query_time

?
?
l?記錄下慢查詢
?

Time: 141122 10:39:45

User@Host: root[root] @ localhost [127.0.0.1]

Query_time: 1.625093 ?Lock_time: 0.001000 Rows_sent: 0 ?Rows_examined: 8000000

use testdb;
SET timestamp=1416623985;
select * from emp where ename='IUYTOPUYQWE';
?
說明: Query_time是查詢的時間
Lock_time：等待時間
?
4.5.?開啟慢查詢犧牲sql的執(zhí)行效率
如何使用慢查詢?

1. 系統(tǒng)上線之后,將慢查詢開啟一個星期.
2. 當(dāng)你認為系統(tǒng)所由于數(shù)據(jù)增多導(dǎo)致系統(tǒng)執(zhí)行緩慢.再開啟慢查詢找到執(zhí)行慢的sql語句,然后在優(yōu)化它.
   ?
   4.6.?如何分析一個sql語句的問題-explain 工具
   l?基本用法:
   explain ?sql\G
   l?案例:

?
l?優(yōu)化
添加索引。
?
4.7.?mysql的變量查詢
mysql>show variables;
mysql>show variables?like ‘%xxxx%’;
show ?tables ?like ?'數(shù)據(jù)表名';//查詢一個數(shù)據(jù)表是否存在
如果需要知道每個變量的具體含義，可以查詢手冊.
5.?索引的詳解（重點）
5.1.?索引創(chuàng)建
5.1.1.?主鍵索引的創(chuàng)建
主鍵索引的創(chuàng)建有兩種形式, 1.在創(chuàng)建表的時候，直接指定某列或者某幾列為主鍵,這時就有主鍵索引, 2. 添加表后，再指定主鍵索引
l?直接創(chuàng)建主鍵索引
注意:如果是自增, 該主鍵不能夠刪除

?
l?先創(chuàng)建表，再指定主鍵

?
增加主鍵
ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列名2..)
l?主鍵索引的特點
1.?一個表最多只能有一個主鍵
2.?一個主鍵可以指向多列(復(fù)合主鍵)
3.?主鍵索引的效率是最高，因此我們應(yīng)該給id,一般id是自增.
4.?主鍵索引列是不能重復(fù)，也不能為null
5.1.2.?唯一索引的創(chuàng)建
l?直接在創(chuàng)建表的時候，指定某列或某幾列為唯一索引

?
l?把表創(chuàng)建好后，再指定某列或者某幾列為唯一索引

?
說明: 使用 create unique index 指令，必須指定索引名。

?
說明: 使用alter table 指令，可以指定索引名，也可以不指定。
l?唯一索引的特點
1)?一張表可以有多個唯一索引
2)?唯一索引不能重復(fù)，但是如果你沒有指定not null ，唯一索引列可以為null,而且可以有多個.
3)?什么時候使用唯一索引，當(dāng)某列數(shù)據(jù)不會重復(fù)，才能使用
4)?唯一索引效率也很高，可以考慮優(yōu)先使用
5.1.3.?普通索引的創(chuàng)建
l?在創(chuàng)建表時指定索引,通過key或者index

?
l?把表創(chuàng)建好后，再指定某列或者某幾列為索引

?
l?添加普通索引(2種方式)

?

?
l?特點
1)?一張表中可以有多個普通索引，一個普通索引頁可以指向多列
2)?普通索引列的數(shù)據(jù)可以重復(fù)
3)?效率相對而言低.
5.2.?索引的查詢
l?desc 表名
l?show keys from 表名\G
l?show index from 表名\G
l?show indexes from 表名\G
5.3.?索引的修改
先刪除，再添加。
5.4.?索引的刪除
DROP ?INDEX ?索引名??ON ?表;
ALTER ?TABLE 表名 DROP INDEX 索引名；
?
5.5.?索引的注意事項

索引的缺點:
增刪改速度慢..
優(yōu)點:
查詢速度快…
?
建立索引一定要根據(jù)自己的需求來…
實例:
登錄用戶名是否適合建索引? 用戶名適合建立索引
?
操作日志:
用戶名 ???操作的哪個控制器的哪個方法 ???操作時間記錄
不合適建立索引..
6.?sql語句的優(yōu)化和正確使用索引
6.1.?對于創(chuàng)建的多列(復(fù)合)索引，只要查詢條件使用了最左邊的列，索引一般就會被使用
name ?email
?alter table xxx ?add index (name,email)
select from xx where name = ‘xxx’;
select from xx where email?= ‘xxx’;
?
?

?
說明: dname是左邊的列，因此我們發(fā)現(xiàn)使用到dname,就使用到索引，而下面的sql語句，沒有使用到索引。
6.2.?對于使用like的查詢，查詢?nèi)绻恰?aaa’‘_aa' 不會使用到索引‘a(chǎn)aa%’會使用到索引

?
說明: 在like語句中，如果 '' 中最前有 或者 %就使用不到索引，如果在中間或者最后有 或者 %可以使用到索引。
6.3.?如果條件中有or，則要求or的所有字段都必須有索引，否則不會使用索引

?
說明：因為 deptno 沒有索引，所以整個sql語句就沒有使用到索引。

?
如果在 deptno上也創(chuàng)建索引，就可以使用到索引了.
如果mysql認為全表掃描效率更高，就不會使用索引，而會全表掃描
6.4.?如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引

?

?
6.5.?有些情況下，可以使用連接來替代子查詢。因為使用join，MySQL不需要在內(nèi)存中創(chuàng)建臨時表
子查詢：select? from emp where deptno in (select deptno?from dept)
連接：select from emp left join dept on emp.deptno=dept.deptno where emp.deptno=dept.deptno
6.6.?管理員在導(dǎo)入大量數(shù)據(jù)，可以這樣提高速度
大批量插入數(shù)據(jù)(MySql管理員) 了解
對于MyISAM：
?alter table table_name disable keys;
執(zhí)行insert語句導(dǎo)入
alter table table_name enable keys;
對于Innodb：
1，?將要導(dǎo)入的數(shù)據(jù)按照主鍵排序
2，?set unique_checks=0,關(guān)閉唯一性校驗。
3，?set autocommit=0,關(guān)閉自動提交。
6.7.?如何選擇存儲引擎

?
l?如何選擇的原則
(1)?MyISAM:默認的MySQL存儲引擎。如果應(yīng)用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性要求不是很高。其優(yōu)勢是訪問的速度快。(尤其適合論壇的帖子/信息表/新聞/商品表表)
(2)?InnoDB：提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是對比MyISAM，寫的處理效率差一些并且會占用更多的磁盤空間(如果對安全要求高，則使用innodb)。[賬戶，積分,余額]
6.8.?如何選擇正確的數(shù)據(jù)類型
6.8.1.?在滿足需求的情況下盡量選擇小的類型.
6.8.2.?在精度要求高的應(yīng)用中，建議使用定點數(shù)來存儲數(shù)值，以保證結(jié)果的準確性。decimal 不要用float.
舉例:
?

?
說明: 這里我們看的?float(10,2) , 和?decimal(10,2) decimal 更精準。所以我們對精度高的列，要使用decimal 類型。
6.8.3.?對存儲引擎是MyISAM的表，要定時碎片整理
舉例說明：當(dāng)我們在users表中有大量數(shù)據(jù)時，我們delete 數(shù)據(jù)后，我們發(fā)現(xiàn)磁盤空間沒有回收，因此我們需要定時的進行碎片整理.如下:
創(chuàng)建表:

?
復(fù)制大量數(shù)據(jù)到同一個表中:

?

?
optimize: 該命令可以使表中的數(shù)據(jù)徹底從數(shù)據(jù)文件中刪除.

（本文由源碼時代技術(shù)老師原創(chuàng)發(fā)布，轉(zhuǎn)載請注明來源。）