MySQL索引

索引概念

• 索引是储存引擎用于快速查询到记录的一种数据结构

创建索引

• 在创建表时创建索引的语法

CREATE TABLE tableName(
	columnName dataType,
	columnName dataType,
	INDEX indexName(columnName)
);

• 语法分析：
  tableaName：创建表的表名(自定义)
  columnName：表中的字段名(自定义)
  dataType：数据类型(int、varchar、date …)
  indexName：索引名(自定义)
• 实例：创建一个名称为mytable的表，在mytable表的name字段加一个名称为myIndex的索引

CREATE TABLE mytable(
	id INT NOT NULL,
	name VARCHAR(16) NOT NULL,
	INDEX myIndex(name)
);

• 在创建表后创建索引的语法（两种方式）
  1.CREATE INDEX indexName on tableName(columnName);
  2.ALTER TABLE tableName ADD INDEX indexName(columnName);
• 语法分析：
  indexName：索引名(自定义)
  tableaName：表的表名(需要添加索引的表的表名)
  columnName：表中的字段名(索引所作用在表的字段名)
• 实例：在mytable表的name字段加一个名称为myIndex的索引
  1.CREATE INDEX myIndex on mytable(name);
  2.ALTER TABLE mytable ADD INDEX myIndex(name);

查看索引

• 语法：
  SHOW INDEX FROM tableName;
• 语法分析：
  tableaName：表的表名称(所查询的表的表名)
• 实例：查看mytale表中的索引
  SHOW INDEX FROM mytable;

删除索引

• 语法
  1.DROP INDEX indexName ON tableName;
  2.ALTER TABLE tableName DROP INDEX indexName;
• 实例：删除mytable表中的myIndex索引
  1.DROP INDEX myIndex ON mytable;
  2.ALTER TABLE mytable DROP INDEX myIndex;

分析是否使用到索引

• 语法
  EXPLAIN SELECT * FROM tableName WHERE columnName = value;
• 语法分析
  tableaName：表的表名(所需分析的索引所在的表的表名)
  columuName：字段名(所要查询的字段名)
  value：所要查询的内容(自定义)
• 实例：分析在mytable中查询字段名为jack时，是否使用了索引
  EXPLAIN SELECT * FROM mytable WHERE name = 'jack';
  提示：如果key值为索引名称，则该查询使用了索引，如果key的值为NULL，则表示没有使用索引

索引分类

• 主键索引（PRIMARY KEY）：即主索引，根据主键pk_clolum(length)建立索引，不允许重复，不允许空值
• 唯一索引（UNIQUE）：用来建立索引的列的值必须是唯一的，允许空值
• 普通索引（INDEX）：用表中的普通列构建的索引，没有任何限制
• 全文索引（FULLTEXT）：用大文本对象的列构建的索引
• 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

适合建立索引的情况

• 主键自动建立唯一索引
• 经常作为查询条件在WHERE或者ORDER BY语句中出现的列
• 作为排序的列
• 查询中与其他表关联的字段、外键关系
• 高并发条件下倾向组合索引
• 用于聚合函数的列

不适合建立索引的情况

• 经常增删改的列
• 有大量重复的列
• 表记录太少

索引失效的情况

• 在组合索引中不能有列的值为NULL，如果有，那么这一列组合索引无效
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用那么在ORDER BY中就不要使用
• LIKE操作中，‘%aaa%’不会使用索引，但是‘aaa%’可以使用索引
• 在索引的列上使用表达式或者函数，索引会失效
• 在查询条件中使用不等于、包括符号：<、>、<>会导致索引失效
• 字符串中不加单引号会导致索引失效
• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR连接的每个条件都加上索引
• 如果排序的字段使用索引，那么SELECT的字段也要是索引字段，否则索引失效
• 尽量不要包括多列排序，如果一定要，最好为这列构建组合索引
  提示：具体查看索引是否失效，使用EXPIAIN命令查询