1、性能下降SQL慢 执行时间长 等待时间长
1.查询语句写的烂
2.索引失效
单值索引
复合索引
3.关联查询太多 join (设计缺陷或不得已的需求)
4.服务器调优即各个参数的设置(缓冲、线程等)
2、常见通用的join查询
1.SQL执行顺序
手写
机读
总结
2.join 7种方法
1/inner join select <select_list> from tablea a inner join tableb b on a.key = b.key; 2/left join select <select_list> from tablea a left join tableb b on a.key = b.key; 3/right join select <select_list> from tablea a right join tableb b on a.key = b.key 4/左外join select <select_list> from tablea a left join tableb b on a.key = b.key where b.key is null; 5/右外join select <select_list> from tablea a right join tableb b on a.key = b.key where a.key is null; 6/full outer join select <select_list> from tablea a full outer join tableb b on a.key = b.key 7/满外join select <select_list> from tablea a full outer join tableb b where a.key is null or b.key is null
3.索引 简介
1.是什么
mysql官方对索引的定义为:索引(index)是帮助mysql高效获取数据的数据结构。 可以得到索引的本质:索引是数据结构。
1、可以理解为 “排好序的快速查找数据结构”
1. 详解(重要)
2. 结论
数据本身之外,数据库还维护着一个满足特定查找的数据结构,这些数据结构一某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法, 这种数据结构就是索引。
2、一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上,
2.优势
类似图书馆,提高数据检索效率,降低数据库的IO成本,通过索引列对数据进行排序,降低数据排序的成本 和 CPU的消耗
3.劣势
4.mysql 索引分类
1、单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
2、唯一索引
索引列的值必须唯一,但允许有空值 。
3、复合索引
即一个索引包含多个列
4、基本语法
#创建
create [unique]index indexName on mytable(columnname(length)); #unique 表示唯一字段
alter mytable add [unique]index[indexName] on (columnname(length))
#删除
drop index [indexName] on mytable;
#查看
show index from table_nameG
#使用alter 命令
5.mysql索引结构
1、BTree索引
检索原理
2、Hash索引
3、full_text 全文索引
4、R_Tree索引
6那些情况需要创建索引
7,那些情况不需要创建索引
1、表太少
2、经常增删改的表:[ 提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT, UPDATE, DELETE。 因为更新表时,mysql不仅要保存数据,还要保存一下索引文件。]
3、数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据建立索引。【注:如果某个列包含许多重复的内容,为它建立索引就没有太大的实际效果。】
1.mysql query optimizer
2.Mysql 常见瓶颈
- cpu:cpu在饱和的时候(一般发生在数据装入内存,或从内存中读取数据时)
- IO:磁盘IO瓶颈发生(装入数据大于内存容量时)
- 服务器硬件的性能瓶颈:top,tree,iotat,vmstat 查看系统性能状态
3.性能分析
Explain:
- 是什么(查看执行计划):
- 使用Explain关键字模拟优化器执行sql查询语句(分析查询语句,或是表结构的性能瓶颈),mysql是如何执行sql语句的顺序
- 官网分析图: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
- 能干嘛(下面解答,重复阅读本段内容,会有不同体会):
- 表的读取顺序 ?
- 数据读取操作的读取类型 ?
- 某些索引可以使用 ?
- 某些索引被实际使用 ?
- 表之间的引用 ?
- 每张表有多少行被优化器所查询 ?
- 怎么玩:
- Explain + sql语句
- 执行计划包含的信息 -》
- 字段名解释:
- id:重点
- 描述:select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序。
- 三种情况:
- id相同,执行顺序由上至下
- id不同,如果是子查询,id的序号会递增,id值越大,优先级越高,越先被执行。
- id相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。 衍生 =derived
- select_type:
- 描述:查询的类型,主要用于区别 普通查询、联合查询、子查询等
- 值(作用):
- simple:简单的select 查询,查询中不包含子查询或 UNION
- PRIMARY:查询中若包含任何复杂的子查询,最外层查询
- SUBQUERY:在select 或 where 包含子查询
- DERIVED:
- 在 Form 列表中包含的子查询被标记为DERIVED(衍生)
- mysql会递归执行这些子查询,结果放置到临时表中。
- UNION :
- 若第二个Select 出现在UNION 之后,则被标记为:UNION
- 若 UNION 包含在FROM 子句的子查询中,则被标记:DERIVED
- UNION RESULT:从UNION表中获取结果的select
- table :显示这一行是某一张表的。
- type:重点
- 描述:访问类型
- 常用显示排序(最好->最坏):system->const->ref->range->index->All
- 值:
- system:
- 表只用一行记录(等于系统表),这是const类型的特例,平时不会出现(可以忽略)
- const:
- 表示通过索引一次找到,const用于比较primary key 或者unique索引。如果将主键至于where列表中,mysql就能将该查询转换成为一个常量。
- eq_ref:
- 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键,或唯一索引的扫描。
- ref:
- 唯一性索引扫描,返回匹配某个单独只的所有行。
- 本质上也是一种索引访问,它返回所有匹配某个单独值的行,它可能会找到多个条件的行,所以他应该属于查找和扫描的混合体。
- range:
- 只检索给定范围的行,使用一个索引选择行。
- key列显示使用的索引列,一般就是where中出现between、< 、>、in等查询条件。
- 这种范围索引扫描比全表扫描好,因为他只需要开始与索引的某一个点,而到结束的另一个点,不需要全表扫描。
- index:
- Full Index Scan,index与All的区别为index只遍历索引树,通常比All快,因为索引文件通常比数据文件小。(虽然index和all都是读全表,index是从索引中获取,但是All是从磁盘中获取)。
- All:
- Full Table Scan,将全表以找到匹配的行(全表磁盘扫描,多一些IO的操作)。
- 全显示排序(最好->最坏):system->const->eq_ref->ref-f>fulltext->ref_or_null->index_merge->unique_subquery->range->index->All
- 优化:一般来说,得保证查询达到range级别,最好达到ref
- key_len:
- 描述:
- 表示索引中使用的字节数,可通过该列计算查询中的索引长度,长度越短越好。
- key_len 显示的值为索引字段最大的可能长度,并非实际使用长度,即key_len是根据表定义计算面得,不通过表内检索出的。
- 案例:
- 结论:条件越细,key_len越长。
- ref
- 描述:显示索引的某一列被使用。如果可能是一个常数,某些列或常量被用于查找索引列上的值。
- 案例:
- rows 重点
- 描述:根据表统计信息及索引选用情况,大致估算出找到的记录所需要读取的行数。
- 案例:
- Extra 重点
- 描述:包含不适合在其他列中显示,但十分重要的其他信息。
- 值:
- Using fileshort : 重点
- mysql 会对数据库使用一个外部的索引排序,而不是按照表内的索引进行读取。
- mysql 中无法利用索引完成排序的操作成为"文件排序"。
- 案例:
- 案例1:未正确使用索引排序(文件排序)
- 案例2:正确使用索引排序
- Using temporay 重点
- 描述:
- 用临时表保存临时结果,mysql 查询结果使用临时表。
- 常见使用 排序 order by 和 group by 分组查询。
- 案例:
- 案例1:未正确使用索引排序,又使用 group by 或 order by 产生的结果,会严重拖慢数据库的效率。
- 案例2:正确使用索引排序
- Using index 重点
- 描述:
- 表示相应的操作中使用了覆盖索引(Covering index),避免访问,表的数据行,效率不错!
- 如果同时出现Using where 表明 索引备用类执行索引键值的查找
- 如果没有同事出现Using w here 表明 索引用来执行读取数据,而非查找动作。
- 覆盖索引(Covering index)又被称为:索引覆盖
- 描述:
- 理解方式 一:select 的数据链只用从索引中获取,不必读取数据行,mysql 利用索引返回 select 中的字段,而不必根据索引再次读取数据文件,换句话说 查询列 要被所创建的索引覆盖。
- 理解方式 二:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一列的数据,因此它不必读取整行。毕竟索引的叶子节点,存储它们索引的数据,当能通过索引获取数据,就可以获取到所需要的数据。一个索引包含(或覆盖)满足查询结果的数据叫做覆盖索引。
- 注意:
- 使用覆盖索引,注意select 列表中只能取出所需要的列,不可使用select *
- 因为如果将所有的字段一起做索引,会导致索引文件过大,查询性能下降。
- 案例:
- 案例 1:
- 案例 2:
- Using where : 表示使用 where 过滤
- Using join buffer : 表示 使用连接缓存 #如果join比较多,可以把join buffer 调大一点。
- impossible where :
- 描述:where 子句的值总是 false,不能用来获取任何元素。
- 案例:
- 过滤字段重复
- select table optimized away: 了解
- 在没有Group By 子句的情况下,基于索引优化MIN/MAX的操作或者对于 MyISAM 存储引擎优化 count(*) 操作,不必等到执行间断进行计算,查询计算生成间断完成优化。
- distinct:优化distinct操作,在找到第一匹配的元组即停止找同样值的操作。 了解
- 热身case:
- 案例:
4.索引优化
- 1. 索引分析
- 单表:
- 建表SQL:
- 案例(分析好后,才能有优化):
- 俩表: index 时
- 建表:
。。。。。
- 案例:
索引优化(左连接索引添加到右表):
索引优化(右连接索引添加到左表)
- 三表:
- 建表:
- 案例(和俩表连接的结论类似):
结论: join语句的优化: 尽可能减少 join 语句中的NestedLoop循环总数;"永远记住小结果集驱动大结果集"。 尽可能优化 NestedLoop 的内层循环; 保证Join语句中被驱动表驱动表上的 join 字段被索引 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下,不要太吝啬joinBuffer的设置。
- 2. 索引失效(避免)
- 建表 SQL:
- 案例(索引失效)
- 全值匹配我最爱
- 案例:
- 最佳左前缀法则
- 案例:
- 描述:索引多列,遵守最佳左前缀法则(查询从索引最左前列开始,并且不跳过中间的索引列)。
- 不能在索引上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
- 案例:
- 结论:使用计算会导致索引失效
- 存储索引不能使用索引中范围条件右边的列
- 案例:
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select * 操作
- 案例:
- 结论:尽量使用覆盖索引
- mysql 使用(!=或< >)的时候无法使用索引会导致全表扫描。
- 案例:
- is null,is not null 无法使用索引。
- 案例:
- like 以通配符开头('%abc……') mysql索引失效会变成全表扫描的操作。
- 案例:
- 案例 2 生产环境中(like 俩边+%)
- 结论:
- 1. 使用like 'aaa%' ,%加到右边
- 2.如果 like '%ff%',%加俩边,使用覆盖索引(分析覆盖索引的字段 )
- 少用 or ,用or 连接时索引会失效
- 口诀:
- 1.全值匹配我最爱,最左前缀要遵守。
- 2.带头大哥不能死,中间兄弟不能段。
- 3.索引列上少计算,范围之后全失效。
- 4.like 百分写最右,覆盖索引不写*。
- 5.不等空值还有or,索引失效要少用。
- 6.字符串引号不可丢,SQL高级也不难。
小总结
- 面试题讲解:
- 题目SQL
- 定值、范围还是排序,一般 oder by 是给个范围
- group by 基本上都会进行排序,会有临时表的产生。[注:group by 表面是分组,但分组之前必排序]
- 3. 一般性建议:
- 对于单值索引,尽量选择针对当前 query 过滤性更好的索引。
- 在选择组合索引的时候,当前query中过滤最好的字段在索引字段顺序中,位置越靠前 [左] 越好。
- 在选择组合索引的时候,尽量选择可以能够包含当前query中的where子句中更多字段的索引。
- 尽可能通过分析统计信息和调整query的写法来达到合适的索引。