mysql索引与sql调优探索.pptx

1 MYSQL 索引与SQL调优 玄惭 20:53 1 主要内容 Innodb逻辑存储结构 加速查询 加速插入 案例分析 20:53 2 优化顺序 20:53 3 磁盘评估：IOPS 20:53 4 IOPS:每秒进行读写（I/O）操作的次数,衡量随机访问的性能; IOPS类型：顺序读，顺序写，随机读，随机写(顺序读iops最高) 普通SAS：100-200 iops Intel SSD：2000+ (writes) ，5000+ (reads) 主要内容 Innodb逻辑存储结构 加速查询 加速插入 案例分析 20:53 5 Innodb逻辑存储结构 20:53 6 Innodb逻辑存储结构 古荡新村—第5幢—第5层—5-2号—进门第二间房间 第二个房间：对应表中的二行记录； 5-2号：有3个房间，对对应一个page； 第5层楼有2个住户单位，则对应一个extent； 第5幢楼有8个楼层，则对应一个segment； 古荡新村7撞居民楼对应一个tablespace；· Innodb逻辑存储结构 20:53 7 InnoDB 第一索引(primary index) 1.Innodb存储引擎的表就是索引组织表，表中的数据按照主键顺序存放； 2.索引组织表每张表的主键构造一颗B+树，在叶子节点中存放整张表的行记录； 3.所有叶子节点到根节点的高度H都相同，所以又叫平衡树； 4.叶子节点的数据按照key升序排列，节点间是一个双向链表； Innodb逻辑存储结构 20:53 8 InnoDB 主键索引访问成本： 单行查询成本(SELECT)： S = h IOPS 单行的更新成本(UPDATE)： U = search cost + rewrite data page = (h + 1) Iops 单行的插入成本(INSERT)： I = search cost + rewrite index page + rewrite data page I = h + 1 + 1 = (h + 2) Iops 单行删除成本(DELETE)： D = search cost + rewrite index page + rewrite data page D = h + 1 + 1 = (h + 2) Iops Innodb逻辑存储结构 20:53 9 • InnoDB 第二索引(secondary index) 1.第二索引（secondary index）同样为B+树， 2.叶子节点包含了索引列key value+相关行对应的主键，通过该主键来访问主表（primary index） 3.第二索引的查找成本： 总共的成本=第二索引查找成本+第一索引查找成本 主要内容 Innodb逻辑存储结构 加速查询 加速插入 案例分析 20:53 10 加速查询 20:53 11 加速查询 .随机读,顺序读 .索引范围扫描，覆盖索引扫描，全表扫描 加速查询 20:53 12 InnoDB： 主键查询： SELECT * FROM tbl WHERE primary_key=10; 1.查询首先从root-》branch-》leaf block-》table records； 2.在leaf block中存放了我们的数据行，并且按照主键pk的顺序进行排序； 3.Root和branch的通常被cache； 4.叶子节点的数据通常需要从磁盘中访问，所以整个过程需要1个IOPS； 加速查询 20:53 13 InnoDB: 第二索引 1.查询通过二级索引的root-》branch-》到叶子节点； 2.通过叶子中存放的pk，然后回主表再通过pk定 位到对应的记录； 3.二级索引中的页块可能不在内存中，需要 从磁盘扫描到内存中，主表对应的记录也可能同样如此； 4.整个过程需要2个IOPS; 加速查询 20:53 14 InnoDB: 非唯一索引 1.一个user_id在二级索引中对应满足3条记录，需要回主表3次； 2.二级索引中3条记录在同一个页块中，需要1个iops，主表查询需要3个iops； 3.整个过程需要4个iops； 加速查询 20:53 15 innodb：范围扫描： 1.二级索引中，满足条件的key在同一页块中，需要1个iops；回主表查询需要3个iops； 2.1 io