mysql数据库索引

最新推荐文章于 2025-03-21 18:18:40 发布

原创最新推荐文章于 2025-03-21 18:18:40 发布 · 543 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql索引 #mysql index

数据库专栏收录该内容

18 篇文章

订阅专栏

本文探讨了MySQL数据库在索引使用上的策略。当MySQL认为全表扫描比使用索引更有效率时，它会选择不使用索引。这一决策可能基于对查询效率的评估，可能与索引的树结构和开闭区间有关。高handler_read_rnd_next值指示查询性能低下。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql数据库索引
================================================================
mysql index 最左前缀原则和查询优化器

创建table示例

CREATE TABLE `index_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `index_test` VALUES ('1', '1', 'b1', '3', '4');

sql验证：
explain显示MySQL如何使用索引来处理select语句以及连接表。

EXPLAIN
SELECT * from index_test WHERE a=1 and b='b1' AND c=3; -- key=a_b_c, key_len=268

EXPLAIN
SELECT * from index_test WHERE c=1 and b='b1' AND a=3; -- key=a_b_c, key_len=268

EXPLAIN
SELECT * from index_test WHERE a=1 and b='b1'; -- key=a_b_c, key_len=263

EXPLAIN
SELECT * from index_test WHERE a=1 and c=3; -- key=a_b_c, key_len=5

EXPLAIN
SELECT * from index_test WHERE b='b1' and c=3; -- key=, key_len=

EXPLAIN
SELECT * from index_test WHERE c=3 and b='b1'; -- key=, key_len=

EXPLAIN
SELECT * from index_test WHERE c=3 and a=1; -- key=a_b_c, key_len=5

1.mysql的查询优化器会帮你优化成索引可以识别的形式,如a,b可以应用索引，b,a也是可以应用索引的；

2.最左前缀：顾名思义，就是最左优先，上例中我们创建了a_b_c多列索引，a为最左,
如果使用了a，则会应用索引，可能只应用了a，也可能应用了a,b,如a,c只使用了a；ab则使用了a,b两个，从key_len可以看出
如果没有使用a(最左)，则不会应用索引，如b,c；c,b

3.遇到范围查询(>、<、between、like)
like:

EXPLAIN
SELECT * from index_test WHERE a=1 and b like "%b%"; -- key=a_b_c, key_len=5(只a)
EXPLAIN
SELECT * from index_test WHERE a=1 and b like "b%"; -- key=a_b_c, key_len=263(a,b)
EXPLAIN
SELECT * from index_test WHERE a=1 and b like "%b%" AND c=3; -- key=a_b_c, key_len=5(只a,也没应用c)

-- 如果LIKE的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE比较。
-- like 'partten%';使用索引
-- like '%parrtn%'; 不使用索引
-------------------
=、>、>=

EXPLAIN
SELECT * from index_test WHERE a=1;    -- key=a_b_c, key_len=5
EXPLAIN
SELECT * from index_test WHERE a>1;    -- key=a_b_c, key_len=5
EXPLAIN
SELECT * from index_test WHERE a<1;    -- key=a_b_c, key_len=5
EXPLAIN
SELECT * from index_test WHERE a>=1;   -- key=, key_len=         没使用index??TODO
EXPLAIN
SELECT * from index_test WHERE a<=1;   -- key=a_b_c, key_len=5

EXPLAIN
SELECT * from index_test WHERE a=1 and b="b1" AND c>=3; -- key=a_b_c, key_len=268 使用了index??TODO

-------------------
索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：
1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
2.对于多列索引，不是使用的第一部分，则不会使用索引
3.like查询是以%开头
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引(怎么估计？？)

（上面两个TODO，怎么估计，估计跟树的开闭区间有关？？）

此外，查看索引的使用情况
show status like ‘Handler_read%';
大家可以注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数（？？）

handler_read_rnd_next:这个值越高，说明查询低效

==TODO====================================

EXPLAIN
SELECT * from index_test WHERE a=1 AND b like "b%" AND c=3; -- key=a_b_c, key_len=268
EXPLAIN
SELECT a,b,c from index_test WHERE a=1 AND b like "b%" AND c=3; -- key=a_b_c, key_len=5

EXPLAIN
SELECT * from index_test WHERE a=1 OR b = "b1"; -- key=, key_len=
EXPLAIN
SELECT a, b from index_test WHERE a=1 OR b = "b1"; -- key=a_b_c, key_len=268

======================================

B树、B-树、B+树
生成
平衡（加减数据）