mysql索引

explain来查看sql查询使用情况

索引基本原理

• 创建索引的列的内容排序
• 排序结果生成倒排表
• 倒排表内容上拼上数据地址链
• 查询时，先拿倒排表内容，再取数据地址链，最后拿到具体数据。

增加4种索引

（主键索引区（PI）关联数据地址，普通索引区（SI）关联id地址）

• 普通索引 ( 没唯一性限制，允许NULL)
  alter table 表名 add index index_name(字段名)
• 主键索引（不允许重复，不允许NULL，一表一个）
  alter table table_name add primary key(column)
• 唯一索引（不允许重复，允许NULL，一表多个）
  alter table name_name add unique(column);
• 全文索引(搜索引擎使用)
  alter table table_name add fulltext(col)

删除索引

• alter table table_name drop pKEY 索引名删除普通、唯一、全文索引
• alter table table_name drop primary key 删除主键索引（PS：自增不能直接执行此操作，自增依赖主键索引）
• 对百万级别以上的数据先删除索引在删除数据，最后重新建立索引比直接删除快。

索引覆盖

select查询字段全建立过索引，引擎直接在索引表找，否则有一个字段没建立索引进做全表扫描。

• 尽可能在select后只写必要查询字段，增加索引覆盖几率。

索引关于sql语句

排序 order by

order by （col）col字段没索引会从磁盘读到内存在排序，影响性能。

• 建立index（col）索引直接按索引顺序和映射关系取数据。

like 通配符开头or没使用常量，不会使用索引。

join on on后字段加索引可增加效率。

索引数据结构

• InnoDB存储引擎默认：B+树

InnoDB存储引擎

• 插入缓冲
• 二次写
• 自适应哈希索引
• 预读

索引设计原则

• where子句中的col or 连接子句中指定的col
• 基数小的类索引效果差，不用建
• 使用短索引，对长字符串列进行索引，指定一个前缀长度，节省空间。
  index(field（10）)使用字段值前10个字符建立索引。
• 不要过度索引，其需要额外磁盘空间。

创建索引原则

• 最左前缀匹配原则。–mysql遇到范围查询（>、<、between、like）会停止匹配索引

  eg: a=1 and b = 1 and c > 3 and d = 4 建立（a，b,c,d）顺序的索引，d就用不到索引

• 较频繁作为查询条件的字段才建

• 更新频繁的字段不适合建索引

• 区分度低的字段不适合做索引

创建索引时要注意什么？

• 非空字段
• 取值离散大的字段 （count（）返回值可以查看字段的差异值，返回值越大说明字段唯一值越多离散程度越高）
• 索引字段越小越好

eg:性别 男 女 未知 区分度低

• 尽量扩展索引，不要新建

eg: 表已经有a的索引，现在要加（a,b）的索引，那么只需修改原来的索引。

• 有外键的数据列要建索引

• 查找少，重复值多的不要建

• text , image ，bit 的数据类型的列不建

B树和B+树

区别

• B树：键和值可放在内部节点和叶子节点，叶子节点各种独立。频繁访问的数据放在靠近跟节点的地方将会大大提高热点数据的查询效率。

• 

• B+树：内部节点都是键，叶子节点有一条链相连。一次读取可以在内存页中获得更多key，缩小查找范围。全数据遍历时找到最小节点通过链进行顺序遍历即可，b树要每层遍历。

• 

hash索引

• hash索引无法进行范围查询。B+树所有节点遵循左节点<父节点，右一致，天然支持。
• 不稳定，键值重复多会发生hash碰撞，树的所有查询都是从根节点到叶子节点。
• 不能避免回表查询，B+树在（聚簇索引、覆盖索引）时可以只通过索引完成查询。
• 不支持模糊查询和最左前缀匹配。

聚簇索引和非聚簇索引

• 聚簇索引：将数据存储与索引放在一块，找到索引就找到了数据
• 非聚簇索引：数据存储与索引分开，索引叶子节点指向数据对应行。

联合索引

• 使用多个字段建立的一个索引。需要按照建立时的顺序挨个使用，否则无法命中索引。