索引用于快速找出在某个列中有一特定值的行。
不使用索引，mysql必须从第1条记录开始读完整个表，直到找出相关行。表越大，查询数据所花的时间越多。
如果表中查询的列有一个索引，mysql能快速到达某个位置去搜寻数据文件，而不必查看所有数据。
1、简介
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。
2、索引的含义和特点
索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。
使用索引可以快速找出在某个或多个列中有一特定值的行，所有mysql列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。
索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。
根据存储引擎定义每个表的最大索引数和最大索引长度。
所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。
大多数存储引擎有更高的限制。
Mysql中索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关。
MyISAM和InnoDB存储引擎只支持BTREE索引。
MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
索引的优点主要有以下几条：
（1）通过创建唯一索引，可以保证数据库中每一行数据的唯一性。
（2）可以大大加快数据的查询速度，可以加速表和表之间的连接。
（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。
（4）在使用分组和排序子句进行数据查询时，也可以显著 减少查询中分组和排序的时间。
增加索引也有许多不利的方面，主要表现在如下几个方面：
（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果有大量的索引，索引文件可以能比数据文件更快达到最大文件尺寸。
（3）当对表中的数据进行增加、删除和修改的时间，索引也要动态地维护，这样就降低了数据的维护速度。
3、索引分类
Mysql的索引可以分为以下几类：
1、普通索引和唯一索引
普通索引是mysql中的基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。
2、单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
3、全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。mysql中只有MyISAM存储引擎支持全文索引。
4、空间索引
空间索引是对空间数据类型的的字段建立的索引，mysql中的空间数据类型有4中，分别是GEOMETRY、POINT、LINESTRING和POLYGON。mysql使用SPATIAL关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建空间索引的列，必须使其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。
5、索引的设计原则
索引设计不合理或缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时，应该考虑以下准则：
（1）索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。
（2）避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。
（3）数据量小的表最好不要使用索引，由于数据较少，查询话费的时间可能比遍历索引的时间还要端，索引可能不会产生优化效果。
（4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的性别字段上只有男与女两个不同值，因此就无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
（5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
（6）在频繁进行排序或分组（即进行group by 或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。
6、创建索引
Mysql支持多种方法在单个或多个列上创建索引：在创建表的定义语句create table中指定索引列，使用alter table 语句在存在的表上创建索引列，或者使用create index语句在已存在的表上添加索引。
6.1、创建表的时候创建所以
使用CREAE TABLE创建表时，除了可以定义列的数据类型，还可以定义主键约束、外键约束或者唯一性约束，而不论创建那种约束，在定语约束的同时相当于在指定列上创建了一个索引。
CREATE TABLE table_name [col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；INDEX和KEY为同义词，两者作用相同，用来指定创建索引；col_name为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；index_name指定索引的名称，为可选参数，如果不指定，mysql默认col_name为索引值；langth为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；ASC或DESC指定生序或者降序的索引值存储。
创建普通索引
最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。
create table user (
`id` int(11) not null auto_increment,
`mobile` varchar(11) default null comment '手机号',
`password` varchar(64) default null comment '密码',
`nickname` varchar(50) default null comment '昵称',
primary key(`id`),
index index_mobile (`mobile`)
);
进入控制台执行语句，输入密码后进入mysql操作台：
mysql -u root -p
该语句执行完毕之后，使用SHOW CRWEATE TABLE查看表结构
mysql> show create table user;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mobile` varchar(11) DEFAULT NULL COMMENT '手机号',
`password` varchar(64) DEFAULT NULL COMMENT '密码',
`nickname` varchar(50) DEFAULT NULL COMMENT '昵称',
PRIMARY KEY (`id`),
KEY `index_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
由结果可以看到，user表的mobile字段成功建立了索引。使用EXPLAIN语句查看索引是否正在使用：
mysql> explain select * from user where mobile = '15136204782';
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user | NULL | ref | index_mobile | index_mobile | 14 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
发布时间 : 2024-03-13，阅读量：1130 , 分类： MySql
