MySQL优化之索引详解（mysql索引优化原则）

索引的概述

索引用来快速的查询那些具有特定值的记录。所有的MySQL索引都是以B+树的形式保存的。如果没有索引，执行查询时MySQL必须从第一个记录开始，进行全表扫描，直至找到合适的记录。表里的记录越多，这个操作越耗时。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

（1） 主键索引

主键索引是一种唯一性索引，但是它必须指定“PRIMARY KEY”。主键一般在创建表的时候指定，并且一张表只能有一个主键。

#创建主键索引：

默认情况下，MySQL会为主键自动添加主键索引。

也可以后期添加主键：

Alter table table_name add primary key(field_name);

#删除主键：

Alter table table_name drop primary key;

#查看索引

show index from table_name;

show keys from table_name;

（2） 全文索引

全文索引一般用于查询文本或者长内容而建立。

#全文索引的使用方法

-- 错误使用方法

select * from articles where body like '%mysql%';

-- 建立全文索引

alter table articles add FULLTEXT index fulltext_article(body);

-- 正确使用方法

select * from articles where match (body) against('mysql');

使用全文索引的注意事项：

- MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效.

- MySQL不能对中文进行全文索引，只适用于英文.

- 使用全文索引，只能用固定的语法：match(字段名)… against(关键字).

- MySQL全文索引所能找到的默认最小长度为4个字符，并且如果查询的字符串包含停止词（常见字符），那么该停止词将会被忽略。

（3） 唯一索引

这种索引的所有值都只能出现一次，即必须唯一。

默认的，在创建表时指定字段为唯一时，自动为其创建唯一索引。

#创建唯一索引：

CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；

CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

注意：unique字段可以为null，可以有多个null，但是如果是字符串的话只能有一个为‘’。

（4） 普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

创建方式：

create index 索引名 on 表 (列1,列名2);

-- 案例

-- 未建立索引时查询 1.45s

select * from emp where ename ='MFPkFv';

-- 创建普通索引

create index index_ename on emp (ename);

-- 查询 4ms

select * from emp where ename ='MFPkFv';