MySQL數據庫優化技巧之索引應用技能總結

MySQL數據庫優化技巧之索引應用技能總結。本站提示廣大學習愛好者：（MySQL數據庫優化技巧之索引應用技能總結）文章只能為提供參考，不一定能成為您想要的結果。以下是MySQL數據庫優化技巧之索引應用技能總結正文

本文實例總結了MySQL數據庫優化技巧的索援用法。分享給年夜家供年夜家參考，詳細以下：

這裡緊接上一篇《MySQL數據庫優化技巧之設置裝備擺設技能總結》，進一步剖析索引優化的技能：

(七)表的優化

1. 選擇適合的數據引擎

MyISAM:實用於年夜量的讀操作的表

InnoDB:實用於年夜量的寫讀作的表

2.選擇適合的列類型

應用 SELECT * FROM TB_TEST PROCEDURE ANALYSE()可以對這個表的每個字段停止剖析，給出優化列類型建議

3.關於不保留NULL值的列應用NOT NULL，這對你想索引的列特別主要

4.樹立適合的索引

5.應用定長字段,速度比變長要快

(八)樹立索引准繩

1.公道應用索引

一個Table在一次query中只能應用一個索引,應用EXPLAIN語句來磨練優化法式的操作情形

應用analyze贊助優化法式對索引的應用後果做出更精確的猜測

2.索引應當創立在搜刮、排序、歸組等操作所觸及的數據列上

3.盡可能將索引樹立在反復數據少的數據列中，獨一所以最好

例如：誕辰列，可以樹立索引，但性別列不要樹立索引

4.盡可能比較較短的值停止索引

下降磁盤IO操作，索引緩沖區中可以包容更多的鍵值，進步射中率

假如對一個長的字符串樹立索引，可以指定一個前綴長度

5.公道應用多列索引

假如多個前提常常須要組合起來查詢,則要應用多列索引(由於一個表一次查詢只能應用一個索引,樹立多個單列索引也只能應用一個)

6.充足應用最左前綴

也就是要公道支配多列索引中各列的次序，將最經常使用的排在後面

7.不要樹立過量的索引

只要常常運用於where,order by,group by中的字段須要樹立索引.

8.應用慢查詢日記查找出慢查詢(log-slow-queries, long_query_time)

(九)充足應用索引

1.盡可能比擬數據類型雷同的數據列

2.盡量地讓索引列在比擬表達式中自力, WHERE mycol < 4 / 2 應用索引,而WHERE mycol * 2 < 4不應用

3.盡量纰謬查詢字段加函數,

如：WHERE YEAR(date_col) < 1990改革成WHERE date_col < '1990-01-01'

WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff 改革成WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

4.在LIKE形式的開首不要應用通配符

5.應用straight join可以強迫優化器依照FROM子句的順序來停止聯絡,可以select straight join,強迫一切聯絡,也能夠select * from a straight join b強迫兩個表的次序.

6.應用force index強迫應用指定的索引.如 select * from song_lib force index(song_name) order by song_name比不消force index效力高

7.盡可能防止應用MySQL主動類型轉換,不然將不克不及應用索引.如將int型的num_col用where num_col='5'

(十)SQL語句的優化

1.創立適合的統計中央成果表，下降從年夜表查詢數據的概率

2.盡可能防止應用子查詢，而改用銜接的方法.例如：

SELECT a.id, (SELECT MAX(created) FROM posts WHERE author_id = a.id) AS latest_post
FROM authors a

可以改成：

SELECT a.id, MAX(p.created) AS latest_post
FROM authors AS a
INNER JOIN posts p ON (a.id = p.author_id)
GROUP BY a.id

select song_id from song_lib where singer_id in
(select singer_id from singer_lib
where first_char='A'
) limit 2000

改成:

select song_id from song_lib a
inner join singer_lib b on a.singer_id=b.singer_id and first_char='A' limit 2000

3.拔出斷定反復鍵時,應用ON DUPLICATE KEY UPDATE :
insert into db_action.action_today(user_id,song_id,action_count) values(1,1,1) ON DUPLICATE KEY UPDATE action_count=action_count+1;

4.防止應用游標

游標的運轉效力極低，可以經由過程增長暫時表，應用多表查詢，多表更新等方法完成義務,不要應用游標.

(十一)應用Explain剖析SQL語句應用索引的情形

當你在一條SELECT語句前放上症結詞EXPLAIN，MySQL說明它將若何處置SELECT，供給有關表若何聯絡和以甚麼順序聯絡的信息,借助於EXPLAIN，可以曉得甚麼時刻必需為表參加索引以獲得一個應用索引來尋覓記載的更快的SELECT,你也能曉得優化器能否以一個最好順序聯絡表。為了強迫優化器對一個SELECT語句應用一個特定聯絡順序，增長一個STRAIGHT_JOIN子句。 。

EXPLAIN敕令的普通語法是：EXPLAIN <SQL敕令> 如：explain select * from a inner join b on a.id=b.id

EXPLAIN的剖析成果參數詳解:

1.table：這是表的名字。

2.type：銜接操作的類型。

system:表中唯一一筆記錄（現實運用很少只要一條材料的表)

const:表最多有一個婚配行,用於用常數值比擬PRIMARY KEY或UNIQUE索引的一切部門時,

如：

select * from song_lib where song_id=2

(song_id為表的primary key)

eq_ref:關於每一個來自於後面的表的行組合，從該表頂用UNIQUE或PRIMARY KEY的索引讀取一行,

如：
select * from song_lib a inner join singer_lib b on a.singer_id=b.singer_id
（b的type值為eq_ref)

ref:關於每一個來自於後面的表的行組合，從該表頂用非UNIQUE或PRIMARY KEY的索引讀取一行

如：
select * from song_lib a inner join singer_lib b on a.singer_name=b.singer_name
和
select * from singer_lib b where singer_name='ccc'（b的type值為ref,由於b.singer_name是通俗索引)

ref_or_null:該聯接類型好像ref，然則添加了MySQL可以專門搜刮包括NULL值的行,

如：
select * from singer_lib where singer_name='ccc' or singer_name is null

index_merge:該聯接類型表現應用了索引歸並優化辦法

Key： 它顯示了MySQL現實應用的索引的名字。假如它為空（或NULL），則MySQL不應用索引。

key_len： 索引中被應用部門的長度，以字節計。

3.ref：ref列顯示應用哪一個列或常數與key一路從表當選擇行

4.rows： MySQL所以為的它在找到准確的成果之前必需掃描的記載數。明顯，這裡最幻想的數字就是1。

5.Extra：這裡能夠湧現很多分歧的選項，個中年夜多半將對查詢發生負面影響。普通有：

using where：表現應用了where前提

using filesort: 表現應用了文件排序,也就是應用了order by子句，而且沒有效到order by 裡字段的索引，從而須要額定的排序開支，所以假如湧現using filesort就表現排序的效力很低，須要停止優化,好比采取強迫索引的辦法(force index)

願望本文所述對年夜家MySQL數據庫計有所贊助。