MySQL优化之索引

最新推荐文章于 2022-07-12 14:39:43 发布

沉默木头人

最新推荐文章于 2022-07-12 14:39:43 发布

阅读量6.5k

点赞数

分类专栏： MySql 文章标签：数据库 mysql java 索引

本文链接：https://blog.csdn.net/qq_44322555/article/details/105671146

版权

MySql 专栏收录该内容

10 篇文章 2 订阅

订阅专栏

一、索引基本概念

1.1、索引是什么？

索引可当作是一本书的目录，一个表中的某列加了索引相当于在一个小表中单独记录了此列的信息，在查询大表时根据这个”目录“记载的rowid可以很快的找到对应的数据。

索引主要做3件事：过滤(filter)，排序或分组(sort/group)，覆盖(cover)

一个基本查询的工作流如下：

使用索引以查找匹配的记录，并得到数据的指针。
使用相关数据的指针。
返回查询到的记录。

当可以使用覆盖索引时，索引将会覆盖查询中的所有字段，因此第二步将会被跳过，于是查询流程就变成了下面这样：

使用索引以查找匹配的记录
返回查询到的记录。

1.2、为什么数据库一定要有主键

前提：主键应该为一个对业务无意义、一个有序递增的int或number型字段
1、可以利用主键字段在数据底层存储上能有序的存储，能有效防止底层存储在DML时底层数据的可能存在迁移情况（行迁移、行连接等），影响底层数据存储上空间和性能的严重损耗
2、利用主键能够快速定位和update、delete快速更新或删除
3、能够提高OGG的快速同步和数据一致性保障

1.3、什么样的字段一定要创建索引

1、表与表之间经常join的字段
2、经常在where条件中列
3、复合索引创建指引：当where条件中出现多个条件列的时候，应该按照重复值越低且符合大部分业务场景查询的列作为前导列，减少数据的回表，直接能从索引中返回要的数据
比如：where idcard_no =‘111111111’ and update_time=trunc(sysdate)
idcard_no为个人身份证，该字段几乎为唯一，重复值很低，可以作为复合索引的前导列
update_time为更新时间，该字段会存储非常多的重复值，可以和idcard_no结合创建成复合索引

1.4、什么样的字段一定不能建立成索引

1、基数很低的列（比如性别，type等重复值很高的列）
2、更新频繁但检索不频繁的列
3、BLOB/TEXT等长内容列
4、很少用于检索的列

1.5、什么时候该创建索引又该创建什么索引呢？

1、表数据量大的情况下（只有个几百条数据的情况下加了索引也没有什么感觉）
2、插入修改删除不频繁的业务表（开头说了索引列是要跟小表有一个关联的，如果频繁的增删改，小表也要进行修改，所以dml的语句会降低效率）
3、经常要把哪几个字段拿来进行查询或者排序这个时候可以考虑建立索引（唯一性越强的字段越适合加索引比如主键）
4、数据重复多的话用位图索引（比如性别、科室、类别等）
5、选择性次一级的可以使用复合索引（比如姓名，家庭住址，时间等）这里使用复合索引要把选择性强的索引来放组合的前面，选择性强的就是可以大量筛除数据的字段注：oracle自ORACLE 10g开始已经丢弃了RBO优化器，使用了CBO。所以在Mysql中组合查询要遵守的前导列查询在10g之后可不用遵守。即表test中有组合索引a,b,c,d 在Mysql中where条件中必须a字段放到第一位上，如果查询bcd,或者bacd都不会走索引。
6、这个时候我想到如果既有单字段索引，又有这几个字段上的复合索引，那直接建立一个大的复合索引就好了啊，实际情况却并不是想象的那么美好，在执行计划中，在有单字索引的时候，就不会走复合索引了，但是如果直接建立一个大的复合索引的话执行的效率却又没有选择性强的单字索引效率来的快，所以出现单字索引和复合索引有交叉的情况下一般删除复合索引（具体情况视业务来定）。
7、经常与其他表进行连接的表，在连接字段上应该建立索引。

1.6、什么情况下不能创建索引？

1.查询中很少使用到的列不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系

1.7、使用索引的优点：

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件可以加速表与表直接的相连
4.在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引可以提高性能

1.8、使用索引的缺点：

1.在创建索引和维护索引会耗费时间,随着数据量的增加而增加
2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

二、创建索引

2.1、建表时创建

CREATE TABLE 表名(

字段名 数据类型 [完整性约束条件],

  ……，

[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

[索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

);

说明：

UNIQUE:可选。表示索引为唯一性索引。
FULLTEXT:可选。表示索引为全文索引。
SPATIAL:可选。表示索引为空间索引。
INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。
索引名:可选。给创建的索引取一个新名称。
字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。
长度:可选。指索引的长度，必须是字符串类型才可以使用。
ASC:可选。表示升序排列。 DESC:可选。表示降序排列。

注：索引方法默认使用B+TREE。

单列索引示例：

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',

	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
	-- 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
	FOREIGN KEY (projectid) REFERENCES project (id),
	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
	UNIQUE INDEX (projectid),
	-- 给fileuploadercode字段创建普通索引
	INDEX (fileuploadercode)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

组合索引示例：

CREATE TABLE projectfile (
	id INT AUTO_INCREMENT COMMENT '附件id',
	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
	projectid INT COMMENT '项目id;此列受project表中的id列约束',
	filename VARCHAR (512) COMMENT '附件名',
	fileurl VARCHAR (512) COMMENT '附件下载地址',
	filesize BIGINT COMMENT '附件大小，单位Byte',
	-- 主键本身也是一种索引（注:也可以在上面的创建字段时使该字段主键自增）
        PRIMARY KEY (id),
        -- 创建组合索引
	INDEX (fileuploadercode,projectid)
	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

2.2、建表后创建

ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]；

或

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名 ON 表名(字段名) [USING 索引方法]；

示例一：

CREATE INDEX index_name ON table(column(length))

示例二：

-- 假设建表时fileuploadercode字段没创建索引(注:同一个字段可以创建多个索引，但一般情况下意义不大)
-- 给projectfile表中的fileuploadercode创建索引
ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);

示例三：

ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

示例四：

-- 将id列设置为主键
ALTER TABLE index_demo ADD PRIMARY KEY(id) ;
-- 将id列设置为自增
ALTER TABLE index_demo MODIFY id INT auto_increment;

2.3、查看已经创建的索引

show index from 表名;

提示:我们也可以直接使用工具查看
在这里插入图片描述

2.3、索引的删除

DROP INDEX 索引名 ON 表名

或

ALTER TABLE 表名 DROP INDEX 索引名

示例一：

drop index fileuploadercode1 on projectfile;

示例二：

alter table projectfile drop index s2123;

三、索引的类型

MySQL的索引类型有以下几种：

普通索引

唯一索引

主键索引

组合索引

全文索引

3.1、普通索引

是最基本的索引，它没有任何限制。
上面介绍的创建索引的方式就是普通索引；

3.2、唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

3.3、主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,    PRIMARY KEY (`id`)
);

联合唯一索引：
create语句中加入：

UNIQUE KEY UNIQ_SUB_RES_IDX (name,res_id),

修改表结构的方式

alter table tablename add unique index UNIQ_SUB_RES_IDX(name, res_id);

3.4、组合索引

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

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

3.5、全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

创建表的适合添加全文索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

修改表结构添加全文索引

ALTER TABLE article ADD FULLTEXT index_content(content)

直接创建索引

CREATE FULLTEXT INDEX index_content ON article(content)

四、使用索引注意事项

使用索引时，有以下一些技巧和注意事项：

1、索引不会包含有null值的列

只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

2、使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是唯一的，

那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3、索引列排序

查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符

合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

4、like语句操作

一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5、不要在列上进行运算，这将导致索引失效而进行全表扫描，例如

SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6、不使用not in和<>操作