mysql語句的優化

本篇內容介紹了“mysql語句的優化”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

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

下面的例子：
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';
修改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)語句的效率影響
**四種索引的類型可以通過myadmin創建一個索引的時候看到
**簡述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;功能對表進行碎片整理。 
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)

“mysql語句的優化”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！