mysql資料庫最佳化小結，mysql資料庫小結

mysql資料庫最佳化小結，mysql資料庫小結

一、常見資料庫的最佳化操作

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的服務名稱，單擊停止。 

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

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

 

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

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

慢查詢日誌存放在mysql的data目錄下. 在my.ini設定檔有data目錄

#Pathto the database root

datadir="C:/Documentsand Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“

 

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 uservalues(null,'xiaogang',12,'gang@sohu.com',4),(null,'xiaohong',13,'hong@sohu.com',2),(null,'xiaolong',31,'long@sohu.com',2),(null,'xiaofeng',22,'feng@sohu.com',3),(null,'xiaogui',42,'gui@sohu.com',3);

建立一個班級表：

create table class(   id int not null default 0,   classname varchar(32) not null default '')engine myisam charset utf8;insert into classvalues(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios');

1、主鍵索引

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

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

主鍵索引的特點：

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

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

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

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

 

2、唯一索引

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

在建完表之後，添加：

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

唯一索引的特點：

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

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

比如alter tabletablename 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、對應大批量插入資料

對於MyISAM：

先禁用索引：

alter table table_name disable keys;

loading data//insert語句; 執行插入語句

執行完成插入語句後，開啟索引，統一添加索引。

alter table table_name enable keys;

 

對於Innodb：

1，將要匯入的資料按照主鍵排序

2，setunique_checks=0,關閉唯一性校正。

3，setautocommit=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

也可以鎖定多個表，文法是：locktable 表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,    namevarchar(32) not null default '' comment ‘職員的名稱’,   store_id int not null comment ‘超市的編號範圍是1-20’)engine myisam charset utf8partition 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區。

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

在取出資料時，條件中必須partitionby 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）按日期進行分區很非常適合，因為很多日期函數可以用。但是對於字串來說合適的分區函數不太多 。

十、其他

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語句可以消除刪除和更新造成的磁碟片段，從而減少空間的浪費。

文法格式：optimizetable  表名

清理完成片段之後會減少空間的浪費！