数据库索引与索引失效情况

1.为什么要有索引？
索引可以加快查询速度，尤其是对于一些复杂查询操作，加上合适的索引能够大大提高查询效率。

2.什么时索引？
索引在mysql中也叫一种“键”，时是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是表中数据量越来越大的时候，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了，索引能轻易将查询性能提高好几个数量级。

索引相当于字典的音序表，如果要查某个字，不使用音序表则需要从几百页中逐页去查。

3.索引的原理
索引的目的在于提高查询效率，与我们查阅图书的目录是一个道理，先定位到章节，然后定位到该章节下的一个小结，然后找到页数。
本质都是：通过不断缩小先要获取的数据的范围，来筛选出最终要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询（<、>、between、in)、模糊查询(like)、并集查询or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?
我们回想字典的例子，能不能把数据分成段，然后分段查询呢?最简单的如果1000条数据，1到100分成第一段，10到200分成第二段，201到300分成第三段…这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好?按照搜索树的模型，其平均复杂度是lgN，具有不错的查询性。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一芳面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右。所以简单的搜索树难以满足复杂的应用场景。

要知道数据库的数据是保存在磁盘上的，索引也是保存在磁盘上的。为了提高性能，我们需要把磁盘内的数据和索引数据读到内存中去，才能够加快对数据的访问。当然，也不是一次性把所有都读到内存中，而是采用分块读取的方式，所以在mysql里采用了b+tree的数据结构，能够让我们一块儿一块儿的读取磁盘数据，这个块儿，我们称之为“页”。

4.索引的数据结构
Mysql主要用到两种结构：B+tree 和 Hash索引

Inoodb 存储引擎默认是B+tree索引
Memory 存储引擎默认是Hash索引

MysQL中，只有Memory(Memory表只存在内存中，断电会消失，适用于临时表)存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引（但我们不用）。Hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

B+Tree是mysql使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎模式的索引类型。相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序、范围查询等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作。

对比：
hash类型的索引: 查询单条快，范围查询慢
btree类型的索引: b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它)

5.B+树在实现索引上的优势以及过程
// TODO

6.索引失效情况
牢记七字口诀——模 型 数 空 运 最 快
口诀字面意思就是，要运送一个产品模型的话，要用空运，不要用陆运和海运，数空运最快。叫做：模型数空运最快。

模：模糊查询的意思。like的模糊查询以%开头，索引失效。比如：
SELECT * FROM user WHERE name LIKE ‘%老猿’;

型：代表数据类型。类型错误，如字段类型为varchar，where条件用number，索引也会失效。比如：
SELECT * FROM user WHERE height= 180;
height为varchar类型导致索引失效。

数：是函数的意思。对索引的字段使用内部函数，索引也会失效。这种情况下应该建立基于函数的索引。比如：
SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
create_time字段设置索引，那就无法使用函数，否则索引失效。

空：是Null的意思。索引不存储空值，如果不限制索引列是not null，数据库会认为索引列有可能存在空值，所以不会按照索引进行计算。比如：
SELECT * FROM user WHERE address IS NULL不走索引。
SELECT * FROM user WHERE address IS NOT NULL;走索引。
建议大家这设计字段的时候，如果没有必要的要求必须为NULL，那么最好给个默认值空字符串，这可以解决很多后续的麻烦（切记）。

运：是运算的意思。对索引列进行（+，-，*，/，!, !=, <>）等运算，会导致索引失效。比如：
SELECT * FROM user WHERE age - 1 = 20;

最：是最左原则。在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

快：全表扫描更快的意思。如果数据库预计使用全表扫描要比使用索引快，则不使用索引。