MySQL索引的学习和研究

最新推荐文章于 2024-09-02 17:25:38 发布
最新推荐文章于 2024-09-02 17:25:38 发布
阅读量550 收藏
点赞数 1
分类专栏： MySQL 文章标签： mysql 索引 高性能
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/csujiangyu/article/details/51097419
版权

这里所谈论只针对B-Tree类型索引，也是MySQL用的最多最普通的索引。创建索引的时候是按照字面量的顺序创建的，这个要特别注意。在B-Tree类型索引中，索引顺序是至关重要的。

高性能索引

  • 索引要小而美

  • 单列索引

  • 前缀索引

  • 多列索引

  • 选择合适的索引顺序

  • 覆盖索引

  • 使用索引扫描来排序

首先构造1亿条数据

参照前面写的文章造数据

http://blog.csdn.net/csujiangyu/article/details/51096978

索引结构如下


mysql> show index from user;
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY                 |            1 | id          | A         |    92123632 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | idx_last_first_name_age |            1 | last_name   | A         |      363474 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_last_first_name_age |            2 | first_name  | A         |    92123840 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_last_first_name_age |            3 | age         | A         |    87360984 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_phone               |            1 | phone       | A         |    92123840 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | idx_create_time         |            1 | create_time | A         |       88503 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

user表一共有4个索引，包括一个主键id，2个单列索引(phone, create_time), 1个多列索引(last_name+first_name+age)

通过explain来查看使用索引情况

主要关注如下：

  • type：访问类型，性能从差到好为：

    ALL -> index -> range -> ref -> eq_ref -> const,system -> NULL

  • index：使用的索引

可以使用索引的情况

单列索引全值匹配

这是最简单的方式


mysql> explain SELECT * from user where phone = '13901986429';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_phone     | idx_phone | 14      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

从type可以得知访问类型ref，使用索引key为idx_phone。

多列索引全值匹配


mysql> explain SELECT * FROM  user where last_name='last_name_110' and first_name='first_name_115' and sex='F';
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_last_first_name_age | idx_last_first_name_age | 96      | const,const |    1 |    10.00 | Using where |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

前缀索引

如果是单列索引，可以匹配列的最左前缀


mysql> explain SELECT * from user where phone like '139019864%';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | idx_phone     | idx_phone | 14      | NULL |  100 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

如果是多列索引，可以从左开始组合索引


mysql> explain SELECT * FROM  user where last_name='last_name_110' and first_name='first_name_115';
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_last_first_name_age | idx_last_first_name_age | 96      | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

匹配索引值的范围

使用between


mysql> explain select * from user where id between 1 and 10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |   10 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用in


mysql> explain select * from user where id in (1, 2, 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

使用or


mysql> explain select * from user where id=1 or id=10;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

覆盖索引

Extra字段为Using index，可以看出返回数据只需要查索引即可，不需要回表查其他字段数据。


mysql> explain select phone from user where phone='13900123456';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | idx_phone     | idx_phone | 14      | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

不能使用索引的情况

从type为ALL可以看出进行了全表扫描。

没有使用左前缀索引

phone是索引列，格式为’%XXX’不能是使用索引


mysql> explain SELECT * from user where phone like '%39019864%';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9698255 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

没有采用最左组合索引

last_name + first_name + sex联合组成了多列索引，采用最左匹配， 只有last_name，last_name + first_name，last_name + first_name + sex才能使用索引。


mysql> explain SELECT * FROM  user where first_name='first_name_115' and sex='F';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9698255 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

使用函数


mysql> explain SELECT * FROM  user where left(phone, 11) = '13900123456';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9698255 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

用索引字段进行计算


mysql> explain select * from user where id + 1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 92123838 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

or后面没有使用索引


mysql> explain select * from user where id=1 or age=10;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL | 92123838 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引和数据占用空间的查询


-- 查看example数据库整体大小
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema = 'example';

从图可以看出，1亿条数据索引占用了9.69G，数据占用了10G，总共占用了将近20G。索引占用空间几乎和数据大小一样，因此我们需要慎用索引，在必要的情况下才使用，索引要小而美。

这里写图片描述

Ydoing
关注
专栏目录
对mysql索引的研究和学习.docx
06-28
MySQL索引是对数据库表中一列或多列的值进行排序的一种数据结构，它加快了数据检索的速度。MySQL官方定义索引为数据结构，用于高效获取数据，常见的数据结构包括哈希表、二叉树和B+树。在数据库中，由于数据的存储和...
MySQL之索引(入门级讲解）
tq02的博客
07-21 3404
MySQL的索引知识，创建、查询、删除、索引分类以及数据结构的讲解。入门级讲解哦
MySQL-索引学习
weixin_72915403的博客
01-30 278
索引
MySql的索引学习和使用【含代码示例】
一个做过前端开发的产品经理，经历过睿智产品的折磨导致脱发之后，励志要翻身"农奴"把歌唱，一边打入敌人内部一边持续提升自己，为我们广大开发同胞谋福祉，坚决抵制睿智产品折磨我们码农兄弟！
06-30 660
索引如同图书的目录，能够极大地加快数据检索的速度，减少数据库的I/O成本，提升应用程序的响应时间和整体性能。本文将深入探讨MySQL索引的基本概念、不同类型、创建与管理方法，以及如何在实际开发中高效地利用索引，旨在为初学者和有经验的开发者提供一份全面的指南。聚簇索引是InnoDB存储引擎默认使用的索引类型，它将数据行和索引存储在一起，数据行本身是索引的一部分。如果查询字段完全包含在索引中，那么数据库可以直接从索引中获取数据，无需访问表数据，这种查询称为索引覆盖查询，能够显著提高查询效率。
数据库索引失效情况
lonelyhiker
03-29 4031
戳这里，加关注哦～索引在我们使用MySQL数据库时可以极大的提高查询效率，然而，有时候因为使用上的一些瑕疵就会导致索引的失效，无法达到我们使用索引的预期效果，今天介绍几种MySQL中几种常见的索引失效的原因，可以在以后的工作中尽可能避免因索引失效带来的坑。 一、 被索引字段，发生了隐式类型转换MySQL在sql执行过程中，会将sql语句中与字段原类型不匹配的值，进行一个类型转换看个例子说明，我们创建一个user表，并且添加一个主键id索引，两个二级索引age和phoneCREATE TABLE 't_use
MySQL索引学习
liaguaya的博客
03-24 504
一、索引的分类 1、从功能逻辑上分：普通索引、唯一索引、全文索引、主键索引。 2、按物理实现方式分：聚簇索引和非聚簇索引。 3、按作用字段个数分：单列索引和联合索引。 二、索引的创建 1、隐式的方式创建 在声明有主键约束、唯一性约束、外键约束的字段上，会自动添加相关索引。 2、显示的创建索引 a、在创建表的时候添加索引： b、在已经存在的表上创建索引： alter table … add … create … index … on … 三、索引的删除 1、使用alter table alter
MySQL 索引 详解（保姆级教程）
weixin_55772633的博客
08-10 5349
索引是帮助 MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。索引结构描述B+Tree最常见的索引类型，大部分引擎都支持B+树索引（默认）Hash底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询R-Tree(空间索引)空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型（通常使用较少）
mysql索引优化学习讲义以及示例
08-01
MySQL索引优化是提升数据库性能的关键环节，通过合理设计和使用索引，可以显著加快查询速度，减少数据访问的时间。在MySQL中，索引是一种特殊的数据结构，允许数据库快速找到存储在表中的特定记录。下面我们将深入...
mysql学习资源1111
03-27
5. **事务处理**：了解ACID（原子性、一致性、隔离性和持久性）属性，学习如何在MySQL中使用BEGIN、COMMIT、ROLLBACK等语句进行事务管理。 6. **视图**：学习如何创建和使用视图，它能提供一个虚拟的表，简化复杂的...
MySQL学习研究资料.zip
02-22
这份"MySQL学习研究资料.zip"压缩包很可能包含了一系列关于MySQL的学习材料，如教程文档、实战案例、练习题或者代码示例等，旨在帮助用户深入理解和掌握MySQL的核心概念和技术。 在MySQL的学习中，有几个关键知识点...
MySQL学习资料PDF
02-11
总的来说，这些书籍共同构成了一个完整的MySQL学习路径，从基础操作到高级性能优化，再到特定存储引擎的深入研究。无论是初学者还是有经验的开发者，都能从中获益匪浅。学习这些内容不仅可以帮助你掌握数据库管理的...
给开发童鞋的MySQL索引学习漫画
08-17
给开发童鞋的MySQL索引学习漫画给开发童鞋的MySQL索引学习漫画给开发童鞋的MySQL索引学习漫画给开发童鞋的MySQL索引学习漫画给开发童鞋的MySQL索引学习漫画给开发童鞋的MySQL索引学习漫画给开发童鞋的MySQL索引学习漫画
mysql-实用-索引失效和优化原则
07-23
mysql-实用-索引失效和优化原则
Mysql索引学习
weixin_43482965的博客
02-24 518
MySQL索引
MySQL 高级 —— 聚簇索引和非聚簇索引【含代码示例】
一个做过前端开发的产品经理，经历过睿智产品的折磨导致脱发之后，励志要翻身"农奴"把歌唱，一边打入敌人内部一边持续提升自己，为我们广大开发同胞谋福祉，坚决抵制睿智产品折磨我们码农兄弟！
06-29 1087
掌握聚簇索引和非聚簇索引的原理及使用技巧，对于优化MySQL数据库性能至关重要。在实际开发中，应根据数据特性和查询模式，合理设计索引结构，充分利用索引的优势，避免不必要的性能损耗。希望本文能帮助你更好地理解和运用索引，提升数据库操作的效率和响应速度。欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。DTcode7的博客首页。
【MySQL】索引
zzg的学习博客
07-30 1954
本篇主要讲述了MySQL中的索引的相关知识
MySQL索引学习总结
最新发布
weixin_65565393的博客
09-02 894
MySQL索引学习、MySQL索引优化难点和面试题
【mysql】mysql之索引学习
zerotoall的博客
08-26 932
一. 索引 　　 1.什么是索引 　　 2.索引的种类 　　　 1）Btree索引 　　　 2）B+tree索引 　　　 3）B*tree索引 　　 3索引根据算法分类 　　　 1）主键索引（聚集索引） 　　　 2）唯一键索引 　　　 例 　　　 3）普通索引（辅助索引） 　　　 4）全文索引 　　　 5）查看索引 　　　 6）删除索引 　　 4索引根据配置方法分类 　　　 7）联合索引 　　　 8）前缀索引 　 二、explain的使用
MySQL学习总结（索引的概述、索引的创建、索引的查看、索引的删除）
2202_75688394的博客
04-22 2236
学习MySQL的索引的概述、索引的创建、索引的查看、索引的删除
MySQL基础教程：数据库管理系统和SQL应用
在学习 MySQL 之前，需要了解一些基本术语和概念，例如： * 数据库（Database） * 表（Table） * 字段（Field） * 记录（Record） * 主键（Primary Key） * 外键（Foreign Key） * 索引（Index） * 视图（View） *...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值