高性能mysql_part3索引

一、索引基础知识

什么是索引

数据库索引是一种数据结构，它以额外的写入和存储空间为代价来提高数据库表上数据检索操作的速度。牺牲插入 更新等的效率

常用数据结构

• 二分查找法/折半查找
• B+树
• hash索引

基础知识

• 数据库中大多数索引都是通过B+树实现的
• MySQL中除了B+树索引外还需要关注Hash索引

哈希索引

• InnoDB Adaptive Hash Index
  在内存中为频繁使用的索引创建hash索引，加速查找

B+ Tree

• 在MySQL数据库中没指定类型，默认是指使用B+ Tree 数据结构进行存储
• 对于MySQL存储引擎而言，其实使用的B+ Tree是为了满足数据读写性能以及适配磁盘访问模式的优化数据结构，每一个叶子结点都包含指向下一个叶子结点的指针
• 索引分为聚簇索引（clustered index）、辅助索引（Secondary Index）、堆索引
  如图所示，辅助索引查到后还需要使用PK再去聚簇索引中找一次
  

索引类型

哈希索引、B+Tree索引、全文索引、空间索引、分行树索引

InnoDB分析

索引类型

• 主键索引：默认使用–显示主键、第一个唯一索引、内置的6字节ROWID
• 辅助索引：非聚簇索引均为辅助索引,
• 联合索引： 多个字段联合组建索引
• 覆盖索引：通过索引即可查询到所有记录，不需要回到聚簇索引中

Extra列为Using Index表示优化器使用了覆盖索引。
（eg：desc select a, b from test where a=‘MARY’ order by b\G)
（eg：show index from table_name）

二、索引使用技巧

基础知识：

谓词、过滤因子、基数、选择率、回表

谓词：

条件表达式，通俗讲到就是过滤字段，WHERE子句由一个或多个谓词组成

过滤因子

描述谓词到选择性，即表中满足谓词条件的记录数所占的比例。因子越小代表能过滤的数量越多，应该在此字段上建立索引。

基数（Cardinality）

（某个键值去重后的行数），索引列不重复记录数量的预估值

选择率

count(distinct city) / count(*) 选择率越接近1则更适合创建索引

回表

无法通过索引扫描访问所有数据，需要回到主表进行数据扫描并返回

哪里查看Cardinality

查看

show index from city

更新策略

• 触发统计：表中超过1/16的数据发生变化、stat_modified_counter > 2000 000 000
• 采样统计：每次随机获取innodb_stats_persistent_sample_pages页的数量进行Cardinality统计
• 手动统计：alter table table_name engine=innodb、analyze table table_name
  

如何查看是否走索引

关注的参数

extra执行情况的描述和说明，use index > use where > use filesort

key_len的计算规则

1. 定长数据类型：char，int，datetime，需要有是否为空的标记，这个标记需要占用1个字节
2. 变长数据类型：比如varchar，除了是否为空的标记外，还需要有长度信息，需要占用2个字节
3. 字符集不同：gbk一个字符2个字节，utf8一个字符3个字节

索引实践

1. 定位由于索引不合适或者缺少索引而导致的慢查询语句
2. 设计索引
3. 创建索引策略
4. 调优索引
   
   
   
   

命名、创建规范