Mysql数据库引擎和索引测试

不同引擎的主要区别：

 MyISAM是非事务安全型的，而InnoDB是事务安全型的
•MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
•MyISAM支持全文类型索引（FULLTABLE），而InnoDB不支持全文索引。
•MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM
•InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM中存储了表的行数，只要简单的读出保存好的行数即可。
•InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。

show ENGINES ：

 不用引擎的应用场景：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。--select
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。--insert，update

常用操作：

（1）查看表的存储类型（三种）：
show create table tablename
show table status from  dbname  where name=tablename
mysqlshow  -u user -p password --status dbname tablename
（2）修改表的存储引擎：
alter table tablename type=InnoDB
（3）启动mysql数据库的命令行中添加以下参数使新发布的表都默认使用事务：
--default-table-type=InnoDB
（4）临时改变默认表类型：
set table_type=InnoDB
show variables like 'table_type'
（5）关闭mysql服务net stop mysql
         启动mysql服务net start mysql

 

下面是测试。

首先创建表和存储过程，这里先测试 INNODB 引擎 的情况：

CREATE TABLE `test` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
 `num` INT NOT NULL, 
`flag` VARCHAR(100)
) ENGINE=INNODB  ;
 
DELIMITER $$
DROP PROCEDURE IF EXISTS dowhile $$

CREATE PROCEDURE dowhile(IN n INT) 
BEGIN
DECLARE i INT DEFAULT 1;
WHILE(i<n)DO 
INSERT INTO test VALUES(NULL,i,'测试数据');
SET i=i+1;
END WHILE;
END $$
DELIMITER;


// 添加100w行数据
call dowhile(1000000);
结果 耗时2小时：
受影响的行: 1 时间: 7310.940s

 

 

 

然后创建 MYISAM 引擎的同样的表：

alter table test engine=MYISAM;

执行同样的存储过程吗，添加100w行数据：耗时 2小时啊

 

 

[SQL]
call dowhile(1000000);
受影响的行: 1
时间: 4116.447s

可见， 这个插入的速度，要比 innodb 快一些， 大概是因为没有事务的检查等原因。

 

测试查询和索引：

 

查询单个：

[SQL]SELECT * FROM test WHERE num = '500000';
受影响的行: 0
时间: 0.770s // 第一次稍微久一点点， 因为可能有staring、 没建立cache 等

[SQL]SELECT * FROM test WHERE num = '500000';
受影响的行: 0
时间: 0.610s

[SQL]SELECT * FROM test WHERE num = 900000 ;
受影响的行: 0
时间: 0.601s // 因为没有limit，所以必须要才查询到最后一条， 所以耗时比较久，相当于是全表扫描

[SQL]
SELECT * FROM test WHERE num = 1 ;
受影响的行: 0
时间: 0.620s  // 因为没有limit，所以必须要才查询到最后一条， 所以耗时比较久，相当于是全表扫描； 跟 num 多少无关

[SQL]
SELECT * FROM test WHERE num = 1 limit 1 ;
受影响的行: 0
时间: 0.001s  // 非常快， 因为， 查询到1条就立即返回。

[SQL]
SELECT * FROM test WHERE num = 999999 limit 1 ;
受影响的行: 0
时间: 0.641s // 到最后才查询到， 所以耗时比较久

[SQL]
SELECT * FROM test WHERE num = 1999999 limit 1 ;
受影响的行: 0
时间: 0.622s // 到最后也没查询到， 所以耗时比较久，相当于是全表扫描

[SQL]SELECT * FROM test WHERE num = '500000' LIMIT 1

受影响的行: 0
时间: 0.315s

[SQL]SELECT * FROM test WHERE num = 500000 LIMIT 1 ;
受影响的行: 0
时间: 0.305s

[SQL]SELECT * FROM test WHERE num = 900000 LIMIT 1 ;
受影响的行: 0
时间: 0.549s

[SQL]SELECT * FROM test WHERE num = 900000 LIMIT 1 ;
受影响的行: 0
时间: 0.561s

（上面的测试，试验发现是innodb 和 myisam 中都是差不多的结果，但 总统来说，myisam  时间要快一些，可能是没有事务的开销吧 ）

[SQL]
SELECT * FROM test WHERE id = 1 ;
受影响的行: 0
时间: 0.002s

[SQL]
SELECT * FROM test WHERE num = 1 ;
受影响的行: 0
时间: 0.284s // 总统来说，myisam  时间要快一些，

[SQL]
SELECT * FROM test WHERE num = 999999 ;
受影响的行: 0
时间: 0.294s


[SQL]
SELECT * FROM test WHERE num = 11999999 ;
受影响的行: 0
时间: 0.294s

[SQL]
SELECT * FROM test WHERE num = 1 limit 1 ;
受影响的行: 0
时间: 0.001s

[SQL]
SELECT * FROM test WHERE num = 999999 limit 1 ;
受影响的行: 0
时间: 0.286s

 

可见，第一次执行会慢一些； 加上 limit 1之后， 会快很多  -- limit 1 的意思是说，查询到一条数据就返回， 没有的话， 就需要遍历全部的数据。

 

加上索引测试：

 ALTER TABLE `test`  ADD UNIQUE INDEX `idx_nn` (`num`) ;

 再测试

[SQL]
SELECT * FROM test WHERE num = 500000

受影响的行: 0
时间: 0.001s

[SQL] SELECT * FROM test WHERE num = 500000 LIMIT 1

受影响的行: 0
时间: 0.001s 

 

可以看到，增加索引之后，查询速度直线上升。 而且跟 是否有 limit 关系不大！ Sending data 的时间从 0.3175820 大幅度减少为 0.0000250s， 占比从 99.839%， 减少到 3.333%； 所以说，如果，能够确定结果只有一行，或者只需要一行数据，可以增加个 limit ！但是观察发现，executing的时间是差不多的，差别最大的时间是： Sending data，但是Bytes_sent 是几乎一样的， why？查询一下“Sending data”状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”， 参考：  https://blog.csdn.net/yunhua_lee/article/details/8573621 

 

[SQL]
SELECT * FROM test WHERE num = 1 limit 2 ;
受影响的行: 0
时间: 0.290s

--- 发现 LIMIT 2 也会导致全表扫描.. 

 

如果是唯一索引的话，lmit 1、  lmit 2 都是非常快。 （normal 索引也差不多）

[SQL]

SELECT * FROM test WHERE num = 1 limit 2 ;
受影响的行: 0
时间: 0.001s 

 

结论：

实验发现，无论在查找操作上还是插入数据操作上，InnoDB引擎下所用的时间均大与MYISAM引擎下所用时间，经分析：INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多:
1）INNODB要缓存数据块，MYISAM只缓存索引块；
2）MYISAM寻址定位比INNODB要快；
3）INNODB还需要维护MVCC一致所以InnoDB引擎下查询速度慢；
（MVCC (Multi-Version Concurrency Control)多版本并发控制）
以上就是InnoDB引擎下查询慢于MYSISAM引擎的原因
至于写入操作，InnoDB引擎和MYISAM引擎有这巨大的差距，大约3000多倍的差距，是什么造成如此大的差距，答案是事务。