精华内容
下载资源
问答
  • MySQL事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关 1.MyISAM:不支持事务,用于只读程序提高性能 2.InnoDB:支持ACID事务、行级锁、并发 3.Berkeley DB:支持事务 一个事务是一个连续的一组数据库...
  • SQL优化最干货总结 - MySQL(2020最新版)

    万次阅读 多人点赞 2020-06-29 16:55:47
    MySQL - SQL优化干货总结(吐血版),别辜负了自己的梦想,欢迎白嫖、点赞、收藏。

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里

    前言

    BATJTMD等大厂的面试难度越来越高,但无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至在各大厂招聘岗位职能上都有明码标注,如果是你,在这个问题上能吊打面试官还是会被吊打呢?

    (注:如果看着模糊,可能是你撸多了)

    目录

    前言

    SELECT语句 - 语法顺序:

    SELECT语句 - 执行顺序:

    SQL优化策略

    一、避免不走索引的场景

    二、SELECT语句其他优化

    三、增删改 DML 语句优化

    四、查询条件优化

    五、建表优化

    一张照片背后的故事(自娱角)


    有朋友疑问到,SQL优化真的有这么重要么?如下图所示,SQL优化在提升系统性能中是:(成本最低 && 优化效果最明显) 的途径。如果你的团队在SQL优化这方面搞得很优秀,对你们整个大型系统可用性方面无疑是一个质的跨越,真的能让你们老板省下不止几沓子钱。

    • 优化成本:硬件>系统配置>数据库表结构>SQL及索引。
    • 优化效果:硬件<系统配置<数据库表结构<SQL及索引。
    String result = "嗯,不错,";
    
    if ("SQL优化经验足") {
        if ("熟悉事务锁") {
            if ("并发场景处理666") {
                if ("会打王者荣耀") {
                    result += "明天入职" 
                }
            }
        }
    } else {
        result += "先回去等消息吧";
    } 
    
    Logger.info("面试官:" + result );

    别看了,上面这是一道送命题。

    好了我们言归正传,首先,对于MySQL层优化我一般遵从五个原则:

    1. 减少数据访问: 设置合理的字段类型,启用压缩,通过索引访问等减少磁盘IO
    2. 返回更少的数据: 只返回需要的字段和数据分页处理 减少磁盘io及网络io
    3. 减少交互次数: 批量DML操作,函数存储等减少数据连接次数
    4. 减少服务器CPU开销: 尽量减少数据库排序操作以及全表查询,减少cpu 内存占用
    5. 利用更多资源: 使用表分区,可以增加并行操作,更大限度利用cpu资源

    总结到SQL优化中,就三点:

    • 最大化利用索引;
    • 尽可能避免全表扫描;
    • 减少无效数据的查询;

    理解SQL优化原理 ,首先要搞清楚SQL执行顺序:

    SELECT语句 - 语法顺序:

    1. SELECT 
    2. DISTINCT <select_list>
    3. FROM <left_table>
    4. <join_type> JOIN <right_table>
    5. ON <join_condition>
    6. WHERE <where_condition>
    7. GROUP BY <group_by_list>
    8. HAVING <having_condition>
    9. ORDER BY <order_by_condition>
    10.LIMIT <limit_number>


     

    SELECT语句 - 执行顺序:

    FROM
    <表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
    ON
    <筛选条件> # 对笛卡尔积的虚表进行筛选
    JOIN <join, left join, right join...> 
    <join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
    WHERE
    <where条件> # 对上述虚表进行筛选
    GROUP BY
    <分组条件> # 分组
    <SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
    HAVING
    <分组筛选> # 对分组后的结果进行聚合筛选
    SELECT
    <返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
    DISTINCT
    # 数据除重
    ORDER BY
    <排序条件> # 排序
    LIMIT
    <行数限制>


    SQL优化策略

    声明:以下SQL优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准,以免画蛇添足。


    一、避免不走索引的场景

    1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE username LIKE '%陈%'

    优化方式:尽量在字段后面使用模糊查询。如下:

    SELECT * FROM t WHERE username LIKE '陈%'

    如果需求是要在前面使用模糊查询,

    • 使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf(),查询字符串出现的角标位置,可参阅《MySQL模糊查询用法大全(正则、通配符、内置函数等)》
    • 使用FullText全文索引,用match against 检索
    • 数据量较大的情况,建议引用ElasticSearch、solr,亿级数据量检索速度秒级
    • 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用like '%xx%'。

     

    2. 尽量避免使用in 和not in,会导致引擎走全表扫描。如下:

    SELECT * FROM t WHERE id IN (2,3)

    优化方式:如果是连续数值,可以用between代替。如下:

    SELECT * FROM t WHERE id BETWEEN 2 AND 3

    如果是子查询,可以用exists代替。详情见《MySql中如何用exists代替in》如下:

    -- 不走索引
    select * from A where A.id in (select id from B);
    -- 走索引
    select * from A where exists (select * from B where B.id = A.id);


     
    3. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE id = 1 OR id = 3

    优化方式:可以用union代替or。如下:

    SELECT * FROM t WHERE id = 1
       UNION
    SELECT * FROM t WHERE id = 3

    4. 尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE score IS NULL

    优化方式:可以给字段添加默认值0,对0值进行判断。如下:

    SELECT * FROM t WHERE score = 0

     

    5.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。

    可以将表达式、函数操作移动到等号右侧。如下:

    -- 全表扫描
    SELECT * FROM T WHERE score/10 = 9
    -- 走索引
    SELECT * FROM T WHERE score = 10*9

     

    6. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:

    SELECT username, age, sex FROM T WHERE 1=1

    优化方式:用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。


    7. 查询条件不能用 <> 或者 !=

    使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。


    8. where条件仅包含复合索引非前置列

    如下:复合(联合)索引包含key_part1,key_part2,key_part3三列,但SQL语句没有包含索引前置列"key_part1",按照MySQL联合索引的最左匹配原则,不会走联合索引。详情参考《联合索引的使用原理》

    select col1 from table where key_part2=1 and key_part3=2


    9. 隐式类型转换造成不使用索引 

    如下SQL语句由于索引对列类型为varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。 

    select col1 from table where col_varchar=123; 


    10. order by 条件要与where中条件一致,否则order by不会利用索引进行排序

    -- 不走age索引
    SELECT * FROM t order by age;
    
    -- 走age索引
    SELECT * FROM t where age > 0 order by age;

    对于上面的语句,数据库的处理顺序是:

    • 第一步:根据where条件和统计信息生成执行计划,得到数据。
    • 第二步:将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则重新进行排序操作。
    • 第三步:返回排序后的数据。

    当order by 中的字段出现在where条件中时,才会利用索引而不再二次排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。

    这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。

    11. 正确使用hint优化语句

    MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言,处于版本变更带来的表结构索引变化,更建议避免使用hint,而是通过Analyze table多收集统计信息。但在特定场合下,指定hint可以排除其他索引干扰而指定更优的执行计划。

    1. USE INDEX 在你查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
    2. IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
    3. FORCE INDEX 为强制 MySQL 使用一个特定的索引,可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

    在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。《MySQL中特别实用的几种SQL语句送给大家》博文建议阅读,干货

    例如:

    SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

     

    二、SELECT语句其他优化

    1. 避免出现select *

    首先,select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

    使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

    建议提出业务实际需要的列数,将指定列名以取代select *。具体详情见《为什么大家都说SELECT * 效率低》


    2. 避免出现不确定结果的函数

    特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句,使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。


    3.多表关联查询时,小表在前,大表在后。

    在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。

    例如:表1有50条数据,表2有30亿条数据;如果全表扫描表2,你品,那就先去吃个饭再说吧是吧。

    4. 使用表的别名

    当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。


    5. 用where字句替换HAVING字句

    避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。

    where和having的区别:where后面不能使用组函数

    6.调整Where字句中的连接顺序

    MySQL采用从左往右,自上而下的顺序解析where子句。根据这个原理,应将过滤数据多的条件往前放,最快速度缩小结果集。


    三、增删改 DML 语句优化

    1. 大批量插入数据

    如果同时执行大量的插入,建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快(方法一),一般情况下批量插入效率有几倍的差别。

    方法一:

    insert into T values(1,2); 
    
    insert into T values(1,3); 
    
    insert into T values(1,4);

    方法二:

    Insert into T values(1,2),(1,3),(1,4); 


    选择后一种方法的原因有三。 

    • 减少SQL语句解析的操作,MySQL没有类似Oracle的share pool,采用方法二,只需要解析一次就能进行数据的插入操作;
    • 在特定场景可以减少对DB连接次数
    • SQL语句较短,可以减少网络传输的IO。

    2. 适当使用commit

    适当使用commit可以释放事务占用的资源而减少消耗,commit后能释放的资源如下:

    • 事务占用的undo数据块;
    • 事务在redo log中记录的数据块; 
    • 释放事务施加的,减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候,必须分解删除量并定期commit。


    3. 避免重复查询更新的数据

    针对业务中经常出现的更新行同时又希望获得改行信息的需求,MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法,在MySQL中可以通过变量实现。

    例如,更新一行记录的时间戳,同时希望查询当前记录中存放的时间戳是什么,简单方法实现:

    Update t1 set time=now() where col1=1; 
    
    Select time from t1 where id =1; 

    使用变量,可以重写为以下方式: 

    Update t1 set time=now () where col1=1 and @now: = now (); 
    
    Select @now; 

    前后二者都需要两次网络来回,但使用变量避免了再次访问数据表,特别是当t1表数据量较大时,后者比前者快很多。


    4.查询优先还是更新(insert、update、delete)优先

    MySQL 还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎,比如 MyISAM 、MEMROY、MERGE,对于Innodb 存储引擎,语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下:

    1)写入操作优先于读取操作。

    2)对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理。

    3)对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符,允许你修改它的调度策略:

    • LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE;
    • HIGH_PRIORITY关键字应用于SELECT和INSERT语句;
    • DELAYED关键字应用于INSERT和REPLACE语句。


           如果写入操作是一个 LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作。在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候,才允许写入者开始操作。这种调度修改可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。

    SELECT 查询的HIGH_PRIORITY(高优先级)关键字也类似。它允许SELECT 插入正在等待的写入操作之前,即使在正常情况下写入操作的优先级更高。另外一种影响是,高优先级的 SELECT 在正常的 SELECT 语句之前执行,因为这些语句会被写入操作阻塞。如果希望所有支持LOW_PRIORITY 选项的语句都默认地按照低优先级来处理,那么 请使用--low-priority-updates 选项来启动服务器。通过使用 INSERTHIGH_PRIORITY 来把 INSERT 语句提高到正常的写入优先级,可以消除该选项对单个INSERT语句的影响。


    四、查询条件优化

    1. 对于复杂的查询,可以使用中间临时表 暂存数据;


    2. 优化group by语句

    默认情况下,MySQL 会对GROUP BY分组的所有值进行排序,如 “GROUP BY col1,col2,....;” 查询的方法如同在查询中指定 “ORDER BY col1,col2,...;” 如果显式包括一个包含相同的列的 ORDER BY子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。

    因此,如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL禁止排序。例如:

    SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

    3. 优化join语句

    MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。


    例子:假设要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

    SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )


    如果使用连接(JOIN).. 来完成这个查询工作,速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话,性能将会更好,查询如下:

    SELECT col1 FROM customerinfo 
       LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
          WHERE salesinfo.CustomerID IS NULL 

    连接(JOIN).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。


    4. 优化union查询

    MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行,否则建议使用union all。原因在于如果没有all这个关键词,MySQL会给临时表加上distinct选项,这会导致对整个临时表的数据做唯一性校验,这样做的消耗相当高。

    高效:

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
    
    UNION ALL 
    
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

    低效:

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
    
    UNION 
    
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

    5.拆分复杂SQL为多个小SQL,避免大事务

    • 简单的SQL容易使用到MySQL的QUERY CACHE; 
    • 减少锁表时间特别是使用MyISAM存储引擎的表; 
    • 可以使用多核CPU。

    6. 使用truncate代替delete

    当删除全表中记录时,使用delete语句的操作会被记录到undo块中,删除记录也记录binlog,当确认需要删除全表时,会产生很大量的binlog并占用大量的undo数据块,此时既没有很好的效率也占用了大量的资源。

    使用truncate替代,不会记录可恢复的信息,数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外,使用truncate可以回收表的水位,使自增字段值归零。

    7. 使用合理的分页方式以提高分页效率

    使用合理的分页方式以提高分页效率 针对展现等分页需求,合适的分页方式能够提高分页的效率。

    案例1: 

    select * from t where thread_id = 10000 and deleted = 0 
       order by gmt_create asc limit 0, 15;


           上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此,该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。

    适用场景:当中间结果集很小(10000行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。


    案例2: 

    select t.* from (select id from t where thread_id = 10000 and deleted = 0
       order by gmt_create asc limit 0, 15) a, t 
          where a.id = t.id; 

    上述例子必须满足t表主键是id列,且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序,再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果(例子中是15行)对应的表数据IO。因此,该写法每次翻页消耗的资源和时间都基本相同,就像翻第一页一样。

    适用场景:当查询和排序字段(即where子句和order by子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用。

    五、建表优化

    1. 在表中建立索引,优先考虑where、order by使用到的字段。


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


    3. 查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段分页进行查询,循环遍历,将结果合并处理进行展示。要查询100000到100050的数据,如下:

    SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
       FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

    4. 用varchar/nvarchar 代替 char/nchar

    尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

     

    一张照片背后的故事(自娱角)

     

    这是由蒋玉树先生所拍摄的照片《小店》
    在四川凉山
    母亲临时有事
    让自己的女儿帮忙看店

    突然天上下起了雪
    在这个只有一面墙的小店里
    女孩儿一边搓着手
    一边欣赏着美景

     

    这些MySQL文章你可能也会喜欢:

    《MySQL中的 utf8 并不是真正的UTF-8编码 ! !》

    《MySQL中特别实用的几种SQL语句送给大家》

    《SQL 查询语句先执行 SELECT?兄弟你认真的么?》

    《有意思,原来SQL中的NULL是这么回事儿》

     

    展开全文
  • mysql中只有innoDB存储引擎支持事务处理,所以mysql当中innoDB也是默认的存储引擎。在实际的应用当中经常会使用到事务像转账操作,一个账户的金额减少和另一个账户的金额增加都必须保证都正确执行,否则必须回滚。...

    1.事务

          事务是将许多动作整合成一个逻辑执行单元,这个执行单元要么全部执行,要么一个都不执行不执行。事务操作具有4的特性

          在mysql中只有innoDB存储引擎支持事务处理,所以mysql当中innoDB也是默认的存储引擎。在实际的应用当中经常会使用到事务像转账操作,一个账户的金额减少和另一个账户的金额增加都必须保证都正确执行,否则必须回滚。

          在mysql有个属性叫做autocommit,表示是否自动提交事务,默认值是1,表示true,所以我们一般使用sql语句,执行完就立即更新数据库了。如果set auotcommit=0之后sql语句执行完之后必须执行commit才能使数据持久化。


    2.事务的使用

    开始一个事务start transaction,begin;

    回滚事务rollback;

    提交一个事务commit

    创建一个保存点,可以指定回滚到该点savepoint identifier;

    删除一个事务的保存点release savepoint identifier

    把事务回滚到标记点 rollback to identifier


    使用的例子:

    begin;
    update score set score=40 where scoreid=1;
    savepoint s1;
    update score set score=50 where scoreid=1;
    select * from score;
    rollback to savepoint s1;
    select * from score;
    commit;

    最后结果score=40。


    3.JDBC中使用事务 try,catch块

    try{  
    			connection.setAutoCommit(false);  //不自动提交
    			......执行jdbc代码  
    			connection.commit(); 
    		}catch(Exception e){  
    			connection.roolback(); 
    		}finally{ 
    			 //清理资源
    			connection.setAutoCommit(true);  //改回自动提交
    		}

    JDBC中connection提供有关事务处理:

       getAutoCommit()  获取此Connection 对象的当前自动提交模式。

       setAutoCommit(boolean autoCommit) 将此连接的自动提交模式设置为给定状态。

       getTransactionIsolation() 获取此Connection 对象的当前事务隔离级别。

       setTransactionIsolation(int level) 试图将此Connection 对象的事务隔离级别更改为给定的级别。

       setSavepoint() 在当前事务中创建一个未命名的保存点 (savepoint),并返回表示它的新 Savepoint 对象。

       setSavepoint(String name) 在当前事务中创建一个具有给定名称的保存点,并返回表示它的新 Savepoint 对象。

       releaseSavepoint(Savepoint savepoint) 从当前事务中移除指定的Savepoint 和后续Savepoint 对象。

       rollback() 取消在当前事务中进行的所有更改,并释放此Connection 对象当前持有的所有数据库锁。

       rollback(Savepoint savepoint) 取消所有设置给定Savepoint 对象之后进行的更改。


    展开全文
  • 使用mysql这么多年,以前一直只懂写sql,却不其中运行原理,直至最近抽时间看了一下mysql代码, 对其事务运行原理及sql解析优化有一些更深入的理解. 本篇是讲述sql解析的开篇之作,希望透过最最简单的sql来让大家...

    1. 前言

    使用mysql这么多年,以前一直只懂写sql,却不其中运行原理,直至最近抽时间看了一下mysql源代码,
    对其事务运行原理及sql解析优化有一些更深入的理解.
    本篇是讲述sql解析的开篇之作,希望透过最最简单的sql来让大家了解sql的查询解析过程,
    如果本文力图把一个简单sql的执行过程所涉及的方法及其相关值的变化详细讲清楚,如果有问题欢迎留言.
    

    2. 准备

    2.1 参考

    linux下使用eclipse debug mysql5.6

    2.2 创建表

    create table wlt_tab1(
    id int primary key
    );
    

    2.3 执行查询

    select * from wlt_tab1 where 1=0 and id=0
    

    3. 执行查询

    当前sql执行时序图
    如果看不清楚,可在PC端点击查看到大图,
    下面会针对上面时序图的每个方法详细解说!

    3.1 do_handle_one_connection()

    如果线程池中没有可用的缓存线程,则会通过本方法创建线程来处理用户请求.

    3.2 do_command()

    读取客户端传递的命令并分发

    3.3 dispatch_command()

    根据用户请求信息的第一个字段表示这个请求类型,以下摘取本方法代表性的简化 代码来说明本方法在查询过程中处理了哪些功能

    switch(command){
        case COM_INIT_DB: ...;
        case COM_CHANGE_USER: ...;
        case COM_STMT_PREPARE: ...;
        //如果是查询请求
        case COM_QUERY:
        //从网络数据包中读取Query并存入thd->query
        alloc_query(thd,packet,packet_length);
        //解析
        mysql_parse(thd,thd->query(),thd->query_length(),&parser_state);
        ...
    }

    3.4 mysql_parse()

    /*
      从lex_start方法源代码上看,本方法主要是将thd->lex对象内容重新清理
      置为初始化状态.
      注: thd是当前线程上下文信息类,后续与用户处理相关函数都会传入这个类,
      估计是c++没有像java的ThreadLocal那么方便的类,所以老是要这么麻烦地传
      来传去的
      lex: 语法分析对象
      本方法的实现在:sql_lex.cc
    */
    lex_start(thd);
    /*
    查看query cache中是否有命中,如果有,则返回结果
    如果没有,则作如下动作
    */
    if(query_cache_send_result_to_client(thd,rawbuf,length)<=0){
        //解析sql
        bool err = parse_sql(thd,parser_state,NULL);
        //执行
        mysql_execute_command(thd);
    }

    parse_sql() sql解析过程

    mysql解析过程如下:
    sql解析
    mysql是使用了开始的bison(即yacc的开源版)作为sql语法解析器
    如上图所示,在lex词法解析阶段,会解析出select,from,where这几个token
    接下来sql_yacc.cc的MYSQLparse会根据上面的token解析出语法树,yacc是使用巴科斯范式(BNF)表达语法规则,大家可以百度学习一下,下面节选几个与我们相关的表达式:

    select_from:
              FROM join_table_list where_clause group_clause having_clause
              opt_order_clause opt_limit_clause procedure_analyse_clause
              {
                Select->context.table_list=
                  Select->context.first_name_resolution_table=
                    Select->table_list.first;
              }
            | FROM DUAL_SYM where_clause opt_limit_clause
              /* oracle compatibility: oracle always requires FROM clause,
                 and DUAL is system table without fields.
                 Is "SELECT 1 FROM DUAL" any better than "SELECT 1" ?
              Hmmm :) */
            ;
    where_clause:
              /* empty */  { Select->where= 0; }
            | WHERE
              {
                Select->parsing_place= IN_WHERE;
              }
              expr
              {
                SELECT_LEX *select= Select;
                select->where= $3;
                select->parsing_place= NO_MATTER;
                if ($3)
                  $3->top_level_item();
              }
            ;
    

    parse_sql()方法执行完后,我们可以在gdb中查看语法树lex:

    ##查看select_lex->where
    (gdb) call print_where(lex->select_lex->where,"",QT_WITHOUT_INTRODUCERS)
    WHERE:() 0x7fff98005e10 ((1 = 0) and (`id` = 0))
    (gdb) p lex->select_lex->table_list->first
    $9 = (TABLE_LIST *) 0x7fff98005260
    ##查看sql使用的database
    (gdb) p $9->db
    $10 = 0x7fff980057c0 "wlt"
    (gdb) p $9->table_name
    $11 = 0x7fff98005218 "wlt_tab1"

    3.5 mysql_execute_command()

    //获取解析后的sql语法树
    Lex *lex = thd->lex;
    //根据解析后的sql语法树的类型,决定如何作下一步处理
    switch(lex->sql_command){
        case SQLCOM_SHOW_STATUS:...;
        case SQLCOM_INSERT: ...;
        case SQLCOM_SELECT:
            ...
            res = execute_sqlcom_select(thd,all_tables);
    }

    3.6 execute_sqlcom_select()

    3.7 handle_select()

    3.8 mysql_select()

    sql真正执行入口,
    这里会分别执行:

    • JOIN::prepare() ; //预处理
    • JOIN::optimize();//查询优化
    • JOIN::exec();//执行

    3.9 JOIN::prepare()

    执行sql查询优化计划前的准备工作
    其中 setup_wild()方法会把查询语句中的”*”扩展为表上的所有列

    3.9.1 setup_wild()

    可以看本方法的主要代码:

      while (wild_num && (item= it++))
      {
        if (item->type() == Item::FIELD_ITEM &&
        //如果field值为*
        ((Item_field*) item)->field_name[0] == '*' &&
        !((Item_field*) item)->field)
        {
                  if (subsel &&
              subsel->substype() == Item_subselect::EXISTS_SUBS)
          {
          ...
          }else if (insert_fields(thd, ((Item_field*) item)->context,
                                 ((Item_field*) item)->db_name,
                                 ((Item_field*) item)->table_name, &it,
                                 any_privileges))
          {
          ...
          }
        }

    3.9.2 insert_fields()

    //字段迭代器
    Field_iterator_table_ref field_iterator;
    field_iterator.set(tables);
    for (; !field_iterator.end_of_fields(); field_iterator.next())
        {
          Item *item;
          item= field_iterator.create_item(thd);
          if (!found)
          {
            found= TRUE;
            it->replace(item); 
          }
          else
            it->after(item);   /* 将当前sql语句的表的字段一一加到fields_list中 */
         }

    3.10 JOIN::optimize()

    JOIN::optimize()函数主要功能是对sql各种优化,包括条件下推,关联索引列,计算最优查询优化执行计划…
    与本请求sql优化相关的是optimize_cond()方法
    处理本sql时,optimize_cond()方法最终会将select_lex->cond_value置为Item::COND_FALSE,针对这个结果,后续处理如下:

        if (select_lex->cond_value == Item::COND_FALSE || 
            select_lex->having_value == Item::COND_FALSE || 
            (!unit->select_limit_cnt && !(select_options & OPTION_FOUND_ROWS)))
        {                       /* Impossible cond */
          zero_result_cause=  select_lex->having_value == Item::COND_FALSE ?
                               "Impossible HAVING" : "Impossible WHERE";
          tables= 0;
          primary_tables= 0;
          best_rowcount= 0;
          goto setup_subq_exit;
        }

    3.10.1 optimize_cond()

    这个方法主要代码可以简化如下:

    //等式合并
    build_equal_items(thd,conds,NULL,true,join_list,cond_equal);
    //常量求值
    propagate_cond_constants(thd, (I_List<COND_CMP> *) 0, conds, conds);
    //条件去除
    remove_eq_conds(thd, conds, cond_value) ;

    在刚进这个方法时,我们可以打印 conds对象的值

    (gdb) p call print_where(conds,"",QT_WITHOUT_INTRODUCERS)
    WHERE:() 0x7fff98005e10 ((1 = 0) and (`wlt`.`wlt_tab1`.`id` = 0))
    

    remove_eq_conds()方法会优化掉条件中 1=0

    3.10.1.1 remove_eq_conds()

    本方法会调用: internal_remove_eq_conds(thd, cond, cond_value); // Scan all the condition

    3.10.1.2 internal_remove_eq_conds()

     while ((item=li++))
        {
          /×这里会取当前条件组的第一个条件递归调用本方法
          在递归的方法中会判断到item->const_item()为true,
          并对1=0进行求值:
          *tmp_cond_value= eval_const_cond(cond) ? Item::COND_TRUE : Item::COND_FALSE;
          ×/
          Item *new_item=internal_remove_eq_conds(thd, item, &tmp_cond_value);
                switch (tmp_cond_value) {
          case Item::COND_OK:           // Not TRUE or FALSE
        if (and_level || *cond_value == Item::COND_FALSE)
          *cond_value=tmp_cond_value;
        break;
        //当前1=0的条件会进入 Item::COND_FALSE
          case Item::COND_FALSE:
        if (and_level)
        {
          *cond_value=tmp_cond_value;
          return (Item*) 0;         // Always false
        }
        break;
          }

    这里在gdb中如果输入:

    (gdb) call print_where(conds,"",QT_WITHOUT_INTRODUCERS)
    WHERE:() (nil) 

    3.11 JOIN::exec()

      //
      if (zero_result_cause)
      {
        //返回0结果行
        return_zero_rows(this, *columns_list);
        DBUG_VOID_RETURN;
      }

    作者: 吴炼钿

    展开全文
  • Incorrect string value: '\xE5\xAD\xA6\xE5\xB7\xA5...' 将该表中的每一列的字符集都改为utf-8 SQL语句:alter table dept change loc loc varchar(50) character set utf8;Error Code: 1062. Duplicate entr...
    Error Code: 1366. Incorrect string value: '\xE5\xAD\xA6\xE5\xB7\xA5...'
    
        将该表中的每一列的字符集都改为utf-8
        SQL语句:alter table dept change loc loc varchar(50) character set utf8;

    Error Code: 1062. Duplicate entry '40' for key 'PRIMARY'    
        设置主键自增时,和设置主键时可能有粗心的同学和我一样就是将一个表中的有两个相同值的属性设为主键这时就会报错。解决方法很简单就是保证设置主键属性下的值不能相同即可。

    Error Code: 1005. Can't create table 'book.bookinfo' (errno: 150)    
         在mysql 中建立引用约束的时候会出现MySQL ERROR 1005: Can't create table (errno: 150)的错误信息结果是不能建立 引用约束。
             出现问题的大致情况
           1、外键的引用类型不一样,如主键是int外键是char
        2、找不到主表中引用的列

        3、主键和外键的字符编码不一致,也可能存储引擎不一样

    Error Code: 1146. Table 'book.book' doesn't exist    

    INNODB是MYSQL数据库一种流行的数据库引擎,支持事务(行级),在企业级应用上成为可能

    ibdata用来储存文件的数据,而库名的文件夹里面的那些表文件只是结构而已,由于新版的mysql默认试innodb,所以ibdata1文件默认就存在了,少了这个文件有的数据表就会出错。

    可以尝试修复数据库:repair table tablename

    Error Code: 1452. Cannot add or update a child row

    原因:

    设置的外键和对应的另一个表的主键值不匹配。

    解决方法:

    找出不匹配的值修改。

    或者清空两表数据。

    Error Code: 1136. Column count doesn't match value count at row 1

    由于写的SQL语句里列的数目和后面的值的数目不一致,

    比如insert into 表名 (field1,field2,field3) values('a','b')这样前面的是三列,后面却只有二个值,这就会出现这个错误的。

        Error Code: 2013. Lost connection to MySQL server during query

    一般这种情况可能的原因:
    
    1.数据库太大,导致服务器超时了并且关闭了连接。这种情况你可以通过修改mysql的超时配置来改善,如interactive_timeout、wait_timeout、max_allowed_packet。
    
    2.也有可能是你访问的数据库表有问题,这种情况一般就是只当你访问某个表才会出现这个问题,那么你可能需要修复下该表。
    
    以下是 mysql用户手册 中的相关资料:
    
    18.2 使用MySQL时的一些常见错误
    
    18.2.1 MySQL server has gone away错误
    
    本小节也涉及有关Lost connection to server during query的错误。
    
    对MySQL server has gone away错误最常见的原因是服务器超时了并且关闭了连接。缺省地,如果没有事情发生,服务器在 8个小时后关闭连接。你可在启动mysqld时通过设置wait_timeout变量改变时间限制。
    
    你可以通过执行mysqladmin version并且检验正常运行的时间来检查MySQL还没死掉。
    
    如果你有一个脚本,你只须再发出查询让客护进行一次自动的重新连接。
    
    





    展开全文
  • MySQL事务日志

    万次阅读 2021-03-15 17:51:49
    MySQL事务 事务MySQL区别于NoSQL的重要特征,...事务可看作是对数据库操作的基本执行单元,可能包含一个或者多个SQL语句。这些语句在执行时,要么都执行,要么都不执行。 事务的执行主要包括两个操作,提交和回滚。
  • MySQL 事务与锁详解 什么是数据库的事务? 什么是事务? ​ 事务是数据库最小的工作单元,也就是说我们刚才提到的这些场景,它们是作为一个逻辑单元执行 的,既然是最小的工作单元,意味着它是不可再分的。这里面...
  • 1,MySQL事务支持 1)MySQL事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关: Sql代码 代码如下: MyISAM:不支持事务,用于只读程序提高性能 InnoDB:支持ACID事务、行级锁、并发 Berkeley DB:支持...
  • mySQL事务处理

    2013-06-13 20:04:31
    现在innoDB支持 事务了, 上述的 java 代码是否能实现 以下的 事务隔离的 操作, 在修改的时候处于锁定状态 或者 只可以通过存储过程来实现, 单行的锁定 BEGIN; SELECT book_number FROM book WHERE book_id = ...
  • SQL代码。 坏事可能会导致: 这些 Tx 对象可以保持打开状态,从池中保留一个连接而不返回它。 数据库的状态可能与代表它的Go变量的状态不同步。 你可能会认为你正在一个事务中的单个连接上执行查询,但实际上Go已经...
  • 我这里要说明的mysql事务处理多个SQL语句的回滚情况。比如说在一个存储过程中启动一个事务,这个事务同时往三个表中插入数据,每插完一张表需要判断其是否操作成功,如果不成功则需要回滚,最后一张表判断其插入成功...
  • MySQLsql性能优化

    万次阅读 2018-07-19 19:41:44
    二十种MySQL性能优化 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的...sql代码演示: //查询缓存不开启 select * from table where creat...
  • /* 启动MySQL */ net start mysql /* 连接与断开服务器 */ mysql -h 地址 -P 端口 -u 用户名 -p 密码 /* 跳过权限验证登录MySQL */ mysqld --skip-grant-tables -- 修改root密码 密码加密函数password() update ...
  • 1005:创建表失败 1006:创建数据库失败 1007:数据库已存在,创建数据库失败 1008:数据库不存在,删除数据库失败 1009:不能删除数据库文件导致删除数据库失败 1010:不能删除数据目录导致删除数据库失败 ...
  • mysql实现事务

    千次阅读 2016-08-14 20:34:01
    实现事务:定义、ACID属性、执行过程、实现事务代码事务的回滚、隔离级别、serializable隔离级别中锁的使用
  • SQL批处理与事务控制

    千次阅读 2016-04-25 23:39:02
    今天我想要分享的是关于数据库的批处理与事务的控制。批处理对于项目的实际应用有非常大的具体意义。 一、批处理部分 ...注意:auto_increment只适用于mysql中,对于oracle需要用的是创建一个序列来
  • MySQL事务

    万次阅读 2018-07-25 19:34:15
    MySQL事务 在关系型数据库中,事务的重要性不言而喻,又要对数据库稍有了解的人都知道事务具有ACID四个基本属性,但是大家可能不知道的是如何实现这四个属性。下面,我们将对事务的实现进行分析,尝试理解数据库...
  • 超详细的MySQL三万字总结

    万次阅读 多人点赞 2021-08-22 21:27:51
    SQLyog 登录数据库数据库管理系统数据库管理系统、数据库和表的关系SQL 的概念什么是 SQLSQL 作用SQL 语句分类MySQL 的语法DDL 操作数据库创建数据库创建数据库的几种方式查看数据库修改数据库删除数据库使用数据库...
  • MySQL SQL语句优化技巧

    万次阅读 2016-10-18 16:38:14
    这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(…) 13、很多时候用 exists 代替 in 是一个好的选择: select num from a where num in(select num from b) 用...
  • SQL + NoSQL = MySQLMySQL 文档存储攻略

    千次阅读 2020-01-08 11:15:17
    MySQL文档存储使其可以同时支持传统的 SQL 应用程序和模式自由的文档数据库应用程序,从而消除了对单独 NoSQL 文档数据库的需求。开发人员可以在同一数据库和同一应用程序中混合使用关系数据和 JSON 文档。
  • (1) 用begin,rollback,commit来实现 代码如下:begin 开始一个事务rollback 事务回滚commit 事务确认(2)直接用set来改变mysql的自动提交模式 代码如下:set autocommit=0 禁止自动提交set autocommit=1 开启...
  • MYSQL事务编程

    千次阅读 2012-04-09 14:14:39
    MYSQL事务处理主要有两种方法。 1、用begin,rollback,commit来实现  begin 开始一个事务  rollback 事务回滚  commit 事务确认  2、直接用set来改变mysql的自动提交模式  MYSQL默认是自动提交的,也...
  • Spring TransactionTemplate + Mysql事务理解

    千次阅读 2017-07-10 13:41:39
    Mysql事务使用autocommit说明在mysql有个属性叫做autocommit,表示是否自动提交事务,默认值是1,表示true,所以我们一般使用sql语句,执行完就立即更新数据库了。如果set auotcommit=0之后sql语句执行完之后必须...
  • 一文读懂Spring事务MySQL事务与锁

    千次阅读 2017-01-03 16:26:41
    【1】事务 事务的四个关键属性(ACID) 原子性(atomicity): 事务是一个原子操作, 由一系列动作组成. 事务的原子性确保动作要么全部完成要么完全不起作用. 一致性(consistency): 一旦所有事务动作完成, ...
  • Mysql数据库原理与sql优化

    千次阅读 2019-06-09 17:16:46
    Mysql数据库结构图 一,mysql数据库引擎介绍 1,ISAM(Indexed Sequential Access Method): ISAM是一个定义明确且历经时间考验的数据表格管理方法...ISAM的两个主要不足之处在于,它不支持事务处理,也不能够...
  • Mysql学习代码笔记

    万次阅读 多人点赞 2020-06-17 16:06:35
    数据库事务语言(Transaction Control Language,T C L) ​ start transaction 开启事务 ​ commit 提交事务 ​ rollback 回滚事务 set transaction 设置事务属性 D D L 操作 创建数据库和删除数据库 create ...
  • MySQL事务与锁详解

    千次阅读 2018-10-13 17:05:01
    事务 事务支持ACID特性 A原子性:所有操作要么都做要么都不做 C一致性:事务将数据库从一种状态...ANSI/ISO SQL标准定义了4中事务隔离级别:未提交读(read uncommitted),读提交(read committed),可重复读(repea...
  • MySQL与Spring事务管理

    千次阅读 2017-03-02 20:08:21
    数据库事务是保证在并发情况下能够正确执行的重要支撑,MySQL常见的数据库引擎中支持事务的是InnoDB,事务就是一系列操作,正确执行并提交,如果中途出现错误就回滚。事务要保证能够正常的执行,就必须要保持ACID...
  • 数据库应用——MySQL事务和索引

    千次阅读 2021-04-11 18:42:26
    事务和索引什么是事务执行事务模拟事务索引索引的分类测试索引创建测试表创建索引索引原则 什么是事务 要么都成功,要么都失败 就是将一组SQL放在一个批次中去执行 事务原则:ACID 原则 原子性,一致性,隔离性,...
  • MYSQL

    2020-12-14 13:49:56
    SQL是结构化查询语言,是一种用来操作RDBMS的数据库语言,当前关系型数据库都支持使用SQL语言进行操作,也就是说可以通过 SQL 操作 oracle,sql server,mysql,sqlite 等等所有的关系型的数据库 MYSQL特点 使用C和C++...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 104,031
精华内容 41,612
关键字:

创建事务sql代码mysql

mysql 订阅