mysql------SQL語(yǔ)句優(yōu)化

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

分析表的語(yǔ)法如下：（檢查一個(gè)或多個(gè)表是否有錯(cuò)誤）

3.3.2定期優(yōu)化表

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

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

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

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

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

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

對(duì)于MyISAM存儲(chǔ)引擎的表，可以通過以下方式快速的導(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表非唯一索引的更新，可以提高速度，
注意：對(duì)InnoDB表無效。
注意：對(duì)InnoDB表無效。

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

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

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

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

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

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

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

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