Mysql索引

最新推荐文章于 2024-11-08 22:54:50 发布
最新推荐文章于 2024-11-08 22:54:50 发布
阅读量489 收藏
点赞数
文章标签： mysql 索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/n_a_n/article/details/105238979
版权

文章目录

为什么使用索引速度更快

B+树查找时采用多分查找，速度更快。

底层索引结构

B+树索引

B+树是一个多叉搜索树，数据都存在叶子节点的data域中，非叶子节点中没有数据，只有对应的key的值，这样可以保证每个数据查询的平均效率是一样的。并且叶子节点之间用链表相连接，使得获得全表数据更容易。所有叶子节点都在一层。

B树

B树也是一个多叉搜索树，但是数据存放在节点中，所有叶子节点都在一层。

使用B+树的原因：

  • 是因为B+树查询效率更稳定（数据都在叶子节点）。
  • B+树支持预读取。
  • B+树扫库更方便，叶子节点相连。
  • 范围查找更高效。

不用二叉树原因：

  • 二叉树高度较高，因为查询速度和高度相关，所以二叉树速度更慢。

Hash索引

通过建立对应的hash表结构建立索引。
能以O(1)的速度进行查找，但是失去了有序性，且不支持范围查找。

概念索引

聚簇索引

聚簇索引是指在索引中保存了数据，从而避免了读取磁盘，提高了效率。
在聚簇索引的叶子节点的data域中记录着完整的数据记录（行记录）。一般默认为主键建立聚簇索引。
有序的。

非聚簇索引（辅助索引）

Innodb
innodb的辅助索引的叶子节点中不存储数据，而是保存的指向主键的位置的指针（这里会有一次IO，通过指针找到对应的主键），然后通过主键，使用聚簇索引，找到对应的数据。
MyISAM
MyISAM存储时，数据和索引是分开存储的，数据存储在一个地方，索引存储在一个地方，所以它的叶子节点存储的是指向data的指针，直接找到数据。（MyISAM的聚簇索引和非聚簇索引没什么差别）。
在这里插入图片描述

应用索引

主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引（一般对应聚簇索引）

唯一索引

MySQL数据库索引列的值必须唯一，但允许有空值。

全文索引

使用MyISAM支持

普通索引

基本的索引，它没有任何限制。

联合索引

mysql> CREATE TABLE `t_mobilesms_11` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id，创建任务时的userid',
    `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
    `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
    `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
    `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
    `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
    `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
    `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
    `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
    `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
    `createTime` datetime DEFAULT NULL COMMENT '创建时间',
    `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
    PRIMARY KEY (`id`),
    KEY `联合索引` (`userId`,`mobile`,`billMonth`)
    ) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情';
Query OK, 0 rows affected (0.43 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222';
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key      | key_len | ref   | rows | Extra                 |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t_mobilesms_11 | ref  | 联合索引      | 联合索引 | 767     | const |    1 | Using index condition |
+----+-------------+----------------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.07 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972'
    -> ;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_mobilesms_11 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE billMonth='2018-04'
    -> ;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_mobilesms_11 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid='2222' AND mobile='13281899972'
    -> ;
+----+-------------+----------------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+----------------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t_mobilesms_11 | ref  | 联合索引      | 联合索引 | 841     | const,const |    1 | Using index condition |
+----+-------------+----------------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  mobile='13281899972' AND userid='2222'
    -> ;
+----+-------------+----------------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table          | type | possible_keys | key      | key_len | ref         | rows | Extra                 |
+----+-------------+----------------+------+---------------+----------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | t_mobilesms_11 | ref  | 联合索引      | 联合索引 | 841     | const,const |    1 | Using index condition |
+----+-------------+----------------+------+---------------+----------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
    -> ;
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_mobilesms_11 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid>'2222' AND mobile like '%123';
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table          | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_mobilesms_11 | range | 联合索引      | 联合索引 | 767     | NULL |    1 | Using index condition |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE userid>'2222' AND mobile like '123%';
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table          | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_mobilesms_11 | range | 联合索引      | 联合索引 | 767     | NULL |    1 | Using index condition |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
1 row in set

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE 
mobile like '123%';
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_mobilesms_11 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+----------------+------+---------------+------+---------+------+------+-------------+
1 row in set

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile like '123%'  AND userid>'2222';
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table          | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t_mobilesms_11 | range | 联合索引      | 联合索引 | 767     | NULL |    1 | Using index condition |
+----+-------------+----------------+-------+---------------+----------+---------+------+------+-----------------------+

三个单列索引

mysql> CREATE TABLE `t_mobilesms_11` (
    ->   `id` bigint(20) NOT NULL AUTO_INCREMENT,
    ->   `userId` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '用户id，创建任务时的userid',
    ->   `mobile` varchar(24) NOT NULL DEFAULT '' COMMENT '手机号码',
    ->   `billMonth` varchar(32) DEFAULT NULL COMMENT '账单月',
    ->   `time` varchar(32) DEFAULT NULL COMMENT '收/发短信时间',
    ->   `peerNumber` varchar(64) NOT NULL COMMENT '对方号码',
    ->   `location` varchar(64) DEFAULT NULL COMMENT '通信地(自己的)',
    ->   `sendType` varchar(16) DEFAULT NULL COMMENT 'SEND-发送; RECEIVE-收取',
    ->   `msgType` varchar(8) DEFAULT NULL COMMENT 'SMS-短信; MSS-彩信',
    ->   `serviceName` varchar(256) DEFAULT NULL COMMENT '业务名称. e.g. 点对点(网内)',
    ->   `fee` int(11) DEFAULT NULL COMMENT '通信费(单位分)',
    ->   `createTime` datetime DEFAULT NULL COMMENT '创建时间',
    ->   `lastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
    ->   PRIMARY KEY (`id`),
    ->   INDEX id_index(`userId`),
    ->   INDEX mobile_index(`mobile`),
    ->   INDEX billMonth_index(`billMonth`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=71185 DEFAULT CHARSET=utf8 COMMENT='手机短信详情'
    -> ;
Query OK, 0 rows affected (0.32 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'
    -> ;
+----+-------------+----------------+------+---------------------------------------+----------+---------+-------+------+------------------------------------+
| id | select_type | table          | type | possible_keys                         | key      | key_len | ref   | rows | Extra                              |
+----+-------------+----------------+------+---------------------------------------+----------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t_mobilesms_11 | ref  | id_index,mobile_index,billMonth_index | id_index | 767     | const |    1 | Using index condition; Using where |
+----+-------------+----------------+------+---------------------------------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE mobile='13281899972' AND billMonth='2018-04'
    -> ;
+----+-------------+----------------+------+------------------------------+--------------+---------+-------+------+------------------------------------+
| id | select_type | table          | type | possible_keys                | key          | key_len | ref   | rows | Extra                              |
+----+-------------+----------------+------+------------------------------+--------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | t_mobilesms_11 | ref  | mobile_index,billMonth_index | mobile_index | 74      | const |    1 | Using index condition; Using where |
+----+-------------+----------------+------+------------------------------+--------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM `t_mobilesms_11` WHERE  userid='2222' OR mobile='13281899972'
    -> ;
+----+-------------+----------------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table          | type | possible_keys         | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------------+------+-----------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_mobilesms_11 | ALL  | id_index,mobile_index | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+----------------+------+-----------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

覆盖索引

索引失效情况

  1. 使用OR语句的时候会发生索引失效。
    原因：对于or左右的两个字段，如果第一个加了索引，第二个没加，那么查找的顺序为（索引+全表+合并），那么既然要全表扫描了，何不直接一次全表扫描得到需要的数据。
    但如果or的左右列都加了索引，那么索引还是可能走的。
  2. 使用like通配符科恩那个会导致索引失效
    使用like %123此种情况时，会导致索引失效
    但是使用 like 123%这种情况的时候，不会导致索引失效。
  3. 联合索引查序顺序和定义顺序不同会失效
    比如一个索引 idx_name_age(name,age)
    那么where name=“…” and age="…“有效；
    where name=“…” 有效
    where age=”…" 无效
  4. 使用mysql的内置函数会是索引无效
  5. 对索引列加运算（=，+，-，/）
  6. 对索引列使用in not in时会失效
  7. 对索引列使用！=或<>时会失效
  8. 对索引列使用is null或is not null时会失效
  9. 对索引进行表达式操作时会失效
    10.mysql估计使用全表扫描要比使用索引快,则不使用索引
N_a_n
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
分享几道关于MySQL索引的重点面试题
01-19
但MySQL 索引你真的懂吗？这几道题带你了解索引的几个重要知识点 1. 什么是最左前缀原则？ 以下回答全部是基于MySQL的InnoDB引擎 例如对于下面这一张表 如果我们按照 name 字段来建立索引的话，采用B+树的结构，...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
mysql 索引
liulanba的博客
10-25 1万+
在 MySQL 中创建索引可以大大提高查询效率，加快数据检索速度，他的索引包含 普通索引和唯一索引，以及主键索引，联合索引。
MySQL 索引
weixin_68840588的博客
06-17 4679
介绍MySQL索引的相关知识点
Mysql 索引
Impassive_y的博客
04-26 1万+
图片来源网络，侵删 索引 Mysql 的索引类型有很多种，Hash索引，B树索引，B+树索引和全文索引。Mysql有多种存储引擎，每个存储引擎对索引的支持可能会不同。 What Mysql 索引是能改善数据库表随机访问速度的一种数据结构。可以通过指定一个或者多个指定列的方式创建索引。 所以索引是一种数据结构。 Why 当数据量达到一定程度的时候，如果根据顺序扫描全部的数据，会出现数据查询效果低，查询时间长，导致用户体验不佳。为了提高数据的访问的效率，引入了索引。 How 创建索引的时候，需要指定至少一列数据
MySQL索引
stdio_54456153的博客
10-30 134
本文参考自聚集索引与非聚集索引的总结 以及 姜承尧的《MySQL技术内幕–InnoDB存存储引擎》 MySQL 索引详解 在开始介绍前，我们先整理一下 MySQL 的索引： 聚集索引、非聚集索引（普通索引，组合索引，唯一索引）、倒排索引（用于全文检索，也称全文索引）、哈希索引 其中，聚集索引和非聚集索引的存储结构都是 B+ 树结构，全文检索的倒排索引则主要是一张辅助表。 需要注意的是：B+ 树...
大厂基本功 | MySQL 三大日志 ( binlog、redo log 和 undo log ) 的作用？
langjiaohjiopji的博客
11-06 1126
MySQL日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属二进制日志binlog（归档日志）和事务日志redo log（重做日志）和undo log（回滚日志）。今天就来聊聊redo log（重做日志）、binlog（归档日志）、两阶段提交、undo log（回滚日志）。MySQL InnoDB 引擎使用redo log(重做日志)保证事务的持久性，使用undo log(回滚日志)来保证事务的原子性。MySQL数据库的数据备份、主备、主主、主从都离不开。
LINUX下的Mysql：Mysql基础
W2155的博客
11-05 372
数据库其实是一个网络服务，我下载好一个mysql 是分为两部分的，mysql客户端和mysqld服务端，只不过我们学习的时候，客户端和服务端是在同一台机器上的。使用netstat -nltp命令查看mysqld的网络端口号。通过mysql客户端 连接服务器上的mysqld，就可以修改服务器 内存中或磁盘中的数据。我在口语中说的数据库一般是指，存储在内存和磁盘中的特定结构的数据。
MySQL库操作
zgwnb666的博客
11-08 666
MySQL库操作
使用Docker-compose安装mysql5.7
州仔
11-08 89
1.首先选择一个目录用来存放docker-compse文件以及mysql的数据（例如logs、conf）docker发现没有mysql5.7这个镜像会去自动下载，执行完以后MySQL启动成功。2.填写docker-compse.yml内容。宿主机需要开放3306端口。
[面试]Mysql的锁机制
ouyfg的博客
11-08 394
MySQL中的锁主要用于控制多个事务对数据库资源的并发访问，以确保数据的一致性和完整性。锁机制是数据库并发控制的核心，通过锁定数据库中的资源来防止并发操作引发的数据冲突。行锁允许多个事务在同一个表中并发执行不同行上的操作，而不会互相阻塞，因此具有较高的并发性。但相比于表锁，行锁需要更多的资源来维护锁的状态，开销更大。表锁是一种较粗粒度的锁机制，会锁定整个表，而不是某个具体的行或记录。综上，MySQL的锁机制是数据库并发控制的核心。除了表级锁和行级锁，MySQL还提供了其他类型的锁来支持不同的并发控制需求。
MySQL 基础
jeffery 的博客
11-08 293
循序渐进理解 MySQ L的基础知识，包含 SQL、约束、函数、多表查询、事务等
MySQL 中有哪几种锁？
error_log7的博客
11-08 296
在 MySQL 中，锁机制用于控制并发访问，以保证数据的一致性和完整性。
2.索引：深入解析 B+ 树：原理、MySQL 应用及与其他数据结构的对比
最新发布
m0_72560900的博客
11-08 482
MySQL 中 B+ 树结构的设计极具针对性，其高扇出、顺序访问和 I/O 优化等特性使其在数据库索引结构中占据主导地位。高效范围查询：B+ 树支持顺序遍历，适合执行范围查询和批量排序。频繁读写操作：通过自动平衡和页存储机制，B+ 树能够在高频查询和插入操作中保持良好的性能。事务支持：B+ 树在叶子节点中支持 MVCC 机制，使其能有效应对高并发事务环境。通过深入理解 MySQL 的 B+ 树索引结构，我们可以在实际应用中更好地设计和优化数据库，提高查询性能和存储效率。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值