性能下降SQL慢 执行时间长 等待时间长 可能原因:
查询数据过多
关联了太多的表,太多join
没有利用到索引:索引针对 列 建索引。但并不可能每一列都建索引 索引并非越多越好。当数据更新了,索引会进行调整。也会很消耗性能。 且 mysql 并不会把所有索引都用上,只会根据其算法挑一个索引用。所以建的准很重要。
服务器调优及各个参数设置(缓冲、线程数等)
索引:MySQL官方对索引的定义为,索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。
你可以简单理解为“排好序的快速查找数据结构”。 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈稀索引(hash index)等。
优势:类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本,通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
劣势:实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句
mysql索引分类:
主键索引:设定为主键后数据库会自动建立索引,innodb为聚簇索引
单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
唯一索引:索引列的值必须唯一,但允许有空值
复合索引:即一个索引包含多个列,在数据库操作期间,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)
当表的行数远大于索引列的数目时可以使用复合索引
基本语法:创建 ALTER mytable ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除:DROP INDEX [indexName] ON mytable;
查看:SHOW INDEX FROM table_name\G
需要建立索引的情况:主键自动建立唯一索引。频繁作为查询条件的字段应该创建索引(where 后面的语句)。查询中与其它表关联的字段,外键关系建立索引。单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引),查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。查询中统计或者分组字段。
哪些情况不要创建索引:表记录太少,
经常增删改的表:Why:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
Where条件里用不到的字段不创建索引
数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。
注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
mysql常见的瓶颈:
cpu:SQL中对大量数据进行比较、关联、排序、分组
io:实例内存满足不了缓存数据或排序等需要,导致产生大量 物理 IO。查询执行效率低,扫描过多数据行。
锁:不适宜的锁的设置,导致线程阻塞,性能下降。死锁,线程之间交叉调用资源,导致死锁,程序卡住。
或者服务器硬件性能不行。
优化原理:
可以通过使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
通过explain出的数据进行分析得出如下信息:
表的读取顺序,哪些索引可以使用,数据读取操作的操作类型,哪些索引被实际使用,表之间的引用,每张表有多少行被优化器查询
名次段解释:
id:
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
id相同,执行顺序由上至下,id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
id相同不同,同时存在.id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行
select_type:
查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
1.SIMPLE 简单的 select 查询,查询中不包含子查询或者UNION
2.PRIMARY 查询中若包含任何复杂的子部分,最外层查询则被标记为Primary
3.DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
4.SUBQUERY 在SELECT或WHERE列表中包含了子查询
5.DEPENDENT SUBQUERY 在SELECT或WHERE列表中包含了子查询,子查询基于外层
6.UNCACHEABLE SUBQUREY 无法被缓存的子查询
7.union 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
8.union result 从UNION表获取结果的SELECT
type:
type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL
system>const>eq_ref>ref>range>index>ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
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快,因为索引文件通常比数据文件小。
(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
all:Full Table Scan,将遍历全表以找到匹配的行
index_merge:在查询过程中需要多个索引组合使用,通常出现在有 or 的关键字的sql中
ref_or_null:对于某个字段既需要关联条件,也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
index_subquery:利用索引来关联子查询,不再全表扫描。
unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引
possible_keys:显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
key:实际使用的索引。如果为NULL,则没有使用索引.查询中若使用了覆盖索引,则该索引和查询的select字段重叠
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。
key_len字段能够帮你检查是否充分的利用上了索引
ref:显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值
rows:rows列显示MySQL认为它执行查询时必须检查的行数。越少越好
exra:包含不适合在其他列中显示但十分重要的额外信息
Using filesort :说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
给ename加上排序(0.00s)即在索引中排序
Using temporary :使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
create index idx_deptno_ename on emp(deptno,ename) 后解决
USING index:表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引只是用来读取数据而非利用索引执行查找。
覆盖索引:索引是高效找到行的一个方法,但是一般数据库也能使用索引找到一个列的数据,因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据,那就不需要读取行了。①一个索引 ②包含了(或覆盖了)[select子句]与查询条件[Where子句]中 ③所有需要的字段就叫做覆盖索引。
例如:
select id , name from t_xxx where age=18;
有一个组合索引 idx_id_name_age_xxx 包含了(覆盖了),id,name,age三个字段。查询时直接将建立了索引的列读取出来了,而不需要去查找所在行的其他数据。所以很高效。
(个人认为:在数据量较大,固定字段查询情况多时可以使用这种方法。)
注意:
如果要使用覆盖索引,一定要注意select列表中只取出需要的列,不可select *,因为如果将所有字段一起做索引会导致索引文件过大,查询性能下降。
Using where:过滤where
using join buffer:使用了连接缓存
出现在当两个连接时驱动表(被连接的表,left join 左边的表。inner join 中数据少的表) 没有索引的情况下。给驱动表建立索引可解决此问题。且 type 将改变成 ref
impossible where:where子句的值总是false
索引失效案例:
1.全值匹配最好
2.最佳左前缀法则,如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
(带头大哥不能死,不能跳过中间列)
and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在,会自动优化。
经过试验结论 建立了 idx_nameAge 索引 id 为主键
1.当使用覆盖索引的方式时,(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件)),即使不是以 name 开头,也会使用 idx_nameAge 索引。
既 select 后的字段 有索引,where 后的字段也有索引,则无关执行顺序。
2.除开上述条件 才满足最左前缀法则。
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select
6.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
使用 != 和 <> 的字段索引失效( != 针对数值类型。 <> 针对字符类型
前提 where and 后的字段在混合索引中的位置比比当前字段靠后 where age != 10 and name='xxx' ,这种情况下,mysql自动优化,将 name='xxx' 放在 age !=10 之前,name 依然能使用索引。只是 age 的索引失效)
7.is not null 也无法使用索引,但是is null是可以使用索引的
8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
like ‘%abc%’ type 类型会变成 all
like ‘abc%’ type 类型为 range ,算是范围,可以使用索引
如果真的要查%xx%就用覆盖索引,其中主键也会用到index。
9.字符串不加单引号索引失效
10.少用or,用它来连接时会索引失效
一般性建议:对于单键索引,尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候,当前Query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。(避免索引过滤性好的索引失效)
在选择组合索引的时候,尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
优化总结口诀:
全值匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上无计算,范围之后全失效;
like百分加右边,覆盖索引不写星*
不等空值还有or,索引失效要少用;
字符串里有引号,SQL高级也不难