mysql innodb 索引优化_mysql的innodb引擎索引优化

在数据量较小时，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等)，即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。

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