MySQL索引原理详解

先说走索引的情况，比如说满足dept_id=12的主键id有1w个，而且均匀分布在不同的page里，那么mysql需要一个page一个page的把结果读取出来(random io)。需要磁盘io 1w次。全表扫描的话，再比如全表有10w条记录，表文件为400m 大小(不少了吧)，那么因为是顺序读盘，一次最多读1m数据，那么只需要磁盘io 400次。

本节参考：

《mysql没有索引的字段_MySQL-mysql索引与select字段不是没关系吗？》

《MySQL在有索引列情况下select *的输出结果顺序》

4、为什么order by字段上使用索引字段更快

B+树的索引都是按照顺序存储的，所以在对结果处理时无需做过多的排序操作

5、多表关键查询时，为什么要小表驱动大表（小表在前，大表在后）

一、驱动表的定义

当进行多表连接查询时， [驱动表] 的定义为：指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]

未指定联接条件时，行数少的表为[驱动表]（Important!）

忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断

既然“未指定联接条件时，行数少的表为[驱动表]”了，而且你也对自己写出的复杂的 Nested Loop Join 不太有把握（如下面的实例所示），就别指定谁 left/right join 谁了，请交给 MySQL优化器运行时决定吧。

如果您对自己特别有信心

二、MySQL数据库引擎分类

memory [ˈmeməri]

2.1. Innodb 引擎分类

MySQL 常用索引分为：普通索引、唯一索引、主键索引、组合索引、全文索引

2.1.1.普通索引

关键字：Normal [ˈnɔːml]

普通索引是最基本的索引类型，唯一的任务是加快对数据的访问速度，没有任何限制。

默认会使用btree

// 1、直接创建索引
create index index_name on table(column(length));
// 常规创建
create index index_name on user(name);
// 针对字符串的前几个字符进行创建索引，节省空间，减少比较时间、解决排序问题
create index index_name on user(name(5));
//2、修改表结构的方式新增索引
alter table table_name add index index_name(column(length));
// 常规创建
alter table user add index index_name(name);
// 对字符串的前几个字符进行创建索引
alter table user add index index_name(name(10));
// 3、创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` char(20) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (name(length))
)
// 4、删除索引
drop index index_name on table;

2.1.2.唯一索引

关键字：Unique [juˈniːk]

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

// 1、 创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
// 常规创建
create unique index index_name on user(name);
// 2、修改表结构
ALTER TABLE table_name ADD UNIQUE indexName(column(length))
// 常规创建
alter table user add unique index index_name(name);
// 3、创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `name` char(20) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    UNIQUE INDEX index_name (name(length))
)

2.1.3.主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

// 创建主键的时候自动创建主键索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

主键索引和唯一索引的区别

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

唯一性索引列允许空值，而主键列不允许为空值。

主键列在创建时，已经默认为空值 + 唯一索引了。

主键可以被其他表引用为外键，而唯一索引不能。

一个表最多只能创建一个主键，但可以创建多个唯一索引。

主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

在 RBO 模式下，主键的执行计划优先级要高于唯一索引。两者可以提高查询的速度。

2.1.4.组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

2.1.5.全文索引

全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的- where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用。

目前只有char、varchar，text 列上可以创建全文索引。

在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多

// 创建表的适合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `content` text CHARACTER NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);
// 修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
// 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

2.2.索引方式

MySQL 索引方式分为：B-TREE 和 HASH。

1、B-TREE可理解为”排好序的快速查找结构”

每个叶子节点到根节点距离相等

所有被索引的列都是排过序的

适合范围查找、支持数据排序

2、HASH理论查询时间复杂度为O(1)

在磁盘随机存放数据

仅支持”=”,”IN”和”<=>”精确查询，不能使用范围查询

不支持排序

无法进行前缀索引

在任何时候都不能避免表扫描

检索效率高，索引的检索可以一次定位

只有Memory引擎支持显式的Hash索引

必须回行，通过索引拿到数据位置,必须回到表中取数据

2.3.索引失效情况

1、like 以%开头，索引无效；

当like前缀没有%，后缀有%时，索引有效

2、or语句前后没有同时使用索引

当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

4、在索引字段上使用not，<>，!=

where中索引列有运算

不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。优化方法： key<>0 改为 key>0 or key<0。

5.联合索引失效情况

例如创建的索引是 key index （b,c,d）为联合索引，那么

有效情况： b || bc || bcd 这三种情况单独使用都是有效的

失效情况：bd || cd || d || c 这些情况下是无效的，组合列跨列使用都不能使索引生效

联合索引是最左匹配原则

在索引的排序上 , 是先按b排序 , 再按c排序 , 再按d排序