查询与索引优化

1: 慢查询:

  1.  启动慢查询常用配置:
    1. slow_query_log 启动停止记录慢查询日志
    2. slow_query_log_file 指定慢查询日志存储的路径及文件
    3. long_query_time指定记录慢查询日志SQL执行 时间的阈值(单位:秒,默认是10秒)
    4. log_queries_not_using_indexs 是记录没有使用索引的SQL
    5. log_output日志存放的地方[table] [file] [table,file]
  2. 查看配置的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‘;
    2
    show VARIABLES like ‘log_output‘;
    3
    show VARIABLES like ‘slow_query_log_file‘;
记录符合条件的sql  
  1. 查询语句
  2. 数据修改语句
  3. 已经回滚的SQL
慢查询日志的信息描述
  1. 用户名,用户的ip信息,线程id号
  2. 执行花费的时间【单位:毫秒】
  3. 执行获得锁的时间
  4. 获取的结果行数
  5. 扫描的数据行数
  6. sql执行的具体时间
  7. 具体的SQL
慢查询日志分析工具
  1. mysqldumpslow 汇总除查询条件外其他完全相同的SQL,并将分析结果按照参数中所指定的顺序输出
    1. 实例: mysqldumpslow -s r -t 10 slow_mysql.log
    2. -s order(顺序),c:总次数,t: 总时间,l: 锁的时间,r:总数据行,(at,al,ar :t,l ,r平均数),-t top 指定取前面几条最为输出
  2. pt_query_digest

2:索引:

  1.  什么是索引
    1. 索引是帮助Mysql高效获取数据的数据结构
  2. 索引的分类
    1. 普通索引: 一个索引只包含单个列,一个表可以有多个单列索引
    2. 唯一索引: 索引列的值必须唯一,但允许空值
    3. 复合索引:索引是建立在多列之上的。
    4. 聚簇索引:并不是一种单独的索引类型,而是一种数据存储方式,就是数据和索引不是一个文件,分开存储
    5. 非聚簇索引: 不是聚簇索引的都是
  3. 什么列适合做索引
    1. 某一列数据唯一(重复数据不多)
    2. 经常用来查询显示的列
    3. 经常用来关联的列 例如: where 条件中使用的列

3:执行计划

  1. 执行顺序
    1. id 相同,看table的顺序
    2. id 不同,id越大越先执行
    3. id如果相同,可以认为是一组,从上往下顺序执行,id 越大,越先执行
  2. 执行计划--select_type
    1. simple  简单的select 查询,查询中不包含子查询或者UNION
    2. primary 查询中若包含任何复杂的子部分,最外层查询则被标记为
    3. subquery 在select 或者where列表中包含子查询
    4. derived 在from列表中包含的子查询被标记为derived(衍生),Mysql会递归执行这些子查询,把结果放到临时表中
    5. union  若第二个select 出现在UNION之后,则被标记为UNION;若UNION包含在from子句的子查询中,外层select 将被标记为DERIVED
    6. union result 从union表获取结果的select
  3. 执行计划--type
    1. 最好到 最坏 : system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > All
    2. 常用的 :  system > const > eq_ref > ref > range > index >all
      1. const :通过索引一次查询到
      2. eq_ref :唯一索引扫描,对于每一个索引建,表中只有一条记录与之匹配,常见于主键或者唯一索引扫描
      3. ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。本质压实一种索引访问,返回所有匹配某个单独值的行,有可能有多个复合条件的行。
      4. range: 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引,一般就是在你的where语句中出现了between,>, < ,in等查询。这种范围扫描比全表扫描要好。因为只需要开始于索引的某一点,而结束语另外一点。不用扫描全部索引.。
      5. index :使用索引列(使用了整个索引文件,全表扫描的一种)
  4. 执行计划—key :使用的索引,为null, 没有使用索引。
  5. 执行计划--key_len :表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精度的情况下,长度越短越少。其显示的值为索引字段的最大可能长度,并非实际使用长度。即key_len是根据表定义计算而得,不是通过表内检索出来的。
  6. 执行计划--Extra
    1. using filesort :说明mysql对数据使用了一个外部的索引排序,而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作。称为‘‘文件排序‘‘
    2. Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by
    3. using index 是否用了覆盖索引。 如果查询字段中没有有索引字段,可能会出现索引覆盖的问题。一般情况写查询结果的时候,将索引字段放到查询结果中。

4: SQL优化

  1.   SQL优化策

    1. 尽量全值匹配 :就是索引列,在where条件中,尽可能使用索引列
    2. 最佳左前缀法则:指的是查询从索引的最左前列开始,并且不跳过索引中的列。作用是防止索引不失效。
    3. 不在索引列上做任何操作
    4. 范围条件放到最后,原因: 使用了范围查询,会导致范围查询以后的索引失效
    5. 覆盖索引尽量使用; 只访问索引的查询列(尽可能使用索引列),减少select * 
    6. 不等于要慎用,使用不等于的时候,无法使用索引会引起全表扫描
    7. null/not null 对索引可能影响(字段本身是可以为空的,使用is null不会索引失效,使用is  not null会索引失效。字段本身是不可以为空(not null),使用is null 使用了索引  ,使用is not null 索引会失效)
  2. 葵花宝典

  3. 全职匹配我最爱,
    全值匹配我最爱, 最左前缀要遵循
    带头大哥不能死,中间兄弟不能断
    索引列上少计算,范围之后全失效
    like百分写最右,覆盖索引不写*
    不等空值还有OR,索引影响要注意
    varchar引号不能丢,SQL优化有诀窍
    
    
     
    
    
    
    
    1
    全值匹配我最爱, 最左前缀要遵循
    
    2
    带头大哥不能死,中间兄弟不能断
    3
    索引列上少计算,范围之后全失效
    4
    like百分写最右,覆盖索引不写*
    5
    不等空值还有OR,索引影响要注意
    6
    varchar引号不能丢,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;
         
  

相关推荐