Mysql索引

索引概述

索引用于快速找出在某个列中有特定值的行。如果不使用索引,Mysql必须从第一条记录开始遍历,读完整个表,直到找出相关的行。如果表越大,那么花费的时间就越多。如果表中查询的列中有索引,就可以快速定位这些数据,没有必要看所有的数据。

索引的分类

  • 普通索引(index):对索引值没有限制
  • 唯一索引(unique index):索引列的值必须唯一,但允许有空值
  • 主键索引(primary key):是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引。
  • 组合索引:指多个字段上创建的索引。只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合
  • 全文索引

PRIMARY KEY 与UNIQUE的区别:

  • 每个数据表只能有一个PRIMARY KEY
  • PRIMARY KEY不可以包含NULL值,而UNIQUE索引可以.而且,如果你允许某个UNIQUE索引包含NULL值,那它将可以包含多个NULL值。因为mysql无法判断两个NULL值是否代表同样的东西,索引里的多个NULL值将被认为代表多个不同的东西
  • 除PRIAMRY KEY以外,其他类型的索引几乎都可以用CREAET INDEX语句来添加。

设计索引的原则

  • 尽量为用来搜索、分类或分组的数据列编制索引,不要作为输出显示的数据列编制索引。换句话说,最适合有索引的数据列是那些在WHERE子句中出现的数据列、在联结子句中给出的数据列,或者是在ORDER BY或GROUP BY子句中出现的数据列。根据SELECT关键字仅出现在输出列清单里的数据列最好不要有索引。
  • 为字符串值的前缀编索引。假如要为字符串数据列编索引,应当尽可能给出前缀长度。例如,假定你有一个CHAR(200)的数据列,大多数的值前10或20个字符是唯一的,那么就不用为整个数据列编索引。仅为前面的20或30个字符编索引可以节省索引中的大量空间,而且会使查询进行得更快。为较小的值编索引可以减少磁盘输入、输出,加快比较速度。
  • 不要过多索引
  • 对短小的值进行索引。应尽量选用比较“小”的数据类型。比如说,如果一个MEDIUMINT数据列已足以容纳需要存储的数据,就不要选用BIGINT;如果你的数据没有一个比25个字符更长,就不要选用CHAR(100)。比较短小的值可以在以以下几个方面提高索引的处理性能。
    • 短小的值可以让比较操作更快地完成,加快索引查找速度
    • 短小的值可以让索引的“体积”更小,减少磁盘I/O活动
    • 短小的值意味着键缓存块里可以容纳更多的键值,让MYSQL在内存里同时容纳更多的键,而这将加大在不需要从磁盘读取更多索引块的前提下在内存里找到键值的概率。
      对InnoDB存储引擎而言,因为它使用的是聚集索引,所以让主键尽量短小将更有好处。所谓聚集索引是把数据行和主键值集中保存在一起的情况。其他的索引都是些二级索引–它们保存着主键值和二级索引值。现在二级索引里找到一个主键值,再通过它找到相应的数据行。这意味着主键值在每一个二级索引里都会重复出现,如果主键值比较长的话,就会导致每一个二级索引都将占用更多的存储空间。

索引操作

  • 创建索引:
    • 普通索引:CREATE INDEX indexName ON mytable(username(length))ALTER table tableName ADD INDEX indexName(columnName)
    • 唯一索引:CREATE UNIQUE INDEX indexName ON mytable(username(length))
    • 主键:ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
  • 查询索引:SHOW INDEXSHOW CREATE TABLE
  • 删除索引:DROP INDEX index_name ON tbl_name