两种重要的MySQL存储引擎-innodb，myisam

一.MySQL存储引擎

MySQL数据库常用的存储引擎为MyISAM和InnoDB,MyISAM在MySQL5.5之前一直是默认的存储引擎,之后,InnoDB逐渐替代了MyISAM存储引擎

1.如何用命令查看存储引擎

• 查看你的MySQL支持的存储引擎:

show engines;

• 查看你的mysql默认的存储引擎

show variables like '%storage_engine%';

二.两种重要的存储引擎MyISAM,InnoDB

1.锁机制

• 表级锁:开销小,加锁快,不会出现死锁,锁粒度大(整张表),并发度低,发生锁竞争概率大
• 行级锁:开销大,加锁慢,会出现死锁,锁粒度最小(一行数据),并发度高,发生锁竞争概率小
• 页级锁:开销,加锁速度,锁粒度,并发度都介于表级锁和行级锁之间,会出现死锁

应用:对于查询操作远大于修改,插入操作的表而言,采用表级锁更加合适,行级锁更加适合有大量按索引条件查询并发更新少量不同数据,同时又有并发查询的应用

2MyISAM和InnoDB的比较

1.MyISAM只支持表级锁

• MyISAM的表级锁有两种模式:共享读锁和独占写锁
• 对MyISAM的读操作,不会阻塞其他连接对同一张表的读请求,只会阻塞写请求
• 只有对MyISAM的写操作,才会阻塞其他连接对这张表的读和写操作
• MyISAM的加锁解锁都是自动的,在用户执行SELECT操作语句时,会自动给整张表加上读锁,在执行UPDATE,INSERT,DELETE操作时会自动加上写锁
• 在默认情况下,如果是同一时间有一个连接需要进行读操作,另外一个连接需要进行写操作,那么MyISAM会优先让写操作的连接获取到写锁,这也是MyISAM表不适合有大量写入操作和读操作的缘由

2.InnoDB支持表级锁和行级锁

• InnoDB实现了两种类型的行锁:共享锁,怕他锁,此外还有意向共享锁,意向排他锁
• 意向锁是InnoDB存储引擎自动加上的,无需SQL语句的控制
• InnoDB的行锁是通过给索引上的索引项加锁实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录进行加锁
• 如果不通过索引条件检索数据,那么InnoDB将会对表中所有记录加锁,其效果相当于表锁

3.事务

• MyISAM存储引擎不支持事务
  它强调的是高性能的查询,适合读多写少,原子性要求低的情形
• 对于InnoDB存储引擎则提供了较为完善的事务支持,一共支持个等级的事务隔离级别:读取未提交,读取已提交,可重复度,可串行化
  (1)读取未提交:最低的隔离级别,会导致脏读,幻读,不可重复读
  (2)读取已提交:读操作加锁,MySQL默认的隔离级别,会导致幻读,不可重复度
  (3)可重复读:对操作和写操作加锁,会导致幻读
  (4)可串行化:最高的隔离级别,要求同一时间最多只能有一个事务在执行,严重影响性能,容易造成锁冲突和死锁

4.索引

MySQL一共提供了四种索引实现:

• B-Tree索引:最常见的索引类型,通过B树来实现数据的快速访问
• Hash索引:通过构造哈希表来实现数据的快速访问,只有Memory引擎支持
• R-Tree索引:通过空间索引来实现数据快速访问
• Full-Text索引:全文索引.用于快速检索较长的文本

索引的支持情况

主键:
MyISAM可以支持没有主键的表存在,InnoDB必须设置主键,如果用户没有指定主键,那么会在表文件中设置一个6字节长度的ROWID,并以此最为主键
自增列(自增主键):
对于InnoDB的自增列,InnoDB规定必须包含只有该字段的索引,如果是MyISAM的自增列,则可以将该字段与其他字段字段组成联合索引

5、其它区别

1、InnoDB支持外键，MyISAM不支持外键。
2、对于SQL语句SELECT COUNT(*) FROM table，InnoDB需要对整张表进行读取才能得出结果。而MyISAM不同，MyISAM文件保存了整张表的记录，可以直接给出结果。但是如果加上了where条件，InnoDB和MyISAM都需要扫描整表给出结果。
3、对多CPU的优化
在单线程的情况下，MyISAM读取、修改速度要比InnoDB快，但是在多线程的情况下，InnoDB读取、修改速度显著提升，而MyISAM在多CPU的情况下几乎没有提升
4、对于SQL语句DELETE FROM table，InnoDB不会直接删除数据文件，而是一行行删除。对于MyISAM则是直接删除文件重新建立表
5、InnoDB存储引擎的表有着可靠的崩溃恢复机制，数据较容易恢复。MyISAM数据恢复较为困难。

3.使用场景

1、如果应用程序对数据的一致性要求比较高，那么需要选择InnoDB，因为InnoDB支持事务和外键
2、以读操作为主的业务，适合使用MyISAM。对于读多写多的业务，适合使用InnoDB。

三.SQL性能下降原因

1. 查询语句写的烂
2. 索引失效(单值,复合)
3. 关联查询太多join(设计缺陷不得已的需求)
4. 服务器调优及各个参数设置(缓冲,线程数等)

单值索引VS复合索引
一张user表,有id,name,emil,weixinNumber列
单值索引:

create index idx_user_name on user(name)

复合索引:

create index idx_user_nameEmil on user(name,emil);

四.SQL执行顺序

1.手写顺序

2.机读顺序

3.总结:SQL解析