存储引擎MyISAM和InnoDB

存储引擎：创建、查询、更新、删除

innoDB：64T、支持事物、不支持全文索引、支持缓存、支持外键、行级锁定

MyISAM：256T、不支持事物、支持全文索引、插入和查询速度快

memory：内存、不支持事物、不支持全文索引，临时表

archive：none、不支持事物、不支持全文索引，insert，select

myisam：

frm：表的结构信息

myd：数据部分

myi：索引 （创建索引越多，myi越大）

innoDB：

frm：数据表的元数据

ibd：数据

opt：mysql的配置信息

创建“users”表查看默认存储引擎：

修改配置文件中引擎名创建t1表，查看存储引擎名看是否是自己修改的引擎名：

创建t2表的同时指定存储引擎：

创建t3表，使用set命令临时修改存储引擎：

MyISam和InnoDB实例比较

1：创建两张表分别以MyIsam和InnoDB作为存储引擎

create database test;

use test;

create table tm(id int(20) primary key auto_increment,name char(30)) engine=myisam;

create table ti(id int(20) primary key auto_increment,name char(30)) engine=innodb;

查看两张表的存储引擎：

mysql> show create table tm\G

mysql> show create table ti\G

2：插入一千万数据，来比较两个存储引擎的存储效率

（1）设置sql语句结束符

mysql> delimiter $

（delimiter $语句是设置sql语句的结束符为“$”）

（2）创建两个存储过程

mysql> create procedure insertm()

begin

set @i=1;

while @i

do

insert into tm(name) values(concat("wy",@i));

set @i=@i+1;

end while;

end

$

mysql> create procedure inserti()

begin

set @i=1;

while @i

do

insert into ti(name) values(concat("wy",@i));

set @i=@i+1;

end while;

end

$

mysql> delimiter ;

（delimiter ;语句是设置sql语句的结束符为“;”）

（3）利用存储过程向两个表添加数据插入（一千万条）MyIsam存储引擎的表中的时间如下：

mysql> call insertm;

Query OK, 0 rows affected (1 min 49.74 sec)

插入（一千万条）InnoDB存储引擎的表中的时间如下：

 mysql> call inserti;

Query OK, 0 rows affected (13 min 32.96 sec)

比较结果：

MyIsam存储引擎在写入方面有优势

3：查询数据总数目

下面是InnoDB的SQL语句的分析：

MariaDB [test]> desc select count(*) from ti\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: ti

         type: index

possible_keys: NULL

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 1000160

        Extra: Using index

1 row in set (0.00 sec)

下面是MyIsam（他的数据存储在其他的表中所以这里是没有影响行数的）的SQL语句的分析：

riaDB [test]> desc select count(*) from tm\G;

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: NULL

         type: NULL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: NULL

        Extra: Select tables optimized away

1 row in set (0.00 sec)

MyIsam使用专门的MYD表存储数据，所以这里没有查询结果

MyISAM存储引擎会是数据表生成3个文件：

frm文件存储表的定义、MYD文件是数据文件、MYI文件是索引文件

4：查询某一范围的数据

（1）没有索引的列

MariaDB [test]> select * from tm where name>"wy100" and name

+---------+-----------+

| id      | name      |

+---------+-----------+

|    1000 | wy1000    |

|   10000 | wy10000   |

|  100000 | wy100000  |

| 1000000 | wy1000000 |

+---------+-----------+

4 rows in set (0.68 sec)

  

MariaDB [test]> select * from ti where name>"wy100" and name

+---------+-----------+

| id      | name      |

+---------+-----------+

|    1000 | wy1000    |

|   10000 | wy10000   |

|  100000 | wy100000  |

| 1000000 | wy1000000 |

+---------+-----------+

4 rows in set (2.71 sec)

MariaDB [test]> select * from tm where name="wy9999999";

+---------+-----------+

| id      | name      |

+---------+-----------+

| 9999999 | wy9999999 |

+---------+-----------+

1 row in set (0.42 sec)

MariaDB [test]> select * from ti where name="wy9999999";

+---------+-----------+

| id      | name      |

+---------+-----------+

| 9999999 | wy9999999 |

+---------+-----------+

1 row in set (2.38 sec)

无索引查询MyIsam有优势

（2）有索引的列

对于使用MyIsam存储引擎的表：

select * from tm where id>10 and id

执行时间：

9999988 rows in set (1.88 sec)

对于使用了InnoDB存储引擎的表：

select * from ti where id>10 and id

执行时间：

9999988 rows in set (0.65 sec)

有索引查询INNODB有优势