在數(shù)據(jù)量較小時(shí)税产，innodb引擎會(huì)自行優(yōu)化李请，有時(shí)候不會(huì)使用索引藤抡。
創(chuàng)建數(shù)據(jù)庫：
/*
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;
做個(gè)查詢：
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)
發(fā)現(xiàn)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)
發(fā)現(xiàn)用了索引廉丽，type為ref，結(jié)果喜人妻味。
原因
由于索引掃描后要利用索引中的指針去逐一訪問記錄正压，假設(shè)每個(gè)記錄都使用索引訪問，則讀取磁盤的次數(shù)是查詢包含的記錄數(shù)T责球，而如果表掃描則讀取磁盤的次數(shù)是存儲(chǔ)記錄的塊數(shù)B焦履，如果T>B 的話索引就沒有優(yōu)勢(shì)了。對(duì)于大多數(shù)數(shù)據(jù)庫來說雏逾，這個(gè)比例是10%（oracle嘉裤，postgresql等），即先對(duì)結(jié)果數(shù)量估算栖博，如果小于這個(gè)比例用索引屑宠，大于的話即直接表掃描。
這里仇让，①中會(huì)有4條數(shù)據(jù)典奉，T=4，B=丧叽？ 后續(xù)解答卫玖。。
