sysbench測試類型oltp 執行了哪些操作

02-04

MySQL壓測工具sysbench很流行，我們常用的是oltp測試，我們需了解執行測試的時候的具體做了哪些操作，我打開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

覆蓋索引範圍查找

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

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

| 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)

查詢計劃: 索引範圍查找

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

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

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

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