另一個MySQL DBA: routines

显示标签为“routines”的博文。显示所有博文

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年4月26日星期六

MySQL的分區時間戳 - 日期時間

Original post: http://anothermysqldba.blogspot.com/2014/04/mysql-partitions-timestamp-datetime.html

所以最近我意識到，我還沒有談過很多關於MySQL的分區。
在MySQL分區很多好的博客文章已經存在，並且我已經列出了幾個在下面。

我碰巧遇到以下的情況，我希望它證明了幫助他人。

而timestamp數據類型通常用於日期時間的作品更好地與分區。
（時間戳NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP）

所以下面是一個例子，如何如何解決這個問題。

首先我們有這個簡單的表。


 CREATE TABLE `t1` ( 
 `t1_id` int(11) NOT NULL AUTO_INCREMENT, 
 `field1` varchar(25) DEFAULT NULL, 
 `field2` int(10) DEFAULT '0', 
 `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
 PRIMARY KEY (`t1_id`), 
 KEY `tr` (`time_recorded`) 
 ) ENGINE=InnoDB AUTO_INCREMENT=856964

我們希望在time_recorded場分區。為了讓我們直接將打破它每月。


 ALTER TABLE t1 
 PARTITION BY RANGE ( TO_DAYS(time_recorded) ) ( 
 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')) 
 ); 
 ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

所以，現在什麼....

嗯，我知道我們需要的主鍵更新，如果我們想要的分區。


 ALTER TABLE t1  DROP PRIMARY KEY, ADD PRIMARY KEY (`t1_id`,`time_recorded`), LOCK=NONE; 
 Query OK, 0 rows affected (38.96 sec) 
 Records: 0  Duplicates: 0  Warnings: 0

請注意，我有鎖= NONE這是因為我使用的MySQL 5.6

主鍵更改將不會允許我雖然添加的分區，我還是需要調整的數據類型。我正在使用共享這個時候LOCKED =。請仔細閱讀上面的超鏈接獲取更多信息。如果你碰巧撿不工作一個類型，它通常意味著一個可行的解決方案。


 ALTER TABLE t1 CHANGE  time_recorded  time_recorded datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, LOCK=SHARED; 
 Query OK, 854312 rows affected (41.89 sec) 
 Records: 854312  Duplicates: 0  Warnings: 0

所以，現在我們可以添加我們的分區。


 ALTER TABLE t1 
 ->  PARTITION BY RANGE ( TO_DAYS(time_recorded) ) ( 
 ->      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')) 
 ->  ); 
 Query OK, 854312 rows affected (50.74 sec) 
 Records: 854312  Duplicates: 0  Warnings: 0

我們可以選擇，DELETE，UPDATE，INSERT和每個分區等。更多關於這裡： https://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html 。


 SELECT COUNT(t1_id) FROM t1 PARTITION (Jan2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |       661752 | 
 +--------------+ 
 1 row in set (0.55 sec) 
 SELECT COUNT(t1_id) FROM t1 PARTITION (Feb2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |        64952 | 
 +--------------+ 
 1 row in set (0.04 sec) 
 SELECT COUNT(t1_id) FROM t1 PARTITION (Mar2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |        71336 | 
 +--------------+ 
 1 row in set (0.04 sec) 
 SELECT COUNT(t1_id) FROM t1 PARTITION (Apr2014); 
 +--------------+ 
 | COUNT(t1_id) | 
 +--------------+ 
 |        56272 | 
 +--------------+ 
 1 row in set (0.05 sec)

而這個工程，我們現在要做有一個分區。我們現在也必須要考慮的分區維護。格林的博客上給出了一個非常漂亮的自動滴的例子和分區的增加。誠然，如果你不想放棄你將需要調整的例子分區。
我已經調整了我的表的例子。請仔細閱讀格林的博客更多的細節。


 DROP PROCEDURE IF EXISTS Rotate_t1_Partition; 
 DELIMITER ;; 
 CREATE PROCEDURE Rotate_t1_Partition (newPartValue DATETIME) 
 BEGIN 
 -- Setup 
 DECLARE keepStmt VARCHAR(2000) DEFAULT @stmt; 
 DECLARE partitionToDrop VARCHAR(64); 
 
 -- Find and drop the first partition in the table. 
 SELECT partition_name 
 INTO partitionToDrop 
 FROM INFORMATION_SCHEMA.PARTITIONS 
 WHERE table_schema='forums_mysql' 
 AND table_name='t1' 
 AND partition_ordinal_position=1; 
 SET @stmt = CONCAT('ALTER TABLE t1 DROP PARTITION ',  partitionToDrop); 
 PREPARE pStmt FROM @stmt; 
 EXECUTE pStmt; 
 DEALLOCATE PREPARE pStmt; 
 
 -- Add a new partition using the input date for a value limit. 

 SET @stmt = CONCAT('ALTER TABLE t1 ADD PARTITION (PARTITION ', 
DATE_FORMAT(newPartValue - interval 1 MONTH, '%b%Y'), ' VALUES LESS THAN
 (TO_DAYS(\'',  DATE_FORMAT(newPartValue, '%Y-%m-%d'),'\')))'); 
 PREPARE pStmt FROM @stmt; 
 EXECUTE pStmt; 
 DEALLOCATE PREPARE pStmt; 
 
 -- Cleanup 
 SET @stmt = keepStmt; 
 END;; 
 DELIMITER ;

所以，我現在可以更新這個表很容易地以下。


 CALL Rotate_t1_Partition('2015-03-01'); 
 Query OK, 0 rows affected (1.11 sec)

你也可以，因為格林的博客指出的那樣，你可以使用這個用NOW（）+間隔1個月，你決定為你的分區永遠的時間框架。格蘭特，你必須記住，如果你傳遞已經是一個分區的日期將會失敗。

要繼續與自動化，您可以將它添加到一個事件。


 CREATE EVENT Rotate_t1_Partition 
 -> ON SCHEDULE EVERY 1 MONTH 
 -> DISABLE ON SLAVE 
 -> COMMENT 'Remove oldest partition and add a new one ' 
 -> DO 
 -> CALL Rotate_t1_Partition(NOW() + interval 1 MONTH); 
 Query OK, 0 rows affected (0.04 sec)

記住對於上面的例子中的有效時間。我只是用這個作為一個例子。

2014年3月12日星期三

MySQL的計劃停機時間和其他狀態變量進行審查

Original post: http://anothermysqldba.blogspot.com/2014/03/planning-mysql-downtime-and-other.html

我們都知道，而MySQL是穩定的，並可以運行了很長一段時間沒有重新啟動。幾乎每隔一段時間，我們需要重新啟動它調整一些變量或升級雖然。有些人可能會認為是在一夜之間做到這一點的最好時機，因為他們的用戶都睡著了。然而，經常通宵還包括備份，並且可以做不同的數據挖掘等其他相關的crontab腳本

所以你怎麼能得到時，你有服務器上的活動最少的指引？現在，每個應用程序和數據庫是不同的，所以你需要考慮哪些相關的活動正在運行時，不同的時間框架內。如果你開始記錄“threads_connected的”狀態變量每分鐘5到10分鐘，這同樣取決於如何加載你的系統是一小時一次可能被罰款，你會很快聚集在您登錄不同的MySQL連接的數量時，框架。

這可以做一個無數的方法。它已經提供了很多企業級的工具，你可以編寫腳本等，但只是為了保持它在MySQL。我將使用一個事件和日常的這個簡單的例子。

創建一個簡單的表：

CREATE TABLE `threads_count` ( 
 `threads_count_id` int(11) NOT NULL AUTO_INCREMENT, 
 `variable_value` varchar(255) DEFAULT NULL, 
 `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
 PRIMARY KEY (`threads_count_id`) 
 ) ENGINE=InnoDB;

創建一個簡單的程序：

delimiter // 
 CREATE PROCEDURE threads_tally() 
 BEGIN 

 INSERT INTO threads_count SELECT NULL , VARIABLE_VALUE , NOW() FROM 
information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME IN 
('Threads_connected'); 
 END// 
 delimiter ;

創建一個簡單的事件：

SET GLOBAL event_scheduler = ON; 
 CREATE EVENT `threads_tally` 
 ON SCHEDULE EVERY 5 MINUTE 
 DISABLE ON SLAVE 
 COMMENT 'keeps a count of Threads_connected' 
 DO 
 CALL threads_tally(); 
 DELIMITER ;

但現在執行上面的例子中，你將有你可以從（可能放到一個圖形或只是進行審查）中選擇值，以便可以看到，當存在服務器上的最少連接數的表。根據您的應用程序和流量，你可以保持這個運行足夠長的時間來獲得一個有效的角度，然後禁用事件。

select variable_value , time_recorded FROM threads_count;

現在，你當然可以藉此更進一步，如果你沒有企業工具，並保持一個日誌中的所有審查你的狀態變量的：

表中的值：

CREATE TABLE `status_review` ( 
 `status_review_id` int(11) NOT NULL AUTO_INCREMENT, 
 `variable_name` varchar(255) DEFAULT NULL, 
 `variable_value` varchar(255) DEFAULT NULL, 
 `time_recorded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
 PRIMARY KEY (`status_review_id`) 
 ) ENGINE=InnoDB;

日常收集的值：

delimiter // 
 CREATE PROCEDURE status_reviews() 
 BEGIN 
 INSERT INTO status_review SELECT NULL , VARIABLE_NAME, VARIABLE_VALUE , NOW() FROM information_schema.GLOBAL_STATUS; 
 END// 
 delimiter ;

事件執行例程：

SET GLOBAL event_scheduler = ON; 
 CREATE EVENT `status_reviews` 
 ON SCHEDULE EVERY 1 HOUR 
 DISABLE ON SLAVE 
 COMMENT 'keeps a eye on status values' 
 DO 
 CALL status_reviews(); 
 DELIMITER ;

您可能只希望某些變量名和值進行審核，並可以在常規相應地調整查詢。運行時間取決於您的需要，我在這個例子中使用一個小時。

當然，這也將隨著時間的推移，產生了大量的信息，因此，如果相關的一些清潔這些表隨著時間的推移可能需要。這一切都取決於你如何經常登錄並查看所提供的信息。

雖然你會看到，因為你的數據增長和系統的許多價值的穩步增長使用，你也將能夠確定數據的尖峰在數據和關聯，為您的應用程序和系統中使用。

MySQL的企業，例如已經將圖中所有此類信息對你，但是這是一種方式，你也可以做自己的數據一點時間和審查。

订阅：博文 (Atom)

Pages