Mysql索引

1. 索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

2. 索引的优缺点：

   优点：

   • 所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引。

   • 大大加快数据的查询速度。

   缺点：

   • 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。

   • 索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值。

   • 当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

3. 索引的使用原则：

   并不是每个字段都设置为索引好，也不是索引越多越好，而是需要自己合理的使用。

   • 对经常更新的表就避免对其设置过多的索引，对经常用于查询的字段应该创建索引。

   • 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

   • 在一个列上(字段上)不同值较少的不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多的可是建立索引。

   • 比较频繁作为查询条件的字段应该创建索引。

   • 不会出现在where子句中的字段不该创建索引。

4. 索引的几种类型

   MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换。

   存储引擎	优点	缺点	应用场景
   MyISAM	独立于操作系统，这说明可以轻松地将其从Windows服务器移植到Linux服务器	不支持事务/行级锁/外键约束	适合管理邮件或Web服务器日志数据
   InnoDB	健壮的事务型存储引擎；支持事务/行级锁/外键约束自动灾难恢复/AUTO_INCREMENT		需要事务支持，并且有较高的并发读取频率

   * 主键索引
   --数据列不允许重复，不允许为NULL，一个表只能有一个主键。
   ALTER TABLE table_name ADD PRIMARY KEY (column);
   * 普通索引
   --MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和NULL值。一个表允许多个列创建普通索引。
   ALTER TABLE table_name ADD INDEX index_name (column);
   * 唯一索引
   --索引列中的值必须是唯一的，但是允许NULL值。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。一个表允许多个列创建唯一索引。
   ALTER TABLE table_name ADD UNIQUE (column);
   * 全文索引
   --主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引，基于倒排索引，类似于搜索引擎。MyISAM存储引擎支持全文索引，InnoDB 存储引擎在 MySQL5.6.4 版本中也开始支持全文索引。
   ALTER TABLE table_name ADD FULLTEXT (column);
   

5. 索引的使用

   --创建普通索引，创建索引时未指定索引的名，会自动帮我们用字段名当作索引名
   CREATE TABLE book(
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   author VARCHAR(20) NOT NULL,
   info VARCHAR(255) NULL,
   INDEX(author));
   --用EXPLAIN关键字，来查看索引是否正在被使用，并且输出其使用的索引信息
   --创建唯一索引
   CREATE TABLE tab1(
   id INT(5) NOT NULL,
   name CHAR(20) NOT NULL,
   UNIQUE INDEX uniqId(id)
   );
   --创建主键索引
   CREATE TABLE tab2(
   id INT(4) NOT NULL,
   name char(20) DEFAULT NULL,
   PRIMARY KEY(id));
   --创建全文索引，支持的字段类型为CHAR、VARCHAR和TEXT，存储引擎为MyISAM
   CREATE TABLE tab4(
   id INT(4) NOT NULL,
   name CHAR(20) NOT NULL,
   age INT(3) NOT NULL,
   info VARCHAR(255),
   FULLTEXT INDEX fullTxtIdx(info)
   )ENGINE=MyISAM;