mysql学习笔记之三（索引）

索引

       数据库对象索引：一种组合数据的方式，通过索引对象，可以快速查询到数据库对象表中的特定记录，是一种提高性能的最常用方式。

       一个索引会包含表中的按照一定顺序排序的一列或多列字段。

        索引操作：

       创建索引，修改索引，删除索引。

        数据库对象索引主要为了提高从表中检索数据的速度。由于数据存储在数据库表中，所以索引是创建在数据库对象上的，由表中的一个字段或多个字段生成的键组成，这些键存储在数据结构里（B-树或哈希表）中，通过MySql可以快速有效的查找与键值相关联的字段。根据索引的存储类型，可以将所以分为B型树索引和哈希索引

        注意：InnoDB和MyISAM存储引擎支持Btree类型索引，MEMORY存储引擎支持Hash类型索引，默认为前者索引。

         数据库对象索引的出现，除了提供数据库管理系统的查找速度，而且还可以保证字段的唯一性。从而实现数据库表的唯一性。

六种索引：普通索引，唯一索引，全文索引，单列索引，多列索引，空间索引

利弊：

利：提高查找速度

弊：过多索引占用磁盘空间

适合创建索引的情况：

1、经常被查询的字段，即在where中出现的字段

2、分组的字段，即在group by中出现的字段

3、存在依赖关系的字表和父表之间的联合查询，即主键或外键字段

4、设置唯一完整性的字段

不适合创建的情况：

1、在查询中很少被使用的字段

2、拥有许多重复值的字段普通索引

创建：

三种方式：

1、创建表时创建索引

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

3、通过SQL语句在ALTER TABLE来创建

1、create table tablename(

column1 type,

column2 type,

....

INDEX|KEY [indexname] (columname [(长度)] [ASC|DESC])

  )
  通过index或key来指定字段为索引。
  在创建索引时，可以指定索引的长度。这是因为不同存储引擎定义了标的最大索引数和最大索引长度
mysql所支持的存储引擎对每一个表至少支持16个索引，总索引长度至少为256字节
create table t_index1(

id int,

name varchar(20),

loc varchar(20),

index index_id(id) 

)
之后向表里插入多条记录（这是必须的，如果只有一条记录，出现不了下面的字样），然后执行

explain select * from t_index1 where id=1\G

之后出现

possible key:index_id

key:index_id

的字样之后就说明id的索引对象被启用

2、在已经存在的表上创建普通索引

create index indexname on tablename(column[(长度)][asc|desc])

create index index_name on t_index1(name)
验证：

explain select * from t_index1 where id=1\G

字样出现

possible key:index_name

key:index_name

3、通过SQL语句alter table 创建普通索引

alter table tablename add index|key indexname(columnname[(length)][asc|desc])

又见识到create和alter的两个功能。修改索引会不会用alter呢？删除索引会不会用drop呢？往后看看

唯一索引

1、创建表时创建唯一索引
create table tablename(

column1 type,

column2 type,

...

unique index|key indexname(column[(length)][asc|desc])

)

       所谓的唯一索引就是在普通索引上添加上关键字UNIQUE。

2、在已经存在的表上创建一个唯一索引
试着自己写写

create unique index indexname on tablename(columname[(长度)][asc|desc])

完全OK

3、通过SQL语句alter table创建索引
alter table tablename add unique index|key indexname(columnname[(length)][asc|desc])

全文索引

     全文索引主要关联在数据类型为char，varchar，text字段，以便能够更加快速查询数据量较大的字符串类型的字段
      mysql从3.23.23版本开始支持全文索引，只能在存储类型为myISAM的数据库表上创建全文索引。
      默认情况下，全文索引的搜索方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。
关键字：fulltext index
三种创建方式跟上面的类似，将index关键字写成fulltext index就是创建了全文索引

多列索引

在创建索引时，所关联的字段不是一个字段，而是多个字段
虽然可以通过所关联的字段进行查询，但是只有查询条件使用了所关联字段中的第一个字段，多列索引才会被使用。
create table t_name(

column1 type,

column2 type,

...

index|key index_columni_columnj(columni[(length)][asc|desc],columnj[(length)][asc|desc])

)

其他两种创建方式类推一下就可以了。
六种索引：普通索引，唯一索引，全文索引，单列索引，多列索引，空间索引
一共是六种，这里讲到了三种。

网上搜索到的空间索引

     空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，

分别是GEOMETRY、POINT、LINESTRING、POLYGON。

     MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须

        将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

create table index6(

id int,

space geometry not null,

spatial index index6_sp(space)

);
以上都是创建索引，有创建就得有删除

删除索引

drop index indexname on tablename

语法很简单。