查询与索引优化
1: 慢查询:
- 启动慢查询常用配置:
- slow_query_log 启动停止记录慢查询日志
- slow_query_log_file 指定慢查询日志存储的路径及文件
- long_query_time指定记录慢查询日志SQL执行 时间的阈值(单位:秒,默认是10秒)
- log_queries_not_using_indexs 是记录没有使用索引的SQL
- log_output日志存放的地方[table] [file] [table,file]
- 查看配置的sql
show VARIABLES like ‘slow_query_log‘; show VARIABLES like ‘log_output‘; show VARIABLES like ‘slow_query_log_file‘; 3 1 show VARIABLES like ‘slow_query_log‘;
2show VARIABLES like ‘log_output‘;
3show VARIABLES like ‘slow_query_log_file‘;
- 查询语句
- 数据修改语句
- 已经回滚的SQL
- 用户名,用户的ip信息,线程id号
- 执行花费的时间【单位:毫秒】
- 执行获得锁的时间
- 获取的结果行数
- 扫描的数据行数
- sql执行的具体时间
- 具体的SQL
- mysqldumpslow 汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出
- 实例: mysqldumpslow -s r -t 10 slow_mysql.log
- -s order(顺序),c:总次数,t: 总时间,l: 锁的时间,r:总数据行,(at,al,ar :t,l ,r平均数),-t top 指定取前面几条最为输出
- pt_query_digest
2:索引:
- 什么是索引
- 索引是帮助Mysql高效获取数据的数据结构
- 索引的分类
- 普通索引: 一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引: 索引列的值必须唯一,但允许空值
- 复合索引:索引是建立在多列之上的。
- 聚簇索引:并不是一种单独的索引类型,而是一种数据存储方式,就是数据和索引不是一个文件,分开存储
- 非聚簇索引: 不是聚簇索引的都是
- 什么列适合做索引
- 某一列数据唯一(重复数据不多)
- 经常用来查询显示的列
- 经常用来关联的列 例如: where 条件中使用的列
3:执行计划
- 执行顺序
- id 相同,看table的顺序
- id 不同,id越大越先执行
- id如果相同,可以认为是一组,从上往下顺序执行,id 越大,越先执行
- 执行计划--select_type
- simple 简单的select 查询,查询中不包含子查询或者UNION
- primary 查询中若包含任何复杂的子部分,最外层查询则被标记为
- subquery 在select 或者where列表中包含子查询
- derived 在from列表中包含的子查询被标记为derived(衍生),Mysql会递归执行这些子查询,把结果放到临时表中
- union 若第二个select 出现在UNION之后,则被标记为UNION;若UNION包含在from子句的子查询中,外层select 将被标记为DERIVED
- union result 从union表获取结果的select
- 执行计划--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
- const :通过索引一次查询到
- eq_ref :唯一索引扫描,对于每一个索引建,表中只有一条记录与之匹配,常见于主键或者唯一索引扫描
- ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。本质压实一种索引访问,返回所有匹配某个单独值的行,有可能有多个复合条件的行。
- range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了between,>, < ,in等查询。这种范围扫描比全表扫描要好。因为只需要开始于索引的某一点,而结束语另外一点。不用扫描全部索引.。
- index :使用索引列(使用了整个索引文件,全表扫描的一种)
- 执行计划—key :使用的索引,为null, 没有使用索引。
- 执行计划--key_len :表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精度的情况下,长度越短越少。其显示的值为索引字段的最大可能长度,并非实际使用长度。即key_len是根据表定义计算而得,不是通过表内检索出来的。
- 执行计划--Extra
- using filesort :说明mysql对数据使用了一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作。称为‘‘文件排序‘‘
- Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by
- using index 是否用了覆盖索引。 如果查询字段中没有有索引字段,可能会出现索引覆盖的问题。一般情况写查询结果的时候,将索引字段放到查询结果中。
4: SQL优化
SQL优化策
- 尽量全值匹配 :就是索引列,在where条件中,尽可能使用索引列
- 最佳左前缀法则:指的是查询从索引的最左前列开始,并且不跳过索引中的列。作用是防止索引不失效。
- 不在索引列上做任何操作
- 范围条件放到最后,原因: 使用了范围查询,会导致范围查询以后的索引失效
- 覆盖索引尽量使用; 只访问索引的查询列(尽可能使用索引列),减少select *
- 不等于要慎用,使用不等于的时候,无法使用索引会引起全表扫描
- null/not null 对索引可能影响(字段本身是可以为空的,使用is null不会索引失效,使用is not null会索引失效。字段本身是不可以为空(not null),使用is null 使用了索引 ,使用is not null 索引会失效)
葵花宝典
- 全职匹配我最爱,
全值匹配我最爱, 最左前缀要遵循 带头大哥不能死,中间兄弟不能断 索引列上少计算,范围之后全失效 like百分写最右,覆盖索引不写* 不等空值还有OR,索引影响要注意 varchar引号不能丢,SQL优化有诀窍 1 全值匹配我最爱, 最左前缀要遵循 2 带头大哥不能死,中间兄弟不能断
3索引列上少计算,范围之后全失效
4like百分写最右,覆盖索引不写*
5不等空值还有OR,索引影响要注意
6varchar引号不能丢,SQL优化有诀窍
3: 批量导入:
导出数据文件 select * from my_user into OUTFILE ‘E:\t.txt‘; 导入数据 LOAD DATA INFILE ‘E:\my_user.txt‘ INTO TABLE my_user1; x
1
导出数据文件
2
select * from my_user into OUTFILE ‘E:\t.txt‘;
3
4
导入数据
5
LOAD DATA INFILE ‘E:\my_user.txt‘ INTO TABLE my_user1;
相关推荐
moyekongling 2020-11-13
chenjiazhu 2020-09-29
liuweiq 2020-09-09
silencehgt 2020-09-07
mrandy 2020-08-15
Accpcjg 2020-08-02
bluetears 2020-07-05
bendan 2020-07-04
minggehenhao 2020-07-04
AngelicaA 2020-07-04
ribavnu 2020-11-16
坚持是一种品质 2020-11-16
kikaylee 2020-10-31
Ida 2020-09-16
sunnyxuebuhui 2020-09-07
西瓜皮儿的皮儿 2020-09-07
LuckyLXG 2020-09-08