四、mysql优化技术-索引管理

索引的目的在于提高查询效率。mysql中主要包括以下几种索引：

1. 主键索引
2. 唯一索引
3. 普通索引
4. 全文索引

主键索引：

一张表的主键自动会使用主键索引，主键索引只有一个，主键索引可以在创建表的时候添加，如

 

CREATE TABLE TABLE_NAME (
    ID INT PRIMARY KEY,
    NAME VARCHAR(20)
);

 也可以在表创建完后使用alter语句加上主键索引，如

 

 

ALTER TABLE TABLE_NAME ADD PRIMARY KEY(COLUMN_NAME);

主键字段不能为NULL，也不能重复 

 

 

唯一索引

当表的某列值唯一，即列中添加了unique约束时，该列就使用了唯一索引，既可以在创建表的时候就创建唯一索引，如

CREATE TABLE TABLE_NAME (
    ID INT PRIMARY KEY,
    NAME VARCHAR(20) UNIQUE
);

也可以建完表后再添加，如

 

CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);

 添加了unique约束的字段可以为NULL，并且可以有多个值为NULL，但如果是具体值，则不能重复

 

 

普通索引

普通索引由关键字KEY或INDEX定义，目的是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件或排序条件中的数据列创建索引，添加方式如下：

CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME);

 

全文索引

全文索引主要针对文本的检索，如表的某个字段直接存储了文章的内容，使用LIKE关键字无法很好的查询，创建方式如下：

CREATE TABLE TABLE_NAME (
    ID INT PRIMARY KEY,
    NAME VARCHAR(20)，
    CONTENT TEXT,
    FULLTEXT (NAME, CONTENT)
) ENGINE = MYISAM;

使用全文索引查询时方式如下：

 

SELECT * FROM TABLE_NAME WHERE MATCH(NAME, CONTENT) AGAINST ('KEY_WORDS');

 全文索引只对myisam存储引擎的表生效，如果有这种需求，一般不会直接使用数据库的全文索引，而是使用专门的全文检索工具，如lucene、solr等

 

 

查看索引

1、DESC TABLE_NAME;

 Key列显示的是索引，目前emp表没有任何索引，添加一个主键索引，将empno列设为主键

 再来查看一下

 这种方式查看索引时无法看到索引的名称

 

2、SHOW INDEX(ES)/KEYS FROM TABLE_NAME;

 

删除索引

 

ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;

如果是删除主键索引，可以直接使用如下方式

 

ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

 

回过头来看下上节的查询

 在没有创建任何索引时，这条sql语句执行了4秒多，现在empno列上添加了主键索引，再来执行以下

瞬间结果就出来了，看下执行计划

从查询计划中可以看出，使用了主键索引，查询类型为const，速度相当快，找到结果前预计至扫描了一行记录

 

索引使用注意事项

在dept表中增加一些数据，可以使用脚本中的insert_dept触发器，初始化完后，表中数据如下：

创建复合索引

 

 

1、索引会占用磁盘空间

2、索引会加快查询，但索引维护需要开销，数据更新的时候也要一并更新索引，影响更新的效率，所以，任何技术都有两面性

3、不出现在where条件中的列不应该创建索引

4、更新频繁的列不适合创建索引

5、唯一性差的列不适合创建索引

6、对于创建的多列索引，只要查询条件使用了最左边的列，索引一般会被使用，看如下执行计划

loc列位于复合索引index_name_loc的右侧，查询没有使用索引

dname列位于复合索引左侧，查询使用了索引

 

7、对于使用like的查询，查询如果是'%'开头的，不会使用索引，如果是以'%'结尾，则会使用索引

 与上面的查询唯一区别就是，查询以'%'开头，查询没有使用索引

 

8、如果条件中有or，即使其中有条件带索引，也不会使用

 empno列上有主见索引，但查询并没有使用

查询同样没有使用索引

 

9、如果列类型是字符串，一定要在条件中将数据使用引号引起来，否则不使用索引

 

查看索引使用情况

 

show status like 'Handler_read%';

 

 

Handler_read_first：读索引的第一项(的次数)

Handler_read_key：读索引的某一项(的次数)

Handler_read_last：读索引的最后一项(的次数)

Handler_read_next：读索引的下一项(的次数)

Handler_read_prev：读索引的前一项(的次数)

Handler_read_rnd：数据文件读取情况计数器

Handler_read_rnd_next：数据文件读取情况计数器

 

Handler_read_rnd 和 Handler_read_rnd_next 值越低越好，如果很高，应该进行索引相关的调优，而Handler_read_key的数值越高越好，越高代表使用索引读很高。

 

使用索引能快速提高查询速度，有的时候sql语句本身也会对查询速度造成很大影响，接下来看看sql语句本身的优化。