MySQL索引-索引基础，高性能mysql第四版pdf百度网盘

最新推荐文章于 2024-06-02 14:54:06 发布

Android惊奇耶

最新推荐文章于 2024-06-02 14:54:06 发布

阅读量878

点赞数 7

分类专栏： 2024年程序员学习文章标签： mysql pdf android

本文链接：https://blog.csdn.net/m0_61418142/article/details/137610976

版权

2024年程序员学习专栏收录该内容

228 篇文章 2 订阅

订阅专栏

为了形象地对比单列索引和组合索引，为表添加多个字段

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL,

age INT NOT NULL

);

为了进一步榨取 MySQL 的效率，就要考虑建立组合索引。就是将 name, city, age 建到一个索引里

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时，usernname 长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高I INSERT 的更新速度。

如果分别在 usernname，city，age 上建立单列索引，让该表有 3 个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。

虽然此时有了 3 个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面3组组合索引

usernname, city, age
usernname, city
usernname

为什么没有 city，age 这样的组合索引呢？这是因为 MySQL 组合索引 “最左前缀” 的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引。

下面的几个 SQL 就会用到这个组合索引

SELECT FROM mytable WHREE username=“admin” AND city=“郑州”

SELECT FROM mytable WHREE username=“admin”

而下面几个则不会用到

SELECT FROM mytable WHREE age=20 AND city=“郑州”

SELECT FROM mytable WHREE city=“郑州”

最左匹配

当创建 (col1,col2,col3) 联合索引时，相当于创建了

(col) 单列索引
(clo1,clo2) 联合索引
(col1,col2,col3) 联合索引

从表记录的排列顺序和索引的排列顺序是否一致来划分

聚集索引：表记录的排列顺序和索引的排列顺序一致
非聚集索引：表记录的排列顺序和索引的排列顺序不一致

聚集索引

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快。因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。

缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

非聚集索引

索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列。

当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。

索引回表

ref-mysql如何设计索引更高效——非聚集索引

| — | — | — | — |

| 1 | u001 | Lass | 15821929853 |

| 2 | u002 | Peter | 13673019487 |

对于上面的表 users，其主键为 id，下面在 user_name 列上创建一个索引。对于 user_name 的索引 idx_user_name(user_name) 而言，其实等价于 idx_user_name(user_name,id)，MySQL会自动在辅助索引的最后添加上主键 id。 下面创建索引的语句，二者等效。

– 创建user_name列上的索引

mysql> create index idx_user_name on users(user_name);

– 等效于显式添加主键id创建索引

mysql> create index idx_user_name_id on users(user_name,id);

– 对比两个索引的统计数据

mysql> select a.space as tbl_spaceid, a.table_id, a.name as table_name, row_format, space_type, b.index_id , b.name as index_name, n_fields, page_no, b.type as index_type from information_schema.INNODB_TABLES a left join information_schema.INNODB_INDEXES b on a.table_id =b.table_id where a.name = ‘test/users’;

±------------±---------±-----------±-----------±-----------±---------±-----------------±---------±-----

±------------±---------±-----------±-----------±-----------±---------±-----------------±---------±-----

| 518 | 1586 | test/users | Dynamic | Single | 1254 | PRIMARY | 9 | 4 | 3 |

| 518 | 1586 | test/users | Dynamic | Single | 4003 | idx_user_name | 2 | 5 | 0 |

| 518 | 1586 | test/users | Dynamic | Single | 4004 | idx_user_name_id | 2 | 45 | 0 |

mysql> select index_name, last_update, stat_name, stat_value, stat_description from mysql.innodb_index_stats where index_name in (‘idx_user_name’,‘idx_user_name_id’);

±-----------------±--------------------±-------------±-----------±----------------------------------+

±-----------------±--------------------±-------------±-----------±----------------------------------+

对比一下两个索引的结果，n_fields 表示索引中的列数，n_leaf_pages 表示索引中的叶子页数，size 表示索引中的总页数。通过数据比对就可以看到，辅助索引中确实包含了主键 id，也说明了这两个索引时完全一致。

| — | — | — | — |

| idx_user_name | 2 | 1358 | 1572 |

| idx_user_name_id | 2 | 1358 | 1572 |

上面证明了辅助索引包含主键id，如果通过辅助索引列去过滤数据有可能需要回表。

举个例子，根据用户名 user_name 去查找信息。

对于索引 idx_user_name 而言，其实就是一个小表 idx_user_name(user_name, id)，如果只查询索引中的列，只需要扫描索引就能获取到所需数据，是不需要回表的。如下 SQL 语句

– SQL 1

select id, user_name from users where user_name = ‘Laaa’;

– SQL 2

select id from users where user_name = ‘Laaa’;

mysql> explain select id, name from users where name = ‘Laaa’;

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

| 1 | SIMPLE | users | NULL | ref | idx_user_name | idx_user_name | 82 | const | 1 | 100.00 | Using index |

mysql> explain select id from users where name = ‘Laaa’;

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

±—±------------±------±-----------±-----±--------------±--------------±--------±------±-----±------

| 1 | SIMPLE | users | NULL | ref | idx_user_name | idx_user_name | 82 | const | 1 | 100.00 | Using index |

上面的 SQL 1 和 SQL 2 的执行计划中的 Extra=Using index 表示使用覆盖索引扫描，不需要回表。

但是如果需要查询的列，不再索引中的列呢？这个时候就会产生回表。如下SQL语句

select user_id, user_name, phone from users where user_name = ‘Laaa’;

可以看到 select 后面的 user_id，phone 列不在索引 idx_user_name 中，就需要通过主键 id 进行回表查找，MySQL内部分如下两个阶段处理

Section 1： select id from users where user_name = ‘Laaa’ //id = 100101

Section 2: select user_id, user_name, phone from users where id = 100101;

将 Section 2 的操作称为回表，即通过辅助索引中的主键 id 去原表中查找数据。

聚集索引和非聚集索引的区别

聚集索引在叶子节点存储的是表中的数据
非聚集索引在叶子节点存储的是主键和索引列

索引创建规范

什么情况下需要建立索引

一般来说，在 WHERE 和 JOIN 中出现的列需要建立索引，但也不完全如此，因为 MySQL 只对 <，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE 才会使用索引。

例如

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE

m.age=20 AND m.city=‘郑州’;

此时就需要对 city 和 age 建立索引，由于 mytable 表的 userame 也出现在了 JOIN 子句中，也有对它建立索引的必要。

刚才提到只有某些时候的 LIKE 才需建立索引。因为在以通配符 % 和 _ 开头作查询时，MySQL 不会使用索引。 例如下句会使用索引

SELECT FROM mytable WHERE username like ‘admin%’

而下句就不会使用

SELECT FROM mytable WHEREt Name like ‘%admin’

因此，在使用 LIKE 时应注意以上的区别。

索引设计原则

适合索引的列是出现在 where 子句中的列，或者连接子句中指定的列
基数较小的类，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
何时使用聚簇索引或非聚簇索引

| 使用动作描述 | 使用聚簇索引 | 使用非聚簇索引 |

| — | — | — |

| 列经常被分组排序 | ✅ | ✅ |

| 返回某范围内的数据 | ✅ | ❎ |

| 一个或极少不同的值 | ❎ | ❎ |

| 小数目不同的值 | ✅ | ❎ |

| 大数目不同的值 | ❎ | ✅ |

| 频繁更新的列 | ❎ | ✅ |

| 外键列 | ✅ | ✅ |

| 主键列 | ✅ | ✅ |

| 频繁修改索引列 | ❎ | ✅ |

索引失效的场景

违反最左匹配原则

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。如不连续，则匹配不上。

比如，建立索引为 (a,b) 的联合索引，那么只查 where b = 2 则不生效。换句话说，如果建立的索引是 (a,b,c)，也只有 (a)，(a,b)，(a,b,c) 三种查询可以生效。

遇到范围查询（>、<、between、like）会停止匹配

比如，a= 1 and b = 2 and c > 3 and d =4，如果建立 (a,b,c,d) 顺序的索引，d 是用不到索引的。因为 c 字段是一个范围查询，它之后的字段会停止匹配。

在索引列上做任何操作

如计算、函数、（手动或自动）类型转换等操作，会导致索引失效而进行全表扫描。

explain select * from user where left(name,3) = ‘zhangsan’ and age = 20

这里对 name 字段进行了 left 函数操作，导致索引失效。

使用不等于（!= 、<>、NOT IN）

explain select * from user where age != 20;

explain select * from user where age <> 20;

上述操作会导致索引失效。

like 中以通配符开头 `%`

like 中以通配符开头 %，会导致索引失效。

//索引失效

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

最后

即使是面试跳槽，那也是一个学习的过程。只有全面的复习，才能让我们更好的充实自己，武装自己，为自己的面试之路不再坎坷！今天就给大家分享一个Github上全面的Java面试题大全，就是这份面试大全助我拿下大厂Offer，月薪提至30K！

我也是第一时间分享出来给大家，希望可以帮助大家都能去往自己心仪的大厂！为金三银四做准备！
一共有20个知识点专题，分别是：