mysql之索引

mysql索引：雖然索引大大提高了查詢速度，同時會降低更新表的速度，如對表進行insert,update和delete.因爲更新表時，mmysql不僅要保存數據，還要保存一下索引文件(該文件保存了主鍵和索引字段)，索引建立過多會佔用磁盤空間的索引文件

分類1：單列索引和組合索引

分類2:  普通索引---唯一索引----主鍵索引----組合索引----全文索引(該索引只能用於myisam)

創建索引：

                 alter  table  tablename add     index  indexname   (tablecolumns(length))  #普通索引

                 alter  table  tablename add   unique  index  indexname   (tablecolumns(length))  #唯一索引

                 或

            CREATE TABLE 表名(字段名 数据类型 [完整性约束条件],
                  [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
                  [索引名](字段名1 [(长度)] [ASC | DESC])
            );     

eg:

• create table  test1(
•                                        name  char(50),
•                                         unique  index  uq(name(10) ）
•                                         );
•  UNIQUE：可选。表示索引为唯一性索引。

• SPATIAL：可选。表示索引为空间索引。
• INDEX和KEY：用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
• 索引名：可选。给创建的索引取一个新名称。
• 字段名1：指定索引对应的字段的名称，该字段必须是前面定义好的字段。
• 长度：可选。指索引的长度，必须是字符串类型才可以使用。
• ASC：可选。表示升序排列。
• DESC：可选。表示降序排列。

索引的值必須唯一，單允許有空值，如果是組合索引，則列值的組合必須唯一。

ALTER TABLE myIndex ADD INDEX name_city_age(vc_Name(10),vc_City,i_Age);

建表时，vc_Name长度为 50，这里为什么用 10 呢？

回答: 因爲多數名稱的前10各字符通常不同，而且使用列的一部分創建索引可以使索引文件大大減少，從而節省了大量的磁盤空間，有利於提高insert操作的速度。

如果分别在 vc_Name,vc_City，i_Age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率一样吗？大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。(也就是mysql在查詢時，只會用到一個索引)

刪除索引:

                    alter  table  tablename  drop index   indexname;

顯示索引信息：

                        show index  from tbale

索引原則：

                    1.越少越好,因爲多了會佔用磁盤空間，增加插入和刪除的操作時間。一個表的索引越多，插入和刪除的速度越慢，如果一個系統需要快速錄入，那麼就決不能使用太多索引]

--------------------------

復合索引：創建時，應該考慮列的順序，而且創建復合索引應當包含少數幾個列，並且這些列在select查詢裏使用。在復合索引裏包含太多的列，會造成需要使用相當多的內存去存儲復合索引的列和值，其後果是內存溢出和性能降低。

[使用asc和desc對復合索引進行進一步優化]

Sql代码  

1. CREATE INDEX idx_example   
2. ON table1 (col1 ASC, col2 DESC, col3 ASC)  

1. SELECT * FROM tb WHERE  col1 = val1  
2. SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  
3. SELECT * FROM tb WHERE  col1 = val1 and col2 = val2  AND col3 = val3
   
4. [使復合索引有效：爲a    a|b    a|b|c]
   

其中 有三列分别是：col1 升序，col2 降序， col3 升序。现在如果我们执行两个查询
1：Select col1, col2, col3 from table1 order by col1 ASC, col2 DESC, col3 ASC
  和索引顺序相同
2：Select col1, col2, col3 from table1 order by col1 DESC, col2 ASC, col3 DESC
 和索引顺序相反
查询1，2 都可以使复合索引优化。
如果查询为：
Select col1, col2, col3 from table1 order by col1 ASC, col2 ASC, col3 ASC

排序结果和索引完全不同时，此时的查询不会被复合索引优化。

-------------------------------

性能優化[參考http://www.jb51.net/article/82254.htm ]

分爲: 數據庫設計------sql語句優化-----數據庫參數配置------硬件資源和操作系統

sql數據庫設計部分

https://blog.csdn.net/wangqyoho/article/details/52900585

sql語句優化部分

       1.使用like關鍵字的查詢時: 如果匹配字符串的第一個字符爲"%",索引不會起作用,只有%不在第一個位置時，索引才有效。

        2.使用復合索引

        3.查詢語句的查詢條件中只有or關鍵字，且or前後的兩個條件中的列都是索引時，查詢中才會使用索引。否則該查詢不會使用索引。

         4.[在mysql中,使用join查詢來替代子查詢]

        mysql中的子查詢雖然使查詢語句很靈活，但是執行效率不高。mysql需要爲子查詢的內層查詢結果建立一個臨時表，然後外層語句從臨時表中查詢記錄。查詢結束後再撤銷臨時表。（鏈接查詢不需要建立臨時表，其速度比子查詢更快）

         5.盡量避免null字段，定義時盡量使用not null,原因是允許null時不方便查詢優化，復合索引也會失效，而且如果列有索引的時候會額外佔用空間。

          6.不要使用select  *

          7.update時，where語句盡量走索引，不然會全表掃描.

          8.or，如果：select a from A where b=1 or c=1这种where里面不同字段进行or，这种尽量改为union

          9.避免使用"%前綴"，因爲會導致索引失效

         10.盡量不用insert select，數據量大有延遲，同步完了可能有錯誤。

數據庫參數配置

  [通過優化mysql的參數可以提高資源利用率，從而達到提高mysql服務器性能的目的]