mysql索引的使用

1. 索引的介绍

1.1索引的作用

索引相当于一本书的目录一样，可以帮助我们快速找到自己想要的数据，从而减少数据库查询、更新的时间

1.2索引的分类

在navicat中，创建表时，有个索引类型的选项

常见的索引类型有这几种：

• NORMAL：普通索引，我们最常用的索引
• UNIQUE：唯一索引，就是在普通索引的基础上，增加了唯一性的限制
• FULLTEXT：全文索引，当我们使用 varchar、text之类的类型，存储大量文本字段时，可以用它来进行检索

ps：主键索引是特殊的唯一索引，多了一个不能为空的限制

2. 索引的创建

2.1 创建方法

2.1.1 通过navicat创建普通索引

• 名：索引名默认会是字段名，一般我们会加个前缀，普通索引加前缀idx_，唯一索引加前缀 uk_
• 字段：在哪个字段上建立索引
• 索引类型

2.1.2 用sql创建表时创建

-- 创建一张表，同时指定在name字段上建立唯一索引，在create_time上建立普通索引
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  KEY `idx_create_time` (`create_time`)
);

2.1.3 表已经存在时，通过sql创建

-- 在name字段上建立唯一索引
alter table t_user add unique index uk_name(name);

-- 在create_time字段上建立普通索引
alter table t_user add index idx_create_time(create_time);

2.2 创建时的注意事项

索引不是越多越好的，因为索引可以加快查询的速度，所以看上去貌似给每个查询的字段都加上索引，就会让表的操作速度增加，其实不是的

索引过多的导致的问题：

1. 更新记录的时候，需要对索引进行更新，这就会增加更新的耗时
2. 索引本身会占用空间

建议加索引的字段：

• 有唯一性要求的字段，可以加上
• where条件中，需要经常用到的、区分度比较高的字段
• 排序的字段
• join的字段

3. 索引查询不生效的情况

先造一些测试数据

CREATE TABLE `t_employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_number` varchar(30) DEFAULT NULL COMMENT '工号',
  `name` varchar(20) NOT NULL,
  `sex` tinyint(4) NOT NULL COMMENT '1-男，2-女',
  `age` tinyint(4) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_name` (`name`) USING BTREE,
  KEY `idx_create_time` (`create_time`),
  KEY `idx_phone` (`phone`),
  KEY `idx_sex` (`sex`),
  KEY `idx_age` (`age`)
);

INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('201311C49877','刘洋',2,18,'13714162251','2013-11-02 10:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('201409K95749','郑雄',1,21,'15714162255','2014-09-02 23:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('201502K95749','黄贺',1,23,'18714162259','2015-02-02 14:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('202009C95749','陆美美',2,25,'15214162256','2020-09-02 23:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('202009Y95749','余强',1,32,'15714162253','2020-09-02 20:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('202004Y95749','王丽',2,27,'15714162252','2020-04-02 23:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('201710K95749','王淑春',2,45,'13114162250','2017-10-02 17:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('202119Y95749','王庚',1,24,'15714162252','2021-11-02 23:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('202089A95749','谢思华',2,32,'18714162785','2020-08-02 11:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('201011K95749','周友玲',2,28,'15714252251','2010-11-02 23:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('200707A95749','刘华',1,59,'15114252251','2007-07-02 13:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('200506K95749','李华',1,22,'15114252223','2005-06-02 23:14:52');
INSERT INTO `test2`.`t_employees` (`job_number`,`name`,`sex`,`age`,`phone`,`create_time`) VALUES ('200611K95749','李明',1,60,'15914252223','2006-11-02 15:14:52');

-- 这个插入语句，会插入13条记录，我测试的时候，执行了17次，生成了221条记录

-- 再额外造一条性别为3的记录
INSERT INTO `test2`.`t_employees`(`job_number`, `name`, `sex`, `age`, `phone`, `create_time`) VALUES ('201311C49877', '刘洋', 3, 18, '13714162251', '2013-11-02 10:14:52');

3.1 查询条件中有函数

需求：查姓李的员工
示例

explain select * from t_employees where left(name,1)  = '李';
-- where条件带有函数left()，没有走索引

改进写法

explain select * from t_employees where name like '李%';
-- 用like右模糊查询，会走索引

3.2 查询条件中有表达式

需求：查下一年就要满60岁的员工
示例

EXPLAIN SELECT * FROM t_employees WHERE age+1=60; 
-- 等号左边有一个表达式 age+1，没有走索引

改进写法

explain SELECT*FROM t_employees WHERE age=60-1;
-- 表达式挪到等号右边，会走索引

3.3 字段值出现隐式转换

需求：查询特定手机号的员工
示例

explain SELECT * FROM t_employees WHERE phone = 13714162251;
-- 没有用到索引，因为：phone的字段类型是varchar，但是查询的条件中，13714162251，没有用引号包裹，是一个数字类型，类型不匹配，存在字段类型转换

改进写法：

explain SELECT * FROM t_employees WHERE phone = '13714162251';
-- 走索引，因为：查询条件用引号包裹，变成了字符类型，phone字段类型也是字符类型

3.4 like查询时百分号在左边

需求：查名叫美美的员工
示例

explain select * from t_employees where name like '%美美';
-- 使用like左模糊匹配，不走索引

个人建议改进做法

• 业务层面修改，让前端把所有的name显示成一个下拉列表，这样查询条件就可以使用等号去匹配查询了
• 使用fulltext全文索引（ps：使用全文索引也不是特别方便，首先是语法比like稍微复杂一丢丢，另外mysql有一些stopwords会被忽略掉无法进行检索，还有就是你搜索的关键字长度默认需要在4-84之间才可以）

3.5 or关联的条件中存在没用上索引的情况时

需求：查询名字叫陆美美，或者年龄是28岁的员工
整个查询不走索引的示例

-- or的条件中，有name字段、age字段，假设我们之前没有为age字段建立索引
alter table t_employees drop index idx_age;
-- 再次看执行计划
explain SELECT * FROM `t_employees` where name = '陆美美' or age = 28;
-- 此时name是索引字段，age不是索引字段，存在1个字段不走索引的情况，结果就是不走索引

整个查询走索引的写法

-- 为没有索引的age字段，加上索引
alter table t_employees add index idx_age(age);
explain SELECT * FROM `t_employees` where name = '陆美美' or age = 28;
-- 此时name和age都是索引字段，走了索引

3.6 not like查询

需求：查询不姓陆的员工
示例

explain SELECT * FROM `t_employees` where name not like '陆%';
-- 不走索引

3.7 not in部分情况下查询

不走索引的示例

-- 查询不是特定名字的员工
explain SELECT*FROM `t_employees` WHERE NAME NOT IN ('余强','刘华','刘洋','周友玲','李华','李明','王丽','王庚','王淑春','谢思华','郑雄','陆美美');
-- 这条sql执行的结果记录数是17条，执行计划显示不走索引

走索引的示例

-- 查询不是特定名字的员工，但是最后一个名字由`陆美美`变成了`黄贺`
explain SELECT*FROM `t_employees` WHERE NAME NOT IN ('余强','刘华','刘洋','周友玲','李华','李明','王丽','王庚','王淑春','谢思华','郑雄','黄贺');
-- 这条sql执行的结果记录数页页也是17条，执行计划显示走了索引。

过滤出来的结果数一样，但仅仅因为过滤数据的不一样，就导致1个走索引，1个不走索引，所以说，not in有时候是不走索引的

3.8 过滤出来的记录数太少

结果记录数少，走索引示例(ps: 顺便证明1点，不等于查询时可以走索引的)

-- 查询性别既不是男，也不是女的员工
EXPLAIN SELECT*FROM `t_employees` WHERE sex !=1 AND sex !=2;
--查询出来的记录数是1条，表的总记录数是222条，从执行计划上看，走了索引

结果记录数多，不走索引的示例

 -- 查询既不是男，也不是性别未知的员工
 EXPLAIN SELECT*FROM `t_employees` WHERE sex !=1 AND sex !=3;
 -- 没走索引，因为：这个sql查询出来的记录数是102条，表的总记录数是222条，此时过滤出来的记录数比较多