mysql创建索引需要注意什么 mysql创建索引后怎么用

转载

laojean 2023-08-15 23:47:42

文章标签 mysql创建索引需要注意什么 mysql 数据库 sql 数据 文章分类 MySQL 数据库

一、为什么要使用索引

        索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。
        索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现数据的快速检索。
        在 MySQL 中，通常有以下两种方式访问数据库表的行数据：
顺序访问
        顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

索引访问
        索引访问是通过遍历索引来直接访问表中记录行的方式。
        使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。
        例如，在学生基本信息表 tb_students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表。当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。
        简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

二、索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

1、索引的优点

索引的优点如下：
通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
可以给所有的 MySQL 列类型设置索引。
可以大大加快数据的查询速度，这是使用索引最主要的原因。
在实现数据的参考完整性方面可以加速表与表之间的连接。
在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

2、索引的缺点

增加索引也有许多不利的方面，主要如下：
创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。
使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，创建索引。

三、按索引的存储方式区分

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

四、按索引的逻辑区分

1、普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引。
CREATE INDEX index_id ON tb_student(id);

2、唯一索引

唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。
下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：
CREATE UNIQUE INDEX index_id ON tb_student(id);

3、主键索引

        顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
        主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
        创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4、空间索引（了解一下）

空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。
创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为MyISAM 的表中创建。
空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。
下面在 tb_student 表中的 line 字段上建立名为 index_line 的索引，SQL 语句如下：
CREATE SPATIAL INDEX index_line ON tb_student(line);
其中，tb_student 表的存储引擎必须是 MyISAM，line 字段必须为空间数据类型，而且是非空的。

5、全文索引（了解一下）

全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。
创建全文索引使用 FULLTEXT 关键字。
在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：
CREATE FULLTEXT INDEX index_info ON tb_student(info);
其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和TEXT 等类型。

五、添加索引

1、添加普通索引：

语法：
create index 索引名 on 表名(作为索引的字段);
create index index_id on students(id);

2、添加唯一索引：

语法：
create unique index 索引名 on 表名(作为索引的字段);
① 创建唯一索引
create unique index u_index_id on students(id);
② 查看索引；

3、添加主键索引：

语法：
alter table 表名 add primary key 索引名 (表中的字段);
alter table students add primary key pri_index (name);
【将students 表中的name字段作为主键索引，索引名为 pri_index】

六、显示表中的索引

语法：
show indexes from 表名;
① 显示所有的数据库；

② 切到数据库；

③ 查看Class_Linux数据库中的表；

④ 查看students表中的索引；
show indexes from students;

Table 表的名称。Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。Key_name 索引的名称。Seq_in_index索引中的列序列号，从1开始。Column_name 列名称。Collation列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。Null如果列含有NULL，则含有YES。如果没有，则该列含有NO。Index_type用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。