MySQL索引的创建与使用

索引的分类

  在学习如何创建索引之前，先了解一下索引的分类
  MySQL中分为：普通索引，唯一索引，主键索引，组合索引，和全文索引。

index_name ：索引名
table：表名
column：列名
length：列长度

普通索引

  是最基本的索引，它没有任何限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。它有以下几种创建方式：

	# 1.直接创建索引
	CREATE INDEX index_name ON table(column(length))
	 
	# 2.修改表结构的方式添加索引
	ALTER TABLE table_name ADD INDEX index_name ON (column(length))
	 
	# 3.创建表的时候同时创建索引
	CREATE TABLE `table` (
	    `id` int(11) NOT NULL AUTO_INCREMENT ,
	    `title` char(255) CHARACTER NOT NULL ,
	    `content` text CHARACTER NULL ,
	    `time` int(10) NULL DEFAULT NULL ,
	    PRIMARY KEY (`id`),
	    INDEX index_name (title(length))
	)
	

唯一索引

  与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

	# 1.创建唯一索引
	CREATE UNIQUE INDEX indexName ON table(column(length))
	 
	# 2.修改表结构
	ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
	 
	# 3.创建表的时候直接指定
	CREATE TABLE `table` (
	    `id` int(11) NOT NULL AUTO_INCREMENT ,
	    `title` char(255) CHARACTER NOT NULL ,
	    `content` text CHARACTER NULL ,
	    `time` int(10) NULL DEFAULT NULL ,
	     UNIQUE indexName (title(length))
	);

主键索引

  是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

	CREATE TABLE `table` (
	    `id` int(11) NOT NULL AUTO_INCREMENT ,
	    `title` char(255) NOT NULL ,
	     PRIMARY KEY (`id`)
	);

组合索引

  指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

  最左前缀：组合索引遵从了最左前缀，利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如，这里由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引组合中的字段可以是(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成最左面的前缀原则，那么就不会用索引，比如，age或者（name，age）组合就不会使用索引查询

	#添加组合索引
	ALTER TABLE `table` ADD INDEX name_city_age (id,name,age); 
	 
	#创建组合索引
	CREATE TABLE tab3(
	id INT(4) NOT NULL,
	name CHAR(20) NOT NULL,
	age INT(3) NOT NULL,
	info VARCHAR(255),
	INDEX multiIdx(id,name,age)
	);

全文索引

  主要用来查找文本中的关键字，而不是直接与索引中的值相比较。跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。全文索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立全文然后再将数据写入的速度快很多。

	# 1.创建表的适合添加全文索引
	CREATE TABLE `table` (
	    `id` int(11) NOT NULL AUTO_INCREMENT ,
	    `title` char(255) CHARACTER NOT NULL ,
	    `content` text CHARACTER NULL ,
	    `time` int(10) NULL DEFAULT NULL ,
	    PRIMARY KEY (`id`),
	    FULLTEXT (content)
	);
	 
	# 2.修改表结构添加全文索引
	ALTER TABLE article ADD FULLTEXT index_content(content)
	 
	# 3.直接创建索引
	CREATE FULLTEXT INDEX index_content ON article(content)

查看索引

## 查看表的索引
SHOW INDEX FROM 表名;

解读索引

• Table
  表示创建索引的数据表名，这里是 sys_数据表。
• Non_unique
  表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。
• Key_name
  表示索引的名称。
• Seq_in_index
  表示该列在索引中的位置，如果索引是单列的，则该列的值为1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
• Column_name
  表示定义索引的列字段。
• Collation
  表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
• Cardinality
  索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
• Sub_part
  表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
• Packed
  指示关键字如何被压缩。若没有被压缩，值为 NULL。 Null 用于显示索引列中是否包
• NULL
  若列含有NULL，该列的值为 YES。若没有，则该列的值为 NO。
• Index_type
  显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）
• Comment
  显示评注。

删除索引

DROP INDEX 索引名 ON 表名;
## 删除book表中的名称为BkNameIdx的索引
DROP INDEX BkNameIdx ON book;

索引失效

1. 创建联合索引，但是没有遵循最左前缀法则，则会出现索引失效的情况。如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
2. where条件有数学运算或函数
3. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
4. mysql在使用 is null，is not null也无法使用索引
5. like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作，当%加在右边时可以使用
6. 字符串不加单引号索引失效
7. where条件使用or
8. 注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引