数据库调研资料.docx

数据库调研mysql数据库优化 1.1 配置文件 1.1.1缓存索引和缓冲池innodb_buffer_pool_size=2Ginnodb_buffer_pool_instances=1pool-size可以缓存索引和行数据，对Innodb表来说非常重要，值越大，IO读写就越少，如果单纯的做数据库服务，该参数可以设置到电脑物理内存的80%pool_instances可以开启多个内存缓冲池，参数非常影响测试结果，特别是非常高的IO负载时，innodb_buffer_pool_instances=8，当pool_size很小时，效率差别很大，而当pool_size较大时，innodb_buffer_pool_instances=1表现最出色。 1.1.2闲置连接时间wait_timeout参数值。interactive_timeout 参数值。当某个mysql的连接闲置时间超过一定时间将会被强行关闭，默认是8小时，由于连接请求空闲时却白白占用内存资源，建议将闲置连接时间尽量减少。Mysql根据客户端类型的不同，来根据wait_timeout或interactive_timeout的值来强行关闭，所以两个参数需配合使用，单独设置无效。 1.1.3最大连接数、堆栈大小max_connections参数值(默认151)back_log参数值(默认50)堆栈大小即连接数超过max_connections值时，新的请求将被放在堆栈中，等待某一连接释放资源，当等待连接数超过back_log时，将不被授予连接资源。如果项目支持外部菜单，建议适当调整最大连接数和堆栈大小。 1.1.4缓存的最大线程数thread_cache_size参数值在高并发情况下，如果该值设置得太小，就会有很多线程频繁创建和销毁，线程创建的开销会变大，查询效率也会下降。1.2索引 1.2.1复合索引直接拿官方文档解释: 假设索引建立在 (last_name, first_name) 上， 那么SELECT * FROM test WHERE last_name=Widenius;SELECT * FROM testWHERE last_name=WideniusAND first_name=Michael;SELECT * FROM testWHERE last_name=WideniusAND (first_name=MichaelOR first_name=Monty);SELECT * FROM testWHERE last_name=WideniusAND first_name =MAND first_name N;以上几种情况都是可以用这个多列索引来加速查询。以下是查询规则：总结：联系实际情况，对于大数据量的历史数据表来说，查询条件应为where meterId and planTime；建立复合索引meterId、planTime对于and条件的查询有很大提升。 1.2.2聚集索引聚集索引不是一种单独的索引类型，而是一种存储数据方式。当前，SolidDB和InnoDB是唯一支持聚集索引的存储引擎。InnoDB按照主键进行聚集，如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集。对于我们的历史数据表来说，没有任何表的外键去引用历史数据表的id作为约束，所以历史表中的id字段我认为是无意义的，主键的聚集索引应该用在planTime字段上，因此planTime字段应有唯一约束。结合公司的历史数据表，对时间字段的唯一约束做了调查，见下图：mysql5.6.4以后的版本，支持定义time(3)或者timestamp(6)这样的字段，然后使用current_timestamp(6)即可为该字段赋值带有毫秒或微秒值的时间数据。上图的时间数据精确到微秒，微秒级别的唯一性足以应付业务上的需要。在测试中，该字段设置为主键，所以不给该字段赋值时，会自动获取毫秒级别的系统当前时间。个人认为采集系统不用赋值也不会出现时间重复问题。1.3 触发器 1.3.1触发添加插入实时数据时，通过触发器自动复制改条数据到历史数据表。测试如下：1.创建触发器:增加person表记录后自动将记录增加到person1表中2.添加一条数据到person表中，结果如下： 1.3.2触发删除删除角色时，通过触发器自动删除用户与角色的关系。测试如下：创建触发器:删除person表记录后自动将person1表中对应的记录删去删除person表中age=888的数据，结果如下：总结：事件的触发可以是insert、update或delete，触发器的执行可以在事件发生前，也可以在时间发生后。通过触发器的执行，可以减少对数据库的操