mysql中，索引是存儲引擎實現(xiàn)的柏靶，不同的存儲引擎索引的工作方式不一樣，由于mysql默認的存儲引擎為InnoDB溃论，所以下面都是基于InnoDB的例子屎蜓。

1、查詢條件中有or

創(chuàng)建一個user 表和一個job表钥勋，建表語句如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

執(zhí)行如下sql

explain SELECT name,age,address FROM user where name = '光頭強' or age=9

上例sql中name字段上有索引index_name炬转，下面來看下執(zhí)行計劃：

image.png

type為All，仍然是走的全表掃描算灸。

2扼劈、like查詢是以'%'開頭

仍然用1中的user表執(zhí)行如下sql

explain SELECT name,age,address FROM user where name like '%頭強' 

上例sql中name上有索引index_name，下面來看下執(zhí)行計劃：

image.png

type為All菲驴，仍然是走的全表掃描荐吵。 但是如果想讓以‘%’開頭仍然使用索引，則需要使用覆蓋索引，即只查詢帶索引字段的列：

explain SELECT name FROM user where name like '%頭強' 

image.png

如上捍靠，執(zhí)行計劃里的key用到了index_name沐旨。

3森逮、對查詢的列上有運算或者函數(shù)的

仍然用1中的user表執(zhí)行如下sql

explain SELECT name,age,address FROM user where substr(name,-2)='頭強'

上例sql中name上有索引index_name榨婆，下面來看下執(zhí)行計劃：

image.png

再比如：

explain SELECT name,age,address FROM user where age+1=12

上例中age有索引index_age，執(zhí)行計劃如下：

image.png

4褒侧、如果列類型是字符串良风，那一定要在條件中將數(shù)據(jù)使用引號引用起來,否則不使用索引

仍然用1中的user表執(zhí)行如下sql

explain SELECT name,age,address FROM user where name = 10

上例sql中name上有索引index_name，下面來看下執(zhí)行計劃：

image.png

同樣闷供，沒有用到索引走的全表掃描烟央。
這里面的原因其實跟例3是一樣的，首先mysql有個類型轉(zhuǎn)換規(guī)則就是將“字符轉(zhuǎn)成數(shù)字”歪脏，所以以上sql就等價于這樣：

explain SELECT name,age,address FROM user where cast(name as signed)= 10

很明顯疑俭，name字段上有函數(shù)。所以跟例3一樣的原因婿失。不會走索引钞艇。

5、左連接查詢或者右連接查詢查詢關(guān)聯(lián)的字段編碼格式不一樣

首先同樣建兩張表豪硅，一個user哩照，一個job，建表語句如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注意user 表的name字段編碼是utf8mb4懒浮，而job表的name字段編碼為gbk飘弧。執(zhí)行左外連接查詢：

EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

執(zhí)行計劃如下：

image.png

b表仍然走的全表掃描。注意如果是用覆蓋索引的話砚著，那么b表就會走索引了次伶。

6、如果mysql估計使用全表掃描要比使用索引快,則不使用索引

這個舉例還用user表和job表稽穆，只不過我們往表里面少插入一些數(shù)據(jù)冠王，建表如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('光頭強', 12, '狗熊嶺', 1);
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('熊大', 9, '狗熊嶺2', 2);

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`job`(`id`, `userId`, `job`, `name`) VALUES (1, 1, 'java', '光頭強');
INSERT INTO `test`.`job`(`id`, `userId`, `job`, `name`) VALUES (2, 2, 'php', '熊大');

還是執(zhí)行上面的查詢：

EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

執(zhí)行計劃如下：

image.png

此時，由于要查詢b.name秧骑，mysql需要回表版确，mysql認為走全表掃描會快一些，所以即使b表的name有索引乎折，也不會走绒疗。

7、連接查詢中骂澄，按照優(yōu)化器順序的第一張表不會走索引

這個可能聽起來有點懵逼吓蘑，舉個栗子，仍然建兩張表柒莉，建表語句如下：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('光頭強', 12, '狗熊嶺', 1);
INSERT INTO `test`.`user`(`name`, `age`, `address`, `id`) VALUES ('熊大', 9, '狗熊嶺2', 2);

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

插入數(shù)據(jù)的insert語句就不寫了构蹬，要多插入一些，否則下面演示會失效扎狱，因為數(shù)據(jù)少mysql認為走全表掃面快一些琳猫，就怎么都用不到索引了伟叛。
執(zhí)行查詢：

EXPLAIN select a.name,a.age,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

此時a表user上的name是有索引的，b表name無索引脐嫂，看下執(zhí)行計劃

image.png

a表统刮，b表都是全表掃描，而建表語句不變账千，把左外連接改為右外連接

EXPLAIN select a.name,a.age,b.name,b.job
from
user a
right JOIN job b
ON a.name =b.name

執(zhí)行計劃如下：

image.png

可以看到侥蒙，a表用到了索引，同樣匀奏，將右連接改為內(nèi)連接再看下

EXPLAIN select a.name,a.age,b.name,b.job
from
user a
inner JOIN job b
ON a.name =b.name

執(zhí)行計劃如下：

image.png

同樣鞭衩，a表也是用到了索引，從上面三個連接查詢來看只有左外連接a表沒有用到索引的娃善，這就是因為由于是左外連接论衍，所以優(yōu)化器的執(zhí)行順序是a表、b表会放，也就是說首先全表掃描a表饲齐，再根據(jù)a表的name查詢b表的值，所以a表無法用到索引咧最。用段偽代碼解釋下：

//mysql代碼
select a.name,a.age,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name
//相當于執(zhí)行以下循環(huán)
List<Map<String,Object>> resultA=select a.name,a.age from user a
for(Map<String,Object> map: resultA){
       List<Map<String,Object>> resultB=select b.name,b.job from job b where b.name=map.get("name")
}

從這段偽代碼可以看到a表沒有where語句捂人，所以根本無法用到name上的索引，而b表此時name上若無索引的話矢沿，導(dǎo)致兩個表都是全表掃描滥搭，所以一般這種連接查詢，A表關(guān)聯(lián)B表捣鲸，要在將優(yōu)化器順序的第二張表上關(guān)聯(lián)的字段上加索引瑟匆，而第一張表則無需加索引，無用的索引也會影響性能栽惶。而第三個例子中內(nèi)連接就不一樣了愁溜，如果是內(nèi)連接優(yōu)化器本身就會根據(jù)索引情況，連接表的大小等去選擇執(zhí)行順序了外厂，所以上例中的內(nèi)連接執(zhí)行順序是b冕象、a，這樣仍然可以用到a的索引汁蝶。

8渐扮、如果查詢中沒有用到聯(lián)合索引的第一個字段论悴，則不會走索引

建立job表如下：

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`,`job`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

執(zhí)行查詢

explain SELECT * from job where job='java'

執(zhí)行計劃如下：

image.png

走的是全表掃描。