mysql數據庫優化語句

mysql數據庫優化語句

 

數據庫語句:
 

    Ddl(數據定義語言)    alter  create   drop

   

    Dml(數據操作語言)   inset  delete  update

    www.aiwalls.com  

    Dtl(數據事務語言)  conmmit  rollback   savepoint

 

    Select

 

    Dcl(數據控制語句) grant賦權限  revoke回收

   

Mysql數據庫優化:
 

1、  數據庫表 要設計合理(符合3NF,有時候也需要適當的逆范式)

2、  Sql語句的優化(索引,常用小技巧)

3、  數據庫的配置

4、  適當的硬件配置和操作系統

5、  讀寫分離

   

問:什麼是數據庫3范式?

    1NF: 就是具有原子性,不可分割(隻要使用的是關系型數據庫,就會自動符合)

   2NF: 在滿足1NF的基礎上,我們考慮是否滿足2NF,隻要表的記錄滿足唯一性,也就是說,你的同一張表中不可能出現完全相同的記錄,一般說我們在表中設計一個主鍵即可。

   3NF: 在滿足2NF: 的基礎上,我們考慮是否滿足3NF,既我們的字段信息可以通過關聯的關系,派生即可(通常我們通過外鍵來處理)使用外鍵數據庫的存儲引擎必須是innoDB

 

問2:數據庫參數配置

      對於innodb存儲引擎最重要的就是內存,所以下面的兩個參數調的很大

          Innodb_additional_mem_pool_size = 64M    www.aiwalls.com  

          Innodb_buffer_pool_size = 1G     緩沖池大小

     對於myisam,需要調整key_buffer_size

           用show  status 語句可以看到當前狀態,以決定調整那些參數

            

一、顯示你使用過多少次insert  , update   ,  delete 等

Sql:    show status  like  “Com”;

              //在命令窗口中不關閉的時候查詢會準確,如果關閉就會從新開始統計

              Show sessionstatus like “Com_update”;

              //就算關閉窗口也會將全部的你執行過的次數統計出來

              Show globalstatus like “Com_insert”;

 

Example:  session   

                     假如已經使用瞭6次update

1、  用session統計 會是6次

如果關閉後命令窗口後在執行Show session statuslike “Com_update”;  就為0瞭

2、  但是如果用Show global status like “Com_insert”;就是6次

 

二、顯示試圖連接Mysql服務器的次數

              Show  status like  “Connections”;

 

    數據庫啟動多長時間瞭

              Show  status like   “uptime”;

      

       顯示慢查詢多少次(默認是10秒)

              Show  status like  “Slow_queries”;

 

四、如何在一個項目中,找到慢查詢的select,mysql數據庫支持把慢查詢的語句記錄到日志中,供程序員來分析  www.aiwalls.com  

       步驟:

1、 啟動mysql(特殊的啟動方式)

a)        在mysql的安裝目錄下的bin目錄下啟動mysqld.exe –slow-query

b)       Netstat –an 查看3306端口是否啟動

c)        查詢慢查詢的次數   show status  like  “Slow_queries”;

d)       設置慢查詢的時間   set long_query_time=1;

 

   索引優化:

 

比如說增加主鍵索引

              Alter  table user   add  primary key(id); 

刪除主鍵索引

              Alter  table user  drop  primary key

刪除索引

       Alter  table  user drop  index   索引名

顯示索引

        Show  index(es)  from  表名

         Show  keys  from  表名

         Desc  表名

   增加索引致使查詢會變快好多,其原理就像一本書如果沒有目錄的話那麼如果你想找一個知識點會很難找到,隻能一點一點的翻著找,如果有目錄的話會很快的定位到這個知識點在那個章節中大概什麼位置這樣查詢起來自然就會快瞭啊,但是有利必有弊,索引會對查詢帶來好處,但是對add   update   delete  來說自然就很麻煩瞭,比如說你添加一個知識點,你不許還有在目錄中添加他是屬於那章那節中的那個知識點,同樣在修改和刪除的時候也會隨之改變,來保持信息的準確性。

       
                                                                                                                 

 

一個自動分析是否需要使用索引的命令:explain

Example:  explain  select  *  from emp   where   id  =   9;


 

索引的分類:

         主鍵索引(primary   key)

         唯一鍵索引(unique)

         Index(普通索引)

         全文索引(fulltext)

         復合索引(多列和在一起)

   www.aiwalls.com  

在那些列上添加索引比較合適:

1、比較頻繁的作為查詢條件的字段應該加上索引

2、  唯一性比較差的字段不適合單獨創建索引,及時頻繁作為查詢條件

3、  更新非常頻繁的字段不適合創建索引

4、  不會出現在where子句中的字段不該創建索引

 

查詢一個表中的所有索引: show  indexes   from  table(表名)               

 

        索引的使用:

             查詢要使用索引最重要的條件是查詢條件中需要使用索引

             以下幾種情況可能會使用到索引

1、  對於創建的多列索引,隻要查詢條件使用瞭最左邊的列,索引一般就會被使用

2、  對於使用like的查詢,查詢如果是‘%aaa’不會使用到索引‘aaa%’會使用到索引

             以下的表中將不使用索引

1、  如果條件中有or,即使其中有條件帶索引也不會使用

2、  對於多列索引,不是使用的第一部分,則不會使用索引

3、  Like查詢是以%開頭

4、  如果列類型是字符串,那麼一定要在條件中將數據使用引號引起來,否則不使用索引。

5、  如果mysql估計使用全表掃描要比使用索引快,則不使用索引。

 

 

查看索引的使用情況

     Show status  like   ‘handler_read%’;


 

隻有handler_read_key  越大越好

     Handler_read_rnd_next  越小越好

 

 數據庫類型:

             MyISAM  不支持事務和外鍵,一張表由三個文件組成,.frm  .myi  .myd

             innoDB    支持事務和外鍵   

            

             對於MyISAM來說查詢快,不過刪除字段時空間是不會釋放的,必須使用手動釋放        optimize   table  table_name  www.aiwalls.com  

 

數據庫分表:

1、  水平分表

 

2、  垂直分表:  

Stu表:

     id

     Name

     Pass

     Photo

Mark表

     Id

     Sid

     Question

     answer

垂直分表針對於關聯類型的表,比如說,有一個學生的個人信息(有頭像)表,一個考試信息(考試題和答案)表,這時我想查一個學生的考試分數和個人信息,那麼mysql會將學生的個人信息和考試信息表關聯,速度回降低很多,所以要將考試信息中的答案和題目分開在創建一個表,如果在提高還可以將頭像分開為一個單獨的表(如百度單獨的圖片服務器)

 

讀寫分離:


 

 

 

作者 web8

發佈留言