2、myslq索引原理与使用规则

一、索引本质
1、索引是什么？
数据库索引，是数据库管理系统（DBMS）中有序的数据结构，以协助快速查询，更新表中的数据。

2、索引类型
（1）Normal（普通索引）：标识index、key
（2）Unique（唯一索引）：标识unique index、unique key，不能重复
（3）Primary（主键索引）：不能重复，且不能出现空值
（4）Full Text（全文索引）：标识fulltext key、fulltext index
注：对于varchar、text类型的字段，查询字段中包含的内容就可以用全文索引。主要用于大文本数据类型的查询
例：select * from 表名 where match(字段名) against('要查询的内容' IN NATURAL LANGUAGE MODE);
（5）前缀索引：

二、索引底层数据结构

（一）、b+tree索引
1、index中的page默认为16KB，os中的block默认为4KB，page由block组成
2、mysql采用b+tree
3、b+tree特点：page中指针的个数等于index个数；叶子节点采用环形单向链表结构；只有叶子节点存储数据，非叶子节点不存储数据
4、主键最好是自增的，防止page分裂，page分裂会消耗cpu资源，同时也会造成磁盘碎片
5、索引存放的位置：show variables like 'datadir';

（二）、hash索引
1、根据字段值计算出hash值，根据hash值找对应的数据，一般用于（=、in）操作，不能用于（>、<）操作。且会存在hash冲突的问题。
2、Innodb不支持hash索引，Memory支持hash索引
3、Adaptive Hash Index自适应的hash索引，指向buffer-pool中的内容

三、不同存储引擎中索引的落地方式
（一）、myisam存储引擎
1、有三个文件user_myisam.frm（定义表结构的文件）、user_myisam.MYD（存储数据的文件）、user_myisam.MYI（存储索引的文件）
2、特点：数据和索引分开存放
3、主键索引和辅助索引中叶子节点存储的数据都是数据的磁盘地址

（二）、Innodb存储引擎
1、有两个文件user_innodb.frm（定义表结构的文件）、user_innodb.ibd（存储数据和索引的文件）
2、特点：数据和索引放在一起
3、聚集索引（主键索引）：叶子节点存储的数据是具体的数据。索引值的顺序和表数据行存储的物理顺序是一致的。
4、辅助索引：叶子节点存储的数据是主键的值。若想查到具体的数据，再根据主键的值从主键索引的叶子节点中获取具体的数据。
思考1：辅助索引的叶子节点为什么不存放数据的物理地址而是存主键的值？发生页分裂时，数据的物理地址就会发生变化，而主键的值是不会发生变化的。
思考2：没有主键怎么办？innodb先会选择一个not null unique key的字段建立主键索引，没有的话innodb会选择隐藏的字段rowid建立主键索引
可以看到：select _rowid as name from 表名;

四、索引的创建和使用原则
（一）、列的离散度
1、离散度公式：count(distinct(column_name)):count(*)
2、查看一个表中的索引：show indexes from 表名;--->Cardinality（该字段上不同的值有多少，可以说明离散度）
3、应该在离散度高的字段上添加索引
4、查看sql性能：explain+sql语句。可以看到是否使用了索引（key字段），和扫描到的行数（rows字段：扫描到的行数越少性能越好）

（二）、联合索引最左原则
1、建立联合索引：alter table 表名 add index '索引名'('字段名1','字段名2');
2、最左原则
3、建立联合索引：index(a,b,c)，实际上是建立了三个索引分别为：index(a,b,c)、index(a,b)、index(a)

（三）、覆盖索引
1、生么是回表？扫描两次索引：先扫描辅助索引找到主键值，在根据主键值扫描主键索引找到具体的数据。
2、什么是覆盖索引？在name上建立索引后（name-index），用select name from 表名查询，就不需要再扫描主键索引了，因为name-index中包含了name的值
3、是否用到了覆盖索引？explain+sql语句。（Extra字段：空值则没有使用覆盖索引，useing index则有使用覆盖索引）
4、覆盖索引的意义？减少了一次主键索引的扫描。索引当字段上有索引时，查询时尽量少使用*。

（四）、索引条件下推（ICP）
1、数据过滤发生在server层，索引过滤发生在存储引擎层。索引条件下推就是让数据过滤发生在存储引擎层。
2、先关配置：show variables like 'optimizer_switch'; 中的index_condition_pushdown=on; 默认打开，关闭
set optimizer_switch='index_condition_pushdown=off';  （默认是session级别，只对当前的回话有效）
3、explain select * from 表名 where last_name = 'sun' and first_name like '%an';
当set optimizer_switch='index_condition_pushdown=off';时，Extra=using where
当set optimizer_switch='index_condition_pushdown=on';时，Extra=using index condition

（五）、使用1、在条件字段上加索引，如：where、order、join on
1、在条件字段上加索引，如：where、order、join on
2、索引的个数不要过多。
3、区分度低的字段不要建索引
4、频繁更新的值，不要作为主键或索引。因为会发生数据结构的调整，如页分裂
5、组合索引把区分度高的放前面
6、创建组合索引，而不是修改单列索引
7、过长的字段如何建立索引？Full Text（全文索引），主要用于大文本数据类型的查询
8、为什么不建议用无序的值（如：uuid、身份证）作为索引？UUID和身份证号是无序的，插入时为了将新纪录放到合适的位置需要去移动已存在的数据，容易早晨page分裂，消耗cpu资源
9、什么时候用不到索引？
a、索引上使用函数（replace、substring、concat、sum、count、avg）、表达式
b、字符串不加引号，出现隐式转换
c、like条件中前面带%
d、负向查询能用到索引么？!=、<>这两个是可以用到索引的