mysql最佳化方案總結

標籤：

u       Mysql資料庫的最佳化技術

對mysql最佳化時一個綜合性的技術，主要包括

a: 表的設計合理化(符合3NF)

b: 添加適當索引(index) [四種: 普通索引、主鍵索引、唯一索引unique、全文索引]

c: 分表技術(水平分割、垂直分割)

d: 讀寫[寫: update/delete/add]分離

e: 預存程序 [模組化編程，可以提高速度]

f: 對mysql配置最佳化 [配置最大並發數my.ini, 調整緩衝大小 ]

g: mysql伺服器硬體升級

h: 定時的去清除不需要的資料,定時進行磁碟重組(MyISAM)

 

 

u       什麼樣的表才是符合3NF (範式)

表的範式，是首先符合1NF, 才能滿足2NF , 進一步滿足3NF

 

1NF: 即表的列的具有原子性,不可再分解，即列的資訊，不能分解, 只有資料庫是關係型資料庫(mysql/oracle/db2/informix/sysbase/sql server)，就自動的滿足1NF

 

? 資料庫的分類

關係型資料庫: mysql/oracle/db2/informix/sysbase/sql server

非關係型資料庫: (特點: 物件導向或者集合)

NoSql資料庫: MongoDB(特點是面向文檔)

 

 

2NF: 表中的記錄是唯一的, 就滿足2NF, 通常我們設計一個主鍵來實現

 

3NF: 即表中不要有冗餘資料, 就是說，表的資訊，如果能夠被推匯出來，就不應該單獨的設計一個欄位來存放. 比如下面的設計就是不滿足3NF:

                       

 

 

 

 

反3NF : 但是，沒有冗餘的資料庫未必是最好的資料庫，有時為了提高運行效率，就必須降低範式標準，適當保留冗餘資料。具體做法是： 在概念資料模型設計時遵守第三範式，降低範式標準的工作放到物理資料模型設計時考慮。降低範式就是增加欄位，允許冗餘。

 

 

 

 

u       Sql語句本身的最佳化

 

問題是： 如何從一個大項目中，迅速的定位執行速度慢的語句. (定位慢查詢)

 

①     首先我們瞭解mysql資料庫的一些運行狀態如何查詢(比如想知道當前mysql啟動並執行時間/一共執行了多少次select/update/delete.. / 當前串連)

 

show status

 

常用的:

show status like ‘uptime’ ;

show  stauts like ‘com_select’  show stauts like ‘com_insert’ ...類推 update  delete

 

? show [session|global] status like .... 如果你不寫  [session|global] 預設是session 會話，指取出當前視窗的執行，如果你想看所有(從mysql 啟動到現在，則應該 global)

 

show status like ‘connections’;

//顯示慢查詢次數

show status like ‘slow_queries’;

②     如何去定位慢查詢

 

構建一個大表(400 萬)-> 預存程序構建

 

預設情況下，mysql認為10秒才是一個慢查詢.

 

l       修改mysql的慢查詢.

 

show variables like ‘long_query_time’ ; //可以顯示當前慢查詢時間

set long_query_time=1 ;//可以修改慢查詢時間

 

 

構建大表->大表中記錄有要求, 記錄是不同才有用，否則測試效果和真實的相差大.

 

建立:

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

 

為了預存程序能夠正常執行，我們需要把命令執行結束符修改

delimiter $$

 

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 $$

 

如果希望在程式中使用，是Ok!

 

 

 

 

建立一個預存程序

 

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 $$

 

#調用剛剛寫好的函數, 1800000條記錄,從100001號開始

call insert_emp(100001,4000000);

 

③     這時我們如果出現一條語句執行時間超過1秒中，就會統計到.

④     如果把慢查詢的sql記錄到我們的一個日誌中

在預設情況下，我們的mysql不會記錄慢查詢，需要在啟動mysql時候，指定記錄慢查詢才可以

bin\mysqld.exe - -safe-mode  - -slow-query-log [mysql5.5 可以在my.ini指定]

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

 

 

先關閉mysql,再啟動, 如果啟用了慢查詢日誌，預設把這個檔案放在

my.ini 檔案中記錄的位置

#Path to the database root

datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

 

⑤     測試,可以看到在日誌中就記錄下我們的mysql慢sql語句.

 

 

最佳化問題.

通過 explain 語句可以分析，mysql如何執行你的sql語句, 這個工具的使用放一下，一會說.

 

添加索引 【小建議: 】

 

u       四種索引(主鍵索引/唯一索引/全文索引/普通索引)

 

1. 添加

 

1.1主鍵索引添加

當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引

create table aaa

(id int unsigned primary key auto_increment ,

name varchar(32) not null defaul ‘’);

這是id 列就是主鍵索引.

 

如果你建立表時，沒有指定主鍵索引，也可以在建立表後，在添加, 指令:

 

alter table 表名 add primary key (列名);

 

舉例:

create table bbb (id int , name varchar(32) not null default ‘’);

 

alter table bbb add primary key (id);

 

 

1.2普通索引

一般來說，普通索引的建立，是先建立表，然後在建立普通索引

比如:

create table ccc(

id int unsigned,

name varchar(32)

)

 

create index 索引名 on 表 (列1,列名2);

 

1.3建立全文索引

 

全文索引，主要是針對對檔案，文本的檢索, 比如文章, 全文索引針對MyISAM有用.

 

建立 ：

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%’; 【不會使用到全文索引】

證明:

explain  select * from articles where body like ‘%mysql%’

 

正確的用法是:

select * from articles where match(title,body) against(‘database’); 【可以】

 

? 說明:

1. 在mysql中fulltext 索引只針對 myisam生效
2. mysql自己提供的fulltext針對英文生效->sphinx (coreseek) 技術處理中文
3. 使用方法是 match(欄位名..) against(‘關鍵字’)
4. 全文索引一個 叫 停止詞,  因為在一個文本中，建立索引是一個無窮大的數，因此，對一些常用詞和字元，就不會建立，這些詞，稱為停止詞.

 

1.4唯一索引

①當表的某列被指定為unique約束時，這列就是一個唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

 

這時, name 列就是一個唯一索引.

 

unique欄位可以為NULL,並可以有多NULL, 但是如果是具體內容，則不能重複.

 

主鍵欄位，不能為NULL,也不能重複.

 

②在建立表後，再去建立唯一索引

create table eee(id int primary key auto_increment, name varchar(32));

 

create unique index 索引名  on 表名 (列表..);

 

 

 

1. 查詢索引

 

desc 表名 【該方法的缺點是：　不能夠顯示索引名.】

show index(es) from 表名

show keys from 表名

 

1. 刪除

 

alter table 表名 drop index 索引名;

 

如果刪除主鍵索引。

alter table 表名 drop primary key       [這裡有一個小問題]

 

 

 

1. 修改

 

先刪除，再重新建立.

  

u       索引使用的注意事項

 

索引的代價:

1. 佔用磁碟空間
2. 對dml操作有影響，變慢

u       在哪些列上適合添加索引?

 

 

總結: 滿足以下條件的欄位，才應該建立索引.

a: 肯定在where條經常使用 b: 該欄位的內容不是唯一的幾個值(sex) c: 欄位內容不是頻繁變化.

 

 

u       使用索引的注意事項

 

把dept表中，我增加幾個部門:

 

alter table dept add index my_ind (dname,loc); //  dname 左邊的列,loc就是右邊的列

 

說明，如果我們的表中有複合索引(索引作用在多列上)， 此時我們注意:

1，  對於建立的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。

 

explain select * from dept where loc=‘aaa‘\G

就不會使用到索引

 

2，對於使用like的查詢，查詢如果是  ‘%aaa’ 不會使用到索引

       ‘aaa%’ 會使用到索引。

 

比如: explain select * from dept where dname like ‘%aaa‘\G

不能使用索引，即，在like查詢時，關鍵的 ‘關鍵字’ , 最前面，不能使用 % 或者 _這樣的字元.， 如果一定要前面有變化的值，則考慮使用 全文索引->sphinx.

 

1. 如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有欄位，都必須建立索引, 我們建議大家盡量避免使用or 關鍵字

 

select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

 

1. 如果列類型是字串，那一定要在條件中將資料使用引號引用起來。否則不使用索引。(添加時,字串必須’’), 也就是，如果列是字串類型，就一定要用 ‘’ 把他包括起來.

 

1. 如果mysql估計使用全表掃描要比使用索引快，則不使用索引。

 

 

 

 

 

 

 

 

explain 可以協助我們在不真正執行某個sql語句時，就執行mysql怎樣執行，這樣利用我們去分析sql指令.

 

 

u       如何查看索引使用的情況:

show status like ‘Handler_read%’;

 

大家可以注意：
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。

       handler_read_rnd_next:這個值越高，說明查詢低效。

 

 

u       sql語句的小技巧

 

1. 在使用group by 分組查詢是，預設分組後，還會排序，可能會降低速度.

 

比如:

 

在group by 後面增加 order by null 就可以防止排序.

1. 有些情況下，可以使用串連來替代子查詢。因為使用join，MySQL不需要在記憶體中建立暫存資料表。

 

select * from dept, emp where dept.deptno=emp.deptno; [簡單處理方式]

 

select * from dept left join emp on dept.deptno=emp.deptno;  [左外串連，更ok!]

 

u       如何選擇mysql的儲存引擎

 

在開發中，我們經常使用的儲存引擎 myisam / innodb/ memory

myisam 儲存: 如果表對事務要求不高，同時是以查詢和添加為主的，我們考慮使用myisam儲存引擎. ,比如 bbs 中的 發帖表，回複表.

 

INNODB 儲存: 對事務要求高，儲存的資料都是重要資料，我們建議使用INNODB,比如訂單表，帳號表.

 

 

問 MyISAM 和 INNODB的區別

1. 事務安全

2. 查詢和添加速度

3. 支援全文索引

4. 鎖機制

5. 外鍵 MyISAM 不支援外鍵， INNODB支援外鍵. (在PHP開發中，通常不設定外鍵，通常是在程式中保證資料的一致)

 

 

 

Memory 儲存，比如我們資料變化頻繁，不需要入庫，同時又頻繁的查詢和修改，我們考慮使用memory, 速度極快.

 

 

 

 

 

u       如果你的資料庫的儲存引擎是myisam,請一定記住要定時進行磁碟重組

 

舉例說明:

create table test100(id int unsigned ,name varchar(32))engine=myisam;

 

insert into test100 values(1,’aaaaa’);

insert into test100 values(2,’bbbb’);

insert into test100 values(3,’ccccc’);

 

我們應該定義對myisam進行整理

optimize table test100;

 

mysql_query(“optimize tables $表名”);

 

mysql最佳化方案總結