您现在的位置是：网站首页> 编程资料编程资料
MySQL索引优化实例分析_Mysql_
2023-05-26
495人已围观
简介 MySQL索引优化实例分析_Mysql_
1.数据准备
#1.建立员工表，并创建name,age,position索引，id为自增主键 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位', `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间', PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=100010 DEFAULT CHARSET=utf8 COMMENT='员工记录表' # 2.前面插入三条数据,并建立employees_min_copy表插入这三条数据 INSERT INTO employees (name,age,`position`,hire_time) VALUES ('LiLei',22,'manager','2021-08-17 21:00:55') ,('HanMeimei',23,'dev','2021-08-17 21:00:55') ,('Lucy',23,'dev','2021-08-17 21:00:55') ; #3.再通过执行计划向表中插入十万条数据 #3.1建立存储过程，往employees表中插入数据（MySQL8.0版本） DELIMITER $$ USE `zhebase`$$ DROP PROCEDURE IF EXISTS `batch_insert_employees`$$ CREATE PROCEDURE `batch_insert_employees`(IN `start_number` BIGINT,IN `counts` BIGINT) BEGIN DECLARE start_number BIGINT DEFAULT start_number; DECLARE stop_number BIGINT DEFAULT start_number; SET stop_number=start_number + counts; WHILE start_number < stop_number DO INSERT INTO employees(name,age,position,hire_time) VALUES(CONCAT('zhang',start_number),start_number,'dev',now()); SET start_number=start_number+1; END WHILE ; COMMIT; END$$ DELIMITER ; #3.2执行存储过程插入十万条数据 CALL batch_insert_employees(1,100000);2.实例一
1.联合索引第一个字段用范围不会走索引
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
原因：MySQL 内部可能觉得第一个字段就用范围，结果集应该很大，还需要回表，回表效率不高，不如直接采用全表扫描 但是我们可以强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
-- 关闭查询缓存 set global query_cache_size=0; set global query_cache_type=0; -- 执行时间0.321s SELECT * FROM employees WHERE name > 'LiLei'; -- 执行时间0.458s SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';
使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高
对于这种情况，如果可以使用覆盖索引，就使用覆盖索引进行优化
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
2.in 和 or 在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager'; #表数据量大走索引，数据量小全表扫描 EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager';
将十万行数据的employees表复制一份插入几行数据，再进行查询
发现进行了全表扫描
3.like xx% 无论数据量多少一般情况都会走索引
EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';
MySQL 底层使用索引下推（Index Condition Pushdown，ICP） 来对 like xx%进行优化。
索引下推： 对于辅助的联合索引(idx_name_age_position)，通常按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 因为在 name 是范围查询，过滤完后，age 和 position 是无序的，后续索引无法使用，只会走name字段索引。
- MySQL5.6 以前： 先在索引树中匹配 name 是 'LiLei' 开头的索引，然后根据索引下的主键进行回表操作，在主键索引上在匹配 age 和 position
- MySQL 5.6以后： 引入索引下推，先在索引树种匹配 name 是 'LiLei' 开头的索引，同时将该所与树通有的所有条件字段进行判断，过滤掉不符合条件的记录再回表匹配其他条件及查询整行数据。
- 优点： 过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数，提高查询效率。
MySQL 范围查找为什么没有使用索引下推优化？ 可能因为范围查找结果集一般较大，like xx%在大多数情况下，过滤后结果集较小。而结果集大的时候，每次检索出来都要匹配后面的字段，不一定比立即回表要快。但是也不是绝对的，有些时候 Like xx%也不会走索引下推。
3.MySQL如何选择合适的索引？
先来看两条 SQL 语句：
# MySQL直接使用全表扫描 EXPLAIN select * from employees where name > 'a'; # MySQL走索引 EXPLAIN select * from employees where name > 'zzz';
我们发现第一条 SQL 进行了全表扫描，第二条 SQL 走了索引。对应第一条SQL，MySQL 通过计算执行成本发现走索引成本比全部扫描更高（走索引需要遍历 name 字段，再进行回表操作查出最终数据，比直接查聚簇索引树更慢)。对于这种情况可以使用覆盖索引进行优化。至于 MySQL 如何选择最终索引，可以用 Trace 工具进行查看。但开启trace工具会影响 MySQL 性能，用完之后需立即关闭。
#开启trace set session optimizer_trace="enabled=on",end_markers_in_json=on; #关闭trace set session optimizer_trace="enabled=off"; #使用trace select * from employees where name > 'a' order by position; select * from information_schema.OPTIMIZER_TRACE;
下面是执行后的Trace中的内容：
{ "steps": [ { #第一阶段：SQL准备阶段，格式化sql "join_preparation": { "select#": 1, "steps": [ { "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position` limit 0,200" } ] /* steps */ } /* join_preparation */ }, { #第二阶段：SQL优化阶段 "join_optimization": { "select#": 1, "steps": [ { #条件处理 "condition_processing": { "condition": "WHERE", "original_condition": "(`employees`.`name` > 'a')", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`employees`.`name` > 'a')" }, { "transformation": "constant_propagation", "resulting_condition": "(`employees`.`name` > 'a')" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`employees`.`name` > 'a')" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { #表依赖详情 "table_dependencies": [ { "table": "`employees`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { #预估表的访问成本 "rows_estimation": [ { "table": "`employees`", "range_analysis": { "table_scan": { --全表扫描情况 "rows": 93205, --扫描行数 "cost": 9394.9 --查询成本 } /* table_scan */, #查询可能使用的索引 "potential_range_indexes": [ { "index": "PRIMARY", --主键索引 "usable": false, -- 是否使用 "cause": "not_applicable" }, { #辅助索引 "index": "idx_name_age_position", "usable": true, "key_parts": [ "name", "age", "position", "id" ] /* key_parts */ } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "gro
相关内容
- Mysql中使用count加条件统计的实现示例_Mysql_
- MySQL常用命令与内部组件及SQL优化详情_Mysql_
- Mysql中count(*)、count(1)、count(主键id)与count(字段)的区别_Mysql_
- Mysql中报错函数floor()函数和rand()函数的配合使用及原理详解_Mysql_
- 通过yum方式安装mySql数据库的全过程_Mysql_
- MySQL外键约束详解_Mysql_
- MySQL索引优化之不适合构建索引及索引失效的几种情况详解_Mysql_
- MySQL索引优化之适合构建索引的几种情况详解_Mysql_
- MySQL筑基篇之增删改查操作详解_Mysql_
- MySQL外键级联的实现_Mysql_
点击排行
本栏推荐
