Posted in mysql

Mysql索引简介，设计，创建与查看

   2019年4月9日  6,644  Leave a comment
Mysql索引简介，设计，创建与查看

索引在数据库中的作用是快速找出某个列中一个特定值的行，不使用索引的话，MySQL 必须从第一条记录遍历到相关行，表数据越大，花费的时间越多，但是如果有索引，就能快速的到达某个位置去搜索数据文件。

一、索引的优缺点

（一）、索引的优点：

  • 通过创建唯一索引，可以保证数据库每一行数据的唯一性
  • 可以大大提高查询速度
  • 可以加速表与表的连接
  • 可以显著的减少查询中分组和排序的时间。

（二）、索引的缺点

  • 创建索引和维护索引需要时间，而且数据量越大时间越长
  • 创建索引需要占据磁盘的空间，如果有大量的索引，可能比数据文件更快达到最大文件尺寸
  • 当对表中的数据进行增加，修改，删除的时候，索引也要同时进行维护，降低了数据的维护速度

二、索引的分类及设计原则

（一）、索引的分类

  1. 普通索引(Normal)：基本索引类型，允许在定义索引的列里插入空值或重复值。
  2. 唯一索引(Unique)：索引列值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
  3. 主键索引：是一种特殊的唯一索引，不允许有空值。
  4. 单列索引：只包含一个列的索引，一个表中可以有多个。
  5. 组合索引：包含多个列的索引，查询条件包含这些列的最左边的字段的时候，索引就会被引用，遵循最左缀原则。
  6. 全文索引(Full Text):在定义的值中支持全文查找，允许空值和重复值，可以在 CHAR，VARCHAR 或者 TEXT 字段类型上创建，仅支持 MyISAM 存储引擎。
  7. 空间索引：针对空间数据做的索引，支持的数据类型有 4 种，分别是 GEOMETRY,POINT,LINESTRING 和 POLYGON。创建空间索引的列必须声明为非空值（NOT NULL），仅支持 MyISAM 存储引擎。

（二）、索引的设计原则

  1. 不是越多越好
  2. 常更新的表越少越好
  3. 数据量小的表最好不要建立索引
  4. 不同的值比较多的列才需要建立索引
  5. 某种数据本身具备唯一性的时候，建立唯一性索引，可以保证定义的列的数据完整性，以提高查询速度
  6. 频繁进行排序或分组的列(group by 或者是 order by)可以建立索引，提高搜索速度
  7. 经常用于查询条件的字段应该建立索引

三、索引的创建

（一）、在创建表的时候创建索引

在创建数据表时创建索引的基本语法结构：

CREATE  TABLE  table_name(
    属性名  数据类型[约束条件],
    ……
    属性名  数据类型
    [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
    [别名] (属性名 1 [(长度)] [ASC | DESC] , 属性名 2 [(长度)] [ASC | DESC] , 
    ...)
);

属性值的含义如下:

  • UNIQUE: 可选参数，表示索引为唯一索引。
  • FULLTEXT:  可选参数，表示索引为全文索引。
  • SPATIAL:  可选参数，表示索引为空间索引。
  • INDEX  和 KEY 参数用于指定字段为索引的，用户在选择时，只需要选择其中的一种即可。
  • “别名” : 为可选参数，其作用是给创建的索引取新名称。
  • 属性名 1:  指索引对应的字段名称，该字段必须被预先定义。
  • 长度:  可选参数，其指索引的长度，必须是字符串类型才可以使用。
  • ASC/DESC: 可选参数，ASC 表示升序排列，DESC 表示降序排列。
1、创建普通索引

创建普通索引，即不添加 UNIQUE、FULLTEXT 等任何参数。

【例】创建表名为 score 的数据表，并在该表的 id 字段上建立索引，SQL 语句如下:

mysql> CREATE table score(
    -> id  int(11)  AUTO_INCREMENT  primary  key  not  null,
    -> name  varchar(50)  not null,
    -> math  int(5)  not null,
    -> English  int (5)  not null,
    -> Chinese  int (5) not  null,
    -> index(id)
    -> );
2、创建唯一索引

创建唯一索引时，使用 UNIQUE 参数进行约束。

【例】创建表名为 address 的数据表，并在该表的 id 字段上建立唯一索引，SQL 语句如下:

mysql> CREATE  table address(
    -> id  int(11)  auto_increment  primary  key  not  null,
    -> name  varchar(50),
    -> address  varchar(200),
    -> UNIQUE  INDEX  address(id  ASC)
    -> );
3、创建全文索引

全文索引只能作用在 CHAR、VARCHAR、TEXT、类型的字段上。创建全文索引需要使用 FULLTEXT 参数进行约束。

【例】创建表名为 cards 的数据表，并在该表的 name 字段上建立全文索引，SQL 语句如下:

mysql> create  table cards(
    -> id int(11)  auto_increment  primary key  not  null,
    -> name  varchar(50),
    -> number  bigint(11),
    -> info  varchar(50),
    -> FULLTEXT  KEY  cards_number(name)
    -> );
4、创建单列索引

创建单列索引，即在数据表的单个字段上创建索引。创建该类型索引不需要引入约束参数，用户在建立时只需要指定单列字段名，即可创建单列索引。

【例】创建名称为 telephone 的数据表，并指定在 tel 字段上建立名称为 tel_num 的单列索引，SQL 语句如下：

mysql> create  table  telephone(
    -> id  int(11)  primary key auto_increment  not  null,
    -> name  varchar(50)  not  null,
    -> tel  varchar(50)  not null,
    -> index (tel)
    -> );
5、创建多列索引

创建多列索引即指定表的多个字段即可实现。

【例】创建名称为 information 的数据表，并指定 name 和 sex 为 多列索引，SQL 语句如下：

mysql> create table  information(
    -> inf_id  int(11)  auto_increment  primary  key  not  null,
    -> name  varchar(50)  not  null,
    -> sex  varchar(5)  not null,
    -> birthday  varchar(50)  not  null,
    -> index  info(name,sex)
    -> );

需要注意的是，在多列索引中，只有查询条件中使用了这些字段中的第一个字段(即上面示例中的 name 字段)，索引才会被使用。 触发多列索引的条件是用户必须使用索引的第一字段，如果没有用到第一字段，则索引不起任何作用，用户想要优化查询速度，可以应用该类索引形式。

6、创建空间索引

创建空间索引时，需要设置 SPATIAL 参数。同样，必须说明的是，只有 MyISAM 类型表支持该类型索引。而且，索引字段必须有非空约束。

【例】创建一个名称为 list 的数据表，并创建一个名为 listinfo 的空间索引，SQL 语句如下:

mysql> create  table  list(
    -> id  int(11)  primary  key  auto_increment  not null,
    -> goods  geometry  not  null,
    -> SPATIAL  INDEX  listinfo(goods)
    -> )engine=MyISAM;

goods  字段上已经建立名称为  listinfo 的空间索引，其中  goods  字段必须不能为空，且数据类型是  GEOMETRY，该类型是空间数据类型。空间类型不能用其他类型代替，否则在生成空间素引时会产生错误且不能正常创建该类型索引。

空间类型除了上述示例中提到的 GEOMETRY 类型外，还包括如  POINT、LINESTRING、POLYGON  等类型，这些空间教据类型在平常的操作中很少被用到。

（二）、已存在表上创建索引

1、添加 PRIMARY KEY（主键索引）：

ALTER TABLE table_name ADD INDEX index_name (column)

2、添加 UNIQUE(唯一索引) ：

ALTER TABLE table_name ADD UNIQUE (column)

3、添加 INDEX(普通索引) ：

ALTER TABLE table_name ADD INDEX index_name (column)

4、添加 FULLTEXT(全文索引) ：

ALTER TABLE table_name ADD FULLTEXT (column)

5、添加多列索引： 

ALTER TABLE table_name ADD INDEX index_name (column1,column2,column3)

四、查看索引是否引用

使用 EXPLAIN 查看索引是否使用（注意要先往表中插入相应的数据,至少两条）

EXPLAIN SELECT * FROM table_name WHERE column='value';
Mysql索引简介，设计，创建与查看-打不死的小强

查询结果的字段详解:

  • select_type: 查询类型，可能的值:SIMPLE,PRIMARY,UNION,SUNQUERY 等
  • table: 表名,按被读取的先后顺序查询
  • partitions: 如果查询是基于分区表的话，会显示查询将访问的分区
  • type: 与其他表的关系，可能的值:ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
  • possible_key: 搜索记录可选用的各个索引
  • key: 实际选用的索引
  • key_len: 索引按字节计算的长度，
  • ref: 行给出关联关系中另一个数据表里的数据列的名称
  • rows: 预计读出的行数
  • filtered: 按条件过滤的行的百分比
  • extra: 与关联操作相关的信息

五、常见索引原则

1．选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

2 ．为经常需要排序、分组和联合操作的字段建立索引：

3．为常作为查询条件的字段建立索引。

4．限制索引的数目：
越多的索引，会使更新表变得很浪费时间。

5．尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。

6．尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。

7．删除不再使用或者很少使用的索引

8 . 最左前缀匹配原则，非常重要的原则。

9 . 尽量选择区分度高的列作为索引
区分度的公式是表示字段不重复的比例

10．索引列不能参与计算，保持列“干净”：带函数的查询会使索引失效（查询条件使用函数，即”=”右边使用函数可以使用索引） 。

11．尽量的扩展索引，不要新建索引。

六、总结

  • 需要明白索引的优缺点，再选择需要用的索引
  • 最好在建表前就确定每张表的索引字段
赏

发表评论

邮箱地址不会被公开。 必填项已用*标注