mysql優化和索引

• 表的優化

1.定長與變長分離

    如 int,char(4),time核心且常用字段，建成定長，放在一張表；

    而varchar,text,blob這種變長字段適合單放一張表，用主鍵與核心表關聯。

2.常用字段和不常用字段要分離

3.在 1 對多需要關聯統計的字段上，添加冗余字段。

• 列類型選擇原則

1.字段類型優先級  整型 > date,time > enum,char > varchar > blob,text

2.夠用就行，不要慷慨，大的字段浪費內存，影響速度。

3.盡量避免用NULL,NULL不利于索引，需要特殊的字節來標注。

• Enum列的說明

1.enum列在內部是用整型來存儲的

2.enum列和enum列想關聯速度最快

3.enum列比(var)char的弱勢--在碰到與char關聯時，要轉化，要花時間

4.優勢---當char非常長時，enum依然是整型固定長度，當查詢的數據量越大時。enum的優勢越明顯。

• 索引優化

B-tree索引：myisam,innodb中默認使用B-tree索引。

hash索引：在memory表里，默認使用hash索引。

hash優缺點：1.查詢時間復雜度為O（1）。

            2.hash計算的結果是隨機的，在磁盤上也是隨機放置。

            3.無法對范圍查詢進行優化；

            4.無法利用前綴索引；

            5.排序也無法優化；

           6.必須回行，就是說，通過索引拿到數據位置必須回到表中取數據。

在where條件常用的列上都加上索引，若是獨立的索引，同時只能用上1個。

若建立多列索引，index（a,b,c），注意和順序有關,索引發揮作用，需要滿足左前綴要求。

索引在where，order by，group by 中會用上（滿足左前綴要求）。

多列索引在實際應用中更加實用，注意在建立多列索引的時候，要結合實際，創建合理的索引。

索引提高查詢的速度、提高排序的速度、提高分組查詢的速度。

• 聚簇索引和非聚簇索引

聚簇索引：btree 

          innodb引擎，索引和數據是在一塊的，

          innodb直接在主鍵索引的樹中，儲存行的數據，（既存儲了主鍵值，又存儲了行數據）。

          innodb次索引指向對主鍵的引用。

          優點：根據主鍵查詢條目比較少時，不用回行（數據就在主鍵節點下）。

          缺點：如果碰到不規則數據插入時，造成頻繁的頁分裂。

非聚簇索引：btree 

            myisam引擎， 索引和數據是分開的，

            myisam索引指向行在磁盤上的位置

            myisam中，主索引和次索引都指向物理行（磁盤位置）。

索引覆蓋：

如果查詢的列恰好是索引的一部分，那么查詢只需要在索引文件上進行，不需要回行到磁盤再找數據。

索引與排序

1. 對于覆蓋索引，直接在索引上查，就是有序的；

2. 在innodb引擎上，沿著索引的字段排序，也是自然有序的，對于myisam引擎，按某索引字段排序，但取出的字段包含有不是索引字段，那么會先取出所有行，再進行排序。

3. 先取出數據，形成臨時表，做filesort文件排序（要盡可能避免）。

4. 爭取目標：取出來的數據本身就是有序的，利用索引來排序。
   

SQL語句優化

1. SQL語句的時間花在等待時間、執行時間，只有降低執行時間，那其他語句鎖定的時間也減少了；
   

2. SQL語句的執行時間又花在查找、取出；

3. 如何查詢快？a)聯合索引的順序、區分度、長度 b)取得快，索引覆蓋 c)傳輸的少，更少的行和列；

4. 切分查詢，將數據拆成多次，例：插入10000條數據，每1000條為單位插入；

5. 分解查詢，按邏輯把多表連接查詢分成多個簡單SQL

   總結：少查，盡量精準數據，少取行；必須要查，盡量走在索引上查詢行；取時，取盡量少的列；

6. 在group時，用帶有索引的列來group，可以避免臨時表和文件排序，速度會稍快些，另外用int型比char型分組，也要快些；

7. 在group時，我們假設只取了A表的內容，group by的列，盡量用A表的列，會比B表的列要快。

8. order by的列要和group by 的列一致，否則也會引起臨時表，因為兩者都需要排序，如果兩者的列不一致，那必須經過至少1次排序；
   

如何判斷查詢是否用到了索引？

查詢方法：  explain  SQL  \G

extra字段:

    using index： 指用到了索引覆蓋，效率非常高；

    using where：指光靠索引還定位不到，還得where判斷一下

    using temporary:指用上了臨時表，group by 與 order by 不同列時或 group by 、order by 別的表的列時

    using filesort:文件排序，文件可能在磁盤，也可能在內存。

子查詢

1.from型子查詢

注意：內層from語句查到的臨時表，是沒有索引的，所以from的返回內容要盡量少，需要排序，在內層先排好序。

2.in型子查詢

mysql的查詢優化器，針對in型優化，被改成exists子查詢的執行效果，單行單行的執行過濾。當外層表越大時，查詢速度越慢。可以用連接查詢來代替子查詢。

limit及翻頁優化

limit offset，N

當offset非常大時，效率很低，因為mysql先取offset+N行，返回放棄前offset行，返回N行。

優化辦法：

1）從業務上去解決，不允許翻過100頁。例如百度，一般能翻到70多頁。

2）不用offset，用條件查詢(ID上有索引)

 select * from tablename where id>1000000 limit 2;

3）只查索引，不查數據，得到ID，再用ID去查具體條目(ID上有索引)，這種技巧就是延遲關聯

select id,xxx,xxx from tablename inner join (

select id from tablename limit 1000000,2 ) as tmp using(id);

比下面直接查數據的方法快，

select id,xxx,xxx from tablename limit 1000000,2;