REPLACE INTO 문 사용
*주의사항
*myIsam 으로 하고 많이 사용된다면 시퀀스당 하나씩 테이블을 설정해라
innodb를 쓸경우 잠금때문에 성능 저하가 발생 할 수 있다.
why
하나의 클라이언트가 번호를 가져갈며 트랜 잭션을 걸면 다른 클라이언트가 그 트랜잭션이 끝날때까지 대기 하여야함
*복제를 사용할 경우 nextVal()의 펑션을 사용하면 안된다.
why?
복제에서 가져갈때
nextVal()을 호출 함으로 시퀀스가 두번 증가
*select * from TEST.compare a where id not in (select id from TEST.test1 b);
빈번호 찾는 쿼리
http://code.flickr.net/2010/02/08/ticket-servers-distributed-unique-primary-keys-on-the-cheap/
* replace 문은 atomic 이 보장되는 문장이다.
1.테이블 생성
CREATE TABLE `Tickets64` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`stub` char(1) NOT NULL default '',
PRIMARY KEY (`id`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=MyISAM
2.
SELECT * FROM Tickets64;
REPLACE INTO Tickets64 (stub) VALUES ('a');
SELECT LAST_INSERT_ID();
3.
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE FUNCTION `testNextVal` ()
RETURNS INTEGER
BEGIN
REPLACE INTO Tickets64 (stub) VALUES ('a');
RETURN LAST_INSERT_ID();
END
4.
select testNextVal ();
6.테스트 테이블
CREATE TABLE test1 (
id INT NOT NULL ,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
7.테스트 프로시저
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `insertsValAtTableWithNTimes`(
IN i_table_name VARCHAR(255)
, IN i_n_times INT
)
BEGIN
DECLARE x INT DEFAULT 1;
SET @v_table_name := i_table_name;
SET @v_sql := 'INSERT INTO @p_table_name(id, name) VALUES(? ,?);';
SET @v_sql := REPLACE(@v_sql,'@p_table_name',@v_table_name);
PREPARE stmt FROM @v_sql;
WHILE x <= i_n_times DO
SET @v_id := 0;
SELECT testNextVal () INTO @v_id;
SET @v_name := CONCAT(x,'test');
EXECUTE stmt USING @v_id, @v_name;
SET x = x+1;
END WHILE;
DEALLOCATE PREPARE stmt;
END
8테스트
SELECT * FROM Tickets64;
TRUNCATE TABLE Tickets64;
SELECT * FROM test1;
TRUNCATE TABLE test1;
call insertsValAtTableWithNTimes('test1',10);
비교테이블
delimiter $$
CREATE TABLE T_COMPARE (
`id` bigint(20) unsigned NOT NULL auto_increment,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$
비교 프로시저
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `createTestData`(
IN i_table_name VARCHAR(255)
, IN i_n_times INT
)
BEGIN
DECLARE x INT DEFAULT 1;
SET @v_table_name := i_table_name;
SET @v_sql := 'INSERT INTO @p_table_name(name) VALUES(?);';
SET @v_sql := REPLACE(@v_sql,'@p_table_name',@v_table_name);
PREPARE stmt FROM @v_sql;
WHILE x <= i_n_times DO
SET @v_name := CONCAT('test');
EXECUTE stmt USING @v_name;
SET x = x+1;
END WHILE;
DEALLOCATE PREPARE stmt;
END
테스트 데이터 만들기
call createTestData('t_compare',1000000);
확인
select max(id) FROM t_compare;
SELECT count(*) FROM t_compare;
테스트
창뛰우고
call insertsValAtTableWithNTimes('test1',333333);
show processlist; 애들이 경쟁하는게 보인다.
잘못된 데이터 있나 체크
select count(*) from t_compare b where b.id not in (select id from test1);
마스터-마스터 구조로 만들경우
TicketServer1:
auto-increment-increment = 2
auto-increment-offset = 1
TicketServer2:
auto-increment-increment = 2
auto-increment-offset = 2
Flickr에서 현재 사용중
insert update문
1.테이블 생성
CREATE TABLE `TEST`.`sequence_data` (
`sequence_name` varchar(100) NOT NULL,
`sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
`sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
`sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
`sequence_cur_value` bigint(20) unsigned DEFAULT 1,
`sequence_cycle` boolean NOT NULL DEFAULT FALSE,
PRIMARY KEY (`sequence_name`)
) ENGINE=MyISAM;
2.시퀀스 입력
INSERT INTO TEST.sequence_data (sequence_name) VALUE ('sq_my_sequence');
INSERT INTO TEST.sequence_data(sequence_name, sequence_increment, sequence_max_value)VALUE('sq_sequence_2', 10, 100)
3.프로시저 생성
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE FUNCTION TEST.`nextval` (`seq_name` varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
DECLARE cur_val bigint(20);
SELECT
sequence_cur_value INTO cur_val
FROM
TEST.sequence_data
WHERE
sequence_name = seq_name;
IF cur_val IS NOT NULL THEN
UPDATE
TEST.sequence_data
SET
sequence_cur_value = IF ((sequence_cur_value + sequence_increment) > sequence_max_value,
IF (sequence_cycle = TRUE,sequence_min_value,NULL),
sequence_cur_value + sequence_increment)
WHERE
sequence_name = seq_name;
END IF;
RETURN cur_val;
END
4.getnextVal()
SELECT TEST.nextval('sq_my_sequence');
5.널이 리턴 될수 있는 상황
둘다 에러 상황임
When the sequence doesn’t exist
When current sequence value out of range
http://www.microshell.com/database/mysql/emulating-nextval-function-to-get-sequence-in-mysql/
6.테스트 테이블
CREATE TABLE TEST.test1 (
id INT NOT NULL ,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
7.테스트 프로시저
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE TEST.`insertsValAtTableWithNTimes`(
IN i_table_name VARCHAR(255)
, IN i_n_times INT
)
BEGIN
DECLARE x INT DEFAULT 1;
SET @v_table_name := i_table_name;
SET @v_sql := 'INSERT INTO TEST.@p_table_name(id, name) VALUES(? ,?);';
SET @v_sql := REPLACE(@v_sql,'@p_table_name',@v_table_name);
PREPARE stmt FROM @v_sql;
WHILE x <= i_n_times DO
SET @v_id := 0;
SELECT TEST.nextval('sq_my_sequence') INTO @v_id;
SET @v_name := CONCAT(x,'test');
EXECUTE stmt USING @v_id, @v_name;
SET x = x+1;
END WHILE;
DEALLOCATE PREPARE stmt;
END
8테스트
SELECT * FROM TEST.test1;
TRUNCATE TABLE `TEST`.`test1`;
mysql -h -u hoeuser -p
use test;
call TEST.insertsValAtTableWithNTimes('test1',1000);
비교테이블
delimiter $$
CREATE TABLE `compare` (
`id` int(11) NOT NULL,
`name` char(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$
UPDATE `test`.`sequence_data` SET `sequence_cur_value`='1' WHERE `sequence_name`='sq_my_sequence';
call TEST.insertsValAtTableWithNTimes('compare',60000);
Myisam 테이블을 사용해서 여러개가 그룹별 seq 만들기
Myisam table has a feature where , if a primary key contains 2 columns where one is an autoincrement and other is a varchar , then for each value of the text column the autoincrement column will create separate sequence of autoincrement numbers.
CREATE TABLE IF NOT EXISTS `vt_sequences` (
`type` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`type`,`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
In the above table type and id columns form the primary key. If the following insert is made :
INSERT INTO `vt_sequences` (`type`) VALUES
('A'),
('A'),
('B'),
('B'),
('A');
A 1 2011-10-22 15:29:04
A 2 2011-10-22 15:29:04
B 1 2011-10-22 15:29:04
B 2 2011-10-22 15:29:04
A 3 2011-10-22 15:29:04
So the id column has a 1 for A and B separately. So now if we want autoincrement numbers resetting every month we could insert 2011-10 and 2011-11 in the type field and the autoincrement numbers would be independant.
Another very useful thing about this technique is that if a particular entry is deleted , say A 3 is deleted , then next time when A is insert 3 would be reused , so autoincrement numbers get reused.
and use get_last_inserted_id
CREATE TABLE component_core (
component_id INT AUTO_INCREMENT,
PRIMARY KEY(component_id)
);
CREATE TABLE component_history (
component_id INT NOT NULL,
version_id INT AUTO_INCREMENT,
DATA VARCHAR(20),
version_start_date DATETIME NOT NULL,
version_end_date DATETIME NOT NULL,
PRIMARY KEY(component_id,version_id)
) ENGINE=MYISAM;
INSERT INTO component_core () VALUES ();
INSERT INTO component_core () VALUES ();
INSERT INTO component_history
(component_id, DATA, version_start_date, version_end_date)
VALUES (1, 'data', NOW(), NOW());
INSERT INTO component_history
(component_id, DATA, version_start_date, version_end_date)
VALUES (1, 'data', NOW(), NOW());
INSERT INTO component_history
(component_id, DATA, version_start_date, version_end_date)
VALUES (1, 'data', NOW(), NOW());
INSERT INTO component_history
(component_id, DATA, version_start_date, version_end_date)
VALUES (2, 'data', NOW(), NOW());
INSERT INTO component_history
(component_id, DATA, version_start_date, version_end_date)
VALUES (2, 'data', NOW(), NOW());
INSERT INTO component_history
(component_id, DATA, version_start_date, version_end_date)
VALUES (2, 'data', NOW(), NOW());
앞에 키를 두어서 pk를 잡는거 어차피 샤딩 되니까 상관없다
create table some_sharded_table(
shard_key int,
some_child_key bigint auto_increment not null,
key(some_child_key),
primary key (shard_key, some_child_key),
data1 int,
data2 char(10),
…
)
You can safely move records between shards, because the primary key includes the directory provided key. The directory server maps keys to shards.
You can then partition `some_sharded_table` nicely:
alter table some_sharded_table partition by hash(shard_key) partitions 16;
All the values related to one shard_key are now in one physical server, and the values are then partitioned again locally for improved performance through smaller indexes. This helps alleviate the overhead of the increased length of the secondary key used for the auto_increment value.
You can use the primary key index to get the most recent N rows very efficiently for any shard_key, something that is very common in most sharded applications that I see.
last insert id 를 써서 사용하는 예제
/* For this example, we'll put the sequences table in the test database. */
USE test;
/* Create a sequence table */
CREATE TABLE IF NOT EXISTS sequences
(name CHAR(20) PRIMARY KEY,
val INT UNSIGNED);
DROP FUNCTION IF EXISTS nextval;
DELIMITER //
/* The actual sequence function. Call nextval('seqname'), and it returns the next value. */
/* If the named sequence does not yet exist, it is created with initial value 1. */
CREATE FUNCTION nextval (seqname CHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT INTO sequences VALUES (seqname,LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE val=LAST_INSERT_ID(val+1);
RETURN LAST_INSERT_ID();
END
//
DELIMITER ;
/* Let's now use a sequence in a test table... */
CREATE TABLE IF NOT EXISTS data
(id int UNSIGNED NOT NULL PRIMARY KEY DEFAULT 0,
info VARCHAR(50));
DROP TRIGGER nextval;
/* The trigger only generates a new id if 0 is inserted. */
/* The default value of id is also 0 (see the create table statement) so that makes it implicit. */
CREATE TRIGGER nextval BEFORE INSERT ON data
FOR EACH ROW SET new.id=IF(new.id=0,nextval('data'),new.id);
TRUNCATE TABLE data;
INSERT INTO data (info) VALUES ('bla');
INSERT INTO data (info) VALUES ('foo'),('bar');
SELECT * FROM data;
