图片 1

锁等待设置,利用索引改善sql语句

一. 什么时候使用表锁

  对于INNODB表,在绝大部分情况下都应该使用行锁。在个别特殊事务中,可以考虑使用表锁(建议)。
  1.
事务需要更新大部份或全部数据,表又比较大,默认的行锁不仅使这个事务执行效率低,可能造成其他事务长时间锁等待和锁冲突,这种情况考虑使用表锁来提高事务的执行速度(具我在sql
server中的经历,该大表有上100w,删除40w,表锁有时会造成长时间未执行完成.
还是使用分批来执行好)。
  2.
事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表,避免死锁,减少数据库因事务回滚带来的开销。
  使用表锁注意两点
    (1) lock
tables虽然可以给innodb加表锁,但表锁不是由innodb存储引擎层管理,则是由上层mysql
server负责。仅当autocommit=0,
innodb_table_locks=1(默认设置)时,innodb层才知道mysql加的表锁,mysql
server也才能感知innodb加的行锁。
    (2) 用lock tables对innodb表加锁时要注意, 要将autocommit
设置为0,否则mysql 不会给表加锁; 事务结束前,不要用unlock
tables释放表锁,因为它会隐式的提交事务。 commit 或rollback
并不能释放用lock tables 加的表锁。必须用unlock tables释放表锁。

    下面在5.7版本数据库中,会话2也会阻塞,按上面说法是不会阻塞的,因为会话1没有设置SET
autocommit =0(以后在论证)

-- 会话1 给city加表锁读,  不设置  SET autocommit =0
  LOCK TABLES city READ

  --  会话2 会阻塞
 UPDATE city SET CityCode='005' WHERE city_id=103  

  -- 会话1提交
 COMMIT;
 -- 会话1 释放表锁
 UNLOCK TABLES;

(1)库的基础操作

很多人不知道SQL语句在SQL
SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL
SERVER误解。比如:

二. 关于死锁

  在myisam中是使用的表锁,在获得所需的全部锁时,
要么全部满足,要么等待,因此不会出现死锁。下面在innodb中演示一个死锁例子:

会话1

会话2

SET autocommit =0

SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

SET autocommit =0

SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

— 因为会话2 已获得排他锁, 该语句等待

 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

 

 

— 死锁

 SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

  上面案例中,
两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。
发生死锁后,innodb会自动检测到,并使一个事务释放锁并回退(回滚),另一个事务得锁完成事务。

  • 查看已有库: show databases;
  • 创建库(制定默认字符集): ccreate database 库名 default
    charset=utf8;
  • 查看创建库的语句: show create database 库名;
  • 查看当前所在库: select database();
  • 切换库: use 库名;
  • 查看库中已有表: show tables;
  • 删除库: drop database 库名;

1.select * from table1 where name=”zhangsan” and tID >
10000和执行select * from table1 where tID > 10000 and
name=”zhangsan”

三. 锁等待查看    

  涉及外部锁或表锁,innodb并不能完全自动检测到死锁,这需要设置锁等待超时参数innodb_lock_wait_timeout来解决(设置需慎重),这个参数并不是只用来解决死锁问题,在并发下,大量事务无法立即获得所需锁而挂起,将占用大量资源,甚至拖跨数据库
(在sql server中默认是-1 总是等待)。

--  下面是5秒  获取不到锁就超时
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

图片 1

(2)表的管理

一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tID是一个聚合索引,那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要先从全表中查找看有几个name=”zhangsan”的,而后再根据限制条件条件tID>10000来提出查询结果。

  • 创建表(制定字符集)

事实上,这样的担心是不必要的。SQL
SERVER中有一个“查询分析优化器”,它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间,也就是说,它能实现自动优化。

    create table tablename(

虽然查询优化器可以根据where子句自动的进行查询优化,但大家仍然有必要了解一下“查询优化器”的工作原理,如非这样,有时查询优化器就会不按照您的本意进行快速查询。

    字段名 数据类型,

在查询分析阶段,查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数(SARG),那么就称之为可优化的,并且可以利用索引快速获得所需数据。

    字段名 数据类型,

SARG的定义:用于限制搜索的一个操作,因为它通常是指一个特定的匹配,一个值得范围内的匹配或者两个以上条件的AND连接。形式如下:

    … …

列名 操作符 <常数 或
变量>或<常数 或 变量> 操作符列名

    );

列名可以出现在操作符的一边,而常数或变量出现在操作符的另一边。如:

  • 查看创建表的语句(字符集)

Name=’张三’

    show create table tablename;

价格>5000

  • 查看表结构

5000<价格

    desc tablename;

Name=’张三’ and 价格>5000

  • 删除表

如果一个表达式不能满足SARG的形式,那它就无法限制搜索的范围了,也就是SQL
SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。

    drop table tablename;

介绍完SARG后,我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验:

(3)表记录管理

1、Like语句是否属于SARG取决于所使用的通配符的类型

  • 在表中插入记录

如:name like ‘张%’
,这就属于SARG

    insert into tablename values(值1),(值2),…,(值N);

而:name like ‘%张’
,就不属于SARG。

  • 查询表记录

原因是通配符%在字符串的开通使得索引无法使用。

    select * from tablename;

2、or 会引起全表扫描

    select 字段名1,字段名2,…,字段名N from tablename;

Name=’张三’ and 价格>5000 符号SARG,而:Name=’张三’ or 价格>5000
则不符合SARG。使用or会引起全表扫描。

(4)表字段的操作

3、非操作符、函数引起的不满足SARG形式的语句

    语法:

不满足SARG形式的语句最典型的情况就是包括非操作符的语句,如:NOT、!=、<>、!<、!>、NOT
EXISTS、NOT IN、NOT
LIKE等,另外还有函数。下面就是几个不满足SARG形式的例子:

      alter table tablename 执行动作;

ABS(价格)<5000

  • 添加字段
    •   alter table tablename add 字段名 数据类型 first | after
      字段名;
  • 删除字段
    •   alter table tablename drop 字段名;
  • 修改字段数据类型
    •   alter table tablename modify 字段名 新数据类型;

Name like ‘%三’

      注: 修改数据类型时,会受到表中原有数据的限制!

有些表达式,如:

  • 修改字段名
    •   alter table tablename change 旧名 新名 数据类型;
  • 修改表明
    •   alter table tablename rename 新表明;

WHERE 价格*2>5000

(5)表记录的管理

SQL SERVER也会认为是SARG,SQL SERVER会将此式转化为:

  1. 删除表记录
    1.   delete from tablename where 条件;
  2. 更新表记录
    1.   update tablename set 字段名1=值1, 字段名2=值2,… where
      条件;

WHERE 价格>2500/2

(6)一些特殊的匹配方式

但我们不推荐这样使用,因为有时SQL
SERVER不能保证这种转化与原始表达式是完全等价的。

  1. 模糊匹配
    1.   where 字段名 like 表达式;
    2.   表达式
      1.   _   :  匹配单个字符
      2.   %  : 匹配 0 到多个字符
  2. 正则匹配 regexp
    1.   where 字段名 regexp “正则表达式”;
    2.   正则表达式符号
      1.   ^ : 以…开头
      2.   $: 以…结尾
      3.   . : 匹配任意一个字符
      4.   []: 包含…内容
        1.         [0-9]:匹配带数字的
        2.         [a-z]:匹配带小写字母的
        3.         [A-Z]
      5.   * : 星号前面的字符出现 0 次或者多次

4、IN 的作用相当与OR

语句:

Select * from table1 where tid in (2,3)和Select * from table1 where
tid=2 or tid=3

是一样的,都会引起全表扫描,如果tid上有索引,其索引也会失效。

5、尽量少用NOT

6、exists 和 in 的执行效率是一样的

很多资料上都显示说,exists要比in的执行效率要高,同时应尽可能的用not
exists来代替not
in。但事实上,我试验了一下,发现二者无论是前面带不带not,二者之间的执行效率都是一样的。因为涉及子查询,我们试验这次用SQL
SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics
I/O状态打开:

发表评论

电子邮件地址不会被公开。 必填项已用*标注

标签:
网站地图xml地图