May 27 2016

mysql 索引

索引是在存储引擎中实现的,因此每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。
根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。
大多数存储引擎有更高的限制。MYSQL中索引的存储类型有两种:B-TREE和HASH,具体和表的存储引擎相关;
MYISAM和InnoDB存储引擎只支持B-TREE索引;MEMORY和HEAP存储引擎可以支持HASH和B-TREE索引

索引的优点:
1、通过创建唯一索引,保证数据库表每行数据的唯一性
2、大大加快数据查询速度
3、在使用分组和排序进行数据查询时,可以显著减少查询中分组和排序的时间
索引的缺点:
1、维护索引需要耗费数据库资源
2、索引需要占用磁盘空间,索引文件可能比数据文件更快达到最大文件尺寸
3、当对表的数据进行增删改的时候,因为要维护索引,速度会受到影响
索引的分类
1、普通索引和唯一索引
主键索引是一种特殊的唯一索引,不允许有空值
2、单列索引和复合索引
单列索引只包含单个列
复合索引指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用复合索引时遵循最左前缀集合
3、全文索引
全文索引类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在
CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引
4、空间索引
空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,
分别是GEOMETRY、POINT、LINESTRING、POLYGON。
MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列,必须
将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建
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为同义词,两者作用相同,用来指定创建索引
col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择;
index_name指定索引的名称,为可选参数,如果不指定,MYSQL默认col_name为索引值;
length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;
asc或desc指定升序或降序的索引值存储
在已经存在的表中创建索引,可以使用ALTER TABLE或者CREATE INDEX语句

1、使用ALTER TABLE语句创建索引,语法如下

ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]
[index_name](col_name[length],…)[ASC|DESC]

添加索引之后,使用SHOW INDEX语句查看指定表中创建的索引
各个参数的含义

1、TABLE:要创建索引的表
2、Non_unique:索引非唯一,1代表是非唯一索引,0代表唯一索引
3、Key_name:索引的名称
4、Seq_in_index:该字段在索引中的位置,单列索引该值为1,复合索引为每个字段在索引定义中的顺序
5、Column_name:定义索引的列字段
6、Sub_part:索引的长度
7、NULL:该字段是否能为空值
8、Index_type:索引类型

MYSQL中使用ALTER TABLE或者DROP INDEX语句来删除索引,两者实现相同功能
1、使用ALTER TABLE删除索引
语法
ALTER TABLE table_name DROP INDEX index_name
注意:AUTO_INCREMENT约束字段的唯一索引不能被删除!!
2、使用DROP INDEX 语句删除索引
DROP INDEX index_name ON table_name
删除表中的某列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。
如果索引中的所有列都被删除,则整个索引将被删除!!
创建一个普通索引
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;
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=latin1
可以发现已经把index变成key关键字了 然后默认的名字和列名一样
mysql> INSERT INTO book VALUES(12,’NIHAO’,’NIHAO’,’文学’,’henhao’,1990);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM book WHERE year_publication=1990;
+—-+————-+——-+——+——————+——————+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+——————+——————+———+——-+——+——-+
| 1 | SIMPLE | book | ref | year_publication | year_publication | 1 | const | 1 | |
+—-+————-+——-+——+——————+——————+———+——-+——+——-+
1 row in set (0.00 sec)

EXPLAIN语句输出结果的各个行的解释如下:
select_type: 表示查询中每个select子句的类型(简单 OR复杂)
type:表示MySQL在表中找到所需行的方式,又称“访问类型”,常见类型如下:(从上至下,效果依次变好)
possible_keys :指出MySQL能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
key: 显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL
key_len :表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度
ref :表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows :表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
Extra :包含不适合在其他列中显示但十分重要的额外信息 如using where,using index
唯一索引
唯一索引列的值必须唯一,但允许有空值。如果是复合索引则列值的组合必须唯一

CREATE TABLE t1
(
id INT ,
NAME CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

show create table t1
CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`NAME` char(30) NOT NULL,
UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> insert into t1(name) values(‘uc’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1(name) values(‘ucjmh’);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+——+——-+
| id | NAME |
+——+——-+
| NULL | uc |
| NULL | ucjmh |
+——+——-+
2 rows in set (0.00 sec) 可以发现唯一索引允许有空值 且不仅一个的空值 其实在oracle里的唯一索引也是一样允许的

创建复合索引

CREATE TABLE t3(
id INT NOT NULL,
NAME CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR (255),
INDEX MultiIdx (id, NAME)
);
INSERT INTO t3(id ,NAME,age,info) VALUES(1,’小明’,12,’nihao’),(2,’小芳’,16,’nihao’);

EXPLAIN SELECT * FROM t3 WHERE id=1 AND NAME=’小芳’



Copyright 2019. All rights reserved.

Posted 2016年5月27日 by ucjmh in category "mysql

Leave a Reply

Your email address will not be published. Required fields are marked *