1. mysql主要架构
1.1 主要配置文件
1)二进制日志log-bin:其主要作用用于主从复制
2)错误日志log-error:默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等
3)查询日志log:默认关闭,记录查询的SQL语句,如果开启会降低mysql的整体性能,因为记录日志需要消耗系统资源
4)数据文件
frm文件:存放表结构
myd文件:存放表数据
myi文件:存放表索引
1.2 mysql的逻辑架构
1)连接层:最上层是一些客户端的连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2)服务层:第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如存储过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化。如,确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统性能
3)引擎层:存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信,不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。
4) 存储层:数据出处层,主要是将数据存储在运行与裸设备的文件系统上,并完成与存储引擎的交互。
1.3 存储引擎简介
1.3.1 查看存储引擎
1)查看存储引擎:SHOW ENGINES
2)查看默认的存储引擎:SHOW VARIABLES LIKE '%storage_engine%'
1.3.2 mysql两种常用的存储引擎对比
2. SQL性能 下降的原因
1. 查询语句写的烂
2. 索引失效
3. 关联查询太多join(设计缺陷或者不得已的需求)
4. 服务器调优及各个参数设置(缓冲、线程数等)
3. 常见通用join
3.1 SQL执行顺序
3.1.1 手写
3.1.2 机读
总结:
3.2 七种join理论
3.2.1 left join
3.2.2 inner join
3.2.3 right join
3.2.4 left join(A独有)
3.2.5 right join(B独有)
3.2.6 outer join
3.2.7 full outer join
注意:MySQL不支持full outer join这种语法。
4. 索引
一般而言,一张表建立的索引不要超过5个
4.1 简介
索引(index)是帮助mysql高效获取数据的数据结构,索引的本质是一种“排好序的快速查找的数据结构”。一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引
其中聚集索引,次要索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然除了B+树这种类型的索引之外,还有哈希索引(hash-index)等。
注意:对于经常进行update的字段不适合建索引
4.2 索引优势劣势
1)优势:提高数据检索的效率,降低数据库的IO成本,通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
2)劣势:
2.1 索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是要占用空间的。
2.2 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行insert、update、delete,因为对表的更新,mysql不仅要保存数据,还要保存索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
2.3 索引只是提高效率的一个因素,如果你的mysql有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询
4.3 索引的分类
4.3.1 单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
4.3.2 唯一索引
索引列的值必须唯一,但允许有空值
4.3.3 复合索引
一个索引包含多个列
4.4 基本语法
4.4.1 创建
1)CREATE [UNIQUE] INDEX indexName ON myTable(colName(length));
2)ALTER myTable Add [UNIQUE] INDEX [indexName] ON (colName(length))
4.4.2 删除
DROP INDEX [indexname] on myTable
4.4.3 查看
SHOW INDEX FROM table_name
4.4.4 使用alter命令
添加主键:ALTER TABLE tabName ADD PRIMARY KEY(colm_list)
添加唯一索引(除了NULL以外,NULL可能会出现多次):ALTER TABLE tabName ADD UNIQUE index_name(colm_list)
添加普通索引,索引值可以出现多次:ALTER TABLE tabName ADD INDEX index_name(colm_list)
指定索引为FULLTEXT,用于全文索引:ALTER TABLE tabName ADD FULLTEXT index_name(colm-list)
4.4 索引的使用
4.4.1 哪些情况需要建索引
1)主键自动建立唯一索引
2)频繁作为查询条件的字段应该创建索引
3)查询中与其它表关联的字段,外键关系建立索引
4)频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引
5)where条件里用不到的字段不创建索引
6)单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
7)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
8)查询中统计或者分组字段
4.4.2 哪些情况不要创建索引
1)表记录太少
2)经常增删改的表:因为虽然会提高查询速度,但是同时却会降低更新表的速度,如对表进行insert、update和delete。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
3)数据重复且分布平均的表字段:如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果,因此应该只为最经常查询和最经常排序的数据列建立索引
注意:
假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列不同值的数目与表中记录数的比,如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99,一个索引的选择性越接近1,这个索引的效率就越高
4.5 性能分析
4.5.1 mysql查询解析器 MySQL Query Optimizer
MySql中有专门负责优化select语句的优化器模块,主要功能;通过计算分析系统中收集到的统计信息,为客户端请求的Query提供它认为最优的执行计划(它认为最优的数据检索方式,但不一定是DBA认为的最优的方式,这部分最耗时间)
当客户端向mysql请求一条query,命令解析器模块完成请求分类,区别出是select并转发给mysql query optimizer时,mysql query optimizer首先会对整条query进行优化,处理掉一些常量表达式的预算,直接换算层常量值。并对query中的查询条件进行简化和转换,如去掉一些无用或显而易见的条件、结构调整等。然后分析query中hint信息(如果有),看显示Hint信息是否可以完全确定该query的执行计划。如果没有hint或hint信息还不足以完全确定执行计划,则会读取所涉及对象的统计信息,根据query进行写相应的计算分析,然后再得出最后的执行计划
4.5.2 MySQL常见瓶颈
1)CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
2)IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3)服务器硬件的性能瓶颈:top、free、iostat和vmstat来查看系统的性能状态
4.5.3 Explain
5. mysql优化——Explain详解
5.1 简介
explain是用来查看执行计划的。使用explain关键字可以模拟优化器执行SQL查询语句,从而知道mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
通过explain可以知道,表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少被优化器查询等。
5.2 使用语法
EXPLAIN SQL语句
5.3 执行计划包含信息各个字段解释
5.3.1 id(表示表的读取和加载执行顺序)
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
三种情况:
1)id相同,执行顺序由上至下
2)id不同,如果是子查询,id的序列号会递增,id值越大优先级越高,越先被执行
3)id相同不同,同时存在
id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行,因此这个执行顺序是:t3——> s1 ——> t2
衍生 = DERIVED
上图中 蓝色的2就是表示id=2
5.3.2 select_type(数据读取操作的操作类型)
表示查询类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。
常见常用的值:
1)SIMPLE:简单的select查询,查询中不包含子查询或者UNION
2)PRIMARY:查询中若包含任何复杂的子部分,最外层查询则被标记为primary,即primary一般是最后加载的
3)SUBQUERY:在select或where列表中包含了子查询
4)DERIVED:在from列表中包含的子查询被标记为DERIVED(衍生),mysql会递归执行这些查询,把结果放在临时表中
注意:临时表有时候会增加系统负担,但是有时候又不得不用。
5)UNION:若第二个select出现在UNION之后,则被标记为UNION;若UNION包含在from子句的子查询中,外层select将被标记为DERIVED
6)UNION RESULT:从UNION表获取结果的select
5.3.3 table
显示该行数据是来自哪张表
5.3.4 partitions
5.3.5 type
type显示的是访问类型,是一个较为重要的指标,显示查询使用了何种类型
一共有如下几种可选值:
1)system:表只有一条记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计
2)const :表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。入,将主键置于where列表中,mysql就能将该查询转换成一个常量
3)eq_ref :唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
4)ref :非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
5)range :只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where语句中出现了between、<、>、in等的查询,这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不同扫描全部索引
6)index :Full INDEX Scan,index与All区别为index类型只遍历索引树,这通常要比All快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但是index是从索引中读取的,而all是从硬盘中读取的)
7)ALL :Full Table Scan,将遍历全表以找到匹配的行
type值从好到坏,完整的结果顺序为:system> const> eq_ref> fulltext > ref_or_null > index-merge > unique_subquery > index_qbquery > range > index > ALL
通常,好些值在实际中很难遇到,常用的顺序从最好到最差依次是: system>const>eq_ref>ref>range>index>ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
5.3.6 possible_keys
显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
5.3.7 key
实际使用的索引。如果为null,则没有使用索引(没有建立索引或者索引失效)。查询中若使用了覆盖索引,则该索引仅出现在key列表中
5.3.8 key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好,key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
5.3.9 ref
显示索引的哪一列被使用了,如果可能的话,是一个常数,哪些列或常量被用于查找索引列上的值
由key_len可知t1表的idx_col1_col2被充分使用,col1匹配t2表的col1,col2匹配了一个常量,即'ac'
结论:查询中与其它表关联的字段,外键关系建立索引
5.3.10 rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
5.3.11 extra(扩展的)
包含不适合在其他列中显示但十分重要的额外信息
1)Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”
2)Using temporary:使用了用临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于排序order by 和 分组查询group by。
注意:出现该提示,SQL就需要优化了,往往拖慢SQL查询的元凶就是它。
3)USING index:表示相应的select操作中使用了覆盖索引(Covering index),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
索引覆盖(Covering index):就是select的数据列只用从索引中就能够取得,不必读取数据行,mysql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说,查询列要被所建的索引覆盖
另一种理解:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行,毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了,一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引
注意:如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select*,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降
4)using where:表明使用了where过滤
5)using join buffer:表示使用了连接缓存
6)impossible where:表示where子句的值总是false,不能用来获取任何元组
7)select tables optimized away:在没有group by子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段在进行计算,查询执行计划生成的阶段即完成优化
8)distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作
5.4 练习
分析该语句的执行顺序