MySQL学习之索引入门

一.索引基础

1.什么是索引？
索引是一个单独的，存储在磁盘上的数据库结构，它包含着对数据库里所有记录的引用指针。使用索引可以让我们不必在查找数据时按顺序一个一个往下找，而是一次到位，方便快捷。

MySQL中索引的存储类型有两种：BTREE和HASH(这里涉及数据结构，先了解即可)

2.使用索引的优点：
1）通过创建唯一索引保证每行数据的唯一性
2）可以大大加快数据的查询速度
3）使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。
4）加速表和表之间的连接

3.索引的不利：
1）需维护
2）占用磁盘空间

4.索引的分类：
1）普通索引：允许在索引的列中插入重复值和空值。
2）唯一索引：索引列每行数据必须唯一，允许一个空值，但主键索引是一种特殊的唯一索引，不允许空值
3）单列索引：顾名思义，以一个列所作为索引列，一个表中可以有多个单列索引
4）组合索引：以多个列的组合作为索引，组合索引的查询遵循最左前缀原则（只有在查询条件中使用了这些列的左边列时，索引列才会被使用）
5）全文索引：在定义索引的列上支持值的全文范围内的查找，允许在这些索引列中插入重复值和空值。

6）空间索引：对空间数据类型的字段建立的索引。（初学了解即可）

5.索引设计原则： （这些原则让我们建立良好的索引避免性能浪费）

1）尽量使用最少的索引达到最好的效果。太多的索引不仅占用磁盘空间，而且会影响插入，删除，更新等操作（因为表中数据更改的同时存储索引的磁盘的数据也会发生变化）

2）避免对经常更新的表进行太多索引，且尽量用单列索引，对经常用于查询的字段应该创建索引。

3）索引适合于数据量大的表。

4）在不同值少的列上不要使用索引（索引失效）

5）当唯一性是某种数据的特征时，可以指定唯一索引

6）在频繁进行排序或分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

二.索引SQL语句

1.创建索引：

1）在创建表时指定索引

例子：
CREATE TABLE book
( bookid INT NOT NULL,
bookname VARCHAR(225) NOT NULL,
authors VARCHAR(255) NOT NULL,
year_publication YEAR NOT NULL,
INDEX(year_publication) //建立普通单列索引
);

同理：
UNIQUE INDEX(year_publication) //建立唯一索引
INDEX MultiId(bookid,bookname) //建立组合索引 ，查询遵循最左原则
FULLTEXT INDEX fullText(bookname) //建立全文索引

2）向已存在表中加入索引

ALTER TABLE book ADD INDEX bookIdx(bookid) 或者
CREATE INDEX bookIdx ON book(bookid)
其他同理

2.删除索引

ALTER TABLE book DROP INDEX bookIdx; 或者
DROP INDEX bookIdx ON book;

三.索引案例实战

1.首先我们先创建一个表，不指定索引：

CREATE TABLE book (
  bookid int(11) NOT NULL,
  bookname varchar(225) NOT NULL,
  authors varchar(225) NOT NULL,
  info varchar(225) DEFAULT NULL,
  comment varchar(225) DEFAULT NULL,
  year_publication year(4) NOT NULL