MySQL数据库--索引

1. 数据库索引

1.1 索引概述

索引就类似于书籍中的目录，在一本书中，无须阅读整本书，利用目录就可以快速査找所需信息，书中的目录是一个词语列表，其中注明了包含各个词的页码
而数据库索引则是在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据
数据库中的索引是某个表中一列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单

1.2 索引作用

设置了合适的索引之后，数据库利用定位技术，加快查询速率
当表很大的时候或者内容很多的时候，或者查询涉及到多个表时，使用索引可使查询加快成干倍
可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本
通过创建唯一性索引保证数据表数据的唯一性
可以加快表与表之间的连接
在使用分组和排序时，可大大减少分组和排序时间
需要额外的磁盘空间

1.3 创建索引的原则依据

敲黑板，划重点，要在合适的地方设置索引，不然的话就变成了数据库的负担

表的主键、外键必须有索引
数据量超过300行的表应该有索引
经常与其他表进行连接的表，在连接字段上应该建立索引
唯一性太差的字段不适合建立索引
更新太频繁地字段不适合创建索引
经常出现在 Where子句中的字段，特别是大表的字段，应该建立索引
索引应该建在选择性高的字段上
索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

二、索引的分类

逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

2.1 普通索引

2.1.1 普通索引格式

普通索引是最基本的索引，它没有任何限制，也是大多数情况下用到的索引。

直接创建索引的方式
mysql>CREATE INDEX index_name ON table_name (column(length));
column 是指定要创建索引的列名
length 是可选项

索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字 节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。

2.1.2 创建普通索引

方法一：

mysql> create table info (id int(4) not null,name varchar(10) not null,address varchar(50) default '未知');    ##创建一个表结构
Query OK, 0 rows affected (0.01 sec)

mysql> describe info;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | NO   |     | NULL    |       |
| name    | varchar(10) | NO   |     | NULL    |       |
| address | varchar(50) | YES  |     | 未知    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> create index id_index on info(id);    ##为info表中的id创建索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe info;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | NO   | MUL | NULL    |       |
| name    | varchar(10) | NO   |     | NULL    |       |
| address | varchar(50) | YES  |     | 未知    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

方法二：

修改表结构的方式添加索引
mysql>ALTER TABLE table_name ADD INDEX index_name (column(length));

mysql> alter table info add index name_index (name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe info;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(4)      | NO   | MUL | NULL    |       |
| name    | varchar(10) | NO   | MUL <