SQL語句優(yōu)化 3.1 優(yōu)化SQL語句的一般步驟 3.1.1 通過show status命令了解各種SQL的執(zhí)行頻率。

格式： mysql>show [session|global]status;
其中： session（默認(rèn)）表示當(dāng)前連接，global表示自數(shù)據(jù)庫啟動至今

mysql>show?status;
mysql>show?global??status;
mysql>show?status?like?'Com_%';
mysql>show?global?status?like?'Com_%';

3.1.2 定位執(zhí)行效率較低的SQL語句

explain?select?*?from?table?where?id=1000;
desc?select?*?from?table?where?id?=1000;

3.1.3 通過EXPLAIN分析較低效SQL的執(zhí)行計劃

3.2 索引問題 索引是數(shù)據(jù)庫優(yōu)化中最常見也是最重要的手段之一，通過索引通?？梢詭椭脩艚鉀Q大多數(shù)的SQL性能問題。 3.2.1 索引的存儲分類 MyISAM存儲引擎的表的數(shù)據(jù)和索引是自動分開存儲的，各自是獨一的文件；InnoDB存儲引擎的表的數(shù)據(jù)和索引是存儲在同一個表空間里面，但可以有多個文件組成。 MySQL目前不支持函數(shù)索引，但是能對列的前面某一部分進行索引，例如name字段，可以只取name的前4個字符進行索引，這個特性可以大大縮小索引文件的大小，用戶在設(shè)計表結(jié)構(gòu)的時候也可以對文本列根據(jù)此特性進行靈活設(shè)計。

mysql>create?index?ind_company2_name?on?companny2(name(4));
????其中company表名ind_company2_name索引名

3.2.2MySQL如何使用索引 索引用于快速找出在某個列中有一特定值的行。對相關(guān)列使用索引是提高select操作性能的最佳途徑。 （一）.使用索引

（1）對于創(chuàng)建的多列索引，只要查詢的條件中用到最左邊的列，索引一般就會被使用。如下創(chuàng)建一個復(fù)合索引。

mysql>create?index?ind_sales2_com_mon?onsales2(company_id,moneys);
然后按company_id進行查詢，發(fā)現(xiàn)使用到了復(fù)合索引
mysql>explain?select?*?from?sales2?where?company_id=2006G
使用下面的查詢就沒有使用到復(fù)合索引。
mysql>explain?select?*?from?sales2?where?moneys=1G

（2）使用like的查詢，后面如果是常量并且只有%號不在第一個字符，索引才可能會被使用，如下：

（3）如果對大的文本進行搜索，使用全文索引而不使用like“%…%”. 如果列名是索引，使用column_name is null將使用索引。如下
（二）.存在索引但不使用索引

（1）如果MySQL估計使用索引比全表掃描更慢，則不使用索引。例如如果列key_part1均勻分布在1到100之間，查詢時使用索引就不是很好

msyql>select?*?from?table_name?where?key_part1>1?and?key_part<90;

（2）如果使用MEMORY/HEAP表并且where條件中不使用“=”進行索引列，那么不會用到索引。Heap表只有在“=”的條件下會使用索引。
（3）用or分割開的條件，如果or前的條件中的列有索引，而后面的列中沒有索引，那么涉及的索引都不會被用到。

從上面可以發(fā)現(xiàn)只有year列上面有索引。來看如下的執(zhí)行計劃。

（4）如果不是索引列的第一部分，如下例子：可見雖然在money上面建有復(fù)合索引，但是由于money不是索引的第一列，那么在查詢中這個索引也不會被MySQL采用。

（5）如果like是以%開始，可見雖然在name上面建有索引，但是由于條件中l(wèi)ike的值的“%”在第一位了，那么MySQL也會采用這個索引。

（6）如果列類型是字符串，但在查詢時把一個數(shù)值型常量賦值給了一個字符型的列名name，那么雖然在name列上有索引，但是也沒有用到。

3.2.3查看索引使用情況 如果索引正在工作，Handler_read_key的值將很高，這個值帶了一個行被索引值讀的次數(shù)。 Handler_read_rnd_next的值高則意味著查詢運行低效，并且應(yīng)該建立索引補救。
3.3 兩個簡單使用的優(yōu)化方法 對于大多數(shù)開發(fā)人員來說，可能只希望掌握一些簡單實用的優(yōu)化方法，對于更多更復(fù)雜的優(yōu)化，更傾向于交給專業(yè)DBA來做。 3.3.1定期分析表和檢查表

ANALYZE?[LOCAL?|?NO_WRITE_TO_BINLOG]?TABLE??tal_name?[,tbl_name]?...

本語句用于分析和存儲表的關(guān)鍵字分析，分析的結(jié)果將可以使得系統(tǒng)得到準(zhǔn)確的統(tǒng)計信息，使得SQL能夠生成正確的執(zhí)行計劃。

分析表的語法如下：（檢查一個或多個表是否有錯誤）

3.3.2定期優(yōu)化表

優(yōu)化表的語法格式：

OPTIMIZE?[LOCAL?|?NO_WRITE_TO_BINLOG]?TABLE?tb1_name?[,tb1_name]

如果已經(jīng)刪除了表的一大部分，或者如果已經(jīng)對含有可變長度行的表進行了很多改動，則需要做定期優(yōu)化。這個命令可以將表中的空間碎片進行合并，但是此命令只對MyISAM,BDB和InnoDB表起作用。

3.4 常用SQL的優(yōu)化 3.4.1 大批量插入數(shù)據(jù)

當(dāng)用load命令導(dǎo)入數(shù)據(jù)的時候，適當(dāng)設(shè)置可以提高導(dǎo)入的速度。

select?name?from?tb1?infile?into?"/tmp/test.txt";
load?data?infile?"/tmp/test.txt"?into?table?tb1(name);

對于MyISAM存儲引擎的表，可以通過以下方式快速的導(dǎo)入大量的數(shù)據(jù)。

ALTER?TABLE?tb1_name?DISABLE?KEYS
loading?the?data
ALTER?TABLE?tb1_name?ENABLE?KEYS

DISABLE KEYS 和 ENABLE KEYS 用來打開或關(guān)閉MyISAM表非唯一索引的更新，可以提高速度，
注意：對InnoDB表無效。
注意：對InnoDB表無效。

（2）關(guān)閉唯一性校驗可以提高導(dǎo)入效率
在導(dǎo)入數(shù)據(jù)前先執(zhí)行set unique_checks=0,關(guān)閉唯一性校驗，在導(dǎo)入結(jié)束后執(zhí)行set unique_checks=1,恢復(fù)唯一性校驗，可以提高導(dǎo)入效率。

（1）針對InnoDB類型表數(shù)據(jù)導(dǎo)入的優(yōu)化
因為InnoDB表的按照主鍵順序保存的，所以將導(dǎo)入的數(shù)據(jù)主鍵的順序排列，可以有效提高導(dǎo)入數(shù)據(jù)的效率。

（3）關(guān)閉自動提交可以提高導(dǎo)入效率
在導(dǎo)入數(shù)據(jù)前先執(zhí)行set autocommit=0,關(guān)閉自動提交事務(wù)，在導(dǎo)入結(jié)束后執(zhí)行set autocommit=1,恢復(fù)自動提交，可以提高導(dǎo)入效率。

3.4.2 優(yōu)化insert語句 盡量使用多個值表的insert語句，這樣可以大大縮短客戶與數(shù)據(jù)可的連接，關(guān)閉等損耗。 可以使用insert delayed （馬上執(zhí)行）語句得到更高的效率。 將索引文件和數(shù)據(jù)文件分別存放不同的磁盤上。 可以增加bulk_insert_buffer_size變量值的方法來提高速度，但是只對MyISAM表使用。 當(dāng)從一個文件中裝載一個表時，使用LOAD DATA INFILE。這個通常比使用很多insert語句快20倍。 3.4.3 優(yōu)化group by語句 如果查詢包含group by但用戶想要避免排序結(jié)果的損耗，則可以使用order by null 來禁止排序：
如下沒有使用order by null來禁止排序

如下使用order by null的效果：
3.4.4 優(yōu)化order by語句

在某些情況中，MySQL可以使用一個索引來滿足order by子句，而不需要額外的排序。Where條件和order by使用相同的索引，并且order by的順序和索引順序相同，并且order by的字段都是升序或者都是降序。

3.4.5 優(yōu)化嵌套查詢

下面是采用嵌套查詢的效果（可以使用更有效的鏈接查詢JOIN替代）。