MySQL索引

最新推荐文章于 2024-06-17 15:31:11 发布

原创最新推荐文章于 2024-06-17 15:31:11 发布 · 249 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

21 篇文章

订阅专栏

本文详细介绍了MySQL数据库中的索引，包括其作用、优缺点、不同类型的索引（普通、唯一、主键、组合、全文）以及创建和管理索引的方法。索引能提升查询速度，但也会影响数据更新的效率。合理选择索引列和类型，如选择唯一性、常用查询条件的列，可以有效优化数据库性能。

任何标准表最多可以创建 16 个索引列。

1、索引是什么？为什么使用它？优缺点：

索引概念：

索引 是种特殊的数据库结构，由数据表中的一列或多列组合而成，可用来快速查询数据表中有某一特定值的记录。

为什么使用索引：

索引可以进行数据性能调优，实现快速检索。

MYSQL通常有以下两种访问数据库表的行数据的方式：

1、顺序访问

2、索引访问

简而言之，不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

优缺点：

优点

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

   * 可以给所有的 MySQL 列类型设置索引。

   * 可以大大加快数据的查询速度，这是使用索引最主要的原因。

   * 在实现数据的参考完整性方面可以加速表与表之间的连接。

   * 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点

   * 创建和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

   * 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。

   * 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

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

MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。

2、索引类型：

1、普通索引

概念：

最基本的索引，无任何限制，用于加速查询

创建方法：

建表时一起创建 CREATE TABLE mytale (name VARCHAR(10), INDEX index_mytable_name(name));

建表后直接创建索引 CREATE INDEX index_mytale_name ON mytable(name);

修改表结构 ALTER TABLE mytable ADD INDEX index_mytable(name);

注：如果是字符串字段，还可以指定索引的长度，在列命令后面加上索引长度就可以了（例如:name(11)）

2、唯一索引

概念：

索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一。创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

创建方法：

建表时一起创建 CREATE TABLE mytale (name VARCHAR(10), UNIQUE index_unique_mytable_name(name));

建表后直接创建索引 CREATE UNIQUE INDEX index_mytale_name ON mytable(name);

修改表结构ALTER TABLE mytable ADD UNQIUE INDEX index_mytable(name);

3、主键索引

概念：

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

自增长的列必须设置为主键key，否则汇报如下错误：

创建方法：

建表时一起创建CREATE TABLE mytable (id int(11) NOT NULL AUTO_INCREMENT,name VARCHAR(10), PRIMARY KEY(id));

修改表结构ALTER TABLE mytable ADD PRIMARY KEY ('id') ;

4、组合索引

概念：

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

创建方法：

建表时一起创建 CREATE TABLE mytable (id int(11) NOT NULL AUTO_INCREMENT,name VARCHAR(10), INDEX index_mytable_name(id,name)) ;

建表后直接创建索引 CREATE INDEX index_mytale_name ON mytable(id,name);

修改表结构ALTER TABLE mytable ADD INDEX index_mytale_name (id,name) ;

5、全文索引

概念：

主要用来查找文本中的关键字，而不是直接与索引中的值比较。

fulltext索引跟其他索引不大一样，更像一个搜索引擎，不是简单地where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。

它可以在create table，alter table ，create index使用， 不过目前只有char、varchar，text 列上可以创建全文索引， 存储引擎必须是 MyISAM

创建方法：

建表时一起创建 CREATE TABLE mytable(id int(11) NOT NULL AUTO_INCREMENT, title char(250) NOT NULL, contents text NULL, create_at int(10) NULL DEFAULT NULL, PRIMARY KEY(id) , FULLTEXT(contents ));

建表后直接创建索引 CREATE FULLTEXT INDEX index_mytable_contents ON mytable(contents);

修改表结构ALTER TABLE mytable ADD FULLTEXT INDEX index_mytale_contents (contents) ;

索引随可以提高查询速率，但对于更新、创建、删除操作，需要额外的维护索引，这会导致性能受影响，因此，索引不能建立的太多。

一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

3、创建索引：

"column_name" 规定需要索引的列, index_name索引名 CREATE ( UNIQUE) INDEX index_name ON table_name ( LastName, FirstName )	用于在表中创建索引更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引 UNIQUE 唯一的索引意味着两个行不能拥有相同的索引值
DROP INDEX index_name ON 'talbe_name' ALTER TABLE 'table_name' DROP INDEX index_name ALTER TABLE 'table_name' DROP PRIMARY KEY ALTER TABLE 'table_name' DROP FOREIGN KEY fk_symbol	撤销索引
ALTER TABLE 'table_name' ADD INDEX index_name (column_list) #普通索引 ALTER TABLE 'table_name' ADD UNIQUE (column_list) #唯一索引 ALTER TABLE 'table_name' ADD PRIMARY KEY (column_list) #主键索引 ALTER TABLE 'table_name' ADD FULLTEXT (column_list) #全文索引 ALTER TABLE 'table_name' ADD INDEX index_name (column_list1,column_list2) #组合索引	添加索引
show index from 'table_name'	查看表的索引
ALTER table 'table_name' disable keys ALTER table 'table_name' enable keys	禁用索引重开索引插入记录时，MYSQL会根据表的索引对插入的记录排序，这会影响插入速度，为解决此情况，在插记录前先禁用索引，等记录插完后再开启索引

4、查看表的索引：show index from 'table_name'

mysql> show index from  applications;
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table        | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| applications |          1 | index_name |            1 | deviceid    | A         |          95 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

列表中字段的含义：

Non_unique：若索引不能包括重复值，则为0，若可以则为1；
Key_name：索引的名称
Seq_in_index：索引中的虚列号，从1开始
Column_nameL：列名称
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）