SQL-結構化查詢語言

SQL基本文法

SQL語句可以單行或者多行書寫，以分号結尾

可以使用空格或者縮進，來增強語句的可讀性

MySQL不區分大小寫，建議關鍵字使用大寫

SQL語句分類

DDL：資料定義語言，對庫表結構的操作。

DML：資料操作語言，對表記錄進行更新（增删改）。

DQL：資料查詢語言，對表的記錄進行查詢。

DCL：資料控制語言，對使用者進行建立及授權。

索引

索引，是幫助MySQL高效擷取資料的資料結構。

索引，目的在于提高查找效率，可以類比字典。

索引，是排好序的快速查找的資料結構。

索引，會影響where和orderby（查找和排序）。

資料本身之外，資料庫還維護着一個滿足特定查找算法的資料結構；

這些資料結構以某種方式指向資料，這樣就可以在這些資料結構的基礎上實作進階查找算法，

這種資料結構就是索引。

預設一遍都是B+數索引。

索引優勢劣勢

劣勢：

索引也是要占用空間的。

雖然索引提供了查詢速度；但是降低了寫入（插入更新）速度。

索引隻是提供查詢效率的一個因子，需要花大量時間研究優化。

優勢：

提高資料檢索效率，降低資料庫的IO成本。

通過索引對資料排序，降低資料排序成本，降低了cpu的消耗。

索引分類

單值索引，即一個索引隻包含單個列，一個表可以有多個單值索引。

唯一索引，索引列的值必須唯一，但允許有空值。

符合索引，即一個索引包含多個列。

添加索引：

添加主鍵： alter table tb1 add primary key (column_list)

添加唯一索引：alter table tb1 add unique index_name (column_list)

添加一般索引：alter table tb1 add index index_name(column_list)

添加全文索引：alter table tb1 add fulltext index_name(column_list)

索引結構：BTree索引

建立索引的情況

主鍵自動建立唯一索引

頻繁作為查詢條件的列，應該建立索引

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

頻繁更新的字段不适合建立索引

where條件裡用不到的字段，不要建立索引

單鍵索引與組合索引，高并發情況下，傾向于選擇組合索引

查詢中，需要排序的字段

查詢中，統計或者分組的字段

不需要建立索引的情況

表資料很少

經常增删改的列

資料重複且分布均勻。

例如：10w行資料，某列A，隻有兩種狀态且每種狀态分布為50%；那麼一般對A建索引，不會提高查詢效率。

索引的選擇性是指索引列中不同指的數量與表資料量的比值。如果一個表有10w條記錄，表索引列有9w9999個不同值；那麼這個索引的選擇性就是：9w9999 / 10w 約等于 0.999。一個索引的選擇性越接近于1，這個索引的效率就越高。

索引失效

1、使用!= 或者 <> 導緻索引失效

例如：

SELECT a.* FROM coupon_state_manager a WHERE a.customer_id != 101           

2、類型不一緻導緻的索引失效

（1）字段varchar，條件為int，會導緻失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_phone = 13756494953           

（2）字段int，條件字元型，不會導緻索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = '101'           

3、函數導緻的索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE DATE(receive_date) = '2020-02-17 14:08:31'           

4、運算符導緻的索引失效

對列進行了（+，-，*，/，!）, 那麼都将不會走索引

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id -1 = 100           

5、OR引起的索引失效

（1）or連接配接的字段，都建立了索引，不會失效

（2）or連接配接的字段，如果其中一個沒建立索引，則會是整個索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = 101 or coupon_id = 2           

6、模糊搜尋導緻索引失效

（1）'%abc' '%abc%'會導緻索引失效（要查詢的字段，都在索引中，不會失效）

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name LIKE '%李%'           

（2）'abc%'不會導緻索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name LIKE '李%'           

7、NOT IN、NOT EXISTS導緻索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id NOT IN (100,101)           

8、IS NOT NULL會導緻索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name IS NOT NULL           

9、複合索引

Mysql從左到右的使用索引中的字段；

一個查詢可以隻使用索引中的一部分，但隻能是最左側部分。

例如索引是key index （a,b,c）。

可以支援a | a,b| a,b,c 3種組合進行查找，但不支援 b,c進行查找 。

當最左側字段是常量引用時，索引就十分有效。

建立複合索引時，應該仔細考慮列的順序。

對索引中的所有列執行搜尋或僅對前幾列執行搜尋時，複合索引非常有用；

僅對後面的任意列執行搜尋時，複合索引則沒有用處。