mysql之優化語句
MySQL客戶端連接成功後，通過使用show [session|global] status 命令可以提供服務器狀態信息。
其中的session來表示當前的連接的統計結果，global來表示自數據庫上次啟動至今的統計結果,默認是session級別的。 www.2cto.com
下面的例子：
show status like ‘Com_%’; 其中Com_XXX表示XXX語句所執行的次數.重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數，
可以容易地了解到當前數據庫的應用是以插入更新為主還是以查詢操作為主，以及各類的SQL大致的執行比例是多少。
還有幾個常用的參數便於用戶了解數據庫的基本情況。
Connections：試圖連接MySQL服務器的次數 show status like 'Connections'
Uptime：服務器工作的時間（單位秒）show status like 'Uptime'
Slow_queries：慢查詢的次數 (默認是10) show status like ‘Slow_queries’
如何查詢mysql的慢查詢時間
Show variables like 'long_query_time';
www.2cto.com
修改mysql 慢查詢時間
set long_query_time=2
***如何定位慢查詢
Show variables like 'long_query_time';
可以重新設置 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多.
****在默認情況下mysql不記錄慢查詢日志，需要在啟動的時候指定bin\mysqld.exe - -slow-query-log
該慢查詢日志會放在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”會產生如下信息：
select_type:表示查詢的類型。
table:輸出結果集的表
type:表示表的連接類型
possible_keys:表示查詢時，可能使用的索引
key:表示實際使用的索引
key_len:索引字段的長度
rows:掃描的行數 www.2cto.com
Extra:執行情況的描述和說明
說起提高數據庫性能，索引是最物美價廉的東西了。不用加內存，不用改程序，不用調sql，只要執行個正確的’create index’，
查詢速度就可能提高百倍千倍，這可真有誘惑力。可是天下沒有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，
這些寫操作，增加了大量的I/O。
是不是建立一個索引就能解決所有的問題?ename
上沒有建立索引會怎樣?
select * from emp where ename=‘axJxC’;
索引的代價
磁盤占用
對dml(update delete insert)語句的效率影響
**四種索引的類型可以通過phpmyadmin創建一個索引的時候看到
**簡述mysql四種索引的區別
PRIMARY 索引 =》在主鍵上自動創建
UNIQUE 索引=> 相當於INDEX + Unique
INDEX 索引=>就是普通索引
FULLTEXT => 只在MYISAM 存儲引擎支持, 目的是全文索引，在內容系統中用的多， 在全英文網站用多(英文詞獨立). 中文數據不常用，意義不大 國內全文索引通常 使用 sphinx 來完成.
**復合索引
create index 索引名 on 表名(列1，列2);
建立索引:create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..); alter table table_name ADD INDEX [index_name] (index_col_name,...)
添加主鍵(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 聯合主鍵
刪除索引 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，如果列類型是字符串，那一定要在條件中將數據使用引號引用起來。否則不使用索引。
5，如果mysql估計使用全表掃描要比使用索引快，則不使用索引。
添加一個主鍵索引
alter table dept add primary key (deptno)
--測試語句
explain select * from dept where deptno=105\G;
結果是:
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)
--創建多列索引
alter table dept add index myind (dname,loc);
--證明對於創建的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用
explain select * from dept where dname=‘rjTUPqjZvf’\G; 會顯示使用到了索引myind
explain select * from dept where loc=‘MsBDpMRX’\G; 不會顯示使用到了索引myind
--對於使用like的查詢
explain select * from dept where dname like ‘%rjTUPqjZvf’\G; 不會顯示使用到了索引myind
explain select * from dept where dname like ‘rjTUPqjZvf%’\G; 會顯示使用到了索引myind
--如果條件中有or，即使其中有條件帶索引也不會使用
--為了演示，我們把復合索引刪除，然後只在dname上加入索引.
alter table dept drop index myind www.2cto.com
alter table dept add index myind (dname)
explain select * from dept where dname=‘aaa’ or loc=‘aa’\G;//就不會使用到dname列上的
--如果列類型是字符串，那一定要在條件中將數據使用引號引用起來。否則不使用索引
select * from dept from dname=1234\G //不會使用到索引
select * from dept from dname=‘1234’\G //會使用到索引
查看索引的使用情況show status like ‘Handler_read%’; handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。
handler_read_rnd_next:這個值越高，說明查詢低效。
有些情況下，可以使用連接來替代子查詢。因為使用join，MySQL不需要在內存中創建臨時表。
如果想要在含有or的查詢語句中利用索引，則or之間的每個條件列都必須用到索引，如果沒有索引，則應該考慮增加索引
MyISAM 在插入數據時，默認放在最後.,刪除數據後，空間不回收.(不支持事務和外鍵)
InnoDB支持事務和外鍵
在精度要求高的應用中，建議使用定點數來存儲數值，以保證結果的准確性
create table temp1( t1 float(10,2), t2 decimal(10,2));
insert into temp1 values(1000000.32,1000000,32); 發現 t1 成了 1000000.31 所以有問題.
對於存儲引擎是MyISAM的數據庫,如果經常做刪除和修改記錄的操作，要定時執行optimize table table_name;功能對表進行碎片整理。 www.2cto.com
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;--復制
delete from temp2 where id=1; 發現 該表對於的數據文件沒有變小
定期執行 optimize table temp2 發現表大小變化，碎片整理完畢
&&對於InnoDB它的數據會存在data/ibdata1目錄下，在data/數據庫/只有一個 *.frm表結構文件.
如果一個表的記錄數太多了,如果我拆成100個表，那麼每個表只有10萬條記錄。一個好的拆分依據是 最重要的。UNION
有些表記錄數並不多，可能也就2、3萬條，但是字段卻很長，表占用空間很大，檢索表時需要執行大量I/O，嚴重降低了性能。這個時候需要把大的字段拆分到另一個表，並且該表與原表是一對一的關系。 (JOIN)
作者 ljfbest
