您好,欢迎访问代理记账网站
  • 价格透明
  • 信息保密
  • 进度掌控
  • 售后无忧

SQL优化

一、索引优化分析

性能下降sql慢、执行时间长、等待时间长

①查询语句有问题

②索引失效

③关联查询太多join

④服务器调优及各个参数设置

SQL执行顺序

①手写顺序

SELECT DISTINCT

    <select_list>

FROM

    <left_table> <join_type>

JOIN  <right_table>  ON  <join_condition>

WHERE

    <where_condition>

GROUP BY

    <group_by_list>

HAVING

    <having_condition>

ORDER BY

    <order_by_condition>

LIMIT  <limit_number>

②机读顺序

FROM  <left_table>

ON  <join_condition>

<join_type>  JOIN  <right_table>

WHERE  <where_condition>

GROUP BY  <group_by_list>

HAVING  <having_condition>

SELECT

DISTINCT  <select_list>

ORDER BY  <order_by_condition>

LIMIT  <limit_number>

常见join查询

索引简介

1、简介

索引是帮助MySQL高效获取数据的数据结构,是一种排好序的快速查找数据结构;一般来说索引也很大,不可能全部存储在内存中,因此往往以索引文件的形式存储在磁盘上,平常所说的索引,不特殊说明,都是指B树(多路搜索树)。

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。下图就是一种可能的索引方式示例:

为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。

 

2、优势

①类似于书目索引,提高数据检索效率,降低数据库IO成本

②通过索引列对数据进行排序,降低数据排序成本,降低CPU消耗

3、劣势

①实际上索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也要占空间的

②虽然索引提高了查询速度,却会降低更新速度。因为更新表时,不仅要保存数据,还要调整因更新而变化的索引信息

4、MySQL索引分类

①单值索引:一个索引只包含单个列

②唯一索引:索引列的值必须唯一UNIQUE,但允许有空值

③复合索引:一个索引包含多个列

5、MySQL索引结构

①BTree索引

②Hash索引

③full-text索引

④R-Tree索引

6、哪些情况需要创建索引

①主键自动创建唯一索引

②频繁作为查询条件的字段应该创建索引

③查询中与其它表关联的字段、外键关系建立索引

④频繁更新的字段不适合创建索引,因为每次除了要更新数据,还要更新索引

⑤where条件里用不到的字段不创建索引

⑥单值索引和组合索引,优选组合索引

⑦查询中排序的字段,排序字段若通过索引去访问,大大提高排序速度(要考虑索引字段和有order by需求的字段)

⑧查询中统计或分组的字段,group by的前提是分组

7、哪些情况不需要创建索引

①表记录太少

②经常增、删、改的表

③如果某个数据列包含许多重复的内容,建索引没什么效果

假如一个表有10万行记录,有一个字段S只有F和M两种值,且每个值的分布概率大约为50%,那么对这种表S字段建索引一般不会提高数据库的查询速度。

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近1,这个索引的效率就越高。

性能分析

1、作用:

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理SQL语句

①表的读取顺序

②数据读取操作的类型

③哪些索引可以使用

④哪些索引被实际使用

⑤表之间的引用

⑥每张表有多少行被优化器查询

2、用法

EXPLAIN + SQL

3、执行计划包含的信息

①id

select查询的序列号,表示查询中执行select子句或操作表的顺序

三种情况:

a.id相同,执行顺序由上至下

b.id不同,如果是子查询,id的序号会递增,id值越大优先级越高

c.id既有相同,又有不同:id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行

②select_type

查询类型,主要用于区别普通查询、联合查询、子查询等复杂:

SIMPLE——简单的select查询,查询中不包含子查询或者UNION

PRIMARY——查询中若包含子查询,最外层的查询是PRIMARY

SUBQUERY——在select或where列表中包含子查询

DERIVED——在from列表中包含的子查询被标记为DERIVED,MySQL会递归执行这些子查询,把结果放在临时表里

UNION——若第二个select出现在UNION之后,则被标记为UNION;若UNION包含在from子句的子查询中,外层select被标记为DERIVED

UNION RESULT——从UNION表获取结果的select

③table

显示这一行的数据是关于哪张表的

④partitions

如果查询是基于分区表的话,会显示查询将访问的分区

⑤type

显示查询使用了何种类型,从最好到最差依次是: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,与all的区别是,只遍历索引树,通常比all快,因为索引文件比数据文件小。index是从索引中读取,all是从硬盘中读

all——Full Table Scan,将遍历全表以找到匹配的行

⑥possible_keys

显示可能应用到这张表中的索引,一个或多个。查询涉及到的字段若存在索引,则该索引将被列出,但不一定被查询实际使用

⑦key

实际使用的索引,如果为NULL,则没使用索引;查询中若使用了覆盖索引,该索引仅出现在key列表中

⑧key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精度的情况下,长度越短越好;key_len显示的值为索引字段的最大可能长度,并非实际使用长度,是根据表的定义计算得到,不是通过表内检索出的

⑨ref

显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值

⑩rows

根据表统计信息及索引选用情况,大致估算出找到所需记录需要读取的行数

⑪filtered

表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数

⑫Extra

额外的信息说明

Using filesort——说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”

Using temporary——使用了临时表保存中间结果,MySQL在对查询结果排序时,使用临时表。常见于排序order by和分组查询group by

Using index——表示相应的select操作使用了覆盖索引,避免了访问数据行(覆盖索引:select的数据列只用从索引中就能取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,也就是查询列要被所建的索引覆盖)

Using where——使用了where过滤

using join buffer——使用了连接缓存

impossible where——where sex= 'm' and sex= 'female',where值为false

select tables optimized away

distinct

索引优化

e.g.   ALTER TABLE exampletable ADD INDEX idx_exampletable_nameAgeAddress(name,age,address);

1.全值匹配

2.最佳左前缀法则

如果索引了多列,要遵守最左前缀法则。指查询从索引的最左列开始,并且不跳过索引中的列

①符合最佳左前缀,用上了三个索引:EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND age = 20 AND address='nanjing';

②一个索引都没用上:EXPLAIN SELECT * FROM exampletable WHERE age = 20 AND address='nanjing';

③只用上了第一个NAME索引:EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND address='nanjing';

④能用到NAME的索引:EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx'; 

⑤能用到NAME和age的索引:EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND age = 22; 

3.不要在索引列上做任何操作(计算、函数、自动或手动的类型转换),会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

①三个索引,第二个是范围条件,第二个索引只用到了排序,没用到检索。且第二个之后的索引都失效了:EXPLAIN SELECT * FROM exampletable WHERE NAME='xxx' AND age >25 AND address='nanjing';

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*

6.mysql在使用不等于(!=或<>)时无法使用索引会导致全表扫描

①无法使用索引:EXPLAIN SELECT * FROM exampletable WHERE NAME != 'xxx'; (使用覆盖索引好像也能解决?)

7.is null,is not null也无法使用索引

①无法使用索引:EXPLAIN SELECT * FROM exampletableWHERE NAME is null; (使用覆盖索引好像也能解决?)

8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描

%写在左侧或两侧都写会使索引失效,写在右边不会失效,但可能不满足业务要求,查不到数据,如果必须在两边都写%,那么可以使用覆盖索引来解决索引失效的问题。


分享:

低价透明

统一报价,无隐形消费

金牌服务

一对一专属顾问7*24小时金牌服务

信息保密

个人信息安全有保障

售后无忧

服务出问题客服经理全程跟进