06-MySQL索引

最新推荐文章于 2020-04-07 11:46:20 发布

BIGEC

最新推荐文章于 2020-04-07 11:46:20 发布

阅读量284

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/u012666996/article/details/80074300

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

06-MySQL索引

索引简介

索引用于快速找出在某个列中有一特定值的行。如果不使用索引，MySQL必须从第一条记录开始读完整张表，直到找到相关的行。表越大，查询时间越长。

索引含义和特点

索引是一个单独的、存储在磁盘上的数据库结构。它们包含着对数据库表里所有记录的引用指针。使用索引用于快速找出在某个或者多个列中有一特定值的行。所有的MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，每种存储引擎的索引都不一定完全相同，且每种索引引擎不一定支持所有索引类型。

所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。MySQL中索引的存储类型有两种：BTREE 和HASH，表的存储引擎MyISAM和InnoDB只支持BTREE索引；MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。MySQL存储引擎选择.

优缺点

优点：

通过建立唯一索引，可以保证数据库表中的每一行数据的唯一性。
可以大大加快数据的查询速度
在实现数据的参考完整性方面，可以加速表和表之间的链接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间

缺点：

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

索引分类

普通索引和唯一索引

普通索引：MySQL中基础的索引类型，允许在定义索引的列中插入重复值和空值。

唯一索引：索引列必须唯一，但是允许有空值，如果是组合索引，则列值的组合必须唯一，主键索引是一种特殊的唯一索引，不允许有空值。
单列索引和组合索引

单列索引：一个索引只包含单列，一个表可以有多个单列索引。

组合索引：指在表的多个字段组合上创建的索引，只有在和查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀原则。
全文索引

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

空间索引是对空间索引数据类型的字段建立的索引，MySQL中的空间数据类型有4中，分别是：GEOMETRY、POINT、LINESTRING、和POLYGON。MySQL使用SPATIAL关键字进行拓展，使得能够用于创建正规索引类的语法创建空间索引。创建空间索引的列，必须将其生命为NOT NULL ，空间索引只能在存储引擎为MyISAM的表中创建。

索引的设计原则

少而精：一个表中如有大量索引，不仅占空间，还影响insert/delete/update的性能。
避免对经常更新的表进行过多的索引，索引列尽可能少。查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如学生表中的性别字段上只有“男”与“女”两个不同值，因此就无需建立索引。
当唯一性是某种数据本身的特性时，指定唯一索引。使用唯一索引要确保数据的完整性。
在频繁进行排序（order by）或者分组（group by）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

创建索引

在创建表的同时，创建索引

语法：

create table table_name [col_name data_type]
[unique|fulltext|spatial][index|key][index_name](col_name[length])[ASC|DESC]

参数	是否可选	备注
unique	可选参数	唯一索引
fulltext	可选参数	全文索引
spatial	可选参数	空间索引
index/key	否	指定创建索引
index_name	可选参数	索引名称，如果不指定，MySQL默认col_name为索引值
col_name	否	索引字段列、必须从数据表中定义的多个列中选择
length	可选参数	表示索引的长度，只有字符串类型的字段才能指定索引长度
ASC/DESC	否	指定升序或者降序的索引值存储

普通索引

最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度

create table book (
    bookid int not NULL,
    bookname varchar(255) not NULL,
    authors varchar(255) not NULL,
    info varchar(255)   NULL,
    COMMENT varchar(255) NULL,
    year_publication YEAR not NULL,
    INDEX(year_publication) -- 创建索引
);

mysql> show create table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `COMMENT` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> explain select * from book where year_publication=1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

行	含义
select_type	指定所使用的SELECT查询类型，SIMPLE(不包含子查询的简单select)，PRIMARY（需要Union操作或含子查询的select），UNION（包含union结果的数据表），SUBQUERY（）
table	查询需要用到的表
type	本数据表和其他数据表之间的关联关系，system,const,ref,eq_ref,range,index,ALL
possible_keys	MySQL在搜索数据记录时刻选用的各个索引
key	MySQL实际选用的索引
key_len	索引按照字节计算的长度，key_len越小，表示越快
ref	关联关系中另一个数据表里的数据列的名字
rows	MySQL在执行这个查询时预计会从这个数据表里读出的数据行的个数
ex	提供了与关联操作有关的信息

从查询结果中可以看出possible_keys和key都是year_publication，说明查询时使用了索引。

唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。他与前面的普通索引类似，不同的就是：索引列的值必须是唯一，允许null值，如果是组合索引，则列值的组合必须唯一。

create table book_unique(
    id int not NULL,
    name char(30) not NULL,
    UNIQUE INDEX UniqIdx(id)
)

mysql> show create table book_unique \G
Table: book_unique
Create Table: CREATE TABLE `book_unique` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

单列索引

create table book_single(
    id int not NULL,
    name char(30) not NULL,
    INDEX SingleIdx(name)
)

mysql> show create table book_single \G；
       Table: book_single
Create Table: CREATE TABLE `book_single` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

由查询结果可以看出，id字段上已经成功建立了一个名为SingleIdx的单列索引，索引长度为20。

组合索引

组合索引是在多个字段上创建的一个索引

create table book_multi(
    id int not NULL,
    name char(30) not NULL,
    age int not null,
    info varchar(255),
    INDEX MultiIdx(id,name,age)
)

mysql> show create table book_multi\G；
       Table: book_multi
Create Table: CREATE TABLE `book_multi` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

可以看到，id、name和age字段上已经成功建立了名为MultiIdx的组合索引。

该索引遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集成为最左前缀。本例子中索引行中按照id/name/age的顺序存储，索引可以搜索以下组合（id,name,age）、(id,name)、(id),MySQL不能使用局部索引，如（age）或者（name,age）没有使用索引。

mysql> explain select * from book_multi  where age=11 AND name = "eric"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book_multi
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL   -- 没有使用索引
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

全文索引

FULLTEXT（全文索引）可以用于全文搜索，只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列创建索引。

create table book_fulltext(
    id int not NULL,
    name char(30) not NULL,
    age int not null,
    info varchar(255),
    FULLTEXT INDEX FullTextIndex(info)
) ENGINE=MyISAM;

mysql> show create table book_fulltext \G;
*************************** 1. row ***************************
       Table: book_fulltext
Create Table: CREATE TABLE `book_fulltext` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FullTextIndex` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

非常适合适合大型数据集，对于小的数据集，它的用处非常小。

空间索引

create table book_spat(
    g geometry not NULL, SPATIAL INDEX spatidx(g)
) ENGINE = myISAM;

mysql> show create table book_spat \g;
+-----------+-------------------------------------------------------------------
-----------------------------------------------------+
| Table     | Create Table
                                                     |
+-----------+-------------------------------------------------------------------
-----------------------------------------------------+
| book_spat | CREATE TABLE `book_spat` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatidx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-----------+-------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)

创建要求，必须是非空字段，并且表的存储引擎为MyISAM.

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

使用ALTER TABLE 创建索引

语法：

alter table table_name add [unique|fulltext|spatial][index|key][index_name]
(col_name[length],...)[ASC|DESC]

ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

使用CREATE INDEX创建索引

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name on table_name(col_name[length],...)[ASC|DESC]

CREATE INDEX BkNAMEIdx ON book(bookname);

删除索引

使用ALTER TABLE删除索引

alter table 删除索引的基本语法格式如下：

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE book DROP INDEX BkNAMEIdx;

使用DROP INDEX 删除索引

drop index删除索引的基本语法格式如下：

DROP INDEX index_name ON table_name;

DROP INDEX BkNAMEIdx on book

BIGEC

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
06-MySQL索引

06-MySQL索引06-MySQL索引索引简介索引含义和特点优缺点索引分类索引的设计原则创建索引在创建表的同时，创建索引普通索引唯一索引单列索引组合索引全文索引空间索引在已经存在的表上创建索引使用ALTER TABLE 创建索引使用CREATE INDEX创建索引删除索引使用ALTER TABLE删除索引使用DROP INDEX 删除...
复制链接

扫一扫