MySQL 索引种类

# MySQL 索引种类

在研究索引类型之前得先准备一张表用做测试表

-- 创建一张表
CREATE TABLE `user` (
  `id_` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name_` varchar(255) NOT NULL COMMENT '姓名',
  `age` int(10) NOT NULL COMMENT '年龄',
  `phone` varchar(11) DEFAULT NULL COMMENT '手机号',
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 正常 0 禁用',
  PRIMARY KEY (`id_`),
  KEY `name_index` (`name_`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 填入数据
INSERT INTO `user` VALUES (1, '赵一', 18, '18200000000', '2021-05-19 10:35:48', '2021-05-19 10:35:51', 1);
INSERT INTO `user` VALUES (2, '钱二', 22, '18200000001', '2021-05-19 10:36:28', '2021-05-19 10:36:31', 1);
INSERT INTO `user` VALUES (3, '孙三', 21, '18200000002', '2021-05-19 10:37:52', '2021-05-19 10:37:54', 1);
INSERT INTO `user` VALUES (4, '李四', 23, '18200000003', '2021-05-19 10:37:56', '2021-05-19 10:37:59', 1);

普通索引【normal】

  1. 加速查询
  • 创建一个普通索引

    create index `name_index` ON `user`(`name_`);
    
  • 查询测试(这里我是用 navicat)

    explain SELECT * FROM `user` where `name_` LIKE '赵%';
    

    此时可以看到使用了这个普通索引字段,在看下面一个。

explain SELECT * FROM user where name_ LIKE ‘%赵%';


![](http://image.runbrick.com/20210531144225.png)

证明设置成功,而且 like 查询 如果写了前 % 索引会失效。这里可以查看图片的 **rows** 字段



**唯一索引【unique】**

> 1. 加速查询
> 2. 列值唯一
> 3. 可以有 NULL

* 创建一个唯一索引

```sql
-- 创建手机号的唯一索引
create unique index `unique_index_phone` ON `user`(phone);
  • 测试一下是否成功并且是否列值唯一,我们创建一行新的数据

    INSERT INTO `book`.`user`( `name_`, `age`, `phone`, `create_time`, `update_time`, `status`) VALUES ( '赵五', 31, '18200000003', '2021-05-19 10:37:56', '2021-05-19 10:37:59', 1);
    

​ 可以看出并没有添加成功,这就是唯一索引的作用之一。把上面的语句中手机号改为18200000004 再次测试。就可以发现能添加成功了。

​ 虽然之前没有添加成功,但是ID增长了。这就是说,其实是添加了的但是因为手机号重复所以被回滚了。

唯一索引也有加速查询的功能但是都类似于普通索引所以就不在测试了。

  • 测试一下空值是否能成功

    INSERT INTO `book`.`user`( `name_`, `age`, `phone`, `create_time`, `update_time`, `status`) VALUES ( '钱六', 31, '', '2021-05-19 10:37:56', '2021-05-19 10:37:59', 1);
    

    发现是可以成功的,所以 唯一索引 的值是可以为空的。但空值也是唯一的,如果再有个空值就加不进去了

主键索引 【PRIMKEY】

  1. 加速查询
  2. 列值唯一
  3. 不可以有 NULL

这个和 唯一索引 的功能好像,但是其中有一点不同 主键索引 的值不能有 NULL。表中的 **id_** 字段就是主键。

组合索引【联合索引】

  1. 由多个字段组成的,用于组合搜索
  2. 加速查询
  • 创建一个组合索引

    create index `name_phone_index` ON `user`(`name_`,`phone`);
    
  • 测试组合索引(我删除了上面出主键索引的其他索引)

    explain select * from `user` where  `name_` = '赵五' and `phone`= '18200000004'  and `age` = 31 ;
    

    explain select * from `user` where  `phone`= '18200000004'  and `age` = 31 ;
    

    explain select * from `user` where  `name_` = '赵五';
    

    第二个测试的结果并没有使用到组合索引,这是因为 组合索引,最左匹配的原则。如果前导列没有出现也不会使用该索引

    explain select * from `user` where  `phone`= '18200000004' and `name_` = '赵五'  and `age` = 31 ;
    

    这样也是可行的,MySQL 的优化器会自动帮我们调整顺序去找到索引。同时在遇到范围查询的时候就会停止匹配

全文索引

对文本内容进行分词搜索

全文搜索我用的不多,没有太深入了解这个索引。