首页 / MYSQL / 第六章· MySQL索引管理及执行计划

第六章· MySQL索引管理及执行计划

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了第六章· MySQL索引管理及执行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8078字，纯文字阅读大概需要12分钟。

内容图文

一.索引介绍

1.什么是索引

1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。
2）让获取的数据更有目的性，从而提高数据库检索数据的性能。

2.索引类型介绍

1)BTREE:B+树索引
2)HASH：HASH索引
3)FULLTEXT：全文索引
4)RTREE：R树索引

图1·B+tree索引
?
图2·B*tree索引

3.索引管理

索引建立在表的列上(字段)的。
在where后面的列建立索引才会加快查询速度。
pages<---索引（属性）<----查数据。

3.1、索引分类：

主键索引
普通索引*****
唯一索引

3.2、添加索引：

#创建索引
alter table test add index index_name(name);
#创建索引
create index index_name on test(name);
#查看索引
desc table;
#查看索引
show index from table;
#删除索引
alter table test drop key index_name;
#添加主键索引（略）
#添加唯一性索引
alter table student add unique key uni_xxx(xxx);
#查看表中数据行数
select count(*) from city;
#查看去重数据行数
select count(distinct name) from city;

3.3、前缀索引和联合索引

前缀索引

根据字段的前N个字符建立索引

alter table test add index idx_name(name(10));

避免对大列建索引
如果有，就使用前缀索引

联合索引

多个字段建立一个索引

例：
where a.女生 and b.身高 and c.体重 and d.身材好
index(a,b,c)
特点：前缀生效特性
a,ab,ac,abc,abcd 可以走索引或部分走索引
b bc bcd cd c d ba ... 不走索引

原则：把最常用来做为条件查询的列放在最前面

#创建people表
create table people (id int,name varchar(20),age tinyint,money int ,gender enum('m','f'));
#创建联合索引
alter table people add index  idx_gam(gender,age,money);

二.explain详解

explain命令使用方法

mysql> explain select name,countrycode from city where id=1;

explain命令应用
查询数据的方式

1.全表扫描

1）在explain语句结果中type为ALL
2）什么时候出现全表扫描?
2.1 业务确实要获取所有数据
2.2 不走索引导致的全表扫描
2.2.1 没索引
2.2.2 索引创建有问题
2.2.3 语句有问题
生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描

2.索引扫描

- 2.1 常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null

从上到下，性能从最差到最好，我们认为至少要达到range级别

index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A

join B 
on A.sid=B.sid

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

mysql> explain select * from city where id=1000;

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> explain select * from city where id=1000000000000000000000000000;

Extra（扩展）

Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer

如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引

mysql> explain select * from city where countrycode='CHN' order by population;

当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现

mysql> explain select * from city where population>30000000 order by population;
mysql> select * from city where population=2870300 order by population;

key_len: 越小越好

前缀索引去控制

rows: 越小越好

三.建立索引的原则（规范）

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

那么索引设计原则又是怎样的?

1、选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

例如:
学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。

select count(*) from world.city;
select count(distinct countrycode) from world.city;
select count(distinct countrycode,population ) from world.city;

注意：如果重复值较多，可以考虑采用联合索引

2．为经常需要排序、分组和联合操作的字段建立索引

例如:
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。
如果为其建立索引，可以有效地避免排序操作

3．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
因此，为这样的字段建立索引，可以提高整个表的查询速度。

3.1 经常查询
3.2 列值的重复值少
注：如果经常作为条件的列，重复值特别多，可以建立联合索引

4．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

5．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7.重点关注：

7.1.没有查询条件，或者查询条件没有建立索引

#全表扫描
select * from table;
select  * from tab where 1=1;

在业务数据库中，特别是数据量比较大的表,是没有全表扫描这种需求。

1）对用户查看是非常痛苦的。
2）对服务器来讲毁灭性的。
3）SQL改写成以下语句：

#情况1
#全表扫描
select * from table;
#需要在price列上建立索引
selec  * from tab  order by  price  limit 10;
#情况2
#name列没有索引
select * from table where name='zhangsan'; 
1、换成有索引的列作为查询条件
2、将name列建立索引

7.2.查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from city where population>3000 order by population;

1）如果业务允许，可以使用limit控制。
2）结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。

7.3.索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决

7.4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)

#例子
错误的例子：select * from test where id-1=9; 
正确的例子：select * from test where id=10;

7.5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

mysql> create table test (id int ,name varchar(20),telnum varchar(10));
mysql> insert into test values(1,'zs','110'),(2,'l4',120),(3,'w5',119),(4,'z4',112);
mysql> explain select * from test where telnum=120;
mysql> alter table test add index idx_tel(telnum);
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum=120;
mysql> explain select * from test where telnum='120';

7.6． <> ，not in 不走索引

mysql> select * from tab where telnum <> '1555555';
mysql> explain select * from tab where telnum <> '1555555';

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit
or或in尽量改成union

EXPLAIN  SELECT * FROM teltab WHERE telnum IN ('110','119');
#改写成
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

7.7．like "%_" 百分号在最前面不走

#走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';
#不走索引
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';

%linux%类的搜索需求，可以使用Elasticsearch -------> ELK

7.8.单独引用联合索引里非第一位置的索引列

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m'; 
#不走索引
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';

内容总结

以上是互联网集市为您收集整理的第六章· MySQL索引管理及执行计划全部内容，希望文章能够帮你解决第六章· MySQL索引管理及执行计划所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/908650.html

来源：【匿名】

【上一篇】第五章· MySQL数据类型【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【第六章· MySQL索引管理及执行计划】教程文章相关的互联网学习教程文章

在MySQL中使用explain查询SQL的执行计划【代码】

1、什么是MySQL执行计划要对执行计划有个比较好的理解，需要先对MySQL的基础结构及查询基本原理有简单的了解。 MySQL本身的功能架构分为三个部分，分别是应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。逻辑层，主要负责查询处理、事务管理等其他数据库功能处...

MySQL EXPLAIN 命令: 查看查询执行计划【代码】

MySQL 的 EXPLAIN 命令可以查看SELECT语句的执行的计划，是 MySQL 查询优化的必备工具。通过执行计划可以了解查询方式、索引使用情况、需要扫描的数据量以及是否需要临时表或排序操作等信息。我们需要分析执行计划对查询进行有的放矢的优化。需要注意:EXPLAIN不考虑触发器、存储过程或用户自定义函数对查询的影响EXPLAIN不考虑缓存EXPLAIN只能分析执行计划，不能显示存储引擎在执行查询过程中进行的操作部分统计信息是估算的，并非...

MySQL执行计划explain的key_len解析

作者：沃趣科技高级数据库专家邱文辉前言当用Explain查看SQL的执行计划时，里面有列显示了 key_len 的值，根据这个值可以判断索引的长度，在组合索引里面可以更清楚的了解到了哪部分字段使用到了索引。下面演示中，表结构的合理性这边暂且不说，只是证明一下索引长度的计算方法。目前大部分博文是字符类型的索引长度计算方法，下面列举几个类型的索引长度计算方法：1、整数类型(dg1)root@127.0.0.1 [mytest]> desc table_key;+-...

( 转 ) MySQL高级之 explain执行计划详解【图】

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。explain执行计划包含的信息其中最重要的字段为：id、type、key、rows、Extra各字段详解idselect查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序三种情况： 1、id相同：执行顺序由上至下 2、id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 3、id相同...

[MySQL] explain执行计划解读【代码】【图】

Explain语法EXPLAIN SELECT …… 变体： 1. EXPLAIN EXTENDED SELECT …… 将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 2. EXPLAIN PARTITIONS SELECT …… 用于分区表的EXPLAIN执行计划包含的信息 id包含一组数字，表示查询中执行select子句或操作表的顺序 id相同，执行顺序由上至下如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行 id如果相同，可以认为是一组，...

mysql 执行计划的理解

1、执行计划就是在sql语句之前加上explain，使用desc 也可以。2、desc有两个选项extended和partitions，desc extended 将原sql语句进行优化，通过show warnings 可以看到优化后的sql语句。 desc partitions 可以查看使用分区表的信息。3、比如：mysql> desc select * from student;+----+-------------+---------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possibl...

MySQL——通过EXPLAIN分析SQL的执行计划

在MySQL中，我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。下面分别对EXPLAIN命令结果的每一列进行说明：select_type:表示SELECT的类型，常见的取值有：类型说明SIMPLE简单表，不使用表连接或子查询PRIMARY主查询，即外层的查询UNIONUNION中的第二个或者后面的查询语句SUBQUERY子查询中的第一个table:输出结果集的表（表别名）type:表示MySQL在表中找到所需行的方...

mysql执行计划初步解读2【代码】

继续上次没有写完的执行计划部分6.possible_keys可能使用到的索引，这个的理解呢，表示查询时可能使用的索引。只是可能用到，但是并不一定用到。如果是空的，表明肯定是没有相关的索引。但是如果有值，也不一定能够用到。7.key实际使用的索引。6，7的话一起来看下面的例子： mysql> explain select * from `order` where seller_id = 19; +----+-------------+-------+------------+------+------------------+------+---------+...

MySQL执行计划【代码】【图】

MySQL 使用 explain + sql 语句查看执行计划，该执行计划不一定完全正确但是可以参考。EXPLAINSELECT * FROMuserWHERE nid = 3;select_type说明SIMPLE简单查询PRIMARY最外层查询SUBQUERY映射为子查询DERIVED子查询UNION联合UNION RESULT使用联合的结果table : 正在访问的表名type说明ALL全数据表扫描index全索引表扫描RANGE对索引列进行范围查找INDEX_MERGE合并索引，使用多个单列索引搜索REF根据索引查找一个或多个值EQ_REF搜索时...

Oracle从缓存里面查找真实的执行计划【图】

有关Oracle 的执行计划说明，参考：Oracle Explain Plan 有关Oracle 的执行计划说明，参考：Oracle Explain Plan 见一. 查看当前session 的SIDSYS@anqing1(rac1)> SELECT USERENV(SID) FROM DUAL;USERENV(SID)--------------137SYS@anqing1(rac1)> SELECT SID FROM V$MYSTAT WHERE ROWNUM =1;SID----------137 二. 查看缓存中的Explain Plan1）根据SID，从v$sql中找到相应SQL的HASH_VALUE和ADDRESS/* Formatted on 2011/6/20 1...

MySQL执行计划extra中的using index 和 using where using index 的区别【代码】【图】

MySQL执行计划中的extra列中表明了执行计划的每一步中的实现细节，其中包含了与索引相关的一些细节信息其中跟索引有关的using index 在不同的情况下会出现Using index， Using where Using index ，Using index condition等那么Using index 和 Using where;Using index 有什么区别？网上搜了一大把文章，说实在话也没怎么弄懂，于是就自己动手试试。本文仅从最简单的单表去测试using index 和 using where using index以及简单测试...

MySQL执行计划分析工具EXPLAIN用法详解_PHP教程【图】

MySQL执行计划分析工具EXPLAIN用法详解对于DBA来讲熟悉SQL的执行计划分析技巧对于快速定位数据库性能问题至关重要，下面简单介绍一下如何分析MySQL的执行计划。一、EXPLAIN用法详解：EXPLAIN SELECT …… 变体： 1. EXPLAIN EXTENDED SELECT ……将执行计划“反编译”成SELECT语句，运行SHOW WARNINGS 可得到被MySQL优化器优化后的查询语句 2. EXPLAIN PARTITIONS SELECT ……用于分区表的EXPLAIN示例如下所示：具体解释如下1.执行...

MySQL执行计划explain的key_len解析

.0.1 [mytest]> desc table_key; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | sid | bigint(20) | NO | | 0 | | | name | char(10) | YES | | NULL | | | age | tinyint(4) | Y...

mysql 执行计划的理解

网站优化—mysql explain执行计划【图】

：通过上面的步骤就可以对MySQL进行调优，只要将上面的步骤重复不断执行，可以使用MySQL在最优的情况下工作。?profile定义：MySQL提供的一个可以分析SQL语句执行过程的工具，通过它大致可以了解到MySQL将如何执行用户传递过来的sql语句。用法使用的时候只要在对应的sql语句前面，加上关键字explain 或者同义词 desc # desc select * from tableName where id = 1000000; # explain select * from tableName where id = 1000000; ...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / 第六章· MySQL索引管理及执行计划

第六章· MySQL索引管理及执行计划

内容导读

内容图文

一.索引介绍

1.什么是索引

2.索引类型介绍

3.索引管理

3.1、索引分类：

3.2、添加索引：

3.3、前缀索引和联合索引

二.explain详解

1.全表扫描

2.索引扫描

三.建立索引的原则（规范）

1、选择唯一性索引

2．为经常需要排序、分组和联合操作的字段建立索引

3．为常作为查询条件的字段建立索引

4．尽量使用前缀来索引

5．限制索引的数目

6．删除不再使用或者很少使用的索引

7.重点关注：

7.1.没有查询条件，或者查询条件没有建立索引

7.2.查询结果集是原表中的大部分数据，应该是25％以上

7.3.索引本身失效，统计数据不真实

7.4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)

7.5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误

7.6． <> ，not in 不走索引

7.7．like "%_" 百分号在最前面不走

7.8.单独引用联合索引里非第一位置的索引列

内容总结

内容备注

内容手机端

【第六章· MySQL索引管理及执行计划】教程文章相关的互联网学习教程文章

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程