MySQL索引优化实例说明

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL索引优化实例说明，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5890字，纯文字阅读大概需要9分钟。

内容图文

下面分别创建三张表，并分别插入1W条简单的数据用来测试，详情如下：

[1] test_a 有主键但无索引

CREATE TABLE `test_a` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(100) NOT NULL,

`content` text NOT NULL,

`number` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

[2] test_b 有主键和单列索引

CREATE TABLE `test_b` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(100) NOT NULL,

`content` text NOT NULL,

`number` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `titleIndex` (`title`) USING BTREE,

UNIQUE KEY `numberIndex` (`number`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

[3] test_c 有主键和组合索引

CREATE TABLE `test_c` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(100) NOT NULL,

`content` text NOT NULL,

`number` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`),

KEY `titleNumberIndex` (`title`,`number`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

下面按照单列索引和组合索引分别对以上三张表进行查询测试，另外下面的时间都是多次测试取的平均值。

[1] 单列索引

[1.1] 查询指定的字段

[SQL] SELECT * FROM test_a WHERE title = ‘title_5000‘;

[USE] 12ms

[SQL] SELECT * FROM test_b WHERE title = ‘title_5000‘;

[USE] <1ms

说明：可以看到未加索引时间为12ms，加索引后小于1ms，还是相差很大的。

[SQL] SELECT * FROM test_a WHERE content = ‘content_5000‘;

[USE] 13ms

[SQL] SELECT * FROM test_b WHERE content = ‘content_5000‘;

[USE] 13ms

说明：由于content字段都未加索引，因此时间基本一致。

[1.2] 测试LIKE查询

[SQL] SELECT * FROM test_a WHERE title LIKE ‘%5000‘;

[USE] 13ms

[SQL] SELECT * FROM test_b WHERE title LIKE ‘%5000‘;

[USE] 13ms

说明：两者用时基本一致，因此索引并未命中。

[SQL] SELECT * FROM test_a WHERE title LIKE ‘5000%‘;

[USE] 12ms

[SQL] SELECT * FROM test_b WHERE title LIKE ‘5000%‘;

[USE] <1ms

说明：如果LIKE是前缀匹配则会命中索引，否则不会命中。另外以上的查询结果为空，但是不影响说明索引的作用。

[1.3] 测试OR语句

[SQL] SELECT * FROM test_a WHERE title = ‘title_5000‘ OR content = ‘content_5000‘;

[USE] 13ms

[SQL] SELECT * FROM test_b WHERE title = ‘title_5000‘ OR content = ‘content_5000‘;

[USE] 13ms

说明：两者用时基本一致，因此索引并未命中。

[SQL] SELECT * FROM test_a WHERE title = ‘title_5000‘ OR number = ‘5000‘;

[USE] 13ms

[SQL] SELECT * FROM test_b WHERE title = ‘title_5000‘ OR number = ‘5000‘;

[USE] <1ms

说明：如果OR两边的字段都加索引则命中，否则只有一个加索引则不命中。

[1.4] 测试IN语句

[SQL] SELECT * FROM test_a WHERE title IN(‘title_4999‘,‘title_5000‘,‘title_5001‘);

[USE] 12ms

[SQL] SELECT * FROM test_b WHERE title IN(‘title_4999‘,‘title_5000‘,‘title_5001‘);

[USE] <1ms

说明：IN语句也是可以命中索引的。

[1.5] 测试(BETWEEN,>,>=,<,<=)语句

[SQL] SELECT * FROM test_a WHERE number <= 5000;

[USE] 14ms

[SQL] SELECT * FROM test_b WHERE number <= 5000;

[USE] 14ms

[SQL] SELECT * FROM test_a WHERE number < 5000;

[USE] 14ms

[SQL] SELECT * FROM test_b WHERE number < 5000;

[USE] 14ms

[SQL] SELECT * FROM test_a WHERE number > 5000;

[USE] 14ms

[SQL] SELECT * FROM test_b WHERE number > 5000;

[USE] 14ms

[SQL] SELECT * FROM test_a WHERE number >= 5000;

[USE] 14ms

[SQL] SELECT * FROM test_b WHERE number >= 5000;

[USE] 14ms

[SQL] SELECT * FROM test_b FORCE INDEX(numberIndex) WHERE number >= 5000;

[USE] 14ms

[SQL] SELECT * FROM test_a WHERE number BETWEEN 4999 AND 5001;

[USE] 11ms

[SQL] SELECT * FROM test_b WHERE number BETWEEN 4999 AND 5001;

[USE] <1ms

说明：BETWEEN可以命中索引，其他比较符号未命中索引，强制使用索引效果也不明显，这个应该是与MySQL的索引足够性判断，如果索引大于30%就会使用全表扫描，具体待我查证后再详细介绍。

[1.6] 字段使用函数

[SQL] SELECT * FROM test_a WHERE SUBSTR(title,7,4) = ‘5000‘;

[USE] 13ms

[SQL] SELECT * FROM test_b WHERE SUBSTR(title,7,4) = ‘5000‘;

[USE] 13ms

说明：对字段使用函数则不能命中索引。

[2] 组合索引

[2.1] 测试OR语句

[SQL] SELECT * FROM test_a WHERE title = ‘title_5000‘ OR content = ‘content_5000‘;

[USE] 13ms

[SQL] SELECT * FROM test_c WHERE title = ‘title_5000‘ OR content = ‘content_5000‘;

[USE] 13ms

说明：组合索引中的OR语句没有命中索引。

[2.2] 测试AND语句

[SQL] SELECT * FROM test_a WHERE title = ‘title_5000‘ AND number = 5000;

[USE] 12ms

[SQL] SELECT * FROM test_c WHERE title = ‘title_5000‘ AND number = 5000;

[USE] <1ms

[SQL] SELECT * FROM test_a WHERE number = 5000;

[USE] 12ms

[SQL] SELECT * FROM test_c WHERE number = 5000;

[USE] 12ms

[SQL] SELECT * FROM test_a WHERE title = ‘title_5000‘;

[USE] 12ms

[SQL] SELECT * FROM test_c WHERE title = ‘title_5000‘;

[USE] <1ms

说明：以上证明了最左前缀匹配原则。

MySQL中EXPLAIN解释命令

EXPLAIN命令显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了，如：

explain select surname,first_name form a,b where a.id=b.id

EXPLAIN列的解释：

table：显示这一行的数据是关于哪张表的

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：MYSQL认为必须检查的用来返回请求数据的行数

Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

system 表只有一行：system表。这是const连接类型的特殊情况

const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

原文：http://www.cnblogs.com/gide/p/4446347.html

内容总结

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

内容备注

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

内容手机端

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

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

来源：【匿名】

【上一篇】MySQL中UPDATE语句里SET后使用AND的执行过程和结果分析【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL索引优化实例说明】教程文章相关的互联网学习教程文章

下面分别创建三张表，并分别插入1W条简单的数据用来测试，详情如下：[1] test_a 有主键但无索引 CREATE TABLE `test_a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `title` varchar(100) NOT NULL, `content` text NOT NULL, `number` int(10) unsigned NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8; [2] test_b 有主键和单列索引 CREATE TABLE `test_b` ( `id` int(10) uns...

MySQL函数索引及优化【代码】【图】

很多开发人员在使用MySQL时经常会在部分列上进行函数计算等，导致无法走索引，在数据量大的时候，查询效率低下。针对此种情况本文从MySQL5.7 及MySQL8.0中分别进行不同方式的优化。1、 MySQL5.7MySQL5.7版本中不支持函数索引，因此遇到函数索引的时候需要进行修改，否则即使查询的字段上有索引，执行时也无法使用索引而进行全表扫描，数据量大的表查询时间会比较长。具体案例如下：1.1 创建测试表及数据mysql>use testdb; Databas...

MYSQL索引优化【图】

1.索引的作用：　　（1）提高查询速度　　（2）提高排序速度　　（3）提高分组统计的速度2.索引的解析：explain select * from t4 where a1=3 and a2>4 and a3= 53.聚簇索引和非聚簇索引myisam 存储引擎使用的是非聚簇索引innodb 采用的是聚簇索引（主键使用），其他字段的索引是次级索引 innodb的主索引文件上，直接存放该行数据称为聚簇索引，次索引指向对主键的引用。myisam 中，主索引和次索引，都指向物理行（磁盘位置）4.索引...

MYSQL索引优化之单表示例【图】

1. 创建表　CREATE TABLE IF NOT EXISTS `article` ( `id` BIGINT(10) NOT NULL AUTO_INCREMENT, `author_id` INT(10) NOT NULL, `category_id` INT(10) NOT NULL, `views` INT(10) NOT NULL, `comments` INT(10) NOT NULL, `title` VARCHAR(10) COLLATE utf8_unicode_ci NOT NULL, `content` TEXT COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=u...

MySQL——索引优化实战

上篇文章中介绍了索引的基本内容，这篇文章我们继续介绍索引优化实战。在介绍索引优化实战之前，首先要介绍两个与索引相关的重要概念，这两个概念对于索引优化至关重要。本篇文章用于测试的user表结构：索引相关的重要概念基数单个列唯一键（distict_keys）的数量叫做基数。SELECT COUNT(DISTINCT name),COUNT(DISTINCT gender) FROM user;user表的总行数是5，gender 列的基数是 2，说明 gender 列里面有大量重复值，name 列的基数...

mysql优化工具（索引优化）

mysql优化工具1.pt-duplicate-key-checker(检查数据库的重复索引),这款工具可以帮助我们找到重复的索引并且还会给你删除重复索引的建议语句，非常好用。 2. 原文：https://www.cnblogs.com/ccw869476711/p/11910095.html

MySQL的索引及其优化【图】

前言索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对...

MySQL Order By索引优化方法

尽管 ORDER BY 不是和索引的顺序准确匹配，索引还是可以被用到，只要不用的索引部分和所有的额外的 ORDER BY 字段在 WHERE 子句中都被包括了。使用索引的MySQL Order By 下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：复制代码代码如下:SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; SELECT * FROM t1 WHERE key_part1=constant GROUP ...

MySQL索引优化--对前缀索引使用like模糊匹配时的实际索引选择

由于我在最近的项目中对mysql的某张表的varchar列加上前缀索引后，在查询语句中即使where子句里只有course_num like "4%"这个条件，通过使用explain发现还是会走all类型进行全表查询。随后，我发现用绝大多数的博文中的数据进行复盘测试时，得到的结果和他们的对不上，在翻阅MySQL的优化器相关知识时，绝大多数博文中写的形如"4%"这种百分号在后面的一定会走range类型用前缀索引进行查询，这不是绝对正确的，在一些情况下不会走前缀...

MySQL架构、存储索引、缓存、索引优化【代码】【图】

文章目录 MySQL的架构详解存储引擎MyISAMInnoDB其他存储引擎管理存储引擎 MySQL中的系统数据库mysql 数据库performance_schema 数据库information_schema 数据库sys 数据库 MySQL服务器配置及状态命令行选项及服务器选项服务器系统变量服务器状态变量服务器变量SQL_MODE 查询缓存 Query Cache查询缓存的原理查询缓存相关的服务器变量SELECT语句的缓存控制查询缓存相关的状态变量查询的优化命中率和内存使用率估算参考文献：MySQL的...

小蚂蚁学习mysql性能优化（3）--SQL以及索引优化--慢查日志分析工具和explain说明

昨天在测试操作数据库的时候碰到两个问题忘了记录下来，今天补充上去，接上篇 1. 安装测试数据库sakila时报错。Mysql server has gone away的问题。解决方法：查看 show global variables like ‘max_allowed_packet‘; 一般来说会显示 max_allowed_packet 1048576 修改为 set global max_allowed_packet = 1024*1024*16; 问题解决。不要问我为什么，我也不知道 T_T 2. 我用 mysql 版本 5.6.* 在设置l...

mysql5.6.20开启慢查询日志以及创建索引优化慢查询

root@localhost : 100.00% (6) of query, 100.00% (6) of all usersQuery abstract:SELECT user_id,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM tab_user_login_record WHERE login_time BETWEEN N AND N GROUP BY user_id;Query sample:SELECT `user_id`,COUNT(id) AS num,MAX(login_time) AS last_login_time FROM `tab_user_login_record` WHERE `login_time` BETWEEN 1501862400 AND 1503158399 GROUP BY user...

MySQL第9天：MySQL索引优化分析之join查询【代码】【图】

MySQL索引优化分析之join查询 #编写时间：2017.3.12 #编写地点：广州常见join查询： 1、SQL执行顺序：手写、机读、总结（1）手写（2）机读（3）总结2、join图 3、建立SQL 建立部门表和员工表4、7种join （1）练习1（2）练习2（3）练习3（4）练习4（5）练习5（6）练习6注意：mysql不支持FULL OUTER；union表示合并且去重。（7）练习7

MySQL如何利用索引优化ORDER BY排序语

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。 MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。通过索引优化来实现MySQL的ORDER BY语句优化：1、ORDER BY的索引优化。如果一个SQL语句形如： SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。2、WHERE + ORDER BY的索引...

mysql 索引优化 Explain的使用【图】

SIMPLE：表示此查询不包含 UNION 查询或子查询 PRIMARY：表示此查询是最外层的查询 SUBQUERY：子查询中的第一个 SELECT UNION：表示此查询是 UNION 的第二或随后的查询 DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询 UNION RESULT, UNION 的结果 DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果. DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询...

首页 / MYSQL / MySQL索引优化实例说明

MySQL索引优化实例说明

内容导读

内容图文

内容总结

内容备注

内容手机端

【MySQL索引优化实例说明】教程文章相关的互联网学习教程文章