精华内容
下载资源
问答
  • Sql数据库类型优化思路 1 优化索引、SQL 语句、分析慢查询; 2.设计表的时候严格根据数据库的设计范式来设计数据库; 3.使用缓存,把经常访问到的数据而且不需要经常变化的数据放在缓存中,能节约磁盘 IO 4.优化硬件...

    Sql数据库类型优化思路

    1 优化索引、SQL 语句、分析慢查询;
    2.设计表的时候严格根据数据库的设计范式来设计数据库;
    3.使用缓存,把经常访问到的数据而且不需要经常变化的数据放在缓存中,能节约磁盘 IO
    4.优化硬件;采用 SSD,使用磁盘队列技术(RAID0,RAID1,RDID5)等
    5.采用 MySQL 内部自带的表分区技术,把数据分层不同的文件,能够提高磁盘的读取效率;
    6.垂直分表;把一些不经常读的数据放在一张表里,节约磁盘 I/O;
    7.主从分离读写;采用主从复制把数据库的读操作和写入操作分离开来;
    8.分库分表分机器(数据量特别大),主要的的原理就是数据路由;
    9.选择合适的表引擎,参数上的优化
    10.进行架构级别的缓存,静态化和分布式;
    11.不采用全文索引;
    12.采用更快的存储方式,例如 NoSQL 存储经常访问的数据**。

    对sql命令进行优化

    a. 应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索。
    b. 应尽量避免在 where 子句中对字段进行 null 值判断,避免使用!=或<>操作符,避免使用 or 连接条件,或在 where 子句中使用参数、对字段进行表达式或函数操作,否则会导致权标扫描
    c. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无 法正确使用索引。
    d. 使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为 条件时才能保证系统使用该索引,否则该索引将不会被使用。
    e. 很多时候可考虑用 exists 代替 in。
    f. 尽量使用数字型字段。
    g. 尽可能的使用 varchar/nvarchar 代替 char/nchar。
    h. 任何地方都不要使用 select from t ,用具体的字段列表代替“”,不要返回用不到的任何字段。
    i. 尽量使用表变量来代替临时表。
    j. 避免频繁创建和删除临时表,以减少系统表资源的消耗。
    k. 尽量避免使用游标,因为游标的效率较差。
    l. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF。
    m. 尽量避免大事务操作,提高系统并发能力。
    n. 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

    如果你觉得学不会?莫慌,小编推荐大家入群 前面895中间797最后751,群里面有志同道合的小伙伴哦!

    展开全文
  • 数据库优化方案,主要的还是要优化查询语句,主要思路就是避免全表查询!!1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 ...

    数据库优化方案,主要的还是要优化查询语句,主要思路就是避免全表查询!!

    优化法则层级对应优化效果及成本经验参考:

    优化法则

    性能提升效果

    优化成本

    减少数据访问

    1~1000

    返回更少数据

    1~100

    减少交互次数

    1~20

    减少服务器CPU开销

    1~5

    利用更多资源

    @~10

    接下我们就优化查询语句来分析如何优化:

    1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

    2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num is null

    最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

    备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。


    可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

    select id from t where num = 0

    3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

    4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

    select id from t where num=10 or Name = 'admin'

    可以这样查询:

    select id from t where num = 10 union allselect id from t where Name = 'admin'

    5.in和 not in 也要慎用,否则会导致全表扫描,如:
    select id from t where num in(1,2,3)

    对于连续的数值,能用 between就不要用 in 

    select id from t where num between 1 and 3

    很多时候用 exists 代替 in 是一个好的选择:

    select num from a where num in(select num from b)

    用下面的语句替换:

    select num from a where exists(select 1 from b where num=a.num)

    6.下面的查询也将导致全表扫描:

    select id from t where name like ‘%abc%’

    若要提高效率,可以考虑全文检索。

    7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

    select id from t where num = @num

    可以改为强制查询使用索引:

    select id from t with(index(索引名)) where num = @num

    应尽量避免在 where子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    select id from t where num/2 = 100

    应改为:

    select id from t wherenum = 100*2

    9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

    1. select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id
    2. select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30--生成的id

    应改为:

    1. select id from t where name like 'abc%'
    2. select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

    10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

    12.不要写一些没有意义的查询,如需要生成一个空表结构:

    select col1,col2 into #t from t where 1=0
    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
    create table #t(…)

    13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

    14.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

    15.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

    16.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。


    17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

    18.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

    19.尽可能的使用 varchar/nvarchar代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    20.任何地方都不要使用 select * from table ,用具体的字段列表代替“*”,如:select class from table,不要返回用不到的任何字段
    展开全文
  • 删除生产环境中2015年6月份以前的流程为避免影响考勤,暂定在本月考勤定稿后实施实施方案:数据量大的信息主要是流程,主要存放在flow_run流程主表,flow_run_data流程数据表,flow_run_prcs流程审批信息,flow_run_log...

    整体思路:

    1.备份现有系统数据,提供一个可以查询历史的入口(可以查询2015年6月份以前的所有OA产生的流程)

    2.删除生产环境中2015年6月份以前的流程

    为避免影响考勤,暂定在本月考勤定稿后实施

    实施方案:

    数据量大的信息主要是流程,主要存放在flow_run流程主表,flow_run_data流程数据表,flow_run_prcs流程审批信息,flow_run_log流程日志表中,清理

    思路:

    ①创建和flow_run_data、flow_run、flow_run_prcs、flow_run_log表结构一致的表

    ②把需要清理的历史数据插入刚创建的备份表中

    ③删除正式表中的数据

    ④将流水号小于1552669的流程,并且发起时间晚于2016-6月后发起的流程,补充回来

    操作:

    1.前端oa web暂停访问服务,备份TD_OA数据库

    # /opt/lampp/lampp stopmysql

    # cd /opt/lampp/var/mysql

    # cp -r TD_OA TD_OA20160110

    # /opt/lampp/lampp startmysql

    2.依次执行以下SQL语句:

    首先找出流水号小于1552669即将被删除的流程,但流程发起时间再2015年6月以后的流程,记录下来备用

    select * from FLOW_RUN where run_id<=1552669 and BEGIN_TIME >= '2015-06-01 00:00:00'

    //对FLOW_RUN的操作

    CREATE TABLE FLOW_RUN20160110 LIKE FLOW_RUN;

    INSERT INTO FLOW_RUN20160110 SELECT * FROM FLOW_RUN WHERE RUN_ID < '1552669';

    DELETE FROM FLOW_RUN WHERE RUN_ID < '1552669';

    INSERT INTO FLOW_RUN select * from FLOW_RUN20160110 where run_id in('584490','621983','703990','733445','734642','734644','749954','764080','780194','839734','842274','842278','892757','932299','943126','944519','960193','962908','969219','970406','970408','976232','979118','982751','992712','994829','994830','999550','1002805','1002899','1007474','1025116','1027857','1030068','1032745','1032902','1033768','1038664','1039657','1040968','1040972','1048023','1048016','1054885','1066457','1070039','1071353','1072081','1075418','1076231','1076697','1083951','1083958','1085104','1091354','1108510','1112252','1114173','1114176','1140968','1142448','1146718','1208277','1208682','1216832','1228601','1235767','1240570','1263727','1268715','1268758','1269839','1272948','1290858','1295721','1295753','1298645','1298741','1303259','1303738','1307887','1312995','1317068','1317307','1325178','1325912','1329457','1329851','1329981','1329983','1330877','1332508','1332647','1332751','1332891','1334072','1335860','1337215','1338074','1340149','1342051','1343507','1346642','1346946','1348131','1348132','1348158','1348162','1356573','1373057','1376649','1378173','1380292','1380330','1380845','1381094','1383155','1383471','1383473','1386240','1387288','1387779','1388137','1388138','1389978','1390044','1391211','1391327','1391328','1392363','1392737','1393690','1395162','1395271','1396384','1397462','1398062','1399178','1399419','1401378','1401826','1402605','1403587','1403882','1403913','1404044','1404046','1404052','1404054','1404773','1406228','1406987','1409131','1410499','1410585','1411032','1411042','1411282','1411305','1411309','1411312','1411793','1413874','1413876','1413879','1414104','1414870','1414871','1414875','1415664','1416085','1416090','1416221','1417097','1418177','1418494','1418495','1418990','1420509','1420587','1421184','1422028','1422390','1422640','1422840','1423347','1424677','1425187','1425281','1425566','1425722','1429117','1429135','1429922','1432571','1432776','1433991','1434899','1436004','1436849','1437395','1438758','1438800','1438803','1439295','1439383','1442837','1443094','1444598','1446595','1451527','1453569','1454404','1458178','1460047','1460248','1460249','1460449','1507804','1463741','1463871','1466738','1467790','1468011','1468015','1468016','1468930','1469419','1470052','1471155','1471815','1471899','1472586','1472594','1473151','1473213','1473214','1473220','1473226','1475437','1477486','1477571','1478596','1479365','1482224','1482669','1486315','1486387','1488028','1488638','1489844','1494423','1498233','1498465','1499322','1500198','1501502','1501639','1502556','1503971','1504104','1504647','1505152','1505230','1506171','1506837','1508201','1508812','1509057','1509058','1509192','1509665','1509667','1509895','1511559','1512804','1513202','1513323','1513608','1514809','1515444','1516612','1516817','1517208','1517253','1518300','1518877','1519663','1519768','1519831','1519832','1519833','1520211','1520216','1520217','1520807','1520891','1520893','1520901','1521384','1521385','1521386','1521387','1521388','1521389','1521390','1521820','1521901','1522096','1523222','1525120','1526357','1526509','1526700','1527649','1531102','1531297','1531299','1531332','1531337','1531338','1531591','1531687','1531913','1532700','1532701','1533501','1533831','1533932','1534445','1535170','1536261','1536996','1537319','1537806','1538574','1539426','1539455','1539585','1540010','1540457','1540458','1540461','1540510','1540513','1540537','1540548','1540553','1540602','1540852','1541183','1541286','1541735','1542414','1542529','1542530','1542605','1542673','1542793','1543892','1544197','1544495','1544638','1544707','1544710','1544934','1545097','1545575','1545731','1545732','1545886','1546157','1546940','1547013','1547014','1547312','1547320','1547700','1547908','1548047','1548612','1548780','1548782','1548788','1549280','1549419','1549422','1549467','1549492','1549499','1549589','1549592','1549594','1549966','1549967','1550045','1550073','1550271','1550292','1550499','1551504','1551779','1552669')

    //对FLOW_RUN_DATA的操作

    CREATE TABLE FLOW_RUN_DATA20160110 LIKE FLOW_RUN_DATA;

    INSERT FLOW_RUN_DATA20160110 SELECT * FROM FLOW_RUN_DATA WHERE RUN_ID < '1552669'

    DELETE FROM FLOW_RUN_DATA WHERE RUN_ID < '1552669';

    INSERT INTO FLOW_RUN_DATA select * from FLOW_RUN_DATA20160110 where run_id in('584490','621983','703990','733445','734642','734644','749954','764080','780194','839734','842274','842278','892757','932299','943126','944519','960193','962908','969219','970406','970408','976232','979118','982751','992712','994829','994830','999550','1002805','1002899','1007474','1025116','1027857','1030068','1032745','1032902','1033768','1038664','1039657','1040968','1040972','1048023','1048016','1054885','1066457','1070039','1071353','1072081','1075418','1076231','1076697','1083951','1083958','1085104','1091354','1108510','1112252','1114173','1114176','1140968','1142448','1146718','1208277','1208682','1216832','1228601','1235767','1240570','1263727','1268715','1268758','1269839','1272948','1290858','1295721','1295753','1298645','1298741','1303259','1303738','1307887','1312995','1317068','1317307','1325178','1325912','1329457','1329851','1329981','1329983','1330877','1332508','1332647','1332751','1332891','1334072','1335860','1337215','1338074','1340149','1342051','1343507','1346642','1346946','1348131','1348132','1348158','1348162','1356573','1373057','1376649','1378173','1380292','1380330','1380845','1381094','1383155','1383471','1383473','1386240','1387288','1387779','1388137','1388138','1389978','1390044','1391211','1391327','1391328','1392363','1392737','1393690','1395162','1395271','1396384','1397462','1398062','1399178','1399419','1401378','1401826','1402605','1403587','1403882','1403913','1404044','1404046','1404052','1404054','1404773','1406228','1406987','1409131','1410499','1410585','1411032','1411042','1411282','1411305','1411309','1411312','1411793','1413874','1413876','1413879','1414104','1414870','1414871','1414875','1415664','1416085','1416090','1416221','1417097','1418177','1418494','1418495','1418990','1420509','1420587','1421184','1422028','1422390','1422640','1422840','1423347','1424677','1425187','1425281','1425566','1425722','1429117','1429135','1429922','1432571','1432776','1433991','1434899','1436004','1436849','1437395','1438758','1438800','1438803','1439295','1439383','1442837','1443094','1444598','1446595','1451527','1453569','1454404','1458178','1460047','1460248','1460249','1460449','1507804','1463741','1463871','1466738','1467790','1468011','1468015','1468016','1468930','1469419','1470052','1471155','1471815','1471899','1472586','1472594','1473151','1473213','1473214','1473220','1473226','1475437','1477486','1477571','1478596','1479365','1482224','1482669','1486315','1486387','1488028','1488638','1489844','1494423','1498233','1498465','1499322','1500198','1501502','1501639','1502556','1503971','1504104','1504647','1505152','1505230','1506171','1506837','1508201','1508812','1509057','1509058','1509192','1509665','1509667','1509895','1511559','1512804','1513202','1513323','1513608','1514809','1515444','1516612','1516817','1517208','1517253','1518300','1518877','1519663','1519768','1519831','1519832','1519833','1520211','1520216','1520217','1520807','1520891','1520893','1520901','1521384','1521385','1521386','1521387','1521388','1521389','1521390','1521820','1521901','1522096','1523222','1525120','1526357','1526509','1526700','1527649','1531102','1531297','1531299','1531332','1531337','1531338','1531591','1531687','1531913','1532700','1532701','1533501','1533831','1533932','1534445','1535170','1536261','1536996','1537319','1537806','1538574','1539426','1539455','1539585','1540010','1540457','1540458','1540461','1540510','1540513','1540537','1540548','1540553','1540602','1540852','1541183','1541286','1541735','1542414','1542529','1542530','1542605','1542673','1542793','1543892','1544197','1544495','1544638','1544707','1544710','1544934','1545097','1545575','1545731','1545732','1545886','1546157','1546940','1547013','1547014','1547312','1547320','1547700','1547908','1548047','1548612','1548780','1548782','1548788','1549280','1549419','1549422','1549467','1549492','1549499','1549589','1549592','1549594','1549966','1549967','1550045','1550073','1550271','1550292','1550499','1551504','1551779','1552669')

    //对FLOW_RUN_PRCS操作

    CREATE TABLE FLOW_RUN_PRCS20160110 LIKE FLOW_RUN_PRCS;

    INSERT INTO FLOW_RUN_PRCS20160110 SELECT * FROM FLOW_RUN_PRCS WHERE RUN_ID < '1552669'

    DELETE FROM FLOW_RUN_PRCS WHERE RUN_ID < '1552669';

    INSERT INTO FLOW_RUN_PRCS select * from FLOW_RUN_PRCS20160110 where run_id in('584490','621983','703990','733445','734642','734644','749954','764080','780194','839734','842274','842278','892757','932299','943126','944519','960193','962908','969219','970406','970408','976232','979118','982751','992712','994829','994830','999550','1002805','1002899','1007474','1025116','1027857','1030068','1032745','1032902','1033768','1038664','1039657','1040968','1040972','1048023','1048016','1054885','1066457','1070039','1071353','1072081','1075418','1076231','1076697','1083951','1083958','1085104','1091354','1108510','1112252','1114173','1114176','1140968','1142448','1146718','1208277','1208682','1216832','1228601','1235767','1240570','1263727','1268715','1268758','1269839','1272948','1290858','1295721','1295753','1298645','1298741','1303259','1303738','1307887','1312995','1317068','1317307','1325178','1325912','1329457','1329851','1329981','1329983','1330877','1332508','1332647','1332751','1332891','1334072','1335860','1337215','1338074','1340149','1342051','1343507','1346642','1346946','1348131','1348132','1348158','1348162','1356573','1373057','1376649','1378173','1380292','1380330','1380845','1381094','1383155','1383471','1383473','1386240','1387288','1387779','1388137','1388138','1389978','1390044','1391211','1391327','1391328','1392363','1392737','1393690','1395162','1395271','1396384','1397462','1398062','1399178','1399419','1401378','1401826','1402605','1403587','1403882','1403913','1404044','1404046','1404052','1404054','1404773','1406228','1406987','1409131','1410499','1410585','1411032','1411042','1411282','1411305','1411309','1411312','1411793','1413874','1413876','1413879','1414104','1414870','1414871','1414875','1415664','1416085','1416090','1416221','1417097','1418177','1418494','1418495','1418990','1420509','1420587','1421184','1422028','1422390','1422640','1422840','1423347','1424677','1425187','1425281','1425566','1425722','1429117','1429135','1429922','1432571','1432776','1433991','1434899','1436004','1436849','1437395','1438758','1438800','1438803','1439295','1439383','1442837','1443094','1444598','1446595','1451527','1453569','1454404','1458178','1460047','1460248','1460249','1460449','1507804','1463741','1463871','1466738','1467790','1468011','1468015','1468016','1468930','1469419','1470052','1471155','1471815','1471899','1472586','1472594','1473151','1473213','1473214','1473220','1473226','1475437','1477486','1477571','1478596','1479365','1482224','1482669','1486315','1486387','1488028','1488638','1489844','1494423','1498233','1498465','1499322','1500198','1501502','1501639','1502556','1503971','1504104','1504647','1505152','1505230','1506171','1506837','1508201','1508812','1509057','1509058','1509192','1509665','1509667','1509895','1511559','1512804','1513202','1513323','1513608','1514809','1515444','1516612','1516817','1517208','1517253','1518300','1518877','1519663','1519768','1519831','1519832','1519833','1520211','1520216','1520217','1520807','1520891','1520893','1520901','1521384','1521385','1521386','1521387','1521388','1521389','1521390','1521820','1521901','1522096','1523222','1525120','1526357','1526509','1526700','1527649','1531102','1531297','1531299','1531332','1531337','1531338','1531591','1531687','1531913','1532700','1532701','1533501','1533831','1533932','1534445','1535170','1536261','1536996','1537319','1537806','1538574','1539426','1539455','1539585','1540010','1540457','1540458','1540461','1540510','1540513','1540537','1540548','1540553','1540602','1540852','1541183','1541286','1541735','1542414','1542529','1542530','1542605','1542673','1542793','1543892','1544197','1544495','1544638','1544707','1544710','1544934','1545097','1545575','1545731','1545732','1545886','1546157','1546940','1547013','1547014','1547312','1547320','1547700','1547908','1548047','1548612','1548780','1548782','1548788','1549280','1549419','1549422','1549467','1549492','1549499','1549589','1549592','1549594','1549966','1549967','1550045','1550073','1550271','1550292','1550499','1551504','1551779','1552669')

    //FLOW_RUN_LOG操作

    CREATE TABLE FLOW_RUN_LOG20160110 LIKE FLOW_RUN_LOG;

    INSERT INTO FLOW_RUN_LOG20160110 SELECT * FROM FLOW_RUN_LOG WHERE RUN_ID < '1552669'

    DELETE FROM FLOW_RUN_LOG WHERE RUN_ID < '1552669';

    INSERT INTO FLOW_RUN_LOG select * from FLOW_RUN_LOG20160110 where run_id in('584490','621983','703990','733445','734642','734644','749954','764080','780194','839734','842274','842278','892757','932299','943126','944519','960193','962908','969219','970406','970408','976232','979118','982751','992712','994829','994830','999550','1002805','1002899','1007474','1025116','1027857','1030068','1032745','1032902','1033768','1038664','1039657','1040968','1040972','1048023','1048016','1054885','1066457','1070039','1071353','1072081','1075418','1076231','1076697','1083951','1083958','1085104','1091354','1108510','1112252','1114173','1114176','1140968','1142448','1146718','1208277','1208682','1216832','1228601','1235767','1240570','1263727','1268715','1268758','1269839','1272948','1290858','1295721','1295753','1298645','1298741','1303259','1303738','1307887','1312995','1317068','1317307','1325178','1325912','1329457','1329851','1329981','1329983','1330877','1332508','1332647','1332751','1332891','1334072','1335860','1337215','1338074','1340149','1342051','1343507','1346642','1346946','1348131','1348132','1348158','1348162','1356573','1373057','1376649','1378173','1380292','1380330','1380845','1381094','1383155','1383471','1383473','1386240','1387288','1387779','1388137','1388138','1389978','1390044','1391211','1391327','1391328','1392363','1392737','1393690','1395162','1395271','1396384','1397462','1398062','1399178','1399419','1401378','1401826','1402605','1403587','1403882','1403913','1404044','1404046','1404052','1404054','1404773','1406228','1406987','1409131','1410499','1410585','1411032','1411042','1411282','1411305','1411309','1411312','1411793','1413874','1413876','1413879','1414104','1414870','1414871','1414875','1415664','1416085','1416090','1416221','1417097','1418177','1418494','1418495','1418990','1420509','1420587','1421184','1422028','1422390','1422640','1422840','1423347','1424677','1425187','1425281','1425566','1425722','1429117','1429135','1429922','1432571','1432776','1433991','1434899','1436004','1436849','1437395','1438758','1438800','1438803','1439295','1439383','1442837','1443094','1444598','1446595','1451527','1453569','1454404','1458178','1460047','1460248','1460249','1460449','1507804','1463741','1463871','1466738','1467790','1468011','1468015','1468016','1468930','1469419','1470052','1471155','1471815','1471899','1472586','1472594','1473151','1473213','1473214','1473220','1473226','1475437','1477486','1477571','1478596','1479365','1482224','1482669','1486315','1486387','1488028','1488638','1489844','1494423','1498233','1498465','1499322','1500198','1501502','1501639','1502556','1503971','1504104','1504647','1505152','1505230','1506171','1506837','1508201','1508812','1509057','1509058','1509192','1509665','1509667','1509895','1511559','1512804','1513202','1513323','1513608','1514809','1515444','1516612','1516817','1517208','1517253','1518300','1518877','1519663','1519768','1519831','1519832','1519833','1520211','1520216','1520217','1520807','1520891','1520893','1520901','1521384','1521385','1521386','1521387','1521388','1521389','1521390','1521820','1521901','1522096','1523222','1525120','1526357','1526509','1526700','1527649','1531102','1531297','1531299','1531332','1531337','1531338','1531591','1531687','1531913','1532700','1532701','1533501','1533831','1533932','1534445','1535170','1536261','1536996','1537319','1537806','1538574','1539426','1539455','1539585','1540010','1540457','1540458','1540461','1540510','1540513','1540537','1540548','1540553','1540602','1540852','1541183','1541286','1541735','1542414','1542529','1542530','1542605','1542673','1542793','1543892','1544197','1544495','1544638','1544707','1544710','1544934','1545097','1545575','1545731','1545732','1545886','1546157','1546940','1547013','1547014','1547312','1547320','1547700','1547908','1548047','1548612','1548780','1548782','1548788','1549280','1549419','1549422','1549467','1549492','1549499','1549589','1549592','1549594','1549966','1549967','1550045','1550073','1550271','1550292','1550499','1551504','1551779','1552669')

    验证数据量是否和预估的相同

    select count(run_id) from FLOW_RUN //327503

    select count(run_id) from FLOW_RUN_DATA //13419201

    select count(run_id) from FLOW_RUN_PRCS //1082718

    3、在门户首页提供可查询历史的链接,并将备份的数据库拷贝到链接后台数据库中

    4、验证通过后恢复首页访问

    展开全文
  • 二、优化方案: 代码优化:for循环次数过多、作了许多无谓的条件查询,相同逻辑重复多次 A.尽量少用或者不用sqlserver自带的函数 B.连续数值条件,用BETWEEN不用IN C.Update 语句,如果只更改1、2个字段,不要...

    一、优化方向:

    1. sql异界索引的优化
    2. 数据库设计合理化
    3. 系统配置优化:my.cnf
    4. 硬件优化

    二、优化方案:

    1. 代码优化:for循环次数过多、作了许多无谓的条件查询,相同逻辑重复多次    A.尽量少用或者不用sqlserver自带的函数
      B.连续数值条件,用BETWEEN不用IN  C.Update 语句,如果只更改1、2个字段,不要Update全部字段   D.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型   E.不建议使用 select * from t ,用具体的字段列表代替“*”  F.表与表之间通过一个冗余字段来关联,要比直接使用JOIN有更好的性能    G.select count(*) from table;这样不带任何条件的count会引起全表扫描
    2. 合理使用索引:选择合适的索引列,选择在where,group by,order by,on从句中出现的列作为索引项,对于离散度不大的列没有必要创建索引。
    3. 分表:分表方式:水平分割(按行)、垂直分割(按列)
    4. 数据库读写分离:

      当一台服务器不能满足需求时,采用读写分离【写: update/delete/add】的方式进行集群。 
      一台数据库支持最大连接数是有限的,如果用户的并发访问很多,一台服务器无法满足需求,可以集群处理。mysql集群处理技术最常用的就是读写分离。

      主从同步:数据库最终会把数据持久化到磁盘,集群必须确保每个数据库服务器的数据是一致的。从库读主库写,从库从主库上同步数据。 
      读写分离:使用负载均衡实现,写操作都往主库上写,读操作往从服务器上读。

    5. 缓存:缓存服务:Redis

    6. 异步/多线程

      针对某些客户端的请求,在服务端可能需要针对这些请求做一些附属的事情,这些事情其实用户并不关心或者用户不需要立即拿到这些事情的处理结果,这种情况就比较适合用异步的方式处理这些事情。

    7. 搜索引擎

      例如:solr,elasticsearch

       

     

    展开全文
  • 本篇文章只是给大家提供一些优化方面的方向和思路,而具体业务场景的不同,使用的MySQL服务版本不同,都会使得优化方案的制定也不同。 首先开看优化可能带来的问题 优化不总是对一个单纯的环境进行,还很可能是一个...
  • 摘要:本文主要从大型数据库ORACLE环境四个不同级别的调整分析入手,分析ORACLE的系统结构和工作机理,从九个不同方面较全面地总结了ORACLE数据库优化调整方案。从多个角度出发,对Oracle优化中的很多关键问题进行...
  • 整体思路: 1.备份现有系统数据,提供一个可以查询历史的入口(可以查询2015年6月份以前的所有OA产生的流程) 2.删除生产环境中2015年6月份以前的流程 为避免影响考勤,暂定在本月考勤定稿后实施 实施...
  • 给大家提供一些优化方面的方向和思路,而具体业务场景的不同,使用的MySQL服务版本不同,都会使得优化方案的制定也不同。 1.1 MySQL介绍 ​MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网...
  • **本篇文章只是给大家提供一些优化方面的方向和思路,而具体业务场景的不同,使用的MySQL服务版本不同,都会使得优化方案的制定也不同。 首先开看优化可能带来的问题 优化不总是对一个单纯的环境进行,还很可能是一...
  • 市面上存在两种数据库负载均衡的思路: a基于数据库连接的负载均衡: 例如总共有100个数据库连接,50个连接登录到数据库机器A,另外50个连接登录到数据库机器B,这样每个连接中接 下来的所有请求全都是发往同一...
  • Mysql 数据库优化

    2021-03-19 14:15:53
    Mysql优化思路及落地方案 优化思路 公司项目添加新功能,上线后发现有些功能的列表查询时间很久,需要仔细排查到底什么原因导致的,不一定是sql语句的问题 导致数据查询慢的原因有多种,如:缓存失效,由于高并发...
  • SqlServer千万级以上的数据表查询优化方案《冷热数据库分离》的思路 标签: 数据表   nbsp   microsoft   soft   条件   sof   数据库   最新   一段时间   原文:...
  • MySQL数据库性能(SQL)优化方案  本文探讨了提高MySQL 数据库性能的思路,并从8个方面给出了具体的解决方法。 1、选取最适用的字段属性  MySQL可以很好的支持大数据量的存取,但是一般说来,数据库中的表越小...
  • 编辑推荐:本文来自于cnblogs,本文主要介绍了postgresql连接查询方式、优化思路优化方案等相关知识。一个优化的SQL:SELECT order_date,order_source,SUM(commodity_num) num,SUM(actual_charge) chargeFROM ...
  • 这篇文章只是记录一下我们团队开发过程中遇到的数据库访问慢时的解决思路,不一定能得到大家想要的答案,但我希望可以让各位有所思考,有所讨论。 接口响应时长过长 上个月我们经理把我叫了过去,说我们的产品的...
  • 1.利用mybatis的foreach拼接动态aql或者在java中写循环拼接,将数据分组拼接成大sql,比如可以每1万行数据拼接为一个insert语句,只要连接1000次数据库即可。2.设置mybatis的sqlsession的ExecutorType为batch,如果用...
  • mysql优化索引和配置,以及慢查询分析s首先基本的思路1)性能瓶颈定位使用show命令、慢查询日志、explain分析查询、profiling分析查询、2)索引及查询优化3)配置优化MySQL数据库常见的两个瓶颈cpu、i/o:CPU主要在...
  • Oracle性能诊断艺术(Oracle数据库优化的里程碑式著作) 基本信息 原书名: Troubleshooting Oracle Performance 原出版社: Apress 作者: (瑞)Christian Antognini 译者: 童家旺 胡怡文 冯大辉 丛书名: ...
  • 分库分表能有效缓解单机和单表带来的性能瓶颈和压力,突破网络 IO、硬件资源、连接数的瓶颈,同时也带来一些问题,下面将描述这些问题和解决思路。 事务一致性问题 分布式事务 当更新内容同时存在于不同库,不可...
  • 优化巡检数据库性能

    2021-01-14 09:10:43
    优化由于HK巡检事件太多, mysql数据表无限增大, 数据库访问异常慢问题. 由于巡检事件主要目的是查看设备状态, 没必要完全保留, 所以可以只保存最新10条. 一般的解决方案, 对表进行插入删除, 速度慢, 定位慢, 耗时长...
  • 一般而言,数据库都是“读多写少”,“读写分离”的思路数据库集群中采用一个主库负责写入数据,其他都是从库负责读取数据。· 读写分离有以下三点要求 读库和写库数据必须一致 写数据必须写到写库 读数据...
  • 一、为什么难 秒杀系统难做的原因:库存只有一份,所有人会在集中的时间读和写这些数据。例如小米手机每周二的秒杀,可能手机只有1万...对于第一个问题,已经很容易想到用缓存来处理抢购,避免直接操作数据库,例如...
  • 查询结果集的时候还要查询本次有多少条数据 使用count的话就考虑sql太复杂,于是没办法使用size,效率又好低 有没遇到相似情况的帮忙提供个解决方案或者思路
  • SQL优化思路大全

    2017-07-19 11:20:00
    一、百万级数据库优化方案1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表...

空空如也

空空如也

1 2 3 4 5 ... 18
收藏数 348
精华内容 139
关键字:

数据库优化方案思路