关于MySQL索引，看完这篇文章，收藏使用不香吗？

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，并不是所有的存储引擎都支持所有的索引类型。下面来详细总结一下MySQL索引。

MySQL索引的类型有一下几种：

第一、普通索引

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。

ALTER TABLE `t_user` ADD INDEX `idx_user_username` (`username`);

第二、唯一索引

唯一索引的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

ALTER TABLE `t_user` ADD UNIQUE `idx_user_username` (`username`);

第三、主键索引

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

CREATE TABLE `t_user` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',

`username` varchar(16) NOT NULL COMMENT '用户名',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户';

第四、组合索引

CREATE TABLE mytable(

ID INT NOT NULL,

username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL,

age INT NOT NULL

);

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 username, city, age 建到一个索引里。

ALTER TABLE mytable ADD INDEX name_city_age (username(10), city, age);

建表时，usernname 长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname, city, age

usernname, city

usernname

为什么没有 city, age 这样的组合索引呢?这是因为 MySQL 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个 SQL 就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="长春"

SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="长春"

SELECT * FROM mytable WHREE city="长春"

如果分别在 usernname, city, age 上建立单列索引mysql表索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但 MySQL 只能用到其中的那个它认为似乎是最有效率的单列索引。

MySQL使用索引的优缺点总结：

使用索引的优点

1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性。

2.建立索引可以大大提高检索的数据,以及减少表的检索行数。

3.在表连接的连接条件 可以加速表与表直接的相连。

4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)。

5.建立索引,在查询中使用索引 可以提高性能。

使用索引的缺点

1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加。

2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间。

3.当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

最后告诉大家使用索引需要注意的地方

在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的，一般来说：

1.在经常需要搜索的列上,可以加快索引的速度。

2.主键列上可以确保列的唯一性。

3.在表与表的而连接条件上加上索引,可以加快连接查询的速度。

4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)。

5.在一些where 之后的 = ,BETWEEN ,IN 以及某个情况下的like 建立字段的索引(B-TREE)。

6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick '%ABC%' 那么这个索引讲不会起到作用.而nickname lick 'ABC%' 那么将可以用到索引。

7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' '字符串。

8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的..)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作。

9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描。

10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快。

（编辑：徐州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!