MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构

为什么要引入索引：

索引的目的在提高查询效率。如果没有索引就需要a-z进行全表扫描。

一般来说索引本身也很大，不可能全部储存在内存中，因此索引往往以索引文件的形式存储在磁盘上。

索引的优缺点：

  • 优点
  1. 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
  • 缺点
  1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的
  2. 虽然索引大大的提高了查询速度，同时却会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
  3. 每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  4. 索引只是提高效率的一个因素，如果MySQL有大量数据的表，就需要花时间研究建立最优秀的索引，或优化查询语句，索引都是不停的根据业务场景不停修改调整的

MySQL索引：

MySQL目前主要有以下索引类型：

  1. 普通索引key
  2. 唯一索引unique key
  3. 主键索引primary key
  4. 组合索引 index（name，age，gender）
  5. 全文索引

MySQL的索引结构主要包括有：

BTree索引、Hash索引、full-text索引、R-Tree索引

如果没有特别指明，都是指B+树别构的索引。

InnoDB存储引擎中的B+树索引是由二叉查找树，平衡二叉树和B树这三种数据结构演化过来的。

SHOW INDEX FROM student;//可以查询特定表的索引结构

二叉查找树：

二叉查找树的特点就是任何结点的左节点的键值都小于当前节点的键值，右节点大于当前节点。顶端的节点我们称之为根节点，没有子节点的节点我们称之为叶节点

平衡二叉树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二叉树会进行调整树上的节点来保持平衡。

平衡二叉树特点：平衡二叉树又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1

B树：

为了解决平衡二叉树的弊端（二叉树的节点非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘 IO，我们查找数据的效率将会极低！），我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是 B 树。

B 树（Balance Tree）即为平衡树的意思，下图即是一棵 B 树：

根节点的p1指向17 35节点

从上图可以看出，B 树相对于平衡二叉树，每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，

上述图中的 B 树为 3 阶 B 树，高度也会很低。

基于这个特性，B 树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

B+树：

B+数是平衡的多叉树，从根节点到每个叶子节点的高度差不超过1，而且同层次的节点间有指针互相连接。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+树和B树的不同：

1、B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。之所以这么做是因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。

如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数又会再次减少，数据查询的效率也会更快。

另外，B+ 树的阶数是等于键值的数量的，例如我们的B+ 树中每个节点可以存储3个键，3层B+ 树可以存储3*3*3=27个数据。如果我们的 B+ 树一个节点可以存储 1000 个键值，那么 3 层 B+ 树可以存储 1000×1000×1000=10 亿个数据。

一般根节点是常驻内存的，所以一般我们查找 10 亿数据，只需要 2 次磁盘 IO。

2、B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。

在 InnoDB 中，我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

二叉树

二分查找->二插排序树->平衡二叉树->B树（多插平衡树）->B+树

最终让这个树：更矮更胖，减少查找的次数，因为查找的次数和树的高度是相关的，数据库表的索引是放到硬盘上的，如果树的高度太高那么IO次数就会变多。

哈希索引（Hash索引）

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

聚集索引 VS 非聚集索引：

在 MySQL 中，B+ 树索引按照存储方式的不同分为聚集索引和非聚集索引。

①聚集索引（聚簇索引）：以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。

这是因为 InnoDB 是把数据存放在 B+ 树中的，而 B+ 树的键值就是主键，在 B+ 树的叶子节点中，存储了表中所有的数据。

这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。

②非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

覆盖索引（Covering Index），也可以称为索引覆盖：

就是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。

建立了索引index(col1,col2,clo3)，查询时候没有使用select *，也没有用select col1,col2,clo3,col4,col5，而是使用select col1,col2,col3，查询列要被所建的索引覆盖。

1、ALTER TABLE

在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)//说明：其中table_name是要增加索引的表名//column_list指出对哪些列进行索引//多列时各列之间用逗号分隔。//索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称//另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

2、CREATE INDEX

CREATE INDEX可对表增加普通索引或UNIQUE索引

CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)//说明：table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义//另外，不能用CREATE INDEX语句创建PRIMARY KEY索引。

3、删除索引

可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。

第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

4、查看索引

mysql> show index from tblname;mysql> show keys from tblname;

索引的使用场景、索引优化、Sql优化

需要创建索引的情况

1、主键自动建立唯一索引 Primary Key = Unique Key + Not Null

2、频繁作为查询条件的字段应该创建索引（银行系统的银行账号、电信系统的手机号）

3、查询中与其它表关联的字段，外键关系建立索引

4、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

5、查询中统计或者分组字段

6、单值/组合索引的选择问题：在高并发下倾向创建组合索引index(name,age,gender)

不需要创建索引的情况

1、表记录太少

2、经常增删改的表：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

3、频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重了IO负担

4、Where条件里用不到的字段不创建索引，如果根据银行卡号查找就要建立索引

5、数据重复且分布平均的表字段（如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）。

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。

　一个索引的选择性越接近于1，这个索引的效率就越高

SQL中的逻辑删除和物理删除

在实际开发中基本都会有删除数据的需求，删除又分为逻辑删除和物理删除。下面说下二者的区别：

一、所谓的逻辑删除其实并不是真正的删除，而是在表中将对应的是否删除标识（is_del）或者说是状态字段（status）做修改操作。比如0是删除，1是未删除。在逻辑上数据是被删除的，但数据本身依然存在库中。

对应的sql语句一般是这样的：

update... set status/is_del=...

这样在做查询操作的时候，就可根据此字段进行查询，有删除标识的即可不显示。

二、物理删除就是真正的从数据库中做删除操作了，对应的sql语句为 delete … where …做物理删除操作的数据将会不在库中了。

逻辑删除的目的：1、为了大数据分析，直接删除就没有数据了 2、删除后索引维护成本高

索引优化、SQL优化、避免索引失效

1、尽量全值匹配

index(name,age,gender) 组合索引， where name=’zhansgan’ and age=23 and gender=’男’，全值匹配，使用索引效率高。

当建立了索引列后，能在where条件中使用索引的尽量使用索引

2、遵循最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中间列。

带头大哥不能死，中间兄弟不能断。

index(name,age,gender)组合索引，where后面是name才会使用索引，否则都是全表扫描，可以理解为有name这个火车头索引就不会失效，有了这个火车头火车就可以跑，没有火车头只有车厢就是全表扫描。

where name=’zhangsan’ and age=23,where name=’zhangsan’ , name作为开头上面的索引是有效的

where age=23, gender=’男’ name不作为开头索引无效，全表扫描

where name=’zhansgan’ and gender=’男’ ,中间兄弟不能断，只用了索引的一部分name

3、不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

4、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少使用 select * ，使用selectname,age,gender

5、mysql 在使用不等于(!= 或者)的时候无法使用索引会导致全表扫描

6、is null，is not null 也无法使用索引

7、like 如果以通配符开头(’%abc…’)，会导致mysql索引失效，而变成全表扫描的操作

8、少用or，用它来连接时会索引失效