Mysql索引

gengchao 2020-08-07 PM 289℃ 0条

唯一索引和主键索引的区别

主键就是唯一索引,但是唯一索引不一定是主键。
唯一索引:可以为NULL,但是控制只允许有一个,值不允许重复。对于单列的唯一索引,要求该列的值都是不重复的,对于多列唯一索引,要求这些列的组合是唯一的。
主键索引:值不可以为空,值不允许重复。

create table `test`(
    `id` int unsigned NOT NULL AUTO_INCREMENT ,
    `title` varchar(255) NOT NULL DEFAULT '' COMMENT'标题',
    `phone` varchar(11) NOT NULL COMMENT '手机号码',
    `content` text NOT NULL DEFAULT '' COMMENT '手机号码',
    PRIMARY KEY(`id`),#设置主键
    UNIQUE phone (`phone`)# 设置唯一索引,代表phone的值不允许重复了
);

-- 设置多列索引

create table `test`(
    `id` int unsigned NOT NULL AUTO_INCREMENT ,
    `title` varchar(255) NOT NULL DEFAULT '' COMMENT'标题',
    `phone` varchar(11) NOT NULL COMMENT '手机号码',
        `name` varchar(100) NOT NULL COMMENT '姓名',
    `content` text NOT NULL DEFAULT '' COMMENT '手机号码',
    PRIMARY KEY(`id`),#设置主键
    UNIQUE phone (`phone`, `name`)# 这两个字段的组合值不允许重复了
);

普通索引

该类索引添加没有任何限制,任何字段都可以添加。
添加语句

create table `test`(
    `id` int unsigned NOT NULL AUTO_INCREMENT ,
    `title` varchar(255) NOT NULL DEFAULT '' COMMENT'标题',
    `phone` varchar(11) NOT NULL COMMENT '手机号码',
    `content` text NOT NULL DEFAULT '' COMMENT '手机号码',
    PRIMARY KEY(`id`),#设置主键
    INDEX title_index (`title`)# 设置普通索引
);

联合索引

在多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合。

DROP TABLE IF EXISTS `test`;
create table `test`(
    `id` int unsigned NOT NULL AUTO_INCREMENT ,
    `name` varchar(100) NOT NULL DEFAULT '' COMMENT'姓名',
    `title` varchar(255) NOT NULL DEFAULT '' COMMENT'标题',
        `age`    tinyint(3) NOT NULL DEFAULT 0 COMMENT'年龄',
    `phone` varchar(11) NOT NULL COMMENT '手机号码',
    `content` text NOT NULL DEFAULT '' COMMENT '内容',
    PRIMARY KEY(`id`),
    INDEX name_title_phone (`name`, `title`, `phone`, `age`)
);

※什么是最左匹配原则?

例如上表中的索引顺序是name - title - phone
能够使用索引的组合有((name)、(name, title)、(name, title, phone)),但是这个在使用的过程中会向右一直匹配,直到遇到范围查询 (>,<,BETWEEN,LIKE) 就停止匹配,其后的索引列将不会使用索引来优化查找了。

索引使用注意事项
- 无法跨字段使用索引,例如 `where name='lisi' and phone='13385393'`是无法使用索引的,

-对于非等值条件,如 >、<、!= 等,**联合索引前缀对于索引片的过滤只能到第一个使用非等值条件的字段为止,后续字段虽然在联合索引上也无法参与索引片的过滤。**这里比如 where name='zhangsan' and title > 'title' and phone  ='baseball';,对于该查询条件,首先可以根据 name 字段过滤索引片中第一个字段的非 Bush 的数据,然后根据联合索引的第二个字段定位到索引片的 Chicago 位置,由于其是非等值条件,这里 MySQL 就会从定位的 Chicago 往下顺序扫描,由于 phone  字段是可能分散在索引第三个字段的任何位置的,因而第三个字段无法参与索引片的过滤。

因此 B-Tree 的列顺序非常重要,上述使用规则都和列顺序有关。对于实际的应用,一般要根据具体的需求,创建不同列和不同列顺序的索引。假设有索引 Index(A,B,C):

# 使用索引
A>5 AND A<10 - 最左前缀匹配
A=5 AND B>6 - 最左前缀匹配
A=5 AND B=6 AND C=7 - 全列匹配
A=5 AND B IN (2,3) AND C>5 - 最左前缀匹配,填坑

# 不能使用索引
B>5 - 没有包含最左前缀
B=6 AND C=7 - 没有包含最左前缀

# 使用部分索引
A>5 AND B=2 - 使用索引 A 列
A=5 AND B>6 AND C=2 - 使用索引的 A 和 B 列

使用索引对结果进行排序,需要索引的顺序和 ORDER BY 子句中的顺序一致,并且所有列的升降序一致(ASC/DESC)。如果查询连接了多个表,只有在 ORDER BY 的列引用的是第一个表才可以(需要按序 JOIN)。

# 使用索引排序
ORDER BY A - 最左前缀匹配
WHERE A=5 ORDER BY B,C - 最左前缀匹配
WHERE A=5 ORDER BY B DESC - 最左前缀匹配
WHERE A>5 ORDER BY A,B - 最左前缀匹配

# 不能使用索引排序
WHERE A=5 ORDER BY B DESC,C ASC - 升降序不一致
WHERE A=5 ORDER BY B,D - D 不在索引中
WHERE A=5 ORDER BY C - 没有包含最左前缀
WHERE A>5 ORDER BY B,C - 第一列是范围条件,无法使用 BC 排序
WHERE A=5 AND B IN(1, 2) ORDER BY C - B 也是范围条件,无法用 C 排序

※ order by 和group by 类似,字段顺序与索引一致时,会使用索引排序;字段顺序与索引不一致时,不使用索引。

详情请参见

mysql联合索引的使用规则-CSDN

索引能够提升查询的速度,但是在insert和update的时候速度会变慢,因为在保存的时候不仅保存数据,还需保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会增长很快。
索引只是提高效率的一个因素,如果有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

什么样的字段不适合做索引?
1 频繁更新的字段
2 不会再where条件中出现的字段
3 唯一性很差的字段不适合,比如个人信息中的年龄字段,尽管查询次数很多,但是唯一性很差,所以不适合做索引。
什么样的字段适合做索引?
1 肯定在where条经常使用
2 该字段的内容不是唯一的几个值
3 字段内容不是频繁变化。

标签: none

非特殊说明,本博所有文章均为博主原创。

评论啦~