索引优化实践
146 浏览量
2020-12-14
17:24:50
上传
评论
收藏 275KB PDF 举报
索引优化实践索引优化实践
SQL文件如下：
/*
Navicat MySQL Data Transfer
Source Server : localhost
Source Server Version : 50022
Source Host : localhost:3306
Source Database : test
Target Server Type : MYSQL
Target Server Version : 50022
File Encoding : 65001
Date: 2020-03-13 16:57:10
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `employees`
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(24) NOT NULL,
`age` int(11) unsigned zerofill NOT NULL default '00000000000',
`position` varchar(20) NOT NULL,
`hire_time` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES ('1', 'LiLei', '00000000022', 'mana ger', '0000-00-00 00:00:00');
INSERT INTO `employees` VALUES ('2', 'HanMeimei', '00000000023', 'dev', '2020-03-13 15:33:11');
INSERT INTO `employees` VALUES ('3', 'Lucy', '00000000023', 'dev', '0000-00-00 00:00:00');
1.全值匹配全值匹配
mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | Using where |
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_name_age_position | idx_name_age_position | 78 | const,const | 1 | Using where |
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
1 row in set
mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | employees | ref | idx_name_age_position | idx_name_age_position | 140 | const,const,const | 1 | Using where |
+----+-------------+-----------+------+-----------------------+-----------------------+---------+-------------------+------+-------------+
1 row in set
2.最左前缀法则最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引
中的列。
资源评论
