mysql語句的優(yōu)化

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

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

下面的例子：
show status like ‘Com_%’; 其中Com_XXX表示XXX語句所執(zhí)行的次數(shù).重點注意：Com_select,Com_insert,Com_update,Com_delete通過這幾個參數(shù)，
可以容易地了解到當前數(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ù) (默認是10) show status like ‘Slow_queries’
如何查詢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多.
****在默認情況下mysql不記錄慢查詢?nèi)罩?，需要在啟動的時候指定bin\mysqld.exe - -slow-query-log
該慢查詢?nèi)罩緯旁赿ata目錄下[在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í)行個正確的’create index’，
查詢速度就可能提高百倍千倍，這可真有誘惑力?？墒翘煜聸]有免費的午餐，查詢速度的提高是以插入、更新、刪除的速度為代價的，
這些寫操作，增加了大量的I/O。 
是不是建立一個索引就能解決所有的問題?ename
上沒有建立索引會怎樣?
select * from emp where ename=‘axJxC’;
 
索引的代價
磁盤占用
對dml(update delete insert)語句的效率影響
**四種索引的類型可以通過myadmin創(chuàng)建一個索引的時候看到
**簡述mysql四種索引的區(qū)別
PRIMARY 索引 =》在主鍵上自動創(chuàng)建
UNIQUE 索引=> 相當于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] , …..); 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=‘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，即使其中有條件帶索引也不會使用
--為了演示，我們把復(fù)合索引刪除，然后只在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列上的
--如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號引用起來。否則不使用索引
select * from dept from dname=1234\G //不會使用到索引
select * from dept from dname=‘1234’\G //會使用到索引
 
查看索引的使用情況show status like ‘Handler_read%’; handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數(shù)。
handler_read_rnd_next:這個值越高，說明查詢低效。
 
有些情況下，可以使用連接來替代子查詢。因為使用join，MySQL不需要在內(nèi)存中創(chuàng)建臨時表。
如果想要在含有or的查詢語句中利用索引，則or之間的每個條件列都必須用到索引，如果沒有索引，則應(yīng)該考慮增加索引
MyISAM 在插入數(shù)據(jù)時，默認放在最后.,刪除數(shù)據(jù)后，空間不回收.(不支持事務(wù)和外鍵)
InnoDB支持事務(wù)和外鍵
 
在精度要求高的應(yīng)用中，建議使用定點數(shù)來存儲數(shù)值，以保證結(jié)果的準確性
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;功能對表進行碎片整理。 
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，嚴重降低了性能。這個時候需要把大的字段拆分到另一個表，并且該表與原表是一對一的關(guān)系。 (JOIN)

“mysql語句的優(yōu)化”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！