MySQL数据库的理解和学习

小饶学编程之JAVA SE第二部分——01MySQL基础

一、简述在MySQL 数据库中 MyISAM 和InnoDB 的区别

InnoDB支持事务外键，MyISAM不支持；
InnoDB支持行级锁（默认）和表级锁，MyISAM支持表级锁；
InnoDB不存储总行数，MyISAM支持；
InnoDB支持多版本并发控制(MVVC)，MyISAM不支持；
InnoDB不支持全文索引，MyISAM支持；

索引的存储方式不同。
MySQL5.5以后默认使用InnoDB，之前是MyISAM。

一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
一个InnoDb引擎存储在一个文件空间
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

二、事务的并发问题？什么是脏读、幻读、不可重复读？要怎么处理？

这些问题都是MySQL进行事务并发控制时经常遇到的问题。

1. 脏读：在事务进行过程中，读到了其他事务未提交的数据。
   比如：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
2. 不可重复读：在一个事务过程中，多次查询的结果不一致。
   比如：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3. 幻读：在一个事务过程中，用同样的操作查询数据，得到的记录数不相同。
   比如：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了修改或者删除操作并提交，导致事务A多次读取同一数据时，莫名的多出了一些之前不存在数据，或者莫名的丢了一些数据。像发生了幻觉一样。
   处理的方式有很多种：加锁、重务隔离、MVCC

加锁：
1、脏读：在修改时加排他锁，直到事务提交才释放。读取时加共享锁，读完释放锁。
2、不可重复读：读数据时加共享锁，写数据时加排他锁。
3、幻读：加范围锁。

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

三、事务的基本特性和隔离级别有哪些？

事务：表示多个数据操作组成一个完整的事务单元，这个事务内的所有数据操作要么同时成功，要么同时失败。

事务的特性ACID：
1、原子性：事务是不可分割的，要么完全成功，要么完全失败。
2.一致性：事务无论是完成还是失败，都心须保持事务内操作的一致性。当失败时，都要对前面的操作进行回滚，不管中途是否成功。
3、隔离性：当多个事务操作一个数据的时候，为防止数据损坏，需要将每个事务进行隔离，互相不干扰。
4、持久性：事务开始就不会终止。他的结果不受其他外在因素的影响。

事务的隔离级别：SHOW VARIABLES like 'transaction%’
设置隔离级别：set transaction level xxx 设置下次事务的隔离级别。
set session transaction level xxx 设置当前会话的事务隔离级别
set global transaction level xxx 设置全局事务隔离级别

MySQL当中有五种隔离级别
NONE 无锁：不使用事务。
READ UNCOMMITED 读未提交： 允许脏读
READ COMMITED 读提交：防止脏读，最常用的隔离级别
REPEATABLE READ 可重复读：防止脏读和不可重复读。MYSQL默认
SERIALIZABLE 串行化： 事务串行，可以防止脏读、幻读，不可重复度。
五种隔离级别，级别越高，事务的安全性是更高的，但是，事务的并性能也就会越低。

四、MySQL的锁有哪些？什么是间隙锁？

MyISAM 支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

从锁的粒度来区分
1、行锁：加锁粒度小，但是加锁资源开销比较大。InnDB支持。
共享锁：读锁。多个事务可以对同一个数据共享同一把锁。持有锁的事务都可以访问数据，但是只能读不能修改。select xxx LOCK IN SHARE MODE.
排他锁：写锁。只有一个事务能够获得排他锁，其他事务都不能获取该行的锁。InnoDB会对updateldeletelinsert语句自动添加排他锁。SELECT xXx FOR UPDATE。
自增锁：通常是针对MySQL当中的自增字段。如果有事务回滚这种情况，数据会回滚，但是自增序列不会回滚。
2、表锁：加锁粒度大，加锁资源开销比较小。MyISAM和InnoDB都支持。
表共享读锁
表排他写锁
意向锁：是InnoDB自动添加的一种锁，不需要用户干预。

1.乐观锁：并不会真正的去锁某⾏记录，⽽是通过⼀个版本号来实现的。
2. 悲观锁：上⾯所的⾏锁、表锁等都是悲观锁。

五、谈一下对MySQL索引的理解？

①索引是用来帮助MySQL高效获取数据的排好序的数据结构。
②底层的数据结构是通过B+树或 hash 表来实现的。
③不同类型的数据结构是跟存储引擎相关的，MyISAM 和InnoDB 使用的B+树，MEMORY使用的 hash 表。
④原因是不同的存储引擎是数据在磁盘上的不同组织形式。
⑤使用B+树的原因是支持范围查找，查询速度快（树的高度低，）。
⑥在日常工作中，使用的比较多的主键索引和组合索引。而主键索引和组合索引会存在回表、最左匹配、覆盖索引、索引下推等。
⑦SQL优化可以通过索引优化，提高查询效率。
详细了解索引可以看这一篇：
面试必问：一文弄懂MySQL数据库索引之底层数据结构和索引类型

六、MySQL 数据库作发布系统的存储，一天五万条以上的增量， 预计运维三年,怎么优化？

1、设计良好的数据库结构， 允许部分数据冗余， 尽量避免 join 查询， 提高效率。
2、选择合适的表字段数据类型和存储引擎， 适当的添加索引。
3、MySQL 库主从读写分离。
4、找规律分表， 减少单表中的数据量提高查询速度。5、添加缓存机制， 比如 memcached， apc等。
6、不经常改动的页面， 生成静态页面。
7、书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

七、锁的优化策略

1、读写分离
2、分段加锁
3、减少锁持有的时间
多个线程尽量以相同的顺序去获取资源
不能将锁的粒度过于细化， 不然可能会出现线程的加锁和释放次数过多， 反而效率不如一次加一把大锁。

八、MySQL数据库优化

1、SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化

★缓存优化
●将查询出的数据放入redis缓存里，取数据时先从redis里拿

★读写分离
●设置数据库主从同步，主数据库做写，从数据库读。

★分库分表
●垂直分库，垂直分表，水平分库，水平分表

★优化sql
●避免全表扫描
●减少无效数据的查询

★sq|执行效率分析explain
●查看索引

★建立索引
●为合适的字段建立索引
●索引改变了文件存储的数据结构innodb弓|擎是使用B+tree效率更高，而且支持范围查询