索引优化分析

最新推荐文章于 2022-08-07 02:43:08 发布
最新推荐文章于 2022-08-07 02:43:08 发布
阅读量98 收藏
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_39079631/article/details/116134742
版权

MySQL学习笔记

索引优化分析

SQL性能下降原因

  • 执行时间长、等待时间长
  • 四个原因：查询语句繁琐，索引失效，关联查询太多join（设计缺陷或或不得已的要求），服务器调优及各个参数设置（缓冲、线程数等）

Join查询

  • 机读指令顺序：
FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT
DESTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>
  • 七种join理论：假设表A与表B：
  1. C=A∩B
select * from tableA A INNER join TableB B on A.Key=B.Key;
  1. C=A∪(A∩B)=A
select * from TableA A LEFT join TableB B on A.Key=B.Key;
  1. C=B∪(A∩B)=B
select * from TableA A RIGHT join TableB B on A.Key=B.Key;
  1. C=A∩(A∩B)
select * from TableA A LEFT join TableB B on A.Key=B.Key where B.Key is NULL;
  1. C=B∩(A∩B)
select * from TableA A RIGHT join TableB B no A.Key=B.Key where B.Key is NULL;
  1. C=A∪B
select * from TableA A FULL OUTER join TableB B on A.Key=B.Key;
  1. C=(A∩(A∩B))∪(B∩(A∩B)) = C(A∩B)
select * from TableA A FULL OUTER join TableB B on A.Key=B.Key where A.Key is NULL or B.Key is NULL;
  • 下面进行实战练习
create table tbl_dept(
id int(11) not null auto_increment,
deptName varchar(30) default null,
locAdd varchar(40) default null,
primary key(id)
)engine=INNODB auto_increment=1 default charset=utf8;

CREATE TABLE tbl_emp(
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY(id),
KEY fk_dept_id(deptId)
#CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES tbl_dept(id) 
)ENGINE=INNODB AUTO_increment=1 DEFAULT CHARSET=utf8;

insert into tbl_dept(deptName,locAdd) values('RD',11);
insert into tbl_dept(deptName,locAdd) values('HR',12);
insert into tbl_dept(deptName,locAdd) values('MK',13);
insert into tbl_dept(deptName,locAdd) values('MIS',14);
insert into tbl_dept(deptName,locAdd) values('FD',15);

insert into tbl_emp(name,deptId) values('z3',1);
insert into tbl_emp(name,deptId) values('z4',1);
insert into tbl_emp(name,deptId) values('z5',1);

insert into tbl_emp(name,deptId) values('w5',2);
insert into tbl_emp(name,deptId) values('w6',2);

insert into tbl_emp(name,deptId) values('s7',3);

insert into tbl_emp(name,deptId) values('s8',4);

insert into tbl_emp(name,deptId) values('s9',51);

  • 我们使用select * from tbl_emp可以获得八行，使用select * from tbl_dept可以获得五行，使用select * from tbl_emp,tbl_dept有40行。

  • 然后分别验证七种join。例如mysql> select * from tbl_emp a inner join tbl_dept b on a.deptId = b.id;

  • 如果使用left等，以left为例，如果左表满足、右表不满足，则右边的部分补null。

mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
  • 第四种，A独有的部分：
mysql> select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null;
+----+------+--------+------+----------+--------+
| id | name | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
  • 第六种的mysql语句会出现问题，事实上mysql不支持这种语法格式，使用union关键字：合并并去重。
mysql> 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;
+------+------+--------+------+----------+--------+
| id   | name | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)

索引

  • 索引是帮助MySQL高效获取数据的数据结构。
  • 有两大基本功能：排序和查找。可以简单理解为：排好序的快速查找数据结构。
  • 数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
  • 我们平常说的索引，如果没有特别指明，都是指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));

# 删除
drop index [indexName] ON mytable;

# 查看
show index from table_name;
  • mysql索引结构：BTree索引、hash索引、full-text全文索引、R-Tree索引。主要研究BTree索引。
  • 适合创建索引的情况：
    • 主键自动建立唯一索引
    • 频繁作为查询条件的字段应该创建索引
    • 查询中与其他表关联的字段，外键关系建立索引
    • 频繁更新的字段不适合创建索引
    • where条件里用不到的字段不创建索引。
    • 单键/组合索引的选择问题。高并发下倾向于创建组合索引
    • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
    • 查询中统计或者分组字段
  • 不要创建索引的情况：
    • 表记录太少（理论上三百万条数据性能会开始下降）
    • 经常增删改的表
    • 数据重复且分布平均的表字段，因此应该只为最经常查询的和最经常排序的数据列建立索引。**索引的选择性是指索引列中不同值的数目与表中记录数的比。**如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99，一个索引的选择性越接近1，这个索引的效率就越高。

性能分析

  • MySQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的不一定是DBA认为的最优的，所以这部分最耗费时间）
  • 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，去别处是select并转发给Mysql Query Optimizer时，它会首先对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
  • MySQL常见瓶颈：
    • CPU：CPU在饱和时候一般发生在数据装入内存或从磁盘上读取数据的时候。
    • IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候
    • 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态。
explain
  • 是什么：查看执行计划。使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL如何处理SQL语句。分析查询语句或是表结构的性能瓶颈。
  • 能干啥：
    • 表的读取顺序
    • 数据读取操作的操作类型
    • 哪些索引可以使用
    • 哪些索引被实际使用
    • 表之间的引用
    • 每张表有多少行被优化器查询
  • 怎么用：exlpain+SQL语句，执行计划包含的信息。
mysql> explain select * from  tbl_emp;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tbl_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

  • 表头各字段解释：

    • id：select查询到序列号，包含一组数字，表示查询中执行的select字句或操作表的顺序。有三种情况：id相同执行顺序由上到下；id不同：子查询id值越大越先被执行，而后执行主查询；id相同不同同时存在：子查询里，id越大越先被执行，第n个id被加载的叫做衍生，derivedN。id越大越先执行，id相同从上往下顺序执行。
    • select_type：有六种：simple,primary,subquery,derived,union,union result。查询的类型。
      • simple：简单的select查询，查询中不包含子查询或者union
      • primary：查询中若包含任何子查询，最外层的就是primary
      • subquery：在select或where列表中包含子查询
      • derived：在from列表中包含的子查询被标记为derived，MySQL会递归执行这些子查询，把结果放在临时表里。
      • union：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为dericed。
      • union result：从union表获取结果的select
    • table：显示这一行数据是关于哪张表的
    • type：显示查询使用了何种类型，从最好到最差依次是system>const>eq_ref>ref>range>index>ALL。一般来说得保证查询至少达到range级别，最好能达到ref。
      • system：表只有一行记录（等与系统表），这是const类型的特例，平常不会出现。
      • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
      • eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
      • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
      • range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好。
      • index：只遍历索引树，通常比全表扫描ALL快，因为索引文件通常比数据文件要小。
      • ALL：全表扫描 lj。

  • possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

  • key：实际使用的索引，如果为null则表示没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中（也就是possible_keys是null，但是key仍然有值），select查询字段和个数刚好吻合，从索引找到答案。

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

  • ref：显示索引的哪一列被使用了，如果可能的话是一个常数。哪些列或常量被用于查找索引的值。

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

索引优化

案例一：单表优化
mysql> create table if not exists `article`(
    -> `id` int(10) unsigned not null primary key auto_increment,
    -> `author_id` int(10) unsigned not null,
    -> `category_id` int(10) unsigned not null,
    -> `views` int(10) unsigned not null,
    -> `comments` int(10) unsigned not null,
    -> `title` varbinary(255) not null,
    -> `content` text not null);

mysql> insert into `article` ( `author_id`,`category_id`,`views`,`comments`,`title`,`content`) values (1,1,1,1,'1','1'),
    -> (2,2,2,2,'2','2'),
    -> (1,1,3,3,'3','3');
Query OK, 3 rows affected (0.00 sec)
  • 查询category_id为1且comments大于1的情况下，views最多的article_id。
mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views DESC limit 1;
+----+-----------+
| id | author_id |
+----+-----------+
|  3 |         1 |
+----+-----------+
1 row in set (0.00 sec)

mysql> explain select id,author_id from article where caregory_id = 1 and comments > 1 order by views DESC limit 1;
ERROR 1054 (42S22): Unknown column 'caregory_id' in 'where clause'
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 |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
  • 可以发现，extra里出现Using where; Using filesort，type是ALL，即最坏的情况。优化是必须的。
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         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
  • 尝试创建索引：前面我们使用到了category_id，comments，views三个列，那么就可以先用这三个列尝试创建索引。
mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.01 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         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
  • 创建完复合索引之后，我们可以使用explain来试一试。
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 |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
  • 可以看到，type变为了range，使用的索引为idx_article_ccv，但是，Using filesort任然没有解决。如果我们尝试性的把select语句中的comments > 1改为comments = 1，会发现
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)
  • 我们可以发现，范围性的索引会导致性能下降。当前的索引，效果并不是特别好，需要重新建立索引。
mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index idx_article_cv on article(category_id, views);
Query OK, 0 rows affected (0.01 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         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_cv |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_cv |            2 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows 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       | ref  | idx_article_cv | idx_article_cv | 4       | const |    2 |    33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 检索加排序，同时用到索引，结果非常理想。
案例二：双表优化
mysql> create table if not exists `class`(
    -> `id` int(10) unsigned not null auto_increment,
    -> `card` int(10) unsigned not null,
    -> primary key(`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> create table if not exists `book`( `bookid` int(10) unsigned not null auto_increment, `card` int(10) unsigned not null, primary key(`bookid`));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into class(card) values(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.00 sec)
...

mysql> insert into book(card) values(FLOOR(1+(RAND()*20)));
Query OK, 1 row affected (0.00 sec)
...

mysql> select * from book inner join class on book.card=class.card;
+--------+------+----+------+
| bookid | card | id | card |
+--------+------+----+------+
|     12 |    8 |  1 |    8 |
|      4 |   12 |  2 |   12 |
|     12 |    8 |  4 |    8 |
|      5 |    9 |  5 |    9 |
|      6 |    9 |  5 |    9 |
|      9 |    2 |  6 |    2 |
|     16 |    2 |  6 |    2 |
|     10 |   20 |  7 |   20 |
|      7 |   18 |  9 |   18 |
|     13 |   18 |  9 |   18 |
|      9 |    2 | 10 |    2 |
|     16 |    2 | 10 |    2 |
|      2 |   15 | 12 |   15 |
|     19 |   13 | 15 |   13 |
|     19 |   13 | 16 |   13 |
|     14 |    7 | 17 |    7 |
|     17 |    7 | 17 |    7 |
|     18 |    7 | 17 |    7 |
|     19 |   13 | 18 |   13 |
+--------+------+----+------+
19 rows in set (0.00 sec)
  • 下面开始explain分析：
mysql> explain select * from class left join book on class.card = book.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 |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
  • type有all。添加索引优化：
mysql> alter table `book` add index Y (`card`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from class left join book on class.card = book.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            |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | db26.class.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
  • 换用另一种索引试一试
mysql> drop index Y on book;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `class` add index Y (`card`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

  • 显然，同样的索引，加在左表和右表是不一样的。这是由左连接特性决定的。left jion 条件用于确定如何从右表搜索行，左边一定都有。

  • 记住左连接建在右表，右连接建在左表，反着建就好了。

案例三：三表优化
mysql> create table if not exists `phone`( `phoneid` int(10) unsigned not null auto_increment, `card` int(10) unsigned not null, primary key (`phoneid`) )engine=INNODB;

Query OK, 0 rows affected (0.01 sec)

mysql> insert into phone(card) values(floor(1+(rand()*20)));
Query OK, 1 row affected (0.00 sec)
...

mysql> select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
+----+------+--------+------+---------+------+
| id | card | bookid | card | phoneid | card |
+----+------+--------+------+---------+------+
|  2 |   12 |      4 |   12 |       2 |   12 |
| 15 |   13 |     19 |   13 |       6 |   13 |
| 16 |   13 |     19 |   13 |       6 |   13 |
| 18 |   13 |     19 |   13 |       6 |   13 |
| 12 |   15 |      2 |   15 |       7 |   15 |
| 17 |    7 |     14 |    7 |       9 |    7 |
| 17 |    7 |     17 |    7 |       9 |    7 |
| 17 |    7 |     18 |    7 |       9 |    7 |
|  2 |   12 |      4 |   12 |      10 |   12 |
| 12 |   15 |      2 |   15 |      16 |   15 |
|  9 |   18 |      7 |   18 |      17 |   18 |
|  9 |   18 |     13 |   18 |      17 |   18 |
|  5 |    9 |      5 |    9 |    NULL | NULL |
|  5 |    9 |      6 |    9 |    NULL | NULL |
|  6 |    2 |      9 |    2 |    NULL | NULL |
| 10 |    2 |      9 |    2 |    NULL | NULL |
|  7 |   20 |     10 |   20 |    NULL | NULL |
|  1 |    8 |     12 |    8 |    NULL | NULL |
|  4 |    8 |     12 |    8 |    NULL | NULL |
|  6 |    2 |     16 |    2 |    NULL | NULL |
| 10 |    2 |     16 |    2 |    NULL | NULL |
|  3 |   17 |   NULL | NULL |    NULL | NULL |
|  8 |   16 |   NULL | NULL |    NULL | NULL |
| 11 |   16 |   NULL | NULL |    NULL | NULL |
| 13 |    5 |   NULL | NULL |    NULL | NULL |
| 14 |   17 |   NULL | NULL |    NULL | NULL |
| 19 |    5 |   NULL | NULL |    NULL | NULL |
| 20 |    5 |   NULL | NULL |    NULL | NULL |
+----+------+--------+------+---------+------+

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 |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> alter table `phone` add index z (`card`);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table `book` add index Y (`card`);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

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            |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | db26.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | z             | z    | 4       | db26.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
  • 优化效果已经达到了。索引最好设置在需要经常查询的字段中。
  • **结论：**要尽量减少join语句中的NestedLoop的循环总次数；永远用小结果集驱动大的结果集。优先优化NestedLoop的内层循环。保证Join语句中被驱动表上Join条件字段已经被索引。当无法保证驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置。

索引失效

  • 是我们应该避免的情况。案例建表如下：
mysql> create table staffs( id int primary key auto_increment, `NAME` varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位', add_time timestamp not null default current_timestamp comment '入职时间' )charset utf8 comment '员工记录表';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into staffs(name,age,pos,add_time)values('z3',22,'manager',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> insert into staffs(name,age,pos,add_time)values('July',23,'dev',NOW());
Query OK, 1 row affected (0.01 sec)

mysql> insert into staffs(name,age,pos,add_time)values('2000',23,'dev',NOW());
Query OK, 1 row affected (0.00 sec)

mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | NAME | age | pos     | add_time            |
+----+------+-----+---------+---------------------+
|  1 | z3   |  22 | manager | 2021-04-25 15:59:49 |
|  2 | July |  23 | dev     | 2021-04-25 16:00:06 |
|  3 | 2000 |  23 | dev     | 2021-04-25 16:00:14 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)

mysql> alter table staffs add index idx_staffs_nameAgePos(name,age,pos);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 我们建立了一个索引，使用这个索引：
mysql> explain select * from staffs where name = 'July';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

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

mysql> explain select * from staffs where name = 'July' and age = 25 and pos = 'dev';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
  • 可以发现，key_len从74变到78又变到140。
  • 但是如果搜索的顺序发生改变，我们就会发现，索引不好使了。
mysql> explain select * from staffs where age = 23 and pos = 'dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 = 'dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 left(name,4) = 'July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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='July' and age > 25 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_nameAgePos | idx_staffs_nameAgePos | 78      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
  • 尽量使用覆盖引擎（只访问索引的查询（索引列与查询列一致）），减少select *。Using index 效果好。
mysql> explain select name,age,pos from staffs where name='July' and age=25 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_nameAgePos | idx_staffs_nameAgePos | 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!='July';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | 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)
  • like以通配符开头的mysql索引失效，会变成全表扫描。一边而言使用like时，%应该加在右边。
mysql> explain select * from staffs where name like '%July%';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 'July%';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name like '%July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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> create table `tbl_user`(
    -> `id` int(11) not null auto_increment,
    -> `name` varchar(20) default null,
    -> `age` int(11) default null,
    -> email varchar(20) default null,
    -> primary key(`id`)
    -> )engine=INNODB auto_increment=1 default charset=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('2aa2',222,'a@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tbl_user(name,age,email) values('aa',121,'e@163.com');
Query OK, 1 row affected (0.00 sec)
  • 没建索引之前：
mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,name from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id,name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  • 创建索引：
mysql> create index idx_user_nameAge on tbl_user(name,age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key              | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_user_nameAge | 68      | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
...
  • 可以发现，单独id、name、age索引都不会失效，id,name、name,age、id,name,age也都不会失效，但是，发现*失效了。使用like时，百分号写在右边，如果非要两边百分号，那么就使用覆盖索引。
  • 字符串不加单引号会导致索引失效。varchar类型不能失去单引号。
mysql> select * from staffs where name='2000';
+----+------+-----+-----+---------------------+
| id | NAME | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2021-04-25 16:00:14 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)

mysql> select * from staffs where name=2000;
+----+------+-----+-----+---------------------+
| id | NAME | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2021-04-25 16:00:14 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (0.00 sec)
  • 后面这一条发生了类型转换！索引失效：
mysql> explain select * from staffs where name='2000';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name=2000;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
  • 少用or，用它来连接时也会导致索引失效。
Lanciberrr
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
MySql（十七）--索引优化
csdn_kenneth的博客
10-03 440
  DROP TABLE IF EXISTS `staffs`; CREATE TABLE `staffs` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL, `age` int(11) NOT NULL DEFAULT '0', `pos` varchar(20) NOT NULL, ...
浅谈MySQL索引优化分析
09-09
MySQL索引优化分析是数据库管理员和开发人员必须掌握的关键技能，因为它直接影响到数据库的查询性能和整体效率。在MySQL中，索引是一种特殊的数据结构，用于加速数据检索。当正确地设计和使用时，索引能够显著提高...
mysql 索引失效的几种情况
qq_39412825的博客
01-17 612
范围后索引失效 首先看一条sql ，使用到了category_id,comments ,views三个字段。给这三个字段都加上索引。然后查询： 第一个sql，索引失效。看type字段第一个是range ，第二个是type。为什么呢，因为comments>1使得整个sql语句无法使用索引。 是因为comments的失效，导致views无法匹配。 优化方案：建立category_id,views的联合索引。取消范围查询的索引建立； 左右连接查询索引建立方式 左连接，加右表外键索引；因为是由左连接特性决定
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MYSQL高级篇-----索引优化分析
沉淀、分享、成长，让自己和他人都能有所收获！
08-07 1万+
对于单键索引，尽量选择针对当前query过滤性更好的索引。在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。假设index(a, b, c)where语句索引是否被使用可以，使用到a可以，使用到a，b可以，使用到a，b，c。...............
MySQL索引优化分析
charmsongo的博客
08-07 3385
为什么你写的sql查询慢？为什么你建的索引常失效？通过本章内容，你将学会MySQL性能下降的原因，索引的简介，索引创建的原则，explain命令的使用，以及explain输出字段的意义。助你了解索引，分析索引，使用索引，从而写出更高性能的sql语句。还在等啥子？撸起袖子就是干！ 案例分析 我们先简单了解一下非关系型数据库和关系型数据库的区别。 MongoDB是NoSQL中的一种。NoSQL的全...
MySQL高级---索引优化分析(三、索引简介)
我是太阳
01-21 2万+
1、索引的概念         MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。         在数据之外，数据库系统还维...
Mysql原理及索引优化分析
何忆清风
09-10 8万+
一、Mysql索引数据结构 （https://www.cs.usfca.edu/~galles/visualization/Algorithms.html 数据结构可视化） 1、二叉树 特点：二叉树，右边节点始终大于左边。 缺点：如果插入的数据是有序的，那么就会造成一边的节点一直增长，导致树的节点过深变成一个链表的结构，如果查找时I/O的次数也就变多 2、红黑树（平衡二叉树） 特点：如果右边的节点高度 大于2，那么就会自动将节点进行转换平衡。 缺点：如果数据过多，还是会导致树的高度不可控，查找的效率也会变
MySQL索引优化分析（下）
一颗洛米
07-04 2692
五、索引优化1、索引分析（1）、单表建表CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY auto_increment, author_id INT(10) UNSIGNED NOT NULL COMMENT'作者id', category_id INT(10) UNSIGNED NOT NUL...
mysql 索引优化
GuoWangWang11的博客
07-12 3817
mysql索引优化
MySQL索引优化
给自己一个smile
02-20 5904
目录 一、MySQL索引基础 本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容。 一、MySQL索引基础 首先，我们将从索引基础开始介绍一下什么是索引，分析索引的几种类型，并探讨一下如何创建索引以及索引设计的基本原则。 此部分用于测试索引创建的user表的结构如下： desc user; 1. 什么是索引？ "索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。" ——《高性能M..
MySQL高级知识-查询与索引优化分析
02-25
手写手写SQL顺序机读顺序总结-SQL解析顺序SQL解析SQLJOINs七种JOIN图解实验：练习1、A、...你可以简单理解为“排好序的快速查找结构”一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存
索引优化分析案例.md
06-06
索引优化分析案例.md
工业视觉智能-分割模型基础知识
08-18
根据《阿里云工业视觉智能高级工程师ACP认证免费课程》（https://edu.aliyun.com/course/3128115/lesson/342981389）创建的个人笔记
计算机毕业设计jsp高考志愿选择辅助系统idea论文
08-18
计算机毕业设计jsp高考志愿选择辅助系统idea论文
分享一款特别好看特别美的樱花博客前端页面
08-18
分享一款特别好看特别美的樱花博客前端页面
电脑软件：圆形印章生成.exe
最新发布
08-18
电脑软件：圆形印章生成.exe
安全测试：安全测试策略.zip
08-18
史上最全软件测试技术全套教程，包括： Postman Selenium 单元测试 压力测试 回归测试 安全测试 性能测试 测试工具 集成测试 等流行技术的系列教程
智慧校园解决方案PPT(28页).pptx
08-18
智慧校园的建设目标是通过数据整合、全面共享，实现校园内教学、科研、管理、服务流程的数字化、信息化、智能化和多媒体化，以提高资源利用率和管理效率，确保校园安全。 智慧校园的建设思路包括构建统一支撑平台、建立完善管理体系、大数据辅助决策和建设校园智慧环境。通过云架构的数据中心与智慧的学习、办公环境，实现日常教学活动、资源建设情况、学业水平情况的全面统计和分析，为决策提供辅助。此外，智慧校园还涵盖了多媒体教学、智慧录播、电子图书馆、VR教室等多种教学模式，以及校园网络、智慧班牌、校园广播等教务管理功能，旨在提升教学品质和管理水平。 智慧校园的详细方案设计进一步细化了教学、教务、安防和运维等多个方面的应用。例如，在智慧教学领域，通过多媒体教学、智慧录播、电子图书馆等技术，实现教学资源的共享和教学模式的创新。在智慧教务方面，校园网络、考场监控、智慧班牌等系统为校园管理提供了便捷和高效。智慧安防系统包括视频监控、一键报警、阳光厨房等，确保校园安全。智慧运维则通过综合管理平台、设备管理、能效管理和资产管理，实现校园设施的智能化管理。 智慧校园的优势和价值体现在个性化互动的智慧教学、协同高效的校园管理、无处不在的校园学习、全面感知的校园环境和轻松便捷的校园生活等方面。通过智慧校园的建设，可以促进教育资源的均衡化，提高教育质量和管理效率，同时保障校园安全和提升师生的学习体验。 总之，智慧校园解决方案通过整合现代信息技术，如云计算、大数据、物联网和人工智能，为教育行业带来了革命性的变革。它不仅提高了教育的质量和效率，还为师生创造了一个更加安全、便捷和富有智慧的学习与生活环境。
MySQL索引优化explain分析
08-13
MySQL的`EXPLAIN`语句可以帮助我们分析查询语句的执行计划，从而优化查询性能。下面是一些常见的优化指标和相关的...以上是一些常见的MySQL索引优化方法，具体的优化策略需要根据具体的查询语句和数据情况进行调整。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Lanciberrr

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值