第七章：索引

1、MySQL默认的查询方式是遍历整个表；

什么是索引？

索引记录的是数据的存储位置 他是一种特殊的数据结构，索引可以提高查询的效率，它是独立于数据表之外的。

MyISAM、InnoDB支持btree和b+tree索引，Memory支持btree和hash索引。每一个表至少
可以添加16个索引，总的索引长度至少256字节；

常用的索引类型：
1.主键索引：primary key
2.唯一索引和普通索引；
3.单列索引和组合索引；
4.全文索引：fulltext
5.空间索引：spatial

主键和唯一索引：不但可以提高查询效率，还对索引字段的值进行条件限制。unique唯一索引
字段的值必须具有唯一性；primary key主键索引字段的值是唯一且非空；
其他的索引作用是提高查询效率。

创建索引的语法：
1.在创建表的时候添加索引：
CREATE TABLE 表名 [字段名 数据类型 ] [unique 唯一索引|fulltext全文索引|spatial空间索引]
INDEX|KEY [索引名] 索引字段（length） [ASC|DESC]；
2.给已经创建好的表添加索引：
（1）ALTER TABLE 表名 ADD [unique 唯一索引|fulltext全文索引|spatial空间索引]
INDEX|KEY [索引名] 索引字段（length） [ASC|DESC]；
（2）CREATE INDEX [unique 唯一索引|fulltext全文索引|spatial空间索引]
INDEX|KEY [索引名] ON 表名（字段名）；

创建普通索引：
普通索引是最基础的索引类型，没有唯一性限制，作用是只加快对数据的访问速度；

mysql> CREATE TABLE book(
    -> bookid INT NOT NULL,
    -> bookname VARCHAR(255) NOT NULL,
    -> authors VARCHAR(255) NOT NULL,
    -> info VARCHAR(255),
    -> comment VARCHAR(255),
    -> year_publication YEAR NOT NULL,
    -> INDEX(year_publication)
    -> );
Query OK, 0 rows affected (0.04 sec)

查看索引：
1.

mysql> SHOW CREATE TABLE book\G
*************************** 1. row ***************************
       Table: book
Create Table: 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=utf8
1 row in set (0.01 sec)

mysql> SHOW INDEX FROM book\G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

创建唯一索引：
唯一索引主要作用是减少查询索引列操作的执行时间，而且还对该列的值进行唯一性限制，但
允许有空值。

mysql> CREATE TABLE t1
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> UNIQUE INDEX UniqIdx(id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SHOW INDEX FROM t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: UniqIdx
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

单列索引：
普通索引包含单列和组合索引；单列索引就是给数据表中的某一列添加索引，一张表中可以添加
多个单列索引；

mysql> CREATE TABLE t2 ( id INT NOT NULL, name CHAR(50), INDEX SingleIdx(name) );
Query OK, 0 rows affected (0.00 sec)

组合索引：
是在多个字段上创建一个索引。遵循最左前缀原则。比如现在有一个组合索引（id,name,age）,
实际作用相当于添加了三个索引：（id）、（id,name）、（id,name,age）
创建组合索引：

mysql> CREATE TABLE t3
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> INDEX MultiIdx(id,name,age)
    -> );
Query OK, 0 rows affected (0.07 sec)

全文索引：
FULLTEXT，只有MyISAM引擎支持全文索引，并且只为CHAR/VARCHAR/TEXT 列添加。
索引总是对整个列进行，不值局部索引，适合大型数据的表创建。

mysql> CREATE TABLE t4
    -> (
    -> id INT NOT NULL,
    -> name CHAR(30) NOT NULL,
    -> age INT NOT NULL,
    -> info VARCHAR(255),
    -> FULLTEXT INDEX FullIdx(info(100))
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

空间索引（spatial）：
只有myisam引擎支持，且空间索引字段的值必须为非空。

mysql> CREATE TABLE t5
    -> (
    -> g GEOMETRY NOT NULL,
    -> SPATIAL INDEX spaIdx(g)
    -> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> SHOW INDEX FROM t5\G
*************************** 1. row ***************************
        Table: t5
   Non_unique: 1
     Key_name: spaIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

在已经存在的表上创建索引：
ALTER TABLE ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [索引名] 索引字段（length）；
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名（字段名）；

mysql> ALTER TABLE book ADD INDEX (bookname(30));
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE UNIQUE INDEX Unidex ON book(bookid);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

验证查询又没有用到索引：explain的用法。

mysql> EXPLAIN SELECT bookid FROM book WHERE bookid=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
         type: const
possible_keys: Unidex,BIdex
          key: Unidex
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

table : 表名
type：显示查询连接使用了什么类型。从最好到最差：const、eq_ref、ref、range、indexhe、
ALL
possible_keys ：显示可能应用在这张表里的索引。如果NULL，表示没有可用的索引。
key：实际使用的索引；
key_len：索引宽度，在不损失精确度的情况下，长度越短越好；
ref：显示索引的那一列被使用了。
rows：返回请求结果读取了几行数据；
extra：关于mysql如何解析查询的额外信息。

删除索引：
ALTER TABLE 表名 DROP INDEX 索引名；

mysql> ALTER TABLE book DROP INDEX year_publication;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引的规则：
（1）创建索引并非越多越好；
（2）数据量小的表最好不要创建索引；
（3）避免对经常更新的数据创建索引；
（4）在条件表达式中经常用到的不同值较多的列创建索引（where子句中常用的字段）；
（5）当唯一性是某种数据本身的特征时，我们创建唯一索引；
（6）主键本身就是索引；
（7）在频繁进行排序或分组的列上简历索引，如果排序的列有多个，可以创建组合索引；