Skip to content

Commit 42b8c3e

Browse files
JstarfishJstarfish
committed
📝mysql
1 parent 3f2ac6a commit 42b8c3e

1 file changed

Lines changed: 89 additions & 14 deletions

File tree

‎docs/data-store/MySQL/MySQL-Index.md‎

Lines changed: 89 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,9 @@
11
# MySQL索引篇——妈妈再也不担心我不会索引了
22

3+
>索引问题，在面试中是肯定会出现的，记一道知乎服务端面试题
4+
>
5+
>“如果有这样一个查询 `select * from table where a=1 group by b order by c;` 如果每个字段都有一个单列索引，索引会生效吗？如果是符合索引，能说下几种情况吗？
6+
37
## 一、回顾索引基础
48

59
- MYSQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构，所以说**索引的本质是：数据结构**
@@ -292,7 +296,7 @@ MyISAM 引擎的索引文件和数据文件是分离的。**MyISAM 引擎索引
292296

293297
![img](https://tva1.sinaimg.cn/large/007S8ZIlly1gewoy2lhr5j320d0u016k.jpg)
294298

295-
在 Innodb 中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。InnoDB 的数据文件可以按照表来切分（只需要开启`innodb_file_per_table)`，切分后存放在`xxx.ibd`中，默认不切分，存放在`xxx.ibdata`中。
299+
在 Innodb 中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。InnoDB 的数据文件可以按照表来切分（只需要开启`innodb_file_per_table)`，切分后存放在`xxx.ibd`中，默认不切分，存放在 `xxx.ibdata`中。
296300

297301
#### 辅助（非主键）索引：
298302

@@ -350,25 +354,27 @@ MyISAM 引擎的索引文件和数据文件是分离的。**MyISAM 引擎索引
350354

351355
#### 独立的列
352356

353-
如果查询中的列不是独立的，MySQL 就不会使用索引。“独立的列”是指索引不能是表达式的一部分，也不能是函数的参数。
357+
**如果查询中的列不是独立的，MySQL 就不会使用索引**。“独立的列”是指索引不能是表达式的一部分，也不能是函数的参数。
354358

355359
比如：
356360

357361
```mysql
358-
EXPLAIN SELECT * from sakila.actor WHERE actor_id=5;
362+
EXPLAIN SELECT * FROM user_info where id = 2;
359363
```
360364

361-
在 sakila.actor 表中，actor_id 是主键，有主键索引，索引 exmplain 出来结果就是：
362-
![这里写图片描述](https://img-blog.csdn.net/20161222212032141?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbGl1eGlhbmc4Nw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
365+
在 user_info 表中，id 是主键，有主键索引，索引 exmplain 出来结果就是：
366+
367+
![](https://static01.imgkr.com/temp/035c42a7c91b48499029dd1f3c5641cb.png)
363368

364369
可见这次查询使用了PRIMARY KEY来优化查询，如果变成这样：
365370

366371
```mysql
367-
EXPLAIN SELECT * from sakila.actor WHERE actor_id + 1=5;
372+
EXPLAIN SELECT * FROM user_info where id + 1 = 2;
368373
```
369374

370375
结果就是：
371-
![这里写图片描述](https://img-blog.csdn.net/20161222212154288?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbGl1eGlhbmc4Nw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
376+
377+
![](https://static01.imgkr.com/temp/4e6c1f2fe25646bb98d799a2d2d2e52c.png)
372378

373379

374380

@@ -394,33 +400,33 @@ ALTER TABLE table_name ADD KEY(column_name(prefix_length));
394400

395401
#### 多列索引（复合索引、联合索引）
396402

397-
在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL 的查询性能。对于下面的查询where条件，这两个单列索引都是不好的选择：
403+
**在多个列上建立独立的单列索引大部分情况下并不能提高 MySQL 的查询性能**。对于下面的查询 where 条件，这两个单列索引都是不好的选择：
398404

399405
```mysql
400406
select film_id, actor_id from table1 where actor_id=1 or film_id=1;
401407
```
402408

403-
MySQL 5.0 版本之前，MySQL会对这个查询使用全表扫描，除非改写成两个查询UNION的方式。
409+
MySQL 5.0 版本之前，MySQL会对这个查询使用全表扫描，除非改写成两个查询 UNION 的方式。
404410

405411
MySQL 5.0 和后续版本引入了一种叫做“**索引合并**”的策略，查询能够同时使用这两个单列索引进行扫描，并将结果合并。这种算法有三个变种：OR 条件的联合（union），AND 条件的相交（intersection），组合前两种情况的联合及相交。索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建得很糟糕：
406412

407413
1. 当出现服务器对多个索引做相交操作时（多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
408414

409-
2. 当出现服务器对多个索引做联合操作时（多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
415+
2. 当出现服务器对多个索引做联合操作时（多个OR条件），通常需要耗费大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
410416

411417
3. 如果在 explain 中看到有索引合并，应该好好检查一下查询和表的结构，看是不是已经是最优的。
412418

413419

414420

415421
组合索引(concatenated index)：由多个列构成的索引，如 `create index idx_emp on emp(col1, col2, col3, ……)`，则我们称 idx_emp 索引为组合索引。
416422

417-
在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1 列为引导列。当我们进行查询时可以使用 ”where col1 = ? ”，也可以使用 ”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。**所以限制条件中包含先导列时，该限制条件才会使用该组合索引。**
423+
在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1 列为**<mark>引导列</mark>**。当我们进行查询时可以使用 ”where col1 = ? ”，也可以使用 ”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ? ”查询就不会使用该索引。**所以限制条件中包含先导列时，该限制条件才会使用该组合索引。**
418424

419425

420426

421427
#### 覆盖索引
422428

423-
**覆盖索引**（Covering Index），或者叫索引覆盖， 也就是平时所说的不需要回表操作
429+
**覆盖索引**（Covering Index），或者叫索引覆盖， 也就是平时所说的**<mark>不需要回表操作</mark>**
424430

425431
- 就是 select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL 可以利用索引返回 select 列表中的字段，而不必根据索引再次读取数据文件，换句话说**查询列要被所建的索引覆盖**。
426432

@@ -436,7 +442,7 @@ MySQL 5.0 和后续版本引入了一种叫做“**索引合并**”的策略，
436442

437443
MySQL 有两种方式可以生成有序的结果，通过排序操作或者按照索引顺序扫描，如果 explain 的 type 列的值为index，则说明 MySQL 使用了索引扫描来做排序（不要和 extra 列的 Using index 搞混了，那个是使用了覆盖索引查询）。扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录，但如果索引不能覆盖查询所需的全部列，那就不得不扫描一条索引记录就回表查询一次对应的整行，这基本上都是随机 IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在IO 密集型的工作负载时。
438444

439-
MySQL 可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的。只有当索引的列顺序和 order by 子句的顺序完全一致，并且所有列的排序方向（倒序或升序，创建索引时可以指定ASC或DESC）都一样时，MySQL 才能使用索引来对结果做排序，如果查询需要关联多张表，则只有当 order by 子句引用的字段全部为第一个表时，才能使用索引做排序，order by 子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则 MySQL 都需要执行排序操作，而无法使用索引排序。
445+
**MySQL 可以使用同一个索引既满足排序，又用于查找行，因此，如果可能，设计索引时应该尽可能地同时满足这两种任务，这样是最好的**。只有当索引的列顺序和 order by 子句的顺序完全一致，并且所有列的排序方向（倒序或升序，创建索引时可以指定ASC或DESC）都一样时，MySQL 才能使用索引来对结果做排序，如果查询需要关联多张表，则只有当 order by 子句引用的字段全部为第一个表时，才能使用索引做排序，order by 子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则 MySQL 都需要执行排序操作，而无法使用索引排序。
440446

441447

442448

@@ -478,6 +484,75 @@ MySQL 允许在相同列上创建多个索引，无论是有意的还是无意
478484

479485
## 四、索引优化
480486

487+
### 导致SQL执行慢的原因
488+
489+
1. 硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。
490+
491+
2. 没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不完整了。所以互联网公司的数据做的是假删除.一是为了做数据分析,二是为了不破坏索引 ）
492+
493+
3. 数据过多（分库分表）
494+
495+
4. 服务器调优及各个参数设置（调整my.cnf）
496+
497+
498+
499+
### [建索引的几大原则](https://tech.meituan.com/2014/06/30/mysql-index.html "MySQL索引原理及慢查询优化")
500+
501+
1. 最左前缀匹配原则，非常重要的原则，MySQL 会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d 的顺序可以任意调整。
502+
503+
2. =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的形式。
504+
505+
3. 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
506+
507+
4. 索引列不能参与计算，保持列“干净”，比如 `from_unixtime(create_time) = ’2014-05-29’` 就不能使用到索引，原因很简单，b+ 树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成 `create_time = unix_timestamp(’2014-05-29’)`。
508+
509+
5. 尽量的扩展索引，不要新建索引。比如表中已经有 a 的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
510+
511+
512+
513+
### [优化要注意的一些事](https://www.cnblogs.com/frankdeng/p/8990181.html "优化要注意的一些事")
514+
515+
1. 索引其实就是一种归类方式，当某一个字段属性都不能归类，建立索引后是没什么效果的，或归类就二种（0和1），且各自都数据对半分，建立索引后的效果也不怎么强。
516+
517+
2. 主键的索引是不一样的，要区别理解。
518+
519+
3. 当时间存储为时间戳保存的可以建立前缀索引。
520+
521+
4. 在什么字段上建立索引，需要根据查询条件而定，不要一上来就建立索引，浪费内存还有可能用不到。
522+
523+
5. 大字段（blob）不要建立索引，查询也不会走索引。
524+
525+
6. 常用建立索引的地方：
526+
- 主键的聚集索引
527+
- 外键索引
528+
- 类别只有0和1就不要建索引了，没有意义，对性能没有提升，还影响写入性能
529+
- 用模糊其实是可以走前缀索引
530+
531+
7. 唯一索引一定要小心使用，它带有唯一约束，由于前期需求不明等情况下，可能造成我们对于唯一列的误判。
532+
533+
8. 由于我们建立索引并想让索引能达到最高性能，这个时候我们应当充分考虑该列是否适合建立索引，可以根据列的区分度来判断，区分度太低的情况下可以不考虑建立索引，区分度越高效率越高。
481534

535+
9. 写入比较频繁的时候，不能开启MySQL的查询缓存，因为在每一次写入的时候不光要写入磁盘还的更新缓存中的数据。
536+
10. 二次SQL查询区别不大的时候，不能按照二次执行的时间来判断优化结果，没准第一次查询后又保存缓存数据，导致第二次查询速度比第二次快，很多时候我们看到的都是假象。
537+
11. Explain 执行计划只能解释SELECT操作。
538+
12. 使用UNION ALL 替换OR多条件查询并集。
539+
13. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
540+
14. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：`select id from t where num is null` 可以在 num上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：`select id from t where num=0`
541+
15. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
542+
16. 应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：`select id from t where num=10 or num=20` 可以这样查询：`select id from t where num=10 union all select id from t where num=20`
543+
17. in 和 not in 也要慎用，否则会导致全表扫描，如：`select id from t where num in(1,2,3)` 对于连续的数值，能用 between 就不要用 in 了：`select id from t where num between 1 and 3`
544+
18. 下面的查询也将导致全表扫描：`select id from t where name like '李%'` 若要提高效率，可以考虑全文检索。
545+
19. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：`select id from t where num=@num` 可以改为强制查询使用索引：`select id from t with(index(索引名)) where num=@num`
546+
20. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：`select id from t where num/2=100` 应改为: `select id from t where num=100*2`
547+
21. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：`select id from t where substring(name,1,3)='abc' `，name 以 abc 开头的 id 应改为: `select id from t where name like 'abc%'`
548+
22. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
549+
23. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
550+
24. 不要写一些没有意义的查询，如需要生成一个空表结构：`select col1,col2 into #t from t where 1=0` 这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样： create table #t(...)
551+
25. 很多时候用 exists 代替 in 是一个好的选择：`select num from a where num in(select num from b)` 用下面的语句替换：` select num from a where exists(select 1 from b where num=a.num)`
552+
26. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
553+
27. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
554+
28. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
555+
29. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
556+
30. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
557+
31. 任何地方都不要使用 `select * from t` ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
482558

483-
> [美团技术-MySQL索引原理及慢查询优化](https://tech.meituan.com/2014/06/30/mysql-index.html)

0 commit comments

Comments
 (0)