mysql語句的優(yōu)化

146次閱讀

共計 4428 個字符，預(yù)計需要花費 12 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“mysql 語句的優(yōu)化”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

MySQL 客戶端連接成功后，通過使用 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。
其中的 session 來表示當(dāng)前的連接的統(tǒng)計結(jié)果，global 來表示自數(shù)據(jù)庫上次啟動至今的統(tǒng)計結(jié)果, 默認(rèn)是 session 級別的。

下面的例子：
show status like lsquo;Com_% rsquo;; 其中 Com_XXX 表示 XXX 語句所執(zhí)行的次數(shù). 重點注意：Com_select,Com_insert,Com_update,Com_delete 通過這幾個參數(shù)，
可以容易地了解到當(dāng)前數(shù)據(jù)庫的應(yīng)用是以插入更新為主還是以查詢操作為主，以及各類的 SQL 大致的執(zhí)行比例是多少。

還有幾個常用的參數(shù)便于用戶了解數(shù)據(jù)庫的基本情況。
Connections：試圖連接 MySQL 服務(wù)器的次數(shù) show status like Connections
Uptime：服務(wù)器工作的時間（單位秒）show status like Uptime
Slow_queries：慢查詢的次數(shù) (默認(rèn)是 10) show status like lsquo;Slow_queries rsquo;
如何查詢 mysql 的慢查詢時間
Show variables like long_query_time
修改 mysql 慢查詢時間
set long_query_time=2

*** 如何定位慢查詢
Show variables like long_query_time
可以重新設(shè)置 set long_query_time=2
**** 測試語句 ***
select * from emp e,dept d where e.empno=123451 and e.deptno=d.deptno;
如果帶上 order by e.empno 速度就會更慢，有時會到 1min 多.
**** 在默認(rèn)情況下 mysql 不記錄慢查詢?nèi)罩荆枰趩拥臅r候指定 bin\mysqld.exe – -slow-query-log
該慢查詢?nèi)罩緯旁?data 目錄下 [在 mysql5.0 這個版本中時放在 mysql 安裝目錄 /data/ 下], 在 mysql5.5.19 下是需要查看
my.ini 的 datadir= C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/“
來確定.

Explain select * from emp where ename=“zrlcHd”會產(chǎn)生如下信息：
select_type: 表示查詢的類型。
table: 輸出結(jié)果集的表
type: 表示表的連接類型
possible_keys: 表示查詢時，可能使用的索引
key: 表示實際使用的索引
key_len: 索引字段的長度
rows: 掃描的行數(shù) www.2cto.com
Extra: 執(zhí)行情況的描述和說明

說起提高數(shù)據(jù)庫性能，索引是最物美價廉的東西了。不用加內(nèi)存，不用改程序，不用調(diào) sql，只要執(zhí)行個正確的 rsquo;create index rsquo;，
查詢速度就可能提高百倍千倍，這可真有誘惑力。可是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，
這些寫操作，增加了大量的 I /O。
是不是建立一個索引就能解決所有的問題?ename
上沒有建立索引會怎樣?
select * from emp where ename= lsquo;axJxC rsquo;;

索引的代價
磁盤占用
對 dml(update delete insert)語句的效率影響
** 四種索引的類型可以通過 myadmin 創(chuàng)建一個索引的時候看到
** 簡述 mysql 四種索引的區(qū)別
PRIMARY 索引 =》在主鍵上自動創(chuàng)建
UNIQUE 索引 = 相當(dāng)于 INDEX + Unique
INDEX 索引 = 就是普通索引
FULLTEXT = 只在 MYISAM 存儲引擎支持, 目的是全文索引，在內(nèi)容系統(tǒng)中用的多，在全英文網(wǎng)站用多(英文詞獨立). 中文數(shù)據(jù)不常用，意義不大國內(nèi)全文索引通常使用 sphinx 來完成.

** 復(fù)合索引
create index 索引名 on 表名(列 1，列 2);
建立索引:create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , hellip;..); alter table table_name ADD INDEX [index_name] (index_col_name,…)
添加主鍵 (索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 聯(lián)合主鍵
刪除索引 DROP INDEX index_name ON tbl_name; alter table table_name drop index index_name; www.2cto.com
刪除主鍵 (索引) 比較特別: alter table t_b drop primary key;
查詢索引 (均可) show index from table_name; show keys from table_name; desc table_Name;
下列的表將不使用索引：
1，如果條件中有 or，即使其中有條件帶索引也不會使用。
2，對于多列索引，不是使用的第一部分，則不會使用索引。
3，like 查詢是以 % 開頭
4，如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引。
5，如果 mysql 估計使用全表掃描要比使用索引快，則不使用索引。

添加一個主鍵索引
alter table dept add primary key (deptno)
– 測試語句
explain select * from dept where deptno=105\G;
結(jié)果是:
mysql explain select * from dept where deptno=105\G;
*************************** 1. row ***************************
id: 1 www.2cto.com
select_type: SIMPLE
table: dept
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: const
rows: 1
Extra:
1 row in set (0.00 sec)

– 創(chuàng)建多列索引
alter table dept add index myind (dname,loc);
– 證明對于創(chuàng)建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用
explain select * from dept where dname= lsquo;rjTUPqjZvf rsquo;\G; 會顯示使用到了索引 myind
explain select * from dept where loc= lsquo;MsBDpMRX rsquo;\G; 不會顯示使用到了索引 myind
– 對于使用 like 的查詢
explain select * from dept where dname like lsquo;%rjTUPqjZvf rsquo;\G; 不會顯示使用到了索引 myind
explain select * from dept where dname like lsquo;rjTUPqjZvf% rsquo;\G; 會顯示使用到了索引 myind
– 如果條件中有 or，即使其中有條件帶索引也不會使用
– 為了演示，我們把復(fù)合索引刪除，然后只在 dname 上加入索引.
alter table dept drop index myind www.2cto.com
alter table dept add index myind (dname)
explain select * from dept where dname= lsquo;aaa rsquo; or loc= lsquo;aa rsquo;\G;// 就不會使用到 dname 列上的
– 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引
select * from dept from dname=1234\G // 不會使用到索引
select * from dept from dname= lsquo;1234 rsquo;\G // 會使用到索引

查看索引的使用情況 show status like lsquo;Handler_read% rsquo;; handler_read_key: 這個值越高越好，越高表示使用索引查詢到的次數(shù)。
handler_read_rnd_next: 這個值越高，說明查詢低效。

有些情況下，可以使用連接來替代子查詢。因為使用 join，MySQL 不需要在內(nèi)存中創(chuàng)建臨時表。
如果想要在含有 or 的查詢語句中利用索引，則 or 之間的每個條件列都必須用到索引，如果沒有索引，則應(yīng)該考慮增加索引
MyISAM 在插入數(shù)據(jù)時，默認(rèn)放在最后., 刪除數(shù)據(jù)后，空間不回收.(不支持事務(wù)和外鍵)
InnoDB 支持事務(wù)和外鍵

在精度要求高的應(yīng)用中，建議使用定點數(shù)來存儲數(shù)值，以保證結(jié)果的準(zhǔn)確性
create table temp1(t1 float(10,2), t2 decimal(10,2));
insert into temp1 values(1000000.32,1000000,32); 發(fā)現(xiàn) t1 成了 1000000.31 所以有問題.
對于存儲引擎是 MyISAM 的, 如果經(jīng)常做刪除和修改記錄的操作，要定時執(zhí)行 optimize table table_name; 功能對表進(jìn)行碎片整理。
create table temp2(id int) engine=MyISAM;
insert into temp2 values(1); insert into temp2 values(2); insert into temp2 values(3);
insert into temp2 select * from temp2;– 復(fù)制
delete from temp2 where id=1; 發(fā)現(xiàn) 該表對于的數(shù)據(jù)文件沒有變小
定期執(zhí)行 optimize table temp2 發(fā)現(xiàn)表大小變化，碎片整理完畢
對于 InnoDB 它的數(shù)據(jù)會存在 data/ibdata1 目錄下，在 data/ 數(shù)據(jù)庫 / 只有一個 *.frm 表結(jié)構(gòu)文件.

如果一個表的記錄數(shù)太多了, 如果我拆成 100 個表，那么每個表只有 10 萬條記錄。一個好的拆分依據(jù)是最重要的。UNION
有些表記錄數(shù)并不多，可能也就 2、3 萬條，但是字段卻很長，表占用空間很大，檢索表時需要執(zhí)行大量 I /O，嚴(yán)重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關(guān)系。(JOIN)