MySQL优化之索引

1、什么是索引

在MySQL中，索引（index）也叫做‘键（key）’,是存储引擎用于快速找到记录的一种数据结构。打个比方：索引就像一本书的目录。

2、索引的分类

索引类型	说明	例子
普通索引(INDEX或KEY)	对关键字没有要求。分为单列索引和组合索引； 组合索引最左前缀原则 select * from user where name = ‘xxx’ 此时，会走索引 select * from user where address = ‘xxx’ 则不会走索引	ADD INDEX `idx_name`(`name`) USING BTREE（单列索引）
		ADD INDEX `idx_mult`(`name`, `address`) USING BTREE（组合索引）
唯一索引（UNIQUE KEY）	要求关键字不能重复。同时增加唯一约束。	ADD UNIQUE INDEX `idx_unique`(`en_name`);
主键索引（PRIMARY KEY）	也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。	ADD PRIMARY KEY (`name`) USING BTREE;
全文索引（FULL TEXT）	关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。	ADD FULLTEXT INDEX `idx_full`(`en_name`);

Explain 执行计划

删除索引时，再看执行计划：

从查询的行数可知，有索引时查询会快的多，因为它只需要查找一行，而没有索引时，会造成全表扫描。

3、索引原则

（1）列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

第三个语句 empno-1就不是列独立：就不能用索引。类似函数等等。（write_time < unix_timestamp()-$gc_maxlifetime）

（2）左侧原则

Like：匹配模式必须要左边确定不能以通配符开头。

假如业务逻辑上出现: field like % keywork%;类似查询，需要使用全文索引。

归纳：

a、不要过度索引。索引越多，占用空间越大，反而性能变慢；

b.只对WHERE子句中频繁使用的建立索引；

c.尽可能使用唯一索引，重复值越少，索引效果越强；

d.使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；

e.充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边。

f.索引存在，如果没有满足使用原则，也会导致索引无效：

4、索引方式

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。

HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。