事务

事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。

图片 1图片 2

 1 delimiter \\
 2 create PROCEDURE p1(
 3     OUT p_return_code tinyint
 4 )
 5 BEGIN 
 6   DECLARE exit handler for sqlexception 
 7   BEGIN 
 8     -- ERROR 
 9     set p_return_code = 1; 
10     rollback; 
11   END; 
12  
13   DECLARE exit handler for sqlwarning 
14   BEGIN 
15     -- WARNING 
16     set p_return_code = 2; 
17     rollback; 
18   END; 
19  
20   START TRANSACTION; 
21     DELETE from tb1;
22     insert into tb2(name)values('seven');
23   COMMIT; 
24  
25   -- SUCCESS 
26   set p_return_code = 0; 
27  
28   END\\
29 delimiter ;

支持事务的存储过程

1 set @i =0;
2 call p1(@i);
3 select @i;

事务:innodb支持事务,事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。

 

一、函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数,可以简化用户的操作。

1.事务的开始和结束名称需要相同

函数

MySQL中提供了许多内置函数,例如:

图片 3图片 4

 1 CHAR_LENGTH(str)
 2         返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
 3         对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
 4 
 5     CONCAT(str1,str2,...)
 6         字符串拼接
 7         如有任何一个参数为NULL ,则返回值为 NULL。
 8     CONCAT_WS(separator,str1,str2,...)
 9         字符串拼接(自定义连接符)
10         CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
11 
12     CONV(N,from_base,to_base)
13         进制转换
14         例如:
15             SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
16 
17     FORMAT(X,D)
18         将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点,或不含小数部分。
19         例如:
20             SELECT FORMAT(12332.1,4); 结果为: '12,332.1000'
21     INSERT(str,pos,len,newstr)
22         在str的指定位置插入字符串
23             pos:要替换位置其实位置
24             len:替换的长度
25             newstr:新字符串
26         特别的:
27             如果pos超过原字符串长度,则返回原字符串
28             如果len超过原字符串长度,则由新字符串完全替换
29     INSTR(str,substr)
30         返回字符串 str 中子字符串的第一个出现位置。
31 
32     LEFT(str,len)
33         返回字符串str 从开始的len位置的子序列字符。
34 
35     LOWER(str)
36         变小写
37 
38     UPPER(str)
39         变大写
40 
41     LTRIM(str)
42         返回字符串 str ,其引导空格字符被删除。
43     RTRIM(str)
44         返回字符串 str ,结尾空格字符被删去。
45     SUBSTRING(str,pos,len)
46         获取字符串子序列
47 
48     LOCATE(substr,str,pos)
49         获取子序列索引位置
50 
51     REPEAT(str,count)
52         返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
53         若 count <= 0,则返回一个空字符串。
54         若str 或 count 为 NULL,则返回 NULL 。
55     REPLACE(str,from_str,to_str)
56         返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
57     REVERSE(str)
58         返回字符串 str ,顺序和字符顺序相反。
59     RIGHT(str,len)
60         从字符串str 开始,返回从后边开始len个字符组成的子序列
61 
62     SPACE(N)
63         返回一个由N空格组成的字符串。
64 
65     SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
66         不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
67 
68         mysql> SELECT SUBSTRING('Quadratically',5);
69             -> 'ratically'
70 
71         mysql> SELECT SUBSTRING('foobarbar' FROM 4);
72             -> 'barbar'
73 
74         mysql> SELECT SUBSTRING('Quadratically',5,6);
75             -> 'ratica'
76 
77         mysql> SELECT SUBSTRING('Sakila', -3);
78             -> 'ila'
79 
80         mysql> SELECT SUBSTRING('Sakila', -5, 3);
81             -> 'aki'
82 
83         mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
84             -> 'ki'
85 
86     TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
87         返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。
88 
89         mysql> SELECT TRIM('  bar   ');
90                 -> 'bar'
91 
92         mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
93                 -> 'barxxx'
94 
95         mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
96                 -> 'bar'
97 
98         mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
99                 -> 'barx'

部分内置函数

1、自定义函数

图片 5图片 6

 1 delimiter \\
 2 create function f1(
 3     i1 int,
 4     i2 int)
 5 returns int
 6 BEGIN
 7     declare num int;
 8     set num = i1 + i2;
 9     return(num);
10 END \\
11 delimiter ;

View
Code

2、删除函数

1 drop function func_name;

3、执行函数

图片 7图片 8

1 # 获取返回值
2 declare @i VARCHAR(32);
3 select UPPER('alex') into @i;
4 SELECT @i;
5 
6 
7 # 在查询中使用
8 select f1(11,nid) ,name from tb2;

View
Code

 

MySQL内置函数:
十六进制:0-9对应0-9;A-F对应10-15;

 

(一)数学函数
函数 功能
ABS(x) 求绝对值
PI() 返回圆周率
SQRT(x) 平方根函数
MOD(x,y) 求余函数
RAND()和RAND(x) 获取随机数的函数
ROUND(x)、ROUND(x,y)和TRUNCATE(x,y) 四舍五入函数
SIGN(x) 符号函数
POW(x,y)、POWER(x,y)和EXP(x) 幂运算函数
LOG(x)和LOG10(x) 对数运算函数

lr_start_transaction(“transaction_name”);

CHAR_LENGTH(str)
        返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
        对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

mysql> select char_length('mudy');
+---------------------+
| char_length('mudy') |
+---------------------+
|                   4 |
+---------------------+
1 row in set (0.22 sec)

CONCAT(str1,str2,...)
        字符串拼接
        如有任何一个参数为NULL ,则返回值为 NULL。

mysql> select concat('mudy','huyue');
+------------------------+
| concat('mudy','huyue') |
+------------------------+
| mudyhuyue              |
+------------------------+
1 row in set (0.04 sec)

CONCAT_WS(separator,str1,str2,...)
        字符串拼接(自定义连接符)
        CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。

mysql> select concat_ws('-','mudy','huyue');
+-------------------------------+
| concat_ws('-','mudy','huyue') |
+-------------------------------+
| mudy-huyue                    |
+-------------------------------+
1 row in set (0.00 sec)

CONV(N,from_base,to_base)
        进制转换

将16进制的10转换成2进制的
mysql> select conv('10',16,2);
+-----------------+
| conv('10',16,2) |
+-----------------+
| 10000           |
+-----------------+
1 row in set (0.00 sec)


mysql> select conv('9',16,2);
+----------------+
| conv('9',16,2) |
+----------------+
| 1001           |
+----------------+
1 row in set (0.00 sec)


mysql> select conv('a',16,2);
+----------------+
| conv('a',16,2) |
+----------------+
| 1010           |
+----------------+
1 row in set (0.00 sec)

将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 
并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点,或不含小数部分。
mysql> select format(10000000000,3);
+-----------------------+
| format(10000000000,3) |
+-----------------------+
| 10,000,000,000.000    |
+-----------------------+
1 row in set (0.09 sec)

INSERT(str,pos,len,newstr)
        在str的指定位置插入字符串
            pos:要替换位置其实位置
            len:替换的长度
            newstr:新字符串
        特别的:
            如果pos超过原字符串长度,则返回原字符串
            如果len超过原字符串长度,则由新字符串完全替换


mysql> select insert('mudy',5,1,'good');
+---------------------------+
| insert('mudy',5,1,'good') |
+---------------------------+
| mudy                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select insert('mudy',1,2,'good');
+---------------------------+
| insert('mudy',1,2,'good') |
+---------------------------+
| gooddy                    |
+---------------------------+
1 row in set (0.07 sec)

mysql> select insert('mudy',1,5,'good');
+---------------------------+
| insert('mudy',1,5,'good') |
+---------------------------+
| good                      |
+---------------------------+
1 row in set (0.00 sec)

INSTR(str,substr)
        返回字符串 str 中子字符串的第一个出现位置。

mysql> select instr('mudy','y');
+-------------------+
| instr('mudy','y') |
+-------------------+
|                 4 |
+-------------------+
1 row in set (0.05 sec)

LEFT(str,len)
        返回字符串str 从开始的len位置的子序列字符。

mysql> select left('mudy',2);
+----------------+
| left('mudy',2) |
+----------------+
| mu             |
+----------------+
1 row in set (0.07 sec)

LOWER(str)
        变小写

UPPER(str)
        变大写

LOCATE(substr,str,pos)
        获取子序列索引位置

第三个参数表示起始位置
mysql> select locate('dy','mudymudy',1);
+---------------------------+
| locate('dy','mudymudy',1) |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.04 sec)

mysql> select locate('dy','mudymudy',4);
+---------------------------+
| locate('dy','mudymudy',4) |
+---------------------------+
|                         7 |
+---------------------------+
1 row in set (0.00 sec)

REPEAT(str,count)
        返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
        若 count <= 0,则返回一个空字符串。
        若str 或 count 为 NULL,则返回 NULL 。

mysql> select repeat('mudy',2);
+------------------+
| repeat('mudy',2) |
+------------------+
| mudymudy         |
+------------------+
1 row in set (0.04 sec)

SPACE(N)
        返回一个由N空格组成的字符串。

mysql> select space(4)
    -> ;
+----------+
| space(4) |
+----------+
|          |
+----------+
1 row in set (0.04 sec)

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
        不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 
        pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串
        ,起始于位置 pos。 使用 FROM的格式为标准 SQL 
        语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串
        结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 
        使用一个负值。


mysql> select substring('imaumudy' from 4 for 3);
+------------------------------------+
| substring('imaumudy' from 4 for 3) |
+------------------------------------+
| umu                                |
+------------------------------------+
1 row in set (0.06 sec)

mysql> select substring('imaumudy',2,3);
+---------------------------+
| substring('imaumudy',2,3) |
+---------------------------+
| mau                       |
+---------------------------+
1 row in set (0.00 sec)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
        返回字符串 str , 其中所有remstr 
        前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给
        定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。


mysql> select trim('    mudy     ');
+-----------------------+
| trim('    mudy     ') |
+-----------------------+
| mudy                  |
+-----------------------+
1 row in set (0.00 sec)


mysql> select trim(leading 's' from 'ssssssmudysssssss');
+--------------------------------------------+
| trim(leading 's' from 'ssssssmudysssssss') |
+--------------------------------------------+
| mudysssssss                                |
+--------------------------------------------+
1 row in set (0.00 sec)


mysql> select trim(trailing 's' from 'ssssssmudysssssss');
+---------------------------------------------+
| trim(trailing 's' from 'ssssssmudysssssss') |
+---------------------------------------------+
| ssssssmudy                                  |
+---------------------------------------------+
1 row in set (0.00 sec)

–事務XML控制

(二)字符串函数
函数 功能
CHAR_LENGTH(s) 计算字符串s的字符数
LENGTH(s) 计算字符串s的长度
CONCAT(s1,s2,…) 将s1、s2等多个字符串合并成一个字符串
CONCAT_WS(x,s1,s2,…) 将s1、s2等多个字符串合并成一个字符串,但可将各字符串直接用参数x隔开
UPPER(s)和UCASE(s) 将字符串s的所有字母变成大写字母
LOWER(s)和LCASE(s) 将字符串s的所有字母变成小写字母
REPEAT(s,n) 将字符串s重复n次
STRCMP(s1,s2) 比较字符串s1和s2。如果s1大于s2,结果返回1;如果s1等于s2,结果返回0;如果s1小于s2,结果返回-1。
REVERSE(s) 将字符串s的顺序反过来
SUBSTRING(s,a,b) 从字符串s中的第a个字符开始取b个字符

…//事务处理

自定义函数:
注意在函数中不可以写sql语句,可以有返回值,但是没有in out inout
支持==select nid into a from student where name = ‘mudy’;==

 

(三)日期和时间函数
函数 功能
CURDATE()和CURRENT_DATE() 获取当前日期
CURTIME()和CURRENT_TIME() 获取当前时间
NOW()、CURRENT_TIMESTAMP()、LOCALTIME()和SYSDATE() 获取当前的日期和时间
UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(d) 将时间d以UNIX时间戳的形式返回
FROM_UNIXTIME(d) 把UNIX时间戳的时间转换为普通格式的时间
MONTH(d) 返回日期d中的月份值,其取值范围是1~12
MONTHNAME(d) 返回日期d中的月份的英文名称
YEAR(d) 返回日期d中的年份值
QUARTER(d) 返回日期d是本年第几季度,值的范围是1~4
HOUR(t) 返回时间t中的小时值
MINUTE(t) 返回时间t中的分钟值
SECOND(t) 返回时间t中的秒钟值

lr_end_transaction(“transaction_name”,LR_AUTO);

delimiter \\
CREATE FUNCTION f1(i1 int,i2 int)
RETURNS int

BEGIN
    DECLARE num int;
    set num = i1 + i2;
    RETURN(num);
END\\

delimiter ;


mysql> select f1(3,4);
+---------+
| f1(3,4) |
+---------+
|       7 |
+---------+
1 row in set (0.07 sec)

 

(四)条件判断函数
函数 功能
IF(expr,v1,v2) 如果表达式expr成立,返回结果v1;否则,返回结果v2。
IFNULL(v1,v2) 如果v1的不为空,就显示v1的值;否则就显示v2的值。

 

  • 索引的功能:

    • 约束

      • 主键
      • 外键
      • 唯一
      • 普通
      • 组合
    • 加速查找

— 用事务/XML实现函数效果

(五)系统信息函数
函数 功能
VERSION() 返回数据库的版本号
CONNECTION_ID() 返回服务器的连接数,也就是到现在为止MySQL服务的连接次数
DATABASE()和SCHEMA() 返回当前数据库名
USER()、SYSTEM_USER()、SESSION_USER()、CURRENT_USER()和CURRENT_USER 返回当前用户的名称
LAST_INSERT_ID() 返回最后生成的AUTO_INCREMENT值

2.事务和子事务

索引的生成:算法是B-tree

 

(六)加密函数
函数 功能
PASSWORD(str) 可以对字符串str进行加密
MD5(str) 可以对字符串str进行加密
DECODE(crypt_str,pswd_str) 可以使用字符串pswd_str来为crypt_str解密

在VuGen中可以通过事务来完成一组操作的响应时间监控,如果想监控某一个事务中某一步操作的响应时间,就要使用子事务来完成。当然也可以使用事务嵌套

  • 索引的种类:
    • 普通索引-加速查找
    • 唯一索引-加速查找,约束列数据不能重复,可以为null
    • 主键索引-加速查找,约束列数据不能重复,不能为null
    • 组合索引-多列可以创建一个索引文件

create table ta( id int
, name varchar (
50))

二、自定义函数

lr_start_sub_transaction(“子事务名”, “父事务名”);


insert ta

(一)语法
Create function function_name(参数列表)returns返回值类型

lr_end_sub_transaction(“子事务名”, 子事务状态);

1、普通索引的创建

select 1, ‘a’ union
all

(二)叙述
  • 函数体、函数名,应合法的标识符,不应与系统关键字冲突。
  • 一个函数应该属于某个数据库,可以使用db_name.funciton_name的形式执行当前函数所属数据库,否则为当前数据库。
  • 参数部分,由参数名和参数类型组成。
  • 返回值类类型。注意,一定要有return返回值语句。

注意:子事务和父事务很像,但是父事务支持的很多函数在子事务中都无法实现,所以应酌情考虑。

  • 创建表的同时创建索引

select 1, ‘b’ union
all

(三)举例
DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `ZZZ1`(PARA_VAR varchar(30)) 
RETURNS varchar(100) CHARSET latin1
BEGIN
DECLARE NAYIBU_FIR VARCHAR(30) DEFAULT 'THIS IS MY FIRST,';
SET NAYIBU_FIR = concat(NAYIBU_FIR,PARA_VAR);
RETURN NAYIBU_FIR;
END$$

 

select 1, ‘a’ union
all

(四)查看自定函数
show function status;

例:做一个登录的事务,同时把打开登录页面和登录操作都做成一个事务,这样就可以得到三个事务的时间:打开页面和登录操作及整个操作的时间。

create table student(
    ......
    index ix_name(name)
)

select 2, ‘e’ union
all

三、事务

应用中的一个业务逻辑,往往由多条语句组合完成。那么我们就可以简单地将事务理解为一组SQL语句的集合,要么这个集合全部成功集合,要么这个集合就全部失败退回到第一句之前的状态。

Action()

  • 添加索引

select 2, ‘f’ union
all

(一)语法
  1. 开启事务start transaction,可以简写为 begin
  2. 然后记录之后需要执行的一组sql
  3. 提交commit
  4. 如果所有的sql都执行成功,则提交,将sql的执行结果持久化到数据表内。
  5. 回滚rollback
  6. 如果存在失败的sql,则需要回滚,将sql的执行结果,退回到事务开始之时。
  7. 无论回滚还是提交,都会关闭事务!需要再次开启,才能使用。
  8. 还有一点需要注意,就是事务只针对当前连接。

{

select 3, ‘g’ union
all

(二)举例

使用第一个链接A

Start transaction;
Insert into students values('qreal',5);

结果成功,数据已经变成修改之后!但此时我们没有提交。再从其他连接B来查看,发现数据没有插入。此时如果连接A选择提交,也就是commit操作。则连接B的数据也会发生变化。而如果连接A选择回滚,也就是rollback操作。则连接A再次查询则发现数据还原。

    //开始登录事务

create index index_name on table_name(column_name)
mysql> create index nameindex on course(cname);
Query OK, 0 rows affected (0.80 sec)
Records: 0  Duplicates: 0  Warnings: 0

select 3, ‘h’ union
all

(三)基本原理

提交,就会将结果持久化,不提交就不会。如果我们不开启事务,只执行一条sql,马上就会持久化数据,可以看出,普通的执行就是立即提交。这是因为MySQL默认对sql语句的执行是自动提交的。也就是说,开启事务,实际上就是关闭了自动提交的功能,改成了commit手动提交!
我们可以通过简单的对是否自动提交加以设置,完成开启事务的目的!
自动提交的特征是保存在服务的一个autocommit的变量内。可以进行修改:

Set autocommit = 0;

还需要注意一点,就是事务类似于外键约束,只被innodb引擎支持。

    lr_start_transaction(“login”);

创建索引的代价:增加 删除 更新 都慢了

select 3, ‘i’ union
all

(四)事务的特点

事务的特点ACID。也就是原子性、一致性、隔离性和持久性。

  • 原子性:事务是不可分割的。
  • 一致性:保证数据在事务的执行周期内,是一致的!
  • 隔离型:多个事务之间的干扰关系!隔离级别!
  • 持久性:事务一旦被提交,就不可能再被回滚!

   

  • 删除索引

select 3, ‘j’ union
all

    //打开登录界面

select 4, ‘k’ union
all

    lr_start_sub_transaction(“loginpage”, “login”);   

drop index_name on tablename

select 4, ‘m’ union
all

    web_url(“WebTours”,

  • 查看索引

select 4, ‘l’ union
all

        “URL=”,

select 5, ‘A’ union
all

        “Resource=0”,

show index from table_name
mysql> show index from course;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| course |          0 | PRIMARY   |            1 | cid         | A         |          12 |     NULL | NULL   |      | BTREE      |         |               |
| course |          1 | nameindex |            1 | cname       | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

select 5, ‘B’ union
all

        “RecContentType=text/html”,

2、唯一索引

select 5, ‘C’ union
all

        “Referer=”,

create unique index index_name on table_name(column_name)

select 5, ‘D’ union
all

        “Snapshot=t16.inf”,

3、主键索引

select 5, ‘E’ union
all

        “Mode=HTML”,

创建一个主键列就行了

select 6, ‘F’ union
all

        LAST);

4、组合索引

select 6, ‘G’ union
all

    lr_end_sub_transaction(“loginpage”, LR_AUTO);

#普通组合索引:无约束
create table student(
    ......
    index ix_name(name,age)
)

#联合唯一索引:有约束,两列数据同时不相同才能插入,不然报错
create unique index index_name on table_name(column_name,column_name2)

select 6, ‘H’ union
all

    lr_think_time(4);

组合索引在查找的时候,遵循==最左匹配==的原则

select 6, ‘K’ union
all

 

select * from student where name = 'mudy'#会走索引
select * from student where name = 'mudy' and age = 12#会走索引
select * from student where age = 12 #不会走索引

select 6, ‘M’

    //提交登录页面的表单


 

    lr_start_sub_transaction(“submit”, “login”);

1、覆盖索引

— 合并效果

    web_submit_form(“login.pl”,

select * from tb where nid = 1
# 这种是先去索引中找,再去数据中找

select nid from tb where nid < 10
# 先去索引中找

#zhezho难过情况,只需要在索引表中就能获取到数据时,称为覆盖索引

/*

        “Snapshot=t17.inf”,

2、合并索引

ID          Name

        ITEMDATA,

name 与 age都是单独的索引

select * from student where name = 'mudy'
select * from student where name = 'mudy' or age = 15

        “Name=username”, “Value=jojo”, ENDITEM,

执行计划-想对比较准确的表达出当前SQLyun行状况
是否走索引,走索引的效率高

1           a ,b,a

        “Name=password”, “Value=bean”, ENDITEM,

explain SQL语句
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.11 sec)

type:all 表示会对整个的数据表进行全表扫描
type:index 表示对全索引扫描
all与index的效率一般不高,都是有优化的余地

2、limit

3、range
对于索引进行范围查找的时候,会执行range
mysql> explain select * from student where sid<2;
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)


注意:如果是>或!=的话,就是全表扫描了,不走索引,type:all


mysql> explain select * from course where cname='e' or cid='1';
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table  | partitions | type        | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                                       |
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | course | NULL       | index_merge | PRIMARY,nameindex | nameindex,PRIMARY | 33,4    | NULL |    2 |   100.00 | Using union(nameindex,PRIMARY); Using where |
+----+-------------+--------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.12 sec)

mysql> explain select * from course where cid='1';
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | course | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from course where cname='生物';
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | course | NULL       | ref  | nameindex     | nameindex | 33      | const |    2 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.09 sec)

index_merge:表示索引合并了,possible_keys表示有可能的索引

2           e,f

        “Name=login.x”, “Value=0”, ENDITEM,

type的可能值:

3            g ,h,i,j

        “Name=login.y”, “Value=0”, ENDITEM,

查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL    全表扫描,对于数据表从头到尾找一遍
       select * from tb1;
       特别的:如果有limit限制,则找到之后就不在继续向下扫描
              select * from tb1 where email = 'seven@live.com'
              select * from tb1 where email = 'seven@live.com' limit 1;
              虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。

INDEX           全索引扫描,对索引从头到尾找一遍
                select nid from tb1;

RANGE          对索引列进行范围查找
        select *  from tb1 where name < 'alex';
        PS:
            between and
            in
            >   >=  <   <=  操作
            注意:!= 和 > 符号


INDEX_MERGE     合并索引,使用多个单列索引搜索
                select *  from tb1 where name = 'alex' or nid in (11,22,33);

REF             根据索引查找一个或多个值
                select *  from tb1 where name = 'seven';

EQ_REF          连接时使用primary key 或 unique类型
                select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;



CONST           常量
                表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
                select nid from tb1 where nid = 2 ;

SYSTEM          系统
                表仅有一行(=系统表)。这是const联接类型的一个特例。
                select * from (select nid from tb1 where nid = 1) as A;

4           k,m,l

        LAST);

参考type row,查看执行效率

5           A ,B,C,D,E

    lr_end_sub_transaction(“submit”, LR_AUTO);

如何命中索引

6           F ,G,H,K,M

 

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的:当or条件中有未建立索引的列才失效,以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型,传入条件是必须用引号引起来,不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的:如果是主键,则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的:如果是主键或索引是整数类型,则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123



- order by
    select email from tb1 order by name desc;
    当根据索引排序时候,选择的映射如果不是索引,则不走索引
    特别的:如果对主键排序,则还是走索引:
        select * from tb1 order by nid desc;

- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

 

    lr_end_transaction(“login”, LR_AUTO);

int类型的!=

(6 個資料列受到影響
)

}

mysql> explain select * from student where class_id != 2;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | classindex    | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)

mysql> explain select * from student where class_id > 2;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | classindex    | classindex | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

mysql> explain select * from student where class_id < 2;
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | classindex    | classindex | 5       | NULL |    2 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

 

 

面试必备

MySQL翻页

第一页 where nid > 16 limit 10

慢日志

ysql> show variables like '%query%';
+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| binlog_rows_query_log_events | OFF                                  |
| ft_query_expansion_limit     | 20                                   |
| have_query_cache             | YES                                  |
| long_query_time              | 10.000000                            |
| query_alloc_block_size       | 8192                                 |
| query_cache_limit            | 1048576                              |
| query_cache_min_res_unit     | 4096                                 |
| query_cache_size             | 1048576                              |
| query_cache_type             | OFF                                  |
| query_cache_wlock_invalidate | OFF                                  |
| query_prealloc_size          | 8192                                 |
| slow_query_log               | OFF                                  |
| slow_query_log_file          | /usr/local/mysql/data/liuna-slow.log |
+------------------------------+--------------------------------------+
13 rows in set (0.30 sec)

*/

运行结果:

 

图片 9

— 方法
1( 不用函数实现更新、查询
) 如下用于几列合并一列方法
1 比方法
2 效率高

 

 

3.事务状态

declare @tb table ( id int
, name varchar (
50), con int identity (
1, 1))

在默认情况下使用LR_AUTO来作为事务状态,对于一个事务有以下4个状态可以选择。

insert @tb

1) LR_AUTO

select * from ta

指事务的状态由系统自动根据默认规则来判断,结果为PASS/FAIL/STOP

 

LR_AUTO也是根据服务器的返回状态信息来决定事务是以LR_PASS状态通过还是以LR_FAIL状态结束,只要服务器返回页面,那么事务就会认为请求成功发出去了,服务器看懂了请求也返回了内容,自然事务就是PASS状态。

begin tran

这样由于事务的自动判断,导致虽然操作失败的,但是得到了一个响应时间,并且这个响应时间又没正确反映出做这件事情的真正时间,最终影响测试结果。添加检测函数有时候可以解决这个问题

while exists( select 1 from @tb)

 

    begin

例:判断是否登录成功(以lr自带web站点为例)

        update a

图片 10

        set a. name=
a. name+ ‘,’ + b.
name

Action2()

        from ta a , @tb b

{

        where a. id=
b. id  and

    lr_start_transaction(“login”);

        not exists( select *
from @tb where id=
b. id  and con< b.
con )

 

 

    //打开登录界面

        delete b

    web_url(“WebTours”,

        from @tb b where not
exists( select 1 from @tb where id=
b. id  and con< b.
con)

        “URL=”,

    end

        “Resource=0”,

 

        “RecContentType=text/html”,

select distinct id, 显示 = stuff ( name , 1, charindex ( ‘,’ , name
), ”
) from ta

        “Referer=”,

 

        “Snapshot=t16.inf”,

— 以下用于更新
commit tran 替换
rollback tran

        “Mode=HTML”,

–update ta set
name=stuff(name,1,charindex(‘,’,name),”)

        LAST);

–commit tran

 

 

    lr_think_time(4);

rollback tran

 

 

    //注意函数的位置

select * from ta

    //
jojo1为要查找的文本(实际文本如上图,jojo,仅在登录后才出现,这里为了演示故意找不存在的文本)

 

web_reg_find(“Search=Body”, “SaveCount=login_times”, “Text=jojo1”,
LAST);

 

 

— 方法 2( 通过创建函数实现查询更新、查询 )

    //提交登录页面的表单

create function ta_fun( @id int
)

    web_submit_form(“login.pl”,

returns varchar ( 1000)

        “Snapshot=t17.inf”,

as

        ITEMDATA,

begin

        “Name=username”, “Value=jojo”, ENDITEM,

    declare @sql varchar (
1000)

        “Name=password”, “Value=bean”, ENDITEM,

    set @sql= ”

        “Name=login.x”, “Value=0”, ENDITEM,

    select @sql= @sql+
‘,’ +
name from ta where id=
@id

        “Name=login.y”, “Value=0”, ENDITEM,

    –print @sql

        LAST);

    return stuff (
@sql, 1, 1, ” )

 

end

    lr_think_time(4);

 

   

select distinct id, 显示 = dbo. ta_fun( id)
from ta

    if(atoi(lr_eval_string(“{login_times}”)) >= 1)
{//找到“jojo”,说明登录成功

–select id, 显示
=dbo.ta_fun(id) from ta group by id

        lr_end_transaction(“login”, LR_PASS);}

— 以下用函数更新

    else{  //未找到,说明登录失败

update ta set name =
dbo. ta_fun( id)

    lr_end_transaction(“login”, LR_FAIL);} 

 

    return 0;

–drop table ta 删测试表

}

–drop function ta_fun  删测试函数

运行结果:

 

图片 11
 

— 以下用 SQL2005 实现方法

2) LR_PASS

 

指事务是以PASS状态通过的,说明改事务正确地完成了,并且记录下对应的时间,这个时间就是指做这件事情所需要消耗的时间。

–XML 方法 1

 

SELECT *

3) LR_FAIL

FROM ( SELECT DISTINCT id FROM ta )
A

LR_FAIL是指事务以FAIL状态结束,该事务是一个失败的事务,没有完成事务中脚本应该达到的效果,得到的时间不是正确操作的时间,这个时间在后期的统计中将被独立统计。

OUTER APPLY

 

    ( SELECT [name]= STUFF
( REPLACE ( REPLACE (( SELECT
name FROM ta N WHERE id =
A. id FOR XML
AUTO ), ‘<N
name=”‘ , ‘,’ ),
‘”/>’ , ” ), 1,
1, ”
)) N

4) LR_STOP

 

LR_STOP将事务以STOP状态停止。

–XML 方法
2

 

select

说明:

    ID, [Name]= stuff
(( select ‘,’

事务的PASS和FAIL状态会在场景的对应计数器中记录,包括通过的次数和事务的响应时间,方便后期分析改事务的吞吐量以及响应时间的变化情况

  • Name from
    Ta where ID= a. ID
    for xml path
    ( ”
    )), 1, 1, ” )

 

from Ta a

4.事务相关的函数

group by ID

//获得对应事务达到该函数运行位置时持续的时间,返回double类型

 

lr_get_transaction_duration(“事务名”);

 

 

//获得对应事务达到该函数运行位置时的wasted时间,返回double类型。

lr_get_transaction_wasted_time(“事务名”);

 

//为一个事务添加wasted时间,无返回值

lr_wasted_time(毫秒)

 

 

//将一个事务暂停,该函数后的操作都不会被记录事务时间

lr_stop_transaction(“事务名”);

 

Action2()

{

    double duration  = 0;

    double wastedtime = 0;

 

    lr_start_transaction(“login”);

    //打开登录界面

    web_url(“WebTours”,

        “URL=”,

        “Resource=0”,

        “RecContentType=text/html”,

        “Referer=”,

        “Snapshot=t16.inf”,

        “Mode=HTML”,

        LAST);

 

    lr_think_time(4);

 

    //事务达到该函数运行位置时持续的时间

    duration = lr_get_transaction_duration(“login”);

    lr_output_message(“duration %f”, duration);

 

    //事务达到该函数运行位置时浪费的时间

    wastedtime = lr_get_transaction_wasted_time(“login”);

    lr_output_message(“wastedtime %f”, wastedtime);

 

    //为事务添加一个浪费时间 5s//该浪费时间无法直接通过lr_get_transaction_wasted_time获得

    lr_wasted_time(5);

    wastedtime = lr_get_transaction_wasted_time(“login”);

  lr_output_message(“wastedtime %f”, wastedtime);

 

    //暂停事务

    lr_stop_transaction(“login”);

 

    //注意函数的位置

    web_reg_find(“Search=Body”, “SaveCount=login_times”, “Text=jojo”,
LAST);

 

    //提交登录页面的表单

    web_submit_form(“login.pl”,

        “Snapshot=t17.inf”,

        ITEMDATA,

        “Name=username”, “Value=jojo”, ENDITEM,

        “Name=password”, “Value=bean”, ENDITEM,

        “Name=login.x”, “Value=0”, ENDITEM,

        “Name=login.y”, “Value=0”, ENDITEM,

        LAST);

 

    lr_think_time(4);

 

    //事务达到该函数运行位置时浪费的时间

    wastedtime = lr_get_transaction_wasted_time(“login”);

    lr_output_message(“wastedtime %f”, wastedtime);

 

    if(atoi(lr_eval_string(“{login_times}”)) >= 1)

        lr_end_transaction(“login”, LR_PASS);

    else

        lr_end_transaction(“login”, LR_FAIL);

  

    return 0;

}

图片 12

 

注意:通过代码stop事务后可以重新通过lr_start_transaction函数恢复被暂停的事务

 

5.包含“资源下载”的事务处理

一般情况都可以十分简单地获得请求的响应时间,但是对于下载操作来说就并不是那么方便了,这个时候需要利用一个web_get_int_property()函数来解决。

Action()

{

int i;

lr_start_transaction(“download”);

web_url(“LoadRunner”,”RUL=);
//下载操作请求

i=web_get_int_property(HTTP_INFO_DOWNLOAD_SIZE);

if(i>5000) //当下载的文件大小大于5000个字节时认为下载成功,否则失败。

lr_end_transaction(“download”,LR_PASS);

else

lr_end_transaction(“download”,LR_FAIL);

}

一般来说,对于下载操作的性能测试集中在需要多少时间弹出下载对话框,而后面的内容其实属于网络带宽的问题了,另一个方面现在很少使用IE进行直接下载,而采用迅雷一类的p2p下载工具,所以很少考虑具体下载文件操作对网络和服务器读写操作的影响。

发表评论

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

网站地图xml地图