mysql索引

索引概述

• 建立数据库的目的是管理大量数据，而建立索引的目的就是提高数据检索效率，改善数据库工作性能，提高数据访问速度
• 索引是数据库管理系统中一个满足特定查找算法(排序)的数据结构，这些数据结构以某种方式引用（指向）数据，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树

• 一般来说索引本身也很大，不可能全部存储咋内存中，需要占用一定的磁盘空间，因此索引往往以索引文件的形式存储在磁盘上（索引是一个文件，保存在物理空间中）
• 没有索引的sql语句，会进行全表扫描，从上到下依次查询，直到找到符合条件的数据，效率是非常低的
• 索引页中包含着索引键值和它所指向该行记录在数据页中的物理位置，叫做行定位符（RID：Row ID）
• 一定要在“加快查询速度”与“降低修改速度”之间做好平衡

索引的优缺点

优势：

• 类似于书籍的目录索引，提高数据的检索效率，减低数据的IO压力
• 通过索引对数据进行排序，减低数据排序的成本，降低CPU的消耗

劣势：

• 索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所有索引列也是要占用空间的
• 创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率

索引的使用场景

• 对查询频次较高，且数据量比较大的表建立索引，
• 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建N个索引，如果where子句中使用了该索引的前几个字段，那么这条查询sql可以利用组合索引来提升效率
• 使用唯一索引，区分度越高，使用索引的效率越高，若是不能有效区分数据的列不适合做索引列
• 更新频繁字段不适合创建索引，索引可以有效提升查询数据的效率，但索引数量不是越多越好，索引越多，维护索引的代价就越大，对于insert,update,delete操作较多的表来说，索引过多，会引入相当高的维护代价，减低DML操作的效率，另外索引过多，Mysql选择索引时也会影响效率
• 使用短索引，索引创建之后也是使用硬盘来存储的，短索引可以在特定大小的空间内存储更多的索引值，相应的可以提升Mysql访问索引的I/O效率，也可以提升整体的访问效率
• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
• 定义有外键的数据列一定要建立索引
• 对于定义为text、image和bit的数据类型的列不要建立索引
• 表中记录太少不适合建立索引

索引结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引

索引是在Mysql的存储引擎层中实现的，而不是在服务层，所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型

InnoDB引擎主要支持的BTREE索引

索引的分类

主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引：索引列的值必须唯一，但允许有空值，NULL值可以出现多次

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值

其中唯一索引和普通索引可以有单列或者多列组成

覆盖索引：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖

 

索引语法

• 创建表时建索引

CREATE TABLE user_index2 (
	id INT auto_increment PRIMARY KEY,
	first_name VARCHAR (16),
	last_name VARCHAR (16),
	id_card VARCHAR (18),
	information text,
	KEY name (first_name, last_name),
	FULLTEXT KEY (information),
	UNIQUE KEY (id_card)
);

• 使用ALTER TABLE命令去增加索引，ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

• 使用CREATE INDEX命令创建，CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

CREATE INDEX index_name ON table_name (column_list);

• 根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

创建索引需要注意点

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高
• 有索引且查询条件能使用索引时，数据库会先度取索引，根据索引内容和查询条件，查询出定位符（ROWID），再根据ROWID取出需要的数据。由于索引内容通常比全表内容要少很多，因此通过先读索引，能减少I/O，提高查询性能
• 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.

视图

视图（view）是一种虚拟存在的表，视图并不在数据中真实存在，通俗的讲，视图就是一条SELECT语句执行之后的结果集，视图相对于普通的表的优势主要包括以下几列

简单：使用视图的用户完全不需要关心后面对应的表结构，关联条件，筛选条件，对用来说已经是过滤好的结果集

安全：使用视图的用户只能访问他们被允许的结果集，相当于对表的列实现了权限管理

数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列不会影响视图的使用，源表列名改变，则可以通过修改视图来解决，不会造成对访问这的影响