MYSQL学习之索引

索引

一、索引的定义
索引用于快速找出在某一列（column）中的具有一特定值的行；
索引用于快速定位包含目标条件的行；
不使用索引，mysql默认的检索规则是从第一行开始读完整张表，表越大，查询花费时间越长。

二、索引的分类
1、单列索引
普通索引 KEY key_name (column_name)
MySQL中基本索引类型，没有什么限制；
允许在定义索引的列中插入重复值和空值；
纯粹为了查询数据更快一点
唯一索引 UNIQUE KEY key_name(column_name)
索引列中的值必须是唯一的，允许有空值
主键索引 PRIMARY KEY key_name(COLUMN_name)
一种特殊的唯一索引，不允许有空值

2、组合索引
KEY key_name(column1 , column2, column3, …)
1 在表中多个字段组合上创建索引
2 只有在查询条件中使用了这个字段组合的最左边字段时，索引才会被触发
3 使用组合索引时遵循最左前缀原则
最左前缀原则：
组合索引就是遵从了最左前缀，利用索引中最左边的字段来匹配行，这样的字段称为最左前缀
例如：（如何触发索引）
这里由id、name和age 3个字段构成的索引，key k1(id, name, age)
索引行中就按id/name/age的顺序存放，
索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。
当执行sql查询语句，筛选条件是where id=? AND name=? AND age=?这样的顺序查询时(即，查询条件与索引的字段顺序一致时)，就触发了索引；

3、全文索引
FULLTEXT key()
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引

4、空间索引
空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。
在创建空间索引时，使用SPATIAL关键字。
要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

三、索引的优缺点
优点：
1 所有的MySQL列类型都可以被索引，也就是可以给任意字段设置索引
2 加快数据查询速度
缺点：
1 创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
2 索引也需要占空间，我们知道数据表中的数据也会有最大上限设置的，
如果我们有大量的索引，索引文件可能会比数据文件更快达到上限值
3 当对表中的数据进行增删改时，索引也需要动态的维护，降低了数据的维护速度

四、索引的使用原则
1 并不是每个字段都设置索引就好，也不是索引越多越好
2 对更新频繁的表要避免进行过多的索引，对查询频繁的字段应该适当创建索引
3 数据量小的表最好不要用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果
4 重复值较多的列上不要建立索引，重复值较少的列上要建立索引

总结：表数据量大，查询频繁，单列不同值较多的情况下可以建立索引

五、索引示例

CREATE TABLE `user_info` (
  `u_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `u_name` varchar(50) DEFAULT NULL COMMENT '用户名',
  `u_no` varchar(10) DEFAULT NULL COMMENT '部门编号',
  `u_d_id` int(11) DEFAULT NULL COMMENT '部门编号',
  `u_age` int(11) DEFAULT NULL COMMENT '年龄',
  `u_salary` int(11) DEFAULT '0' COMMENT '工资',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`u_id`), # 主键索引
  KEY `k_u_name` (`u_name`), # 普通索引
  KEY `k_u1` (`u_name`, `u_age`, `u_no`) # 组合索引
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;