關於mysql效能最佳化問題的整理

Mysql優化綜合性的問題：

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

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

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

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

E、預存程序[模組化編程，可以提高速度]

F、對mysql配置最佳化[配置最大並發數，my.ini調整快取大小]

G、Mysql伺服器引薦升級

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

推薦Mysql相關影片教學：https://www. php.cn/course/list/51/type/2.html

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.exebin - -log-slow-queries=d:bac.log

具體操作如下：

     如果已啟用了慢速查詢，則預設儲存在mysql.ini檔案的此處

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

         2、執行指令\bin\mysqld.exe –safe-mode  –  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的時候，指定慢查詢的)。

3、索引

♥索引的類型：

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

############################################################### #       一、新增######      1.1主鍵索引新增######             ######                  Createtable aaa(id int unsigned primary key auto_increment,### default);######       1.2普通指標###

                  一般來說，且以一般為建立資料表，而建立一般指標。

                  與上：

             

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

                                  Id int unsignedauto_increment not null primary key,

               Body text,

                          Fulltext (title,body)

                          )engine=myisam charsetutf8;

                  錯誤用法：

                          Select * from articles where body like ' %mysql%'[未使用全文索引]

                  證明：

      articles body like '%mysql%';

                  正確的用法：

                          Select * from article wherematch("p)against(cdata  Select * from article wherem   說明：

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

                         2、針對英文生效，àsphinx(    3、使用的方法，match(字段名，…)against('關鍵字')

                         4且且以「寫出指標為「稱為停止詞」。因為在一個文本中，創建索引的是一個無限大的書，因此，對一些常用詞和字符就不會創建，這些詞，稱為停止詞

           1.4創建唯一索引

#                地table ddd(id int primary keyauto_increment,name varchar(32) unique);

                                 

                        第二種、create table eee(id int primary keyauto_increment,name #1)));;            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 (刪除主鍵索引名)

       四、修改

             與已被修改為皆為

二、針對每個已寫出的慢速正如上一節提到的正確的使用索引能加快查詢速度，那麼我們在編寫 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:輸出結果集的表格

            #            key:表示實際使用的索引

            key_len:索引欄位長度上的長度

             Select_type類型：

            primary : 子查詢中最外層查詢

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

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

      union result: union中合併結果

type 類型:

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

        款一個特例

            const : 資料中最多有一個符合行

extra 類型:

       或任何子句使用子句使用中：

##      

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

        收集統計資料不可能存在結果

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

       

##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的查詢，查詢條件如果是'�a'則不會使用到索引，'aaa%'會使用到索引

下列情況不會使用索引：

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

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

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

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

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

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

如何選擇mysql的儲存引擎

         1：myISAM

        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控制台）

合理的硬體資源與作業系統

         

##         

 1

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;

# 

建立表 login1（

#o  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 int unsigned not null

        id int unsigned not null

  自我鍵，/ *這個不能設定自增長* /

        name varchar（32）not null default''，

        pwd varchar（32）not null default''

##ault#    ) varchar（32）not null default''

#ault# 

垂直分割：

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

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

（恢復開始時間到之後的所有資料）

更多相關問題，請造訪PHP中文網：https:/ /www.php.cn/

#

以上是關於mysql效能最佳化問題的整理的詳細內容。更多資訊請關注PHP中文網其他相關文章！