最新MYSQL优化(innodb干货)讲解教学讲义PPT.ppt

《最新MYSQL优化(innodb干货)讲解教学讲义PPT.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、MYSQL优化(innodb干货)讲解数据库与数据库实例数据库(databases)：安装关系型数据库时在操作系统中生成的物理文件。frm、myd、myi、ibd等格式的文件数据库实例(instance)：数据库后台的进程、线程、共享内存。instance是真正用来操作数据库文件的存储引擎MyISAM和InnoDBInnoDB二次写插入缓冲是为了提高写性能两次写是为了提高可靠性InnoDB自适应哈希索引时间复杂度为O(1)，比BTREE效率还要快。Innodb会自动的根据访问的频率和模式为某些页建立哈希索引。使用哈希索引，读取和写入的速度会提高两倍，对于辅助索引的join操作性能会提

2、高5倍。自适应哈希索引只能用于等值查询：select*fromtablewherename='张三';select*fromtablewhereage>20andage<30;SHOWENGINEINNODBSTATUS;InnoDB文件mysql在启动时会读取一个配置文件，定位数据库的各种文件位置以及制定某种初始化参数。数据库文件PID文件二进制文件InnoDB二进制文件12恢复recovery复制repication可以使用二进制文件进行point-in-time的恢复主从复制SHOWvariableLIKE'datadir';InnoDB复制InnoDB表空间tablespac

3、e:段(segment)、区(extent)、页(page)表空间中存放的数据包括数据、索引、插入缓存，以及一些其他数据。(二次写缓存还是在共享的内存池中)InnoDB外键12CASCADESETNULL当父表进行update或delete操作时，子表数据同时进行update或delete操作当父表进行update或delete操作时，子表数据更新为NULL34NOACTION当父表进行update或delete操作时，子表数据不进行操作RESTRICT当父表进行update或delete操作时，抛出错误，默认外键设置InnoDB会对外键自动添加索引索引索引分类索引哈希索引BTree

4、索引聚集索引辅助聚集索引联合索引单一索引索引聚集索引聚集索引是按照每张表的主键构造一棵BTree树，叶节点存放整张表行记录数据，聚集索引的叶节点成为数据页。1叶节点存放整行数据，查询速度快2针对主键排序和范围查询速度快3一张表只能有一个聚集索引索引辅助聚集索引辅助聚集索引，及非聚集索引。1一张表可以由多个聚集索引2叶节点不包含全部数据3有机会使用插入缓存索引索引操作对于索引的添加和删除操作，新创建一张临时表，然后把数据导入临时表，删除原表，最好把临时表名字改为原表名。针对非聚集索引，InnoDB实现了一种快速索引创建方法，避免数据复制和临时表的创建。Btree索引正确使用：高选择性、

5、<20%索引联合索引左侧原则索引索引的缺点一、索引要占用磁盘空间。通常情况下，这个问题不是非常突出。不过，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个非常大的表，索引文件的大小可能达到操作系统允许的最大文件限制。第二，对于需要写入数据的操作，比如delete、update及insert操作，索引会降低他们的速度。这是因为mysql不仅要把改动数据写入数据文件，而且他还要把这些改动写入索引文件。索引锁锁类型由读表操作加上的锁，加锁后其他用户只能获取该表或行的共享锁，不能获取排它锁，也就是说只能读不能写入。由写表操作加上的锁，加锁后其他用户不能

6、获取该表或行的任何锁，允许事务删除或者更新一条数据1共享锁SLock2排他锁XLock锁死锁分析SELECT*FROMINFORMATION_SCHEMA.INNODB_TRX;//查看正在锁的事务明细SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCKS;//查看正在锁的事务SELECT*FROMINFORMATION_SCHEMA.INNODB_LOCK_WAITS;//查看等待锁的事务showstatuslike'innodb_row_lock%';//查看行锁争夺情况事务ACID特性1原子性整个事务是不可分离的工作单位2一致性事务将数据从一种状态

7、转变为下一种一致状态，事务开始和结束数据完整性没有被破坏3隔离性一个事务的影响在该事务提交前对其他事务不可见，通过锁来实现4持久性事务一旦提交，结果是永久性的事务隔离级别当两个事务同时进行时，即使事务没有提交，所做的修改也会对事务内的查询做出影响，这种级别显然很不安全。修改时，会对该行加上行共享锁。只有在事务提交后，才会对另一个事务产生影响，修改时，会对该行加上行共享锁。1READUNCOMMITTED2READCOMMITTED3REPEATABLERE