建索引的几大原则
A、最左前缀匹配原则，非常重要的原则，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的顺序可以任意调整。
B、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
C、尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
D、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
E、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。1、MySQL 索引简介
1.1 MySQL 索引是什么？
索引是一个单独的、存储在磁盘上的数据库结构，包含着对数据表里所有记录的引用指针。
1.2 MySQL 索引的存储类型有哪些？
MySQL中索引的存储类型有两种，即 BTree 和 Hash。
1.3 MySQL 索引在哪里实现的？
索引是在存储引擎中实现的。（MySQL 的存储引擎有：InnoDB、MyISAM、Memory、Heap）
InnoDB / MyISAM 只支持 BTree 索引
Memory / Heap 都支持 BTree 和 Hash 索引
1.4 存储引擎是什么？
存储引擎就是指表的类型以及表在计算机上的存储方式。
1.5 索引的优缺点有哪些？
优点：
提高数据的查询的效率（类似于书的目录）
可以保证数据库表中每一行数据的唯一性（唯一索引）
减少分组和排序的时间（使用分组和排序子句进行数据查询）
被索引的列会自动进行分组和排序
缺点：
占用磁盘空间
降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件）
2、MYSQL 索引的分类
1、普通索引 和 唯一索引
普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入重复值和空值
唯一索引：要求索引列的值必须唯一，但允许有空值
如果是组合索引，则列值的组合必须 唯一
主键索引是一种特殊的唯一索引，不允许 有空值
2、单列索引 和 组合索引
单列索引：一个索引只包含单个列，一个表可以有多个单列索引
组合索引：在表的多个字段组合上创建的索引
只有在查询条件中使用了这些字段的 左边字段时，索引才会被使用（最左前缀原则）
3、全文索引
全文索引的类型为fulltext
在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值
全文索引可以在 char、varchar 和 text 类型的列上创建
4、空间索引
空间索引是对空间数据类型的字段建立的索引
MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon
MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引
创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。
5、前缀索引
在 char、varchar 和 text 类型的列上创建索引时，可以指定索引列的长度
3、MySQL 索引的数据结构
MySQL 索引的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。
Hash：使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。
Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。
BTree：属于多叉树，又名多路平衡查找树。
性质：
BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址）
BTree 节点的键值按非降序排列
BTree 所有叶子节点都位于同一层（具有相同的深度）
查询过程，例如：Select * from table where id = 6;
BTree 的不足：
不支持范围查询的快速查找（每次查询都得从根节点重新进行遍历）
节点都存储数据会导致磁盘数据存储比较分散，查询效率有所降低
B+Tree：在 BTree 的基本上，对 BTree 进行了优化：只有叶子节点才会存储 键值 - 数据，非叶子节点只存储 键值 和 子节点 的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表。
等值查询，例如：Select * from table where id = 8;
范围查询，例如：Select * from table where id between 8 and 22;
B+Tree 的优点：
保证了等值查询和范围查询的快速查找
单一节点存储更多的元素，减少了查询的 IO 次数
4、MySQL 索引 的实现
4.1 MyISAM 索引
MyISAM 的 数据文件（.myd） 和 索引文件（.myi） 是分开存储的
MyISAM（B+Tree）叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
MyISAM 的 主键索引（Primary key）和 辅助索引（Secondary key）在结构上没有任何区别，只是 主键索引 要求 键值唯一，而 辅助索引 键值 可以重复
4.2 InnoDB 索引
数据和索引都存储在一个文件中（.ibd）
一般情况下，聚簇索引等同于主键索引；除 聚簇索引 外的所有索引 均称为 辅助索引
InnoDB（B+Tree）叶子节点中存储的键值为索引列的值
如果是聚簇索引，数据为整行记录（除了主键值）
如果是辅助索引，数据为该行的主键值
每一张表都有一个聚簇索引
如果表中有定义主键，主键索引用作聚簇索引
如果表中没有定义主键，选择第一个不为 NULL 的唯一索引列用作聚簇索引
如果以上都没有，使用一个 6 字节长整形的隐式字段 ROWID （自增）用作聚簇索引
根据在 辅助索引树 中获取的 主键id，再到 主键索引树 查询数据的过程 称为 回表 查询
组合索引
遵循 最左匹配（最左前缀）原则：
使用 组合索引 查询时，MySQL 会一直向右匹配直至遇到范围查询（>、<、between、like）就停止匹配。
只有第一列是有序的，其它列都是无序的（最左匹配原则的原因）
主键索引（聚簇索引）：
辅助索引：
组合索引：
覆盖索引：
覆盖索引不是一种索引结构，而是一种优化手段
我们只需要查询 组合索引 中的字段，而不需要表中的其它字段，在这过程中不会产生回表现象，这种情况称为 覆盖索引
create index idx on user(name, age, gender);
-- 使用覆盖索引
explain select name, age, gender from user where name ='万叶'and age =18 and gender ='0';
-- 未使用覆盖索引
explain select * from user where name ='万叶' and age = 18 and gender = '0';
5、MySQL 索引 的使用
5.1 MySQL 索引 的基本语法
定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引
创建表时：
create table table_name(
[col_name data_type] [unique | fulltext | spatial...],
[unique...] [index | key] [index_name] (col_name [length], ...)
);
create table user (
id INT NOT NULL,
name CHAR(30) NOT NULL,
unique index uniqueIdx(id)
);表已存在时：
-- 第一种
alter table table_name add [unique...] [index | key] [index_name] (col_name [length], ...);
alter table user add unique index uniqueIdx(id);
-- 第二种
create [unique...] index index_name on table_name (col_name [length], ...);
create unique index uniqueIdx on user(id);
-- 删除索引
drop index index_name on table_name;5.2 怎么判断要不要加索引？
加索引：
数据本身具有某种的性质，如：唯一性、非空性…
频繁进行 分组或排序 的列；如果待排序的列有多个，可以建立 组合索引
不加索引：
经常更新的列
列 的值类型 很少，如 性别
条件中用不到的列
参与计算的列
数据量小的表
5.3 只要创建了索引，就一定会生效吗？
不一定。当使用 组合索引 时，如果没有遵循 最左匹配 原则，索引不生效。
例如，创建 id、name、age 组合索引
id、（id、name）、（id、name、age）查询，索引生效
age、（age、name）查询，索引不生效
5.4 怎样判断索引是否生效？
使用 explain 关键字。
possible_keys：MySQL 在搜索数据记录时可选用的各个索引
key：MySQL 实际选用的索引
例如：
explain select * from user where id = 1;
5.5 怎么避免索引失效?
使用组合索引时，遵循 最左匹配 原则
不在索引列上进行任何操作，如：计算、函数、类型转换
尽量使用覆盖索引
索引列 尽量不使用 不等于（!= / <>）条件、通配符开头的模糊查询（like %abc）、or 作为连接条件
字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）
索引会失效的多种情况：
评论0
暂时没有评论