认识达内从这里开始

MySQL数据库是目前大多数软件编程开发项目都会用到的一个数据库类型，而本文我们就简单来了解一下，MySQL数据库索引创建方法分享。

1.MySQL索引的分类

在创建索引之前了解一下MySQL有哪些索引，然后我们才能选择合适的索引。

常见的索引有，普通索引、索引、主键索引、联合索引、全文索引等。

普通索引

普通索引就是基本的索引，没有任何限制。

可以使用命令创建普通索引：

ALTERTABLE`table_name`ADDINDEXindex_name(`column`);

索引

与普通索引不同，索引的列值必须，允许为null。

创建方式是这样的：

ALTERTABLE`table_name`ADDUNIQUEindex_name(`column`);

主键索引

主键索引是一种特殊的索引，并且一张表只有一个主键，不允许为null。

创建方式是这样的：

ALTERTABLE`table_name`ADDPRIMARYKEY(`column`);

联合索引

联合索引是同时在多个字段上创建索引，查询效率更高。

创建方式是这样的：

ALTERTABLE`table_name`ADDINDEXindex_name(`column1`,`column2`,`column3`);

全文索引

全文索引主要用来匹配字符串文本中关键字。

当需要字符串中是否包含关键字的时候，我们一般用like，如果是以%开头的时候，则无法用到索引，这时候就可以使用全文索引了。

创建方式是这样的：

ALTERTABLE`table_name`ADDFULLTEXT(`column`);

2.哪些字段适合创建索引?

我总结了有以下几条：

2.1频繁查询的字段适合创建索引

一张表的字段总会有冷热之分，很明显那些频繁使用的字段更适合为它创建索引。

2.2在where和on条件出现的字段优先创建索引

为什么不是在select后面出现的字段优先创建索引?

因为查询SQL会先匹配on和where条件的字段，具体的匹配顺序是这样的：

from>on>join>where>groupby>having>select>distinct>orderby>limit

2.3区分度高的字段适合创建索引

比如对于一张用户表来说，生日比性别的区分度更高，更适合创建索引。

可以使用下面的方式手动统计一下，每个字段的区分度，值越大，区分度越高：

select

count(distinctbirthday)/count(*),

count(distinctgender)/count(*)

fromuser;

对于已经创建好的索引，我们还可以使用MySQL命令查看每个索引的区分度排名：

图中Cardinality列表示索引的区分度排名，也被称为基数。

2.4有序的字段适合创建索引

有序的字段在插入数据库的过程中，仍能保持B+树的索引结构，不需要频繁更新索引文件，性能更好。

3.哪些字段不合适创建索引?

说完哪些字段适合创建索引，就有不适合创建索引的的字段。

3.1区分度低的字段不适合创建索引。

刚才说了用户表中性别的区分度较低，不如生日字段适合创建索引。

3.2频繁更新的字段不适合创建索引

更新字段的过程中，需要维护B+树结构，会频繁更新索引文件，降低SQL性能。

3.3过长的字段不适合创建索引

过长的字段会占用更多的空间，不适合创建索引。

3.4无序的字段不适合创建索引

无序的字段在插入数据库的过程中，为了维护B+树索引结构，需要频繁更新索引文件，性能较差。

4.创建索引的其他注意事项

4.1优先使用联合索引

查询的时候，联合索引比普通索引能更精准的匹配到所需数据。

图中就是在(age，name)两个字段上建立的联合索引，在B+树中的存储结构。

可以看出，是先age排序，age相等的数据，再按name排序。

对于这条查询SQL：

selectage,namefromuserwhereage=18andname='李四';

联合索引只需一次就可以查到所需数据，如果我们只在age字段上建立索引，会先匹配到age=18的三条数据，然后再逐个遍历，效率更差，所以平时应该优先使用联合索引。

4.2使用联合索引时，区分度的字段放前面

这样可以减少查询次数，更快地匹配到所需数据。

4.3过长字符串可以使用前缀索引

比如在匹配用户地址的时候，如果乡镇已经能区分大部分用户了，就没必要精确到街道小区了。

创建普通索引的时候，指定索引长度，就可以创建前缀索引了。

ALTERTABLE`user`ADDINDEXidx_address(address(3));

4.4值的字段，使用索引

使用索引，可以避免程序bug导致产生重复数据。

4.5排序和分组字段也尽量创建索引

在orderby和groupby中的字段也尽量创建索引，避免使用文件排序，可以使用索引排序提供性能。

4.6避免创建过多索引

索引好用，适度即可。创建过多的索引，会占用更多存储空间，也会严重影响SQL性能，每次更新SQL，都需要更新大量索引文件，得不偿失。

【免责声明】：本内容转载于网络，转载目的在于传递信息。文章内容为作者个人意见，本平台对文中陈述、观点保持中立，不对所包含内容的准确性、可靠性与完整性提供形式地保证。请读者仅作参考。更多内容请加danei0707学习了解。欢迎关注“达内在线”参与分销，赚更多好礼。