mysql的innodb引擎索引优化

作者: 管恺森 | 来源:发表于2018-06-20 11:12 被阅读42次

学习的技术栈，技术书籍必看for me
mysql索引浅析
mysql的innodb引擎索引优化
MySQL相关文章索引（2）
总结mysql索引失效的N种情况
[数据库之十二] 数据库索引之覆盖索引
MySQL索引及其优化
mysql索引
mysql 索引
索引

在数据量较小时，innodb引擎会自行优化，有时候不会使用索引。

创建数据库：

/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : MySQL
 Source Server Version : 50721
 Source Host           : 127.0.0.1
 Source Database       : test

 Target Server Type    : MySQL
 Target Server Version : 50721
 File Encoding         : utf-8

 Date: 06/20/2018 11:02:19 AM
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(128) DEFAULT NULL,
  `teacher_id` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  `rank` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_class_name_teacher_id_count` (`class_name`,`teacher_id`,`count`) USING BTREE,
  KEY `idx_t` (`teacher_id`) USING BTREE,
  KEY `idx_t_c_r` (`teacher_id`,`count`,`rank`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', 'class1', '1', '50', '1'), ('2', 'class2', '3', '51', '2'), ('3', 'class3', '1', '50', '1'), ('4', 'class4', '1', '48', '1'), ('5', 'class5', '1', '52', '2'), ('6', 'class6', '2', '45', '3');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

做个查询：

mysql> select * from class;
+----+------------+------------+-------+------+
| id | class_name | teacher_id | count | rank |
+----+------------+------------+-------+------+
|  1 | class1     |          1 |    50 |    1 |
|  2 | class2     |          3 |    51 |    2 |
|  3 | class3     |          1 |    50 |    1 |
|  4 | class4     |          1 |    48 |    1 |
|  5 | class5     |          1 |    52 |    2 |
|  6 | class6     |          2 |    45 |    3 |
+----+------------+------------+-------+------+
6 rows in set (0.00 sec)

奇怪的问题

分析sql

分析sql：explain select * from class where teacher_id = 1; ①

mysql> explain select * from class where teacher_id = 1;
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | idx_t,idx_t_c_r | NULL | NULL    | NULL |    6 |    66.67 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

发现type为ALL，没用使用索引。

重新做分析

分析sql：explain select * from class where teacher_id = 3;②

mysql> explain select * from class where teacher_id = 3;
+----+-------------+-------+------------+------+-----------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | class | NULL       | ref  | idx_t,idx_t_c_r | idx_t | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

发现用了索引，type为ref，结果喜人。

原因

由于索引扫描后要利用索引中的指针去逐一访问记录，假设每个记录都使用索引访问，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。

https://www.zhihu.com/question/51004639/answer/123766749

这里，①中会有4条数据，T=4，B=？后续解答。。