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服務器性能的目的]


阅读更多
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页