一、Count()和Max()的優化方法 1、查詢最后支付時間-優化max()函數 1)語句：select max(payment_date) from payment; 2)查看執行計劃：explain?select max(payment_date) from payment \G 3)優化方案（建立索引）：create index idx_paydate on payment(payment_date); 2、 在一條SQL中同時查出2006年和2007年電影的數量-優化count()函數 錯誤的方式： 1)select count(release_year = '2006' or release_year = '2007') from film; ?// 無法分開計算2006年和2007年的電影數量 2)select count(*) from film where release_year = '2006' and release_year = '2007'; ?// release_year不可能同時為2006和2007，邏輯錯誤 正確的方式： select count(release_year = '2006' or null) as '2006年電影數量',count(release_year = '2007' or null) as '2007年電影數量' from film; count(*)和count(某一列)討論： 1)它們值可能不同，count(某一列)所結果是不包含空值(null)的行，而count(*)是包含空值(null)的那行。 二、子查詢的優化 通常情況下，需要把子查詢優化為join查詢，但在優化時要注意關聯鍵是否有一對多的關系，要注意重復數據（使用distinct去重）。 三、優化group by查詢 優化前：explain select actor.first_name, actor.last_name, count(*) from skila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id; 優化后：explain select actor.first_name, actor.last_name, c.cnt from sakila.actor inner join ( select actor_id, count(*) as cnt from sakila.film_actor group by actor_id ) as c using(actor_id); 四、優化limit查詢 limit常用于分頁處理，時常會伴隨order by從句使用，因此大多時候會使用filesorts這樣會造成大量的IO問題。 優化前：select fiilm_id, description from sakila.film order by title limit 50, 5; 優化步驟1：使用有索引的列或主鍵進行order by操作 select film_id, description from sakila.film order by film_id limit 50, 5; 優化步驟2：記錄上次返回的主鍵，在下次查詢時使用主鍵過濾 select film_id, des機cription from sakila.flim where film_id > 55 and film_id <= 60 order by film_id limit 1, 5; ?// 避免了數據量大時掃描過多的記錄(要求主鍵是順序增長) 五、如何選擇合適的列建立索引 1、在where從句，group by從句，order by從句，on從句中出現的列 2、索引字段越小越好（原因：MySQL的每次讀取都以頁為單位，如果頁中存儲的數量越大，則一次IO操作獲取的數據量就越大，查詢的效率就越高） 3、離散度大的列放到聯合索引的前面（離散度越大的列的可選擇性越高，因為放在聯全索引的前面效率就越好） select * from payment where staff_id = 2 and customer_id = 584; 選擇index(staff_id,customer_id)還是index(customer_id,staff_id)？ ?由于customer_id的離散度更大，所以應該使用Index(customer_id,staff_id) 判斷列的離散程度： select count ( distinct customer_id ), count ( distinct staff_id ) from payment; ?//?唯一值越多則離散度越大 ps：若個索引包含了查詢中的所有列，則稱該索引為覆蓋索引。當我們查詢的執行頻率非常高，并且查詢中所包含的列比較少時，可使用覆蓋索引對SQL進行優化。 六、索引的維護及優化---重復及冗余索引 增加索引能提高查詢(select)效率，但會影響寫入操作（insert、update、delete）的效率。 過多的索引會影響寫入操作的效率，同樣也會影響查詢效率。 重復索引是指相同的列以相同的順序建立的同類型的索引，如下表中primary key 和 id 列上的索引就是重復索引 create table test( id int not null?primay key, name varchar(10) not null, title varchar(50) not null, ?unique(id) )engine=innodb; 冗余索引是指多個索引的前綴列相同，或是在聯合索引中包含了主鍵的索引，下面這個列子中key(name,id)就是一個冗余索引 create table test( id int not null?primay key, name varchar(10) not null, title varchar(50) not null, ? key(name,id) )engine=nonodb; 七、索引的維護及優化---查找重復及冗余索引（使用工具更為方便） select a.table_schema as '數據名', a.table_name as '表名', a.index_name as '索引1', b.index_name as '索引2',? a.column_name as '重復列名' from statistics a join statistics b? on a.table_schema=b.table_schema and a.table_name=b.table_name and a.seq_in_index=b.seq_in_index and a.column_name=b.column_name where a.seq_in_index=1 and a.index_name<>b.index_name 八、索引的維護及優化---刪除不用索引

轉載于:https://www.cnblogs.com/lusunlufar/p/4217992.html

總結

以上是生活随笔為你收集整理的SQL语句及索引优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。