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

// todo

InnoDB是是Mysql默認(rèn)的事務(wù)性存儲(chǔ)引擎

InnoDB才有MVCC來(lái)支持高并發(fā)，并且實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別，默認(rèn)級(jí)別是可重復(fù)讀

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

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

不支持事物和行級(jí)鎖

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

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

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

哈希索引
基于哈希表的實(shí)現(xiàn)，優(yōu)點(diǎn)是查詢非常快
在Mysql中只有Memory存儲(chǔ)引擎支持哈希索引

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

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

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

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

幫助服務(wù)器避免進(jìn)行排序和創(chuàng)建臨時(shí)表

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

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

雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行insert,update和delete。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要保存一下索引文件每次更新添加索引列的字段，都會(huì)調(diào)整因?yàn)楦滤鶐?lái)的鍵值變化后的索引信息。

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

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)的列需要添加索引。但是，因?yàn)镸ySQL只對(duì)<,<=,=,>,>=,between,in,以及某些時(shí)候的like才會(huì)使用索引（使用like時(shí)，以通配符%和_查詢時(shí)，MySQL不會(huì)使用索引）

哪些情況下需要?jiǎng)?chuàng)建索引

主鍵自動(dòng)建立唯一索引

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

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

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

查詢中排序的字段，排序字段通過(guò)索引去訪問(wèn)將大大提高排序速度

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

哪些情況下不需要?jiǎng)?chuàng)建索引

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

經(jīng)常增刪改的表（因?yàn)槊看卧鰟h改不僅要操作數(shù)據(jù)還要操作索引）

where條件用不到的字段

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

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

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

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

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

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

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

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

為較長(zhǎng)的字符串使用前綴索引

不要過(guò)多創(chuàng)建索引，過(guò)多的索引會(huì)增加額外的磁盤空間，對(duì)DML操作速度影響很大，因?yàn)槊吭鰟h改查一次就得重新建立索引

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

索引不會(huì)包含有Null值的列，所以我們?cè)跀?shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為NULL

使用短索引

對(duì)字符串列進(jìn)行索引,如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度

索引列排序

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

不在索引列進(jìn)行運(yùn)算操作

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

獨(dú)立的列

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

前綴索引

對(duì)于blob，text,varchar類型的列，必須使用前綴索引，只索引開(kāi)始的部分字符

多列索引

在需要使用多個(gè)列作為條件查詢時(shí)，使用多列索引比使用單列索引性能要好

索引列的順序

在寫查詢語(yǔ)句時(shí)，將選擇性強(qiáng)的列放在前面

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

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

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

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

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

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

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

在使用!=或者<>的時(shí)候無(wú)法使用索引，會(huì)導(dǎo)致全表掃描

is null 和 is not null 也無(wú)法使用索引

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

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

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

字符串不加單引號(hào)索引會(huì)失效

少用or，用它連接時(shí)會(huì)索引失效

避免子查詢，使用join

對(duì)于單鍵索引，盡量選擇針對(duì)當(dāng)前查詢語(yǔ)句過(guò)濾性更好的索引作為查詢條件

在選擇組合索引時(shí)，當(dāng)前query中過(guò)濾性最好的索引放在where條件的位置越靠前越好

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

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

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

key:使用的索引

rows:掃描的行數(shù)

慢查詢主要是因?yàn)樵L問(wèn)了過(guò)多數(shù)據(jù)，除了訪問(wèn)過(guò)多行之外，也包括訪問(wèn)了過(guò)多列。最好不要使用select * 語(yǔ)句，要根據(jù)需要選擇查詢的列

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

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

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

BIN(x) // 返回x的二進(jìn)制數(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的隨機(jī)數(shù)

SELECT rand() ; -- 0.8320153586864615  隨機(jī)數(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值的個(gè)數(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() // 返回當(dāng)前日期

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

CURTIME()/CURRENT_TIME() // 返回當(dāng)前時(shí)間

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

DATE_FORMAT(date,fmt) // 按照f(shuō)mt格式，格式化date

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

DAYOFWEEK(date) // 返回date為一周之內(nèi)的第幾天,從0開(kāi)始，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) // 時(shí)間戳轉(zhuǎn)成fmt格式的字符串時(shí)間

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

HOUR(time) // 返回time的小時(shí)值(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() // 獲取當(dāng)前日期和時(shí)間

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

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

SELECT WEEK(CURDATE()); -- 9

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

SELECT YEAR(CURDATE()); -- 2018

MD5(str) // 計(jì)算字符串str的MD5檢驗(yàn)值

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

SHA(str) // 計(jì)算字符串str的安全散列算法檢驗(yàn)值

// todo

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

INET_NTOA(num) // 返回?cái)?shù)字代表的ip

多帶帶的distinct只能放在開(kāi)頭

-- 會(huì)報(bào)錯(cuò)
select id,DISTINCT(name) from test;

-- 不會(huì)報(bào)錯(cuò)
select DISTINCT(name) from test;