MySQL索引详细介绍及入门使用
MySQL索引详细介绍及入门使用
一、什么是索引？
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
举例说明：
例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。其中MySQL中的索引的存储类型有两种：BTREE、HASH。 也就是用树或者Hash值来存储该字段，要知道其中详细是如何查找的，就需要会算法的知识了。我们现在只需要知道索引的作用，功能是什么就行。
二、Mysql中索引的优点、缺点、使用原则
优点：
- 所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引。
- 大大加快数据的查询速度。
缺点：
- 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
- 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值。
- 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
使用原则：
通过上面说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，而是需要自己合理的使用。
- 对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引。
- 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
- 在一同值少的列上(字段上)不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可以建立索引。
三、索引的分类
索引大致可以分为四种：单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引
1、单列索引
- 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
- 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
- 主键索引：是一种特殊的唯一索引，不允许有空值。
2、组合索引
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
3、全文索引
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
4、空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
四、索引操作
创建索引
方式一：创建表时，添加索引
-- 示例
CREATE TABLE `user` (
`id` int(11) NOT NULL COMMENT 'id',
`username_` varchar(255) DEFAULT NULL COMMENT '用户名',
`password_` varchar(255) DEFAULT NULL COMMENT '密码',
`sex_` int(1) DEFAULT NULL COMMENT '性别',
`info_` varchar(500) DEFAULT NULL COMMENT '说明',
PRIMARY KEY (`id`),
KEY `username_index` (`username_`) USING BTREE COMMENT '用户名字段添加索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
方式二：创建表后，添加索引
格式：ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
-- 示例 添加普通索引
ALTER TABLE `user` ADD INDEX sex_index(sex_)
格式：CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])[ASC|DESC]
-- 示例 添加普通索引
CREATE INDEX AgeIndex ON `user`(age_);
删除索引
方式一：使用ALTER TABLE 关键字
格式：ALTER TABLE 表名 DROP INDEX 索引名
-- 示例
ALTER TABLE `user` DROP INDEX AgeIndex
方式二：使用DROP INDEX关键字
格式：DROP INDEX 索引名 ON 表名
-- 示例
DROP INDEX AgeIndex ON `user`
参考：
