6 第四章 mysql高级语法 -索引index

1. 什么是索引 ？？？

   索引是对数据库表中一列或多列的值(例如employee表的姓名lname列)进行排序的一种结构。如果要按姓查找特定的职员，与必须搜索表中的所有行相比，使用索引会帮助你更快的获得该信息。

   索引是一个单独的、物理的数据库结构，他是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。


   实际上，可以把引索理解为一种为一种特殊的目录，好比是一本书的目录。索引能加快数据库的查询速度。例如对于这样一个查询： select * from table1 where id=100.如果没有引索，必须要便利整个表，直到id等于100的这一行被找到为止。有了索引之后(必须是在id这一列上建立的索引)，直接在索引里面找到100，就可以得到这一行的位置，也就是找到了这一行。可见，索引是用来定位的。
  

    mysql数据库中，索引都要以B-树的形式保存。如果没有索引，执行查询时mysql必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，mysql 无需扫描任何记录即可快速的得到目标记录所在的位置。


   对于索引中的每一项，mysql在内部为他保存一个数据文件中实际记录所在位置的‘指针’。例如我们创建了一个名为people的表

create table people(peopleid smallint not null,name char(50) not null);

假设我们存入了1000行数据，如果我们要查找name等于‘mike’记录的peopleid
select peopleid from people where name='mike';

    mysql能够在name的索引中查找‘mike’值，然后直接转到数据文件中相应的行，准确的返回改行的 peopleid(999)。在这个过程中，mysql只需处理一行就可以返回结果。如果没有name列的索引，mysql要扫描数据文件中的所有记录，即1000个记录！！！显然，需要mysql处理的记录数量越少，则它完成任务的速度就越快。


2 索引的语法 ！！！！

   sql语法中使用 create index 创建索引。

mysql> ? create index
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

  不同存储引擎支持的索引类型
myisam     btree
innodb     btree
memeory/hea  hash btree


1. 普通索引
   这是最基本的索引类型，而且它没有唯一之类的限制。使用 create index 命令创建普通索引：

   create index indexname on mytable(username(length))

   如果是char varchar 类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length，下同。页可以在修改表的时候创建
   alter mytable add index [indexname] on (username(length));

创建表的时候指定索引，例如：

   create table mytable(
      id int not null,
      username varchar(16) not null,
      index (indexname) [username(length)]
);


2.唯一性索引
    索引和前面的‘普通索引’基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一，但允许有空值

   直接创建唯一索引的语法如下：
create unique index indexname on mytable (username(length));
   修改表结构的时候创建
alter mytable and unique [indexname] on (username(length));
   创建表的时候直接指定
create table mytable(
   id int not null,
   username varchar(16) not null,
   unique [indexname] (username(length))
);
  
3.主键
   主键是一种特殊的唯一索引，不允许有空值一般是在建表的时候创建主键索引。
   create table mytable(
          id int not null,
          username varchar(16) not null,
          primary key (id)
);

4. 全文索引
   创建全文索引 create fulltext index
   删除全文索引 drop index index_name on tbl_name
  
  drop index 用于从表 tbl_name中取消名称为 index_name的索引。
  alter table tablename drop primary key;
  alter table tablename drop index indexname;


3. 索引的使用

 create inde idx_u on article (article_category, article_id);

 select * from 'article ' where article_category=11 order by aritcle_id desc limit 5;