讲真，MySQL索引优化看这篇文章就够了

作者：CoderFocus
原文：www.cnblogs.com/songwenjie/p/9410009.html

本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容，下面一一展开。

一、MySQL——索引基础

首先，我们将从索引基础开始介绍一下什么是索引，分析索引的几种类型，并探讨一下如何创建索引以及索引设计的基本原则。

此部分用于测试索引创建的user表的结构如下：

1、什么是索引

“索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。”

——《高性能MySQL》

我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。

2、索引类型

可以使用SHOW INDEX FROM table_name;查看索引详情：

主键索引 PRIMARY KEY

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。

唯一索引 UNIQUE

唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引：

可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引：

普通索引 INDEX

这是最基本的索引，它没有任何限制。

可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引：

组合索引 INDEX

即一个索引包含多个列，多用于避免回表查询。

可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引：

全文索引 FULLTEXT

也称全文检索，是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引：

索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引。

3、索引设计的原则

二、MySQL——索引优化实战

上面我们介绍了索引的基本内容，这部分我们介绍索引优化实战。在介绍索引优化实战之前，首先要介绍两个与索引相关的重要概念，这两个概念对于索引优化至关重要。

此部分用于测试的user表结构：

1、索引相关的重要概念

基数

单个列唯一键（distict_keys）的数量叫做基数。

SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;

user表的总行数是5，gender列的基数是2，说明gender列里面有大量重复值，name列的基数等于总行数，说明name列没有重复值，相当于主键。

返回数据的比例：

user表中共有5条数据：

SELECT * FROM user;

查询满足性别为0（男）的记录数：

那么返回记录的比例数是：

同理，查询name为'swj'的记录数：

返回记录的比例数是：

现在问题来了，假设name、gender列都有索引，那么SELECT * FROM user WHERE gender = 0; SELECT * FROM user WHERE name = 'swj';都能命中索引吗？

user表的索引详情：

SELECT * FROM user WHERE gender = 0;没有命中索引，注意filtered的值就是上面我们计算的返回记录的比例数。

SELECT * FROM user WHERE name = 'swj';命中了索引index_name，因为走索引直接就能找到要查询的记录，所以filtered的值为100。

因此，返回表中30%内的数据会走索引，返回超过30%数据就使用全表扫描。当然这个结论太绝对了，也并不是绝对的30%，只是一个大概的范围。

回表

当对一个列创建索引之后，索引会包含该列的键值及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描，应该直接走全表扫描。

EXPLAIN命令结果中的Using Index意味着不会回表，通过索引就可以获得主要的数据。Using Where则意味着需要回表取数据。

2、索引优化实战

有些时候虽然数据库有索引，但是并不被优化器选择使用。

我们可以通过SHOW STATUS LIKE 'Handler_read%';查看索引的使用情况：

索引优化规则：

返回数据的比例是重要的指标mysql 模糊搜索 优化，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

name列创建普通索引：

前导模糊查询不能命中索引：

EXPLAIN SELECT * FROM user WHERE name LIKE '%s%';

非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询：

EXPLAIN SELECT * FROM user WHERE name LIKE 's%';

EXPLAIN SELECT * FROM user WHERE name=1;

EXPLAIN SELECT * FROM user WHERE;

name,age,status列创建复合索引：

ALTER TABLE user ADD INDEX index_name (name,age,status);

user表索引详情：

SHOW INDEX FROM user;

根据最左原则，可以命中复合索引index_name：

EXPLAIN SELECT * FROM user WHERE AND status=1;

注意，最左原则并不是说是查询条件的顺序：

EXPLAIN SELECT * FROM user WHERE status=1 AND;

而是查询条件中是否包含索引最左列字段：

EXPLAIN SELECT * FROM user WHERE status=2 ;

union:

EXPLAIN SELECT*FROM user WHERE status=1

UNION ALL

SELECT*FROM user WHERE status = 2;

in:

EXPLAIN SELECT * FROM user WHERE status IN (1,2);

or:

EXPLAIN SELECT*FROM user WHERE status=1OR status=2;

查询的CPU消耗：or>in>union

EXPLAIN SELECT * FROM payment WHERE customer_id = 203 OR amount = 3.96;

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

负向条件有：!=、、not in、not exists、not like等。

status列创建索引：

ALTER TABLE user ADD INDEX index_status (status);

user表索引详情：

SHOW INDEX FROM user;

负向条件不能命中缓存：

EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;

可以优化为in查询，但是前提是区分度要高，返回数据的比例在30%以内：

EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);

status,age列分别创建索引：

ALTER TABLE user ADD INDEX index_status (status);

ALTER TABLE user ADD INDEX index_age (age);

user表索引详情：

SHOW INDEX FROM user;

范围条件查询可以命中索引：

EXPLAIN SELECT * FROM user WHERE status>5;

范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引：

EXPLAIN SELECT * FROM user WHERE status>5 AND age

如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：

EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;

EXPLAIN SELECT * FROM user WHERE age>24;

EXPLAIN SELECT * FROM user WHERE age+1>24;

计算逻辑应该尽量放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。

被查询的列，数据能从索引中取得，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。

user表的索引详情：

因为status字段是索引列，所以直接从索引中就可以获取值，不必回表查询：

Using Index代表从索引中查询：

EXPLAIN SELECT status FROM user where status=1;

当查询其他列时，就需要回表查询，这也是为什么要避免SELECT*的原因之一：

EXPLAIN SELECT * FROM user where status=1;

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。

remark列建立索引：

ALTER TABLE user ADD INDEX index_remark (remark);

IS NULL可以命中索引：

EXPLAIN SELECT * FROM user WHERE remark IS NULL;

IS NOT NULL不能命中索引：

EXPLAIN SELECT * FROM user WHERE remark IS NOT NULL;

虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，应该使用NOT NULL约束以及默认值。

3、小结

对于自己编写的SQL查询语句，要尽量使用EXPLAIN命令分析一下，做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱，SQL能力是一个重要的指标。作为后端程序员，深以为然。

三、数据库索引背后的数据结构

第一部分开头我们简单提到，索引是存储引擎快速找到记录的一种数据结构。进一步说，在数据库系统里，这种数据结构要满足特定查找算法，即这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。

1、B-Tree

B-Tree是一种平衡的多路查找(又称排序)树，在文件系统中和数据库系统中有所应用，主要用作文件的索引。其中的B就表示平衡(Balance) 。

B-Tree的特性

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。那么B-Tree是满足下列条件的数据结构：

d为大于1的一个正整数，称为B-Tree的度：

h为一个正整数，称为B-Tree的高度：

key和指针互相间隔，节点两端是指针：

一个节点中的key从左到右非递减排列：

所有节点组成树结构。

每个指针要么为null，要么指向另外一个节点；每个非叶子节点由n-1个key和n个指针组成，其中d

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!