2014年5月22日星期四

MySQL的幻方吉普車:套路,循環,變量

Original post: http://anothermysqldba.blogspot.com/2014/05/mysql-magic-square-4x4-routines-loop.html

我得到了今天突發奇想用魔方玩,認為這可能是一個很好的機會,讓使用MySQL例程,循環和if檢查的一個例子。 

所以,如果你不知道的幻方是什麼我已經包含了幾個環節。 它可能會為您節省了谷歌搜索,但其他認為數獨是一個例子。 
同樣,這更多的是如何使用的例程和loops和中頻檢查的一個例子,所以我限制了幻方以一個4x4的方形。 
我傳遞的值4到例程來說明如何這樣做。 這是一個不必要的步驟,因為我是硬編碼的廣場上4,但無論如何,它的工作原理的例子。 

DROP PROCEDURE IF EXISTS magic_sq; 
DROP TABLE IF EXISTS `magic_sq` ; 

delimiter // 
CREATE PROCEDURE magic_sq( N int(11)) 
BEGIN 
DECLARE nXn INT; 
DECLARE SQ_SUM INT; 
DECLARE _passfail_ INT; 
DECLARE min INT; 
DECLARE max INT; 

-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW 
DROP TABLE IF EXISTS `magic_sq` ; 
CREATE TABLE `magic_sq` ( 
`xy_id` int(11) NOT NULL AUTO_INCREMENT, 
`1` int(11) NULL DEFAULT NULL, 
`2` int(11) NULL DEFAULT NULL, 
`3` int(11) NULL DEFAULT NULL, 
`4` int(11) NULL DEFAULT NULL, 
PRIMARY KEY (`xy_id`) , 
UNIQUE KEY `Y1` (`1`), 
UNIQUE KEY `Y2` (`2`), 
UNIQUE KEY `Y3` (`3`), 
UNIQUE KEY `Y4` (`4`) 
) ENGINE= MEMORY; 


-- n X n 
SET @nXn = N + N; 
-- SQ_SUM This is the formula for what the total should equal 
SET @SQ_SUM = ( N * (POW(N,2) + 1) ) / 2; 
-- MIN Value 
SET @min=1; 
-- MAX Value 
SET @max=POW(N,2); 

-- BUILD THE SQUARE 
WHILE ( @min <= @nXn ) 
DO 

-- TEST VALUES 
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 ) ; 

-- IF VALID RESULTS THEN SAVE THEM 
IF _passfail_ = 1 THEN 
INSERT INTO magic_sq VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
END IF; 

-- CONTINUE 
SET @min= @min +2; 
SET @max= @max -2; 

END WHILE; 

END// 
delimiter ; 


現在,我建立了常規,我能得到有效的結果? 請記住,這是不是所有可能的選項,這可能會奏效。只是選擇924選擇一個4x4正方形存在。 

CALL magic_sq(4); 
select * from magic_sq; 
+-------+------+------+------+------+ 
| xy_id | 1 | 2 | 3 | 4 | 
+-------+------+------+------+------+ 
| 1 | 1 | 2 | 15 | 16 | 
| 2 | 3 | 4 | 13 | 14 | 
| 3 | 5 | 6 | 11 | 12 | 
| 4 | 7 | 8 | 9 | 10 | 
+-------+------+------+------+------+
 

+-------+---------+ 
| xy_id | per_row | 
+-------+---------+ 
| 1 | 34 | 
| 2 | 34 | 
| 3 | 34 | 
| 4 | 34 | 
+-------+---------+ 
不! 他們的工作為行而不是列。 雖然這顯示了如何使用程序,循環和IF語句,示例失敗,它並沒有提供我想要的東西。 因此,我不得不返工插入更多的IF檢查以及掉期幾號的結尾。 

DROP PROCEDURE IF EXISTS magic_sq; 
DROP TABLE IF EXISTS `magic_sq` ; 

delimiter // 
CREATE PROCEDURE magic_sq( N int(11)) 
BEGIN 
DECLARE nXn INT; 
DECLARE SQ_SUM INT; 
DECLARE _passfail_ INT; 
DECLARE _io_ INT; 
DECLARE min INT; 
DECLARE max INT; 

-- DRAW THE TEMPLATE FOR THE SQUARE MAX of 8 FOR NOW 
DROP TABLE IF EXISTS `magic_sq` ; 
CREATE TABLE `magic_sq` ( 
`xy_id` int(11) NOT NULL AUTO_INCREMENT, 
`1` int(11) NULL DEFAULT NULL, 
`2` int(11) NULL DEFAULT NULL, 
`3` int(11) NULL DEFAULT NULL, 
`4` int(11) NULL DEFAULT NULL, 
PRIMARY KEY (`xy_id`) , 
UNIQUE KEY `Y1` (`1`), 
UNIQUE KEY `Y2` (`2`), 
UNIQUE KEY `Y3` (`3`), 
UNIQUE KEY `Y4` (`4`) 
) ENGINE= MEMORY; 


-- n X n 
SET @nXn = N + N; 
-- SQ_SUM This is the formula for what the total should equal 
SET @SQ_SUM = ( N * (POW(N,2) + 1) ) / 2; 
-- MIN Value 
SET @min=1; 
-- MAX Value 
SET @max=POW(N,2); 

-- insert_options 
SET _io_ =0; 

-- BUILD THE SQUARE 
WHILE ( @min <= @nXn ) 
DO 

-- TEST VALUES 
SET _passfail_ = IF ( (@min + (@min +1) + ( @max - 1) +@max) = @SQ_SUM ,1 , 0 ) ; 

-- IF VALID RESULTS THEN SAVE THEM 
IF _passfail_ = 1 THEN 

IF _io_ = 0 THEN 
INSERT INTO magic_sq VALUES (NULL,@min ,(@min +1),( @max - 1) , @max );
SET _io_ =1; 
ELSEIF _io_ = 1 THEN 
INSERT INTO magic_sq VALUES (NULL,( @max - 1),@max , @min , (@min +1) ); 
SET _io_ =2; 
ELSEIF _io_ = 2 THEN 
INSERT INTO magic_sq VALUES (NULL,@max ,(@min +1) , ( @max - 1) , @min ); 
SET _io_ =4; 
ELSEIF _io_ = 4 THEN 
INSERT INTO magic_sq VALUES (NULL, (@min +1) , @max , @min ,( @max - 1) ); 
SET _io_ =0; 
END IF; 

END IF; 

-- CONTINUE 
SET @min= @min +2; 
SET @max= @max -2; 

END WHILE; 
SELECT @x3y2 := `2` FROM magic_sq WHERE xy_id = 3; 
SELECT @x3y3 := `3` FROM magic_sq WHERE xy_id = 3; 
SELECT @x4y2 := `2` FROM magic_sq WHERE xy_id = 4; 
SELECT @x4y3 := `3` FROM magic_sq WHERE xy_id = 4; 


UPDATE magic_sq SET `2` = @x4y3 , `3` = @x4y2 WHERE xy_id = 3; 
UPDATE magic_sq SET `2` = @x3y3 , `3` = @x3y2 WHERE xy_id = 4; 
select * from magic_sq; 
select SUM(`1`),SUM(`2`),SUM(`3`),SUM(`4`) from magic_sq; 
select xy_id, SUM(`1` +`2` +`3` + `4`) as per_row from magic_sq GROUP BY xy_id; 

END// 
delimiter ;


現在工作的呢? 

CALL magic_sq(4); 
+-------+------+------+------+------+ 
| xy_id | 1 | 2 | 3 | 4 | 
+-------+------+------+------+------+ 
| 1 | 1 | 2 | 15 | 16 | 
| 2 | 13 | 14 | 3 | 4 | 
| 3 | 12 | 7 | 10 | 5 | 
| 4 | 8 | 11 | 6 | 9 | 
+-------+------+------+------+------+ 
4 rows in set (0.22 sec) 

+----------+----------+----------+----------+ 
| SUM(`1`) | SUM(`2`) | SUM(`3`) | SUM(`4`) | 
+----------+----------+----------+----------+ 
| 34 | 34 | 34 | 34 | 
+----------+----------+----------+----------+ 
1 row in set (0.22 sec) 

+-------+---------+ 
| xy_id | per_row | 
+-------+---------+ 
| 1 | 34 | 
| 2 | 34 | 
| 3 | 34 | 
| 4 | 34 | 
+-------+---------+ 
好吧,我騙了一點只是走動的列,但你的想法。

2014年5月15日星期四

http://anothermysqldba.blogspot.com/2014/05/a-look-at-mysql-57-dmr.html

Original post: http://anothermysqldba.blogspot.com/2014/05/a-look-at-mysql-57-dmr.html

所以我想它是關於時間我看著的MySQL 5.7。 這是一個高層次的概述,但我一直在尋找在MySQL的5.7簡而言之文件: 
於是我開始用新鮮的Fedora 20(Xfce的)安裝。 
總體來說,我會檢討,我發現好奇和有趣與MySQL 5.7的幾個項目。 果殼有很多信息,因此非常值得檢討。 

我下載了MySQL的-5.7.4-M14-1.linux_glibc2.5.x86_64.rpm-bundle.tar 

安裝,計劃做好以下 
# tar -vxf MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar 
# rm -f mysql-community-embedded* 
]# ls -a MySQL-*.rpm 
MySQL-client-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-embedded-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-shared-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-devel-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-server-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-test-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
# yum -y install MySQL-*.rpm 
Complete! 

雖然它說完整的我還注意到一個錯誤。 它應該還沒有完成安裝,如果它發現一個錯誤,但確定.... 
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db: 
Data::Dumper 

這個錯誤被證實.. 
# /etc/init.d/mysql start 
Starting MySQL............ ERROR! The server quit without updating PID file 
# tail /var/lib/mysql/fedora20mysql57.localdomain.err 
ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 
# /usr/bin/mysql_install_db 
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db: 
Data::Dumper 
# yum -y install perl-Data-Dumper 
# /usr/bin/mysql_install_db 
A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! 
You will find that password in '/root/.mysql_secret'. 

You must change that password on your first connect, 
no other statement but 'SET PASSWORD' will be accepted. 
# chown -R mysql:mysql /var/lib/mysql/mysql/ 
# cat /root/.mysql_secret 
# mysql -u root -p 
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword'); 
Query OK, 0 rows affected (0.01 sec) 

mysql> select @@version; 
+-----------+ 
| @@version | 
+-----------+ 
| 5.7.4-m14 | 
+-----------+ 

更健壯的程序升級等被記錄在這裡: 
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html 
檢查以確保你有GLIBC_2.15 如果您計劃在您的操作系統上安裝此。 

行,所以現在,它被安裝,我們有什麼。 
mysql> select User , Host,plugin from mysql.user \G 
*************************** 1. row *************************** 
User: root 
Host: localhost 
plugin : mysql_native_password 
mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
+--------------------+ 
mysql> SELECT @@default_password_lifetime \G 
*************************** 1. row *************************** 
@@default_password_lifetime: 360 

這些都是姍姍來遲的改進,並感謝大家的改進。 
所以現在來看看,在休息,我們至少需要某種形式的數據和架構。 因此,我將安裝世界數據庫測試。 
# wget http://downloads.mysql.com/docs/world_innodb.sql.gz 
# gzip -d world_innodb.sql.gz 
# mysql -u root -p -e "create database world"; 
# mysql -u root -p world < world_innodb.sql 
# mysql -u root -p world 
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` char(35) NOT NULL DEFAULT '', 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY ` CountryCode ` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB
 
mysql> ALTER TABLE City ALGORITHM=INPLACE, RENAME KEY CountryCode TO THECountryCode; 
Query OK
 
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` char(35) NOT NULL DEFAULT '', 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY ` THECountryCode ` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB 

mysql> DROP TABLE test.no_such_table; 
ERROR 1051 (42S02): Unknown table 'test.no_such_table' 
mysql> GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT; 
Query OK, 0 rows affected (0.45 sec) 

mysql> SELECT @p1, @p2 \G 
*************************** 1. row *************************** 
@p1: 42S02 
@p2: Unknown table 'test.no_such_table' 
1 row in set (0.01 sec)
  • 觸發器 
    觸發器的限制已經解除和多個觸發器是允許的。 請參閱文檔,因為他們給一個很好的例子。 我將演示它的一些在這裡只是為了顯示在一個表上的多個觸發器是可能的。
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); 
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; 
mysql> SET @sum = 0; 
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); 
SELECT @sum AS 'Total amount inserted'; 
+-----------------------+ 
| Total amount inserted | 
+-----------------------+ 
| 1852.48 | 
+-----------------------+
 
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account 
-> FOR EACH ROW PRECEDES ins_sum 
-> SET 
-> @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), 
-> @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
 
mysql> SHOW triggers \G 
*************************** 1. row *************************** 
Trigger: ins_transaction 
Event: INSERT 
Table: account 
Statement: SET 
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), 
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0) 
Timing: BEFORE 
Created: 2014-05-14 21:23:49.66 
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 
Definer: root@localhost 
character_set_client: utf8 
collation_connection: utf8_general_ci 
Database Collation: latin1_swedish_ci 
*************************** 2. row *************************** 
Trigger: ins_sum 
Event: INSERT 
Table: account 
Statement: SET @sum = @sum + NEW.amount 
Timing: BEFORE 
Created: 2014-05-14 21:22:47.91 
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 
Definer: root@localhost 
character_set_client: utf8 
collation_connection: utf8_general_ci 
Database Collation: latin1_swedish_ci
mysql> CREATE TABLE t1 
-> ( c1 CHAR(10) CHARACTER SET latin1 
-> ) DEFAULT CHARACTER SET gb18030 COLLATE gb18030_chinese_ci ; 
Query OK 
mysql> HANDLER City OPEN AS city_handle; 
mysql> HANDLER city_handle READ FIRST; 
+----+-------+-------------+----------+------------+ 
| ID | Name | CountryCode | District | Population | 
+----+-------+-------------+----------+------------+ 
| 1 | Kabul | AFG | Kabol | 1780000 | 
+----+-------+-------------+----------+------------+
 
mysql> HANDLER city_handle READ NEXT LIMIT 3; 
+----+-----------+-------------+---------------+------------+ 
| ID | Name | CountryCode | District | Population | 
+----+-----------+-------------+---------------+------------+ 
| 5 | Amsterdam | NLD | Noord-Holland | 731200 | 
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 | 
| 7 | Haag | NLD | Zuid-Holland | 440900 | 
+----+-----------+-------------+---------------+------------+
 
mysql> CREATE TABLE `t2` ( 
-> `t2_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
-> `inserttimestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
-> `somevalue` int(10) unsigned DEFAULT NULL, 
-> `rowLastUpdateTime` datetime DEFAULT NULL, 
-> PRIMARY KEY (`t2_id`,`inserttimestamp`) 
-> ) ENGINE=InnoDB;
 
mysql> ALTER TABLE t2 
-> PARTITION BY RANGE ( TO_DAYS(inserttimestamp) ) ( 
-> PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')), 
-> PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')), 
-> PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')), 
-> PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')), 
-> PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')), 
-> PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')), 
-> PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')), 
-> PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')), 
-> PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')), 
-> PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')), 
-> PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')), 
-> PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')), 
-> PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01')) 
-> );
 
mysql> INSERT INTO t2 VALUES (NULL,NOW(),1,NOW()); 
mysql> HANDLER t2 OPEN AS t_handle; 
mysql> HANDLER t_handle READ FIRST; 
+-------+---------------------+-----------+---------------------+ 
| t2_id | inserttimestamp | somevalue | rowLastUpdateTime | 
+-------+---------------------+-----------+---------------------+ 
| 1 | 2014-05-14 21:53:28 | 1 | 2014-05-14 21:53:28 | 
+-------+---------------------+-----------+---------------------+ 
mysql> select @@binlog_format\G 
*************************** 1. row *************************** 
@@binlog_format: ROW
 

# mysqlbinlog --database=world mysql-bin.000002 | grep world | wc -l 
22543# mysqlbinlog --rewrite-db='world->renameddb' mysql-bin.000002 | grep renameddb | wc -l 
22542

2014年5月6日星期二

MySQL錯誤1118(42000)的MySQL 5.0到MySQL 5.5或以上版本


所以,我最近有一個TB的數據庫,我不得不從MySQL 5.0升級到MySQL 5.5。
本博客文章將涉及以下內容:
  • SQL_MODE
  • innodb_strict_mode
  • SLAVE IO_THREAD
在mysql_upgrade過程(其中做了mysqlcheck的),我很快發現了以下錯誤:

ERROR 1118 (42000) at line 23: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

於是我開始,我不得不先檢查幾件事情。

# The master DB was > select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
| |
+------------+
新的數據庫是
mysql> select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +--------------------------------------------+ 
好吧,我寧願有東西SQL_MODE了一個空值。
所以,我讓那去。
# MASTER SERVER
select @@innodb_strict_mode;
ERROR 1193 (HY000): Unknown system variable 'innodb_strict_mode'

# NEW SERVER mysql> select @@innodb_strict_mode;
+----------------------+
| @@innodb_strict_mode |
+----------------------+
| 1 |
+----------------------+
你可以閱讀更多有關在這裡設置:
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-strict-mode.html
mysql> SET GLOBAL innodb_strict_mode=0;

上面的命令讓我至少升級mysql的一個檢查表。 為了安全起見我還是成立了一個bash腳本來轉儲和加載所有的表。 (是他們的所有TBS)

不要走捷徑,並承擔一切正常。

如果您收到錯誤mysqldump的並重新加載該文件。 更好的是安全比遺憾以後。

一旦數據被加載到5.5 + I可以查看和調整變量。

所以,不用說這是要花費一些時間來轉儲和裝載數據的傳輸塊。 我想給所有從機的機會,我可以趕上盡可能快。 雖然我的shell腳本傾銷和加載數據,沒有理由的數據庫無法採集二進制日誌在此期間。

mysql> START SLAVE IO_THREAD ; 
所以,現在當我PROCESSLIST會顯示:

SELECT /*!40001 SQL_NO_CACHE */ * 
從站狀態顯示:
Slave_IO_Running:是
Slave_SQL_Running:無

因此,我收集日誌,而我清理數據庫。
這應該允許數據庫迅速趕上一次,我準備好了。

2014年5月3日星期六

MySQL基準與mysqlslap

Original post: http://anothermysqldba.blogspot.com/2014/05/mysql-benchmark-with-mysqlslap.html

因此,基準不同的MySQL查詢在你的數據庫是一個明智的做法。 這應該不用說。 雖然我們優化查詢,我們可以利用解釋(和擴展)的最佳服用時間為基準他們應該證明是有益的。 

這是執行mysqlslap語句的一個簡單的例子。 

對於這個例子,我從MySQL載入世界資料庫。 ( http://dev.mysql.com/doc/index-other.html 

我創建了一個連接的所有三個表,並把它放到/ tmp目錄/ tests.sql查詢。 解釋計劃如下。 

root@localhost [world]> EXPLAIN EXTENDED SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: C
type: range
possible_keys: CountryCode,name_key
key: name_key
key_len: 5
ref: NULL
rows: 127
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Y
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 3
ref: world.C.CountryCode
rows: 1
filtered: 100.00
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: L
type: ref
possible_keys: PRIMARY,CountryCode
key: CountryCode
key_len: 3
ref: world.C.CountryCode
rows: 2
filtered: 100.00
Extra: Using index
3 rows in set, 1 warning (0.00 sec)

root@localhost [world]> show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `world`.`C`.`Name` AS `City`,`world`.`Y`.`Name` AS `Country`,`world`.`L`.`Language` AS `Language`,`world`.`Y`.`Population` AS `Population` from `world`.`City` `C` join `world`.`Country` `Y` join `world`.`CountryLanguage` `L` where ((`world`.`Y`.`Code` = `world`.`C`.`CountryCode`) and (`world`.`L`.`CountryCode` = `world`.`C`.`CountryCode`) and (`world`.`Y`.`Continent` = 'Europe') and (`world`.`C`.`Name` like 'D%'))


現在mysqlslap工具,此後一直圍繞的MySQL 5.1.4 
下面是一些其他有用的鏈接。 
現在,我有我的疑問,我可以基準是對數據庫使用以下命令。 

mysqlslap - 並發= 150 - 迭代= 50 - 查詢= / tmp目錄/的test.sql - 創建型模式=世界 

一注: 
查詢具有作為工具方便地做錯誤是非常乾淨的。 
例如下面扔這個錯誤: 

SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population
FROM City C
INNER JOIN Country Y ON C.CountryCode = Y.Code
INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode
WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' 

雖然這個查詢工作就好了。 

SELECT C.Name as City, Y.Name as Country, L.Language,Y.Population FROM City C INNER JOIN Country Y ON C.CountryCode = Y.Code INNER JOIN CountryLanguage L ON C.CountryCode = L.CountryCode WHERE C.Name LIKE 'D%' AND Y.Continent='Europe' 


該工具將輸出的基準測試結果為你 


Benchmark
Average number of seconds to run all queries: 0.104 seconds
Minimum number of seconds to run all queries: 0.096 seconds
Maximum number of seconds to run all queries: 0.141 seconds
Number of clients running queries: 150
Average number of queries per client: 1 


mysqlslap - 幫助會給你無數的選擇與測試您的查詢。 

您可以自動做的一切 

# mysqlslap --auto-generate-sql
Benchmark
Average number of seconds to run all queries: 0.243 seconds
Minimum number of seconds to run all queries: 0.243 seconds
Maximum number of seconds to run all queries: 0.243 seconds
Number of clients running queries: 1
Average number of queries per client: 0 


你可以測試插件也是如此。 比如我創建這個表: 

CREATE TABLE `foobar_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB ; 


所以後來用下面的測試。 

# mysqlslap --concurrency=1150 --iterations=530 --query="use test;insert into foobar_table (id) values (null)" --delimiter=";"
mysqlslap: Could not create thread 


確定錯誤是不是非常有幫助......但希望你注意到這個錯誤。 這是很難有1150個並發交易,如果你只有530次迭代。 


# mysqlslap --concurrency=150 --iterations=30 --query=/tmp/test1.sql --create-schema=test --verbose
Benchmark
Average number of seconds to run all queries: 0.260 seconds
Minimum number of seconds to run all queries: 0.192 seconds
Maximum number of seconds to run all queries: 0.476 seconds
Number of clients running queries: 150
Average number of queries per client: 1


例如下面的工作要好得多。 

# mysqlslap --concurrency=200 --iterations=1000 --query=" insert into foobar_table (id) values (null)" --verbose --create-schema=test
Benchmark
Average number of seconds to run all queries: 0.282 seconds
Minimum number of seconds to run all queries: 0.217 seconds
Maximum number of seconds to run all queries: 0.726 seconds
Number of clients running queries: 200
Average number of queries per client: 1 


只是為了證明我們在做真正的插入.. 

root@localhost [test]> select count(id) from foobar_table;
+-----------+
| count(id) |
+-----------+
| 206091 |
+-----------+
1 row in set (0.13 sec) 

現在,我還應該說,這只是我使用博客文章一個測試數據庫,所以不評價對這些結果的生產數據庫。 

我猜點之後這一切......找到一個麻煩的查詢,優化它最好就可以了,它的基準。 這是更好地了解自己的極限,而不是只是一個猜測。