摘要：雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行和。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復合索引。

// todo

InnoDB是是Mysql默認的事務性存儲引擎

InnoDB才有MVCC來支持高并發(fā)，并且實現(xiàn)了四個標準的隔離級別，默認級別是可重復讀

InnoDB存儲引擎下的表是基于聚簇索引建立的，對主鍵的查詢性能有很高的提升

提供了大量的特性，包括全文索引、壓縮、空間函數(shù)等

不支持事物和行級鎖

InnoDB：支持事物、在線熱備份、行鎖
MyISAM：支持全文索引、地理空間索引

索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，所以索引本質(zhì)上是一種數(shù)據(jù)結(jié)構(gòu)

B-Tree索引
B-Tree索引是大多數(shù)Mysql存儲引擎默認的索引類型
使用B-Tree索引后，不用再進行全表掃描，只需要對樹進行搜索即可，因此查找速度會快很多
可以指定多個列作為索引列，多個索引列共同組成鍵
B-Tree 索引適用于全鍵值、鍵值范圍和鍵前綴查找，其中鍵前綴查找只適用于最左前綴查找
除了用于查詢，還可以用于排序和分組

哈希索引
基于哈希表的實現(xiàn)，優(yōu)點是查詢非?？?br>在Mysql中只有Memory存儲引擎支持哈希索引

空間索引(R-Tree)
MyISAM存儲引擎支持空間索引，可以用于地理數(shù)據(jù)存儲

全文索引
MyISAM存儲引擎支持全文索引，用于查找文本中的關(guān)鍵字，而不是直接比較索引中的值

加快數(shù)據(jù)查詢方式，提高數(shù)據(jù)庫查詢性能

大大減少了服務器需要掃描的數(shù)據(jù)量

幫助服務器避免進行排序和創(chuàng)建臨時表

將隨機 I/O 變?yōu)轫樞?I/O

實際上索引也是一張表，該表保存了主鍵與索引字段，并指向?qū)嶓w表的記錄，所以索引列也是要占用空間的。

雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行insert,update和delete。因為更新表時，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加索引列的字段，都會調(diào)整因為更新所帶來的鍵值變化后的索引信息。

索引只是提高效率的一個因素，如果你的MySQL有大數(shù)據(jù)量的表，就需要花時間 研究建立最優(yōu)秀的索引，或優(yōu)化查詢語句。

create index

CREATE INDEX index_name ON table_name (column_list)

alter table

ALTER TABLE `table_name` ADD INDEX index_name (column_list)

以articles的type字段為例

-- 創(chuàng)建索引
CREATE INDEX idx_type ON articles (type);

-- 刪除索引
drop index idx_type on articles

-- 查看索引
show index from articles

一般情況下，在where或join子句中出現(xiàn)的列需要添加索引。但是，因為MySQL只對<,<=,=,>,>=,between,in,以及某些時候的like才會使用索引（使用like時，以通配符%和_查詢時，MySQL不會使用索引）

哪些情況下需要創(chuàng)建索引

主鍵自動建立唯一索引

頻繁作為查詢條件的字段應該創(chuàng)建索引

查詢中與其他表關(guān)聯(lián)的字段，外鍵關(guān)系建立索引

單鍵/組合索引的選擇問題(在高并發(fā)下傾向組合索引)

查詢中排序的字段，排序字段通過索引去訪問將大大提高排序速度

查詢中統(tǒng)計或分組字段

哪些情況下不需要創(chuàng)建索引

表記錄太少，網(wǎng)上有建議2000為界限，2000以下不創(chuàng)建

經(jīng)常增刪改的表（因為每次增刪改不僅要操作數(shù)據(jù)還要操作索引）

where條件用不到的字段

數(shù)據(jù)重復且分布平均的字段，索引的選擇性較低，即當前字段不重復的索引值與表中當前字段的記錄數(shù)比值，值越大越不建議建索引

給維度高的列創(chuàng)建索引

數(shù)據(jù)列中不重復值出現(xiàn)的個數(shù)，數(shù)量越高，維度越高

重復數(shù)據(jù)會降低維度

給緯度高的列創(chuàng)建索引，比如用戶表的年齡維度就高于性別

性別這種低緯度的列不適合建索引

對where,on,group by,order by中出現(xiàn)的列使用索引

對較小的數(shù)據(jù)列使用索引，這樣會使索引文件更小，同時內(nèi)存中也可以裝載更多的索引建

為較長的字符串使用前綴索引

不要過多創(chuàng)建索引，過多的索引會增加額外的磁盤空間，對DML操作速度影響很大，因為每增刪改查一次就得重新建立索引

使用組合索引，可以減少文件索引大小，在使用時速度要優(yōu)于多個單列索引

索引不會包含有Null值的列，所以我們在數(shù)據(jù)庫設計時不要讓字段的默認值為NULL

使用短索引

對字符串列進行索引,如果可能應該指定一個前綴長度

索引列排序

MySQL查詢只使用一個索引，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會使用索引的。因此數(shù)據(jù)庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創(chuàng)建復合索引。

不在索引列進行運算操作

建組合索引的時候，區(qū)分度最高的在最左邊

獨立的列

在查詢時，索引不能是表達式的一部分，也不能是函數(shù)的參數(shù)，否則無法使用索引

前綴索引

對于blob，text,varchar類型的列，必須使用前綴索引，只索引開始的部分字符

多列索引

在需要使用多個列作為條件查詢時，使用多列索引比使用單列索引性能要好

索引列的順序

在寫查詢語句時，將選擇性強的列放在前面

left join是由左邊決定的，左邊一定都有，所以右邊是我們的關(guān)鍵點，建立索引要建右邊的。當然如果索引在左邊，可以用右連接。
盡可能減少Join語句中的NestedLoop的循環(huán)次數(shù)：“永遠用小結(jié)果集驅(qū)動大的結(jié)果集”

最佳左前綴法則：如果查詢中使用了多個索引列，要遵循最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中列。

不在索引列上做任何操作（計算、函數(shù)、（自動/手動）類型轉(zhuǎn)換），會導致索引失效而轉(zhuǎn)向全表掃描。

where條件的列=的判斷放在比較運算符>、<等的左邊，放在比較運算符右邊的索引會失效
比如：

  select * from user where username="saboran" and age > 18 and mobile = "18862612345"

其中username、age、mobile都有索引，但是只有username和age的索引會生效，mobile索引用不到

select查詢時盡量減少select * 操作，用需要的字段代替*

在使用!=或者<>的時候無法使用索引，會導致全表掃描

is null 和 is not null 也無法使用索引

like 以通配符開頭，mysql索引會失效變成全表掃描
所以最好用右邊通配符匹配like "tssk%"
如果要使用兩邊通配符匹配，則將like條件放在最后一個
比如：

select age from users where a = 3 and b = 4 and c like "%abcd%";

這樣a、b、c都有索引的話，a、b用的上，c用不上

字符串不加單引號索引會失效

少用or，用它連接時會索引失效

避免子查詢，使用join

對于單鍵索引，盡量選擇針對當前查詢語句過濾性更好的索引作為查詢條件

在選擇組合索引時，當前query中過濾性最好的索引放在where條件的位置越靠前越好

盡可能通過分析統(tǒng)計信息和調(diào)整query的寫法來達到選擇合適索引的目的

用來分析SQL語句，分析結(jié)果中比較重要的字段有：

select_type:查詢類型，有簡單查詢、聯(lián)合查詢和子查詢

key:使用的索引

rows:掃描的行數(shù)

慢查詢主要是因為訪問了過多數(shù)據(jù)，除了訪問過多行之外，也包括訪問了過多列。最好不要使用select * 語句，要根據(jù)需要選擇查詢的列

最好使用limit語句取出想要的那些行，還可以建立索引來減少條件語句的全表掃描

ABS(x) // 返回x的絕對值

select abs(age) from users limit 1; -- 18

BIN(x) // 返回x的二進制數(shù)

select bin(age) from users limit 1; -- 10010

CEILING(x) // 返回大于x的最小整數(shù)值

SELECT CEILING (19.1) ; -- 20

FLOOR(x) // 返回小于x的最大值

SELECT floor (19.1) ; -- 19

RAND() // 返回0到1的隨機數(shù)

SELECT rand() ; -- 0.8320153586864615  隨機數(shù)

ROUND(x,y) // 返回參數(shù)x的四舍五入的y位小數(shù)值

SELECT ROUND(100.123456,3); -- 100.123

AVG(col) // 返回指定列的平均數(shù)

select avg(age) from users ; -- 14.0000

COUNT(col) // 返回指定列中非null值的個數(shù)

SELECT count(id) from users ; -- 2

MIN(col) // 返回指定列的最小值

select min(age) from users ; -- 10

MAX(colcol) // 返回指定列的最大值

select max(age) from users ; -- 18

SUM(col) // 返回指定列所有值的和

select sum(age) from users ; -- 28

GROUP_CONCAT(col) // 返回由屬于一組的列值連接組合而成的結(jié)果

select GROUP_CONCAT(age) from users ; -- 18,20

CONCAT(s1,s2,s3,sn) // 將s1,s2,s3,sn連接為字符串

select CONCAT(id,age,name) from users limit 1; -- 118安小下

CONCAT_WS("|") // 將s1,s2,s3,sn連接為字符串，并使用|分隔，|可以替換為任意分隔符

SELECT CONCAT_WS("|",id,name,age) from users limit 1; -- 1|安小下|18

CURDATE()/CURRENT_DATE() // 返回當前日期

SELECT CURRENT_DATE(); -- 2018-03-08

CURTIME()/CURRENT_TIME() // 返回當前時間

SELECT CURRENT_TIME(); -- 08:54:15

DATE_FORMAT(date,fmt) // 按照fmt格式，格式化date

SELECT DATE_FORMAT(CURRENT_DATE(),"%Y/%m/%d"); -- 2018/03/08

DAYOFWEEK(date) // 返回date為一周之內(nèi)的第幾天,從0開始，0代表第一天

SELECT DAYOFWEEK(CURRENT_DATE()); -- 5

DAYOFMONTH(date) // 返回date為一月之內(nèi)的第幾天

SELECT DAYOFMONTH(CURRENT_DATE()); -- 8

DAYOFYEAR(date) // 返回date為一年之內(nèi)的第幾天

SELECT DAYOFYEAR(CURRENT_DATE()); -- 67

DAYNAME(date) // 返回date的星期名

SELECT DAYNAME(CURRENT_DATE()); -- Thursday

FROM_UNIXTIME(timestimps,fmt) // 時間戳轉(zhuǎn)成fmt格式的字符串時間

SELECT FROM_UNIXTIME(1520500384,"%Y/%m/%d"); -- 2018/03/08

HOUR(time) // 返回time的小時值(0-23)

SELECT HOUR("20:10"); -- 20

MINUTE(time) // 返回time的分鐘值(0-59)

SELECT HOUR("20:10"); -- 10

MONTH(date) // 返回date的月份值(1-12)

SELECT MONTH(CURRENT_DATE()); -- 3

MONTHNAME(date) // 返回date的月份名

SELECT MONTHNAME(CURRENT_DATE()); -- March

NOW() // 獲取當前日期和時間

SELECT NOW(); -- 2018-03-08 09:26:55

WEEK(date) // 返回日期date為一年中的第幾周

SELECT WEEK(CURDATE()); -- 9

YEAR(date) // 返回日期date的年份

SELECT YEAR(CURDATE()); -- 2018

MD5(str) // 計算字符串str的MD5檢驗值

PASSWORD(str) // 返回字符串str的加密版本，這個加密是不可逆的

SHA(str) // 計算字符串str的安全散列算法檢驗值

// todo

INET_ATON(ip) // 返回ip代表額數(shù)字

INET_NTOA(num) // 返回數(shù)字代表的ip

多帶帶的distinct只能放在開頭

-- 會報錯
select id,DISTINCT(name) from test;

-- 不會報錯
select DISTINCT(name) from test;