MySQL

1 Connectors连接器，指的是不同语言中与SQL的交互
 
2 Management Serveices & Utilities： 系统管理和控制工具
 
3 Connection Pool: 连接池。
管理缓冲用户连接，线程处理等需要缓存的需求
 
4 SQL Interface: SQL接口。
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
 
5 Parser: 解析器。
SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。
主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 
b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的


6 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
 
7 Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
 
8 Engine ：存储引擎。
存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。
Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）
现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB
默认下MySql是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。
InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。 Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。
 


3、NDB集群引擎
MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群(MySQL Cluster)。




4、Archive引擎
Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。
Archive引擎支持行级锁和专用的缓存区，所以可以实现高并发的插入，但它不是一个事物型的引擎，而是一个针对高速插入和压缩做了优化的简单引擎。


5、Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。
 
 6、Memory引擎
如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。
Memory表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，这可能呆滞部分内存的浪费。
临时表和Memory表不是一回事。临时表是指使用CREATE TEMPORARY TABLE语句创建的表，它可以使用任何存储引擎，只在单个连接中可见，当连接断开时，临时表也将不复存在。
 




Blackhole引擎
Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
 
CSV引擎
CSV引擎可以将普通的SCV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
 



DDL（Data Definition Language）数椐定义语言：这些语言定义了不同的数据段、数据库、表、列、索引等数据对象，主要用于操作数据库本身的数据或者说数据结构。
DML（Data Manipulation Language）数据操作语句：用于增加、删除、修改、查询数据库中记录的数据，主要为操作数据本身。
DCL（Data Control Language）数据控制语句：用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。


细分也可以分为六类：DDL、DML、DQL、TPL、DCL、DCL、CCL。
DDL数据定义语言：create、drop
DML数据操作语言：insert、delete、update
DCL数据控制语言：grant、revoke
DQL数据查询语言：select、where、order by、group by、having
TPL数据处理语言：确保被DML影响的表的所有行及时得以更新，begin transaction、commit、rollback
CCL指针控制语言：declare cursor，fetch into和update where current 用于对一个或多个表单独的操作。

MyISAM
MyISAM的表支持3种不同的存储格式：1.静态（固定长度）表；2.动态表；3.压缩表。
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。MyISAM是在web、数据仓储和其他应用环境下最常用的存储引擎之一。
myisam有表元数据的缓存 行数count(*)


InnoDB
用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（commit）和回滚（rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。
InnoDB实现了以下两种类型的行锁。
l  共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
l  排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！




explain
select count(*) from uia_userdetail FORCE INDEX (PRIMARY)
select count(*) from uia_userdetail FORCE INDEX (permit)

3.Parser解析器
SQL命令传递到解析器的时候会被解析器验证和解析。-----解析器是由Lex和YACC实现的，是一个很长的脚本。
主要功能：
a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 
b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
预处理


4.查询执行
查询执行阶段只是简单的根据执行计划给出的指令逐步执行，在这个过程中，有大量的操作需要通过调用存储引擎实现的接口（handler API）来完成。MySQL在优化阶段就是为每个表创建了一个handler实例，优化器就是根据这些接口获取表的相关信息。


5.结果返回
即使查询不需要返回查询的结果给客户端，MySQL仍会返回一些查询基本信息，比如影响行数。
如果有查询返回数据，MySQL会将数据逐步返回，而不是统一获取后统一返回。
这样即缩短了客户端获取数据的的时间，又使得服务器不需要存储太多数据。

每个cache都是以SQL文本做为key的
query_cache_type=1时，可以使用SQL_NO_CACHE，涉及平凡更新的表的SQL语句加上SQL_NO_CACHE关键字禁用cache，可以避免不必要的内存操作，尽可能保持内存的连续性
query_cache_type=2时，只缓存SQL_CACHE
查询很分散的SQL不应该使用cache
show variables like '%query_cache%'查询缓存配置参数
show status like '%Qcache%'查询缓存情况
Qcache_hits命中次数
Qcache_inserts未命中后插入次数




命令：show variables like '%query_cache%'查询缓存配置参数
命令：show status like '%Qcache%'查询缓存情况



查询优化器将语法树转换为执行计划，并找出最好的执行计划。
尽可能使用索引
比较类型相同可以便于优化器更好的利用索引






6 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
1=1

1.优先使用join联表查询，而不是子查询
原因：过多的子查询会使效率下降。
2.当join联表查询时，过滤条件优先使用on，之后无法使用on的，再使用where进行过滤
原因：MySQL使用join联表时，首先处理on中的过滤条件，然后再处理where中的过滤条件，将过滤条件优先放在on后，可以时筛选出的集合尽可能小，提高效率。
3.确认查询结果只有一条时，使用limit 1
原因：如果不使用limit 1，select检索到第一条匹配信息后，仍然对后边的数据继续检索，使用limit 1 可以避免查找到结果后不必要的继续查找，提高性能。
4.不要使用order by rand()
原因：效率太低，可以先使用select count(*) from table;获取总条数n，在n~0之间获取随机值，使用limit n-1,1;的方式获取随机数据，效率明显高于order by rand()
5.不要滥用MySQL的类型自动转换，如：数字3不要写为'3'
原因：类型转换会增加不必要的性能开销
6.尽量避免使用模糊查询

尽量使用唯一索引
尽量将索引建立在比较短的列上
原因：通常越小的数据类型在内存、磁盘及CPU缓存中使用的空间更少，处理速度更快
尽量将索引建立在简单的数据类型上
原因：简单的类型比字符串处理所需要的开销更小
不要建立过多的索引
原因：每个索引都会占用一定空间，在插入时会影响效率，同时，在查询命令时进行执行计划也需要分析更多的内容，从而影响效率
尽量避免使用Null
原因：含有空值的列很难进行查询优化，索引、索引的统计信息以及比较运算更加复杂。可以使用0、特殊字符或者空字符串进行替代
建立复合索引时，注意各列顺序
原因：MySQL只能对最左边的索引进行有效搜索，而且，如果第一个匹配，就不会去判断第二个。假设存在组合索引it1c1c2(c1,c2)，查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是，查询语句select * from t1 where c2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。

使用Explain查看select的情况。其中type字段显示连接使用了何种类型，从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL；
select * from tableName procedure analyse();根据表中数据分析各字段使用情况进行建议优化。由于是根据表中现有数据分析，所以procedure analyse()是基于数据量足够大且查询远大于插入的后期优化
使用“垂直分割”的方式，将表分为定长（字段长度确定：表中没有如下类型的字段：  VARCHAR，TEXT，BLOB）和不定长的两张表
原因：对应定长表的查询时的偏移量方便技术，所以速度快，但是如果定长表和不定长表需要经常联表，则不建议分表，毕竟联表查询效率不如单表查询
利用慢查询日志找出性能低劣的查询


optimize table:optimize table tableName整理表
原因：对表中数据进行大量删除后，会出现数据被删除但数据之前占用的空间没有被释放，而是等待新的数据来占用，使用optimize会重新整理这部分空间，使其被释放。修改存储索引（MyISAM、InnoDB）时也会重新整理空间