MySQL索引优缺点及使用讲解

索引

---

1. 什么是索引

创建在表上，是对数据库表中一列或多列的值进行排序的结构。
用于快速查询数据库表中的特定记录，可提高查询速度。

    不同的存储引擎定义了每个表的最大索引数和最大索引长度。所有存储引擎对每个表至少支持 
    16个索引，总索引长度至少256字节。 

2.分类

2.1 聚簇索引和非聚簇索引

聚簇索引

数据的物理存放顺序和索引顺序是一致的；
唯一性；
聚簇索引的叶结点就是数据结点；
主键默认设为聚簇索引；
InnoDB引擎按照聚簇索引存储数据。

非聚簇索引
索引顺序与数据物理排列顺序无关；
一 个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；
非聚簇索引的叶结点是索引结点，有一个指针指向对应的数据块；
MyISAM使用的是非聚簇索引。

2.2 存储类型
索引有两种存储类型：B型（BTREE）树索引和哈希（HASH）索引。

InnoDB和MyISAM存储引擎支持BTREE索引。

HASH索引

特征如下：

    只用于使用=或<=>操作符的等式比较。
    优化器不能使用HASH索引来加速ORDER BY操作。
    MySQL不能确定在两个值之间大约有多少行。如果将一个MyISAM表改为HASH索引的MEMORY 
    表，会影响一些查询的执行效率。
    只能使用整个关键字来搜索一行。

BTREE索引

当时>、<、>=、<=、BETWEEN 、！= 或者 <> , 或者 LIKE‘pattern' 操纵符时，都可以使
用相关列上的索引。

下列范围查询适用于BTREE索引和HASH索引：

SELECT* FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);

下列范围查询适用于BTREE索引：

SELECT* FROM t1 WHERE key_col > 1 AND key_col < 10;

SELECT* FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'lisa' 
                              AND simon';

3.优点和缺点

优点：
- 提高检索速度；
- 对于有依赖关系的子表和父表之间的联合查询时，可提高查询速度；
- 使用分组和排序子句进行数据查询时，显著节省查询中分组和排序的时间。

缺点：
- 创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；
- 索引需要占用物理空间；
- 增加、删除和修改数据时，要动态的维护索引，造成数据的维护速度降低了。

4. 索引分类：

1）普通索引：不附加任何限制条件，可创建在任何数据类型中，其值是否唯一和非空由字段本 身的完整性约束条件决定。

2）唯一性索引：使用UNIQUE参数设置索引。索引的值是唯一的。

3）全文索引：使用FULLTEXT参数设置索引。只能创建在CHAR、VARCHAR或TEXT类型字段上。查询数据量较大的字符串类型的字段时，使用全文索引可提高查询速度。

4）单列索引：在表中的单个字段上创建索引。只根据该字段进行索引（索引对应一个字段）。单列索引可以是普通索引或者唯一性索引或者全文索引。

5）多列索引：在表的多个字段上创建一个索引。可通过几个字段进行查询。
只有查询条件使用多列关键字最左边的前缀时，才可以使用索引，否则将不能使用索引。

6）空间索引：使用SPATIAL参数设置。
只能建立在空间数据类型上，可提高系统获取空间数据的效率。
只有MyISAM存储引擎支持空间检索，索引字段不为空。

---

5. 索引的设计原则

1）选择唯一性索引。索引的列的基数越大，索引效果越好。

2）为经常需要排序、分组和联合操作的字段建立索引（避免排序操作，浪费时间）

3）为常作为查询条件的字段建立索引（提高查询速度）

4）限制索引的数目，不要过度索引（每个索引都需要占用磁盘空间，会降低写操作的性能。）

5）尽量使用短索引。如果对字符串进行索引，应该指定一个前缀长度。

6）尽量使用左前缀来索引（字段值很长，使用值的前缀索引）

7）删除不再使用或者很少使用的索引（减少索引对更新操作的影响）

8）对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存。
InnoDB表的普通索引会保存主键的键值，主键应选择较短的数据类型，减少索引的磁盘占用，提高索引的缓存效果。

9）辅助索引，叶子结点存放着索引字段的值及对应的主键值

10）对查询where条件中区分度高的字段加索引；对查询分组和排序分组加索引。

11）一下情况无法使用到索引：like通配符在最左，not in, !=, <>, 队列做函数运算， 隐式数据类型转换，OR子句。

12）FORCE INDEX强制加索引

---

6. 创建索引

创建表示可直接创建索引

1）创建普通索引

    创建一个表名为index1的表，在表中的id字段上建立索引。                      

CREATE TABLE index1（id  INT , name  VARCHAR(20),INDEX(id) ）；

2）创建唯一性索引

CREATE TABLE index2 （id  INT UNIQUE， name VARCHAR(20),   
                      UNIQUE INDEX index2_id(id  ASC)  );

3)创建全文索引(只有MyISAM存储引擎支持全文索引)

创建一个表名为index3的表，在表中的info字段上建立名为index3_info的全文索引。 

CREATE  TABLE index3 （id  INT, info  VARCHAR(20),        
                      FULLTEXT INDEX index3_info(info) )ENGINE=MyISAM; 

4）创建单列索引（单个字段）

创建一个表名为index4的表，表中的subject字段上建立名为index4_st的单列索引。 

CREATE  TABLE  index4（id  INT， subject VARCHAR(30)，
                        INDEX index4_st(subject(10)) );

5)创建多列索引（多个字段,只有使用了第一个字段时才会触发索引）

创建表名为index5的表，在表中的name和sex字段上建立名为index5_ns的多列索引。 

CREATE  TABLE  index5(id  INT,  name  VARCHAR(20), sex  CHAR(4), 
                        INDEX  index5_ns(name, sex)  );

6）创建空间索引

在index6表中的space字段上建立名为index6_sp的空间索引。            

    CREATE  TABLE  index6（id  INT，space GEOMETRY NOT NULL,
                           SPATIAL INDEX index6_sp(space) )ENGINE=MyISAM;

7. 在已存在的表上建立索引

CREATE UNIQUE INDEX index_id ON index(course_id);

1）创建普通索引

 CREATE INDEX index7_id ON  example0(id);   
 //id字段上建立索引

2）唯一性索引

 CREATE UNIQUE  INDEX index8_id  ON  index8(course_id); 
 //索引为index8_id

3)全文索引

CREATE  FULLTEXT INDEX index9_info  ON  index9(info);  

4)单列索引

  CREATE INDEX index10_addr  ON  index10(address(4));
  //查询address前4个字符

5）创建多列索引

CREATE INDEX index11_na  ON index11(name,address);  
//必须有name字段

6）空间索引

CREATE SPATIAL INDEX  index12_line  ON  index12(line);

8. 用ALTER TABLE 语句创建索引

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT |SPATIAL] INDEX
                    索引名（属性名 [(长度)] [ASC|DESC]);

1）创建普通索引

    在s1表中name字段上建立index_name的索引。如下：

ALTER TABLE s1 ADD INDEX index_name(name(20)); 

2）唯一性索引

在s2表中course_id字段上，建立index_id的唯一性索引。如下：

ALTER TABLE s2 ADD UNIQUE INDEX index_id(course_id);

3)全文索引

    在s3表中的info字段上建立名为index_info的全文索引。如下：

ALTER TABLE s3 ADD FULLTEXT INDEX index_info(info);  

4)单列索引

    在s4表中的address字段上建立名为index_addr的单列索引。

ALTER TABLE s4 ADD INDEX index_addr(address(4));

5）创建多列索引

    在s5表中的name和address字段上建立名为index_na索引

ALTER TABLE s5 ADD INDEX index_na(name,address);

6）空间索引

    在s6表中的line字段上建立名为index_line的多列索引。

ALTER TABLE s6 ADD SPATIAL INDEX index_line（line);

---

9. 删除索引

DROP INDEX 索引名 ON 表名;

查看索引使用情况

show status like 'Handler_read%';   

Handler_read_rnd_next的值较高意味着查询运行抵消。

11. 索引优化

定期分析表和检查表

ANALYZE table tbl_name;     //表分析
CHECK table tbl_name;       //表检查

定期优化表

OPTIMIZE table tbl_name;