MySQL索引及优化

最新推荐文章于 2024-07-07 12:29:28 发布
最新推荐文章于 2024-07-07 12:29:28 发布
阅读量478 收藏 2
点赞数
分类专栏： MySQL 文章标签： mysql sql spring python java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/HelloWorld16/article/details/103546799
版权

MySQL索引及优化

摘要

  本文主要介绍MySQL索引的概念、索引的底层数据结构，然后介绍了索引的四种分类，使读者可以理解索引的基本知识；并通过讲解索引的优缺点以及应用场景，以解决索引如何应用；最后通过介绍explain工具，以及详细阐述了SQL中索引的优化方法，以便在实际开发中掌握并优化索引的使用。

  本人还写了MySQL相关博文，有兴趣的研友可以点击如下链接，请各位研友指正并留言。
   MySQL事务与隔离级别
   MySQL的锁及其MVCC
   索引为什么选择B+Tree

1、索引介绍

1.1 索引的底层结构

  索引其实是一种数据结构，它是一种排好序的便于查找的数据结构，用Btree或B+tree实现。
  三层Btree如图所示：

  三层B+tree如图所示：

1.2 索引的分类

1.2.1 单值索引

  单值索引是指一个索引只包含表中一个字段。一张表可以有多个单值索引。
  在表test中name、age字段分别建一个单值索引

mysql> create index idx_name on test(name);
mysql> create index idx_age on test(age);

  查看表test的表信息

mysql> desc test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| name    | varchar(10) | YES  | MUL | NULL    |       |
| age     | int(11)     | YES  | MUL | NULL    |       |
| addr    | varchar(10) | YES  |     | NULL    |       |
| collage | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

  查看表test的索引信息

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | idx_age  |            1 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

  从索引信息中可知，索引idx_age、idx_name 的Seq_in_index项都为1，也就表示了两个索引没有关联关系。

  删除表test的索引

mysql> drop index idx_age on test;        
mysql> drop index idx_name on test;

1.2.2 主键索引

  如果表中设置了主键，Innodb就会自动为主键建立索引。主键索引列的值因为自身就是主键，所以具有值唯一、不允许为NULL。主键索引属于单值索引。
  注意：主键索引是InnoDB存储引擎会自动创建的索引。若有主键，则直接创建主键索引；若没有主键，则选取唯一键来创建；若没有唯一键，则自动添加6位row_id来创建主键索引。同时，主键索引是聚集类索引。
  例如：为表test中的字段id添加主键

mysql>alter table test add primary key (id);

  查看表信息

mysql> desc test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | 0       |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| addr    | varchar(10) | YES  |     | NULL    |       |
| collage | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

  查看表test的索引信息

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

  删除表test的主键

mysql> alter table test drop primary key;

1.2.3 唯一索引

  唯一索引是指索引列的值唯一，但允许为Null，如身份证信息、卡号等。与主键索引的区别就是唯一索引允许为Null。唯一索引属于单值索引。
  为表test的字段id添加唯一性约束

mysql> alter table test add unique (id);

  查看表test的表信息

mysql> desc test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | 0       |       |
| name    | varchar(10) | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| addr    | varchar(10) | YES  |     | NULL    |       |
| collage | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

  查看表test的索引信息

mysql> show index from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | id       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

  删除表test的唯一约束

mysql> alter table test drop key id;

1.2.4 组合索引

  组合索引就是包含了多个字段的索引。
  例如：为表test的字段name, age, addr创建组合索引：

mysql> create index idx_name_age_addr on test(name, age, addr);

  查看表test的表信息

mysql> desc test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | 0       |       |
| name    | varchar(10) | YES  | MUL | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| addr    | varchar(10) | YES  |     | NULL    |       |
| collage | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

  查看表test的索引信息

mysql> show index from test;
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name          | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | idx_name_age_addr |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_name_age_addr |            2 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_name_age_addr |            3 | addr        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

  从索引信息中可以知道字段name、age、addr的Key_name项都标记了组合索引idx_name_age_addr ，同时Seq_in_index 也标记了组合索引中字段的顺序。

2、索引的优缺点

  优点：

  1. 类似字典目录，提高数据检索的效率，从而减少了IO访问次数，使性能得到提升；
  2. 使用索引进行数据排序，可以降低数据排序的开销。

  缺点：

  1. 索引是一种数据结构，需要额外的占用空间；
  2. 索引降低了数据的更新效率，如insert、update、delete操作，因为MySQL除了要维护新的数据外，还要维护索引；
  3. 建表的同时要综合考虑如何建高效的索引。

3、索引的应用场景

  需要建索引的场景：

  1. 主键自动建立主键索引；
  2. 查询条件中频繁出现的字段应该创建索引；
  3. 查询时与其他表关联的字段，外键关系建立索引；
  4. 查询中排序的字段应该建立索引；
  5. 查询中分组的字段应该建立索引。

  不需要建索引的场景

  1. 频繁更新的字段不适合建索引；
  2. where中用不到的字段不适合建索引；
  3. 表中记录少不适合建索引；
  4. 经常增删改的表不适合建索引；
  5. 表中字段重复值太多，不适合建索引。

4、explain分析索引

  explain是mysql提供的一种分析SQL执行情况的工具，所以使用explain可以看出SQL执行时，索引是否用到，是否失效。

mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

  首先介绍explain中各列所代表的意思：

  1. id 代表SQL语句的执行顺序，当有嵌套SQL时，可以根据id知道子SQL语句的执行顺序，id号越大越先执行。
  2. select_type 用于标记查询类型，如普通查询、联合查询、子查询等。
select_type的取值含义
SIMPLE简单查询，不包含子查询或union
PRIMARYSQL包含子查询，最外层SQL标识为PRIMARY
SUBQUERYSQL包含子查询，子查询被标记为SUBQUERY
DERIVED在From列表中包含的子查询被标记为DERIVED
UNION带UNION的SQL语句中，第二条select语句被标记为UNION
UNION RESULT带UNION的SQL语句的执行结果被标记为UNION RESULT
  1. type 代表SQL扫描范围的类型。
type的取值含义
system表中只有一行记录，等于系统表
const通过索引一次就找到了
eq_ref唯一性索引扫描，如主键索引、唯一索引
ref非唯一性索引扫描
range只检索给定范围的行，使用一个索引来选择行
index遍历索引树
all遍历全表
  1. possible_keys 表示预计使用到的索引键；
  2. key 表示实际使用到的索引键；
  3. key_len 索引中使用的字节数，长度越短越好
  4. ref 显示索引的哪一列被使用了
  5. rows 表示找到目标记录所需要读取的行数
  6. extra 额外信息
extra的值含义
Using filesort使用了一个外部索引排序，而不是已有的内部索引排序
Using temporary使用临时表保存中间结果，常见于排序（order by）和分组（group by）
Using index使用了覆盖索引，避免访问表的数据行，效率不错。
如果同时出现了Using where，表明索引被用来执行主键的查找，再进行了回表操作；
如果没有同时出现Using where，表明索引用来读取数据而非查找。
Using where表明使用了where过滤
Using join buffer使用了连接缓存
impossible wherewhere子句的值总是false，不能用来获取任何元素

5、查询语句的索引优化

  建立表test，并填充值

CREATE TABLE `test` (
  `id` int,
  `name` varchar(10),
  `age` int ,
  `addr` varchar(10) ,
  `collage` varchar(10) ,
) 

insert test values (1,'xuebao',19,'aaa111','asd');      
insert test values (2,'tujiu',18,'bbb222','qwer');
insert test values (3,'xiezi',20,'ccc333','hjkl');
insert test values (4,'shanyin',19,'ddd444','uio');
insert test values (5,'haishi',18,'eee555','bmn');

  建组合索引idx_name_age_addr

create index idx_name_age_addr on test;

5.1 全值匹配我最爱，最左前缀要遵守

  1. select * from test;
mysql> explain select * from test;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)。

  1. select * from test where name=‘xuebao’ and age=19 and addr=‘aaa111’;
mysql> explain select * from test where name='xuebao' and age=19 and addr='aaa111';
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 31      | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了三个索引键(ref为三个const)，在索引树中遍历(Using index)。

  1. select * from test where name=‘xuebao’ and age=19;
mysql> explain select * from test where name='xuebao' and age=19;
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 18      | const,const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了两个索引键(ref为两个const)，在索引树中遍历(Using index)。

  1. select * from test where name=‘xuebao’ ;
mysql> explain select * from test where name='xuebao';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了一个索引键(ref为一个const)，在索引树中遍历(Using index)。

  总结：在具有组合索引时，根据组合索引创建的顺序，where语句中包含索引键个数越多，查询效率越高。即全值匹配我最爱，最左前缀要遵守。

5.2 带头大哥不能死，中间兄弟不能断

  1. select * from test where age=19 and addr=‘aaa111’;
mysql> explain select * from test where age=19 and addr='aaa111';                  
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。

  1. select * from test where name=‘xuebao’ and addr=‘aaa111’;
mysql> explain select * from test where name='xuebao' and addr='aaa111';           
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了一个索引键(ref为一个const)，在索引树中遍历(Using index)，但在where语句中却使用了两个索引键，可见最后一个索引键addr失效。

  总结：在使用组合索引时，可以使用部分索引键，但必须按照组合索引创建的顺序，按从左到右的顺序，使用部分索引键，如该例中组合索引为idx_name_age_addr，顺序为name、age、addr，所以使用部分组合索引键时，首先满足name带头大哥的存在，否则索引失效；然后选择使用name或name+age，最好不要使用name+addr，否则只有带头大哥name索引键有效。即带头大哥不能死，中间兄弟不能断。

5.3 索引列上少计算，范围之后全失效

  1. select * from test where left(name,3)=‘xue’ and age=19 and addr=‘aaa111’;
mysql> explain select * from test where left(name,3)='xue' and age=19 and addr='aaa111';                      
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。

  1. select * from test where name=‘name’ and ABS(age)=18 and addr=‘aaa111’;
mysql> explain select * from test where name='name' and ABS(age)=18 and addr='aaa111'; 
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了一个索引键(ref为一个const)，在索引树中遍历(Using index)，索引键age用于索引扫描，索引键addr失效。

3.select * from test where name=‘name’ and age>18 and addr=‘aaa111’;

mysql> explain select * from test where name='name' and age>18 and addr='aaa111';                
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_name_age_addr | idx_name_age_addr | 18      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+

  从上表可知，该SQL使用到了索引(key为idx_name_age_addr)，在给定范围类查询(type为range)，在索引树中遍历(Using index)，这种情况下只有索引键name被使用，addr索引键失效，而age索引键用于索引扫描。

  1. select * from test where name=‘xuebao’ and age=19 and left(addr,3)=‘aaa’;
mysql> explain select * from test where name='xuebao' and age=19 and left(addr,3)='aaa';
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 18      | const,const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref、key为idx_name_age_addr )，使用了两个索引键(ref为两个const)，在索引树中遍历(Using index)，索引键addr用于索引扫描。

  总结：索引键上不要使用函数、计算、对比，否则会使该索引用于排序，且该索引之后的索引全失效。即索引列上少计算，范围之后全失效。

5.4 Like百分写最右，覆盖索引不写星

  1. select * from test where name like “%aa” and age=19 and addr=‘aaa111’;
mysql> explain select * from test where name like "%aa" and age=19 and addr='aaa111';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。

  1. select * from test where name like “aa%” and age=19 and addr=‘aaa111’;
mysql> explain select * from test where name like "aa%" and age=19 and addr='aaa111';  
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_name_age_addr | idx_name_age_addr | 31      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为range、key为idx_name_age_addr )，索引键name用于索引扫描，在索引树中遍历(Using index)。

  1. select name from test where name like “%aa” and age=19 and addr=‘aaa111’;
mysql> explain select name from test where name like "%aa" and age=19 and addr='aaa111';           
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_name_age_addr | 31      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+

mysql> explain select age from test where name like "%aa" and age=19 and addr='aaa111';    
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_name_age_addr | 31      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+

mysql> explain select addr from test where name like "%aa" and age=19 and addr='aaa111';   
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_name_age_addr | 31      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+

  从上表可知，该SQL使用到了索引(type为index、key为idx_name_age_addr )，使用的是覆盖索引(Using where; Using index)。

  1. select name,addr from test where name like “%aa” and age=19 and addr=‘aaa111’;
mysql> explain select name,addr from test where name like "%aa" and age=19 and addr='aaa111';
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_name_age_addr | 31      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+

mysql> explain select age,addr from test where name like "%aa" and age=19 and addr='aaa111';    
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | idx_name_age_addr | 31      | NULL |    5 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+--------------------------+

  从上表可知，该SQL使用到了索引(type为index、key为idx_name_age_addr )，使用的是覆盖索引(Using where; Using index)。同时从上面例子可以得知，覆盖索引只要覆盖到组合索引中任何一个或多个索引键即可。

  总结：在使用like语句时，‘%’尽量写在右边，where语句中不能使用到索引时，尽可能在select中选择索引键字段，从而使用到覆盖索引，覆盖索引意味着在索引树中就可以查询到所需数据，而不用回表操作。即Like百分写最右，覆盖索引不写星。

5.5 不等空值还有or，索引失效要少用

  1. select * from test where name is not null and age=18 and addr=‘aaa111’;
mysql> explain select * from test where name is not null and age=18 and addr='aaa111';
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys     | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_name_age_addr | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+-------------------+------+---------+------+------+-------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，所以组合索引失效。由此可见is not null用于带头大哥name会使组合索引全失效。

  1. select * from test where name=‘name’ and age is not null and addr=‘aaa111’;
mysql> explain select * from test where name='name' and age is not null and addr='aaa111';        
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_name_age_addr | idx_name_age_addr | 18      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为range、key为idx_name_age_addr )，索引键age用于索引扫描，在索引树中遍历(Using index)。

  1. select * from test where name=‘name’ and age=18 and addr is not null;
mysql> explain select * from test where name='name' and age=18 and addr is not null;
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_name_age_addr | idx_name_age_addr | 31      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为range、key为idx_name_age_addr )，索引键addr用于索引扫描，在索引树中遍历(Using index)。

5.6 varchar数引不能丢，SQL高级也不难

  1. select * from test where name=‘xuebao’ and age=18 and addr=111; 111没有引号
mysql> explain select * from test where name='xuebao' and age=18 and addr=111;
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 18      | const,const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref 、key为idx_name_age_addr )，索引键(ref为两个const)，索引键addr用于索引扫描，在索引树中遍历(Using index)。

  1. select * from test where name=‘xuebao’ and age=18 and addr=‘111’; 111带引号
mysql> explain select * from test where name='xuebao' and age=18 and addr='111';
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref               | rows | Extra                 |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 31      | const,const,const |    1 | Using index condition |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------------+------+-----------------------+

  从上表可知，该SQL使用到了索引(type为ref 、key为idx_name_age_addr )，索引键(ref为三个const)，全值匹配型。

  总结：由此可见，varchar型字段在匹配数字字符时一定要带引号，否则该索引键及后续索引键都会失效。即varchar数引不能丢，SQL高级也不难。

6、排序语句的索引优化

6.1 where使用有效索引，oder by能使用索引最左前缀

mysql> explain select * from test where name='asdf' order by age;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+

mysql> explain select * from test where name='asdf' and age=19 order by addr;
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref         | rows | Extra                              |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 18      | const,const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------------+------+------------------------------------+

mysql> explain select * from test where name='asdf' order by age, addr; 
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                              |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition; Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+------------------------------------+

mysql> explain select * from test where name='asdf' and age>18 order by age, addr;
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys     | key               | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_name_age_addr | idx_name_age_addr | 18      | NULL |    1 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+

  总结：order by中可以使用索引的前提是where语句中有有效索引，同时order by 在有效索引的基础上继续满足最左前缀规则即可使用索引。

6.2 不能使用索引进行排序

  1. select * from test order by name ASC,age DESC, addr DESC;
mysql> explain select * from test order by name ASC,age DESC, addr DESC;      
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，因为order by中索引键的升降序不一致。同时extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。

  1. select * from test where id=2 order by age, addr;
mysql> explain select * from test where id=2 order by age, addr;                      
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，因为where中没有有效索引。同时extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。

  1. select * from test where name=‘asdf’ order by addr;
mysql> explain select * from test where name='asdf' order by addr;  
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+

  从上表可知，该SQL使用了索引(type为ref ，possible_keys 、key为idx_name_age_addr )，但extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为order by没有基于where的有效索引键满足最左前缀原则。

  1. select * from test where name=‘asdf’ order by age,collage;
mysql> explain select * from test where name='asdf' order by age,collage;
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | test  | ref  | idx_name_age_addr | idx_name_age_addr | 13      | const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+----------------------------------------------------+

  从上表可知，该SQL使用了索引(type为ref ，possible_keys 、key为idx_name_age_addr )，但extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为order by中collage不是索引的一部分。

  1. select * from test where name in (‘xuebao’, ‘tujiu’, ‘sanyin’) order by age,addr;
mysql> explain select * from test where name in ('xuebao', 'tujiu', 'sanyin') order by age,addr;
+----+-------------+-------+------+-------------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys     | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+-------------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | test  | ALL  | idx_name_age_addr | NULL | NULL    | NULL |    5 | Using where; Using filesort |
+----+-------------+-------+------+-------------------+------+---------+------+------+-----------------------------+

  从上表可知，该SQL是全部扫描(type为ALL)，未使用索引(possible_keys 、key为NULL)，同时extra出现了Using filesort，表示使用了外部索引，也就是在查询之前mysql自己建了一套索引再进行查询动作，由此可见效率低，耗资源。因为对排序来说，多个相等条件也是范围查询。

编符侠16
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL索引优化总结
千里之行，始于足下
05-06 1287
文章目录MySQL索引优化总结select_typeExtra与type详细说明索引的CURD**索引的创建**索引的删除索引的查看索引的类型具体例子 MySQL索引优化总结 mysql> explain select * from t_student_course where stu_id = 2; – 结果： id: 1 select_type: SIMPLE – 查询类型（简单查...
mysql 创建索引 中断_【转】Mysql索引失效的情况
weixin_34599904的博客
01-19 608
在工作中经常能遇到索引失效的情况，只要索引失效就导致了SQL查询慢，服务响应慢，用户体验差的情况；所以下面我们就讨论一下MySQL中索引失效的情况口诀全职匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；LIKE百分写最右，覆盖索引无所谓；不等空值还有 OR，索引影响要注意；VAR 引号不可丢，SQL 优化有诀窍。1.首先创建表create table s...
Mysql之索引失效
张鑫的博客
08-27 6153
【优化口诀】  全值匹配我最爱，最左前缀要遵守；  带头大哥不能死，中间兄弟不能断；  索引列上少计算，范围之后全失效；  LIKE百分写最右，覆盖索引不写*；  不等空值还有OR，索引影响要注意；  VAR引号不可丢， SQL优化有诀窍。 解析索引失效案例： 前提建立了一个复合索引：ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
MySQL索引优化
mz474920631的博客
06-16 1万+
这里说一下几种常见优化索引的方法：前缀索引顾名思义就是使用某个字段中字符串的前几个字符建立索引，那我们为什么需要使用前缀来建立索引呢？使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。不过，前缀索引有一定的局限性，例如：覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表
mysql 索引优化
GuoWangWang11的博客
07-12 3758
mysql索引优化
MySQL 索引及优化实战1
08-03
1.聚集索引（Clustered Index） 2.非聚集索引（Non-clustered Index） 1. 普通索引：最基本的索引，它没有任何限制 2. 唯
Mysql索引步骤及优化
12-14
MySQL索引是数据库管理系统中用于加速数据检索的重要工具。它的工作原理类似于书籍的目录，能够快速定位到数据表中的特定记录。索引分为聚簇索引和非聚簇索引两类。聚簇索引根据数据的物理存储位置进行排序，而非聚...
MySql索引优化
12-14
MySQL索引优化是数据库管理中的关键环节，它直接影响着数据查询效率和系统整体性能。创建索引的主要目的是为了加速数据的检索，特别是对于大型数据库，这一点尤为重要。索引能够确保数据的唯一性，使得数据库表中的...
MySQL索引原理及慢查询优化
12-14
总的来说，优化MySQL的慢查询需要结合索引原理、查询优化技巧、数据库设计和运维实践。通过对业务需求的理解，我们可以制定合适的索引策略，改进SQL语句，从而提升数据库的整体性能，满足高并发、大数据量的业务需求...
MySQL调优篇 | SQL调优实战（5）完结篇
一线工作者的所见、所闻......
06-05 1万+
MySQL调优篇写到这里就差不多告一段落了，希望大家都能真真正正能写出高性能的SQL，结合实践中不断的实验和摸索，早日晋级资深或者架构师。
MySQL中索引失效的N种情况
IT小跟班
03-06 772
下面的示例，现场演示了具体有哪些情况会出现索引失效。 例：表结构及数据如下： select * from tb_user; show index from tb_user; 建立了username、age、gender的联合索引 1、全值匹配我最爱，最左前缀要遵守 where条件中，联合索引时一定要遵从最左前缀原则，一旦跳过某一列，其后的索引将会失效。当然，全值匹配的效率是最...
MySQL表的增删查改
Yuan_o_的博客
07-05 1267
- 把lisi尝试转换成int失败了第二种情况成功：上述这样的转换就是 “隐式类型转换” ，在Java中是非常排斥这一点的比较支持隐式类型转换的称为“弱类型系统”，C/SQL对于隐式类型转换触发的情况更多不太支持隐式类型转换的称为“强类型系统”，虽然Java排斥，但是Java中也有自动装箱、自动拆箱。
MySQL之备份与恢复(十)
最新发布
Cover_sky的博客
07-07 1074
有各种各样的好的和不是那么好的备份工具。我们喜欢对LVM使用mylvmbackup做快照备份，使用Percona Xtrabackup(开源)或MySQL Enterprise Backup(收费)做InnoDB热备份。不建议对大数据量使用mysqldump,因为它对服务器有影响，并且漫长的还原时间不可预知。有一些备份工具已经出现多年了，不幸的是有些已经过时。最明显的例子是Maatkkit的mk-parallel-dump。它从没有正确运行，甚至被重新设计过好几次还是不行。
Fastapi 项目第二天首次访问时数据库连接报错问题Can‘t connect to MySQL server
qq_27371025的博客
07-06 554
Fastapi 项目使用 sqlalchemy 连接的mysql 数据库，每次第二天首次访问数据库相关操作，都会报错：sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, “Can’t connect to MySQL server on ‘x.x.x.x’ ([Errno 111] Connection refused)”)
mysql索引介绍学习
02-15
本文将介绍MySQL索引的存储模型及使用优化。首先，让我们来了解MySQL的架构。 MySQL的架构包括连接管理、查询缓存、语法解析和预处理等部分。在连接管理方面，客户端需要与数据库建立连接，MySQL负责认证、管理连接...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值