首页 > 数据库 > sysbench oltp 执行了哪些操作

sysbench oltp 执行了哪些操作

2014年6月15日 3,361 views 发表评论 阅读评论

sysbench oltp 执行了哪些操作

sysbench执行测试的时候的行为我们需了解,我打开gnerel日志验证了下。

 

 

1.oltp

初始化数据,如:

CREATE TABLE sbtest8 (

id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

k INTEGER UNSIGNED DEFAULT ‘0’ NOT NULL,

c CHAR(120) DEFAULT ” NOT NULL,

pad CHAR(60) DEFAULT ” NOT NULL,

PRIMARY KEY (id)

) /*! ENGINE = innodb MAX_ROWS = 1000000 */

9828 Query CREATE INDEX k_8 on sbtest8(k)

进行各种查询,如下,

9830 Query BEGIN

9830 Query SELECT c FROM sbtest1 WHERE id=4969    基于主键查询

9830 Query SELECT c FROM sbtest1 WHERE id=5320

9830 Query SELECT c FROM sbtest1 WHERE id=5792

9830 Query SELECT c FROM sbtest1 WHERE id=4973

9830 Query SELECT c FROM sbtest1 WHERE id=6380

9830 Query SELECT c FROM sbtest1 WHERE id=4522

9830 Query SELECT c FROM sbtest1 WHERE id=4503

9830 Query SELECT c FROM sbtest1 WHERE id=4824

9830 Query SELECT c FROM sbtest1 WHERE id=5048

9830 Query SELECT c FROM sbtest1 WHERE id=4984

9830 Query SELECT c FROM sbtest1 WHERE id BETWEEN 4977 AND 4977+99 主键范围查找

9830 Query SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN 4951 AND 4951+99 主键范围查找+聚合函数

9830 Query SELECT c FROM sbtest1 WHERE id BETWEEN 5018 AND 5018+99 ORDER BY c 主键范围查找+文件排序(filesort)

9830 Query SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN 4992 AND 4992+99 ORDER BY c 主键范围查找+临时表+文件排序

9830 Query UPDATE sbtest1 SET k=k+1 WHERE id=5028  主键查找

9830 Query UPDATE sbtest1 SET c=’15161106334-50535565977-63188288836-92351140030-06390587585-66802097351-49282961843-49596942957-6205879

2596-92020240819′ WHERE id=5050 主键查找;

9830 Query DELETE FROM sbtest1 WHERE id=4987 主键查找

9830 Query INSERT INTO sbtest1 (id, k, c, pad) VALUES (4987, 4967, ‘76022818191-82933803603-81845875017-31928300264-16934042125-67052432

228-92123768050-95121478647-79362588344-09017007031’, ‘35411714211-52054317597-26283585383-48610978532-72166636310’)

 

commit

 

 

2. insert

创建了表

CREATE TABLE `sbtest1` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`k` int(10) unsigned NOT NULL DEFAULT ‘0’,

`c` char(120) NOT NULL DEFAULT ”,

`pad` char(60) NOT NULL DEFAULT ”,

PRIMARY KEY (`id`),

KEY `k_1` (`k`)

) ENGINE=InnoDB AUTO_INCREMENT=22518 DEFAULT CHARSET=utf8 MAX_ROWS=1000000

执行如下insert语句测试insert性能.

INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 4969, ‘56669196388-62491235116-66222720146-64857397964-64622616377-50575939475 -47009471341-09736071715-16110633450-53556597763’, ‘18828883692-35114003006-39058758566-80209735149-28296184349′)

 

 

3. delete

初始化表后,会执行.

DELETE FROM sbtest1 WHERE id=4969;  基于主键查找记录删除

 

4. select

初始化表后,会执行.

SELECT pad FROM sbtest4 WHERE id=4968  基于主键查找记录

 

5. update_index

初始化表后,会执行.

UPDATE sbtest4 SET k=k+1 WHERE id=5357 基于主键查找记录更新索引列

 

6. update_non_index

初始化表后,执行

UPDATE sbtest6 SET c=’17483045850-67050264653-66791204294-39297494132-11072044327-55270711521-78553937287-38020368822-95321773683-44023158409’ WHERE id=4974

基于主键查询,更新非索引列

 

7.select_random_ranges

CREATE TABLE sbtest (

id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,

k INTEGER UNSIGNED DEFAULT ‘0’ NOT NULL,

c CHAR(120) DEFAULT ” NOT NULL,

pad CHAR(60) DEFAULT ” NOT NULL,

PRIMARY KEY (id)

) /*! ENGINE = innodb MAX_ROWS = 1000000 */

CREATE INDEX k on sbtest(k)

执行以下查询.

SELECT count(k)

FROM sbtest

WHERE k BETWEEN 4971 AND 4976 OR k BETWEEN 5027 AND 5032 OR k BETWEEN 4955 AND 4960

OR k BETWEEN 6013 AND 6018 OR k BETWEEN 4995 AND 5000 OR k BETWEEN 5047 AND 5052 OR k BETWEEN 6345 AND 6350

OR k BETWEEN 5029 AND 5034 OR k BETWEEN 4981 AND 4986 OR k BETWEEN 5004 AND 5009

覆盖索引范围查找

+—-+————-+——–+——-+—————+——+———+——+——+————————–+

| id | select_type | table  | type | possible_keys | key  | key_len | ref | rows | Extra |

+—-+————-+——–+——-+—————+——+———+——+——+————————–+

| 1  | SIMPLE     | sbtest | range | k             | k   | 4       | NULL | 55   | Using where; Using index |

 

 

 

8.select_random_points

初始化表后.

执行.select_random_points

SELECT id, k, c, pad

FROM sbtest

WHERE k IN (4953, 5007, 4999, 4953, 5033, 4988, 4998, 4960, 5030, 5028)

查询计划: 索引范围查找

+—-+————-+——–+——-+—————+——+———+——+——+————-+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+—-+————-+——–+——-+—————+——+———+——+——+————-+

| 1 | SIMPLE | sbtest | range | k | k | 4 | NULL | 9 | Using where |

+—-+————-+——–+——-+—————+——+———+——+——+————-+

 

 

 » 如果喜欢可以: 点此订阅本站
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.