精华内容
下载资源
问答
  • postgresql中 建表语句、索引和插入数据如下: CREATE TABLE test( ID SERIAL PRIMARY KEY NOT NULL, ...需要更新数据,时间很长,请问有什么方法优化。 update test set lastconsumeshop='优化语句' ;
  • W_Report 有1千万左右的数据, 如果用ado.net 批量查出来一批,for循环内存中逐条加密后再update回去的方式. 初步测试了一下每秒 21 条. 这种肯定 是最原始的方案,1千万条估算了下要80小时, 全部处理完要3天. ...

    近期由于安全等保测评要求必须提高数据库信息的安全等级, 需要对已有数据进行加密处理… 表W_Report 有1千万左右的数据, 如果用ado.net 批量查出来一批,for循环内存中逐条加密后再update回去的方式. 初步测试了一下每秒 21 条.

    这种肯定 是最原始的方案,1千万条估算了下要80小时, 全部处理完要3天. 升级过程中得停止系统. 业务停3天是不可能的. 再加上其它的服务器硬件给力, 算每秒100条, 最起码也要停16个小时. 这肯定不行.

    毛主席教导我们: 只要脑袋不熄火,办法总比困难多.

    下面采用了新的方案批量处理,

    新方案的思路是
    第一步:将数据按月份分批批量加载到系统内存中,一次性加载1个月的. 大概有20万条数据. 使用Ado.net 读取到 DataReader,
    第二步:逐行处理,在内存中计算加密后的数据,然后保存到DataTable
    第三步:使用SqlBulkCopy 批量将加密后的DataTable 插入到一张临时表
    第四步:使用Update语句一次性将临时表的数据更新到老表
    第五步:清空临时表中的数据, 准备处理下一个月的数据.

    此方案每一步的隐患都很多
    第一步:不知道加载时间多少.
    第二步:少不了的计算时间
    第三步:SqlBulkCopy 批量插入10万条数据据说只要1-2秒.
    第四步:Update语句用一个表去更新另外一个表的性能不知如何.
    第五步:清空临时表,可以使用直接删表的方式, 速度会很快.秒秒钟.

    为了不做无用功, 先测验第四步的性能如何:
    1.先取一个月的数据插入到临时表当作测试用. 共取了 157375条数据 用时不到1秒
    这里只取了3个需要加密的字段和主键.

    select  Id, PatientName,PatientIDCard,PatientPhone  into TempReportNewData from W_Report WHERE ReportTime  BETWEEN '2019-11-01' and '2019-11-30' 
    

    第一次测试. 无主键 再更新回去到老表 用时 157375条数据 272.486s 平均每秒578条数据

    update W_Report 
    set 
    PatientName = tmp.PatientName,
    PatientIDCard = tmp.PatientIDCard,
    PatientPhone = tmp.PatientPhone
    from TempReportNewData as tmp
    where W_Report.Id = tmp.Id
    
    

    第二次测试. 加主键后 再更新回去 用时 157375条数据 279.281s 平均每秒576条数据
    比原来的还慢了点, 影响不大.先不管了, 当作没变化.

    第三次测试,. 在主表的更新条件中加时间索引字段作为条件.157375条数据 用时 4.055s秒
    我当时就傻眼了. 这么快的速度.

    update W_Report 
    set 
    PatientName = tmp.PatientName,
    PatientIDCard = tmp.PatientIDCard,
    PatientPhone = tmp.PatientPhone
    from TempReportNewData as tmp
    where W_Report.Id = tmp.Id 
    and W_Report.ReportTime  BETWEEN '2019-11-01' and '2019-11-30'
    

    至此,第四步的效率从一定的程度上来看,还是可以接受的,
    时间范围半个月应该更快, 因为有时间索引做过滤条件可以大幅提高更新速度. 毕竟时间索引可以跳过好多数据的排查过程. 灵活运用索引很有必要.
    在这次的测试过后,又冒出来一个新的想法, 可以先把前面的3步提前做了.先逐条计算好放到临时表中. 然后等升级切换的时候再批量分批一个月一个月的更新到W_report
    那就是分分钟的事情了. 毕竟这些3个月之前的老数据这些字段的数据是不会变的. 可以提前做.

    明天再继续测试 取数据的效率和加密的效率.

    反正步骤1,2,3可以提前做, 慢点也无所谓, 压力不大了.

    又过了一天.经过同事测试,发现我昨天的写的代码效率变低了. 1000多条就要50多秒, 这是为何?
    后来同事排查了一下发现

    UPDATE W_Report
    SET PatientName = tmp.PatientName,
     PatientIDCard = tmp.PatientIDCard,
     PatientPhone = tmp.PatientPhone
    FROM
     TempReportNewData AS tmp
    WHERE
     W_Report.Id = tmp.Id
    AND W_Report.ReportTime BETWEEN '2019-09-27 00:00:00' AND '2019-09-28 00:00:00'
    AND tmp.ReportTime BETWEEN '2019-09-27 00:00:00' AND '2019-09-28 00:00:00'
    

    他的语句中加了时分秒, 然后就慢了好几个数量级.

    经过测试前面3步 按天从数据库读出来,再计算,然后再批量使用SqlBulkCopy 插入,经过测试34万条数据用了30秒. 速度也是非常快的.

    展开全文
  • A关联B,条件查询都限定在B上,并且在B先做一个查询,...为了优化查询,在A添加字段,冗余该字段,并在字段上做索引,这样查询效率高点; select * from A join ( select * from B ) t on A.x = t.x

    A表关联B表,条件查询都限定在B表上,并且在B表先做一个查询,然后再关联的,A表无该字段,因此不对A表做限定;
    但是
    为了优化查询,在A表添加字段,冗余该字段,并在字段上做索引,这样查询效率高点;

    select *
    from A
    join (
    select * from B
    ) t
    on A.x = t.x

    展开全文
  • MySQL的实战系列:大字段如何优化

    千次阅读 2018-08-20 20:28:13
    MySQL的实战系列:大字段如何优化 背景 – 线上发现一张,1亿的数据量,物理大小尽然惊人的,1.2T 最终发现,原来有很多字段,10个VARCHAR,1个文本 这么,会给运维带来很的痛苦:DDL咋办...

    MySQL的实战系列:大字段如何优化

    背景

    线上发现一张表,1亿的数据量,物理大小尽然惊人的大,1.2T
    最终发现,原来有很多字段,10个VARCHAR,1个文本
    这么大的表,会给运维带来很大的痛苦:DDL咋办恢复咋办备份咋办???

    基本知识:InnoDB磁盘格式的InnoDB存储架构

    蓝图:数据库 - >表空间 - >页面 - >行 - >列

    InnoDB物理结构存储结构

    database_file

    InnoDB逻辑存储结构

    表空间

    InnoDB页面存储结构

    页类型

    数据页(B-tree Node)
    撤消页面(撤消日志页面)
    系统页面(系统页面)
    事务数据页(事务系统页面)
    插入缓冲位图页(插入缓冲页面)
    未压缩的二进制大对象页面(未压缩的BLOB页面) )
    压缩的二进制大对象页(压缩BLOB页面)

    页大小

    默认16K(若果没有特殊情况,下面介绍的都是默认16K大小为准)
    一个页内必须存储2行记录,否则就不是B +树,而是链表了

    结构图

    innodb_page

    InnoDB row存储结构

    行文件格式总体规划图

    row_file_format

    行fomat为紧凑型的结构图

    紧凑

    行fomat为冗余的结构图

    不常用

    compress&dynamic与Compact的区别之处

    动态

    字段之字符串类型

    char(N)vs varchar(N)

    不管是char,还是varchar,在compact row-format格式下,NULL都不占用任何存储空间
    在多字节字符集的情况下,CHAR vs VARCHAR的实际行存储基本没区别
    CHAR不管是否是多字符集,对未能占满长度的字符还是会填充为0x20
    规范中:对字符和VARCHAR可以不做要求

    varchar(N):255 vs 256

    当实际长度大于255的时候,变长字段长度列表需要用两个字节存储,也就意味着每一行数据都会增加1字节个
    实测下来存储空间增长并不算大,且性能影响也不大,所以,尽量在256之内吧

    varchar(N)&char(N)的最大限制

    char的最大限制是:N <= 255
    varchar的最大限制是:N <= 65535,注意官方文档说的是N是字节,并且说的是一行的所有字段的总和小于65535,而varchar(N)中的ñ表示的是字符。
    测试后发现,65535并不是最大限制,最大的限制是65532

    [MySQL 5.6.27]
    
    * char的最大限制是: N<=255
    root:test> create table test( a char(65535))charset=latin1 engine=innodb;
    ERROR 1074 (42000): Column length too big for column 'a' (max = 255); use BLOB or TEXT instead
    
    * 测试后发现,65535并不是最大限制,最大的限制是65532
    root:test> create table test( a varchar(65535))charset=latin1 engine=innodb;
    ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
    
    root:test> create table test( a varchar(65532))charset=latin1 engine=innodb;
    Query OK, 0 rows affected (0.00 sec)
    
    * varchar 的最大限制是: N<=65535 , 注意官方文档说的是N是字节,并且说的是一行的所有字段的总和小于65535,而varchar(N)中的N表示的是字符
    
    root:test> create table test_1( a varchar(30000),b varchar(30000),c varchar(5535))charset=latin1 engine=innodb;
    ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
    
    * varchar(N)中的N表示的是字符
    
    root:test> create table test_1( a varchar(50000))charset=utf8 engine=innodb;
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    root:test> show warnings;
    +-------+------+--------------------------------------------+
    | Level | Code | Message                                    |
    +-------+------+--------------------------------------------+
    | Note  | 1246 | Converting column 'a' from VARCHAR to TEXT |
    +-------+------+--------------------------------------------+
    1 row in set (0.00 sec)
    
    root:test> show create table test_1;
    +--------+-------------------------------------------------------------------------------+
    | Table  | Create Table                                                                  |
    +--------+-------------------------------------------------------------------------------+
    | test_1 | CREATE TABLE `test_1` (
      `a` mediumtext
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +--------+-------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    off-page:行溢出

    • 为什么会有行溢出关闭页的这个概念呢

    假设创建了一张表,里面有一个字段是一个varchar(30000),innoDB的页才16384个字节,如何存储的下呢?所以行溢出就来了嘛

    • 如何看出行溢出了?

    可以通过姜承尧写的工具查看
    其中溢出的页有未压缩的BLOB页面:243453

    [root()@xx script]# python py_innodb_page_info.py t.ibd
    
    Total number of page: 537344:
    Insert Buffer Bitmap: 33
    Freshly Allocated Page: 74040
    File Segment inode: 1
    B-tree Node: 219784
    File Space Header: 1
    扩展描述页: 32
    Uncompressed BLOB Page: 243453
    
    • 溢出有什么危害

    溢出的数据不再存储在B + tree中
    溢出的数据使用的是解压缩BLOB页面,并且存储独享,这就是存储越来越大的真正原因
    通过下面的测试,你会发现,t_long插入的数据仅仅比t_short多了几个字节,但是最终的存储却是2~3倍的差距

    * 表结构
    
    root:test> show create table t_long;
    +--------+---------------------------------------------------------------------------------------------------------+
    | Table  | Create Table                                                                                            |
    +--------+---------------------------------------------------------------------------------------------------------+
    | t_long | CREATE TABLE `t_long` (
      `id` int(11) DEFAULT NULL,
      `col1` text
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +--------+---------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    root:test> show create table t_short;
    +---------+----------------------------------------------------------------------------------------------------------+
    | Table   | Create Table                                                                                             |
    +---------+----------------------------------------------------------------------------------------------------------+
    | t_short | CREATE TABLE `t_short` (
      `id` int(11) DEFAULT NULL,
      `col1` text
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +---------+----------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    
    * 测试案例
    
    foreach $num (1 .. 48849){
    
            $sql_1 = "insert into $table_short select $num,repeat('a',8090)";
            $sql_2 = "insert into $table_long select $num,repeat('a',8098)";
            `$cmd -e " $sql_1 "`;
            `$cmd -e " $sql_2 "`;
    }
    
    
    * 最终的记录数
    
    root:test> select count(*) from t_short;
    +----------+
    | count(*) |
    +----------+
    |    48849 |
    +----------+
    1 row in set (0.03 sec)
    
    root:test> select count(*) from t_long;
    +----------+
    | count(*) |
    +----------+
    |    48849 |
    +----------+
    1 row in set (0.02 sec)
    
    
    * 页类型的比较
    
    [root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_short.ibd
    Total number of page: 25344:
    Insert Buffer Bitmap: 2
    Freshly Allocated Page: 887
    File Segment inode: 1
    B-tree Node: 24452
    File Space Header: 1
    扩展描述页: 1
    
    
    
    [root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_long.ibd
    Total number of page: 60160:
    Insert Buffer Bitmap: 4
    Freshly Allocated Page: 8582
    File Segment inode: 1
    B-tree Node: 2720
    File Space Header: 1
    扩展描述页: 3
    Uncompressed BLOB Page: 48849
    
    
    * 最终大小的对比
    
    [root()@xx test]# du -sh * | grep 'long|short' | grep ibd
    941M    t_long.ibd
    397M    t_short.ibd
    
    * 结论
    
    t_short 的表,在400M左右可以理解,因为 8k * 48849 = 400M
    
    t_long 的表,由于独享48849个Uncompressed BLOB Page,严重浪费空间
    
    • 什么情况下会溢出

    原则:只要一行记录的总和超过8k,就会溢出。
    所以:varchar(9000)或者varchar(3000)+ varchar(3000)+ varchar(3000),当实际长度大于8k的时候,就会溢出
    所以:Blob ,文字,一行数据如果实际长度大于8K会溢出,如果实际长度小于8K则不会溢出,并非所有的斑点,文本都会溢出

    • 多列总和大字段vs一列大字段

    多个大字段会导致多次关闭页

    root:test> show create table t_3_col;
    +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------+
    | Table   | Create Table
                                           |
    +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------+
    | t_3_col | CREATE TABLE `t_3_col` (
      `id` int(11) DEFAULT NULL,
      `col1` varchar(7000) DEFAULT NULL,
      `col2` varchar(7000) DEFAULT NULL,
      `col3` varchar(7000) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ---------------------------------------+
    1 row in set (0.00 sec)
    
    root:test> show create table t_1_col;
    +---------+---------------------------------------------------------------------------------------------------------------------------------+
    | Table   | Create Table                                                                                                                    |
    +---------+---------------------------------------------------------------------------------------------------------------------------------+
    | t_1_col | CREATE TABLE `t_1_col` (
      `id` int(11) DEFAULT NULL,
      `col1` varchar(21000) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +---------+---------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    root:test>
    root:test>
    root:test> insert into t_1_col(col1) select repeat('a',21000);
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    root:test>
    root:test>
    root:test> insert into t_3_col(col1,col2,col3) select repeat('a',7000),repeat('a',7000),repeat('a',7000);
    Query OK, 1 row affected (0.00 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    
    [root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_1_col.ibd
    Total number of page: 6:
    Insert Buffer Bitmap: 1
    Uncompressed BLOB Page: 2
    File Space Header: 1
    B-tree Node: 1
    File Segment inode: 1
    
    [root()@xx script]# python py_innodb_page_info.py /data/mysql_data/test/t_3_col.ibd
    Total number of page: 7:
    Insert Buffer Bitmap: 1
    Uncompressed BLOB Page: 3
    File Space Header: 1
    B-tree Node: 1
    File Segment inode: 1
    

    如何对大字段进行优化

    如果有多个大字段,尽量序列化后,存储在同一列中,避免多次off-page
    将文等大字段从主表中拆分出来,a)存储到键值中b)存储在单独的一张子表中,压缩并且
    必须保证一行记录小于8K

    展开全文
  • Mysql 多联合查询效率分析及优化

    万次阅读 多人点赞 2010-07-13 15:23:00
    1. 多连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用',' 如: SELECT * FROM table1 CROSS JOIN table2 SELECT * FROM table1 JOIN table2 SELECT * FROM table1,...

    1. 多表连接类型


    1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如: 

    SELECT * FROM table1 CROSS JOIN table2 
    SELECT * FROM table1 JOIN table2 
    SELECT * FROM table1,table2

            由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢。一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

     2.   内连接INNER JOIN 在MySQL中把INNER JOIN叫做等值连接,即需要指定等值连接条件在MySQL中CROSS和INNER JOIN被划分在一起。 join_table: table_reference [INNER | CROSS] JOIN table_factor [join_condition]

    内连接(INNER JOIN):有两种,显式的和隐式的,返回连接表中符合连接条件和查询条件的数据行。(所谓的链接表就是数据库在做查询形成的中间表)。
    例如:下面的语句1和语句2的结果是相同的。

    语句1:隐式的内连接,没有INNER JOIN,形成的中间表为两个表的笛卡尔积。
    SELECT A.*,  B.*     FROM A,B    WHERE B.ID=B.AID;

    语句2:显示的内连接,一般称为内连接,有INNER JOIN,形成的中间表为两个表经过ON条件过滤后的笛卡尔积。

    SELECT A.*,  B.*     FROM A  INNER(可省略)  JOIN   B  ON    B.ID=B.AID

     

    3. MySQL中的外连接,分为左外连接和右连接,即除了返回符合连接条件的结果之外,还要返回左表(左连接)或者右表(右连接)中不符合连接条件的结果,相对应的使用NULL对应。

    例子:

    user表:

    id | name
    ———
    1 | libk
    2 | zyfon
    3 | daodao

    user_action表:

    user_id | action
    —————
    1 | jump
    1 | kick
    1 | jump
    2 | run
    4 | swim

    sql:

    select id, name, action from user as u
    left join user_action a on u.id = a.user_id

    result:
    id | name    | action
    ——————————–
    1  | libk         | jump           ①
    1  | libk         | kick             ②
    1  | libk         | jump           ③
    2  | zyfon      | run               ④
    3  | daodao | null              ⑤

    分析:
    注意到user_action中还有一个user_id=4, action=swim的纪录,但是没有在结果中出现,
    而user表中的id=3, name=daodao的用户在user_action中没有相应的纪录,但是却出现在了结果集中
    因为现在是left join,所有的工作以left为准.
    结果1,2,3,4都是既在左表又在右表的纪录,5是只在左表,不在右表的纪录

     

    工作原理:

    从左表读出一条,选出所有与on匹配的右表纪录(n条)进行连接,形成n条纪录(包括重复的行,如:结果1和结果3),如果右边没有与on条件匹配的表,那连接的字段都是null.然后继续读下一条。

    引申:
    我们可以用右表没有on匹配则显示null的规律, 来找出所有在左表,不在右表的纪录, 注意用来判断的那列必须声明为not null的。
    如:
    sql:

    select id, name, action from user as u
    left join user_action a on u.id = a.user_id
    where a.user_id is NULL
    

    (注意:

            1.列值为null应该用is null 而不能用=NULL
             2.这里a.user_id 列必须声明为 NOT NULL 的.


    上面sql的result:
    id | name | action
    ————————–
    3 | daodao | NULL

    ——————————————————————————–

    一般用法:

    a. LEFT [OUTER] JOIN:

    除了返回符合连接条件的结果之外,还需要显示左表中不符合连接条件的数据列,相对应使用NULL对应

     SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
    

    b. RIGHT [OUTER] JOIN:

    RIGHT与LEFT JOIN相似不同的仅仅是除了显示符合连接条件的结果之外,还需要显示右表中不符合连接条件的数据列,相应使用NULL对应

     SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column

    Tips:

    1. on a.c1 = b.c1 等同于 using(c1)
    2. INNER JOIN 和 , (逗号) 在语义上是等同的
    3. 当 MySQL 在从一个表中检索信息时,你可以提示它选择了哪一个索引。
    如果 EXPLAIN 显示 MySQL 使用了可能的索引列表中错误的索引,这个特性将是很有用的。
    通过指定 USE INDEX (key_list),你可以告诉 MySQL 使用可能的索引中最合适的一个索引在表中查找记录行。
    可选的二选一句法 IGNORE INDEX (key_list) 可被用于告诉 MySQL 不使用特定的索引。如:

    mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    -> WHERE key1=1 AND key2=2 AND key3=3;
    mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    -> WHERE key1=1 AND key2=2 AND key3=3;
    

     

    2. 表连接的约束条件


     添加显示条件WHERE, ON, USING

    1. WHERE子句

    mysql>

    SELECT * FROM table1,table2 WHERE table1.id=table2.id;
    

    2. ON

    mysql>

    SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
    
    SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    LEFT JOIN table3 ON table2.id=table3.id;
    

    3. USING子句,如果连接的两个表连接条件的两个列具有相同的名字的话可以使用USING

     例如:

    SELECT FROM LEFT JOIN USING ()

     

    连接多于两个表的情况举例:

    mysql>

    SELECT artists.Artist, cds.title, genres.genre 
    
    FROM cds 
    
    LEFT JOIN genres N cds.genreID = genres.genreID 
    
    LEFT JOIN artists ON cds.artistID = artists.artistID; 
    
    

     

    或者 mysql>

    SELECT artists.Artist, cds.title, genres.genre 
    
    FROM cds 
    
    LEFT JOIN genres ON cds.genreID = genres.genreID 
    
     LEFT JOIN artists -> ON cds.artistID = artists.artistID
    
     WHERE (genres.genre = 'Pop'); 
    
    

    --------------------------------------------

     另外需要注意的地方 在MySQL中涉及到多表查询的时候,需要根据查询的情况,想好使用哪种连接方式效率更高。

     1. 交叉连接(笛卡尔积)或者内连接 [INNER | CROSS] JOIN

     2. 左外连接LEFT [OUTER] JOIN或者右外连接RIGHT [OUTER] JOIN 注意指定连接条件WHERE, ON,USING.

     

     

    3. MySQL如何优化LEFT JOIN和RIGHT JOIN


    在MySQL中,A LEFT JOIN B join_condition执行过程如下:

    1)·  根据表A和A依赖的所有表设置表B。

    2)·  根据LEFT JOIN条件中使用的所有表(除了B)设置表A。

    3)·   LEFT JOIN条件用于确定如何从表B搜索行。(换句话说,不使用WHERE子句中的任何条件)。

    4)·  可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系,MySQL提示出现一个错误。

    5)· 进行所有标准WHERE优化。

    6)· 如果A中有一行匹配WHERE子句,但B中没有一行匹配ON条件,则生成另一个B行,其中所有列设置为NULL。

    7)· 如果使用LEFT JOIN找出在某些表中不存在的行,并且进行了下面的测试:WHERE部分的col_name IS NULL,其中col_name是一个声明为 NOT NULL的列,MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

    RIGHT JOIN的执行类似LEFT JOIN,只是表的角色反过来。

    联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作,因为检查的表交换更少。请注意这说明如果执行下面类型的查询,MySQL进行全扫描b,因为LEFT JOIN强制它在d之前读取:

    SELECT *
    FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;
    

    在这种情况下修复时用a的相反顺序,b列于FROM子句中:

    SELECT *
    FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key)
    WHERE b.key=d.key;
    

    MySQL可以进行下面的LEFT JOIN优化:如果对于产生的NULL行,WHERE条件总为假,LEFT JOIN变为普通联接。

    例如,在下面的查询中如果t2.column1为NULL,WHERE 子句将为false:

    SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

    因此,可以安全地将查询转换为普通联接:

    SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

    这样可以更快,因为如果可以使查询更佳,MySQL可以在表t1之前使用表t2。为了强制使用表顺序,使用STRAIGHT_JOIN。

    展开全文
  • mysql大表更新sql的优化策略

    万次阅读 2013-12-19 21:38:42
    问题sql背景:项目有6个的要根据pid字段要写入对应的brand_id字段。但是这个其中有两个是千万级别的。我的worker运行之后,线上的mysql主从同步立刻延迟了!运行了一个多小时之后,居然延迟到了40分钟,而且只...
  • 1.4、在左和右的连接字段上都建索引后: 左使用了覆盖索引扫描,扫描行数没变,查询效率得到优化;右使用了非唯一性索引单值扫描ref,扫描行数减少,查询效率得到优化;  2、对于内连接 2.1、在左和...
  • tbl_direct_pos_201506 有 190 万数据,DDL:CREATE TABLE `tbl_direct_pos_201506` ( `acq_ins_code` char(13) NOT NULL DEFAULT '' COMMENT '机构代码', `trace_num` char(6) NOT NULL DEFAULT '' COMMENT '...
  • MySQL大表优化方案

    千次阅读 2018-09-12 19:38:58
    当MySQL单记录数过时,增删改查性能都会急剧下降,可以参考以下步骤来优化: 单表优化 除非单数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为...
  • 数据库的使用总要用到数据库的优化,那么主要从哪些方法去考虑优化呢? 今天重点来总结一下,理解中的数据库优化(mysql数据库) 一、mysql优化,主要从哪些方面去考虑 1.存储层 数据的存储引擎选取, 字段类型的选取, ...
  • 字段多少对查询速度的影响

    千次阅读 2017-12-25 13:23:31
    联合查询字段多少对查询的影响
  • 电话号码t_phonebook中有一千万条数据,其中号码字段phone上创建唯一索引, 且电话号码全都有数字组成,要求统计号码为321的电话号码数量, 下面算法执行速度最快的是——————,最慢的是—————— A:...
  • 学生 Student id(编号) name(名字) age(年龄) height(身高) 1 Tommy 26 170 2 Jerry 23 180 3 Frank 30 160        如所示,这里只是呈现了3条数
  • 如何优化MySQL千万级大表,我写了6000字的解读

    万次阅读 多人点赞 2019-10-21 20:03:03
    千万级大表如何优化,这是一个很有技术含量的问题,通常我们的直觉思维都会跳转到拆分或者数据分区,在此我想做一些补充和梳理,想和大家做一些这方面的经验总结,也欢迎大家提出建议。 从一开始脑海里开始也是...
  • 数据库优化优化

    千次阅读 2017-07-31 15:17:39
    需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。1. 将字段很多的分解成多个(垂直分割)对于字段较多的,如果有些字段的使用频率很低,则垂直分割该,将原来的一个分解为两...
  • update字段执行效率慢的解决办法

    万次阅读 2019-03-14 19:13:56
    update字段时,执行效率慢可以换成下边这种方式: 解决办法: 用merge into tableA using (括号里面是你需要的字段,来自于其它的数据 比如 tableB) on(tableA 跟tableB 的关联条件) when matched then update...
  • 1.数据库设计14规则 1. 原始单据与实体之间的关系   可以是一对一、一对多、多对多的关系。...这里的实体可以理解为基本。明确这种对应关系后,对我们设计录入界面大有好处。   〖例1〗:一份员工履
  • 当单记录数过时,增删改查性能都会急剧下降,可能原因:没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 、I/O吞吐量小,形成了瓶颈效应、没有创建计算列导致查询不优化、内存不足、网络...
  • 如果想查询A中class_id字段,没有在B中的class_id出现过的所有相关信息 table: students id username sex classify score class_id 10000 alien 女 作家 57 1 10001 zhang 男 词人 27 2 10002 ping...
  • mysql时间类型字段优化技巧

    千次阅读 2019-02-14 01:19:02
    文章目录mysql时间类型字段优化技巧第一种实现第二种实现两种实现对比 mysql时间类型字段优化技巧 最近开发一个功能时需要查询今天开播的主播用户。在业务上我们有一个数据存储了主播每次开播的记录。中有一...
  • 众所周知,sql写得怎么样,对于查询效率的影响是颇的。下面看一个比较普通的查询: 假设一张有event_date和event_time2个字段分别表示日期和时间,现在直接给你一个时间字符串,这个时间字符串是“日期+时间”...
  • 数据库根据时间字段查询优化

    千次阅读 2020-02-12 17:20:28
    数据库根据时间字段查询优化 mysql数据库 SELECT sw_id FROM link_update_record WHERE create_time BETWEEN '2020-2-1 00:00:00' AND '2020-2-12 23:59:59'; 将时间字段比较值改成时间格式 select sw_id from ...
  • 详解MySQL大表优化方案

    千次阅读 2016-08-04 18:00:53
    当MySQL单记录数过时,增删改查性能都会急剧下降,可以参考以下步骤来优化:单表优化字段、索引、查询SQL、引擎等。
  • @TO关于千万级的大表,单查询优化问题C 关于面试,千万级大表的单查询优化问题 前段时间面试,被问到了千万数据级的大表,单查询操作时如何优化的问题,以下是个人的一些见解及回答内容,如果有不对的地方,...
  • 对数据库优化的初级理解
  • 索引字段顺序对效率的影响

    千次阅读 2013-10-25 11:00:46
    1、驱动中的索引要将区间字段(sendtime之类)放到固定值(orgid等)的后面 2、驱动的连接字段可以放在索引最后,以避免读取rowid 3、连接的连接字段要放在索引最前面。 举个例子: selectcount(*) as col_...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 197,733
精华内容 79,093
关键字:

优化大表更新字段的效率