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

轉(zhuǎn)載于:https://www.cnblogs.com/lusunlufar/p/4217992.html

總結(jié)

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

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。