MySQL数据库之索引

命令加载中
124 阅读10分钟

1. 索引的概念

  • 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。

  • 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

  • 索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。

  • 索引是表中一列或者若干列值排序的方法。

  • 建立索引的目的是加快对表中记录的查找或排序。（加快查询速度、对字段值进行排序）

2. 索引的作用

 2.1 优点

  • 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。

  • 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。

  • 可以降低数据库的I/O成本，并且索引还可以降低数据库的排序成本。

  • 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

  • 可以加快表与表之间的连接。

  • 使用分组和排序时，可大大减少分组和排序的时间。

  • 建立索引在搜索和恢复数据库中的数据时能显著提高性能。

2.2 缺点

  1. 索引需要占用额外的磁盘空间。
  • 对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。（数据的物理地址）
  • 而 InnoDB 引擎的表数据文件本身就是索引文件。（索引文件和数据文件是同一个）
  1. 在插入和修改数据时要花费更多的时间、消耗更多性能，因为索引也要随之变动。

2.3 索引的排序

1.不按照顺序插入数据

QQ截图20221025105016.png

2.创建id字段的索引

QQ截图20221025105333.png

3. 创建索引的原则依据

索引虽可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

  • 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。

  • 记录数超过300行（或500行）的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。（会影响IO和CPU的性能）

  • 经常与其他表进行连接的表，在连接字段上应该建立索引。

  • 唯一性太差的字段不适合建立索引。（因为字段值会有重复）

  • 更新太频繁的字段不适合创建索引。（更新时表和字段都会更新，不建议）

  • 经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。

  • 在经常进行GROUP BY、ORDER BY的字段上建立索引。

  • 索引应该建在选择性高的字段上。（即重复性低的字段）

  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。（大字段建索引会增加磁盘空间，而且搜索起来会影响性能）

3.1 索引的分类和创建

创建表
create table member (id int(10),name varchar(10),cardid int(10),address varchar(50),remark text);

QQ截图20221025105717.png

QQ截图20221025111502.png

3.2 普通索引

最基本的索引类型，没有唯一性之类的限制。

3.3 直接创建索引：

  • (列名(length))：length是可选项，下同。如果忽略length的值，则使用整个列的值作为索引。如果指定，使用列的前length个字符来创建索引，这样有利于减小索引文件的大小。在不损失精确性的情况下，长度越短越好。并且只有字符串类型才能设置length的大小。

  • 索引名建议以“_index”结尾。

CREATE INDEX 索引名 ON 表名 (列名[(length)]

create index name_index on member1 (name(3));

QQ截图20221025111835.png

QQ截图20221025112050.png

3.4 修改表方式创建：

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

列子：
alter table member1 add index (cardid);
alter table member1 add index phone_index (phone);

QQ截图20221025112450.png

QQ截图20221025112541.png

QQ截图20221025112717.png

3.5 创建表的时候指定索引：

CREATE TABLE表名（字段1 数据类型,字段2 数据类型[,...],INDEX 索引名(列名));

例子
 create table member2 (id int(10),name varchar(10),cardid int(20),phone int(11),address varchar(50),remark text,index name_index(name));

QQ截图20221025113348.png

3.6 唯一索引

唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同)。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

3.7 直接创建唯一索引：

CREATE UNIQUE INDEX 索引名 ON 表名(列名)

列子：
create unique index phone_index on member1 (phone);

QQ截图20221025185214.png

3.8 修改表方式创建：

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

列子：
 alter table member1 add unique cardid_index (cardid);

QQ截图20221025185447.png

注意：如果表格中某字段中有两条记录的该字段内容相同，则无法为该字段添加唯一索引。同样，添加了唯一索引的字段无法增加内容相同的记录

3.9 创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

列子：
create table member3 (id int(10),name varchar(10),cardid int(20),phone int(11),address varchar(50),remark text,unique cardid_index(cardid));

QQ截图20221025190323.png 注意：查询时，where指定的条件要按照组合索引的顺序，索引才会生效。

QQ截图20221025190700.png

4.1 唯一组合索引

多个字段组合的值不能重复，其中某个单列是可以重复的

QQ截图20221026085417.png

4.2 全文索引

全文索引：适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。
在MySQL5.6版本以前，FULTEXT索引仅可用于MyISAM引擎，在5.6版本之后innodb引擎也支持FULLTEXT索引。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

4.3 直接创建索引：

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

列子：
create fulltext index addr_index on member1 (address);

QQ截图20221026090911.png

4.4 修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

例子：
alter table member1 add fulltext remark_index (remark);

4.5 创建表的时候指定索引

数据类型可以为CHAR、VARCHAR 或者TEXT

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));

例子：
 create table member4 (id int(10),name varchar(10),cardid int(11), phone int(11),address int(50),remark text,fulltext name_index (name));

QQ截图20221026091611.png

4.6 使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST ('查询内容');

列子：
select * from member1 where match(name) against('zhangsan');

QQ截图20221026094244.png

查询结果内容只要包含该单词（字符串）即可。

select * from member1 where match(remark) against('vip');

QQ截图20221026094537.png

4.7 查看索引

show index from 表名;
show keys from 表名;

QQ截图20221026094902.png

4.8 各字段含义

QQ截图20221026095107.png

字段名含义
Table表的名称
Non_unique如果索引不能包括重复词，则为0；如果可以，则为1
Key_name索引的名称
Seq_in_index索引中的列序号，从1开始
Column_name列名称
Collation列以什么方式存储在索引中。在 MysQL 中，有值'A'（升序）或 NULL（无分类）
Cardinality索引中唯一值数目的估计值
Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL
Packed指示关键字如何被压缩。如果没有被压缩，则为NULL
Null如果列含有NULL，则含有 YES。如果没有，则该列含有NO
Index_type用过的索引方法（BTREE，FULLTEXT，HASH，RTREE）
Comment备注

4.9 删除索引

drop index 索引名 on 表名;

列子：
drop index name_index on member1;

QQ截图20221026105729.png

alter table 表名 drop index "索引名";

例子：
alter table member1 drop index "cardid";

5.1 删除主键索引

alter table 表名 drop primary key;

列子：
alter table member1 drop primary key;

5.2 explain命令分析

在select命令查看前加上explain，可以查看使用的索引。

explain select * from member1 where name='zhangsan' and id=1\G

QQ截图20221026110646.png

5.3 explain的相关参数

参数含义
possible_keys显示可能应用在这张表中的索引。
key实际使用的索引。如果为NULL，则没有使用索引。
key_len使用的索引的长度。在不损失精确性的情况下，长度越短越好。
ref显示索引的哪一列被使用了，如果可能，则是一个常数。
Extra关于MySQL如何解析查询的额外信息。

6. 总结

索引的作用
加快 {对大表或多表查询时的} 查询速度，对字段排序
降低IO成本，排序成本
唯一性索引还能保证字段值的唯一性
加快多表连接，分组，排序，恢复数据库的速度


索引如何工作的？
没有索引是，每次查询都需要全表扫描来定位某行数据，查询速度慢，IO资源消耗较大
有索引时，会先进行索引查询，然后通过索引值的数据对应的物理地址定位到具体的数据行，从而加快查询速度

创建索引
行数较多的情况下，比如建议在超过300行的表的字段上建议创建索引
建议在 主键，外键字段，表连接字段，分组（group by），排序(order by),where 常使用的字段，唯一性较好的字段上创建索引
不建议在 唯一性差的，会频繁更新的，大文本的字段上创建索引

已存在的表
create index 索引名  on 表（字段（索引长度））；

alter table 表 add index 索引名（字段）；

创建表的时候定义索引
create table 表 （字段 数据类型， ....， index 索引名（字段））；

通过表结构中的索引（无法查看到具体信息）
show create table 表；

以表格的形式查看索引
show index form 表；
show keys form 表；


删除索引
drop index 索引名 on 表；

alter table 表 drop index 索引名；

唯一索引 唯一键  一个表可以有多个索引，唯一索引字段的值允许为空，但不允许重复
create unique index 索引名 on 表 （字段）;

aalter table 表 unique 索引名 （字段）；

create table 表 （字段 数据类型， ....， unique 索引名 （字段））；

主键索引 就是主键  一个表只能有一个主键，主键字段的值不允许为null，也不允许重复
alter table 表 add primary key （字段）；

create table 表（字段 数据类型， ....，primary key, ....）;

alter table 表 drop primary key；

组合索引 有多列组合的索引，可以做普通组合索引，唯一组合索引 （多列组合的值不能重复，其中某个单列可以重复）

create index 索引名 on 表 （字段1， 字段2， ...）;
alter table 表 add index 索引名 （字段1，字段2， ....）；

create unique index 索引名 on 表 （字段1，字段2， ....）；
alter table 表 add unique 索引名 （字段1，字段2， ....）；

select 查询语句中的 where 条件使用的字段顺序必须与组合索引的字段顺序一致，否则组合索引无效

全文索引   只适用于 char varchar  text 类型的字段
create fulltext index 索引名 on 表（字段）；

alter table 表 add fulltext 索引名 （字段）；

create table 表 （字段 数据类型， .....， fulltext 索引名 （字段））；

使用全文索引模糊查询
select * from 表 where match（字段） against('XXXX'）；
avatar
文章
阅读
粉丝