MYSQL调优3-索引

索引

上一章我们学习了使用explain来生成一个查询执行计划（QEP），从而发现语句存在的问题。在explain返回的结果中，有三个是和索引有关的（possible key、key、extra），可见索引在改善查询效率上的显著地位。

这一章，我们将全方位讲解如何使用索引来优化我们的数据库。

1.      索引的作用

索引的原理是利用特殊的查找算法（如二叉树算法），限制访问的行数，提高访问效率。

另外，索引还可以高效地在表之间建立连接操作，高效的对结果进行排序等等

2.      基本索引种类及创建

索引可以分为主键索引、普通索引、唯一索引和全文索引。

1)    主键索引

当一张表，把某个列设为主键的时候，则该列就是主键索引

create table aaa(id int unsigned primary key auto_increment ,name varchar(32) not null defaul ‘’);

这是id 列就是主键索引.

如果你创建表时，没有指定主键索引，也可以在创建表后再添加

alter table 表名 add primary key (列名);

2)    普通索引

一般来说，普通索引的创建，是先创建表，然后在创建普通索引

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

当括号中的列不止一个时，我们称之为多列索引，多列索引在一定情况下可以进化为覆盖索引（后面会介绍）。

这里我们通过在emp表中创建普通索引来见识一下索引的威力。

在未创建索引前

我们执行以下语句

select count(1) from emp where ename like 'yu%';

接着我们创建索引

create index index_ename on emp(ename);

再次执行

select count(1) from emp where ename like 'yu%';

不可思议！

索引的威力可见一斑！

当然索引是有开销的

这是我在未创建索引时MySQL安装目录下data/bigtable/emp.ibd文件的大小，对于innodb引擎的表来说，ibd文件存放了表的数据和索引。

在我创建了索引之后

显然，索引会占用主机的空间，这也称作索引的开销。

3)    唯一索引

当表的某列被指定为unique约束时，这列就是一个唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

这时, name 列就是一个唯一索引

在创建表后，再去创建唯一索引

create unique index 索引名  on 表名 (列表..);

unique字段可以为NULL,并可以有多NULL,即Null！=Null。但是如果是具体内容，则不能重复

4)    全文索引

全文索引，只对MyISAM引擎有用。主要是针对对文件，文本的检索, 比如文章或者段落,.

它会把某个数据表的某个数据列出现过的所有单词生成一份清单

全文索引的创建

在创建表时创建

CREATE TABLE articles (       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,       title VARCHAR(200),       body TEXT,       FULLTEXT (title,body)     )engine=myisam charset utf8;

或者在创建表后添加

ALTER TABLE articles ADD FULLTEXT (title,body);

全文索引正确的用法是:

select * from articles where match(title,body) against(‘要搜索的单词’);

另外，mysql自己提供的fulltext针对英文生效，想要搜索中文需要使用sphinx全文检索引擎或者使用加强版的模糊查询。

注意：

全文索引不完全等同于模糊查询

比如title字段有这么个数据’abcd20088ccaa’,使用模糊查询select * from articleswhere title like’%2008%’可以查找到，而使用全文检索select * from articleswhere match(title) against(‘2008’);是检索不到的，因为2008不是一个单词！

本讲结束，下一讲将讲解索引的相关操作。

3.      索引的相关操作

上一讲我们介绍了索引的作用和种类，这一讲我们谈谈索引的相关操作！

1)    添加索引

前面已经有所介绍，这里总结一下

添加主键索引

create table aaa(id int unsigned primary key auto_increment ,name varchar(32) not null defaul ‘’);alter table 表名 add primary key (列名);

添加普通索引

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

添加唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);create unique index 索引名  on 表名 (列表..);

添加全文索引

CREATE TABLE articles (       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,       title VARCHAR(200),       body TEXT,       FULLTEXT (title,body)     )engine=myisam charset utf8;ALTER TABLE articles ADD FULLTEXT (title,body);

2)    查询索引

show index from emp\G

3)    删除索引

drop index 索引名称 on 表名;

4)    修改索引

先删除，再创建

本讲结束，下一讲将给大家聊聊索引的代价。

4.      索引的代价

前面几讲介绍了索引在提高性能方面的威力以及如何使用索引，但是索引也是有开销的

1)    占用磁盘空间

如前面所述，索引是有开销的，表现在添加索引后.ibd文件（innodb引擎）或者.myi文件（myisam引擎）会变大。

2)    导致dml操作速度变慢

添加索引后之所以会快，是因为表依据索引对数据按某种算法（二叉树等）进行排序，所以删除、增加、插入后二叉树要重新排序，导致执行效率降低。

 

此时要看自己的数据库是dml语句执行的多还是dql语句执行的多

使用以下语句可以查询

 

查询一共执行了多少次select

show  status like ‘com_select’

查询一共执行了多少次insert

show status like ‘com_insert’

以此类推

 

一般来说，dql语句操作比dml语句要多得多！接近9:1

 

既然索引有利有弊，那么怎样人为地控制索引的使用呢？

强制不使用索引

select xxx from table ignore index(index_xxx)

强制使用索引

select * from table force index(index_xxx)

本讲结束，下一讲来讲一下如何正确地使用索引。

5.      什么情况下不要使用索引

既然索引是有代价的，那么就不要在不应该使用索引的情况下去使用它。

1)    数据唯一性差的字段不要使用索引

比如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描。

2)    频繁更新的字段不要使用索引

比如logincount登录次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

3)    字段不在where语句出现时不要添加索引

只有在where语句出现，MySQL才会去使用索引

4)    数据量少的表不要使用索引

使用了改善也不大

 

另外。如果mysql估计使用全表扫描要比使用索引快，则不会使用索引。

本讲结束，下一讲聊聊为什么有时候建了索引也不会被Mysql使用。

6.      添加了索引但不被使用的几种常见可能

上一讲聊了什么时候不要使用索引，但有时候使用了也不见得会被使用。

下面是几种添加了索引但不被使用的情况

1)    多列索引查询条件没有使用最左边的字段

对于创建的多列索引，如果查询条件没有使用最左边的列，索引就不会被使用。

多列索引：一个索引对应多个列

比如

我创建了这么个多列索引

create index index_deptno_loc on dept (deptno,loc);

如果where语句中有deptno则会使用索引，否则不使用

如下

2)    如果条件中有or

只要条件中有一个字段没有添加索引，则不会使用索引

 

3)    类型不对应

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

 

4)    MySQL优化器的决定

如果mysql估计使用全表扫描要比使用索引快，则不使用索引

 

最后一点，也是笔者感受最深的一点

5)    like‘%aaa’不会使用到索引

只要模糊查询的模糊值在字符串前面，则不会使用索引‘%aaa’和‘_aaa’都不会！

如下

应该说这是Mysql给程序员们开的一个玩笑。要是我的表数据量很大，而且又需要使用like’%%’这样的模糊查询来检索时，该怎么办？？

下一章，笔者将会给大家分享解决这个问题的N种方法！

7.      解决like’%str’不使用模糊查询的4种方法

上一讲最后说了，只要模糊查询的模糊值在字符串前面，则不会使用索引，‘%aaa’和‘_aaa’都不会！

如下

应该说这是MySQL给程序员们开的一个玩笑。要是我的表数据量很大，而且又需要使用like’%%’这样的模糊查询来检索时，该怎么办？？

接下来，笔者将会给大家分享解决这个问题的四种方法！

1)    Select主键

只要Select的字段刚好是主键，那么就会使用到索引(只对innodb数据库有效)

比如下面的

select idfrom emp where ename like '%haha%'\G

就使用了索引

select * from empwhere ename like '%haha%'\G

则不使用索引

除了主键，其他字段必须设置为覆盖索引才能使索引生效，不能单独设置索引

比如下面这种是不会使用索引的

可以采用分步查询的方法，先select主键再利用主键去找其他字段。不过好像比较麻烦！别怕！接下来会讲一种最优方法——覆盖索引法！

2)    覆盖索引法

覆盖索引是一种特殊的多列索引，当多列索引指向一个查询语句中所有的字段时，该多列索引就被称为覆盖索引。

使用覆盖索引可以解决问题！

创建覆盖索引

当然，如果你想要select很多字段甚至是select*，那你可以创建一个多列索引指向所有字段（innodb可以不指向主键）

注意：
笔者发现，当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！

3) 全文索引法
此方法有较大局限。
全文索引，只对MyISAM引擎有用。主要是针对对文件，文本的检索, 比如文章或者段落,.
它会把某个数据表的某个数据列出现过的所有单词生成一份清单
少于3个字符的单词不会被包含在全文索引里，可以通过修改my.cnf修改选项
ft_min_word_len=3
但是！
全文索引不完全等同于模糊查询
比如title字段有这么个数据’abcd20088ccaa’,使用模糊查询select * from articles wheretitle like’%2008%’可以查找到，而使用全文检索select * from articles where match(title) against(‘2008’);是检索不到的，因为2008不是一个单词！

4) 使用全文检索引擎工具包
采用lucene、Sphinx、solr等专门的全文检索开源工具可以检索某段字符串。