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)时也会重新整理空间