AUTO_INCREMENT ON the MyISAM STORAGE ENGINE

AUTO_INCREMENT ON the MyISAM STORAGE ENGINE


CASE:
HOW TO TUNNING AUTO_INCREMENT ON THE MyIS STORAGE ENGINE


SOLVE:
1.AUTO_INCREMENT column is a multiple-column index and order by last column of a multiple-column index.
2.AUTO_INCREMENT column is one key faster in engine myisam than engine innodb




1.AUTO_INCREMENT column is a multiple-column index and order by last column of a multiple-column index.
mysql> create table autoincre_demo
    -> (
    -> d1 smallint not null auto_increment,
    -> d2 smallint not null,
    -> name varchar(10),
    -> primary key (d2,d1)
    -> )
    -> engine=myisam;
Query OK, 0 rows affected (0.00 sec)


mysql> insert into autoincre_demo(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> select * from autoincre_demo;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)
## order by id2 and id2 is last column of a multiple-column index


2.AUTO_INCREMENT column is one key faster in engine myisam than engine innodb
mysql> create table myisam_tab
    -> (
    -> id MEDIUMINT NOT NULL AUTO_INCREMENT,
    -> id2 MEDIUMINT NOT NULL,
    -> PRIMARY KEY(ID)
    -> )
    -> engine=MYISAM;
Query OK, 0 rows affected (0.01 sec)


mysql> create table innodb_tab
    -> (
    -> id MEDIUMINT NOT NULL AUTO_INCREMENT,
    -> id2 MEDIUMINT NOT NULL,
    -> PRIMARY KEY(ID)
    -> )
    -> engine=innodb;
Query OK, 0 rows affected (0.04 sec)


mysql> insert into myisam_tab(id2) select id2 from myisam_tab;
Query OK, 409600 rows affected (1.10 sec)
Records: 409600  Duplicates: 0  Warnings: 0


mysql> insert into innodb_tab(id2) select id2 from innodb_tab;
Query OK, 409600 rows affected (4.22 sec)
Records: 409600  Duplicates: 0  Warnings: 0
## insert into myisam_tab time is 1.10 sec  and insert into innodb_tab time is 4.22 sec


Reference
http://dev.mysql.com/doc/refman/5.7/en/myisam-storage-engine.html
When rows are inserted in sorted order (as when you are using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree.


Internal handling of one AUTO_INCREMENT column per table is supported. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) The AUTO_INCREMENT value can be reset with ALTER TABLE or myisamchk.


########################################################################################
版權所有，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!【QQ交流群：53993419】
QQ：14040928 E-mail：dbadoudou@163.com
本文連結： http://blog.itpub.net/blog/post/id/2072782/
########################################################################################


dbadoudou scripts


create table autoincre_demo
(
d1 smallint not null auto_increment,
d2 smallint not null,
name varchar(10),
primary key (d2,d1)
)
engine=myisam;
insert into autoincre_demo(d2,name) values(2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
select * from autoincre_demo;


create table myisam_tab
(
id MEDIUMINT NOT NULL AUTO_INCREMENT,
id2 MEDIUMINT NOT NULL,
PRIMARY KEY(ID)
)
engine=MYISAM;
create table innodb_tab
(
id MEDIUMINT NOT NULL AUTO_INCREMENT,
id2 MEDIUMINT NOT NULL,
PRIMARY KEY(ID)
)
engine=innodb;
insert into myisam_tab(id2) select id2 from myisam_tab;
insert into innodb_tab(id2) select id2 from innodb_tab;