尚硅谷MySQL数据库高级-02-索引优化分析

已于 2023-12-01 09:24:15 修改
阅读量423 收藏 1
点赞数 1
CC 4.0 BY-SA版权
分类专栏： mysql 文章标签： java mysql
于 2021-08-23 15:11:30 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/admin741admin/article/details/119735823
本文深入探讨了MySQL索引的优化分析，包括性能下降的原因、SQL语句编写、索引失效场景及解决策略。通过实例展示了如何利用索引提升JOIN查询效率，分析了索引的工作原理与优劣，以及如何根据执行计划调整索引策略，特别是在应对复杂查询和排序时的考虑。还介绍了如何合理创建和选择索引类型，如单值、复合、唯一索引等，以平衡查询效率与数据更新成本。

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

转载参考：

  • https://github.com/RingoTangs/LearningNote/blob/master/MySQL/MySQL.md
  • https://blog.csdn.net/qq_21579045/article/details/99702766

索引优化分析

性能下降SQL慢

  • 查询语句写的烂
  • 索引失效
    • 单值索引
    • 复合索引
  • 关联查询太多join（设计缺陷或不得已的需求）
  • 服务器调优及各个参数设置（缓冲、线程数等）

SQL执行顺序

  • 手写
    在这里插入图片描述

  • 机读
    在这里插入图片描述

  • 总结
    在这里插入图片描述

常见通用的Join查询

在这里插入图片描述

SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id;

SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id;


SELECT * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;


SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;

SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;


/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 Union */

SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id
union
SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id;


SELECT * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null
union
SELECT * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.id is null;

在这里插入图片描述

Union和Union All区别:
* Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
* Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

索引简介

是什么

  • MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结果。从而可以获得索引的本质：索引是排好序的快速查找数据结构。
  • 索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a—z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？？？
  • 重点：索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！
  • 除了数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

在这里插入图片描述

  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  • 我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种数据结构的索引之外，还有哈希索引（Hash Index）等。
  • 一般企业数据是伪删除，一方面大数据，一方面是破坏索引

索引的优势和劣势

  • 优势：
    • 查找：类似大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本。
    • 排序：通过索引対数据进行排序，降低数据排序的成本，降低了CPU的消耗。
  • 劣势：
    • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
    • 虽然索引大大提高了查询速度，但是同时会降低表的更新速度，例如对表频繁的进行INSERT、UPDATE和DELETE。因为更新表的时候，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
    • 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引。

索引分类

  • 单值索引：一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但是允许空值。
  • 复合索引：一个索引包含多个字段。

命令语句

  • 创建：

    • create [unique] index indexname on mytable(columnname(length));

    • alter mytable add [unique] index [indexname] on (columnname(length))

    • 如果是char，varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。

  • 删除：drop index [indexname] on mytable;

  • 查看：show index from table_name\G

  • 使用alter命令

/* 1、该语句添加一个主键，这意味着索引值必须是唯一的，并且不能为NULL */
ALTER TABLE tabName ADD PRIMARY KEY(column_list);

/* 2、该语句创建索引的键值必须是唯一的(除了NULL之外，NULL可能会出现多次) */
ALTER TABLE tabName ADD UNIQUE indexName(column_list);

/* 3、该语句创建普通索引，索引值可以出现多次 */
ALTER TABLE tabName ADD INDEX indexName(column_list);

/* 4、该语句指定了索引为FULLTEXT，用于全文检索 */
ALTER TABLE tabName ADD FULLTEXT indexName(column_list);

索引结构

  • BTree索引。

    • 索引原理
      在这里插入图片描述
      在这里插入图片描述

  • Hash索引。

  • Full-text全文索引。

  • R-Tree索引。

哪些情况需要建索引

  • 主键自动建立主键索引（唯一 + 非空）。
  • 频繁作为查询条件的字段应该创建索引。
  • 查询中与其他表关联的字段，外键关系建立索引。
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
  • 查询中统计或者分组字段（group by也和索引有关）。

哪些情况不要建索引

  • 表记录太少
  • 经常增删改的表
    • Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
  • 频繁更新的字段不适合创建索引
    • 因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
  • where条件里用不到的字段不创建索引
  • 单键/组合索引的选择问题。
    • who？（在高并发下倾向创建组合索引）
  • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
    在这里插入图片描述

性能分析

MySQL Query Optimizer

在这里插入图片描述

MySQL常见瓶颈

  • CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  • IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
  • 服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

Explain

是什么（查看执行计划）

SQL的执行计划，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的。

能干嘛

  • id：表的读取顺序。
  • select_type：数据读取操作的操作类型。
  • possible_keys：哪些索引可以使用。
  • key：哪些索引被实际使用。
  • ref：表之间的引用。
  • rows：每张表有多少行被优化器查询。

怎么玩

explain + SQL

在这里插入图片描述

各字段解释

id

  • id：表的读取和加载顺序。

  • 值有以下三种情况：

    • id相同，执行顺序由上至下。
    • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
    • id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。

  • 衍生：DERIVED

在这里插入图片描述

select_type

  • select_type：数据查询的类型，主要是用于区别，普通查询、联合查询、子查询等的复杂查询。

    • SIMPLE：简单的SELECT查询，查询中不包含子查询或者UNION 。
    • PRIMARY：查询中如果包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
    • SUBQUERY：在SELECT或者WHERE子句中包含了子查询。
    • DERIVED：在FROM子句中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表中。
    • UNION：如果第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。
    • UNION RESULT：从UNION表获取结果的SELECT。
table
  • 显示这一行的数据是关于哪些表的。
type

  • 访问类型排列

  • 从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。

  • 一般来说，得保证查询至少达到range级别，最好达到ref。

  • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。

  • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转化为一个常量。

  • eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组合成的一行，查出来只有一条记录。除 了 system 和 const 类型之外, 这是最好的联接类型。

  • ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。

  • range：只检索给定范围的行，一般就是在WHERE语句中出现了BETWEEN、< >、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不用扫描全部索引。

  • index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。

  • ALL：Full Table Scan，没有用到索引，全表扫描。

在这里插入图片描述
在这里插入图片描述

possible_keys
  • 显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key
  • 实际使用的索引。如果为NULL，则没有使用索引。
  • 查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）

在这里插入图片描述

key_len
  • 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

在这里插入图片描述

ref
  • 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

在这里插入图片描述
在这里插入图片描述

rows
  • 根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读取的行数。

在这里插入图片描述

Extra
  • 包含不适合在其他列中显示但十分重要的额外信息。
  • Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。

在这里插入图片描述

  • Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。
    在这里插入图片描述

  • Using index：表示相应的SELECT操作中使用了覆盖索引，避免访问了表的数据行，效率不错！如果同时出现Using where，表示索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

在这里插入图片描述

在这里插入图片描述

  • Using where：表明使用了where过滤。

  • Using join buffer：使用了连接缓存。

  • impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）

  • select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

  • distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。

最左原则，按楼梯走。跳楼梯就不能Using index。

case

c

索引优化

索引单表优化案例

  • 编写SQL语句并查看SQL执行计划。
mysql>  SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  5 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
  • 创建和查看索引idx_article_ccv
mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.04 sec)
  • 查看现在SQL语句的执行计划
    • 我们发现，创建符合索引idx_article_ccv之后，虽然解决了全表扫描的问题，但是Extra居然还是用的Using filesort，为什么？
mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    2 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
  • 因为type是range ，决定尝试把修改非range
    • 推论：当comments > 1的时候order by排序views字段索引就用不上，但是当comments = 1的时候order by排序views字段索引就可以用上！！！所以，范围之后的索引会失效。
mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_ccv | idx_article_ccv | 8       | const,const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 我们现在知道范围之后的索引会失效，原来的索引idx_article_ccv最后一个字段views会失效，那么我们如果删除这个范围字段的索引，创建新的索引idx_article_cv
mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_article_ccv on article (category_id,views);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from  article;
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY         |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | views       | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
  • 再次看一下SQL执行计划
mysql> explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 4       | NULL |    3 |    33.33 | Using index condition; Using where |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

索引两表优化案例

  • 不创建索引的情况下，SQL的执行计划
    • book和class两张表都是没有使用索引，全表扫描，需要进行优化，
mysql> explain select * from book left join class on book.card = class.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
  • 左表(book表)创建索引
    • 由此可见，左连接将索引创建在右表上更合适，右连接将索引创建在左表上更合适。
mysql> create index idx_book_card on book(card);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from book left join class on book.card = class.card;
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | idx_book_card | 4       | NULL |    1 |   100.00 | Using index                                        |
|  1 | SIMPLE      | class | NULL       | ALL   | NULL          | NULL          | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
  • 删除book表的索引，右表(class表)创建索引
mysql> drop index idx_book_card on book;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_class_card  on class(card);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from book left join class on book.card = class.card;
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | class | NULL       | ref  | idx_class_card | idx_class_card | 4       | mysql_gaoji.book.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

索引三表优化案例

  • 不加任何索引，查看SQL执行计划
mysql> explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
  • 根据两表查询优化的经验，左连接需要在右表上添加索引，所以尝试在book表和phone表上添加索引
mysql> CREATE INDEX idx_book_card ON book(card);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX idx_phone_card ON phone(card);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 再次执行SQL的执行计划
mysql> explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                   |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_book_card  | idx_book_card  | 4       | mysql_gaoji.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | idx_phone_card | idx_phone_card | 4       | mysql_gaoji.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

结论

  • 可能减少Join语句中的NestedLoop的循环总次数:“永远用小结果集驱动大的结果集"
  • 优先优化NestedLoop的内层循环。
  • 保证JOIN语句中被驱动表上JOIN条件字段已经被索引。
  • 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer 的设置。

索引失效

  • 全值匹配我最爱
  • 最佳左前缀法则
  • 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
  • 索引中范围条件右边的字段会全部失效
  • 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *
  • MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描
  • is null、is not null也无法使用索引
  • like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）
  • 字符串字段不加单引号索引失效
  • 少用or，用它来连接时会索引失效

全值匹配我最爱

  • 完全按照索引的字段和顺序
mysql> explain select * from staffs where name = "Ringo" and age = 18 and pos = "manager";
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

最佳左前缀法则

实践

  • 复合索引-单个
    • 只有单独带头大哥才能使用复合索引部分
mysql> explain select * from staffs where name = "Ringo";
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

mysql> explain select * from staffs where pos = "manager";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 复合索引-两个
    • 查询和排序字段正好符合索引–> 用到索引
    • 交换顺序–> 依旧使用到了索引
    • 去除中间的条件–> 依旧使用了索引部分（key_len不一致）
    • 去除开头条件–> 没有使用索引了

可以通过key_len来比较使用复合索引的长度

mysql> explain select * from staffs where name = "Ringo" and age ="18";
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

mysql> explain select * from staffs where  age ="18" and name = "Ringo";
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from staffs where name = "Ringo" and pos ="manager";
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 74      | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from staffs where  age ="18" and pos = "manager";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 复合索引-多个
    • 交换顺序–> 依旧使用到了索引
mysql> explain select * from staffs where name = "Ringo" and age=18 and  pos ="manager" ;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where  age=18 and name = "Ringo"  and  pos ="manager" ;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

总结

  • 最佳左前缀法则：如果索引是多字段的复合索引，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段。

  • 口诀：带头大哥不能死，中间兄弟不能断。

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

实践

mysql> explain SELECT * FROM `staffs` WHERE `name` = 'Ringo';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT * FROM `staffs` WHERE left(`name`,5) = 'Ringo';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

总结

  • 口诀：索引列上不进行计算

索引中范围条件右边的字段会全部失效

实践

mysql> explain select * from staffs where name = "Ringo" and age=18 and  pos ="manager" ;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name = "Ringo" and age>18 and  pos ="manager" ;
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

总结

  • 特点：
    • 范围和范围之前正常，范围之后失效
    • 不能用于order by或者group by的梯子
  • 口诀：范围之后失效

尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *

实践

mysql> explain select * from staffs where name = "Ringo" and age=18 and  pos ="manager" ;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name,age,pos from staffs where name = "Ringo" and age=18 and  pos ="manager" ;
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 140     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name = "Ringo" and age>18 ;
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 78      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name,age,pos from staffs where name = "Ringo" and age>18 ;
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 78      | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

关于SELECT 并且范围的字段，低版本(5.4)是ref并不能被索引检索，高版本(包含5.7)是range但能被索引检索

总结

  • 查询尽量不用*，需要啥查啥字段

MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描

实践

mysql> explain select * from staffs where name = "Ringo";
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name != "Ringo";
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_name_age_pos | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.06 sec)

mysql> explain select * from staffs where name <> "Ringo";
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_name_age_pos | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

总结

  • 不等式让索引失效

is null、is not null也无法使用索引

实践

mysql> explain select * from staffs where name is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name is not null;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_name_age_pos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

总结

  • is null或者is not null 让索引失效

like以通配符开头%abc索引失效会变成全表扫描（使用覆盖索引就不会全表扫描了）

实践

  • 只有%只加字段右边，不影响索引
mysql> explain select * from staffs where name like "Ringo%";
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_name_age_pos | idx_staffs_name_age_pos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.04 sec)

mysql> explain select * from staffs where name like "%Ringo";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name like "%Ringo%";
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 如果一定要使用like %字段%，而且还要保证索引不失效，那么使用覆盖索引来编写SQL
mysql> create index idx_user_nameAge on tbl_user(name,age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0


// 可以使用索引
mysql> EXPLAIN SELECT `id` FROM `tbl_user` WHERE `name` LIKE '%aa%';

// 可以使用索引
mysql> EXPLAIN SELECT `name` FROM `tbl_user` WHERE `name` LIKE '%aa%';

// 可以使用索引
mysql> EXPLAIN SELECT `age` FROM `tbl_user` WHERE `name` LIKE '%aa%';

// 可以使用索引
mysql> EXPLAIN SELECT `age`,`name` FROM `tbl_user` WHERE `name` LIKE '%aa%';

// 可以使用索引
mysql> EXPLAIN SELECT `age`,`name`,`id` FROM `tbl_user` WHERE `name` LIKE '%aa%';

// 索引失效
mysql> EXPLAIN SELECT `age`,`name`,`id`,`email` FROM `tbl_user` WHERE `name` LIKE '%aa%';

// 索引失效
mysql> EXPLAIN SELECT * FROM `tbl_user` WHERE `name` LIKE '%aa%';

总结

  • like，%加右边，肯定偶索引
  • 利用覆盖索引解决两边%的优化问题

字符串字段不加单引号索引失效

实践


mysql> EXPLAIN SELECT * FROM `tbl_user` WHERE name = '123';
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl_user | NULL       | ref  | idx_user_nameAge | idx_user_nameAge | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `tbl_user` WHERE name = "123";
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tbl_user | NULL       | ref  | idx_user_nameAge | idx_user_nameAge | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `tbl_user` WHERE name = 123;
+----+-------------+----------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | idx_user_nameAge | NULL | NULL    | NULL |    4 |    25.00 | Using where |
+----+-------------+----------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

总结

  • 数字字符要加引号

少用or，用它来连接时会索引失效

实践


mysql> EXPLAIN SELECT * FROM `tbl_user` WHERE name = "123" and age =  "18";
+----+-------------+----------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys    | key              | key_len | ref         | rows | filtered | Extra |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | tbl_user | NULL       | ref  | idx_user_nameAge | idx_user_nameAge | 68      | const,const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+------------------+------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM `tbl_user` WHERE name = "123" or age =  "18";
+----+-------------+----------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | idx_user_nameAge | NULL | NULL    | NULL |    4 |    43.75 | Using where |
+----+-------------+----------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

总结

  • 尽量少用or

索引相关题目

Where语句索引是否被使用
where a = 3Y，使用到a
where a = 3 and b = 5Y，使用到a，b
where a = 3 and b = 5 and c = 4Y，使用到a，b，c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4N，没有用到a字段
where a = 3 and c = 5使用到a，但是没有用到c，因为b断了
where a = 3 and b > 4 and c = 5使用到a，b，但是没有用到c，因为c在范围之后
where a = 3 and b like ‘kk%’ and c = 4Y，a，b，c都用到
where a = 3 and b like ‘%kk’ and c = 4只用到a
where a = 3 and b like ‘%kk%’ and c = 4只用到a
where a = 3 and b like ‘k%kk%’ and c = 4Y，a，b，c都用到

面试题分析

mysql> show index from test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 |          0 | PRIMARY          |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)


/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';

/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';

/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';

/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';

/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';

/* 
   6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;

/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;

/* 
   8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段 
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;

/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;

/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;

/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND  `c2` = 'a2' ORDER BY c2, c3;

/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND  `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;

/* 
   13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
      因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
      所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;



/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

where内会进行优化，比如把范围条件按照索引顺序放的比较后
order by和group by不会进行顺序优化，不按照索引的顺序就会导致自己的索引失效，并且产生Using filesort甚至 Using temporary
GROUP BY基本上都需要进行排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生。

总结

  • 对于单值索引，尽量选择针对当前query过滤性更好的索引。
  • 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
SQL优化面试题30道 + MySQL面试宝典(尚硅谷)
の博客
06-16 4453
调优
尚硅谷《MySQL高级特性篇》教程发布
m0_58371965的博客
01-13 1884
MySQL底层如何组织设计数据 怎么理解“索引即数据，数据即索引” 海量数据和高并发场景下如何设计主键 索引下推、覆盖索引对优化有什么优势 如何保证主从一致性 MVCC如何解决可重复读下的幻读 Redo Buffer的刷盘策略清楚吗 分布式场景下数据库怎么保持高性能 …… 温柔的男人像海洋，爱在关键时逞强。 MySQL带玩不玩两三年， 还是只见树叶不见森林的半吊子， 跳槽遇阻受挫，大厂面试未遂， 被面试官无情地蹂躏了一百遍…… 真正使我们陷入麻烦的， 并不是那些我们根
尚硅谷mysql高级：索引、优化
08-12
尚硅谷mysql高级：索引、优化，讲的很棒，分享给大家，欢迎来下载。
“尚硅谷”Mysql数据库DML、DDL
weixin_42902126的博客
08-08 369
分页查询 应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。 limit 【offset】,size offset：要显示条目的起始索引(起始索引从0开始) size：要显示的条目个数 #查询11条-25条数据 select * from emp limit 10,15; union联合查询 union：将多条查询语句的结果合并成一个结果。 特点： 1、要求多条查询语句的查询列数是一致的。 2、要求多条查询语句的查询的每一列的类型和顺序最好一致。 3、union默认是去重的，使用union
尚硅谷周阳老师 Mysql 高级优化规则总结
@chenhui的博客
01-21 776
尚硅谷周阳老师Mysql索引优化口诀 周阳，yyds 全值匹配我最爱，最左前缀要遵守； 带头大哥不能死，中间兄弟不能断； 索引列上少计算，范围之后全失效； LIKE百分写最右，覆盖索引不写星； 不等空值还有 or，索引失效要少用； ...
尚硅谷数据库|视图/存储过程与函数/流程控制
weixin_73795426的博客
03-20 780
定义条件就是给错误码命名#举例1：定义“Field_Not_Be_NULL”错误名与MySQL中违反非空约束的错误类型是“ERROR 1048 (23000)”对应。#方式1：使用MySQL_error_code#方式2：使用sqlstate_value#举例2：定义"ERROR 1148(42000)"错误，名称为command_not_allowed。#方法1：捕获sqlstate_value#方法2：捕获mysql_error_value#方法3：先定义条件，再调用。
MySQL入门到高级-康师傅版.zip
08-30
8. **备份与恢复**：学习如何备份和恢复MySQL数据库，包括全量备份、增量备份和热备份策略。 9. **安全性**：了解如何创建和管理用户账户，设置访问权限，以及防止SQL注入攻击。 10. **高级主题**：如分区、联接...
尚硅谷mysql笔记.zip
08-18
这份笔记可能包含了从基础概念到高级特性的全面讲解，包括但不限于SQL语言、数据类型、数据库设计、索引、事务处理、视图、存储过程和触发器等。 1. SQL语言基础：SQL是Structured Query Language的缩写，是用于...
尚硅谷周阳Mysql高级思维导图脑图学习笔记.rar
07-04
【尚硅谷周阳Mysql高级思维导图脑图学习笔记】是针对MySQL数据库系统的一份高级学习资料，包含了丰富的知识体系，旨在帮助学习者深入理解并掌握MySQL的高级特性和优化技巧。这份资源以.mmap格式提供，这是一种专业的...
【数据库】2、索引、调优、explain（尚硅谷笔记）
努力充实，远方可期
04-26 1434
尚硅谷JAVA研究院 版本v1.1 第 1 章 MySQL 简介 1.什么是 Mysql MySQL 是一个关系型数据库管理系统， 由瑞典 MySQL AB 公司开发， 目前属于 Oracle 公司。 Mysql 是开源的， 可以定制的， 采用了 GPL 协议， 你可以修改源码来开发自己的 Mysql 系统。 MySQL 使用标准的 SQL 数据语言形式。 Mysql 可以允许于多个系统上， 并...
尚硅谷MySQL基础数据库表
02-11
尚硅谷MySQL基础视频中的四张表，包含了全部数据，测试过与视频中查询结果一致。
尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇
08-10
MYSQL教程 MYSQL教程MYSQL教程 MYSQL教程尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇。数据库视频教程，全套视频教程。
尚硅谷MySQL高级_笔记
10-05
尚硅谷MySQL高级的视频学习笔记，由本人整理
MySQL高级 第5章索引优化分析
尚硅谷铁杆粉丝的博客
07-02 310
第5章索引优化分析 1. 索引的概念 1.1 是什么 MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就...
MySQL高级特性_06_索引的数据结构_尚硅谷_宋红康
Java全栈开发者
03-12 901
索引（index)是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。你可以简单理解为“排好序的快速查找数据结构“，满足特定查找算法。这些数据结构以某种方式指向数据，这样就可以在这些数据结构的技术上实现高级查找算法。索引是在存储引擎中实现的因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引。总索引长度至少为256字节。有些存储引擎支持更多的索引数和更大的索引长度。
【尚硅谷MySQL入门到高级-宋红康】一、数据库概述
白羊座的橙子
02-26 1556
MySQL从5.7版本直接跳跃发布了8.0版本 ，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验。
尚硅谷-个人-数据库记录基础1
BOWWOB的博客
07-20 149
案例： 1.查询员工中第二个字符为（需要转义）非普通字符_的员工 select last_name from employees where last_name like '_\_%' 2.in相当于三个or +==，所以里面的字符串不能使用%号 select last_name from employees where id In('case1','case2','case3') 3.查询员工中第二个字符为（需要转义）非普通字符_的员工 第二种解法： escape 相当于转义的位置 s
mysql高级篇 | 尚硅谷 | 第10章_索引优化与查询优化
最新发布
m0_74795952的博客
12-10 836
直接通过二级索引对应的数据找到了查询结果，无需回表理解方式一：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。理解方式二：非聚簇复合索引的一种形式，它包括在查询里的SELECT、JOIN和WHERE子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。简单说就是，索引列+主键包含。
尚硅谷-个人-数据库记录基础7
BOWWOB的博客
07-21 183
count查询的是非空值的个数 #与disdinct搭配使用 select count(distinct salary) from userinfo; #sum avg 处理整数类型 #min max count 处理所有类型 count函数详细介绍： 都可以查询出记录总数 select count(salary) from userinfo; select count(*) from userinfo; select count(1) from userinfo; 效率： MY
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值