mysql分享之索引1.ppt

mysql分享之索引 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引简介 索引是对数据库表中一列或多列的值进行排序的一种结构 使用索引可快速访问数据库表中的特定信息 索引定义 普通索引唯一性索引全文索引 索引分类 Btree索引hash索引 单列索引组合索引 索引简介 加速查询避免排序保证数据唯一性保证数据物理有序实现行级锁只有一种表连接方式 NestedLoop没有多块IO 不支持并行 索引的重要性 索引的负面影响 占用空间减慢DML操作 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引结构 b tree索引所有叶子节点高度相同叶子节点有双向链表指向上 下一个叶子节点每一个entry保存key 行指针 createtablet col1intnotnull col2intnotnull primarykey 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主键设计原则 索引设计与创建 创建索引需考虑执行频率及其带来的负面影响 原则是确保收益为正在建表时 应充分考虑需要添加什么索引 尽量避免上线后添加索引4 1创建索引过程5 5创建索引过程 索引创建 创建索引过程需要锁表 要决定是否创建一个索引需要知道什么信息 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引与查询 查看索引信息 showindexfromshowcreatetable 判断查询是否用到索引 explainselect 对于update where 和delete where 怎么看执行计划 索引与查询 最左前缀原则 查询条件必须包含索引最左边的列才会用到索引 假设有索引index a b c 以下哪些语句可以用到索引 1 a andb andc 2 a andc 3 b andc 4 c 覆盖索引 包含所有满足查询需要的数据的索引叫覆盖索引覆盖索引避免二次查找 对innodb尤其有帮助 createtablet abigintprimarykey bvarchar 10 cvarchar 10 key b engine innodb 1 selecta bfromtwhereb 2 selecta b cfromtwhereb 索引与查询 用索引避免排序 假设有索引index a b c 以下可能会避免排序 1 where orderbya c b2 where orderbya b c3 where orderbya b cdesc4 wherea orderbyb5 wherea orderbyb c6 wherea orderbyb c7 wherea orderbya c8 whereb orderbya c9 wherec orderbya b cdesc 需要排序的SQL orderbydistinctgroupbyunion前提条件排序列包含在用到的索引中满足最左前缀原则 且更严格 不能跳过中间列orderby顺序必须与索引定义一致所有排序列同时为升序或者降序where条件中指定前导列等于常量是例外 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引与DML INSERT在ClusteredB Tree上插入数据在所有其他SecondaryB Tree上插入主键 DELETE在ClusteredB Tree上删除数据 在所有其他SecondaryB Tree上删除主键 UPDATE非键列在ClusteredB Tree上更新数据 UPDATE主键列在ClusteredB Tree删除原有的记录 只是标记为DELETED 并不真正删除 由后台进程执行purge 在ClusteredB Tree插入新的记录 在每一个SecondaryB Tree上删除原有的数据 在每一个SecondaryB Tree上插入原有的数据 UPDATE辅助索引的键值在ClusteredB Tree上更新数据 在每一个SecondaryB Tree上删除原有的主键 在每一个SecondaryB Tree上插入原有的主键 更新键列时 需要更新多个页 效率比较低 尽量不用对主键列进行UPDATE操作 更新很多时 尽量少建索引 Agender 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引与锁 无索引 表级锁 有索引 行级锁Innodb通过索引实现 行级锁 innodb会锁住所有满足索引列对应条件的记录 createtablet abigintprimarykey bvarchar 10 cvarchar 10 key b engine innodb Insertintotvalues 1 b c1 Insertintotvalues 2 b c2 Updatetsetb b2 whereb b andc c1 会锁定几条记录 索引实现行级锁 是否在表里还加锁 辅助索引没有transactionID 如何加锁 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引使用误区 在列端做运算如果在列上加了函数或者运算 则不能用上索引隐式转换如果隐式转换发生在列段 相当于在列上加了函数转换 不能用到索引条件使用不等于索引扫描不支持不等号 wherecolvalue这种形式的SQL不能用到索引可以转换为 colvalue Select fromtwhereleft t1 modify time 10 2012 05 30 Select fromtwheret1 modify time 2012 05 30 andt1 modify time 2012 05 31 以下哪个SQL可以用到索引 t1 aintprimarykey select fromt1wherea 1 t2 avarchar 10 primarykey select fromt2wherea 1 索引使用误区 查询空值 colisnull btree索引不索引空值考虑用默认值代替null在where中出现的字段都单独创建单列索引一般情况下 一个基于单表的查询只用一个索引设置组合索引 Agenda 索引简介 索引结构 索引与锁 索引使用误区 索引与查询 索引与DML 索引维护技巧 索引设计与创建 索引维护技巧 注意点 长字段索引 如URL innodb索引只能索引767字节