做自媒体的素材网站,大学 生免费商业网站设计,网络营销方法进行推广,成都市住房和建设局官网二、优化
现在的理解数据库优化有四个维度#xff0c;分别是#xff1a;
硬件升级、系统配置、表结构设计、SQL语句及索引。
那优化的成本和效果分别如下#xff1a;
优化成本#xff1a;硬件升级系统配置表结构设计SQL语句及索引。
优化效果#xff1a;…二、优化
现在的理解数据库优化有四个维度分别是
硬件升级、系统配置、表结构设计、SQL语句及索引。
那优化的成本和效果分别如下
优化成本硬件升级系统配置表结构设计SQL语句及索引。
优化效果硬件升级由下图可以看出性价比排名也是硬件升级 一般我们我们在项目中做事也是选择性价比最高的项来开始做下面也从这个顺序讲
一SQL语句及索引
根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容可以整理4个优化法则
1、 减少数据访问减少磁盘访问
2、 返回更少数据减少网络传输或磁盘访问
3、 减少交互次数减少网络传输
4、 减少服务器CPU开销减少CPU及内存开销
以下是每个优化法则层级对应优化效果及成本经验参考 优化法则 性能提升效果 优化成本 减少数据访问 1~1000 低 返回更少数据 1~100 低 减少交互次数 1~20 低 减少服务器CPU开销 1~5 低
1、减少数据访问
1.1、创建并使用正确的索引
MySQL支持多种索引类型这些索引类型在不同的存储引擎中可能有所不同。以下是MySQL中常见的索引类型以及它们通常支持的存储引擎 索引类型 存储引擎 适用场景 BTree索引 InnoDBMyISAMMEMORYNDB (Cluster) BTree索引是最常见的索引类型用于等值查询、范围查询和LIKE查询。InnoDB的主键索引就是B-Tree索引的一个特例称为聚簇索引。 哈希索引 MEMORY 哈希索引基于哈希表实现主要用于等值查询并且不支持排序和范围查询。MEMORY存储引擎支持哈希索引。 全文索引 MyISAMInnoDB (从MySQL 5.6开始) 全文索引主要用于文本数据的全文搜索支持自然语言查询、布尔模式查询和查询扩展。MyISAM和InnoDB存储引擎支持全文索引。 空间索引 (R-Tree索引) MyISAM 空间索引用于空间数据类型如GEOMETRY支持对空间对象的快速检索。MyISAM存储引擎支持R-Tree索引。
在上面的几种索引中我们在实际生产中使用最多的是BTree索引其他的使用场景比较少BTree的原理如下
1.非叶子节点不存据只存储索引值这样便于存储更多的索引值
2.叶子节点包含了所有的索引值和data数据
3.叶子节点用指针连接提高区间的访问性能 B树进行范围查找时从根节点开始对节点内的索引值序列采用二分法查找查找定位两个节点的索引值然后利用叶子节点的指针进行遍历即可。
1.1.1、索引字段的选择
那我们一般在什么字段上建索引这是一个非常复杂的话题需要对业务及数据充分分析后才能得出结果。主键及外键通常都要有索引其它需要建索引的字段应满足以下条件
1、字段出现在查询条件中并且查询条件可以使用索引
2、语句执行频率高一天会有几千次以上
3、更新非常频繁的字段不适合创建索引
4、索引使用时效果一般;
5、通过字段条件可筛选的记录集一般要小于总数的10%
1.1.2、什么条件会使用索引
当字段上建有索引时通常以下情况会使用索引
INDEX_COLUMN ?
INDEX_COLUMN ?
INDEX_COLUMN ?
INDEX_COLUMN ?
INDEX_COLUMN
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||%后导模糊查询
T1. INDEX_COLUMNT2. COLUMN1两个表通过索引字段关联
1.1.3、索引失效场景
不等于操作不能使用索引
NDEX_COLUMN ?
INDEX_COLUMN not in (?,?,...,?)
经过普通运算或函数运算后的索引字段不能使用索引
function(INDEX_COLUMN) ?
INDEX_COLUMN 1 ?
INDEX_COLUMN || a ?
含前导模糊查询的Like语法不能使用索引
INDEX_COLUMN like %||?
INDEX_COLUMN like %||?||%
B-TREE索引里不保存字段为NULL值记录因此IS NULL不能使用索引
INDEX_COLUMN is null
类型转换
NUMBER_INDEX_COLUMN12345
CHAR_INDEX_COLUMN12345
1.1.4、索引使用技巧
使用联合索引的查询
MySQL可以为多个字段创建索引一个索引可以包括16个字段。对于联合索引只有查询条件中使用了这些字段中第一个字段时索引才会生效。如果存在范围查询比如between、、
SQL语句中IN包含的值不应过多不超过200个当只需要一条数据的时候使用limit 1排序字段加索引如果限制条件中其他字段没有索引尽量少用or尽量用union all代替union 使用OR关键字的查询
查询语句的查询条件中只有OR关键字且OR前后的两个条件中的列都是索引时索引才会生效否则索引不生效。
索引下推 覆盖索引不回表区分in和exists、 not in和not exists
区分in和exists主要是造成了驱动顺序的改变这是性能变化的关键如果是exists那么以外层表为 驱动表先被访问如果是IN那么先执行子查询。所以IN适合于外表大而内表小的情况 EXISTS适合 于外表小而内表大的情况。
关于not in和not exists推荐使用not exists
分段查询
数据量大可以分段进行查询循环遍历将结果合并处理进行展示
必要时可以使用force index来强制查询走某个索引使用JOIN优化LEFT JOIN A表为驱动表 INNER JOIN MySQL会自动找出那个数据少的表作用驱动表 RIGHT JOIN B 表为驱动表。 从原理图能够直观的看出如果能够减少驱动表的话减少嵌套循环中的循环次数以减少 IO总量及CPU运算的次数。要注意的是被驱动表的索引字段作为on的限制字段。
数据字段长使用前置索引
等等这样的优化技巧还有很多在具体实践中再进行总结再进行补充。不过遵循这样技巧后具体的是否索引生效还是实现需要看执行计划。
正确的索引可以让查询性能提升1001000倍以上但索引会大大增加表记录的DML(INSERT,UPDATE,DELETE)开销因此在一个表中创建什么样的索引需要平衡各种业务需求。索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少这个没有固定的比例与每个表记录的大小及索引字段大小密切相关以下是一个普通表测试数据仅供参考
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压力比较大的系统表的索引需要仔细评估必要性另外索引也会占用一定的存储空间。下面介绍下执行计划。
1.2、sql执行计划
SQL执行计划是关系型数据库最核心的技术之一它表示SQL执行时的数据访问算法。下来我们先了解一下执行计划是怎样的
在MySQL中可以使用EXPLAIN查看SQL执行计划用法EXPLAIN SELECT * FROM tablename
下面来看一个例子
下面解释下每一行的含义 idSELECT识别符。这是SELECT查询序列号。这个不重要。 select_type表示SELECT语句的类型。
例如
1、 SIMPLE表示简单查询其中不包含连接查询和子查询。
2、 PRIMARY表示主查询或者是最外面的查询语句。
3、 UNION表示连接查询的第2个或后面的查询语句。
DEPENDENT UNIONUNION中的第二个或后续的查询语句使用了外面查询结果
UNION RESULTUNION的结果
SUBQUERYSELECT子查询语句
DEPENDENT SUBQUERYSELECT子查询语句依赖外层查询的结果。
最常见的查询类型是SIMPLE表示我们的查询没有子查询也没用到UNION查询 table表示查询的表。 type表示表的连接类型。
以下的连接类型的顺序是从最佳类型到最差类型
1、 system表仅有一行这是const类型的特列平时不会出现这个也可以忽略不计。
2、 const数据表最多只有一个匹配行因为只匹配一行数据所以很快常用于
3、 eq_refmysql手册是这样说的:对于每个来自于前面的表的行组合从该表中读取一行。这可能是最好的联接类型除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用比较带索引的列。
4、 ref查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于或操作符的带索引的列。
5、 ref_or_null该联接类型如同ref但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
上面这五种情况都是很理想的索引使用情况。
6、 index_merge该联接类型表示使用了索引合并优化方法。在这种情况下key列包含了使用的索引的清单key_len包含了使用的索引的最长的关键元素。
7、 unique_subquery该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
8、 index_subquery该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9、 range只检索给定范围的行,使用一个索引来选择行。
10、 index该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
11、 ALL对于每个来自于先前的表的行组合,进行完整的表扫描。性能最差 possible_keys指出MySQL能使用哪个索引在该表中找到行。
如果该列为NULL说明没有使用索引可以对该列创建索引来提高性能。
key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 key_len显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
注意key_len是确定了MySQL将实际使用的索引长度。
ref显示使用哪个列或常数与key一起从表中选择行。rows显示MySQL认为它执行查询时必须检查的行数。 Extra该列包含MySQL解决查询的详细信息
· Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
· Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
· range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
· Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
· Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
· Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。
· Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
· Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
· Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
1.3、使用缓存
在读多写少的情况下可以使用本地缓存或者分布式缓存来解决一部分读流量在缓存层面承接不必到数据库层面。
2、返回更少的数据
2.1、数据分页处理
一般数据分页方式有
2.1.1、客户端(应用程序或浏览器)分页
将数据从应用服务器全部下载到本地应用程序或浏览器在应用程序或浏览器内部通过本地代码进行分页处理
优点编码简单减少客户端与应用服务器网络交互次数
缺点首次交互时间长占用客户端内存
适应场景客户端与应用服务器网络延时较大但要求后续操作流畅如手机GPRS超远程访问跨国等等。
2.1.2、应用服务器分页
将数据从数据库服务器全部下载到应用服务器在应用服务器内部再进行数据筛选。以下是一个应用服务器端Java程序分页的示例
List listexecuteQuery(“select * from employee order by id”);
Int count list.size();
List subList list.subList(10, 20);
优点编码简单只需要一次SQL交互总数据与分页数据差不多时性能较好。
缺点总数据量较多时性能较差。
适应场景数据库系统不支持分页处理数据量较小并且可控。
2.1.3、数据库SQL分页
采用数据库SQL分页需要两次SQL完成
一个SQL计算总数量
一个SQL返回分页后的数据
优点性能好
缺点编码复杂需要两次SQL交互。
SELECT * FROM table_name ORDER BY some_column LIMIT limit_value OFFSET offset_value;
当使用大的 OFFSET 值时MySQL 必须扫描并跳过许多行才能到达所需的行。一种优化技术是使用“上一页的最后一个 ID”而不是 OFFSET 来获取下一页的数据。
SELECT * FROM table_name WHERE id last_id_of_previous_page ORDER BY id LIMIT page_size;
2.2、只返回需要的字段
2.2.1通过去除不必要的返回字段可以提高性能
调整前select * from product where company_id?;
调整后select id,name from product where company_id?;
2.2.2 垂直分表
T_FILEID,FILE_NAME,FILE_SIZE,FILE_TYPE,FILE_CONTENT
我们可以分拆成两张一对一的关系表
T_FILEID,FILE_NAME,FILE_SIZE,FILE_TYPE
T_FILECONTENTID, FILE_CONTENT
通过这种分拆可以大大提少T_FILE表的单条记录及总大小这样在查询T_FILE时性能会更好当需要查询FILE_CONTENT字段内容时再访问T_FILECONTENT表。
2.2.3 覆盖索引
有些时候我们只是访问表中的几个字段并且字段内容较少我们可以为这几个字段单独建立一个组合索引这样就可以直接只通过访问索引就能得到数据一般索引占用的磁盘空间比表小很多所以这种方式可以大大减少磁盘IO开销。
如select id,name from company where type2;
如果这个SQL经常使用我们可以在type,id,name上创建组合索引
create index my_comb_index on company(type,id,name);
有了这个组合索引后SQL就可以直接通过my_comb_index索引返回数据不需要访问company表。
3、减少交互次数
3.1、batch DML
数据库访问框架一般都提供了批量提交的接口jdbc支持batch的提交处理方法当你一次性要往一个表中插入1000万条数据时如果采用普通的executeUpdate处理那么和服务器交互次数为1000万次按每秒钟可以向数据库服务器提交10000次估算要完成所有工作需要1000秒。如果采用批量提交模式1000条提交一次那么和服务器交互次数为1万次交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO但是会大大减少客户端与服务端的交互次数从而减少了多次发起的网络延时开销同时也会降低数据库的CPU开销。
假设要向一个普通表插入1000万数据每条记录大小为1K字节表上没有任何索引客户端与数据库服务器网络是100Mbps以下是根据现在一般计算机能力估算的各种batch大小性能对比值
以上仅是理论计算值实际情况需要根据具体环境测量。
3.2、In List
很多时候我们需要按一些ID查询数据库记录我们可以采用一个ID一个请求发给数据库这样的我们将单次查询改为in用ID INLIST的这种方式写SQL需要注意的是数据库对in的个数限制一般mysql是300,但是上了200以上可以找dba进行评估
select * from mytable where id in(:id1,id2,...,idn);
3.3、使用存储过程
大型数据库一般都支持存储过程合理的利用存储过程也可以提高系统性能。如你有一个业务需要将A表的数据做一些加工然后更新到B表中但是又不可能一条SQL完成这时你需要如下3步操作
a将A表数据全部取出到客户端
b计算出要更新的数据
c将计算结果更新到B表。
如果采用存储过程你可以将整个业务逻辑封装在存储过程里然后在客户端直接调用存储过程处理这样可以减少网络交互的成本。
当然存储过程也并不是十全十美存储过程有以下缺点
a、不可移植性每种数据库的内部编程语法都不太相同当你的系统需要兼容多种数据库时最好不要用存储过程。
b、学习成本高DBA一般都擅长写存储过程但并不是每个程序员都能写好存储过程除非你的团队有较多的开发人员熟悉写存储过程否则后期系统维护会产生问题。
c、业务逻辑多处存在采用存储过程后也就意味着你的系统有一些业务逻辑不是在应用程序里处理这种架构会增加一些系统维护和调试成本。
d、存储过程和常用应用程序语言不一样它支持的函数及语法有可能不能满足需求有些逻辑就只能通过应用程序处理。
e、如果存储过程中有复杂运算的话会增加一些数据库服务端的处理成本对于集中式数据库可能会导致系统可扩展性问题。
个人观点普通业务逻辑尽量不要使用存储过程定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。
4、减少数据库服务器CPU运算
4.1、合理使用排序
普通OLTP系统排序操作一般都是在内存里进行的对于数据库来说是一种CPU的消耗曾在PC机做过测试单核普通CPU在1秒钟可以完成100万条记录的全内存排序操作所以说由于现在CPU的性能增强对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时你需要注意是否一定要这么做了大记录集排序不仅增加了CPU开销而且可能会由于内存不足发生硬盘排序的现象当发生硬盘排序时性能会急剧下降这种需求需要与DBA沟通再决定取决于你的需求和数据所以只有你自己最清楚而不要被别人说排序很慢就吓倒。
以下列出了可能会发生排序操作的SQL语法
Order by
Group by
Distinct
Exists子查询
Not Exists子查询
In子查询
Not In子查询
Union并集Union All也是一种并集操作但是不会发生排序如果你确认两个数据集不需要执行去除重复数据操作那请使用Union All 代替Union。
Minus差集
Intersect交集
4.2、大量复杂运算在客户端处理
什么是复杂运算一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。如果有大量这类函数运算尽量放在客户端处理一般CPU每秒中也只能处理1万-10万次这样的函数运算放在数据库内不利于高并发处理。
二数据库结构优化
1、优化表结构
一般在创建库和数据表时就应该考虑每张表的数据类型大小。为每个表的数据字段选择合适的类型会减少数据表每一行的存储大小。设想下每行节约十几个字节那么100W行的数据量节约的存储量是相当可观的且存储量越小的表执行查询的速度也就越快。对于数据表的字段类型常用设计原则如下
b、尽量少使用DOUBLE和DECIMAL类型
c、时间类型上尽量使用TIMESTAMP而非DATETIME其存储空间只需要 DATETIME 类型的一半
d、单表不要有太多字段建议在50以内
e、尽量设置NOT NULL避免使用NULL字段NULL字段很难查询优化且占用额外索引空间
f、 对于只包含特定类型的字段可以使用enum、set 等符合数据类型
g、 数值型字段的比较比字符串的比较效率高得多字段类型尽量使用最小、最简单的数据类型。例如P地址可以使用int类型
h、 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT如果非负则加上UNSIGNED
j、 VARCHAR的长度只分配真正需要的空间
k、少使用text等大字段
2、设计中间表
设计中间表 一般针对于统计分析功能或者实时性不高的需求OLTP、OLAP
3、设计冗余字段
为减少关联查询创建合理的冗余字段创建冗余字段还需要注意数据一致性问题
4、主键优化
每张表建议都要有一个主键主键索引而且主键类型最好是有顺序的数值类型。
5 、表拆分
5.1 垂直拆分
垂直拆分按照字段进行拆分其实就是把组成一行的多个列分开放到不同的表中这些表具有不同的结构拆分后的表具有更少的列。例如用户表中的一些字段可能经常访问可以把这些字段放进一张表里另外一些不经常使用的信息就可以放进另外一张表里。插入的时候使用事务也可以保证两表的数据一致。缺点也很明显由于拆分出来的两张表存在一对一的关系需要使用冗余字段而且需要join操作我们在使用的时候可以分别取两次这样的来说既可以避免join操作又可以提高效率。
还有就是按业务平台的不同类型功能模块进行拆分比如分为订单库、资源库、用户库。拆分之后每个业务平台中的应用工程只访问对应的业务数据库一方面将单点数据库拆分成了多个分摊了单库的读写IO压力另一方面拆分后的数据库各自独立实现了业务隔离不再互相影响。
5.2 水平拆分
水平拆分按照行进行拆分常见的就是分库分表。以用户表为例可以取用户ID然后对ID取10的余数将用户均匀的分配进这 0-9这10个表中。查找的时候也按照这种规则又快又方便。有些表业务关联比较强那么可以使用按时间划分的。例如每天的数据量很大需要每天新建一张表。这种业务类型就是需要高速插入但是对于查询的效率不太关心。表越大插入数据所需要索引维护的时间也就越长。
水平拆分优点
a、经过拆分后不存在单库数据量大和高并发的性能瓶颈
b、提高了系统的稳定性和负载能力
c、缓解单库IO压力
水平拆分缺点
a、分库事务的一致性难以解决
b、跨库Join表性能问题逻辑复杂
c、跨库count/order by/group by以及聚合函数问题
d、切分策略如何选择策略问题很可能导致数据分布不均匀问题
e、全局主键问题
应对水平拆分问题的方案
a、跨库事务问题
解决跨库事务问题主要可以通过两种方法。第一种方法使用分布式事务简单有效但是性能代价高。第二种方式应用程序和数据库共同控制保证将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务并通过应用程序来总控各个小事务。该方案优点在于能够灵活控制缺点在于改造量较大。
b、跨库Join表的问题
对于业务平台的数据持久层来说涉及复杂的Join多表查询在所难免。解决这一问题的普遍做法是分两次查询解决。在第一次查询的结果集中找出关联数据的id根据这些id发起第二次请求得到关联数据。
c、跨节点的count,order by,group by以及聚合函数问题
与解决跨节点join问题的类似只需要分别在各个单库上得到结果后在业务应用端进行合并。和join不同的是每个结点的查询可以采用多线程方式并行执行在jdk8中可以用CompletableFuture解决因此很多时候它的合并速度要比单个大数据量的表快很多。
d、数据分片策略选择
水平拆分中一个比较重要的问题就是按照什么逻辑策略来进行数据分片即为拆分库表。一种方案是按照地域类的属性进行拆分另外一种方案则是按照订单ID/用户ID进行拆分。按照地域类的属性进行拆分会使得数据聚合度比较高做聚合查询比较简单实现也相对简单缺点是数据分布不均匀。按订单ID拆分则正相反优点是数据分布均匀不会出现一个数据库数据极大或极小的情况缺点是数据太分散不利于做聚合查询。比如按订单ID拆分后一个客户的订单可能分布在不同的数据库中查询一个客户下面的所有订单可能需要查询多个数据库。对于这种情况一种解决方案是将需要聚合查询的数据做冗余表冗余的表不做拆分同时在业务开发过程中减少聚合查询。
e、全局主键问题
原本依赖数据库生成主键比如自增的表在拆分后需要自己实现主键的生成因为一般拆分规则是建立在主键上的所以在插入新数据时需要确定主键后才能找到存储的表。在实际应用中可以参考全局发号器方案。
分库分表的组件可以用Sharding-JDBC或者MyCat。
5.3 分区
当数据库单表存的数据量过大时候可以考虑采用分区表的方案来解决。MySQL的分区表是由多个相关的物理子表组成这些表也是可以由句柄对象表示所以对于用户也可以直接访问各个分区存储引擎管理分区的各个物理表和管理普通表一样所有的底层表都必须使用相同的存储引擎分区表的索引只是在各个底层表上各自加上一个相同的索引。
MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询时优化器会根据分区定义过滤那些没有需要数据的分区这样查询就无须扫描所有分区—只需查找包含数据的分区即可。
MySQL主要支持4种模式的分区range分区、list预定义列表分区hash 分区key键值分区。
MySQL分区表的优点
a、单表可以存储更多的数据
b、分区表的数据更容易维护可以通过清除整块分区以批量删除大量数据也可以增加新的分区来支持新插入的数据
c、部分查询能够从查询条件确定只落在少数分区上查询执行速度比较快
d、分区表的数据还可以分布在不同的物理设备上从而高效地利用多个硬件设备
e、可以使用分区表来避免某些特殊瓶颈例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
f、可以备份和恢复单个分区
分区表的限制和缺点
a、一个表最多只能分1024个区
b、如果分区字段中有主键或者唯一索引的列那么所有主键列和唯一索引都必须包含
c、分区表中无法使用外键约束
下面是根据时间字段来建立分区表的一个示例
分区表适合的场景
a、最适合的场景数据的时间序列性比较强则可以按时间来分区查询时加上时间范围条件效率会非常高同时对于不需要的历史数据能很容易批量删除;
b、如果数据有明显的热点而且除了这部分数据其他数据很少被访问到那么可以将热点数据单独放在一个分区让这个分区的数据能够有机会都缓存在内存中查询时只访问一个很小的分区表能够有效使用索引和缓存
6、读写分离
大型网站会有大量的并发访问如果还是传统的数据结构或者只是单单靠一台服务器扛如此多的数据库连接操作数据库必然会崩溃数据丢失的话后果更是不堪设想。这时候我们需要考虑如何减少数据库的联接。我们发现一般情况对数据库而言都是“读多写少”也就说对数据库读取数据的压力比较大这样分析可以采用数据库集群的方案。其中一个是主库负责写入数据我们称为写库其它都是从库负责读取数据我们称为读库。这样可以缓解一台服务器的访问压力。
7、数据库集群
如果访问量非常大虽然使用读写分离能够缓解压力但是一旦写操作一台服务器都不能承受了这个时候我们就需要考虑使用多台服务器实现写操作。例如可以使用MyCat搭建MySql集群对ID求3的余数这样可以把数据分别存放到3台不同的服务器上由MyCat负责维护集群节点的使用。
三硬件优化
可以从以下几个方面考虑
1、 配置较大的内存。足够大的内存是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多可以增加系统的缓冲区容量使数据在内存停留的时间更长以减少磁盘的IO。
2、 磁盘I/O相关
使用SSD或者PCIe SSD设备至少获得数百倍甚至万倍的IOPS提升
尽可能选用RAID-10而非RAID-5
使用机械盘的话尽可能选择高转速的例如选用15000RPM而不是7200RPM的盘
四MySQL服务器参数
通过优化MySQL的参数可以提高资源利用率从而达到提高MySQL服务器性能的目的。MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中常用的参数如下
read_buffer_size顺序读的缓冲区变大提高顺序读效率read_rnd_buffer_size随机读的缓冲区变大提高随机读效率sort_buffer_size排序缓冲区变大提高排序效率join_buffer_size连接缓冲区变大提高表连接效率binlog_cache_size二进制缓冲区变大提高binlog写入效率默认32768tmp_table_size临时表缓冲区变大提高临时表存储效率innodb_log_buffer_size变大提高Redo日志写入效率key_buffer_size表索引高速缓冲变大提高 MyISAM 表索引读写效率query_cache_size查询缓存变大提高查询结果返回效率建议关闭innodb_buffer_pool_size用于缓存行数据、索引数据以及事务锁和自适应哈希等可以设置为物理内存的50%-80%bing_format:可以设置为row让数据更加安全可靠wait_timeout当MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout值为8个小时。设置这个值是非常有意义的比如你的网站有大量的MySQL链接请求每个MySQL连接都是要内存资源开销的由于你的程序的原因有大量的连接请求空闲啥事也不干白白占用内存资源或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态可用showprocesslist)如果经常发现MYSQL中有大量的Sleep进程则需要 修改wait-timeout值了。max_connections是指MySql的最大连接数如果服务器的并发连接请求量比较大建议调高此值以增加并行连接数量当然这建立在机器能支撑的情况下因为如果连接数越多介于MySql会为每个连接提供连接缓冲区就会开销越多的内存所以要适当调整该值不能盲目提高设值。MySQL服务器允许的最大连接数16384。max_user_connectionsmax_user_connections是指每个数据库用户的最大连接针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。default-storage-enginedefault-storage-engine InnoDB(设置InnoDB类型另外还可以设置MyISAM类型)设置创建数据库及表默认存储类型。innodb_flush_log_at_trx_commit设置为0或2innodb_flush_log_at_trx_commit是MySQL InnoDB存储引擎独有的参数用于控制InnoDB的Redo log日志记录方式。通过调优该参数可以提升数据库的性能和数据安全性。该参数的取值范围为0、1、2不同的值代表MySQL数据库的Redo log不同的刷盘的方式
当innodb_flush_log_at_trx_commit1时InnoDB将在每次事务提交时将log buffer的数据更新到文件系统os buffer中并调用文件系统的flush操作将数据缓存更新至磁盘中。此种方式下数据库完全遵守ACID特性安全性较高。
当innodb_flush_log_at_trx_commit2时InnoDB将在每次事务提交时将log buffer中的数据更新到文件系统缓存中每秒钟将文件系统缓存中的数据更新到磁盘一次该操作由操作系统调度。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置不能完全保证每秒更新磁盘一次没有被更新到磁盘中的事务可能会因宕机而丢失。
当innodb_flush_log_at_trx_commit0时InnoDB会每秒钟将log buffer中的数据更新到磁盘中。因为DDL变更或其他InnoDB内部原因会导致更新磁盘的操作独立于innodb_flush_log_at_trx_commit参数设置并不能完全保证每秒将数据更新到磁盘一次。因此在实例崩溃恢复场景中可能会出现丢失1秒钟的事务。
需要注意的是当innodb_flush_log_at_trx_commit设置为0或2时并不能完全保证每秒将数据更新到磁盘一次但也有可能更频繁地更新数据到磁盘。因此在实际应用中需要根据性能和数据安全性等方面的需求来设置。
sync_binlog设置为N,sync_binlog是MySQL Binlog日志的重要参数用于控制Binlog的更新策略通过对该参数的调优可以提升数据库的性能和数据安全性
当sync_binlog1时MySQL会在每次事务提交后将Log Buffer中的数据更新到磁盘上此时MySQL安全性较高但是IO消耗也较高。
当sync_binlog0时MySQL会在每次事务提交后将binlog_cache中的数据更新至文件系统缓冲区但不会进行持久化而是依赖操作系统来调度数据刷入磁盘。
当sync_binlogN时MySQL会在每N组事务提交后将数据更新到磁盘中。通过这种方式可以在一定程度上平衡MySQL的性能和数据的安全性。如果N设置得比较大可以提高系统的性能但会降低数据的安全性。
综上所述innodb_flush_log_at_trx_commit和sync_binlog参数需要根据具体的需求来设置
在对数据安全性要求较高的场景下建议将这两个参数设置为1。在对实例性能要求较高的场景下建议将这两个参数设置为0或者将innodb_flush_log_at_trx_commit设置为0sync_binlog设置为N以提高系统的性能但需要注意可能会增加数据丢失的风险。mrr设置为onmrr_cost_based: on尽可能的使用mmr来将随机磁盘读转化为顺序磁盘读从而提高了索引查询的性能启用BKABKA主要适用于join的表上有索引可利用。
启用sqlSET global optimizer_switchmrron,mrr_cost_basedoff,batched_key_accesson;
算法原理将外层循环的行/结果集存入join buffer内存循环的每一行数据与整个buffer中的记录做比较可以减少内层循环的扫描次数。
五linux参数
在 Linux 系统中关于 JO 调度问题建议使用 deadline 或者 noop模式。不要使用cfq模式因为会影响数据库性能。其中对数据库这种随机读写的场景最有利的算注是deadline 模式。在新兴的固态硬盘比如 SSD、 Fusion I/O上最简单的noop反而可能好的算法因为其他3个算法的优化是基于缩短寻道时间的而固态硬盘没有所谓的寻道且1/O响应时间非常短。设置内核参数vm.swappiness1
该参数表示使用swap的意向要不惜一切代价避免使用swap交换分区。swapim参数值可设置范围在0-100之间。低参数值会让内核尽量少用交换高参数值会使内核更地去使用交换空间。不建议设置为0因为有可能会引发out of memory但可以设置为1示尽量不使用swap。
文件系统选择推荐使用xfs
推荐在Linux 下使用xfs文件系统其次是选择ext4文件系统放弃ext3。xis是一性能的日志文件系统特别擅长处理大文件对比ext3、ext4。MySQL在xfs上一般有性能、更高的吞吐相比 ext4更能保证数据完整。
六其他
1、数据库连接池
使用数据库连接池可以将 资源重用得到更快的系统响应速度一般是先查询根据上文提到的max_connections和max_user_connections后来配置应用程序中的数据库连接池常用的数据库连接池有druid、dbcp、HikariCP等一般在druid中要配置如下几项 initialSize 3 初始化配置 minIdle 3 最小连接数 maxActive 15 最大连接数 maxWait 5000 获取连接超时时间单位ms timeBetweenEvictionRunsMillis 90000 连接有效性检测时间(单位:ms) testOnBorrow false 获取连接检测 testOnReturn false 归还连接检测 minEvictableIdleTimeMillis 1800000 最大空闲时间(单位ms) testWhileIdle true 在获取连接后确定是否要进行连接空间时间的检查
当然项目组要根据自己使用的连接池和实际情况进行压测后调整。
2、使用场景
我们要分析自己的场景是olap还是oltpolap和oltp的区别是这样的数据处理大致可以分成两大类联机事务处理OLTP on-line transaction processing 、联机分析处理OLAP On-Line Analytical Processing 。两者的之间的比较如下 OLTP系统最容易出现瓶颈的地方就是CPU与磁盘子系统。
1CPU出现瓶颈常表现在逻辑读总量与计算性函数或者是过程上
逻辑读总量等于单个语句的逻辑读乘以执行次数如果单个语句执行速度虽然很快但是执行次数非常多那么也可能会导致很大的逻辑读总量。设计的方法与优化的方法就是减少单个语句的逻辑读或者是减少它们的执行次数。
另外一些计算型的函数的频繁使用也会消耗大量的CPU时间造成系统的负载升高正确的设计方法或者是优化方法需要尽量避免计算过程如保存计算结果到统计表就是一个好的方法。
2磁盘子系统在OLTP环境中它的承载能力一般取决于它的IOPS处理能力. 因为在OLTP环境中磁盘物理读一般都是db file sequential read也就是单块读但是这个读的次数非常频繁。如果频繁到磁盘子系统都不能承载其IOPS的时候就会出现大的性能问题。
OLTP比较常用的设计与优化方式为Cache技术与B-tree索引技术Cache决定了很多语句不需要从磁盘子系统获得数据所以这样数据库层面 cache与buffer对OLTP系统是很重要的。另外在索引使用方面语句越简单越好这样执行计划也稳定而且一定要使用绑定变量减少语句解析尽量减少表关联尽量减少分布式事务。因为并发量很高批量更新时要分批快速提交以避免阻塞的发生。
OLAP这样的系统中语句的执行量不是考核标准因为一条语句的执行时间可能会非常长读取的数据也非常多。所以在这样的系统中考核的标准往往是磁盘子系统的吞吐量带宽如能达到多少MB/s的流量。这类系统强调数据分析强调SQL执行时长强调磁盘I/O强调分区等。
磁盘子系统的吞吐量则往往取决于磁盘的个数这个时候Cache基本是没有效果的数据库的读写类型基本上是Db文件分散读取与直接路径读/写。应尽量采用个数比较多的磁盘以及比较大的带宽如4Gb的光纤接口。 分区技术在OLAP系统中的重要性主要体现在数据库管理上删除数据可以通过分区进行删除至于分区在性能上的影响它可以使得一些大表的扫描变得很快只扫描单个分区。 对于OLAP系统在内存上可优化的余地很小增加CPU 处理速度和磁盘I/O 速度是最直接的提高数据库性能的方法当然这也意味着系统成本的增加。比如我们要对几亿条或者几十亿条数据进行聚合处理这种海量的数据全部放在内存中操作是很难的同时也没有必要因为这些数据快很少重用缓存起来也没有实际意义而且还会造成物理I/O相当大。所以这种系统的瓶颈往往是磁盘I/O上面的。
3、部署情况
确认自己的系统是独立部署还是混合部署来确定自己可以使用的硬件资源是否稳定。
以上就是我认识到的mysql优化的一些方面要提高数据库的运行效率从数据库系统级优化、数据库设计级优化、程序实现级优化这三个层次上同时下功夫而且以上总结的每项都有一些还顾忌不到的点希望以后在遇到再补充。
4、InnoDB参数
再详细了解mysql这个软件的配置和InnoDB的一些配置来进行优化提高性能
有参考
面向程序员的数据库访问性能优化法则_oracle 多字段in 性能-CSDN博客
