sql索引優化

㈠ sql優化及原理詳解，五分鍾讀懂sql優化

在我而言這算是一個復習，然後總結出來給大家當個教材吧。
我也是看視頻總結出來的筆記，所以說的都很簡單和淺薄。有不全面或者偏頗的地方歡迎指出，共同交流進步哈。（因為我當時是看視頻總結的筆記，所以可能說的比較雜亂，我盡量寫的分明一點，在最後會附上筆記，忽略我字丑）

索引是什麼呢？它相當於字典的目錄。
索引：index是幫助mysql高效獲取數據的數據結構，索引是數據結構（樹，默認是B樹），hash等。
索引的弊端: 事物都是兩面的，有利必然有弊。

索引的優勢： 索引有這么多弊端我們還使用的原因是因為優大於劣。

索引的分類：

舉個小例子讓大家更理解復合索引：如果我把一個表中name，age這兩個列做成復合索引（注意順序很重要）。那麼我們形成的目錄一級目錄是name，二級目錄是age。在name相同時才會age再形成目錄。因為它本身的排序不是像目錄一樣一行一行列出來的，所以我們盡量用目錄來想像它比較好理解。下面是圖解：

有幾點注意的事項：

這里說一下，上面說的方法都是原生的sql，比如我現在習慣使用navicat，所以可以直接操作。。爽的不行。

然後刪除查詢也都是直接可視的，方便的不得了。就不多說了。

mysql做例子，還有個引擎是可以優化的。mysql中引擎分兩種：

sql優化等級：

上面說的這些等級在explain中可以看到。

單表優化常用方法：

多表優化常用方法：

因為上面也提到了b樹，所以還是單獨聊聊吧。其實我也不是很理解。只能說一個淺顯的認識而已。這里也就是簡單的說一下。
首先，B樹不僅可以二叉，還可以三叉，多叉。而只要大於二叉的都叫做BTree。
據說三層BTree可以存放上百萬數據。
BTree一般都指B+樹，數據全部存放在葉節點中。（這里簡單的一個三叉樹圖）

好了，就寫到這里吧，希望日後演算法的知識會的更多以後能把B樹這個坑填完~~~然後有不同意見或者自己理解的可以留言或者私聊。
全文手打，如果你覺得對你有幫助麻煩點個贊點個關注啥的~~

㈡ 如何在 SQL 資料庫優化 索引，SQL索引優化

1、主鍵就是聚集索引
2、只要建立索引就能顯著提高查詢速度
3、把所有需要提高查詢速度的欄位都加進聚集索引，以提高查詢速度

(四)其他書上沒有的索引使用經驗總結
1、用聚合索引比用不是聚合索引的主鍵速度快
2、用聚合索引比用一般的主鍵作order by時速度快，特別是在小數據量情況下
3、使用聚合索引內的時間段，搜索時間會按數據占整個數據表的百分比成比例減少，而無論聚合索引使用了多少個
4 、日期列不會因為有分秒的輸入而減慢查詢速度

(五)其他注意事項
1. 不要索引常用的小型表
2. 不要把社會保障號碼(SSN)或身份證號碼(ID)選作鍵
3. 不要用用戶的鍵
4. 不要索引 memo/notes 欄位和不要索引大型文本欄位(許多字元)
5. 使用系統生成的主鍵

二、改善SQL語句
1、Like語句是否屬於SARG取決於所使用的通配符的類型
2、or 會引起全表掃描
3、非操作符、函數引起的不滿足SARG形式的語句
4、IN 的作用相當與OR
5、盡量少用NOT
6、exists 和 in 的執行效率是一樣的
7、用函數charindex()和前面加通配符%的LIKE執行效率一樣
8、union並不絕對比or的執行效率高
9、欄位提取要按照「需多少、提多少」的原則，避免「select *」
10、count(*)不比count(欄位)慢
11、order by按聚集索引列排序效率最高
12、高效的TOP

㈢ sql調優的幾種方式

你好，
SQL優化的一些方法
1.對查詢進行優化，應盡量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。
2.應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描。
3.應盡量避免在 where 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。
4.應盡量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描。
5.in 和 not in 也要慎用，否則會導致全表掃描，
6.下面的查詢也將導致全表掃描：
select id from t where name like '%abc%'
7.應盡量避免在 where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。
8.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。
9.不要在 where 子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。
10.在使用索引欄位作為條件時，如果該索引是復合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應盡可能的讓欄位順序與索引順序相一致。

㈣ MySQL性能優化之索引設計

上一篇給小夥伴們講了關於SQL查詢性能優化的相關技巧，一個好的查詢SQL離不開合理的索引設計。這篇小二就來嘮一嘮怎麼合理的設計一個索引來優化我們的查詢速度，要是有不合理的地方...嗯..

當然啦，開個玩笑，歡迎小夥伴們指正！

通常情況下，欄位類型的選擇是需要根據業務來判斷的，通常需要遵循以下幾點。

下列各種類型表格內容來自菜鳥教程，權當備忘。

優化建議：

注意： INT(2)設置的為顯示寬度，而不是整數的長度，需要配合 ZEROFILL 使用 。

例如 id 設置為 TINYINT(2) UNSIGNED ，表示無符號，可以存儲的最大數值為255，其中 TINYINT(2) 沒有配合 ZEROFILL 實際沒有任何意義，例如插入數字200，長度雖然超過了兩位，但是這個時候是可以插入成功的，查詢結果同樣為200；插入數字5時，同樣查詢結果為5。

而 TINYINT(2) 配合 ZEROFILL 後，當插入數字5時，實際存儲的還是5，不過在查詢是MySQL會在前面補上一個0，即查詢出來的實際為 05 。

優化建議：

優化建議：

通常來說，考慮好表中每個欄位應該使用什麼類型和長度，建完表需要做的事情不是馬上建立索引，而是先把相關主體業務開發完畢，然後把涉及該表的SQL都拿出來分析之後再建立索引。

盡量少建立單值索引( 唯一索引除外 )，應當設計一個或者兩三個聯合索引，讓每一個聯合索引都盡量去包含SQL語句中的 where、order by、group by 的欄位，同時確保聯合索引的欄位順序盡量滿足SQL查詢的最左前綴原則。

索引基數是指這個欄位在表裡總共有多少個不同的值，比如一張表總共100萬行記錄，其中有個性別欄位，性別一共有三個值：男、女、保密，那麼該欄位的基數就是3。

如果對這種小基數欄位建立索引的話，因為索引樹中只有男、女、保密三個值，根本沒法進行快速的二分查找，同時還需要回表查詢，還不如全表掃描嘞。

一般建立索引，盡量使用那些基數比較大的欄位，那麼才能發揮出B+樹快速二分查找的優勢來。

在 where 和 order by 出現索引設計沖突時，是優先針對where去設計索引？還是優先針對order by設計索引？

通常情況下都是優先針對 where 來設計索引，因為通常情況下都是先 where 條件使用索引快速篩選出來符合條件的數據，然後對進行篩選出來的數據進行排序和分組，而 where 條件快速篩選出來的的數據往往不會很多。

對生產實際運行過程中，或者測試環境大數據量測試過程中發現的慢查詢SQL進行特定的索引優化、代碼優化等策略。

終於輪到實戰了，小二最喜歡實戰了。

寫到這里不得不吐槽一下，這個金三銀四的跳槽季節，年前提離職了，結果離職還沒辦完就封村整整兩個禮拜了，嗚嗚嗚...

上節小二就提到會有個很有意思的小案例，那麼在疫情當下，門都出不去的日子，感覺這個例子更有意思了，咱們來討論一下各種社交平台怎麼做的用戶信息搜索呢。

社交平台有一個小夥伴們都喜歡的功能，搜索好友信息，比如小二熟練的點開省份...城市..性別..年齡..身高...

咳咳咳...小二怎麼可能幹這種事情，小二的心裡只有代碼，嗯...沒錯，就是這樣。

這個就可以說是對於用戶信息的查詢篩選了，通常這種表都是非常大數據量的，在不考慮分庫分表的情況下，怎麼通過索引配合SQL來優化呢？

通常我們在編寫SQL是會寫出類似如下的SQL來執行，有 where、order by、limit 等條件來查詢。

那麼接下來小二一個一個慢慢增加欄位來分析分析，怎麼根據業務場景來設計索引。

針對這種情況，很簡單，設計一個聯合索引 (provice, city, sex) 就完事了。

那麼這時候有小夥伴就會說了，很簡單啊，范圍欄位放最後咱還是知道的，聯合索引改成 (provice, city, sex, age) 不就可以了。

嗯，是的，這么干沒毛病，但是小夥伴們有沒有想過有些人萬一既喜歡帥哥又喜歡美女，別想歪了哈...，挺多小姐姐就既喜歡帥哥又喜歡美女的。

那麼這個時候小姐姐就不搜索性別了，那麼這個時候聯合索引只能用到前兩個欄位了，那麼不符合咱們的專業標准啊，咋辦呢？這時候還是有辦法的，咱們只需要動動小腦袋改改SQL就行了，在沒有選擇性別時判斷一下，改成下面這樣就可以了。

咋辦嘞，同樣往聯合索引裡面塞，例如 (provice, city, sex, hobby, xx, age) 。

針對這種多個范圍查詢的話，為了比較好的利用索引，在業務允許的情況下可以使用固定范圍，然後資料庫欄位存儲范圍標識就可以了，這樣就轉化為了等值匹配，就可以很好地利用索引了。

例如最後登錄時間欄位不記錄最後登錄時間，而是記錄設置欄位 is_login_within_seven_days 在7天內有登錄則為1，否則為0，最後索引設計成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。

那麼根據場景最後設計出來的這個索引可能已經可以覆蓋大部分的查詢流量了，那麼如果還有其他一部分熱度比較高的查詢怎麼辦呢，辦法也很簡單啊，再加一兩個索引即可。

例如通常會查詢這個城市比較受歡迎(評分：score)的小姐姐，這時候添加一個聯合索引 (provice, city, sex, score) 那麼就可以了。

可以看出，索引時必須結合場景來設計的，思路就是盡量用不超過3個復雜的聯合索引來抗住大部分的80%以上的常用查詢流量，然後再用一兩個二級索引來抗下一些非常用查詢流量。

以上就是小二要給大家分享的索引設計，如果能動動你發財的小手給小二點個免費的贊就更好啦~

下篇小二就來講講MySQL事務和鎖機制。

㈤ SQL優化（二）

SQL優化一： sql優化（一）

上片文章已經詳細介紹了explain各個欄位的含義，以及什麼情況應該建立索引，什麼情況不需要建立索引以及sql語句性能的判斷依據，接下來我介紹下如何合理的建立索引。

sql語句：select id，author_id from article where category_id = 1 and comments>1 order by views desc limit 1;

分析：首先我們根據where後面的條件建立符合索引，然後根據order by後面的欄位建立索引，因此建立索引idx_article_ccv，即以（category_id，comments，views）數據列建立復合索引，但由於comments是一個范圍，按照BTree索引的原理，先排序category_id,如果遇到相同的category_id則再排序comments，如果遇到相同的comments則再排序views，又因為comments欄位在復合索引里處於中間位置，而comments>1是一個條件（是一個范圍值），在復合索引的一個范圍值的數據列後面的索引全部失效，mysql無法利用索引再對後面的views部分進行檢索，也就是說views無法按照索引排序，所以explain下此sql語句，type為range，extra使用的是Using filesort，這是比較糟糕的。所以我們放棄comments這個范圍欄位，建立索引idx_article_cv，即以（category_id，views）數據列建立復合索引，explain 此sql，type變成了ref，extra的using filesort也變成了using index，這就變得好多了。

索引：idx_article_cv，即以（category_id，views）數據列建立復合索引

前段時間做了一個銷售精細化項目，是公司crm項目的一個大模塊，大致就是為銷售人員制定指標，實現銷售目標從區域到團到業務員到客戶，實時跟蹤業務員所負責客戶的下單量的情況。這就存在許多關聯關系，區域-團，團-業務員，業務員-客戶，這使得sql常常需要關聯多張表。

sql語句：SELECT

tu.fuserid,

tu.faccount,

tu.fphone,

tu.fcertificationtype,

tu.fcertificatename,

tu.fkeyarea,

tu.fkeyareatext,

DATE_FORMAT(tcr.fupdatetime,'%Y-%m-%d %H:%i:%s') as fupdatetime,

tag.forggroupid,

tag.forggroupname,

tug.forguserid,

tug.fusername,

tug.fuserphone,

tag.fcitycode

FROM t_finedt_user AS tu

LEFT JOIN t_finedt_customer_relation AS tcr

ON tu.fuserid = tcr.fuserid

LEFT JOIN t_finedt_usergroup AS tug

ON tcr.forguserid = tug.forguserid

and tcr.forggroupid = tug.forggroupid

LEFT JOIN t_finedt_areagroup AS tag

ON tug.forggroupid = tag.forggroupid

where tu.fkeyarea=? and tu.fuserid=? and tug.forggroupid = ?

分析：上面的sql是左連接，左邊的表一定是全表查詢，所以要建立右邊表對應關聯欄位的索引，在表t_finedt_user上建立tu_fuserid_fkeyarea索引，即以(fuserid，fkeyarea)欄位建立索引，在表t_finedt_customer_relation 上建立tcr_forguserid_forggroupid索引，即以（forguserid，forggroupid）欄位建立索引，在表t_finedt_usergroup 上建立tug_forguserid_forggroupid索引，即以（forguserid,forggroupid)欄位建立索引，在表t_finedt_areagroup上建立tag_forggroupid索引，即以（forggroupid)欄位建立索引。建立索引後，sql查詢速度明顯快了很多

索引：tcr_forguserid_forggroupid，tu_fuserid_fkeyarea，tug_forguserid_forggroupid，tag_forggroupid

1、盡可能減少join語句中的NestedLoop的循環次數，永遠用小結果集驅動大結果集

2、優先優化NestedLoop的內層循環

3、保證join語句總被驅動表上的join欄位已經被索引

4、當無法保證被驅動表join條件欄位被索引，且內存資源充足的前提下，不要太吝嗇joinBuffer的設置

1、全值匹配我最愛

2、最佳左前綴原則——如果索引了多列，要遵守最左前綴原則，指的是查詢從索引的最左前列開始並且不跳過索引中的列

3、並在索引列上做任何操作（計算、函數、自動or手動類型轉換），這些會導致索引失效而轉向全表掃描

4、存儲引擎不能使用索引中范圍條件右邊的列，范圍之後的索引全失效

5、盡量使用覆蓋索引（之訪問索引的查詢（索引列和查詢的列一致）），減少select *

6、mysql在使用不等於（！=、>、<）的時候無法使用索引會導致全表掃描。

7、is null、is not null也無法使用索引。

8、like以通配符開頭（"%abc.."），mysql索引失效也會變成全表掃描的操作。

9、字元串不加單引號也會引起索引失效

10、少用or，用它來連接時會索引失效。

1、對於單值索引，盡量選擇針對當前query過濾性更好的索引

2、在選擇組合索引的時候，當前query中過濾性最好的欄位在索引欄位順序中，位置越靠前越好

3、在選擇組合索引的時候，盡量選擇盡可能包含當前query中的where字句中更多欄位的索引

4、盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的。

全值匹配我最愛，最左前綴要遵守

帶頭大哥不能死，中間兄弟不能斷

索引列上少計算，范圍之後全失效

like百分寫最右，覆蓋索引不寫里

不等空值還有or，索引失效要少用

var引號不可丟，sql高級也不難

㈥ sql優化的N種方法

1.SQL語句中IN包含的值不應過多:
例如：select id from t where num in(1,2,3) 對於連續的數值，能用between就不要用in了； 實測速度差距不是很大.

2.SELECT語句務必指明欄位名稱:
禁止用 * 來查詢 ,禁止用 * 來查詢 ,禁止用 * 來查詢 , 查找哪個欄位,就寫具體的欄位.

select * from user_test WHERE address=15988;
select address from user_test WHERE address=15988;

3.只查詢一條數據的時候，使用limit 1
【這個很有用】

4.避免在where子句中對欄位進行null值判斷:
【實測：null值的判斷依然走了索引】
explain select uid from user_test WHERE phone is null;

5.避免在where子句中對欄位進行表達式操作:

6.對於聯合索引來說，要遵守最左前綴法則:
例如組合索引(id,name,sex) 使用的時候,可以id 或者id,name . 禁止直接name,或者sex.會導致聯合索引失敗

注意: id, name,sex 這三個欄位填寫順序不會有影響, mysql會自動優化成最左匹配的順序.

前三條sql都能命中索引,中間兩條由於不符合最左匹配原則,索引失效.

最後一條sql 由於有最左索引id 所以索引部分成功,部分失效. id欄位索引使用成功.

7.盡量使用inner join，避免left join：
如果連接方式是inner join，在沒有其他過濾條件的情況下MySQL會自動選擇小表作為驅動表，但是left join在驅動表的選擇上遵循的是左邊驅動右邊的原則，即left join左邊的表名為驅動表。
【實測：不是很准確，具體用explain測試】

8.注意范圍查詢語句:
對於聯合索引來說，如果存在范圍查詢，比如between、>、<等條件時，會造成後面的索引欄位失效。

解決辦法: 業務允許的情況下,使用 >= 或者<= 這樣不影響索引的使用.

explain select * from user_test where uid=10 and name='張三' and phone='13527748096';
explain select * from user_test where uid between( 1 and 10) and name ='張三' and phone='13527748096';

9.不建議使用%前綴模糊查詢:
例如 : LIKE「%name」或者LIKE「%name%」，這種查詢會導致索引失效而進行全表掃描。但是可以使用LIKE 「name%」。

explain select * from user_test where uid=10 and uid like "%1" ;
explain select * from user_test where uid=10 and uid like "1%" ;

10.在 where 子句中使用 or 來連接條件，如果or連接的條件有一方沒有索引,將導致引擎放棄使用索引而進行全表掃描
解決辦法: 將or連接的雙方都建立索引,就可以使用.

explain select * from user_test where uid=10 or name='張三';

11.應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。(此處存在疑點,我本人測試的時候,發現索引還是能使用到)

12.字元串類型的欄位 查詢的時候如果不加引號'' ,會導致自動進行隱式轉換,然後索引失效

㈦ mysql有幾種索引類型使用索引時都有那些地方要注意sql優化原則

MySQL索引類型包括：
一、普通索引
這是最基本的索引，它沒有任何限制。有以下幾種創建方式：
1.創建索引
代碼如下:

CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

2.修改表結構

代碼如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 創建表的時候直接指定。
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

-- 刪除索引的語法：
DROP INDEX [indexName] ON mytable;

二、唯一索引
它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：

代碼如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表結構
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 創建表的時候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主鍵索引
它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引：

代碼如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

當然也可以用 ALTER 命令。記住：一個表只能有一個主鍵。
四、組合索引
為了形象地對比單列索引和組合索引，為表添加多個欄位：

代碼如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

為了進一步榨取MySQL的效率，就要考慮建立組合索引。
二：使用索引的注意事項
使用索引時，有以下一些技巧和注意事項：
1.索引不會包含有NULL值的列
只要列中包含有NULL值都將不會被包含在索引中，復合索引中只要有一列含有NULL值，那麼這一列對於此復合索引就是無效的。所以我們在資料庫設計時不要讓欄位的默認值為NULL。
2.使用短索引
對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個CHAR(255)的列，如果在前10個或20個字元內，多數值是惟一的，那麼就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和I/O操作。
3.索引列排序
MySQL查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那麼order by中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。
4.like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like 「%aaa%」 不會使用索引而like 「aaa%」可以使用索引。
5.不要在列上進行運算

select * from users where YEAR(adddate)<2007;
將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成:

select * from users where adddate<『2007-01-01';

6.不使用NOT IN和<>操作。

三：sql優化原則
常見的簡化規則如下：
1.不要有超過5個以上的表連接（JOIN）
2.考慮使用臨時表或表變數存放中間結果。
3.少用子查詢
4.視圖嵌套不要過深,一般視圖嵌套不要超過2個為宜。
5.連接的表越多，其編譯的時間和連接的開銷也越大，性能越不好控制。
6.最好是把連接拆開成較小的幾個部分逐個順序執行。
7.優先執行那些能夠大量減少結果的連接。
8.拆分的好處不僅僅是減少SQL Server優化的時間，更使得SQL語句能夠以你可以預測的方式和順序執行。

如果一定需要連接很多表才能得到數據，那麼很可能意味著設計上的缺陷。

㈧ mysql有幾種索引類型使用索引時都有那些地方要注意sql優化原則是什麼

mysql的索引類型及使用索引時的注意事項有：

一、普通索引。這是最基本的索引，它沒有任何限制。它有以下幾種創建方式：

1、創建索引

代碼如下:

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR類型，length可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 length，下同。

2、修改表結構

代碼如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 創建表的時候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

-- 刪除索引的語法：

DROP INDEX [indexName] ON mytable;

二、唯一索引。它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。它有以下幾種創建方式：

代碼如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表結構
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 創建表的時候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主鍵索引。它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引：

代碼如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

當然也可以用 ALTER 命令。記住：一個表只能有一個主鍵。

四、組合索引。為了形象地對比單列索引和組合索引，為表添加多個欄位：

代碼如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );

為了進一步榨取MySQL的效率，就要考慮建立組合索引。就是將 name, city, age建到一個索引里：

代碼如下:

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表時，usernname長度為 16，這里用 10。這是因為一般情況下名字的長度不會超過10，這樣會加速索引查詢速度，還會減少索引文件的大小，提高INSERT的更新速度。

如果分別在 usernname，city，age上建立單列索引，讓該表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低於我們的組合索引。雖然此時有了三個索引，但MySQL只能用到其中的那個它認為似乎是最有效率的單列索引。

建立這樣的組合索引，其實是相當於分別建立了下面三組組合索引：usernname,city,age usernname,city usernname 為什麼沒有 city，age這樣的組合索引呢？這是因為MySQL組合索引「最左前綴」的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這三列的查詢都會用到該組合索引，下面的幾個SQL就會用到這個組合索引：

[code]
SELECT * FROM mytable WHREE username="admin" AND city="鄭州" SELECT * FROM mytable WHREE username="admin"

㈨ SQL優化萬能公式：5 大步驟 + 10 個案例

在應用開發的早期，數據量少，開發人員開發功能時更重視功能上的實現，隨著生產數據的增長，很多SQL語句開始暴露出性能問題，對生產的影響也越來越大，有時可能這些有問題的SQL就是整個系統性能的瓶頸。

1、通過慢查日誌等定位那些執行效率較低的SQL語句

2、explain 分析SQL的執行計劃

type由上至下，效率越來越高

Extra

3、show profile 分析

了解SQL執行的線程的狀態及消耗的時間。默認是關閉的，開啟語句「set profiling = 1;」

4、trace

trace分析優化器如何選擇執行計劃，通過trace文件能夠進一步了解為什麼優惠券選擇A執行計劃而不選擇B執行計劃。

5、確定問題並採用相應的措施

案例1、最左匹配

索引

SQL語句

查詢匹配從左往右匹配，要使用order_no走索引，必須查詢條件攜帶shop_id或者索引( shop_id , order_no )調換前後順序

案例2、隱式轉換

索引

SQL語句

隱式轉換相當於在索引上做運算，會讓索引失效。mobile是字元類型，使用了數字，應該使用字元串匹配，否則MySQL會用到隱式替換，導致索引失效。

案例3、大分頁

索引

SQL語句

對於大分頁的場景，可以優先讓產品優化需求，如果沒有優化的，有如下兩種優化方式， 一種是把上一次的最後一條數據，也即上面的c傳過來，然後做「c < xxx」處理，但是這種一般需要改介面協議，並不一定可行。另一種是採用延遲關聯的方式進行處理，減少SQL回表，但是要記得索引需要完全覆蓋才有效果，SQL改動如下

案例4、in + order by

索引

SQL語句

in查詢在MySQL底層是通過n*m的方式去搜索，類似union，但是效率比union高。in查詢在進行cost代價計算時（代價 = 元組數 * IO平均值），是通過將in包含的數值，一條條去查詢獲取元組數的，因此這個計算過程會比較的慢，所以MySQL設置了個臨界值(eq_range_index_pe_limit)，5.6之後超過這個臨界值後該列的cost就不參與計算了。因此會導致執行計劃選擇不準確。默認是200，即in條件超過了200個數據，會導致in的代價計算存在問題，可能會導致Mysql選擇的索引不準確。

處理方式，可以( order_status , created_at )互換前後順序，並且調整SQL為延遲關聯。

案例5、范圍查詢阻斷，後續欄位不能走索引

索引

SQL語句

范圍查詢還有「IN、between」

案例6、不等於、不包含不能用到索引的快速搜索。（可以用到ICP）

在索引上，避免使用NOT、!=、>、!、NOT EXISTS、NOT IN、NOT LIKE等

案例7、優化器選擇不使用索引的情況

如果要求訪問的數據量很小，則優化器還是會選擇輔助索引，但是當訪問的數據占整個表中數據的蠻大一部分時（一般是20%左右），優化器會選擇通過聚集索引來查找數據。

查詢出所有未支付的訂單，一般這種訂單是很少的，即使建了索引，也沒法使用索引。

案例8、復雜查詢

如果是統計某些數據，可能改用數倉進行解決；如果是業務上就有那麼復雜的查詢，可能就不建議繼續走SQL了，而是採用其他的方式進行解決，比如使用ES等進行解決。

案例9、asc和desc混用

desc 和asc混用時會導致索引失效

案例10、大數據

對於推送業務的數據存儲，可能數據量會很大，如果在方案的選擇上，最終選擇存儲在MySQL上，並且做7天等有效期的保存。那麼需要注意，頻繁的清理數據，會照成數據碎片，需要聯系DBA進行數據碎片處理。

㈩ 資料庫基礎詳解：存儲過程、視圖、游標、SQL語句優化以及索引

寫在文章前：本系列文章用於博主自己歸納復習一些基礎知識，同時也分享給可能需要的人，因為水平有限，肯定存在諸多不足以及技術性錯誤，請大佬們及時指正。

存儲過程 是事先經過編譯並存儲在資料庫中的一段SQL語句的集合。想要實現相應的功能時，只需要調用這個存儲過程就行了（類似於函數，輸入具有輸出參數）。

優點 ：

缺點 ：

Delete用來刪除表的全部或者部分數據，執行delete之後，用戶需要提交之後才會執行，會觸發表上的DELETE觸發器（包含一個OLD的虛擬表，可以只讀訪問被刪除的數據），DELETE之後表結構還在，刪除很慢，一行一行地刪，因為會記錄日誌，可以利用日誌還原數據；

Truncate刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器。操作比DELETE快很多（直接把表drop掉，再創建一個新表，刪除的數據不能找回）。如果表中有自增（AUTO_INCREMENT）列，則重置為1。

Drop命令從資料庫中刪除表，所有的數據行，索引和約束都會被刪除。不能回滾，不會觸發觸發器。

觸發器（TRIGGER）是由事件（比如INSERT/UPDATE/DELETE）來觸發運行的操作（不能被直接調用，不能接收參數）。在資料庫里以獨立的對象存儲，用於保證數據完整性（比如可以檢驗或轉換數據）。

約束（Constraint）類型：

從資料庫的基本表中通過查詢選取出來的數據組成的虛擬表（資料庫中只存放視圖的定義，而不存放視圖的數據）。可以對其進行增/刪/改/查等操作。視圖是對若干張基本表的引用，一張虛表，查詢語句執行的結果，不存儲具體的數據（基本表數據發生了改變，視圖也會跟著改變）。

可以跟基本表一樣，進行增刪改查操作( 增刪改操作有條件限制，一般視圖只允許查詢操作 )，對視圖的增刪改也會影響原表的數據。 它就像一個窗口，透過它可以看到資料庫中自己感興趣的數據並且操作它們。 好處：

用於定位在查詢返回的結果集的特定行，以對特定行進行操作。使用游標可以方便地對結果集進行移動遍歷，根據需要滾動或對瀏覽/修改任意行中的數據。主要用於互動式應用。它是一段私有的SQL工作區，也就是一段內存區域，用於暫時存放受SQL語句影響的數據，簡單來說，就是將受影響的數據暫時放到了一個內存區域的虛表當中，這個虛表就是游標。

游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。即游標用來逐行讀取結果集。游標充當指針的作用。盡管游標能遍歷結果中的所有行，但他一次只指向一行。

游標的一個常見用途就是保存查詢結果，以便以後使用。游標的結果集是由SELECT語句產生，如果處理過程需要重復使用一個記錄集，那麼創建一次游標而重復使用若干次，比重復查詢資料庫要快的多。通俗來說，游標就是能在sql的查詢結果中，顯示某一行（或某多行）數據，其查詢的結果不是數據表，而是已經查詢出來的結果集。

簡單來說：游標就是在查詢出的結果集中進行選擇性操作的工具。

讓緩存更高效。對於連接查詢，如果其中一個表發生變化，那麼整個查詢緩存就無法使用。而分解後的多個查詢，即使其中一個表發生變化，對其它表的查詢緩存依然可以使用。分解成多個單表查詢，這些單表查詢的緩存結果更可能被其它查詢使用到，從而減少冗餘的查詢。減少鎖競爭。

索引是對資料庫表中一列或多列的值進行排序的一種結構（說明是在列上建立的），使用索引可快速訪問資料庫表中的特定信息。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行相比，索引有助於更快地獲取信息。索引的一個主要目的就是加快檢索表中數據，亦即能協助信息搜索者盡快的找到符合限制條件的記錄ID的輔助數據結構。

當表中有大量記錄時，若要對表進行查詢，第一種搜索信息方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作。第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過保存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

例如這樣一個查詢：select * from table1 where id=10000。如果沒有索引，必須遍歷整個表，直到ID等於10000的這一行被找到為止。有了索引之後(必須是在ID這一列上建立的索引)，即可在索引中查找。由於索引是經過某種演算法優化過的，因而查找次數要少的多。可見，索引是用來定位的。

從應用上分， 主鍵索引（聚集） ， 唯一索引（聚集/非聚集） ， 普通索引 ， 組合索引 ， 單列索引和全文索引