MySQL索引

一、索引概述

Mysql索引的简历对于Mysql的高效运行是很重要的,索引可以大大提高Mysql的检索速度!

创建索引时,需要确保索引是应用在SQL查询语句的条件(一般作为WHERE子句的条件)!

建立索引会占用磁盘空间的索引文件!

每种搜索引擎支持的索引是不同的,如下:

  • MylSAM、InnoDB支持btree索引;
  • Memory支持btree和hash索引;

1.1 索引的优势

  • 加快查询速度;
  • 创建唯一索引可以保证数据表中数据的唯一性;
  • 实现数据的完整性,加速表和表之间的链接;
  • 减少分组和排序的时间

1.2 索引的劣势

  • 创建索引和维护索引需要耗费大量的时间,并且随着数据量的增加所耗费的时间也会有所增加;
  • 索引需要占用磁盘空间,除了数据表占用数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸;
  • 当对表中的数据进行增加、删除和修改时,索引也要动态地维护,这样就降低了数据的维护速度;

二、索引的分类

2.1 唯一索引和普通索引

  • 普通索引:是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值;
  • 唯一索引:索引列的值必须唯一,但允许是空值。如果是组合索引,则列值的组合必须唯一;
  • 主键索引:是一种特殊的唯一索引,不允许有空格;

2.2 单列索引和组合索引

  • 单列索引:即一个索引只包含单个列,一个表中可以有多个单列索引;
  • 组合索引:指在表的多个字段组合上创建的索引。只有在查询条件中使用了这些字段的左边字段时,索引才会被使用。使用组合索引时遵循最左前缀集合;

2.3 全文索引

全文索引类型为FULLTEXT,在定义索引的列上支持全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。Mysql5.7之前只有MylSAM存储引擎支持全文索引。

2.4 空间索引

空间索引是对空间数据类型的字段简历的索引,Mysql中的空间数据类型有4种,分别是:geometry、point、linstring和polygon。MySQL使用SPATIAL关键字进行扩展,使得能够用于创建空间索引的列,必须将其声明为NOT NULL,同样,在MySQL5.7之前,空间索引只能在存储引擎为MyISAM的表中创建。

2.5 创建索引的规则

  • 创建索引并非是越多越好,一个表中如果有大量的索引,不仅占用磁盘空间,而且会影响insert、delete、update等语句的性能,因为当表中的数据更改是,索引也会进行调整和更新;
  • 数据量小的表最好不要创建索引,由于数据较少,查询花费的时间可能比遍历索引的数据还要长;
  • 避免对经常更新的数据创建索引。而经常用于查询的字段应该创建索引;
  • 在条件表达式中经常用到的不同值较多的列创建索引;
  • 当唯一性是某种数据本地的特征时,我们创建唯一索引;
  • 在频繁进行排序或分则的列上建立索引,如果排序的列有多个,可以创建组合索引;

三、创建表的同时创建索引

3.1 创建普通索引

create table book (
bookid int,
bookname varchar(255),
authors varchar(255),
info varchar(255),
comment varchar(255),
year_publication year,
index(year_publication) );
#创建year_publication列为索引列
show create table book\G;
#查看索引
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) DEFAULT NULL,
  `bookname` varchar(255) NULL,
  `authors` varchar(255) NULL,
  `info` varchar(255) NULL,
  `comment` varchar(255) NULL,
  `year_publication` year(4) DEFAULT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
explain select * from book where year_publication=1999\G;
#使用explain判断索引是否正在被使用
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication     #表示使用的索引名称,没有定义名称会使用字段名为索引名
          key: year_publication
      key_len: 2
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

3.2 创建唯一索引

唯一索引主要原因就是减少查询索引列操作的执行时间,尤其是对比较庞大的数据表。与普通索引类似,不同点在于:索引列的值必须唯一,但允许有空值。如果是组合索引,则该列值的组合必须唯一。

create table t1(
id int not null,
name char(30),
unique index Uniqidx(id));
#创建带唯一索引的表
show create table t1\G;
#查看索引
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30)  NULL,
  UNIQUE KEY `Uniqidx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.3 创建单列索引

单列索引:是在数据表中的某一字段上创建的索引,一个表中可以创建多个单列索引。

create table t2 (
id int not null,
name char(50) null,
index singleidx(name) );
#创建单列索引
show create table t2\G;
#查看创建的索引
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) NULL,
  KEY `singleidx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

3.4 创建组合索引

组合索引:是在多个字段上创建一个索引。遵循最左前缀原则。最左前缀 索引最左边的列来匹配行。

create table t3 (
id int not null,
name char(30) not null,
age int not null,
info varchar(255),
index multiidx(id,name,age) );
#创建组合索引
show create table t3 \G;
#查看组合索引
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30)  NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255)  NULL,
  KEY `multiidx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意:组合索引可以起几个索引的作用,但是使用时并不是随意查询哪个字段都是可以使用索引。而是遵循最左前缀:利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

四、在已经存在的表上创建索引

4.1 添加唯一索引

alter table book add unique index idx_book(bookid);
#针对book表添加唯一索引,索引名称为idx_book(可自定义),针对bookid列建立索引

4.2 添加单列(前缀)索引

alter table book add index idx_comment(comment(50));

4.3 添加全文索引

alter table book add fulltext index idx_info(info);

4.4 添加组合索引

alter table book add index idx_auth_info(authors(20),info);

4.5 添加空间索引

create table t7(g geometry not null);
alter table t7 add spatial index idx_spatial(g);

4.6 查看索引

desc book;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| bookid           | int(11)      | YES  | UNI | NULL    |       |
| bookname         | varchar(255) | YES  |     | NULL    |       |
| authors          | varchar(255) | YES  | MUL | NULL    |       |
| info             | varchar(255) | YES  | MUL | NULL    |       |
| comment          | varchar(255) | YES  | MUL | NULL    |       |
| year_publication | year(4)      | YES  | MUL | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
# UNI为唯一索引,MUL为非唯一索引,PRI为主键索引
show index from book;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | idx_book         |            1 | bookid           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book  |          1 | year_publication |            1 | year_publication | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_comment      |            1 | comment          | A         |           0 |       50 | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_auth_info    |            1 | authors          | A         |           0 |       20 | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_auth_info    |            2 | info             | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| book  |          1 | idx_info         |            1 | info             | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#可以清楚的看到创建的索引,及索引与列的对应关系
show index from book\G;
#效果同上

4.7 删除索引

alter table book drop index idx_book;
#使用alter的方式删除索引
drop index idx_info on book;
#使用drop的方式删除

MySQL索引

上一篇:Hive执行SQL步骤


下一篇:SQL----EXISTS 关键字EXISTS基本意思