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

創(chuàng)新互聯(lián)建站技術(shù)團隊10年來致力于為客戶提供成都網(wǎng)站建設、網(wǎng)站設計、品牌網(wǎng)站制作、全網(wǎng)營銷推廣、搜索引擎SEO優(yōu)化等服務。經(jīng)過多年發(fā)展，公司擁有經(jīng)驗豐富的技術(shù)團隊，先后服務、推廣了1000+網(wǎng)站，包括各類中小企業(yè)、企事單位、高校等機構(gòu)單位。

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

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

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

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

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

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

G、Mysql云服務器引薦升級

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

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

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

第二范式：2NF是對記錄的惟一性約束，要求記錄有惟一標識，即實體的惟一性；

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

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

操作步驟:

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

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

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

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

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

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

show status like ‘Com_%’;

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

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

定位慢查詢：

在默認的情況下mysql是不記錄滿查詢?nèi)罩镜模枰趩拥臅r候指定

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

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

具體操作如下：

如果啟用了慢查詢，默認存儲在mysql.ini文件的此處

1、重啟mysql,找到datadir的路勁，使用cmd進入到data的上級目錄

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

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

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

重新設置滿查詢的時間：Set long_query_time=2;

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

Delimiter $$

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

3、索引

?索引的類型：

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

一、添加

1.1主鍵索引添加

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

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)建全文索引

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

Create table articles(

Id int unsignedauto_increment not null primary key,

Title varchar(20),

Body text,

Fulltext (title,body)

)engine=myisam charsetutf8;

錯誤用法：

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

證明：

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

正確的用法：

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

說明：

1、在mysql中fulltest索引值針對myisam生效

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

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

4、全文索引一個叫停止詞。因為在一個文本中，創(chuàng)建索引的是一個無窮大的書，因此，對一些常用詞和字符就不會創(chuàng)建，這些詞，稱之為停止詞

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

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

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

這時，name默認就是唯一索引

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

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

簡單的說：PRIMARY KEY=UNIQUE+NOT NULL

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

主鍵字段，不能為null,也不能重復

二、查詢

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

2．Show index from表名;

select index from表名\G

3．show keys from表名

三、刪除

Altertable 表名 drop  index 索引名，

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

四、修改

先刪除，在全部

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

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

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

3.不要在字段前面加減運算；

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

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

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

7.不要使用 select *；

8.排序請盡量使用升序 ;

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

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

11.order by / groupby 字段包括在索引當中減少排序，效率會更高。

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

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

2.分頁語句 limit 的問題；

3.刪除表所有記錄請用 truncate，不要用 delete；

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

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

6.在 Innodb上用 select count(*)，因為 Innodb 會存儲統(tǒng)計信息；

7.慎用 Oder by rand()。

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

HEAP是較早的mysql版本

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

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

select_type:表示查詢的類型。

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

type:表示表的連接類型

possible_keys:表示查詢時，可能使用的索引

key:表示實際使用的索引

key_len:索引字段的長度

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

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

Select_type類型：

primary : 子查詢中最外層查詢

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

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

union:union語句中第二個select開始后面所有select

simple: 簡單模式

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

type 類型:

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

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

const : 表最多有一個匹配行

extra 類型:

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

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

impossible WHERE noticed after readingconst tables:Mysql query optimizer

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

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

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

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

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

■索引使用的注意事項

索引的代價：

1、占用磁盤空間

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

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

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

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

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

■使用索引的注意事項：

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

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

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

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

下列情況不會使用索引 ：

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

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

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

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

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

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

如何選擇mysql的存儲引擎
        1：myISAM

如果表對事務的要求不高，同事一查詢和添加為主的，

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

2：InnoDB

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

比如訂單，賬戶表

3：Memory:

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

myISAM和InnoDB的區(qū)別：

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

2、InnoDB支持事務，myISAM不支持事務。

3、MyISAM支持全文索引，

4、鎖機制，myISAM是表鎖，InnoDB是行鎖

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

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

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

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

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

1、進入cmd

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

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

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

Source d:/temp.bak（在mysql控制臺）

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

Master

Slave1

Slave2

Slave3

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

分擔的壓力小了很多。

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

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

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

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

如下：

5、表的分割

水平分割：

大數(shù)據(jù)量的表,我們在提供檢索的時候，應該根據(jù)業(yè)務的需求，找到表的標準，并在檢索頁面約束用戶的檢索方式，而且要配合分頁，

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

三張表：qqlogin0,qqlogin1,qqlogin2

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

create tableqqlogin0(

id int unsigned not null primary key,/* 這個id不能設置自增長 */

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主鍵，/ *這個id不能設置自增長* /

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主鍵，/ *這個id不能設置自增長* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

垂直分割：

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

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

MY.INI

port = 3306默認端口是3306，

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

query_cache_size = 15M這個是查詢緩存的大小

InnoDB的參數(shù)也可以調(diào)大以下兩個參數(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可以看到當前狀態(tài)，以決定該調(diào)整哪些參數(shù)

7、增量備份

實際案例：

如何進行增量備份，和恢復

步驟：

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

2，重新啟動的MySQL

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

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

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

3，當我們進行操作的時候（選擇）

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

如圖4所示，恢復到某個語句的時間點

4,1按照時間點回復

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

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

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

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

4,2按照位置恢復

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

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

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

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

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

分享題目：mysql性能優(yōu)化主要有哪些問題
URL地址：http://m.newbst.com/article38/jesdsp.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供標簽優(yōu)化、外貿(mào)網(wǎng)站建設、動態(tài)網(wǎng)站、網(wǎng)站維護、建站公司、全網(wǎng)營銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)