mysql的索引详解-白红宇

mysql的索引详解

阅读量：4885 次

发布时间：2019-06-11

本文共 4207 字，大约阅读时间需要 14 分钟。

什么是索引：

索引即数据库通过一定的组织方式（B-tree等）把创建索引列上数值和位置存储已达到快速查询的目的。

索引的种类：

(1) 主键索引，一张表指定主键，自动创建索引。

(2) 唯一性索引，列值不能相同，但相对于主键索引可以为空。

(3) FULLTEXT 是全文索引，用于搜索长文本.

(4) 普通索引没有以上限制

有个别人举得很好的例子：

这个系统有一个会员表

有下列字段：

会员编号 INT

会员姓名 VARCHAR(10)

会员身份证号码 VARCHAR(18)

会员电话 VARCHAR(10)

会员住址 VARCHAR(50)

会员备注信息 TEXT

那么这个会员编号，作为主键，使用 PRIMARY

会员姓名如果要建索引的话，那么就是普通的 INDEX

会员身份证号码如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

会员备注信息，如果需要建索引的话，可以选择 FULLTEXT，全文搜索。

不过 FULLTEXT 用于在长文本建立索引效果最好，用在比较短的文本，普通的 INDEX 就可以了。

索引的建立、删除、查看

mysql> desc card_info;

+--------+-------------+------+-----+---------+-------+

+--------+-------------+------+-----+---------+-------+

| number | int(8) | NO | PRI | NULL | |

| phone | int(11) | YES | | NULL | |

| id | int(18) | NO | UNI | NULL | |

+--------+-------------+------+-----+---------+-------+

1．主键索引指定主键时，已经默认创建,id也会产生一个unique索引

mysql> show index from card_info;

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| card_info | 0 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2 rows in set (0.00 sec)

2．创建一个普通索引

create index index_name on card_info(name);

mysql> show index from card_info;

+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| card_info | 0 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |

+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3 rows in set (0.00 sec)

3．创建fulltext索引

alter table card_info add fulltext index index_info(info)

4．删除索引

5． mysql> alter table card_info drop index index_name;

6．创建联合索引

create index name_phone on card_info(name,phone);

索引的选择与注意事项

创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

复合索引
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。