mysql 索引 简书_MySQL 索引

简介

索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。

如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

使用原则

索引底层使用的是B+树实现， 因为 B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。所以B+ 树通常用于数据库和操作系统的中做元数据索引。

虽然合理的设置索引可以明显提高查询速度，但也是要付出额外代价的：

一是创建索引会而外增加数据库的存储空间

二是会在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

通过上面的我们可以知道，索引有利有弊，并不是索引越多越好，我们要有选择性的合理使用索引。

什么时候应该使用索引：

在需要经常查询的列上创建索引，这样会提高查询效率。

在表与表的而连接条件上加上索引,可以提高连接查询的效率。

什么时候避免创建索引：

如果是查询中很少使用到的列是不应该为其建立索引的。

在重复值多的字段上不建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，如果一个字段上不同值较多可是建立索引的。

对经常更新的表(增、删、改)应避免对其进行过多的索引。

数据量小的表最好不要使用索引(数据量越大使用索引效率越高，数据量小提升不大)。

应避免对数据类型为 text， image ，bit字段建立索引，因为这些列的数据量要么相当大，要么取值很少。

不为没有限制 not null，建立索引。

使用索引查询时需要需要注意的：

不当的操作会使MySQL放弃使用索引，转而使用全表扫描。

条件中有 or，MySQL查询时会放弃索引，使用全表扫描。(要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引)

like查询是以%开头，也会让索引失效。

如果列类型是字符串，需要在条件中将数据使用引号包裹起来,否则不使用索引如：select * from user where name = 'admin'; admin 要用引号包裹。

索引列没有限制 not null，因为索引不会记录空值，没有设置 not null 的，数据库会认为索引字段可能存在空值，不会按照索引查找。

就像上面第4条所说的，索引不会记录 null，所以 is null， is not null 也使索引失效。

mysql 使用 != 或者 <> 时也会是索引失效，启用全盘扫面。

不要再索引列上做任何操作(计算、函数、类型转换)，这些都会导致索引失效。

MySQL索引的分类

1. 普通索引

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

2. 唯一索引

索引列中的值必须是唯一的，但是允许为空值。

3. 主键索引

是一种特殊的唯一索引，不允许有空值，MySQL默认为单主键字段设置索引。

4. 组合索引

在表中的多个字段组合上创建的索引，列值的组合必须唯一，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。

5. 全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

而MySQL使用的MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换

查看、添加及删除 索引

查看表的索引信息

MySQL 查看索引的 SQL 语句：

SHOW INDEX FROM 表名;

或者：

SHOW INDEX FROM 数据库名.表名;

再或者：

SHOW INDEX FROM 表名 FROM 数据库名;

SHOW INDEX FROM 会返回表的索引信息，有以下字段：

table：表的名称。

Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1。

Key_name:索引的名称。

Seq_in_index:索引中的列序列号，从1开始。

Column_name:列名称。

Collation:列以什么方式存储在索引中。在MySQLSHOW INDEX语法中，有值’A’(升序)或NULL(无分类)。

Cardinality:索引中唯一值的数目的估计值。基数越大，当进行联合时，MySQL使用该索引的机会就越大。

Sub_part:如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

Packed:指示关键字如何被压缩。如果没有被压缩，则为NULL。

Null:如果列含有NULL，则含有YES。如果没有，则该列含有NO。

Index_type:用的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

Comment:多种评注。

添加索引

第一种方式

使用 alter table 创建索引

ALTER TABLE 表名 ADD 索引类型 (unique,primary key,fulltext,index)[索引名](字段名)

// 普通索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

// ALTER TABLE 表名 ADD INDEX 索引名 (字段名);

//唯一索引

alter table table_name add unique (column_list);

//主键索引

alter table table_name add primary key (column_list);

第二种方式

CREATE INDEX可用于对表增加普通索引或UNIQUE索引，可用于建表时创建索引(建表后也可以使用)。

CREATE INDEX index_name ON table_name(column_list(length));

length可以省略，但是如果是 blob 和 text 类型必须指定 length。

//create只能添加这两种索引;

CREATE INDEX 索引名ON 表名(字段名(长度));

// 普通

CREATE INDEX index_name ON table_name (column_list)

// 唯一

CREATE UNIQUE INDEX index_name ON table_name (column_list)

提示：

对于第一种方式索引名index_name可选，缺省时，MySQL将字段名称作为索引名。

另外上面两种方式都有可以指定多个字段，用逗号分隔即可。