mysql性能優化主要有哪些問題

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

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

B、添加適當的索引(index)[四種：普通索引，主鍵索引，唯一索引，unique，全文索引]

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

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

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

F、對mysql配置優化[配置最大并發數，my.ini調整緩存大小]

G、Mysql云服務器引薦升級

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

1、數據庫表的設計

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

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

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

2、sql優化的一般步驟

操作步驟:

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

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

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

4、確定問題并采取相應的優化措施

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

session來表示當前的連接的統計結果，global來表示自數據庫上次啟動至今的統計結果。默認是session級別的。

show status like ‘Com_%’;

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

Show status like‘Handler_read%’使用查詢的次數

定位慢查詢：

在默認的情況下mysql是不記錄滿查詢日志的，需要在啟動的時候指定

\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(注意執行前先關閉mysql服務)

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

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

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

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

Delimiter $$

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

3、索引

?索引的類型：

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

一、添加

1.1主鍵索引添加

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

Createtable aaa(id int unsigned primary key auto_increment,

name varchar(32) not null default);

1.2普通索引

一般來說，普通索引是先創建表，然后創建普通索引。

比如：

Createindex索引名 from表名

1.3創建全文索引

全文索引，主要是針對文件，比如文章的索引全文索引針對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)技術處理中文

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

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

1.4創建唯一索引

當表的某列被指定為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,但是如果是具體內容，則不能重復

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

二、查詢

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

2．Show index from表名;

select index from表名\G

3．show keys from表名

三、刪除

Altertable 表名 drop  index 索引名，

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

四、修改

先刪除，在全部

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

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

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

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

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

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

6.根據聯合索引的第二個及以后的字段單獨查詢用不到索引；

7.不要使用 select *；

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

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

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

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

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

1.盡量規避大事務的 SQL，大事務的 SQL 會影響數據庫的并發性能及主從同步；

2.分頁語句 limit 的問題；

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

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

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

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

7.慎用 Oder by rand()。

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

HEAP是較早的mysql版本

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

會產生如下信息：

select_type:表示查詢的類型。

table:輸出結果集的表

type:表示表的連接類型

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

key:表示實際使用的索引

key_len:索引字段的長度

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

Extra:執行情況的描述和說明

Select_type類型：

primary : 子查詢中最外層查詢

subquery : 子查詢內層第一個select，結果不依賴于外部查詢

dependent subquery : 子查詢內層第一個select，依賴于外部查詢

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

simple: 簡單模式

union result: union中合并結果

type 類型:

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

system : 表僅有一行(=系統表) 這是const聯接類型的一個特例

const : 表最多有一個匹配行

extra 類型:

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

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

impossible WHERE noticed after readingconst tables:Mysql query optimizer

通過收集統計信息不可能存在結果

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

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

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

普通的查詢如果沒有索引，他會一直去執行，及時匹配到了還要繼續查詢，不能保證后面有沒有要查詢的。要全文索引。

■索引使用的注意事項

索引的代價：

1、占用磁盤空間

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

■總結：滿足以下條件，才應該創建索引

A、肯定在where經常使用

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

C、字段內容不是頻繁變化

■使用索引的注意事項：

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

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

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

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

下列情況不會使用索引 ：

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

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

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

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

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

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

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

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

比如BBS中的發帖，回帖。

2：InnoDB

對事務的要求高，保存的數據都是重要數據，

比如訂單，賬戶表

3：Memory:

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

myISAM和InnoDB的區別：

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

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

3、MyISAM支持全文索引，

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

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

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

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

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

手動備份數據庫：

1、進入cmd

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

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

恢復備份文件數據:

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

合理的硬件資源和操作系統

Master

Slave1

Slave2

Slave3

主庫master用來寫入，slave1—slave3都用來做select，每個數據庫

分擔的壓力小了很多。

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

slave，給程序開發帶來了額外負擔。當然目前已經有中間件來實現這個

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

還是alpha版本的。新浪有個amobe for mysql，也可達到這個目的，結構

如下：

5、表的分割

水平分割：

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

案例：大數據量的用戶表

三張表：qqlogin0,qqlogin1,qqlogin2

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

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;

創建表qqlogin1（

id int unsigned not null主鍵，/ *這個id不能設置自增長* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

創建表qqlogin2（

id int unsigned not null主鍵，/ *這個id不能設置自增長* /

name varchar（32）not null default''，

pwd varchar（32）not null default''

）engine = myisam default charset = utf8;

垂直分割：

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

6、優化的mysql的配置

MY.INI

port = 3306默認端口是3306，

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

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

InnoDB的參數也可以調大以下兩個參數

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam需要調整key_buffer_size

調整參數還要看狀態，用show status可以看到當前狀態，以決定該調整哪些參數

7、增量備份

實際案例：

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

步驟：

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

2，重新啟動的MySQL

啟動之后會發現mylog目錄下生成了一下文件

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

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

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

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

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

4,1按照時間點回復

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

（恢復到停止時間之前的所有數據）

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

（恢復開始時間到之后的所有數據）

4,2按照位置恢復

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

（恢復到停止時間之前的所有數據）

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

（恢復開始時間到之后的所有數據）

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