mysql之索引

原创 2018年04月15日 15:55:34

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


TOMCAT下的JIVE2的中文问题分析及解决

TOMCAT下的JIVE2的中文问题的报告原文摘自jive论坛用户shyguy的帖子。翻译者:cheramiXXX 注意 XXX: 所有的结论都是基于一下平台:Winnt4.0(简体中文版) + Su...
  • cherami
  • cherami
  • 2001-09-21 18:12:00
  • 845

MYSQL学习笔记-索引

  • 2010年08月28日 07:57
  • 7KB
  • 下载

mysql的最佳索引攻略

Explain优化查询检测 所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找,而用的最多,并且是mysql默认...
  • GarfieldEr007
  • GarfieldEr007
  • 2017-01-12 23:00:11
  • 1652

Mysql 索引问题-日期索引使用

这两天发现原来的查询效率慢了,使用explain 查看,居然没有使用索引, 我的索引是日期类型的,首先想到的是mysql对日期类型的索引的处理机制是不是不同,在where条件里试了几种,发现效果都差...
  • spider_zhcl
  • spider_zhcl
  • 2016-11-24 18:02:48
  • 10171

mysql 时间索引失效

项目中查询时间断的数据发现查询时间很长。怀疑没有走时间的索引,于是explain一下 EXPLAIN select * from t_order where created_at>'2015-01-...
  • kelindame
  • kelindame
  • 2017-02-23 12:45:40
  • 5535

mysql简单的索引和in、or、union unionall语句查询速度

MYSQL描述: 一个文章库,里面有两个表:category和article。category里面有10条分类数据。article里面有20万条。article里面有一个"article_categ...
  • zhang515745112
  • zhang515745112
  • 2016-07-04 10:02:47
  • 8681

MySQL 修改索引名称

http://www.codeweblog.com/mysql-%E4%BF%AE%E6%94%B9%E7%B4%A2%E5%BC%95%E5%90%8D%E7%A7%B0/ 对于MySQL...
  • arkblue
  • arkblue
  • 2017-05-17 20:49:49
  • 1731

mysql添加删除索引

mysql添加删除索引
  • chenhualeguan
  • chenhualeguan
  • 2015-11-06 09:50:31
  • 5021

mysql强制使用指定索引查询

mysql强制指定索引查询语法: select * from table_name force index (index_name) where conditions;#########mysql强...
  • zixiao217
  • zixiao217
  • 2017-08-14 17:22:10
  • 1595

mysql建立、删除索引及使用

科技优家 2017-01-16 09:58 一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询...
  • u011277123
  • u011277123
  • 2017-01-17 08:45:03
  • 2563
收藏助手
不良信息举报
您举报文章:mysql之索引
举报原因:
原因补充:

(最多只允许输入30个字)