mysql总结二

数据库引擎
数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务，从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象（如索引、视图和存储过程）。

InnoDB
InnoDB是一个事务型的存储引擎，有行级锁定和外键约束，是mysql5.5以后默认的存储引擎。
Innodb引擎提供了对数据库ACID事务的支持，并且实现了SQL标准的四种隔离级别，该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引，而且**它没有保存表的行数***，当SELECT COUNT() FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于***锁的粒度更小***，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

//这个就是select锁表的一种，不明确主键。增删改查都可能会导致锁全表，在以后我们会详细列出。
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

什么时候才会使用行级锁呢？
当我们通过索引条件检索数据的时候，Innodb才会使用行级锁，否则使用表级锁
我同时打开两个终端，同时开启两个mysql连接，然后我的title是一个索引，我用它作为查询的时候的，两个都能正常查询，

当我不用索引作为查询条件的时候，再看看结果
首先我先删除索引title，使他成为一个普通的字段

所以使用innodb的行级锁需要加所以的；

MySQL官方对InnoDB的讲解：
1）InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。
2）InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中行级锁定适合非常小的空间。
3）InnoDB也支持FOREIGN KEY强制。在SQL查询中，你可以自由地将InnoDB类型的表与其它MySQL的表的类型混合起来，甚至在同一个查询中也可以混合。
4）InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
5） InnoDB被用来在众多需要高性能的大型数据库站点上产生。

适用场景：
1）经常更新的表，适合处理多重并发的更新请求。
相对于表级锁，行级锁能更好的支持并发，因为执行一条sql如果把一整张表给锁住了，其他的只能等待，但是如果只锁住一行，那么其他的只要不是针对被锁住的那一行都能执行。所有并发要好一些
然后它的行级锁是在存储引擎层上实现的
2）支持事务。
Innodb本身支持事务
3）可以从灾难中恢复（通过bin-log日志等）。
4）外键约束。只有他支持外键。
5）支持自动增加列属性auto_increment。

Innodb的两种锁：
排它锁：
对于update, delete, insert 语句，innodb会自动给涉及的数据集加上排它锁，对于普通的select语句不加任何锁
格式： select … from table_name … for upddate;

共享锁：
又称读锁， 其他用户可以并发的读取数据， 但任何事务都不能对数据进行修改，直到释放所有共享锁

死锁：
innodb发生死锁后，一般能自动检测到，并使用事务释放锁并回退，另一个事务获取锁后继续完成事务。

MyIsam：
定义：
MyIASM是MySQL默认的引擎，但是它没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。
MyIsam 存储引擎独立于操作系统，也就是可以在windows上使用，也可以比较简单的将数据转移到linux操作系统上去。
意味着：引擎在创建表的时候，会创建三个文件，一个是.frm文件用于存储表的定义，一个是.MYD文件用于存储表的数据，另一个是.MYI文件，存储的是索引。操作系统对大文件的操作是比较慢的，这样将表分为三个文件，那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。
适用场景：
1）不支持事务的设计，但是并不代表着有事务操作的项目不能用MyIsam存储引擎，可以在service层进行根据自己的业务需求进行相应的控制。
2）不支持外键的表设计。
3）查询速度很快，如果数据库insert和update的操作比较少的话比较适用。
因为insert 和update要锁全表，而在innodb中insert和update的操作只需要锁一行，innodb适合于insert和update的操作比较多的情况， myisam则恰好相反
4）整天 对表进行加锁的场景。
5）MyISAM极度强调快速读取操作。
6）MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择。
缺点：
就是不能在表损坏后恢复数据。（是不能主动恢复）

myisam支持的锁：
表共享锁：
执行查询时，会自动给涉及到的表加锁
语法格式：
加锁：
lock table table_name read
解锁：
unlock tables;

表独占锁：
在执行更新操作时会加写锁
读操作不会阻塞其他对同一表的读请求，但是会阻塞对同一表的写请求，当读锁与写锁同时被申请的时候，优先获得写锁
语法格式：
加锁：
lock table table_name write
解锁：
unlock tables;

死锁：
myisam没有死锁问题，因为它会一次获取所有的锁
加表共享锁，只能读不能改：

加独占锁：
独占锁只能对同一个用户连接进行增删改查
第一个连接，可以查询，插入，删除，和更新

第二个连接：
查询不到任何东西

两种存储引擎的大致区
别表现在：
1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
3）InnoDB支持外键，MyISAM不支持
4）从MySQL5.5.5以后，InnoDB是默认引擎
5）InnoDB不支持FULLTEXT类型的索引
6）InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’