mysql性能優(yōu)化主要有哪些問題

下面講講關(guān)于mysql性能優(yōu)化主要有哪些問題，文字的奧妙在于貼近主題相關(guān)。所以，閑話就不談了，我們直接看下文吧，相信看完mysql性能優(yōu)化主要有哪些問題這篇文章你一定會(huì)有所受益。                                                         

A、表的是設(shè)計(jì)合理化(符合 3范式)

B、添加適當(dāng)?shù)乃饕?index)[四種：普通索引，主鍵索引，唯一索引，unique，全文索引]

C、分表技術(shù)(水平分割，垂直分割)

D、讀寫[寫：update/delete/add]分離

E、存儲(chǔ)過程[模塊化編程，可以提高速度]

F、對(duì)mysql配置優(yōu)化[配置最大并發(fā)數(shù)，my.ini調(diào)整緩存大小]

G、Mysql云服務(wù)器引薦升級(jí)

H、定時(shí)的去清楚不需要的數(shù)據(jù)，定時(shí)進(jìn)行碎片整理

1、數(shù)據(jù)庫表的設(shè)計(jì)

第一范式：1NF是對(duì)屬性的原子性約束，要求屬性(列)具有原子性，不可再分解；(只要是關(guān)系型數(shù)據(jù)庫都滿足1NF)

第二范式：2NF是對(duì)記錄的惟一性約束，要求記錄有惟一標(biāo)識(shí)，即實(shí)體的惟一性；

第三范式：3NF是對(duì)字段冗余性的約束，它要求字段沒有冗余。沒有冗余的數(shù)據(jù)庫設(shè)計(jì)可以做到。

2、sql優(yōu)化的一般步驟

操作步驟:

1、通過show status命令了解各種SQL的執(zhí)行頻率。

2、 定位執(zhí)行效率較低的SQL語句-（重點(diǎn)select）

3、 通過explain分析低效率的SQL語句的執(zhí)行情況

4、確定問題并采取相應(yīng)的優(yōu)化措施

MySQL通過使用show [session|global] status 命令可以提供云服務(wù)器狀態(tài)信息。

session來表示當(dāng)前的連接的統(tǒng)計(jì)結(jié)果，global來表示自數(shù)據(jù)庫上次啟動(dòng)至今的統(tǒng)計(jì)結(jié)果。默認(rèn)是session級(jí)別的。

show status like ‘Com_%’;

其中Com_XXX表示XXX語句所執(zhí)行的次數(shù)。Eg:Com_insert,Com_Select…
重點(diǎn)注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個(gè)參數(shù)，可以容易地了解到當(dāng)前數(shù)據(jù)庫的應(yīng)用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執(zhí)行比例是多少。
Connections：試圖連接MySQL云服務(wù)器的次數(shù)
Uptime：云服務(wù)器工作的時(shí)間（單位秒）
Slow_queries：慢查詢的次數(shù) (默認(rèn)是慢查詢時(shí)間10s)

Show status like‘Handler_read%’使用查詢的次數(shù)

定位慢查詢：

在默認(rèn)的情況下mysql是不記錄滿查詢?nèi)罩镜?，需要在啟?dòng)的時(shí)候指定

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

\bin\mysqld.exe- -log-slow-queries=d:bac.log

具體操作如下：

如果啟用了慢查詢，默認(rèn)存儲(chǔ)在mysql.ini文件的此處

1、重啟mysql,找到datadir的路勁，使用cmd進(jìn)入到data的上級(jí)目錄

2、運(yùn)行命令\bin\mysqld.exe –safe-mode  –slow-query-log(注意執(zhí)行前先關(guān)閉mysql服務(wù))

3、生成的日志文件記錄著所有的記錄信息

顯示慢查詢的時(shí)間：Show variables like ‘long_query_time’;

重新設(shè)置滿查詢的時(shí)間：Set long_query_time=2;

修改命令結(jié)束符：(為了存儲(chǔ)過程能夠正常執(zhí)行，我們需要把命令結(jié)束符號(hào)進(jìn)行修修改)

Delimiter $$

如何把慢查詢的sql語句記錄到我們的日志中(默認(rèn)情況下mysql是不會(huì)記錄的，需要在啟動(dòng)mysql的時(shí)候，指定慢查詢的)。

3、索引

?索引的類型：

★四種索引①主鍵索引②唯一索引③普通索引④全文索引

一、添加

1.1主鍵索引添加

當(dāng)把一張表的某列設(shè)置為主鍵的時(shí)候，則該列就是主鍵索引。

Createtable aaa(id int unsigned primary key auto_increment,

name varchar(32) not null default);

1.2普通索引

一般來說，普通索引是先創(chuàng)建表，然后創(chuàng)建普通索引。

比如：

Createindex索引名 from表名

1.3創(chuàng)建全文索引

全文索引，主要是針對(duì)文件，比如文章的索引全文索引針對(duì)MyISAM有用，針對(duì)innodb沒有用

Create table articles(

Id int unsignedauto_increment not null primary key,

Title varchar(20),

Body text,

Fulltext (title,body)

)engine=myisam charsetutf8;

錯(cuò)誤用法：

Select * from articles where body like ‘%mysql%’[不會(huì)使用到全文索引]

證明：

Explain select * from articles body like ‘%mysql%’;

正確的用法：

Select * from article wherematch(title,body)against(‘database’);[可以]

說明：

1、在mysql中fulltest索引值針對(duì)myisam生效

2、針對(duì)英文生效，àsphinx(coreseek)技術(shù)處理中文

3、使用的方法，match(字段名，…)against(‘關(guān)鍵詞’)

4、全文索引一個(gè)叫停止詞。因?yàn)樵谝粋€(gè)文本中，創(chuàng)建索引的是一個(gè)無窮大的書，因此，對(duì)一些常用詞和字符就不會(huì)創(chuàng)建，這些詞，稱之為停止詞

1.4創(chuàng)建唯一索引

當(dāng)表的某列被指定為unique約束時(shí)，這列就是唯一索引

第一種、Create table ddd(id int primary keyauto_increment,name varchar(32) unique);

這時(shí)，name默認(rèn)就是唯一索引

第二種、create table eee(id int primary keyauto_increment,name varchar(32));

Createunique index索引名 on表名(列名)

簡(jiǎn)單的說：PRIMARY KEY=UNIQUE+NOT NULL

Unique字段可以為null，并可以有多個(gè)null,但是如果是具體內(nèi)容，則不能重復(fù)

主鍵字段，不能為null,也不能重復(fù)

二、查詢

1．Desc表名[該方法的缺點(diǎn)，不能夠現(xiàn)實(shí)索引名]

2．Show index from表名;

select index from表名\G

3．show keys from表名

三、刪除

Altertable 表名 drop  index 索引名，

Altertable 表名 drop primary key。(刪除主鍵索引名)

四、修改

先刪除，在全部

二、針對(duì)SQL編寫導(dǎo)致的慢 SQL，優(yōu)化起來還是相對(duì)比較方便的。正如上一節(jié)提到的正確的使用索引能加快查詢速度，那么我們?cè)诰帉?SQL 時(shí)就需要注意與索引相關(guān)的規(guī)則：

1.字段類型轉(zhuǎn)換導(dǎo)致不用索引，如字符串類型的不用引號(hào)，數(shù)字類型的用引號(hào)等，這有可能會(huì)用不到索引導(dǎo)致全表掃描；

2.mysql 不支持函數(shù)轉(zhuǎn)換，所以字段前面不能加函數(shù)，否則這將用不到索引；

3.不要在字段前面加減運(yùn)算；

4.字符串比較長(zhǎng)的可以考慮索引一部份減少索引文件大小，提高寫入效率；

5.like % 在前面用不到索引；

6.根據(jù)聯(lián)合索引的第二個(gè)及以后的字段單獨(dú)查詢用不到索引；

7.不要使用 select *；

8.排序請(qǐng)盡量使用升序 ;

9.or 的查詢盡量用 union 代替（Innodb）；

10.復(fù)合索引高選擇性的字段排在前面；

11.order by / groupby 字段包括在索引當(dāng)中減少排序，效率會(huì)更高。

除了上述索引使用規(guī)則外，SQL 編寫時(shí)還需要特別注意一下幾點(diǎn)：

1.盡量規(guī)避大事務(wù)的 SQL，大事務(wù)的 SQL 會(huì)影響數(shù)據(jù)庫的并發(fā)性能及主從同步；

2.分頁語句 limit 的問題；

3.刪除表所有記錄請(qǐng)用 truncate，不要用 delete；

4.不讓 mysql 干多余的事情，如計(jì)算；

5.輸寫 SQL 帶字段，以防止后面表變更帶來的問題，性能也是比較優(yōu)的 ( 涉及到數(shù)據(jù)字典解析，請(qǐng)自行查詢資料)；

6.在 Innodb上用 select count(*)，因?yàn)?Innodb 會(huì)存儲(chǔ)統(tǒng)計(jì)信息；

7.慎用 Oder by rand()。

三、顯示慢查詢的次數(shù)：show status like 'slow_queries';

HEAP是較早的mysql版本

四、Explain分析低效率的SQL語句：

會(huì)產(chǎn)生如下信息：

select_type:表示查詢的類型。

table:輸出結(jié)果集的表

type:表示表的連接類型

possible_keys:表示查詢時(shí)，可能使用的索引

key:表示實(shí)際使用的索引

key_len:索引字段的長(zhǎng)度

rows:掃描出的行數(shù)(估算的行數(shù))

Extra:執(zhí)行情況的描述和說明

Select_type類型：

primary : 子查詢中最外層查詢

subquery : 子查詢內(nèi)層第一個(gè)select，結(jié)果不依賴于外部查詢

dependent subquery : 子查詢內(nèi)層第一個(gè)select，依賴于外部查詢

union:union語句中第二個(gè)select開始后面所有select

simple: 簡(jiǎn)單模式

union result: union中合并結(jié)果

type 類型:

all: 完整的表掃描 通常不好

system : 表僅有一行(=系統(tǒng)表) 這是const聯(lián)接類型的一個(gè)特例

const : 表最多有一個(gè)匹配行

extra 類型:

no table: query語句中使用 from dual 或不含任何from子句

Using filesort : 當(dāng)query中包含 order by 操作，而且無法利用索引完成排序

impossible WHERE noticed after readingconst tables:Mysql query optimizer

通過收集統(tǒng)計(jì)信息不可能存在結(jié)果

Using temporary : 某些操作必須使用臨時(shí)表，常見 group by ,order by

Using where: 不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù)

4、為什么使用了索引后查詢速度會(huì)變快

普通的查詢?nèi)绻麤]有索引，他會(huì)一直去執(zhí)行，及時(shí)匹配到了還要繼續(xù)查詢，不能保證后面有沒有要查詢的。要全文索引。

■索引使用的注意事項(xiàng)

索引的代價(jià)：

1、占用磁盤空間

2、對(duì)DML(insert,update,create)操作有影響，變慢

■總結(jié)：滿足以下條件，才應(yīng)該創(chuàng)建索引

A、肯定在where經(jīng)常使用

B、該字段的內(nèi)容不是唯一的幾個(gè)值(sex)

C、字段內(nèi)容不是頻繁變化

■使用索引的注意事項(xiàng)：

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

下列情況有可能使用到索引

a.對(duì)于創(chuàng)建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會(huì)被使用 explain select * from dept where dname='aaa';

b.對(duì)于使用like的查詢，查詢條件如果是'%aaa'則不會(huì)使用到索引，'aaa%'會(huì)使用到索引

下列情況不會(huì)使用索引 ：

a.如果條件中有or，即使其中有條件帶索引也不會(huì)使用換言之，就是要求使用的所有字段都創(chuàng)建索引，建議：盡量避免使用or關(guān)鍵字

b.對(duì)于多列索引，不是使用的第一部分，則不會(huì)使用索引

explain select * from dept where loc='aaa';// 多列索引時(shí)，loc為右邊列，索引不會(huì)使用到

c.like查詢是以%開頭如果一定要使用，則使用全文索引去查詢

d.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引起來，否則不使用索引

e.如果MySQL估計(jì)使用全表掃描要比使用索引塊，則不使用索引

如何選擇mysql的存儲(chǔ)引擎
        1：myISAM

如果表對(duì)事務(wù)的要求不高，同事一查詢和添加為主的，

比如BBS中的發(fā)帖，回帖。

2：InnoDB

對(duì)事務(wù)的要求高，保存的數(shù)據(jù)都是重要數(shù)據(jù)，

比如訂單，賬戶表

3：Memory:

數(shù)據(jù)變化頻繁，不需要入庫同時(shí)又進(jìn)場(chǎng)查詢和修改。

myISAM和InnoDB的區(qū)別：

1、myISAM批量插入快，InnoDB插入慢，myISAM插入時(shí)候不排序。

2、InnoDB支持事務(wù)，myISAM不支持事務(wù)。

3、MyISAM支持全文索引，

4、鎖機(jī)制，myISAM是表鎖，InnoDB是行鎖

5、myISAM不支持外鍵，InnoDB支持外健

① 在進(jìn)度要求高的應(yīng)用中，建議使用定點(diǎn)數(shù)據(jù)來存儲(chǔ)數(shù)值，組U一保證數(shù)據(jù)的準(zhǔn)確性，deciaml進(jìn)度比float高，盡量使用

②  對(duì)于存儲(chǔ)引擎的myISAM的數(shù)據(jù)庫，如果進(jìn)場(chǎng)要走刪除和修改的操作，要定時(shí)執(zhí)行optimize_table_name功能對(duì)表進(jìn)行碎片整理。

③  日期類型要根據(jù)實(shí)際需要選擇引用的最小存儲(chǔ)的早期類型，

手動(dòng)備份數(shù)據(jù)庫：

1、進(jìn)入cmd

2、Mysqldump –uroot –proot數(shù)據(jù)庫【表名1,表名2…】 > 文件路徑

Eg: mysqldump -uroot -proot temp > d:/temp.bak

恢復(fù)備份文件數(shù)據(jù):

Source d:/temp.bak（在mysql控制臺(tái)）

合理的硬件資源和操作系統(tǒng)

Master

Slave1

Slave2

Slave3

主庫master用來寫入，slave1—slave3都用來做select，每個(gè)數(shù)據(jù)庫

分擔(dān)的壓力小了很多。

要實(shí)現(xiàn)這種方式，需要程序特別設(shè)計(jì)，寫都操作master，讀都操作

slave，給程序開發(fā)帶來了額外負(fù)擔(dān)。當(dāng)然目前已經(jīng)有中間件來實(shí)現(xiàn)這個(gè)

代理，對(duì)程序來讀寫哪些數(shù)據(jù)庫是透明的。官方有個(gè)mysql-proxy，但是

還是alpha版本的。新浪有個(gè)amobe for mysql，也可達(dá)到這個(gè)目的，結(jié)構(gòu)

如下：

5、表的分割

水平分割：

大數(shù)據(jù)量的表,我們?cè)谔峁z索的時(shí)候，應(yīng)該根據(jù)業(yè)務(wù)的需求，找到表的標(biāo)準(zhǔn)，并在檢索頁面約束用戶的檢索方式，而且要配合分頁，

案例：大數(shù)據(jù)量的用戶表

三張表：qqlogin0,qqlogin1,qqlogin2

將用戶id%3,按結(jié)果放入不同的表當(dāng)中

create tableqqlogin0(

id int unsigned not null primary key,/* 這個(gè)id不能設(shè)置自增長(zhǎng) */

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

創(chuàng)建表qqlogin1（

id int unsigned not null主鍵，/ *這個(gè)id不能設(shè)置自增長(zhǎng)* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

創(chuàng)建表qqlogin2（

id int unsigned not null主鍵，/ *這個(gè)id不能設(shè)置自增長(zhǎng)* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

垂直分割：

把某個(gè)表的某些字段，這些字段，在查詢時(shí)候并不關(guān)系，但是數(shù)據(jù)量很大，我們建議將這些字段放到一個(gè)表中，從而提高效率

6、優(yōu)化的mysql的配置

MY.INI

port = 3306默認(rèn)端口是3306，

如果想修改端口port = 3309，在mysql_connect（'localhost：3309'，'root'，'root'）;要注意

query_cache_size = 15M這個(gè)是查詢緩存的大小

InnoDB的參數(shù)也可以調(diào)大以下兩個(gè)參數(shù)

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam需要調(diào)整key_buffer_size

調(diào)整參數(shù)還要看狀態(tài)，用show status可以看到當(dāng)前狀態(tài)，以決定該調(diào)整哪些參數(shù)

7、增量備份

實(shí)際案例：

如何進(jìn)行增量備份，和恢復(fù)

步驟：

如圖1所示，配置的my.ini文件或者是my.cof，啟用二進(jìn)制備份

2，重新啟動(dòng)的MySQL

啟動(dòng)之后會(huì)發(fā)現(xiàn)mylog目錄下生成了一下文件

其中：E：\二進(jìn)制日志\ mylog.index索引文件，有哪些備份文件

E：\二進(jìn)制日志\ mylog.000001存放用戶對(duì)象數(shù)據(jù)庫操作的文件

3，當(dāng)我們進(jìn)行操作的時(shí)候（選擇）

查看需要進(jìn)入到MySQL的的安裝目錄下的bin中，然后執(zhí)行mysqlbinlog可以文件，后面追加文件路徑

如圖4所示，恢復(fù)到某個(gè)語句的時(shí)間點(diǎn)

4,1按照時(shí)間點(diǎn)回復(fù)

Mysqlbinlog -stop-datetime =“2013-01-17 12:00:23”d：/binlog/mylog.000001 | mysq -uroot -p

（恢復(fù)到停止時(shí)間之前的所有數(shù)據(jù)）

Mysqlbinlog-start-datetime =“2013-01-17 12:00:23”d：/binlog/mylog.000001 | mysq -uroot -p

（恢復(fù)開始時(shí)間到之后的所有數(shù)據(jù)）

4,2按照位置恢復(fù)

Mysqlbinlog-stop-position =“234”d：/binlog/mylog.000001 | mysq -uroot -p

（恢復(fù)到停止時(shí)間之前的所有數(shù)據(jù)）

Mysqlbinlog-start-position =“234”d：/binlog/mylog.000001 | mysq -uroot -p

（恢復(fù)開始時(shí)間到之后的所有數(shù)據(jù)）

對(duì)于以上mysql性能優(yōu)化主要有哪些問題內(nèi)容，大家還有什么不明白的地方嗎？或者想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。