索引是一种特殊的数据结构,可以用来快速查询数据库的特定记录,建立是一中提高数据库性能的重要方式。
内容:索引的意义,索引的设计,创建和删除
索引简介
索引是建立在表上的,有一列或者多列组成,并对这一列或者多列进行排序的一种结构。
所有存储引擎对每个表至少支持16个索引,总索引长度至少为256字节,索引有两种存储类型,包括B型树索引和哈希索引。
索引的优点是可以提高检索的速度,但是创建和维护索引需要耗费时间,这个时间随着数据量的增加而增加。
索引可以提高查询的速度,但是会影响插入的速度,当要插入大量的数据时,最好的办法是先删除索引,插入数据后再建立索引。
MySQL的索引分为:普通索引,唯一性索引,全文索引,单列索引,多列索引和空间索引。
目前只有MyISAM存储引擎支持全文索引,InnoDB引擎还不支持全文索引。
索引的设计原则
- 选择唯一性索引。
- 为经常需要排序,分组和联合操作的字段建立索引。
- 为常作为查询条件的字段建立索引。
- 限制索引的数目。
- 尽量使用数据量少的索引。
- 尽量使用前缀来索引。如果字段的值很长,最好使用值的前缀来索引,如果只检索子酸的前面的若干字符,可以提高检索的速度。
- 删除不再使用或者很少使用的索引。
原则只是参考而不能拘泥。
创建索引
三种方式:在创建表是创建索引,在已存在的表上创建索引和使用alter table语句创建索引。
mysql> show tables;
+----------------+
| Tables_in_kiwi |
+----------------+
| stu |
+----------------+
1 row in set (0.00 sec)
mysql> create table indexTest(id int, name varchar(20), sex boolean, index index_id(id));
Query OK, 0 rows affected (0.08 sec)
mysql> desc indextest;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> explain select * from indextest where id = 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: index_id
key: index_id
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
+----------------+
| Tables_in_kiwi |
+----------------+
| stu |
+----------------+
1 row in set (0.00 sec)
mysql> create table indexTest(id int, name varchar(20), sex boolean, index index_id(id));
Query OK, 0 rows affected (0.08 sec)
mysql> desc indextest;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | tinyint(1) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> explain select * from indextest where id = 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: indextest
type: ref
possible_keys: index_id
key: index_id
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
创建单列索引,subject(10)是为了不查询全部信息而提高检索的速度。
mysql> create table singleRow(id int,name varchar(20),subject varchar(30),index index_st(subject(10)));
Query OK, 0 rows affected (0.17 sec)
mysql> show create table singlerow\G;
*************************** 1. row ***************************
Table: singlerow
Create Table: CREATE TABLE `singlerow` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`subject` varchar(30) DEFAULT NULL,
KEY `index_st` (`subject`(10))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
多列索引,空间索引类似。
在已存在的表上建立索引
语法为: create [unique|fulltext|spatial] index index_name on table_name (property_name[length] [asc|desc]);
mysql> desc stu;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| s_num | int(10) | YES | MUL | NULL | |
| course | varchar(20) | YES | | NULL | |
| score | varchar(4) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
mysql> show create table stu \G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`s_num` int(10) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index_id` (`id`),
KEY `grade_fk` (`s_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| s_num | int(10) | YES | MUL | NULL | |
| course | varchar(20) | YES | | NULL | |
| score | varchar(4) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
mysql> show create table stu \G;
*************************** 1. row ***************************
Table: stu
Create Table: CREATE TABLE `stu` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`s_num` int(10) DEFAULT NULL,
`course` varchar(20) DEFAULT NULL,
`score` varchar(4) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `index_id` (`id`),
KEY `grade_fk` (`s_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
使用alter table创建索引
语法为:
alter table table_name add [unique|fulltext|spatial] index index_name(property_name[length] [asc|desc]);
mysql> create table index_1(id int, name varchar(20), class int);
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
+----------------+
| Tables_in_kiwi |
+----------------+
| index_1 |
| singlerow |
| stu |
+----------------+
3 rows in set (0.00 sec)
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table index_1 add fulltext index index_alter (name desc);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
mysql> alter table index_1 engine=myisam;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table index_1 add fulltext index index_alter (name desc);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
FULLTEXT KEY `index_alter` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
+----------------+
| Tables_in_kiwi |
+----------------+
| index_1 |
| singlerow |
| stu |
+----------------+
3 rows in set (0.00 sec)
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> alter table index_1 add fulltext index index_alter (name desc);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
mysql> alter table index_1 engine=myisam;
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table index_1 add fulltext index index_alter (name desc);
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
FULLTEXT KEY `index_alter` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
删除索引
语法:
drop index index_name on table_name;
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
FULLTEXT KEY `index_alter` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> drop index index_alter on index_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL,
FULLTEXT KEY `index_alter` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> drop index index_alter on index_1;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table index_1 \G;
*************************** 1. row ***************************
Table: index_1
Create Table: CREATE TABLE `index_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
`class` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
相关推荐
用LUCENE连击MYSQL建立索引并搜索的JAVA代码。里面有建立索引的详细步骤。
为mysql数据库建立索引前些时候,一位颇高级的程序员居然问我什么叫做索引,令我感到十分的惊奇,我想这绝不会是沧海一粟,因为有成千上万的开发者(可能大部分是使用
索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍mysql建立索引的优缺点及常用使用方法。
代码复制粘贴 改下里面的参数 就可以用 而且参数 也有说明 简单易懂
《MySQL索引原理及如何建立高效索引.pptx》主要讲述mysql数据库索引底层原理、作用、 索引使用、索引失效等核心技术点。非常实用!!!
为mysql数据库建立索引.docx
在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表: CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL ); 我们随机向里面插入了10000条记录,...
如何正确合理的建立MYSQL数据库索引
索引是依赖于表建立的,提供了数据库中编排表中数据的内部方法。表的存储由两部分组成,一部分是表的数据页面,另一部分是索引页面。索引就存放在索引页面上。 索引一旦创建,将由数据库自动管理和维护。在编写SQL...
索引的建立对于MySOL的高效运行是很重要的,索引可以大大提高MvSOL的检索速度。打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是...建立索引会占用磁盘空间的索引文件
CREATE INDEX Syntax CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON ... HASH | RTREE} 代码如下: — 创建无索引的表格 create table testNoPK ( id int not null, name varchar(10) ); — 创建
主要介绍了MySQL下使用Inplace和Online方式创建索引的教程,针对InnoDB为存储引擎的情况,需要的朋友可以参考下
在MySql数据库中,有四种索引:聚焦索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FUNLLTEXT INDEX)。 全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术...
重复索引:表示一个列或者顺序相同的几个列上建立的多个索引。 冗余索引:两个索引所覆盖的列重叠 冗余索引在一些特殊的场景下使用到了索引覆盖,所以比较快。 场景 比如文章与标签表 +——+——-+——+ | id | ...
关于建立索引的几个准则: 1、合理的建立索引能够加速数据读取效率,不合理的建立索引反而会拖慢数据库的响应速度。 2、索引越多,更新数据的速度越慢。 3、尽量在采用MyIsam作为引擎的时候使用索引(因为MySQL以...
4.建立索引,在查询中使用索引 可以提高性能 索引缺点 1.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加 2.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间 3....
TextField是不支持建立索引的 MySQL对索引字段长度有限制 innodb引擎的每个索引列长度限制为767字节(bytes),所有组成索引列的长度和不能大于3072字节 myisam引擎的每个索引列长度限制为1000字节,所有组成索引列...
前言 索引是对数据库中一或多个列值的排序,帮助数据库高效获取数据的数据结构 ...如果我们按照 name 字段来建立索引的话,采用B+树的结构,大概的索引结构如下 如果我们要进行模糊查找,查找name 以“张”开头的所有
文章目录推荐阅读索引建立选择适合建立索引不适合建立索引索引失效原因使用索引注意索引不能使用排查不会用到索引 推荐阅读 MySql性能优化之JOIN连接(有图,最全,最详细) 数据库索引(Index)实现原理,面试官常问~...