小貝_mysql最佳化學習，小貝_mysql最佳化

小貝_mysql最佳化學習，小貝_mysql最佳化
mysql最佳化簡要:
1、資料庫設計最佳化
2、sql語句最佳化
3、表分割
4、讀寫分離技術

 

一、資料庫設計最佳化

1、表設計要符合三範式，當然，有時也需要適當的逆範式

2、什麼是三範式

一範式: 具有原子性，不可再分割

二範式: 在滿足一範式的基礎上，我們考慮是否滿足二範式。只要表的記錄滿足唯一性，也是說，同一張表，不可能出現完全相同的記錄，一般說，在表中設計一個主鍵即可。

三範式: 在滿足二範式的基礎上，我們考慮是否滿足三範式。只要表滿足沒冗餘性。

 

二、SQL語句最佳化

1、sql最佳化的一般步驟

a、通過show status命令瞭解各種sql的執行效率

b、定位執行效率較低的sql語句

c、通過explain/desc分析低效率的sql語句的執行情況

d、確定問題並採取相應的最佳化措施

2、showstatus命令

該命令可以顯示mysql資料庫目前狀態，主要關心的是’com’開頭的指令

showstatus like ‘com%’ ó show session status like ‘com%’//顯示當前控制台的情況

showglobal status like ‘com%’ //顯示資料庫從啟動到現在的情況

3、showvariables命令

該命令可以查看mysql當前的變數設定，主要關心的是慢查詢時間

 

4、如何在mysql中找到慢查詢的sql語句

（備忘: mysql資料庫支援把慢查詢語句，記錄到日誌中給程式員分析;預設情況下，mysql不啟用慢查詢日誌）

步驟: a、啟動mysql慢查詢

              a1、在啟動mysql服務時，指定—slow-query-log

              a2、在利用用戶端登進mysql後，設定變數

             

              b、查看慢查詢時間

預設為10秒

             c、修改慢查詢時間

設定為1秒

(這個只能在當前環境生效，如果想每次都生效，就修改mysql的設定檔)

              d、查看慢查詢日誌

              e、根據慢查詢的sql語句，進行最佳化。最廉價的做法就是加索引

              f、加上索引後

 

5、索引的影響

       a、增加磁碟空間

       b、給增刪改帶來不便

6、哪些列上適合添加索引

       a、頻繁地作為查詢條件欄位應該建立索引

       b、唯一性太差的欄位(即該欄位的值變化不大)不適合單獨建立索引，即使頻繁作為查詢條件

       c、更新非常頻繁的欄位不適合建立索引

       d、不會出現在where子句中欄位也不應該建立索引

7、索引的使用

測試表:

Create Table: CREATE TABLE`t2` (

  `id` int(11) NOT NULL DEFAULT '0',

  `name` char(5) DEFAULT NULL,

  `age` tinyint(4) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULTCHARSET=utf8;

insert into t2(name,age) values('a',2),('aa',3),('b',4),('c',3);

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

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

a、對於建立的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。

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

下列的表將不使用索引：

a、如果條件中有or，即使其中有條件帶索引也不會使用。

b、對於多列索引，不是使用的第一部分，則不會使用索引。
c、like查詢是以%開頭
d、如果列類型是字串，那一定要在條件中將資料使用引號引用起來。否則不使用索引。
e、如果mysql估計使用全表掃描要比使用索引快，則不使用索引。

8、驗證索引使用方式

showstatus like ‘Handler_read%’;

備忘:
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。

handler_read_rnd_next:這個值越高，說明查詢低效。

 

9、常用SQL最佳化

a、大批量插入資料:

       對於MyIsam：

1、alter table table_name disable keys;

       2、loading data;

       3、alter table table_name enable keys;

       對於Innodb:

       1、將要匯入的資料按照主鍵排序

       2、set unique_checks=0,關閉 唯一性校正

       3、set autocommit=0，關閉自動認可

(提示: myisam和innodb的區別是：

a、myisam不支援外接，innodb支援

b、myisam不支援事務，innodb支援)

b、最佳化group by

預設情況下，mysql對group by後面的列名進行排序。如果查詢中包括group by但使用者想要避免排序結果的消耗，可以使用order by null禁止排序

 

三、表分割

當一個表的資料很大的時候，其它的最佳化方式已經都考慮進去。起到的作用不大時，就要考慮分表了。即把一張大表分割成多張小表。

分表方式:

a、垂直分表

       此時，表中存在很多列，這個時候可以通過主鍵，把表中列分成多張表，然後再根據主鍵進行關聯.（拆分後，每張表的列都不同）

       分表前: 個人資訊表

id	name	age	email	intro
1	a	11	11@qq.com	xxxx
2	b	22	22@qq.com	yyyy

 

       分表後: 個人資訊表

id	name	age	email
1	a	11	11@qq.com
2	b	22	22@qq.com

       個人介紹表

id	intro
1	xxxx
2	yyyy

 

b、水平分表

       可以通過模數的方式，進行分表。因此，需要判斷分成幾張小表，即模的值為多少。另外，拆分後，每張表的列都是一致的。

       分表前: 個人資訊表

id	name	age	email	intro
1	a	11	11@qq.com	xxxx
2	b	22	22@qq.com	yyyy

       確定模數的值為2，因此可以把這種表分為兩張小表

       1、判斷id的值，id/2=?

       分表後: 個人資訊表0

id	name	age	email	intro
1	a	11	11@qq.com	xxxx

個人資訊表1

id	name	age	email	intro
2	b	22	22@qq.com	yyyy

 

 

四、讀寫分離

通常來說，一台mysql伺服器承載著所有關於資料庫的操作。但是在訪問量大的時候，mysql伺服器很容易出現瓶頸。為了減少mysql伺服器的壓力，(mysql本身支援主從複製功能)

可以通過分離讀寫操作。

1、讀寫分離前

 

2、讀寫分離

 

The quieter you become，the more you are able to hear！

 

 

 

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。