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

// todo

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

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

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

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

不支持事物和行級鎖

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

索引是幫助MySQL高效獲取數據的數據結構，所以索引本質上是一種數據結構

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

哈希索引
基于哈希表的實現，優點是查詢非常快
在Mysql中只有Memory存儲引擎支持哈希索引

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

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

加快數據查詢方式，提高數據庫查詢性能

大大減少了服務器需要掃描的數據量

幫助服務器避免進行排序和創建臨時表

將隨機 I/O 變為順序 I/O

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

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

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

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字段為例

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

-- 刪除索引
drop index idx_type on articles

-- 查看索引
show index from articles

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

哪些情況下需要創建索引

主鍵自動建立唯一索引

頻繁作為查詢條件的字段應該創建索引

查詢中與其他表關聯的字段，外鍵關系建立索引

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

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

查詢中統計或分組字段

哪些情況下不需要創建索引

表記錄太少，網上有建議2000為界限，2000以下不創建

經常增刪改的表（因為每次增刪改不僅要操作數據還要操作索引）

where條件用不到的字段

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

給維度高的列創建索引

數據列中不重復值出現的個數，數量越高，維度越高

重復數據會降低維度

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

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

對where,on,group by,order by中出現的列使用索引

對較小的數據列使用索引，這樣會使索引文件更小，同時內存中也可以裝載更多的索引建

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

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

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

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

使用短索引

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

索引列排序

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

不在索引列進行運算操作

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

獨立的列

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

前綴索引

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

多列索引

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

索引列的順序

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

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

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

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

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條件的位置越靠前越好

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

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

select_type:查詢類型，有簡單查詢、聯合查詢和子查詢

key:使用的索引

rows:掃描的行數

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

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

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

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

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

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

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

SELECT CEILING (19.1) ; -- 20

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

SELECT floor (19.1) ; -- 19

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

SELECT rand() ; -- 0.8320153586864615  隨機數

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

SELECT ROUND(100.123456,3); -- 100.123

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

select avg(age) from users ; -- 14.0000

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

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) // 返回由屬于一組的列值連接組合而成的結果

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為一周之內的第幾天,從0開始，0代表第一天

SELECT DAYOFWEEK(CURRENT_DATE()); -- 5

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

SELECT DAYOFMONTH(CURRENT_DATE()); -- 8

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

SELECT DAYOFYEAR(CURRENT_DATE()); -- 67

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

SELECT DAYNAME(CURRENT_DATE()); -- Thursday

FROM_UNIXTIME(timestimps,fmt) // 時間戳轉成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代表額數字

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

多帶帶的distinct只能放在開頭

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

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