mysql資料庫的最佳化技術

標籤：

1. 表的設計合理化（遵從3NF）<3範式>

• 1NF:表的列具有原子性，不可再分解（列的資訊不能分解，只要是關係型的資料庫就自動滿足1NF）
• 2NF:表中的記錄是唯一的，就滿足2NF(通常我們設計一個主鍵來實現)
• 3NF:表中不要有冗餘資料（表中的資訊能被推倒出來，就不應該單獨成列）

在一對多的情況，為了提高效率，可能會在1中設計欄位提高效率（違反3NF）

　　2. 添加適當的索引（index）[四種：主鍵，唯一，普通，全文]

　　　　（1）主鍵索引的添加：當一張表，把某個列設為主鍵時，則該列就是主鍵索引

　　　　　　create table aaa

　　　　　　　( id int unsigned primary key auto_increment,

　　　　　　　　name varchar(32) not null default ‘ ‘); 

　　　　　　這時id列就是主鍵索引。

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

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

　　　　　　舉例：

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

　　　　　　　　alter table bbb add pirmary key (id);

　　　　(2)唯一索引的添加：　　　　　

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),UNIQUE (Id_P))CREATE TABLE Persons(Id_P int NOT NULL UNIQUE,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255))

　　　　（3）普通索引的建立：普通索引的建立，是先建立表，然後再建立普通索引。

　　　　　　比如：create table ccc(id int unsigned,name varchar(32));

　　　　　　　　create index 索引名 on 表（列）

　　　　（4）全文索引的建立：主要針對檔案，文本的檢查，比如文章，全文索引針對MyISAM引擎有用。　　　　

CREATE TABLE articles(Id_P int NOT NULL UNIQUE,title varchar(200),body text,fulltext(title,body))egin=myisam charset utf8;

　　　　　　如何使用全文索引？

　　　　　　錯誤用法：select *from articles where body like ‘%mysql%‘;[這樣不會使用到全文索引]

　　　　　　正確用法：select *from articles where match(title,body) against (‘database‘);　　

　　　　說明：

　　　　　　　<1>在mysql中fulltext索引只針對myisam生效。

　　　　　　　<2>針對英文生效---->sphinx技術處理中文（coreseek）

　　　　　　　<3>使用方法 match(欄位名) aginst(‘關鍵字‘)

　　　　　　　<4>全文索引有一個停止詞，因為在一個文本中，建立索引是一個無窮大的數，因此，對一些常用詞和字元，就不會建立，這些詞稱為停止詞。

 

　　　　小結：查詢索引命令

　　　　　　desc 表表名；

　　　　　　show index(es) from 表名；

　　　　　　show keys from 表名；

　　3.分表技術[水平分割，垂直分割]

　　　　（1）水平分割：

　　　　　　舉例：如QQ登入表（假設使用者有5億）

　　　　　　　　id    name   pw

　　　　　　　　1      aa       aa

　　　　　　　　2      bb       bb　　

　　　　　　如果驗證5個億的資料驗證，這樣的效率過低。假設把資料分成3個表儲存，由id%3來決定放入和取出的表。三個表的結構和大表相同。

　　　　（2）垂直分割：

　　　　　　　某些表的某些欄位，在查詢的時候，並不關心，但是資料量很大，我們建議把這些大欄位單獨放到另一張表裡，從而提高效率，

　　　　　　　　但是必須與原表保持關聯。

　　4.讀寫（寫操作包括update、delete、add）分離

　　5.預存程序（模組化編程，可以提高速度）

　　6.對mysql配置最佳化[配置最大並發數，調整緩衝大小--->my.ini]

　　7.mysql伺服器硬體升級

　　8.定時清除不需要的資料，定時進行磁碟重組。

　　　　　　

 

 

mysql資料庫的最佳化技術