探索MySQL是否走索引--序章

TigerSY
332 阅读1分钟

假设对where条件之后的列建立索引，有如下几个问题

  1. 对该列进行范围查询，一定会走索引吗？例如：age <= 30;
  2. 对该列进行in查询，一定会走索引吗？例如：age in (1,2,....30)
  3. 问题1和问题2有什么区别？性能上是否等同？
  4. like查询会走索引吗？前面不加%为什么会走索引？
  5. MySQL5.6 5.7 5.8之后，做了什么优化？
  6. 假设where条件之后，age = 18 and province = '北京市'。并且，age和province都有索引，MySQL最终选择哪个索引？两个都用，还是选其中一个？
  7. 常见的，导致索引失效的SQL写法，一定会导致索引失效吗？
  8. 进行范围值的更新操作时，InnoDB加锁机制是什么？
  9. group by city desc;

基于以上问题：

  1. 本文之后，几乎用穷举法，探索并测试是否能使用索引，使用的索引效率如何。
  2. 测试4个MySQL版本，包含5.5，5.6，5.7，5.8，探索版本对索引抉择的影响
  3. 测试4个版本中，5.7会在Windows和Linux下进行测试，探索机器本身对索引抉择的影响
  4. 基于单表的测试，多表联查后期再研究。

前期预备知识及常用sql：

  1. 查询当前版本、查询表中索引

    -- 查看MySQL版本
select VERSION();
-- 查看表中统计信息
show KEYS from user1;
show index from user1;

  2. explain的使用，了解各参数的含义，作为是否走索引的依据

    |id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |

(explain参考链接：www.cnblogs.com/gomysql/p/3…)

准备工作：

  1. MySQL各个版本（5.5  5.6  5.7 5.8）

  2. 建表及索引（5.5版本不能用CURRENT_TIMESTAMP，默认设为NULL）

    CREATE TABLE `user1` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `user_name` varchar(100) DEFAULT NULL COMMENT '用户名',
  `user_age` tinyint(3) DEFAULT NULL COMMENT '用户年龄',
  `user_password` varchar(100) DEFAULT NULL COMMENT '用户密码',
  `user_sex` tinyint(1) DEFAULT NULL COMMENT '性别 1-男，0-女',
  `user_province` varchar(32) DEFAULT NULL COMMENT '用户所在省',
  `user_city` varchar(32) DEFAULT NULL COMMENT '用户所在城市',
  `user_area` varchar(32) DEFAULT NULL COMMENT '用户所在区',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`user_id`),
  KEY `idx_user_name` (`user_name`),
  KEY `idx_user_age` (`user_age`),
  KEY `idx_sex_province_city_area` (`user_sex`,`user_province`,`user_city`,`user_area`),
  KEY `idx_province` (`user_province`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

  3. 准备数据21w条

  4. 全部都是样例数据，（暂不考虑数值合理性，中文英文编码等问题，市区归属哪个省份问题，只为测试）

avatar
文章
阅读
粉丝