数据库读写分析、优化.pptxVIP

数据库读写分析、优化公仔问题1：列举用过的数据库功能？数据库解决的核心问题 读写 事务目标：尽可能读快，尽可能写快量化数据库读写测量读： select * from AA where id =1; 测量写: insert into AA values(2,’强哥’);硬件速度CPU 频率2.xG，多cpu内存 频率几百M网卡 百M，千M网卡/8 字节磁盘IO SAS 盘，db主流盘,100左右随机IO，150-200顺序IO SATA盘比SAS盘更慢 SSD盘 随机读IO 万次，随机写不行 fusion-io盘 随机读写IO都到几万次磁盘性能这么差？看看磁盘结构磁盘结构数据存放在扇区，一次IO总花费时间在8ms左右 (寻道时间+旋转延时)随机,顺序IODB存储结构简介(块，区，段，表空间)DB最小操作单元是块，一个随机IO对应一个块，一次顺序IO可以读取多个块(比如16或更多)DB表存储按主键排序全表读表定义:table users(id, userid,name)select * from users;速度非常慢,怎么办主键索引读select * from user where id =1;索引的两种结构cluster_index (类比字典的拼音查找)估算读的开销比如表记录1000万，索引字段包含2个int字段，则一条索引占用8字节，一个索引块大小比如8K，则索引块存放1000条索引记录。可以算出索引高度为3. 如果只select 2个索引字段,则只需要3次IO，就可以找到结果。如果select *则先要找到索引叶子节点，然后+ 1次磁盘IO 可以找到结果。二级索引读select * from user where user_id=2;回表 先查找主键索引key、然后根据主键索引查找valueOracle的索引存放的是键值和rowidSqlserver,mysql存放的是键值和主键值索引的两种结构Non_cluster_index(类比字典笔画查找)组合索引读select * from user where user_id=2 and name=‘强哥; 公仔问题2?在user_id建索引,user_id, name建索引区别组合索引不对导致cpu高的案例Hash读时间复杂度 O(1)内存随机读\磁盘不合适支持的数据库： msql的memory引擎 默认hash索引 sqlserver ,oracle的 hash join位图读 select count(*) from AA where marry=‘未婚’ and sex=‘女’;位图读索引的开销空间开销 独立的数据结构,分裂、合并等麻烦问题时间开销 修改延时等公仔问题3 除了索引还有其他办法?数据库读过程1.建立连接2.语法分析(检查语法、权限等)3.语法解析(软、硬解析)4.执行计划5.Buffer cache是否存在值,存在则返回数据6. 磁盘IO取数据读优化1.长连接,连接池 减少rtt(减少建立连接开销)2.省去sql解析、执行计划等开销 Handsocket\ Mysql5.6 memcached 访问3.参数化sql(减少解析sql时间)4.加大buffer容量,提升命中率innodb_buffer_pool_size show engine innodb status5.优化索引(减少读，合并读,批量读)读优化6. 减少记录大小(压缩)7.提升IO(硬件提升,ssd,fusion-io)8.分库分表(拆分IO到多台机器)9.oltp\olap分离(最大化IO资源给在线业务)10. 读分离,把读压力拆分到nosql(memcache,redis)11. 读写都分离(mongodb,hbase等) 数据库写DB表存储按主键排序—mysql,sqlserver磁盘文件 – append公仔问题4 主键在name上和自增id上区别数据库写导致并发低的根本原因：事务ACID Atomicity Consistency Isolation Durability数据库写事务一致性 两阶段： 预写操作log,再写数据操作log落地机制？ 安全、效率平衡 log cache buffer\ 刷日志落地进程数据落地机制？ 安全、效率平衡 data cache buffer \ 刷数据落地进程 数据库写redo log– 重做 解决掉电等故障undo log –回滚 解决撤销等问题数据库写事务隔离性数据库写并发修改策略： 1，排他锁。2. 读写锁。3. Copy on write（MVCC）早期的sqlserver,mysql 读写锁oracle mvcc 新版mysql都已升级到mvcc数据库写读写不相互阻塞, mvcc数据库写过程1.建立