mysql索引和锁机制简介及SQL优化课件.ppt

mysql索引和锁机制简介及SQL优化;1;什么是索引;什么是索引;二叉查找树特点;Btree特点;B+tree特点;B*tree特点;为什么使用B-Tree（B+Tree）;为什么使用B-Tree（B+Tree）;B+Tree页结构;MyISAM主键索引;MyISAM非主键索引;Innodb主键索引;Innodb主键索引;Innodb非主键索引;B+Tree的插入;B+Tree的插入;B+Tree的插入;建索引策略;建索引策略;全文索引;自适应Hash索引;自适应Hash索引限制;1;表结构设计原则;表结构设计原则;分析SQL效率方法;最左前缀原理与相关优化;全列匹配;首列匹配;第二列未匹配;未匹配;Like匹配;Like未匹配;范围匹配;Between匹配;???数无法匹配;左侧表达式无法匹配;索引与排序;索引与排序;索引与排序;覆盖索引;未覆盖索引;左连接还是子查询？;使用自增字段做主键;使用自增字段做主键;Select指定列来代替select *;Select count(*);Select count( distinct)优化;在mysql端分页将明显减少用户延迟，不管是mysql内部处理还是网络传输，性能都会大大提高。 每次20-100行是比较可行的。 当offset比较大时： select SQL_NO_CACHE * from abc limit 10000,10?多次运行，时间保持在0.0187左右 ?select SQL_NO_CACHE * from abc where id=100000 limit 10;多次运行，时间保持在0.0061左右，只有前者的1/3。 也是带自增ID的表的一个优势。;Sql里面含有or不会用到索引。 例如name和age都有索引： Select * from user where name=‘hehe’or age=41; 全表扫描 改成 Select * from user where name=‘hehe’union all select * from user where age=41; 可以用到索引。 ;Select * from { select a.id,a.name,a.age,b.name from a, b where a.id=b.id order by a.age DESC } as tmp limit 0,20; 改成： Select a.id,a.name,a.age,b.name from a join b on a.id=b.id order by a.age DESC limit 0,20; 避免了子查询。 ;On duplicate key update Insert into user values(…..) on duplicate key update name =‘d’, age=41; 强烈建议使用。 消除了大量业务逻辑处理，一个事务中完成，效率大大提供。 ;尽量少 join 尽量少排序 尽量少 or 尽量用 union all 代替 union 尽量早过滤 （如ICP，条件过滤放在了数据引擎层） 不必要的表自身连接 Where替换Having，having在检索出所有记录后再进行统计，避免使用。 避免类型转换比如 select * from order where ORDER_ID=12345； 忘记加引号，而且用不上索引。 避免使用MYSQL自带函数 拿不准的时候尽量使用Explain和show profiles来判断执行情况。;1;MyISAM： 表锁 Innodb：行锁 nnoDB实现了以下两种类型的行锁。 l? 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 l? 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。 ;另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。 l? 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。 l? 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。 ; 请求锁模式 ?? 是否兼容 当前锁模式;锁类型;锁类型;锁实现;锁实现;锁实现;锁实现;锁算法;锁算法;锁算法;锁算法;死锁;死锁;死锁;Q A;Date