mysql 索引

mysql索引概念

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引的优缺点

优点：加快了查询的速度(select)
缺点：降低了增删改的速度(update/delete/insert)，增加了表的文件大小(索引文件甚至可能比数据文件还大)

索引的使用原则

  • 索引条件列(where后面最频繁的条件比较适宜索引)
  • 索引散列值，过于集中的值不要索引

索引类型

  • 普通索引：仅仅是加快了查询速度
  • 唯一索引：行上的值不能重复
  • 主键索引：不能重复
1
2
主键索引和唯一索引的区别：主键必唯一，但是唯一索引不一定是主键；
一张表上只能有一个主键，但是可以有一个或多个唯一索引

  • 全文索引

    1
    2
    3
    在MySQL5.6以下，只有MyISAM表支持全文检索。在MySQL5.6以上Innodb引擎表也提供支持全文检索
    相应字段建立FULLTEXT索引
    MySQL不支持中文全文索引，原因很简单：与英文不同，中文的文字是连着一起写的，中间没有MySQL能找到分词的地方，截至目前MySQL5.6版本是如此，但是有变通的办法，就是将整句的中文分词，并按urlencode、区位码、base64、拼音等进行编码使之以“字母+数字”的方式存储于数据库中。

  • 联合索引
    多列b+tree，使用多列值组合而成的b+tree索引
    遵循最左侧原则，从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a a,b a,b,c 3种组合进行查找，但不支持 b,c进行查找。当使用最左侧字段时，索引就十分有效。

查看某张表上的所有索引

show index from tableName [\G,如果是在cmd窗口，可以换行];

建立索引

1
2
alter table 表名 add index/unique/fulltext [索引名](列名) ;  ---索引名可不写,不写默认使用列名
alter table 表名 add primary key(列名)    --不要加索引名，因为主键只有一个

删除索引

1
2
3
4
#删除非主键索引
alter table 表名 drop index 索引名；
#删除主键索引
alter table 表名 drop primary key;