MySQL索引优化指南

-->

所谓索引就是利用一些特定的算法对特定的mysql字段进行排序，比如二叉树算法、哈希算法等。哈希算法就是建立特征值，然后根据特征值快速查找。最常用且默认的 MySQL 算法是二叉树算法 BTREE。通过 BTREE 算法索引的字段。例如扫描20行，就可以得到使用BTREE之前扫描2^20行的结果。具体实现方法将在本篇博客中讲述。会有一个算法专题进行具体的分析和讨论；

解释优化的查询检测

EXPLAIN可以帮助开发者分析SQL问题。解释展示了mysql如何使用索引来处理select语句和连接表。它可以帮助选择更好的索引并编写更优化的查询语句。

如何使用，只需在select语句前添加Explain即可：

解释 select * from blog 其中 false；

Mysql在执行查询之前会分析发送的每一条SQL，以决定是使用索引还是全表扫描。如果你发送一个 select * from blog where falseMysql 不会执行查询操作，因为它已经被 SQL 分析器处理过。经过分析，显然MySQL不会有任何与该操作相匹配的语句；

示例

mysql> 解释从 `user` WHERE `birthday` < "1990/2/2" 中选择 `birday`; - 结果： 身份证号码：1 select_type: SIMPLE -- 查询类型（简单查询、联合查询、子查询） table: user -- 显示这行数据与哪个表相关type: range -- 范围索引（小于1990/2/2的区间内的数据），这是一个重要的列，显示使用了什么类型的连接。从最好到最差的连接类型是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。 const表示一次命中，ALL表示扫描整个表以确定结果。一般来说，要保证查询至少达到range级别，最好达到ref。 possible_keys:birthday——指示MySQL可以使用哪个索引来查找该表中的行。如果为空，则没有相关索引。此时为了提高性能，可以检查WHERE子句，看看是否引用了某些字段，或者检查该字段是否不适合建立索引。 key：生日——实际使用的索引。如果为 NULL，则不使用索引。如果是primary，则表示使用了主键。 key_len: 4 -- 最长索引宽度。如果键为 NULL，则长度为 NULL。长度越短越好，且不会损失精度 ref: const – 显示与 key 一起使用的字段或常量。 rows: 1 -- 这个数字表示MySQL需要遍历多少数据才能找到它。在InnoDB上是不准确的。 额外：使用地点； using index -- 执行状态描述，这里你可以看到的不好的例子是Usingtemporary和Using

选择类型

1. 简单的简单选择（不使用联合或子查询）
2. 主要最外面的选择
3. union 中的第二个或后续 select 语句 union
4. 依赖联合 联合中的第二个或后续 select 语句取决于外部查询
5. 并集结果 并集的结果。
6. 子查询子查询中的第一个选择
7. 依赖子查询 子查询中的第一个 select 依赖于外部查询
8. 派生派生表select（from子句的子查询）

Extra及类型详细说明

1. Distinct：一旦MYSQL找到与行并集匹配的行，就不再搜索
2. 不存在：MYSQL优化了LEFT JOIN。一旦找到符合 LEFT JOIN 标准的行，它就不再搜索
3. 检查每个记录的范围（索引映射：#）：没有找到理想的索引，因此对于上一个表中的每个行组合，MYSQL 检查使用了哪个索引并使用它从表中返回行。这是使用索引
最慢的连接之一
4. Using filesort：当你看到这个时，说明查询需要优化。 MYSQL 需要额外的步骤来发现如何对返回的行进行排序。它根据连接类型和存储排序键值和匹配条件的行指针对所有行进行排序
5. 使用索引：从表中返回列数据，只使用索引中的信息，不读取实际动作。当表的所有请求列都是同一索引的一部分时，就会发生这种情况
6. 使用临时 当看到这个时，查询需要优化。这里，MYSQL需要创建一个临时表来存储结果。当对不同的列集执行 ORDER BY 而不是 GROUP BY
时，通常会发生这种情况
7. Where 使用使用 WHERE 子句来限制哪些行将与下一个表匹配或返回给用户。如果您不想返回表中的所有行并且连接类型为 ALL 或索引，或者查询有问题，就会发生这种情况。不同连接类型的说明（按效率排序
8. 系统表只有一行：系统表。这是 const 连接类型
的特例
9. const：表中能够匹配本次查询的记录的最大值（索引可以是主键，也可以是唯一索引）。因为只有一行，所以这个值其实是一个常量，因为MYSQL先读取这个值，然后把它当作常量
10. eq_ref：在连接中，MYSQL在查询时从上表的每个记录联合中读取一条记录。当查询使用索引作为主键或整个唯一键时使用。
11. ref：仅当查询使用的键不是唯一键或主键，或者是这些类型之一的一部分（例如，使用最左边的前缀）时，才会出现此连接类型。对于前一个表的每个行连接，将从表中读取所有记录。这种类型在很大程度上依赖于与索引匹配的记录数量 - 越少越好 +
12. 范围：此连接类型使用索引返回范围内的行，就像使用 > 或 < 查找某些内容时发生的情况 +
13. index：该连接类型对上表中的每条记录进行全量扫描（比ALL好，因为索引一般小于表数据）+
14. ALL：此连接类型对每个先前记录执行完整扫描。这通常是不好的，应该尽可能避免

其中类型：

1. 如果是Only index，则表示仅使用索引树中的信息来检索信息，这比扫描整个表要快。
2. 如果是whereused，则表示使用了where限制。
3. 如果不可能where，则表示不需要where，通常什么也找不到。
4. 如果这个信息显示Using filesort或者Usingtemporary，那就很难了。 WHERE 和 ORDER BY 的索引往往不能同时考虑两者。如果根据WHERE来确定索引，那么使用filesort必然会出现ORDER BY的情况，这就需要看看是先过滤后排序，还是先排序后过滤更划算。

索引

索引类型

UNIQUE唯一索引

不能出现相同的值，允许NULL值

INDEX 普通索引

允许相同的索引内容

PRIMARY KEY 主键索引

不允许相同的值，并且不能为NULL值。一张表只能有一个primary_key索引

全文索引 全文索引

以上三个索引都是对列的值起作用，但是全文索引可以针对值中的某个单词，比如文章中的某个单词。不过没什么用，因为只有myisam和英文支持，效率不敢恭维，不过可以使用coreseek、xunsearch等第三方应用来完成这个需求

索引凝乳

创建索引

更改表

适合在创建表格后添加

。

ALTER TABLE 表名 ADD 索引类型（唯一、主键、全文、索引）[索引名称]（字段名称）

ALTER TABLE `table_name` ADD INDEX `index_name` (`column_list`) -- 索引名称，可选；如果不是，则当前索引名称为字段名称； ALTER TABLE `table_name` ADD UNIQUE (`column_list`) ALTER TABLE `table_name` 添加主键（`column_list`） 更改表`table_name`添加全文键（`column_list`）

创建索引

CREATE INDEX 可以向表添加普通索引或UNIQUE索引

--例如可以只添加这两个索引； CREATE INDEX 索引名称 ON 表名称（列列表） CREATE UNIQUE INDEX 索引名称 ON 表名称（列列表）

另外，创建表格时还可以添加

创建表 `test1` ( `id`smallint(5) UNSIGNED AUTO_INCRMENT NOT NULL, -- 注意下面创建了主键索引，所以这里不需要创建。 `用户名` varchar(64) NOT NULL COMMENT '用户名', `昵称` varchar(50) NOT NULL COMMENT '昵称/名字', `介绍`文本， 主键（`id`）， UNIQUE KEY `unique1` (`username`), -- 索引名称，可选或不可选，如果不是则应与列名相同 KEY `index1` (`昵称`), 全文关键字“简介”（“简介”）) ENGINE=MyISAM AUTO_INCRMENT=4 DEFAULT CHARSET=utf8 COMMENT='后端用户表';

删除索引

删除索引 `index_name` ON `talbe_name` ALTER TABLE `table_name` DROP INDEX `index_name` -- 这两句话是等价的，都是删除table_name中的索引index_name； ALTER TABLE `table_name` DROP PRIMARY KEY -- 删除主键索引。注意，只有这样才能删除主键索引

查看索引

显示表名\G的索引;

指数变化

更换纱线，删除它并重建它

创建索引的技巧

1。为高维列创建索引

数据列中出现的唯一值的数量。数字越大，维度越高

例如数据表中有8行数据a,b,c,d,a,b,c,d。这张桌子的尺寸是4

为性别、年龄等高维度列创建索引，年龄维度高于性别

性别这样的列不适合创建索引，因为维度太低

2。对 where、on、group by、order by 中出现的列使用索引

3。对较小的数据列使用索引，这将使索引文件更小，并且可以在内存中加载更多索引键

4。对较长的字符串使用前缀索引

5。不要创建太多索引。除了增加额外的磁盘空间之外，还会对DML操作的速度产生很大的影响，因为每次添加、删除或修改时，都必须重新建立索引

6。使用组合索引可以减少文件索引大小，并且比多个单列索引更快

组合索引和前缀索引

请注意，这两个术语是索引技术的名称，而不是索引类型；

组合指数

MySQL单列索引和组合索引有什么区别？

为了形象地比较两者，我们先建一个表格：

创建表`myIndex`（`i_testID` INT NOT NULL AUTO_INCRMENT, `vc_Name` VARCHAR(50) NOT NULL, `vc_City` VARCHAR(50) NOT NULL, `i_Age` INT NOT NULL, `i_SchoolID` INT NOT NULL, 主键（`i_testID`） );

假设表中有1000条数据。这10000条记录中，vc_Name=”erquan”的记录有5条，分布在上面7个，下面8个。然而，城市、年龄、学校的组合是不同的。看看这个 T-SQL：

从 `myIndex` 中选择 `i_testID`，其中 `vc_Name`='二泉' AND `vc_City`='郑州' AND `i_Age`=25； -- 相关搜索；

首先考虑构建MySQL单列索引：

在 vc_Name 列上建立索引。执行T-SQL时，MYSQL快速定位vc_Name=erquan的5条记录，将其取出并放入中间结果集中。在这个结果集中，首先排除vc_City不等于“郑州”的记录，然后排除i_Age不等于25的记录，最后过滤掉唯一符合条件的记录。虽然在vc_Name上建立了索引，MYSQL在查询时不需要扫描全表，效率有所提高，但离我们的要求还有一定的距离。同样，在vc_City和i_Age中分别建立MySQL单列索引的效率也类似。

为了进一步提取MySQL的效率，需要考虑建立组合索引。只需将 vc_Name、vc_City、i_Age 构建为索引即可：

更改表 `myIndex` 添加索引 `name_city_age` (vc_Name(10),vc_City,i_Age);

建表时，vc_Name的长度是50，为什么这里用10呢？这就是下面讨论的前缀索引，因为一般名称长度不会超过10，这样会加快索引查询速度，减少查询次数。索引文件的大​​小提高了INSERT的更新速度。

执行T-SQL时，MySQL不需要扫描任何记录来查找唯一记录！！

如果对vc_Name、vc_City、i_Age分别创建单列索引，使得表有3个单列索引，那么查询效率会和上面的组合索引一样吗？答案非常不同，远远低于我们的综合指数。虽然此时有三个索引，但是MySQL只能使用它认为效率最高的单列索引。另外两个没有使用，也就是说仍然是全表扫描过程。

创建这样的组合索引实际上相当于单独建立

。

• vc_名称,vc_城市,i_年龄
• vc_名称,vc_城市
• vc_名称

这样的三个组合索引！为什么没有vc_City、i_Age等组合索引？这是因为mysql组合索引的“最左前缀”的结果。简单的理解就是只从最左边开始组合。不仅包含这三列的查询将使用此组合索引，以下 T-SQL 也会使用它：

SELECT * FROM myIndex WHREE vc_Name=”二泉” AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_Name=”二泉”

以下内容将不会被使用：

SELECT * FROM myIndex WHREE i_Age=20 AND vc_City=”郑州” SELECT * FROM myIndex WHREE vc_City=”郑州”

即name_city_age(vc_Name(10),vc_City,i_Age)从左到右索引。如果没有左前索引，Mysql不会执行索引查询

前缀索引

如果索引列的长度太长，对该列建立索引会产生一个很大的索引文件，操作起来不方便。可以使用前缀索引方法来索引。前缀索引要控制在合适的点，控制在黄金值0.31就可以了（大于这个值就可以创建）

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; — 如果这个值大于0.31，可以创建前缀索引，Distinct重复 ALTER TABLE `user` ADD INDEX `uname` (title(10)); — 添加前缀索引SQL，在10处建立人名的索引，可以减少索引文件的大​​小，加快索引查询速度

什么样的SQL不使用索引

尽量避免这些非索引sql

SELECT `sname` FROM `stu` W​​HERE `age`+10=30;--不会使用索引，因为所有索引列都参与计算 从左侧的“stu”中选择“sname”（“日期”，4）<1990； -- 不会使用索引，因为使用了函数操作，原理同上 SELECT * FROM `houdunwang` WHERE `uname` LIKE'Backing%' -- 转到索引 SELECT * FROM `houdunwang` WHERE `uname` LIKE "%Backing%" -- 无索引 -- 正则表达式不使用索引，这应该很容易理解，那么为什么在SQL中很难看到regexp关键字呢 -- 比较字符串和数字时不要使用索引； 创建表 `a` (`a` char(10)); EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 转到索引 EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 无索引 select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or，即使有带索引的条件也不会使用。换句话说，所有使用的字段都必须建立索引。我们建议您尽量避免使用 or 关键字。-- 如果mysql估计使用全表扫描比使用索引快，那么就不要使用索引

多表关联时的索引效率

• 从左侧的`stu`中选择`sname`（`日期`，4）<1990年； — 由于使用了函数运算，因此不会使用索引。原理同上
• SELECT * FROM `houdunwang` WHERE `uname` LIKE' backing%' — 转到索引
• SELECT * FROM `houdunwang` WHERE `uname` LIKE “%Backing%” — 无索引

从上图可以看出，所有表的类型都是all，表示全表索引；即6 6 6，一共进行了216次遍历查询；

除了第一个代表全表索引（需要与其他表关联）之外，其余都是范围（从索引区间得到），即6+1+1+1。一共9次遍历查询就够了。 ;

因此，我们建议在join多个表的时候，尽量join尽可能少的表，因为一不小心，就会是笛卡尔积的可怕扫描。另外，我们还建议尽可能使用left join，以少关联多。因为使用 join 的话，必须对第一个表进行全扫描。通过使用更少的关联和更多的连接，可以减少扫描次数。

索引的缺点

不要盲目创建索引，只为查询操作频繁的列创建索引。创建索引会让查询操作更快，但是会降低添加、删除和更新操作的速度，因为在执行这些操作时，它会对要重新排序或更新的索引文件；

不过，在互联网应用中，查询语句比DML语句要大得多，甚至可以占到80%~90%，所以不用太在意。只是在导入大数据时，可以先删除索引，然后批量插入数据，最后添加索引。

-->