|
本文来自于 51cto, 索引是 MySQL 数据库中的重要对象之一, 用于快速找出某个列中有某一特定值的行。 |
定义:
索引用于快速找出某个列中有一特定值的行.
不使用索引, MySQL 必须从第一条记录开始读完整个表, 直到找出相关的行. 表越大, 查询数据所花费的时间越多.
如果表中查询的列有一个索引, MySQL 能快速到达一个位置去搜索数据文件, 而不必查看所有数据.
简介:
索引是对数据库表中一列或多列的值进行排序的一种结构, 使用索引可以提高数据库中特定数据的查询速度.
索引是一个单独的, 存储在磁盘上的数据库结构, 他们包含着对数据表里所有记录的引用指针.
使用索引用于快速找出某个或者多个列中有一特定值的行, 所有 MySQL 列类型都可以被索引, 对相关列使用索引是提高查询操作速度的最佳途径.
例如: 数据库中有 2 万条记录, 现在要执行这样一个查询:
select * from table where num=10000
如果没有索引, 必须遍历整个表, 直到 num 等于 10000 的这一行被找到为止.
如果在 num 列上创建索引, MySQL 不需要任何扫描, 直接在索引里面找到 10000, 就可以得知这一行的位置.
索引是存储引擎实现的, 因此, 每种存储引擎的索引都不一定完全相同, 并且每种存储引擎也不一定支持所有索引类型.
根据存储引擎定义每个表的最大索引数和最大索引长度. 所有存储引擎支持每个表至少 16 个索引, 总索引长度至少为 256 字节. 大多数存储引擎有更高的限制.
MySQL 中索引的存储类型有两种: BTREE 和 HASH, 具体和表的存储引擎相关; MyISAM 和 InnoDB 存储引擎都支持 BTREE 索引; MEMORY/HEAP 存储引擎可以支持 BTREE 和 HASH 索引.
索引优点:
1, 通过创建唯一索引, 可以保证数据库表中每一行数据的唯一性.
2, 可以大大加快数据的查询速度, 这也是创建索引的最主要的原因.
3, 在实现数据的参考完整性方面, 可以加速表和表之间的链接.
4, 在使用分组和排序子句进行数据查询时, 也可以显著减少查询中分组和排序的时间.
索引的缺点:
1, 创建索引和维护索引要耗费时间, 并且随着数据量的增加所耗费的时间也会增加.
2, 索引需要占据磁盘空间, 除了数据表占数据空间之外, 每一个索引还要占一定的物理空间, 如果有大量的索引, 索引文件可能比数据文件更快达到最大文件尺寸.
3, 当对表中的数据进行增加, 删除和修改的时候, 索引也要动态维护, 这样就降低了数据的维护速度.
MySQL 索引的分类:
1, 普通索引和唯一索引
普通索引: MySQL 中最基础的索引类型, 允许在定义索引的列中插入重复值和空值.
唯一索引: 索引列的值必须唯一, 但允许有空值.
如果是组合索引, 则列值和组合必须唯一. 主键索引是一种特殊唯一索引, 不行允许有空值.
2, 单列索引和组合索引
单列索引: 一个索引只包含一个列, 一个表也可以有多个单列索引.
组合索引: 在表的多个字段组合上创建的索引, 只有在查询条件中使用了这些字段的左边字段时, 索引才会被使用. 使用组合索引时遵循最左前缀集合.
3, 全文索引
全文索引类型为 FULLTEXT, 在定义索引的列上支持值得全文查找, 允许在这些索引列中插入重复值和空值. 全文索引可以在 CHAR,VARCHAR 或者 TEXT 类型上创建.
只有 MyISAM 存储引擎支持全文索引.
4, 空间索引
空间索引是对空间数据类型的字段建立索引, MySQL 中的空间数据有 4 中: GEOMETRY,POINT,LINESTRING 和 POLYGON.Mysl 使用 SPATIAL 关键字进行扩展, 使得能够创建正规索引类似的语法创建空间索引.
创建空间索引的列, 必须将其声明为 NOT NULL, 空间索引只能在存储引擎为 MyISAM 中创建.
索引表的设计原则:
1, 索引表不是越多越好, 一个表中如果有大量索引, 不仅占用硬盘空间, 还影响增删改查等语句的性能, 因为当表中的数据更改的同时, 索引也会进行调整和更新.
2, 避免经常更新的表进行过多的索引, 并且索引表中的列尽可能少. 而经常查询的字段应该创建索引, 但要避免添加不必要的字段.
3, 数据量小的表最好不要使用索引. 遍历的时间短与索引的时候, 并不会产生优化效果.
4, 在条件表达式中经常用到的不同值较多的列上建立索引, 在不同值少的列上不要建立索引. 比如性别.
5, 当唯一性是某种数据本身的特征时, 指定唯一索引. 使用唯一索引需要能确保定义的列的数据的完整性, 以提高数据的查询速度.
6, 在频繁进行排序或者分组 (即进行 group by 或 order by 操作) 的列上建立索引, 如果待排序的列有多个, 可以在这些列上建立组合索引.
创建语法:
CREATE TABLE 指定索引列
ALTER TABLE 在存在的表上创建索引
CREATE INDEX 添加索引
CREATE TABLE table_name [col_name data_type]
UNIQUE: 唯一索引
FULLTEXT: 全文索引
SPATIAL: 空间索引
[INDEX|KEY]: 作用相同, 创建索引
col_name: 需要创建索引的字段列, 必须在表中定义的列
index_name: 指定索引名, 如果不指定 MySQL 默认 col_name 为索引值.
length: 索引长度, 仅字符串型才可指定
[ASC|DESC]: 升序, 降序
1, 创建普通索引:
- MySQL> create database lxqdb;
- MySQL> use lxqdb;
- MySQL> create table book
- -> (
- -> book_id int not null,
- -> book_name 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)
- -> );
查看:
KEY `year_publication` (`year_publication`) # 成功建立索引, MySQL 自动添加索引名: year_publication
查看索引是否被正确使用:
- MySQL> explain select * from book where year_publication=2018\G
- *************** 1. row ***********
- id: 1
- select_type: SIMPLE # 表示简单的 select, 不可以使用子查询. 其他取值: PRIMARY | UNION | SUBQUERY
- table: book # 行指定数据表名, 它们被按照读取的先后顺序排列
- type: ref # 行指定本数据表和其他数据表之间的关联. 其他取值: system | const | eq_ref | ref | range | index | ALL
- possible_keys: year_publication # 可以选用的索引
- key: year_publication # 实际选用的索引
- key_len: 1 # 索引按字节计算长度, 值越小表示越快
- ref: const # 关联另一个数据表里的列名
- rows: 1 # 执行查询时预计会从这个数据表里读出的数据行的个数
- Extra: Using index condition # 关联操作信息
- 1 row in set (0.00 sec)
2, 创建唯一索引:
在比较大庞大的数据表中, 为了减少索引列操作的时间. 与普通索引唯一不同的是: 索引列的值必须唯一, 但允许有空值. 如果是组合索引, 则 列值的组合必须唯一.
创建 tb1 表, id 字段使用 UNIQUE 创建唯一索引.
- MySQL> create table tb1
- -> (
- -> id int not null,
- -> name char(20) not null,
- -> UNIQUE INDEX uidx(id)
- -> );
查看:
UNIQUE KEY `uidx` (`id`) # id 字段上创建了名为 uidx 的唯一索引.
3, 创建单列索引:
单列索引是再数据表中某一字段上创建的索引, 一个表中可以创建多个单列索引.
上面的两个例子中创建的索引都是单列索引.
创建 tb2 表, 在表中 name 字段上创建单列索引.
- MySQL> create table tb2
- -> (
- -> id int not null,
- -> name char(20) not null,
- -> INDEX Single_idx(name(10))
- -> );
查看:
KEY `Single_idx` (`name`(10)) # name 字段上创建名为 Single_idx 且长度 10 的单列索引.
4, 创建组合索引:
在多个字段上创建索引.
创建 tb3 表, id,name,age 为索引
- MySQL> create table tb3
- -> (
- -> id int not null,
- -> name char(20) not null,
- -> age int not null,
- -> info varchar(255),
- -> INDEX Multi_idx(id,name,age)
- -> );
查看:
KEY `Multi_idx` (`id`,`name`,`age`) # 建立了组合索引
注意: 组合索引可起几个索引的作用, 但是使用时并不是随便查询哪个字段都可以使用索引, 而是遵从 "最左前缀"
最左前缀: 利用索引中最左边的列集来匹配, 索引中按 id\name\age 的顺序存放, 索引可以搜索下面的字段组合:(id,name,age),(id,name),id.
如果列不构成索引最左前缀, MySQL 不能使用局部索引, 如 age 或者 name,age 组合则不能使用索引.
查询 id 和 name 字段的索引情况:
- MySQL> explain select * from tb3 where id=1 and name='lxq'\G
- key: Multi_idx # 可以看到使用了 MultiIdx 的索引.
查询 name 和 age 字段的索引情况:
- MySQL> explain select * from tb3 where name='lxq' and age=23\G
- key: NULL # 表示并没有使用索引查询.
5, 创建全文索引:
只有 myisam 引擎支持且只为 char,varchar,text 列. 索引总是对整列进行, 不支持局部索引.
创建表 tb4, 在 info 字段上建全文索引:
- MySQL> create table tb4
- -> (
- -> id int not null,
- -> name char(20) not null,
- -> age int not null,
- -> info varchar(255),
- -> FULLTEXT INDEX Fulltext_idx(info)
- -> )engine=myisam;
查看:
FULLTEXT KEY `Fulltext_idx` (`info`) # 名为 Fulltext_idx 的 FULLTEXT. 适合大型数据, 不要在小数据内使用.
6, 创建空间索引:
只有 myisam 引擎支持且只为 not null 列.
创建 tb5 表, GEOMETRY 字段上创建空间索引:
- MySQL> create table tb5
- -> (
- -> gmt geometry not null,
- -> SPATIAL INDEX Spat_idx(gmt)
- -> )engine=myisam;
查看:
- MySQL> show create table t5\G;
- SPATIAL KEY `Spat_idx` (`gmt`) # 名为 Spat_idx 的 GEOMETRY
- -------------------------------
我是分割线 1
-------------------------------
已存在表中创建索引:
1, 在 book 表中的 bookname 字段上建 bookname_idx 的普通索引:
MySQL> alter table book add index bookname_idx(book_name(30));
查看:
- MySQL> show index from book\G
- ************* 2. row *************
- Table: book
- Non_unique: 1 # 索引非唯一, 1 代表非唯一索引, 0 代表唯
- Key_name: bookname_idx # 索引名
- Seq_in_index: 1 # 索引中的位置, 1 为单列, 组合索引为每个字段在索引定义中的顺序
- Column_name: book_name # 索引的列字段
- Collation: A
- Cardinality: 0
- Sub_part: 30 # 索引长度
- Packed: NULL
- Null: # 字段是否为空
- Index_type: BTREE # 索引类型
- Comment:
- Index_comment:
- Table: book
2, 在 bookid 字段上建立名为 UniqidIdx 的唯一索引:
MySQL> alter table book add UNIQUE INDEX Uniqid_idx(book_id);
查看:
- MySQL> show index from book \G;
- ************** 1. row ***********
- Table: book
- Non_unique: 0 # 索引唯一, 1 代表非唯一索引, 0 代表唯一索
- Key_name: Uniqid_idx
- Seq_in_index: 1
3, 在 comment 上创建单列索引:
MySQL> alter table book add INDEX coment_idx(comment(50));
查看:
Sub_part: 50 #只要检索前 50 个字符
4, 在 book 的 authors 和 info 上建组合索引:
MySQL> alter table book add INDEX Au_Info_idx(authors(20),info(50));
查看:
- MySQL> show index from book \G;
- Key_name: Au_Info_idx
- Seq_in_index: 1 # 索引序列 1
- Column_name: authors
- *******************************
- Key_name: Au_Info_idx
- Seq_in_index: 2 # 索引序列 2
- Column_name: info
5, 在 tb6 表 gmt 字段建空间索引:
建表:
MySQL> create table tb6 ( gmt geometry not null)engine=myisam;
增加空间索引:
MySQL> alter table tb6 add SPATIAL INDEX spat_idx(gmt);
查看:
- MySQL>show index from tb6\G
- ---------------------------
我是分割线 2
----------------------------
查看索引:
- UNIQUE KEY `Uniqid_idx` (`book_id`),
- KEY `year_publication` (`year_publication`),
- KEY `bookname_idx` (`book_name`(30)),
- KEY `coment_idx` (`comment`(50)),
- KEY `Au_Info_idx` (`authors`(20),`info`(50))
或
MySQL>show index from book\G
删除索引:
MySQL> alter table book drop index Uniqid_idx; # 删除索引名为 Uniqid_idx 的索引.
注意:
添加 AUTO_INCREMENT 约束字段的唯一索引不能被删除.
或
- MySQL> drop index coment_idx on book; # 删除 book 表内索引名为 coment_idx 的索引.
- -----------------------------
我是分割线 3
-----------------------------
总结:
1, 索引对数据库的如此重要, 应该如何使用?
为数据库选择正确的索引是一项复杂的任务.
如果索引列较少, 则需要的磁盘空间和维护开销都较少.
如果在一个大表上创建了多种组合索引, 索引文件也膨胀的很快.
另一面索引较多可覆盖更多的查询.
删除创建索引不影响应用程序, 也不影响数据库架构, 因此应尝试多个不同的索引, 从而建立最优的索引.
2, 尽量使用短索引.
对字符串类型的字段进行索引, 如果可能应该指定一个前缀长度.
例如: 有一个 char(255)的列, 如果在前 10 个或 30 个字符内, 多数值是唯一的, 则不需要对整个列进行索引.
短索引不仅可以提高查询速度而且可以节省磁盘空间, 减少 I/O 操作.
3, 是不是索引建立得越多越好?
合理的索引可以提高查询速度, 但是不是索引越多越好. 在执行插入语句的时候, MySQL 要为新插入的记录建立索引, 所以过多的索引会导致插入操作变的非常慢.
原则上是只在查询用的字段才建立索引.
4, 为甚查询语句中的索引没有起作用?
在一些情况下, 查询语句中使用了带有索引字段. 但索引字段没有起作用.
例如: 在 where 条件的 like 关键字匹配的字符串以 "%" 开头, 这种情况下不会起作用.
where 条件中使用 or 关键字链接条件, 如果有 1 个字段没有使用索引, 那么其他的索引也不会起作用.
如果使用多列索引, 但是没有使用多列索引中的第一个字段, 那么多列索引也不会起作用.
来源: http://www.tuicool.com/articles/vUveE3n