1. 索引

1. 索引的作用

  1. 提供了类似于书中目录的作用,目的是为了优化查询

2. 索引的分类

  1. B树索引 *******************
  2. Hash索引
  3. R树
  4. Full text
  5. GIS

B树索引

        1       10         20   根索引
       1 5 9   10 15 19  20 15 29  茎索引
      1  5 9  ----                                  叶索引
      2  6
      3  7
      4  8
      Q
依次按照范围向下查找   
当查找一个范围时，
B树索引的强大之处在于可横向查询，减少回表次数
例如 查询 5-9
只需要遍历一次根 茎索引。即可直接在叶索引处横向查询
例如查询 9-15
只需遍历一次根索引，就可在茎索引间横向查询。

#用大白话说是这样 具体的过程 需要百度。

辅助索引
(1). 索引是基于表中,列(索引键)的值生成的B树结构
(2). 首先提取此列所有的值,进行自动排序
(3). 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
(4). 然后生成此索引键值所对应得后端数据页的指针
(5). 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度

聚集索引
(1)表中设置了主键,主键列就会自动被作为聚集索引.
(2)如果没有主键,会选择唯一键作为聚集索引.
(3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)
(4)聚集索引叶子节点,就是磁盘的数据行存储的数据页

聚集索引只能有一个,非空唯一,一般是主键
辅助索引,可以有多个,是配合聚集索引使用的
聚集索引叶子节点,就是磁盘的数据行存储的数据页
MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据
辅助索引,只会提取索引键值,进行自动排序生成B树结构

3. 查看索引

desc table;  KEY列就是索引列
pri 主键索引
mul 辅助索引
uni 唯一索引

4. 索引树高度

1. 数据量级, 解决方法:分表,分库,分布式
2. 索引列值过长 , 解决方法:前缀索引
3. 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用  #enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)

5. 索引增删

alter table emp add index index_name(name);  #index_name 代指索引名字
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

在索引创建期间是会锁表的

alter table emp add index idx_co_po(name,age);

alter table emp add index idx_di(Position(3)); 取Position的前三个字符
注意：数字列不能用作前缀索引。

唯一索引的列 必须为唯一
alter table emp add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

alter table emp drop index idx_uni1;

2. 执行计划分析

2.1 作用

将优化器选择后的执行计划 截取出来，用于判断语句的执行效率

2.2 获取计划

desc select * from mysql.user;
explain select * from mysql.user;
#仅仅是提取了执行计划， 并不会真的执行。

image.png

2.3 分析计划

实际生产环境中，可能不止是从一张表上获取数据。可能会因为一张表的问题 导致整个SQL都很慢

table 表名
type  查询的类型
  全表扫描  ALL
  索引扫描  index range ref eq_ref const(system) NULL    性能从左到右 依次变高
possible_keys  #可能会用到的索引
key        #    真正选择的索引 用于判断是否是 理想的索引
key_len    #    索引的覆盖长度 指的是 最大索引长度
Extra

2.4 索引扫描分析

index: 全索引扫描。 ## desc select id from emp; 
从头到尾遍历索引

range：范围索引扫描  （> < >=, between and , or , in ,like）
desc select id from emp where id > 2;
上述列的范围指示符虽然都属于range，但是大于小于 between and的性能大于剩下几个的，其原因就在B树索引的回表次数上。 联系范围内的回表次数 小于 非连续范围。
对于辅助索引来说 != not in不走索引
对于主键索引列来说 他们俩会走range索引
对于剩下的类型 我们一般使用union all 进行改写。 其索引会升级为 ref

ref 辅助索引#等值查询
desc select * from emp where name = "程咬铁" union all  select * from emp where name = "程咬金";

多表查询字表使用主键列或唯一键列 作为唯一条件的时候

多表连接 分为 驱动表和子表 
驱动表为 from后的第一个表 剩余的都为字表。
而 写多表查询的时候 我们一般要求 驱动表的数据量要比较小。
而on条件 应该使用 索引列，主键列，唯一键。 此条件下的速率应该是最快的
这个 应是设计时 就搞好的。
之所以要求驱动表的数据量小。  是因为 驱动表 默认是必走全表扫描的。

例子
desc select  dep.dep_name  from tmp inner join  dep  on tmp.dep_id = dep.dep_id  where tmp.name = '程咬金';

主键或者唯一键的 等值查询
desc select * from emp where id = 1;

理论是数据库性能的天花板， 因为它不工作。 就是表内没有相关的数据