索引是一个单独的,存储在磁盘上的数据库结构,包含对数据表里所有记录的引用指针。mysql的索引存储类型有两种,BTREE和HASH.
索引是在存储引擎中实现的,每种存储引擎的索引不一定都相同。所有的存储引擎都支持每个表至少16个索引,索引长度至少为256个字节。
索引的分类
1.普通索引和唯一索引
普通索引为基本索引类型,允许在定义的索引列中插入重复值和空值。
唯一索引只索引值必须唯一,但允许空值,主键索引是一种特殊的的唯一索引,不允许有空值。
2.单列索引和组合索引
单列索引是指索引只包含单个列,一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建索引,只有在查询条件中使用了这些字段中的左边字段时,索引才会被使用。使用组和人索引时遵循最左前缀集合。
3.全文索引
全文索引的类型为FULLTEXT,在定义索引的列上支持值得全文查找,允许在这索引列中插入重复值和空值。全文索引可以在CHAR,VARCHAR或者TEXT类型上创建,在mysql中只有MyISAM支持引擎全文索引。
4.空间索引
空间索引是对空间数据类型的字段建立的索引,mysql的空间数据类型只有四中,分别是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]` ![](https://img-blog.csdnimg.cn/2020021417432769.png) 1.创建普通索引 最基本的索引,作用只是为了加快数据访问速度。mysql> create table book(bookid INT NOT NULL,bookname VARCHAR(255) NOT NULL,authers VARCHAR(255) NOT NULL,info VARCHAR(2
55) NOT 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) COLLATE utf8_unicode_ci NOT NULL,
`authers` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`info` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`comment` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`year_publication` year(4) NOT NULL,
KEY `year_publication` (`year_publication`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
year_publication为索引,索引名为year_publication,mysql自动添加的。
用explain语句查看索引是否被使用
mysql> explain select * from book1 where `year_publication`=1990 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: no matching row in const table
EXPLAIN列的解释:
table:显示这一行的数据是关于哪张表的
type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引
key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
rows:MYSQL认为必须检查的用来返回请求数据的行数
但是这里出现了NULL,说明索引没有被使用,查了资料解释只有一个表时,都是列举,显示所有数据 所以为 NULL 多个表时,第一个表 也是需要列举的,也会 是NULL
个人感觉还有其他原因,求路过大佬解释。
创建唯一索引
唯一索引主要为了减少索引列操作的执行时间。唯一索引与普通索引类似,但是索引列的值必须唯一,但允许有空值。如果是组合索引,则组合必须唯一。
mysql> create table t1(id INT NOT NULL,name CHAR(30) NOT NULL,UNIQUE INDEX UniqIdx(id));
mysql> show create table t1 \G
*************************** 1. row *****************