一：结构图
二：MySQL的数据库存储引擎
1：ISAM
全名：Indexed Sequential Access Method， ISAM是一个定义明确且历经时间考验的数据表格管理方法，他在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个不足之处在于，他不支持事务处理，也不能够容错。如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务的应用程序里，就必须经常备份所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序
注意：使用ISAM注意点：必须经常备份所有实时数据
2：MyISAM
MyISAM是MySQL的ISAM的扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还有一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的时间。MyISAM还要一些有用的扩展，例如用来恢复数据库文件的MyISAMCHK工具和和用来恢复浪费空间的呢MyISAMPACK工具。MyISAM强调了快速读取操作。这可能就是为什么MySQL受到了Web开发如此青睐的原因：在Web开发中你所进行的大量数据操作都是读取操作，所以大多数虚拟主机的提供商和INTERNET平台提供商只允许使用MyISAM格式。MyISAM格式的一个重要缺陷就是不能再表损坏后恢复数据
注意：MyISAM引擎使用注意：必须经常使用Optimize Table命令清理空间；必须经常备份所有的实时数据。工具有用来恢复数据库文件的MyISAMCHK工具和和用来恢复浪费空间的呢MyISAMPACK工具。如果使用该数据库引擎，会生成三个文件：
from：表结构信息
MYD：数据文件
MYI：表的索引信息
3：InnoDB
3.1：概念
InnDB数据库引擎是MySQL5.5以后的默认引擎，是造就MySQL灵活性的技术的直接产品，这项技术就是MySQL++API。在使用MySQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM引擎不支持事务处理，也不支持外键。尽管比ISAM和MyISAM慢很多，但是InnoDB包括了对事物处理和外键的支持。如前所诉，如果你的设计需要这些特性，那么就要使用InnoDB
MySQL官方对InnoDB是这样解释的：InnoDB给MySQL提供了具有提交，回滚和崩溃恢复能力的事务安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句提供一个Oracle风格一致的非锁定读，这些特色增加了多用户部署和性能。没有在InnoDB中扩大锁定的需要，因为在InnoDB中适合非常小的空间。InnoDB特支持 FOREIGN KEY强制。在SQL查询中，你可以自由将InnoDB类型的表与其他MySQL类型的表混合起来，甚至在同一个查询中也可以混合。
InnoDB是为了处理巨大数据量时的最大性能设计，他的CPU效率可能是任何其他基于磁盘的关系数据库引擎所不能匹敌的。
innoDB存储引擎被完全与MySQL服务器整合，InnoDB存储引擎在内存缓存数据和索引而维持它自己的缓冲池。innoDB存储他的表和索引在一个表空间中，表空间可以包含数个文件（或原始磁盘分区）。这与MyISAM不同，比如在MyISA表中每个表被存在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制的操作系统中。
InnoBD特点：支持事务安装，数据多版本读取，锁定机制的改进，实现外键。
3.2：InnoDB和MyISAM的区别
3.2.1：InnoDB支持事务，MyISAM不支持，对于InnoDB的每一条SQL都默认封装事务，自动提交，这样会影响速度，所以最好把多条SQL语句放在begin和commit之间，组成一个事务
3.2.2：InnoDB支持外键，而MyISAM不支持。对一个包含外键的InoDB表转为MyISAM会失败
3.2.3：InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后通过主键查询数据，所以主键不应该太大，因为主键很大，其他的索引也会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
3.2.4：InnoDB不保存表的具体行数，执行select count(*) from table时需要进行全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出变量。
3.2.5：InnoDB不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高
3.3：如何选择
3.3.1：是否需要支持事务，需要事务选择InnoDB，不需要考虑MyISAM
3.3.2：如果表中绝大多数都是读查询，可以考虑MyISAM，如果既有读写也挺频繁，使用InnoDB。
3.3.3：系统崩溃后，MyISAM恢复困难，能否接受。
3.3.4：不知道用什么就要InnoDB。
4：NDBCluster
NDB存储引擎也叫NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境，Cluster是MySQL5.0提供的新功能。
5：Merge
Megre存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG MyISAM引擎。因为Merge存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减少应用的复杂度的目的。要创建Merge表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。
6：Memory
Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将人和数据存放到磁盘上。所以一旦MySQL Crach或者主机Crach之后，Memory的表就只有表结构了。Memory表支持索引。由于是存放在内从中，所以Memory都是按照定长的空间来存储数据的，而且不支持Blob和Text类型的字段。Memor实现页级锁定
7：BDB存储引擎
BDB存储引擎全称为BerkeleyDB存储引擎，和InnoDB一样，也不是MySQL自己开发实现的存储引擎，而是由SleepycatSoftWare所提供的开源存储引擎，同样支持事务安全
8：FEDERATED存储引擎
FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程MySQL服务器上面的数据的访问接口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用FEDERATED存储引擎才行，因为MySQL默认是不起用该存储引擎的。
9： ARCHIVE存储引擎
ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操作，仅支持插入和查询操作。锁定机制为行级锁定。
10：BLACKHOLE存储引擎
BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。
11：CSV存储引擎
CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CSV表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。
三：操作数据库引擎
1：查看数据库支持的引擎
show engines;2：查看数据库默认引擎
show variables like '%storage_engine%';3：查看数据表使用的引擎
show create table table_name;4：创建表指定存储引擎
create table table_name (column_name column_type) engine = engine_name5：修改表的存储引擎
alter table table_name ENGINE =engine_name;6： 修改默认的存储引擎
在MySQL配置文件中修改下述内容：
default-storage-engine=INNODB
MySQL配置文件：
windows系统 - MySQL安装目录/my.ini （5.7版本my.ini文件在数据目录中。 C:/programdata/MySQL Server 5.7/mysql/）
linux系统 - /etc/my.cnf注：数据库的隔离级别
read committed(MySQL默认)
read uncommitted
repeatable read(Oracle默认)
serializable
四：数据库索引介绍
1：建立索引的优点
1.1：通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
1.2：可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
1.3：可以加速表与表的连接，特别是在实现数据的参照完整性方面特别有意义
1.4：在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
1.5：通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能
2：建立索引的缺点：
2.1：创建索引和维护索引要耗费时间。这种时间随着数据量的增加而增加
2.2：索引需要占用物理空间。除了数据表占数据空间之外，每一个索引还要占一定的空间，如果要建立聚簇索引，那么需要的空间更大。
2.3：当对表中的数据进行增加，删除，修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
3：适合创建索引的字段
3.1：在经常需要搜索的列上，可以加快搜索的速度
3.2：在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3.3：在经常用在连接的列上，这些列主要是一些外键，可以加快的连接的速度
3.4：在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
3.5：在经常需要排序的列上创建索引，因为索引以及排序，这样查询可以利用索引的排序。
3.6：在经常使用在where子句中的列上面创建索引，加快条件的判断速度。
建立索引，一般按照select的where条件来建立，比如select条件是f1 and f2，那么在f1或f2上建立索引是没有用的，只有在f1和f2上同时建立索引才有效。
4：不适合出创建索引的字段
4.1：对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
4.2：对于那些只有很少种数据值的列也不应该创建索引。是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
4.3：对于text,image,bit数据类型的列不应增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4.4：当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
5：索引的分类
5.1：B-Tree索引
B-Tree索引，顾名思义，就是所有的索引节点都按照balance tree的数据结构来存储。B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快存取速度。
B-tree中，每个结点包含：
5.1.1本结点所含关键字的个数；
5.1.2指向父结点的指针；
5.1.3关键字；
5.1.4指向子结点的指针；
对于一棵m阶B-tree，每个结点至多可以拥有m个子结点。各结点的关键字和可以拥有的子结点数都有限制，规定m阶B-tree中，根结点至少有2个子结点，除非根结点为叶子节点，相应的，根结点中关键字的个数为1~m-1；非根结点至少有[m/2]（[]，向上取整）个子结点，相应的，关键字个数为[m/2]-1~m-1。
B-tree有以下特性：
5.1.1：关键字集合分布在整棵树中；
5.1.2：任何一个关键字出现且只出现在一个结点中；
5.1.3：搜索有可能在非叶子结点结束；
5.1.4：其搜索性能等价于在关键字全集内做一次二分查找；
5.1.5：自动层次控制；
5-2：Full-text索引
Full-text索引就是我们常说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。只能解决’xxx%’的like查询。如：字段数据为ABCDE，索引建立为- A、AB、ABC、ABCD、ABCDE五个。
五：MySQL的索引管理
索引有普通索引，唯一索引，组合索引，全文索引
1：普通索引
这是最基本的索引，他没有任何限制，MyISAM中默认的B-Tree类型的索引，也是大多数情况下用到的索引
1.1：创建索引
CREATE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD INDEX index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , INDEX index_name (title(5)))1.2：查看索引
SHOW INDEX FROM [table_name]
SHOW KEYS FROM [table_name] # 只在MySQL中可以使用keys关键字。1.3：删除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY2：唯一索引
与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似
2.1 创建索引
CREATE UNIQUE INDEX index_name ON table_name (column(length))
ALTER TABLE table_name ADD UNIQUE index_name (column(length))
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , UNIQUE index_name (title(length)))3：全文索引
MySQL从3.23.23版开始支持全文索引和全文检索，FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。
对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
3.1： 创建索引
CREATE FULLTEXT INDEX index_name ON table_name(column(length))
ALTER TABLE table_name ADD FULLTEXT index_name( column)
CREATE TABLE table_name (id int not null auto_increment,title varchar(30) ,PRIMARY KEY(id) , FULLTEXT index_name (title))4：组合索引（最左前缀）
CREATE TABLE article(id int not null, title varchar(255), time date);平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：
–title,time
–title为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引，如下面的几个SQL所示：
使用到上面的索引：
SELECT * FROM article WHERE title='测试' AND time=1234567890;
SELECT * FROM article WHERE title='测试';不使用上面的索引：
SELECT * FROM article WHERE time=1234567890;参考：4.1： 创建索引
CREATE INDEX index_name ON table_name (column_list)六：MySQL中的索引优化
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE次数大于查询次数时，放弃索引。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
1： 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，组合索引中只要有一列含有NULL值，那么这一列对于此组合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。create table table_name(c1 varchar(32) default ‘0’)
2：使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
CREATE INDEX index_name ON table_name (column(length))
3： 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4：like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。
5：不要在列上进行运算
例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′
七：索引总结
最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的。建议：一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
八：SQL优化
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点。虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE次数大于查询次数时，放弃索引。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
1：对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引
2：应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全局扫描。如：
select id from t where num=null;可以在num上设置0，然后进行如下查询：
select id from t where num=0;3：应尽量避免在where子句中使用！=或<>操作符，否则引擎将放弃索引而进行全表扫描。
4：应尽量避免在where子句中使用or来连接条件，否则引擎将放弃索引而进行全表扫描。如：
select id from t where num=10 or num=20;可以使用如下查询：
select id from t where num=10 union all select id from t where num=20;5：in和not in也要慎用，否则会到底全表扫描，如：
select id from t where num in (1,2,3);对于连续的数值，能用between 就不要用in了：
select id form t where num between 1 and 3;6：下面的查询也将导致全表扫描：
select id from where name like '%lk%';7：应该尽量避免在where子句中对字段进行表达式操作，否则引擎将放弃索引而进行全表扫描。如：
select id from t where num/2=100;应改为：
select id from t where num=100*2;8：应避免在where子句中进行函数操作，否则引擎将放弃索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc';应改为：
select id from t where name='abc%';9：不要在where子句中“=”左边进行函数，算数运算或其他表达式运算，否则系统将可能无法正确使用索引。
10：在使用索引字段作为条件时，如果该索引是符复合索引，那么必须使用到该索引中的第一个索引作为条件时，才能保证系统使用该索引，并且应尽量让字段顺序与索引顺序一致。
11：不要写一些没有意义的查询。
12：很多时候用exists代理in是一个好的选择：
select num from a where num in(select num from b);应该改为：
select num from a where exists(select 1 from b where num=a.num);13：并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引。
14：索引并不是越多越好。索引固然可以提高相应的select效率，但同时也降低了insert和update的效率，因为insert或者update时可能会重建索引。
15：尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串每个字符，而对于数字仅比较一次即可。
16：尽可能使用varchar来代替char，因为首先可变长度字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对小的字段内搜索效率显然较高。
17：任何地方都不要使用select * from t，用具体字段代替"*"，不要返回任何用不到的字段。
