2-4 索引

索引

1 什么是索引

索引用于快速找出在某一列（column）中的具有一特定值的行；

索引用于快速定位包含目标条件的行；

不使用索引，mysql默认的检索规则是从第一行开始读完整张表，表越大，查询花费时间越长。

2 索引分类

2.1单列索引

普通索引 KEY key_name (column_name) 

MySQL中基本索引类型，没有什么限制；

    允许在定义索引的列中插入重复值和空值；

    纯粹为了查询数据更快一点

唯一索引 UNIQUE KEY key_name(column_name) 

    索引列中的值必须是唯一的，允许有空值NULL

主键索引 PRIMARY KEY key_name(COLUMN_name) 

    一种特殊的唯一索引，不允许有空值NULL

2.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=?这样的顺序查询时(即，查询条件与索引的字段顺序一致时)，就触发了索引；

1、只有where的情况，遵从最左原则，条件必须有左边的字段，才会用到索引，中间如果断开了，则都不会用到后面的索引；

例子： where c1 = '1' and c2 = '1' and c4 = '1'，这种情况因为c3没有在条件中，所以只会用到c1,c2索引。

特殊情况，使用范围条件的时候，也会使用到该处的索引，但后面的索引都不会用到

例子： where c1 = '1' and c2 > '1' and c3 = '1'，这种情况从c2处已经断开，会使用到c1,c2索引，不会再使用到后面的c3,c4索引。

2、group by和order by 其实一样，也是遵从最左原则，可以看做继承where的条件顺序，但需要where作为基础铺垫，即没有where语句，单纯的group by | order by 也是不会使用任何索引的，并且需要和联合索引顺序一致才会使用索引。

例子：

group by c1 | order by c1，由于没有where的铺垫，不使用任何索引

where c1 = '1' group | order by c2，使用c1,c2索引

where c1 = '1' group | order by c3，只使用c1索引

where c1 > '1' group | order by c2，前面也说了，范围搜索会断掉连接，所以也只会使用c1索引

关于顺序：

where c1 = '1' group | order by c2,c3，使用c1,c2,c3索引

where c1 = '1' group | order by c3,c2，只使用c1索引

2.3 全文索引

FULLTEXT key()

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。

2.4 空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

3 索引的优缺点

 优点：

 1 所有的MySQL列类型都可以被索引，也就是可以给任意字段设置索引

 2 加快数据查询速度

 缺点：

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

 2 索引也需要占空间，我们知道数据表中的数据也会有最大上限设置的，

 如果我们有大量的索引，索引文件可能会比数据文件更快达到上限值

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

4 索引使用原则

 1 并不是每个字段都设置索引就好，也不是索引越多越好

 2 对更新频繁的表要避免进行过多的索引，对查询频繁的字段应该适当创建索引

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

 4 重复值较多的列上不要建立索引，重复值较少的列上要建立索引

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

5 索引示例

# where可以和group by一起使用

# where 必须在 group by前面

# where中不能使用聚合函数，having可以使用聚合函数

# 索引

# 在mysql库中会有一张表维护索引，记录每个索引值对应的行

-- 定义：索引用于快速定位包含目标条件的行；

-- 不使用索引，mysql默认的检索规则是从第一行开始读完整张表，表越大，查询花费时间越长。

# 1 单列索引

# 1-1 普通索引

ALTER TABLE stu_info

ADD INDEX k_no(stu_no);

# 1-2 唯一索引

ALTER TABLE stu_info

ADD UNIQUE k_no(stu_no);

# 1-3 主键索引

ALTER TABLE stu_info

ADD PRIMARY k_no(stu_no);

# 删除索引

DROP INDEX k_no ON stu_info;

# 组合索引

key k_no(id,stu_name,stu_no);

# 触发索引

SELECT * FROM stu_info WHERE id = ?

SELECT * FROM stu_info WHERE id = ? AND stu_name = ?

SELECT * FROM stu_info WHERE id = ? AND stu_name = ? AND stu_no = ?

# 触发不了索引

# 1 没有查询最左边的条件

SELECT * FROM stu_info WHERE stu_name = ?

# 2 条件必须有左边的字段，才会用到索引，中间如果断开了，则都不会用到后面的索引

SELECT * FROM stu_info WHERE id = ? AND stu_no = ?

-- 组合索引遵循最左前缀原则：

--  组合索引就是遵从了最左前缀，利用索引中最左边的字段来匹配行，这样的字段称为最左前缀