MySQL 索引优化指南

内容导读

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

内容图文

MySQL索引

原理

技术分享图片

b+ 树，记住这棵树！索引所有优化都围绕这棵树展开（hash索引除外）

优缺点

优点

索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变成顺序IO
索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增加锁的开销，降低并发性。 InnoDB仅对需要访问的元组加锁，而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做（即索引达不到过滤的目的），MySQL服务器只能对InnoDB返回的数据进行WHERE操作，此时，已经无法避免对那些元组加锁了。如果查询不能使用索引，MySQL会进行全表扫描，并锁住每一个元组，不管是否真正需要。
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效；

类型

存储方式：

聚族索引：同一个数据结构中保存了索引与数据（如InnoDB的主键索引，适合多行检索）

非聚族索引：非聚族索引，也称二级索引（适合单行检索）

索引类型：

普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。

唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。

主键索引：不允许值为空的唯一索引。

组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

全文索引：全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。（很少使用，业界基本用 ES）

使用

原则：

最左前缀匹配原则：
索引选择性：公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重复的比率，比率越大扫描的记录数就越少，性能越佳
索引尽可能短：B+树数据结构决定，过长降低性能；
索引列不能参与计算：B+树数据结构决定，如有函数处理索引将会实效；

注意点：

索引覆盖（一个索引包含(或者说是覆盖)需要查询的所有字段）可减少回表操作提高查询速度；
查询条件可以乱序，MySQL的查询优化器会优化成索引可以识别的模式；
尽可能的扩展索引，不要新建立索引；（索引太多影响写速度）
索引不会包含有NULL值的列：只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的；
单个多列组合索引和多个单列索引的检索查询效果不同，MySQL查询只使用一个索引，如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引；
联合索引实效情况：

技术分享图片

调优步骤：

查看运行效果，是否真的很慢，主要设置SQL_NO_CACHE；
WHERE条件单表查询，锁定最小返回记录表。这句话的意思是，把查询语句的WHERE都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高；
EXPLAIN查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）；
ORDER BY LIMIT 形式的SQL语句，让排序的表优先查；
了解业务的使用场景；
加索引时，参照建立索引的几大原则；
观察结果，不符合预期，则重新从1开始分析；

进阶

磁盘IO与预读

数据结构选择：b+树、b树、红黑树对比

InnoDB/MyISAM 索引对比

Explain详解

参考

《高性能MySQL》第3版

https://blog.csdn.net/mysteryhaohao/article/details/51719871

https://zhuanlan.zhihu.com/p/29118331

https://www.infoq.cn/article/OJKWYykjoyc2YGB0Sj2c

https://tech.meituan.com/2014/06/30/mysql-index.html

https://segmentfault.com/a/1190000021464570

原文：https://www.cnblogs.com/GO-NO-1/p/13283014.html

内容总结

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

内容备注

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

内容手机端

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

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

来源：【匿名】

【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL 索引优化指南】教程文章相关的互联网学习教程文章

MySQL索引原理b+ 树，记住这棵树！索引所有优化都围绕这棵树展开（hash索引除外）优缺点优点索引大大减小了服务器需要扫描的数据量索引可以帮助服务器避免排序和临时表索引可以将随机IO变成顺序IO索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但在早期的MySQL版本中，InnoDB直到事务提交时才会解锁。对不需要的元组的加锁，会增...

理解MySQL——索引与优化【图】

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

MySQL 查询索引失效及如何进行索引优化【代码】【图】

本文为博主原创，未经允许不得转载：我们都知道创建索引的目的是快速从整体集合中选择性地读取满足条件的一部分集合。mysql中一张表是可以支持多个索引的。但是，你写sql语句的时候，并没有主动指定使用哪个索引。不知道你有没有碰到过这种情况，一条创建了索引的sql语句在查询过程中却没有使用索引，或是一条本来可以执行的很快的语句，却由于mysql选错了索引，而导致查询速度变得很慢？充分优化和利用索引能够大大提高数据的查询...

Mysql索引优化【代码】

如何选择合适的列建立索引1.在where从句，group by从句，order by从句，on从句中出现的列2.索引字段越小越好3.离散度大的列放到联合索引的前面1select * from payment where staff_id = 2 and customer_id = 584;由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)，而不是使用index(staff_id,ustomer_id)。如何判断离散度:123456Mysql> select count(distinct customer_id),count(distinct staff_id) from pa...

理解MySQL——索引与优化【图】

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

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 / MySQL 索引优化指南