Mysql不止CRUD，聊聊索引我想很多人对mysql的认知可能就

我想很多人对mysql的认知可能就是CRUD（代表创建(Create)、更新(Update)、读取(Retrieve)和删除(Delete)操作），也不敢说自己会用和熟悉mysql ，当然我就是其中一个，虽然知道mysql有很多东西，但是一直都没有深入的了解和掌握，最近想着好好的把Mysql原理学习下，这篇就是开胃菜吧，以后的慢慢道来。本篇文章内容主是基于mysql的InnoDB存储引擎。
一、Mysql所以介绍索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件) ，而不是在select的字段中，实际上，索引也是一张“表” ，该表保存了主键与索引字段，并指向实体表的记录，虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE 。因为更新表时， MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件。说白了索引就是用来提高速度的，但是就需要维护索引造成资源的浪费，所以合理的创建索引是必要的。
1.1、索引的类别先去官网文档看看支持的索引类型，索引的实现方式如下图所示：（官网）
文章插图
由于本文是基于mysql的InnoDB存储引擎，索引我们主要看第一个表格，其他的表格可以自行的观看，都不难，从表格我们可以看出来， InnoDB存储引擎索引只支持BTREE类型的索引，索引的类别有Primary Key ， Unique ， Key ， FULLTEXT和SPATIAL 。当然也有其他的分法，按照索引列的数量分为单列索引和组合索引。

Primary Key（聚集索引）：InnoDB存储引擎的表会存在主键（唯一非null），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
单列索引：单列索引即一个索引只包含单个列
组合索引：组合索引指在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合
Unique（唯一索引）：索引列的值必须唯一，但允许有空值。若是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值
Key（普通索引）：是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值
FULLTEXT（全文索引）：全文索引类型为FULLTEXT ，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建
SPATIAL（空间索引）：空间索引是对空间数据类型的字段建立的索引， MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON 。 MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL

这里再说一下组合索引的遵循最左前缀原则：

order by使用索引最左前缀- order by a- order by a,b- order by a,b,c- order by a desc, b desc, c desc 如果where使用索引的最左前缀定义为常量 ， 则order by能使用索引- where a=const order by b,c- where a=const and b=const order by c- where a=const and b > const order by b,c不能使用索引进行排序- order by a , b desc ,c desc--排序不一致- where d=const order by b,c--a丢失- where a=const order by c--b丢失- where a=const order by b,d--d不是索引的一部分- where a in(...) order by b,c --a属于范围查询

创建一个简单的表：

CREATE TABLE my_test (`id` int(11) NOT NULL AUTO_INCREMENT,`username` varchar(50) DEFAULT NULL,`sex` varchar(5) DEFAULT NULL,`address` varchar(100) DEFAULT NULL,`birthday` datetime NOT NULL,`user_num` int(11) unique,PRIMARY KEY (`id`),index(username));

show index from my_test;

文章插图