mysql分享之索引1.pptVIP

mysql分享之索引 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引简介 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息 索引定义 普通索引 唯一性索引 全文索引 索引分类 Btree索引 hash索引 单列索引 组合索引 索引简介 加速查询 避免排序 保证数据唯一性 保证数据物理有序 实现行级锁 只有一种表连接方式:Nested Loop 没有多块IO;不支持并行 索引的重要性 索引的负面影响 占用空间 减慢DML操作 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引结构 b*tree索引 所有叶子节点高度相同 叶子节点有双向链表指向上/下一个叶子节点 每一个entry保存key+行指针 create table t ( col1 int not null, col2 int not null, primary key(col1), key(col2) ) Myisam主键 索引结构 结构与主键一样 表A有100万条数据，表B有1亿条数据，结构相同。 通过相同结构索引取1条数据，性能差别多少？ Myisam辅助索引 索引结构 b*tree索引 所有叶子节点高度相同 叶子节点有双向链表指向 上/下一个叶子节点 每一个entry保存了整行数据，表即索引 TID用于事务控制(锁) RP用于MVCC Innodb主键 索引结构 b*tree索引 所有叶子节点高度相同 叶子节点有双向链表指向 上/下一个叶子节点 每一个entry保存key和主键 1. innodb辅助索引存储主键的好处和坏处是？ 2.myisam辅助索引存储主键什么问题？ Innodb辅助索引 索引结构 均为b-tree索引，自平衡，任意叶节点到根节点高度相同 myisam主键和辅助索引结构相同，索引存储行指针 innodb主键就是表，主键也节点存储所有字段值 innodb辅助索引存储key和主键值 索引高度决定IO次数 对大部分索引来说，读写都是离散IO 总 结 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引设计与创建 低选择性的列不加索引，如性别 常用的字段放在前面；选择性高的字段放在前面 需要经常排序的字段，可加到索引中，列顺序和最常用的排序一致 对较长的字符数据类型的字段建索引，优先考虑前缀索引，如index(url(64)) 只创建需要的索引，避免冗余索引，如：index(a,b)，index(a) 用于索引的列禁止使用随机数 通用索引设计原则 索引设计与创建 Innodb表每一个表都要显式设置主键 主键越短越好，最好是自增类型；如果不能使用自增，则应考虑构造使用单向递增型主键，禁止使用随机类型值用于主键。 主键最好由一个字段构成，组合主键不允许超过3个字段。如果业务需求，则可以创建一个自增字段作为主键，再添加一个唯一索引。 选择作为主键的列必须在插入后不再修改或者极少修改，否则需考虑使用自增列作为主键 如果一个业务上存在多个(组)唯一键，以查询最常用的唯一键作为主键。 Innodb主键设计原则 SQL 说明 占用空间信息 写入所需时间(秒) 逻辑IO insert into t_innodb_ordered select * from test order by a; 按主键顺序写入innodb表 313393152 12.58 3515796 insert into t_innodb_no_ordered select * from test order by b; 随机写入innodb表 520093696 20.5 4679794 索引设计与创建 创建索引需考虑执行频率及其带来的负面影响，原则是确保收益为正 在建表时，应充分考虑需要添加什么索引，尽量避免上线后添加索引 4.1 创建索引过程 5.5 创建索引过程 索引创建 创建索引过程需要锁表!!!! 要决定是否创建一个索引需要知道什么信息？ Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引与查询 查看索引信息 show index from show create