常見資料庫的最佳化方式

標籤：

?

目錄

一、常見資料庫的最佳化方式：????1

二、提高效率，反三範式：????1

第一範式：????1

第二範式：????1

第三範式：????1

反三範式設計：????2

三、定位慢查詢。????3

四、索引的講解：????6

五、explain工具的講解????9

六、索引應用講解：????10

七、並發處理的鎖機制：????16

八、分表技術????17

九、分區技術????18

10、其他調優：????20

?

?

一、常見資料庫的最佳化方式：

我們之前講的靜態化，memcache主要是少查詢資料庫，或者不查詢資料庫的。一個網站，必經要查詢資料庫，索引也要對資料庫進行最佳化。

1、表的設計要符合三範式。

2、添加適當的索引，索引對查詢速度影響很大，必須添加索引。主鍵索引，唯一索引，普通索引，全文索引

3、添加適當預存程序，觸發器，事務等。

4、讀寫分離（主從資料庫）

5、對sql語句的一些最佳化，（查詢執行速度比較慢的sql語句）

6、分表分區

分表：把一張大表分成多張表。分區：把一張表裡面的分配到不同的地區儲存，

7、對mysql伺服器硬體的升級操作。

二、提高效率，反三範式：

第一範式：

原子性：表裡面的欄位不能再分割，只要是關係型資料庫，就天然的自動滿足第一範式。

關係型資料庫：（有行和列的概念）mysql,sql server,oracle,db2,infomix,sybase,postgresql

在設計時，先有庫-》表-》欄位-》具體記錄（內容）：在儲存資料時，要設計欄位。

非關係型資料庫（泛指nosql資料庫）：memcache/redis/momgodb/等

第二範式：

一個表中沒有完全相同的記錄，通過一個主鍵即能解決。

第三範式：

表中不能儲存冗餘資料，

?

反三範式設計：

?

?

三、定位慢查詢。

慢查詢：找出在一個網站中，查詢速度比較慢的語句，可以開啟一個記錄檔，記錄查詢速度比較慢的sql語句。在預設情況下，慢查詢日誌是關閉的，預設記錄時間是超過10秒 的sql語句。

1、以記錄慢查詢的方式來啟動mysql,

先關閉mysql,進入到mysql的安裝目錄。

關閉mysql服務：可以通過電腦-》管理->服務-》mysqld的服務名稱，單擊停止。

慢查詢：mysqld.exe --safe-mode --slow-query-log

{mysql的安裝目錄}>bin/mysqld.exe --safe-mode --slow-query-log

通過慢查詢日誌定位執行效率較低的SQL語句。慢查詢日誌記錄了所有執行時間超過long_query_time所設定的SQL語句。

執行：如下已經啟動：

?

2、查看慢查詢日誌的儲存路徑。

開啟了慢查詢日誌後，會建立一個慢查詢記錄檔。該記錄檔儲存在資料庫的目錄下，資料庫的目錄可以通過設定檔查看。

?

?

3、進程測試：

查看當前資料庫下慢查詢記錄時間：

show variables like ‘long_query_time‘;

修改慢查詢時間：

set long_query_time=2;

通過如下的一個函數來進行測試：

benchmark(count,expr)函數可以測試執行count次expr操作需要的時間

一般情況下，出現查詢比較慢的語句，是沒有添加索引導致的。

沒有添加索引前：

添加索引之後：

四、索引的講解：

建立的測試表：

create table user(

id int primary key auto_increment,

name varchar(32) not null default ‘‘,

age tinyint unsigned not null default 0,

email varchar(32) not null default ‘‘,

classid int not null default 1

)engine myisam charset utf8;

insert into user values(null,‘xiaogang‘,12,‘[email protected]‘,4),

(null,‘xiaohong‘,13,‘[email protected]‘,2),

(null,‘xiaolong‘,31,‘[email protected]‘,2),

(null,‘xiaofeng‘,22,‘[email protected]‘,3),

(null,‘xiaogui‘,42,‘[email protected]‘,3);

? ?

建立一個班級表：

create table class(

id int not null default 0,

classname varchar(32) not null default ‘‘

)engine myisam charset utf8;

insert into class values(1,‘java‘),(2,‘.net‘),(3,‘php‘),(4,‘c++‘),(5,‘ios‘);

?

1、主鍵索引

可以在建立表的添加create table emp(id int primary key)

可以在建立完表之後，添加：alter table tablename add primary key(列1,列2)

主鍵索引的特點：

（1）一個表中最多隻有一個主鍵索引

（2）一個主鍵索引可以指向多個列

（3）主鍵索引的列，不能有重複的值，也不能有null

（4）主鍵索引的效率高。

?

2、唯一索引

可以在建立表的時候添加：create table emp(name varchar(32) unique)

在建完表之後，添加：

alter table tablename add unique [索引名](列名)

唯一索引的特點：

（1）一個表中可以有多個唯一索引

（2）一個唯一索引可以指向多個列 ，

比如alter table tablename add unique [索引名](列1，列2)

（3）如果在唯一索引上，沒有指定not null，則該列可以為空白，同時可以有多個null,

（4）唯一索引的效率較高。

?

3、普通索引

使用普通索引主要是提高查詢效率

?

添加alter table tablename add index [索引名]（列1，列3）

?

4、全文索引

mysql內建的全文索引mysql5.5不支援中文，支援英文，同時要求表的儲存引擎是myisam。如果希望支援中文，有兩個方案，

（1）使用aphinx中文版coreseek (來替代全文索引)

（2）外掛程式mysqlcft。

5、查看索引

（1）show index from 表名

（2）show indexes from 表名

（3）show keys from 表名

（4）desc 表名

6、刪除索引

（1）主鍵索引的刪除：

alter table tablename drop primary key;要注意：在刪除主鍵索引時，要首先去掉auto_increment屬性。

?

（2）唯一索引的刪除

alter table tablename drop index 唯一索引的名字

（3）普通索引的刪除：

alter table tablename drop index 普通索引的名字

?

7、添加索引主要的問題：

（1）較頻繁的作為查詢條件欄位應該建立索引

????select * from emp where empno = 1

????唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件

????select * from emp where sex = ‘男‘

????更新非常頻繁的欄位不適合建立索引

????select * from emp where logincount = 1

（2）不會出現在WHERE子句中欄位不該建立索

索引是由代價的，雖然是查詢速度提高了，但是，會影響增該刪的效率。而且索引檔案會佔用空間。

?

?

五、explain工具的講解

?

該工具能夠分析sql執行效率，但是並不執行sql語句。主要是查看sql語句是否用到索引。

文法：explain sql語句\G 或 desc sql語句\G

使用索引時：

?

沒有使用索引時：

?

explain工具的參數說明：

會產生如下資訊：
select_type:表示查詢的類型。
table:輸出結果集的表
type:表示表的連線類型
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引欄位的長度
rows:掃描出的行數(估算的行數)
Extra:執行情況的描述和說明

?

六、索引應用講解：

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

mysql> alter table user add index (name,email);

Query OK, 5 rows affected (0.08 sec)

Records: 5 Duplicates: 0 Warnings: 0

?

?

2、對於使用like的查詢，查詢如果是"%XXX",不會使用到索引，‘XXX%‘會使用到索引。

?

?

?

要注意：在有些情況下，還是會用到like查詢，比如通過歌詞搜尋歌名，通過劇情搜尋電影名稱。藉助於工具，sphinx裡面的coreseek軟體。

?

3、如果條件中有or，則要求or的索引欄位都必須有索引，否則不能用到索引。

該email添加索引後，在測試，會用到索引

4、如果列類型是字串，一定要在條件中將資料使用引號引用起來，否則不使用索引。

?

5、最佳化group by語句

預設情況下， mysql對所有的group by col1,col2進行排序。這與在查詢中指定order by col1,col2類型，如果查詢中包括group by 但使用者想要避免排序結果的消耗，則可以使用order by null禁止排序。

?

?

6、當取出的資料量超過表中資料的20%，最佳化器就不會使用索引，而是全表掃描。

掃描的行數太多了，最佳化器認為全表掃描比索引來的塊。

?

7、查看索引的使用方式

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

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

8、對應大批量插入資料，需要注意的：

大批量插入資料(MySql管理員) 瞭解
對於MyISAM：

先禁用索引：
alter table table_name disable keys;
loading data//insert語句; 執行插入語句

執行完成插入語句後，開啟索引，統一添加索引。
alter table table_name enable keys;

?

對於Innodb：
1，將要匯入的資料按照主鍵排序
2，set unique_checks=0,關閉唯一性校正。
3，set autocommit=0,關閉自動認可。

七、並發處理的鎖機制：

比如執行如下操作：

（1）從資料庫中取出id值，

（2）進行加1操作。

（3）修改完成後，再儲存到資料庫中。

比如原來 id的值為100,==》101

以上步驟執行100次，最後變成200

?

有兩個使用者同時執行的話。

a使用者：

100

101

?

b使用者：

100

101

?

通過鎖機制來進行解決，

鎖機制：在執行時，只有一個使用者獲得鎖，其他使用者處於阻塞狀態，需要等待解鎖。

mysql?的鎖有以下幾種形式：

表級鎖：開銷小，加鎖快，發生鎖衝突的機率最高，並發度最低。myisam引擎屬於這種類型。

行級鎖：開銷大，加鎖慢，發生鎖衝突的機率最低，並發度也最高。innodb屬於這種類型。

1、表鎖的示範；

對myisam表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其他進程的操作。

表添加讀鎖後，其他進程對該表只能查詢操作，修改時會被阻塞。

當前進程，能夠執行查詢操作，不能執行修改操作。不能對沒有鎖定的表進行操作。

鎖表的文法：

lock table 表名 read|write

也可以鎖定多個表，文法是：lock table 表1 read|wirte,表2 read|wirte

對myisam表的寫操作（加寫鎖），會阻塞其他進程對鎖定表的任何操作，不能讀寫，

表加寫鎖後，則只有當前進程對鎖定的表，可以執行任何操作。其他進程的操作會被阻塞。

?

2、行鎖的示範

innodb儲存引擎是通過給索引上的索引項目加鎖來實現的，這就意味著：只有通過索引條件檢索資料，innodb才會使用行級鎖，否則，innodb使用表鎖。

行鎖的文法：

begin

sql語句

commit

開啟行鎖後，當前進程在針對某條記錄執行操作時，其他進程不能操作和當前進程相同id的記錄。

php裡面有檔案鎖，在實際的項目中多數使用檔案鎖，因為表鎖，會阻塞，當對一些表添加寫鎖後，其他進程就不能操作了。這樣會阻塞整個網站，會拖慢網站的速度。

?

類似的面試題：

一件商品，庫存量還有一件，這時有兩個使用者同時請求下訂單，如何防止都下訂單成功，卻沒有貨發。

八、分表技術

分表：把一個大表分成幾個小表：

垂直分割：

在dedecms裡面，垂直分割：

在一個資料庫中想要儲存各種資料，比如說文章資料，電影，音樂，商品資料，

內容主表+附加表：

內容主表：儲存各種資料的一些公用資訊，比如資料的名稱，添加時間等，

可以使用多個附加表，附加表格儲存體一些資料的獨特的資訊。

主要原因：是內容主表裡面的資料訪問比較頻繁。

?

水平分割：

通過id模數

?

九、分區技術

就是把一個表格儲存體到磁碟不同地區，仍然是一張表。

1、基本的概念：

mysql5.1後有4種分區類型：

（1）Range（範圍）–這種模式允許將資料劃分不同範圍。例如可以將一個表通過年份劃分成若干個分區。

（2）List（預定義列表）–這種模式允許系統通過預定義的列表的值來對資料進行分割

（3）Hash（雜湊）–這中模式允許通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分區。例如可以建立一個對錶主鍵進行分區的表。

（4）Key（索引值）-上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。

2 range分區：

假如你建立了一個如下的表，該表儲存有20家超市的職員記錄，這20家超市的編號從1到20.如果你想將其分成4個小分區，可以採用range分區，建立的資料表如下。

建立range分區文法：
create table emp(

id int not null,

name varchar(32) not null default ‘‘ comment ‘職員的名稱‘,

store_id int not null comment ‘超市的編號範圍是1-20‘

)engine myisam charset utf8

partition by range(store_id)(

partition p0 values less than(6), //是store_id的值小於6的儲存地區。

partition p1 values less than(11), //是store_id的值大於等於6小於11的儲存地區。

partition p2 values less than(16),

partition p3 values less than(21)

)

insert into emp values(1,‘楊過‘,1)--à資料是儲存到p0區

insert into emp values(23,‘小龍女‘,15)--à資料是儲存到p2區

?

insert into emp values(100,‘李莫愁‘,11)=à資料是儲存到p2區。

?

測試使用取出資料時是否用到分區：

?

?

在取出資料時，條件中必須partition by range(store_id)，range裡面的欄位。

3、list分區與range分區有類似的地方，

例子：假如你建立一個如下的一個表，該表儲存有20家超市的職員記錄，這20家超市的編號從1到20.而這20家超市分布在4個有經銷權的地區，如下表所示：

create table emp(

id int not null,

name varchar(32) not null default ‘‘,

store_id int not null

)

partition by list(store_id)(

partition p0 values in(5,6,7,8),

partition p1 values in(11,3,12,11),

partition p2 values in(16),

partition p3 values in(21)

)

注意：在使用分區時，where後面的欄位必須是分區欄位，才能使用到分區。

?

4、分區表的限制；

（1）只能對資料表的整型列進行分區，或者資料列可以通過分區函數轉化成整型列

（2）最大分區數目不能超過1024

（3）如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內

（4）按日期進行分區很非常適合，因為很多日期函數可以用。但是對於字串來說合適的分區函數不太多 。

10、其他調優：

1、選擇合適的儲存引擎 （myisam innodb）

• MyISAM:預設的MySQL儲存引擎。如果應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，並且對事務的完整性要求不是很高。其優勢是訪問的速度快。(尤其適合論壇的文章表)
• InnoDB：提供了具有提交、復原和崩潰恢複能力的事務安全。但是對比MyISAM，寫的處理效率差一些並且會佔用更多的磁碟空間(如果對安全要求高，則使用innodb)。[賬戶，積分]
• Memory/heap [一些訪問頻繁，變化頻繁，又沒有必要入庫的資料 ：比如使用者線上狀態]
• 說明: memory表的資料都在記憶體中，因此操作速度快，但是缺少是當mysql重啟後，資料丟失，但表的結構在.
• 註：從mysql5.5.x開始，預設的儲存引擎變更為innodb，innodb是為處理巨大資料量時擁有最大效能而設計的。它的 cpu效率可能是任何其他基於磁碟的關聯式資料庫引擎所不能匹敵的。

2、資料類型的選擇

（1）在精度要求高的應用中，建議使用定點數來儲存數值，以保證結果的準確性。decimal 不要用float

（2）要用於儲存手機號，哪個類型比較合適。假如我們要用char(11),如果字元集是utf8 則佔用多少個位元組。11*3==33,如果是gbk字元集則佔用11*2=22個位元組，

如果用bigint型儲存，則佔用8個位元組，

（3）如果要儲存ip地址。假如用char(15)è佔用很多位元組，能否用整型來儲存呢？

可以通過一個函數，把ip地址轉換成整數。可以使用int來儲存

inet_aton()：把ip地址轉換成整數。

inet_ntoa()：把整數轉換成ip地址。

取出時：

（4）根據需求選擇最小整數類型。比如使用者線上狀態：離線，線上，離開，忙碌，隱式等，可以採用0,1,2,3,5來表示，沒有必要用char()或varchar()型來儲存字串。

3、myisam表的定時維護

對於myisam 儲存引擎而言，需要定時執行optimize table 表名，通過optimize table語句可以消除刪除和更新造成的磁碟片段，從而減少空間的浪費。

create table temp2( id int) engine=MyISAM;

insert into temp2 values(1);

在之前資料的容量：

文法格式：optimize table 表名：

清理完成片段之後。

常見資料庫的最佳化方式