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用于用于MVCCInnodb主键主键索引结构 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主键设计原则主键设计原则SQLSQL说明说明占用空间信息占用空间信息写入所需时间写入所需时间( (秒秒) )逻辑逻辑IOIOinsert into t_innodb_ordered insert into t_innodb_ordered select * from test order by a;select * from test order by a;按主键顺序写入按主键顺序写入innodbinnodb表表31339315231339315212.5812.583515793515796 6insert into t_innodb_no_ordered insert into t_innodb_no_ordered select * from test order by b;select * from test order by b;随机写入随机写入innodbinnodb表表52009369652009369620.520.54679794679794 4索引设计与创建 创建索引需考虑执行频率及其带来的负面影响，原则是确保收益为正 在建表时，应充分考虑需要添加什么索引，尽量避免上线后添加索引 4.1 创建索引过程 5.5 创建索引过程索引创建索引创建创建索引过程需要锁表创建索引过程需要锁表!要决定是否创建一个索引需要知道什么信息？要决定是否创建一个索引需要知道什么信息？Agenda索引简介索引结构索引与锁索引使用误区索引与查询索引与DML索引维护技巧索引设计与创建索引与查询查看索引信息查看索引信息 show index from show create table判断查询是否用到索引判断查询是否用到索引 explain select .对于对于update. where .和和delete . where .怎么看执行计划？怎么看执行计划？索引与查询最左前缀原则最左前缀原则 查询条件必须包含索引最左边的列才会用到索引查询条件必须包含索引最左边的列才会用到索引假设有索引假设有索引index(a,b,c)，以下哪些语句可以用到索引？，以下哪些语句可以用到索引？1) a=? and b=? and c=?2) a=? and c=? 3) b=? and c=? 4) c=?覆盖索引覆盖索引 包含所有满足查询需要的数据的索引叫覆盖索引包含所有满足查询需要的数据的索引叫覆盖索引 覆盖索引避免二次查找，对覆盖索引避免二次查找，对innodb尤其有帮助尤其有帮助create table t(a bigint primary key,b varchar(10),c varchar(10),key(b) engine=innodb;1) select a,b from t where b=?2) select a,b,c from t where b=?索引与查询用索引避免排序用索引避免排序假设有索引假设有索引index(a,b,c)，以下可能会避免排序？，以下可能会避免排序？1) where . order by a,c,b 2) where . order by a,b,c3) where . order by a,b,c desc 4) where a=? order by b5) where a=? order by b,c 6) where a? order by b,c7) where a=? order by a,c 8) where b=? order by a,c9) where c=? order by a,b,c descu 需要排序的需要排序的SQL： order by distinct group by unionu 前提条件前提条件 排序列包含在用到的索引中排序列包含在用到的索引中 满足最左前缀原则，且更严格，不能跳过中间列满足最左前缀原则，且更严格，不能跳过中间列 order by 顺序必须与索引定义一致顺序必须与索引定义一致 所有排序列同时为升序或者降序所有排序列同时为升序或者降序 where 条件中指定前导列条件中指定前导列等于等于常量是例外常量是例外Agenda索引简介索引结构索引与锁索引使用误区索引与查询索引与DML索引维护技巧索引设计与创建索引与DMLn INSERT 在Clustered B+Tree上插入数据 在所有其他Secondary B+Tree上插入主键。n DELETE 在Clustered B+Tree上删除数据。 在所有其他Secondary B+Tree上删除主键。n UPDATE 非键列非键列 在Clustered B+Tree上更新数据。n UPDATE 主键列主键列 在Clustered B+Tree删除原有的记录(只是标记为DELETED,并不真正删除，由后台进程执行purge)。 在Clustered B+Tree插入新的记录。 在每一个Secondary B+Tree上删除原有的数据。 在每一个Secondary B+Tree上插入原有的数据。n UPDATE 辅助索引的键值辅助索引的键值 在Clustered B+Tree上更新数据。 在每一个Secondary B+Tree上删除原有的主键。 在每一个Secondary B+Tree上插入原有的主键。更新键列时，需要更新多个页，效率比较低。更新键列时，需要更新多个页，效率比较低。 尽量不用对主键列进行尽量不用对主键列进行UPDATE操作。操作。 更新很多时，尽量少建索引。更新很多时，尽量少建索引。Agender索引简介索引结构索引与锁索引使用误区索引与查询索引与DML索引维护技巧索引设计与创建索引与锁n 无索引，表级锁；有索引，行级锁无索引，表级锁；有索引，行级锁n Innodb通过索引实现通过索引实现”行级锁行级锁”n innodb会锁住所有满足索引列对应条件的记录会锁住所有满足索引列对应条件的记录create table t(a bigint primary key,b varchar(10),c varchar(10),key(b) engine=innodb;Insert into t values(1,b,c1);Insert into t values(2,b,c2);1)Update t set b=b2 where b=b and c=c1;会锁定几条记录？会锁定几条记录？2)索引实现行级锁，是否在表里还加锁？索引实现行级锁，是否在表里还加锁？辅助索引没有辅助索引没有transaction ID，如何加锁？，如何加锁？Agenda索引简介索引结构索引与锁索引使用误区索引与查询索引与DML索引维护技巧索引设计与创建索引使用误区n 在列端做运算在列端做运算 如果在列上加了函数或者运算，则不能用上索引n 隐式转换隐式转换 如果隐式转换发生在列段，相当于在列上加了函数转换，不能用到索引 n 条件使用不等于条件使用不等于 索引扫描不支持不等号，where colvalue这种形式的SQL不能用到索引 可以转换为. colvalue1)Select . from t where left(t1.modify_time,10)=2012-05-30;2)Select . from t where t1.modify_time=2012-05-30 and t1.modify_time=5.5，逐个添加或者删除 version5.5，多个操作合并为一个，如alter table t drop key a,add key b(col); n 提高索引空间利用率提高索引空间利用率 一般索引的空间利用率在60%-70% 按照索引的顺序写入数据可提升索引空间利用率 版本=5.5，条件允许的话，写入数据前删除主键以外所有索引，按主键顺序写入索引，然后再创建复制索引n 扩展索引扩展索引 如索引从一个字段扩展为两个字段 版本=5.5，先添加新索引，再删除老索引 讨论时间讨论时间谢谢 谢谢