前言
数据库是java开发过程必不可少的数据存储工具，通常我们分为关系型数据库和非关系型数据库，上一篇中已经简单的介绍了redis，这里说一说关系型数据库mysql。
1、mysql引擎
这里只说我们常见的两种引擎，MyISAM和Innodb，其他相关的引擎有兴趣的可以自行了解。首先我们简单的说说这两种引擎的区别。
1）MyISAM，不支持事务，支持全文索引、表锁，在磁盘上有三个与表名相同的文件，.FRM文件存储表结构，.MYD文件存储表数据，.MYI文件存储表索引，MyISAM不缓存数据，只缓存索引。
2）InnoDB，支持事务、外键、行锁和非锁定读，即默认情况下读不产生锁。InnoDB由一系列后台线程和一大块内存组成。默认情况下，后台线程有7个，4个IO线程，1个master thread，1个lock监控线程，1个error监控线程。
在实际开发中，由于事务和行级锁等机制的原因，Innodb可能接触的更多一些。MyISAM更多用于查询，尤其是select * from tableName，可以直接获取到数值，不需要全表扫描。
2、mysql结构
mysql的结构如下图所示，个人十分喜欢这张图，通过这张图还可以大致了解一条SQL语句的执行过程
1）Connectors，与其他编程语言（java等）中的sql语句进行交互
2）Connection pool，连接池，管理缓存用户连接、线程处理等需要缓存的数据
3）Management services&Utilities，系统管理和控制工具
4）SQL interface，SQL接口，接收用户的sql命令，并返回用户需要的结果
5）parser，解析器，验证并解析用户传来的sql语句，将sql语句解析成数据结构，并将这个数据结构传递到后续步骤，如果解析过程需要错误，说明sql语句是错误的，停止执行
6）Optimlzer，优化器，产生多种执行计划，数据库选择最优的方案执行
7）cache&buffer，缓存，如果查询缓存有命中，则直接返回结果。cache是读缓存，buffer是写缓存。
8）Engines，存储引擎
SQL语句执行过程：
1）请求与mysql的connectors进行交互，请求到达后会暂时存放在连接池（connection pool）中，并由处理器（management services&utilities）管理。当请求从等待队列进入处理队列后，管理器会将请求交给SQL接口（SQL interface）。
2）SQL接口接收到请求后，会将请求进行hash处理并和缓存（cache&buffer）中的结果进行比较，如果完全匹配，则直接返回缓存数据。
3）如果缓存未命中，由SQL接口将请求交给解析器（parser），解析器将sql语言解析成数据结构
4）解析器处理完毕，请求便来到优化器（optimizer），优化器会产生多个执行计划，数据库会选择一个最优方案来执行
5）确定最优方案之后，sql语句便可以由存储引擎处理（engines），存储引擎将会到后端的存储设备中获取数据并原路返回
3、索引
mysql中的索引包括主键索引（默认自动为主键添加）、普通索引、唯一索引（字段的值不能重复）、组合索引和全文索引（不建议使用，而且Innodb也不支持，建议使用solr等代替）。其中组合索引需要额外说明一下，组合索引满足最左原则，即假如在A、B、C三列上添加索引，实际上会额外在A和A,B上创建索引，相当于创建了三个索引。另外可以通过show index from tableName查询该表上的索引。索引的算法包括B-Tree和Hash两种，在此强调一下Innodb不支持Hash，即使设置成Hash，索引依然使用B-Tree。
通常我们会在经常使用的查询语句的where和join字段上添加索引，这里需要注意的是join的两个字段必须类型一致，如果是字符串类型，编码格式也要一样，否则将无法使用索引，另外，在like中，以_%通配符开头的查询无法使用索引，如：
select * from tableName where name like '_zhang'
select * from tableName where name like '%zhang'
上述两个sql无法使用name列上的索引
select * from tableName where name like 'zhang_'
select * from tableName where name like 'zhang%'
上述两个sql可以使用name列上的索引
还有一点需要注意的是，对于变化较少的字段不建议添加索引，如性别的列，通常的值是0和1或者true和false，此列添加索引不会有太大的速度提升。一个表的索引个数也不建议过多，增删改数据的同时还要维护索引，过多的索引会降低数据库的效率。
4、mysql优化
mysql的优化从三个方面入手，包括创建表时的优化，索引的创建以及sql的优化
1）创建表时的优化
a）为表添加主键ID，最好使用无符号的int类型，设置成自增（注意分表时id重复的情况）
b）针对字典表可以考虑使用ENUM类型代替字符串，如果插入的值不在ENUM规定值内，则自动使用''空字符串代替
c）尽量使用NOT NULL，使用NULL需要额外的空间，而且NULL无法使用索引
d）使用PROCEDURE ANALYSE（）函数取得建议，根据表中数据进行分析，所以需要数据库中有一定的数据做基础，如：
SELECT * FROM sys_user PROCEDURE ANALYSE();
越小的列越快，在考虑日后数据扩展的前提下，尽量使用较小存储单元的类型，如字典表中数据较少时，主键可以放弃使用INT，用MEDIUMINT、SMALLINT来代替，甚至使用更小的TINYINT，如果不需要记录时间，可以使用DATE代替DATETIME
e）使用固定长度的表，如果表中所有的列都是固定长度的，表会被认为是“static”的，会加快查询速度，如果列中包含varchar、text或blog之一，则表不是固定长度的。固定长度的表由于定长，可以根据偏移量计算下一条数据的位置，而变长的表只能通过寻找下一条数据的主键。固定长度的表的缺点就是会造成空间浪费，因为无论存储数据大小，都会分配定长的空间。注：mysql字段占用空间与类型有关，如int(11)和int(3)都占用四个字节的长度，区别是根据存储数据的长度自动补全零位的个数，存储1111时，由于长度是4，int(3)不需要补零，int(11)会自动补全7个0
f）表分割，表分割可以分为水平分割和垂直分割。水平分割就是建立结构相同但是表名不同的表，将数据按照hash运算之后存储在对应的表中，如sys_user_1,sys_user_2等；垂直分割是将表中一些不常用的或者会经常更新导致查询缓存失效的列分割出来，如家庭住址，最后登录时间等字段。注：被分割的字段不能经常被JOIN，否则会极大降低性能
g）数据库读写分离
2）索引的创建
索引的创建就是在适合的列上添加索引，从而达到加快查询速度的效果，上面已经提及，这里不再累述。
3）SQL的优化
a）使用explain关键字解析sql语句，如：
EXPLAIN SELECT * FROM (SELECT user_sex,COUNT(*) AS counts FROM sys_user u GROUP BY u.user_sex) count_t WHERE count_t.counts > 1
结果如下所示：
通常我们关注的是type这一列，这里不要求记住所有，只需要记住几个典型的，并且了解哪个更优即可，如system、const、eq_ref、ref、range、index、all等。这里有一篇文章，大家可以参考了解一下：《mysql explain 的type解释》
b）在查询结果明确只有一条的情况下，且该列上没有索引，使用LIMIT 1可以在查询到数据之后直接返回，避免全表扫描；判断一条数据是否存在时，使用SELECT 1可以提高查询速度，SELECT 1 > SELECT any column > SELECT *
c）避免SELECT *，用什么数据取什么列，减少网络数据传输
d）尽量少用having、in、not in、is null等关键字或判断，对于having，可以使用子查询来代替；in和not in可以使用exists和not exists代替，如果数据连续，也可以使用between and代替。
5、Innodb文件结构
InnoDB表由共享表空间或独立表空间、日志文件组和表结构文件组成。可以使用show variables like 'innodb_file_per_table'查看是否开启了独立表空间，结果为ON时表示开启独立表空间；OFF时表示使用共享共享表空间。
1）表结构文件（.frm）mysql会为每个表在其数据目录下创建一个tablename.frm文件来存储表结构定义，与引擎无关，用来恢复表结构
2）表空间文件，表空间（tablespace）由分散的段（segment）组成，一个段包含多个区（extent），一个区由64个连续的页（page）组成，每个页的大小为16K，所以一个区的大小为1M，区包含行（row）。
3）共享表空间将所有的数据存储在一个单独的表空间里面；独立表空间为每一个表创建一个表空间文件（.ibd），存储表的数据、索引等数据，撤销（undo）信息、系统事务信息等存放在原来的共享表空间中。在mysql5.6.5及之前默认是共享表空间，之后默认是独立表空间。可以使用 SHOW VARIABLES like 'innodb_data%'命令来查看共享表空间文件信息，文件默认的名称是ibdata1，初始大小为10M，可自动扩展，如：ibdata1:10M:autoextend
4）共享表空间的优劣势
优势：可以将表空间分成多个文件存储在不同的磁盘上，一个表的数据可以存放在不同的表空间文件上，表空间文件大小不受表大小限制；数据和文件放在一起，方便管理
劣势：大量删除数据时，表空间会出现空隙
5）独立表空间的优劣势
优势：每个表有独立的表空间，表数据和索引存储在独立的表空间中，可以实现单表在数据库中的转移（数据文件复制）；大量删除数据的时候，可以压缩表空间文件，回收空间
劣势：单个表空间文件很大
6、事务隔离级别
说到数据库就不得不说一下事务，简单的ACID原则大家都知道，讲的是原子性、隔离性、一致性和永久性，希望你能用实际情况描述一下这四种特性。另外就是事务的隔离级别，如下：
1）Read uncommitted，读取未提交内容，最低级别，任何情况都可能发生
2）Read committed，读取已提交内容，可避免脏读
3）Repeatable read，可重复读，可避免脏读、不可重复读
4）Serializable，串行化，避免脏读、不可重复读、幻读
以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。
顺带提一下数据异常的情况：
1）脏读，一个事务在执行过程中读取了另一个事务未提交的数据。
2）不可重复读，一个事务范围内的多次查询返回不同结果，由于在查询间隔中，数据被其他事务修改并提交了。
3）幻读，一个事务对一批数据进行修改，另一个事务插入一条新数据，此时新插入的数据并未被第一个事务修改，产生幻读。
脏读是读取另一个事务未提交的数据产生的，而不可重复读和幻读是读取另一个事务已提交的数据产生的；不可重复读是针对一条数据产生的，幻读是针对一批数据产生的。
7、Innodb锁
针对锁的粒度大小不同，mysql分为行级锁、表锁、页锁，这里我们只讨论行级锁和表锁。Innodb支持行级锁，MyISAM使用的是表锁。根据锁的性质不同，又可分为读锁（共享锁）、写锁（排它锁）和意向锁。只有读锁与读锁是兼容的，也就是说多个线程可以同时读取同一数据，但是当有一个线程写数据时，其他线程无论是读取还是写该数据均被阻塞。意向锁是一种虚拟的锁，当一个线程对表结构进行修改时，需要对整个表进行加锁，再获取到表锁之后还需要判断是否存在行级锁，传统的方式就是一行一行的判断，这样是极其低效的方法。意向锁的思想是，在添加行级锁时，对表添加意向锁，当有其他线程对表加锁之后，只需要判断是否存在意向锁就可以知道是否存在行级锁了。
添加读锁的方式为：
select * from tableName where id =1 lock in share mode
添加写锁的方式为：
select * from tableName where id =1 for update
这里需要说明一下，添加写锁时，必须明确id且id存在，否则mysql是会锁住一段范围（有gap锁）。
关于mysql的总结大致就这么，还有很多东西等待我们去探索和学习，希望这篇文章能帮助大家对mysql有一个简单的了解。
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
原文链接：http://www.cnblogs.com/1ning/p/6705129.html