MySQL(四)索引的使用

最新推荐文章于 2023-07-29 20:38:24 发布

dearfulan

最新推荐文章于 2023-07-29 20:38:24 发布

阅读量1.3k

点赞数 2

分类专栏： MySQL 文章标签：索引隐式类型转换

本文链接：https://blog.csdn.net/qq_35448165/article/details/104175776

版权

MySQL 专栏收录该内容

14 篇文章 2 订阅

订阅专栏

我们创建索引主要是为了提高查询数据的效率，但是索引并不是越多越好，也不是所有的列都可以创建索引

索引使用原则

列的离散度

列的离散度的公式：count(distinct(column_name)) : count(*) 列的全部不同值和所有数据行的比例。
数据行数相同的情况下，分子越大，列的离散度就越高。简单来说，如果列的重复值越多，离散度就越低，重复值越少，离散度就越高。
当我们建立的索引后去检索数据，如果重复值太多，那么就需要扫描更多的行数
原则一:建立索引，要使用离散度更高的字段。

联合索引最左匹配

在实际使用时，我们往往需要进行多条件查询，会建立联合索引。单列索引可以看成是特殊的联合索引

#建表语句
CREATE TABLE `user_innodb` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

#创建联合索引
ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone);

联合索引在B+树中是复合的数据结构，将多个字段当作一个整体来组合，它是按照从左到右的顺序来建立搜索树的(name在左边，phone在右边)。从上图不难发现，name是有序的，phone是无序的(因为索引的B+树要求有序)。当name相等的时候, phone才是有序的，也就是当name相等的时候才有可以通过phone的"索引"来进行检索

比方说使用这样的条件查询where name='Tom' and phone = '137XXX'去查询数据的时候，B+树会优先比较 name 来确定之后是向右子树还是左子树检索。当找到name相同的节点的时候才会去比较phone。但是如果查询条件只有phone，那么MySQL就不知道如何去比较节点，因为建立B+树的时候name是第一个比较元素，只用phone是无序的，无法比较。

原则二:最左匹配原则，建立联合索引的时候，一定要把最常用的列放在最左边。

举例:
对于上述的联合索引
SELECT * FROM user_innodb WHERE name= 'chenpp' AND phone='150992121'; 可以使用到索引

SELECT * FROM user_innodb WHERE name= 'chenpp' 也可以使用到索引

SELECT * FROM user_innodb WHERE phone='150992121';不可以使用到索引

也就是说我们创建联合索引(name,phone)的时候就相当于建立了两个索引(name)和(name,phone)。

如果我们创建三个字段的联合索引index(a,b,c)，相当于创建三个索引：index(a),index(a,b),index(a,b,c)
用 where b=? 和 where b=? and c=? 是不能使用到索引的。但是where a=? and c=?是可以使用到索引的
不能不用第一个字段

可以验证下:

CREATE TABLE `user_innodb2` (
  `id` int(11) PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `alias` varchar(11) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE user_innodb2 add INDEX comidx_name_alias_phone(name,alias,phone);

覆盖索引

回表：对于非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出主键索引里面的数据值，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表

对于innoDB里通过非主键索引进行查询，就会发生回表

在辅助索引里面，不管是单列索引还是联合索引，如果select的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表。--- 这是为什么不建议开发使用select * from XXX

Extra里面值为“Using index”代表使用了覆盖索引。

索引条件下推（ICP）

默认索引条件下推是开启的

CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date  NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` enum('M','F') NOT NULL,
 `hire_date` date  NULL,
 PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT;

alter table employees add index idx_lastname_firstname(last_name,first_name);


#关闭ICP
set optimizer_switch='index_condition_pushdown=off';
#开启ICP
set optimizer_switch='index_condition_pushdown=on';
#查看ICP参数
show variables like 'optimizer_switch';


INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (1, NULL, '698', 'liu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (2, NULL, 'd99', 'zheng', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (3, NULL, 'e08', 'huang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (4, NULL, '59d', 'lu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (5, NULL, '0dc', 'yu', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (6, NULL, '989', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (7, NULL, 'e38', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (8, NULL, '0zi', 'wang', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (9, NULL, 'dc9', 'xie', 'F', NULL);
INSERT INTO `employees` (`emp_no`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10, NULL, '5ba', 'zhou', 'F', NULL);

如果我们要查询所有姓wang，并且名字最后一个字是zi的员工，比如王麻子，王瘦子
SQL: select * from employees where last_name='wang' and first_name LIKE '%zi' ;

这条SQL有两种执行方式：
1.根据联合索引查出所有姓wang的二级索引数据，然后回表，到主键索引上查询全部符合条件的数据（3 条数据）。然后返回给 Server 层，在Server 层过滤出名字以zi结尾的员工。
2.根据联合索引查出所有姓wang的二级索引数据（3个索引），然后从二级索引中筛选出first_name以zi结尾的索引（1个索引），然后再回表，到主键索引上查询全部符合条件的数据（1条数据），返回给Server 层。

很明显第二种办法查询索引树的次数更少
注意，索引的比较是在存储引擎进行的，而数据记录的比较在Server层进行的。而当first_name的条件不能用于索引过滤时，Server 层不会把first_name的条件传递给存储引擎，所以会多读取两条无用的记录。
这时候，如果满足last_name='wang'的记录有100000条，就会有99999条无用的记录需要读取，这是对资源的浪费。

实际执行一下
ICP开启时:

此时会把first_name LIKE '%zi'下推给存储引擎，只会从数据表读取所需的1条记录。
索引条件下推（Index Condition Pushdown），是MySQL5.6以后完善的功能。只适用于二级索引。

ICP关闭后:

Using Where代表从存储引擎取回的数据不全部满足条件，需要在Server 层过滤。
先用last_name 条件进行索引检索，读取数据表记录后，再进行比较，检查是否符合first_name LIKE '%zi' 的条件。此时3条中只有1条符合条件。

总的来说，索引条件下推的目标就是为了减少访问表的完整行数从而减少 I/O 操作。

索引的创建与使用

Mysql在查询的时候只会使用一个索引，但不是一个字段

索引创建基本原则

1.在用于where判断,order排序(B+树便于进行排序和范围查询),join的（on）字段上创建索引
2.索引的个数不要过多。 —— 因为更新数据的时候，还需要对索引也进行更新，过多的索引会造成空间浪费，导致更新变慢。
3.区分度低的字段，例如性别，不要建索引。—— 离散度太低，导致扫描行数过多。
4.频繁更新的值，不要作为主键或者索引。—— 因为会发生页分裂和页合并，造成额外的开销
5.组合索引把散列性高（区分度高）或者常用的值放在前面。
6.尽量创建复合索引，而不是修改单列索引。
7. 索引会忽略null值，所以在设计数据库的时候需要设置为NOT NULL。
8.不适用于负向查询操作（not in , <>, !=，or） //用到or地方，尽量用union，或者程序两次查找
9.过长的字段，怎么建立索引？
可以使用前缀索引或者对这个较长字段进行hash运算，使用其作为索引(如果发生hash冲突就再过滤就好了)
10.不建议用无序的值（例如身份证、UUID ）作为索引,主要是主键索引
使用递增的id作为索引的时候，在新增数据的时候，由于是递增的，如果原来的叶已满，就会创建新的叶，而如果索引是无序的，那么在新增数据的时候，会强行插入到已满的叶中，导致叶分裂，从而有额外的开销;所以主键一般不建议使用uuid,其他索引不能保证有序

什么时候用不到索引

1.在索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式、计算（+ - * /）：
对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃使用索引树查询。
MySQL 无法再使用索引快速定位功能，而只能使用全索引扫描或者全表扫描。

2.字符串条件查询不加引号，出现隐式转换(如果是id = '1'是可以走索引的)
ALTER TABLE user_innodb add INDEX comidx_name_phone(name,phone);
为什么varchar字段的查询条件不加引号会导致无法使用索引呢?
因为在MySQL 中，字符串和数字做比较的话，是将字符串转换成数字再比较的。
对于非数字的字符串，MySQL里统一都会转成数字0

这样一来，判断name = 136的时候，如果想走索引，就需要把对应索引树的name的值全部转化成数字，一个是转化时候费时，而转化后之后原来name的大小关系也被破坏了(都变成了0) 开销成本太大，还不如全表扫描(不过当使用覆盖索引的时候可以使用到索引，index连接类型，扫描全部的二级索引树)

其实这样的SQL在写的时候也是不推荐的，一个是性能问题，一个是查询出来的数据也不准确

3.like条件中前面带%

对于%模糊匹配，%chenpp无法使用到索引，但是chenpp%可以使用到。如果实在需要进行模糊匹配，可以使用全文索引。

4. 负向查询不是一定不能使用到索引

NOT LIKE 不能使用索引：
explain select * from employees where last_name not like'wang'

!= （<>）和NOT IN 在某些情况下可以：
explain select * from employees where emp_no not in (1)

explain select * from employees where emp_no <> 1

注意一个SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

实际执行一个SQL的时候，用不用索引，使用什么索引，最终都是由优化器来决定的。
MySQL的优化器是基于cost开销的优化器(Cost Base Optimizer),而不是基于规则（Rule-Based Optimizer)。基于开销的优化器不像基于规则的优化器，有具体的执行标准，它在执行的时候会受多种因素影响(比方说数据库版本、数据量、数据选择度)，会实时计算不同的执行路径的开销，去选择开销最小的方案。

最后再总结下索引的几种概念 :

聚集索引:
聚集索引是指索引键值的逻辑顺序和数据库表行数据的物理顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况。在innodb里，其主键索引就是聚集索引

主键索引 : 是一种特殊的唯一索引，一张表中只能定义一个主键索引，通常有一列或列组合，用于唯一标识一条记录，在InnoDB里就是依赖主键索引来组织数据的

联合索引 : 与之相对的是单列索引，就是索引是由不少于2个的数据列组织的，像INDEX(columnA, columnB)，这就是联合索引。

覆盖索引 : InnoDB里辅助索引的叶子节点保存的是主键索引的值，所以使用辅助索引(非主键索引)查询，会先在二级索引树找到对应的主键索引值，再根据主键索引去查询数据，这个过程叫做回表，如果一个SQL只需要查询一次索引树不需要回表就可以找到需要返回的数据(也就是说查询的字段在辅助索引里就可以完全找到),那么就是覆盖索引

索引条件下推 : ICP就是为了减少完整记录读取的条数，它对InnoDB聚集索引无效，只能对辅助索引有效。
一个SQL并不是所有查询条件都可以使用到索引，如果使用了索引条件下推，那么在通过二级索引树检索到主键索引值后，会在存储引擎端根据其他的条件先筛选满足的主键值,然后根据筛选后的主键集合去回表获取最终的表数据。所以使用了索引条件下推可以减少需要回表的次数，就不需要全部查询出来后在server端过滤

补充2020-03-01:

即使查询条件不满足最左匹配原则，如果使用到了覆盖索引，那么最终查询的时候也是可以走索引的
比如:这里虽然只以phone为条件不满足最左匹配原则,但是因为查询列只有name,phone 那么只要扫描整个辅助索引树就可以查询到结果了，不需要进行全表扫描(但是如果在查询列里加上gender字段，就会全表扫描) 不过这里使用的是index连接类型( Full Index Scan),会查询全部索引中的数据，虽然比全表扫描快但也是需要优化的

反过来，即使查询条件满足最左匹配原则，根据实际执行的情况也可能不走索引(因为MySQL的优化器是基于cost开销的优化器)

#创建这样的一张表，随意添加10条数据(age 都大于3)
CREATE TABLE `user_innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `gender` tinyint(1) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `age` int(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age_phone` (`age`,`phone`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

从执行计划可以看出最终走了全表扫描，这是因为使用age>1查询辅助索引树(二级索引树),查询出来所有的主键都满足，那么就需要对每个主键进行回表，开销太大了，直接全表扫描更快

如果使用age>10来查询就不会出现全表扫描了

或者删除数据只剩余3条，就可以看到会使用到索引

再次强调:一个SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

dearfulan

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
MySQL(四)索引的使用

目录索引使用原则列的离散度联合索引最左匹配覆盖索引索引条件下推（ICP）索引的创建与使用索引创建基本原则什么时候用不到索引我们创建索引主要是为了提高查询数据的效率，但是索引并不是越多越好，也不是所有的列都可以创建索引索引使用原则列的离散度列的离散度的公式：count(distinct(column_name)) : count(*)列的全部不...
复制链接

扫一扫