Class: Shiba::Explain

Inherits:
Object
  • Object
show all
Extended by:
CheckSupport::ClassMethods
Includes:
CheckSupport
Defined in:
lib/shiba/explain.rb,
lib/shiba/explain/checks.rb,
lib/shiba/explain/result.rb,
lib/shiba/explain/check_support.rb,
lib/shiba/explain/mysql_explain.rb,
lib/shiba/explain/postgres_explain.rb

Defined Under Namespace

Modules: CheckSupport Classes: Checks, MysqlExplain, PostgresExplain, Result

Constant Summary collapse

COST_PER_ROW_READ =

TBD; data size would be better

2.5e-07
COST_PER_ROW_SORT = 
1.0e-07
COST_PER_ROW_RETURNED = 
3.0e-05
COST_PER_KB_RETURNED = 
0.0004
IGNORE_PATTERNS = 
[
  /No tables used/,
  /Impossible WHERE/,
  /Select tables optimized away/,
  /No matching min\/max row/
]

Instance Method Summary collapse

Methods included from CheckSupport::ClassMethods

check, get_checks

Methods included from CheckSupport

#_run_checks!

Constructor Details

#initialize(query, stats, options = {}) ⇒ Explain

Returns a new instance of Explain.



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
 
# File 'lib/shiba/explain.rb', line 20

def initialize(query, stats, options = {})
  @query = query
  @sql = query.sql

  @backtrace = query.backtrace

  if options[:force_key]
    @sql = @sql.sub(/(FROM\s*\S+)/i, '\1' + " FORCE INDEX(`#{options[:force_key]}`)")
  end

  @options = options

  @explain_json, @select_fields = Shiba.connection.explain(@sql)

  if Shiba.connection.mysql?
    @rows = Shiba::Explain::MysqlExplain.new.transform_json(@explain_json['query_block'])

    # For simple queries, use original table name rather than the alias
    if @select_fields.keys.size == 1
      table = @select_fields.keys.first
      if @rows.first['table'] != table
        @rows.first['table'] = table
      end
    end
  else
    @rows = Shiba::Explain::PostgresExplain.new(@explain_json).transform
  end
  @result = Result.new
  @stats = stats

  run_checks!
end

Instance Method Details

#as_json ⇒ Object 



53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/shiba/explain.rb', line 53

def as_json
  {
    sql: @sql,
    table: @query.from_table,
    md5: @query.md5,
    messages: @result.messages,
    global: global,
    cost: @result.cost,
    severity: severity,
    raw_explain: humanized_explain,
    backtrace: @backtrace
  }
end

#check_fuzzed ⇒ Object 



157
158
159
160
161
162
163
164
165
166
167
168
 
# File 'lib/shiba/explain.rb', line 157

def check_fuzzed
  h = {}
  @rows.each do |row|
    t = row['table']
    if @stats.fuzzed?(t)
      h[t] = @stats.table_count(t)
    end
  end
  if h.any?
    @result.messages << { tag: "fuzzed_data", tables: h }
  end
end

#check_no_matching_row_in_const_table ⇒ Object 



134
135
136
137
138
139
140
 
# File 'lib/shiba/explain.rb', line 134

def check_no_matching_row_in_const_table
  if no_matching_row_in_const_table?
    @result.messages << { tag: "access_type_const", table: @query.from_table }
    first['key'] = 'PRIMARY'
    @result.cost = 0
  end
end

#check_query_is_ignored ⇒ Object 



126
127
128
129
130
131
 
# File 'lib/shiba/explain.rb', line 126

def check_query_is_ignored
  if ignore?
    @result.messages << { tag: "ignored" }
    @result.cost = 0
  end
end

#check_query_shortcircuits ⇒ Object 



150
151
152
153
154
 
# File 'lib/shiba/explain.rb', line 150

def check_query_shortcircuits
  if first_extra && IGNORE_PATTERNS.any? { |p| first_extra =~ p }
    @result.cost = 0
  end
end

#check_return_size ⇒ Object 



180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
 
# File 'lib/shiba/explain.rb', line 180

def check_return_size
  if @query.limit
    result_size = [@query.limit, @result.result_size].min
  elsif @query.aggregation?
    result_size = 1
  else
    result_size = @result.result_size
  end

  result_bytes = select_row_size * result_size
  cost = (result_bytes / 1024.0) * COST_PER_KB_RETURNED

  @result.cost += cost
  @result.messages << { tag: "retsize", result_size: result_size, result_bytes: result_bytes, cost: cost }
end

#cost ⇒ Object 



77
78
79
 
# File 'lib/shiba/explain.rb', line 77

def cost
  @result.cost
end

#first ⇒ Object 



81
82
83
 
# File 'lib/shiba/explain.rb', line 81

def first
  @rows.first
end

#first_extra ⇒ Object 



85
86
87
 
# File 'lib/shiba/explain.rb', line 85

def first_extra
  first["Extra"]
end

#global ⇒ Object 



67
68
69
70
71
 
# File 'lib/shiba/explain.rb', line 67

def global
  {
    server: Shiba.connection.mysql? ? 'mysql' : 'postgres'
  }
end

#humanized_explain ⇒ Object 



214
215
216
217
218
219
 
# File 'lib/shiba/explain.rb', line 214

def humanized_explain
  #h = @explain_json['query_block'].dup
  #%w(select_id cost_info).each { |i| h.delete(i) }
  #h
  @explain_json
end

#ignore? ⇒ Boolean

Returns:

  • (Boolean) 


106
107
108
 
# File 'lib/shiba/explain.rb', line 106

def ignore?
  !!ignore_line_and_backtrace_line
end

#ignore_line_and_backtrace_line ⇒ Object 



110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/shiba/explain.rb', line 110

def ignore_line_and_backtrace_line
  ignore_files = Shiba.config['ignore']
  if ignore_files
    ignore_files.each do |i|
      file, method = i.split('#')
      @backtrace.each do |b|
        next unless b.include?(file)
        next if method && !b.include?(method)
        return [i, b]
      end
    end
  end
  nil
end

#messages ⇒ Object 



73
74
75
 
# File 'lib/shiba/explain.rb', line 73

def messages
  @result.messages
end

#no_matching_row_in_const_table? ⇒ Boolean

Returns:

  • (Boolean) 


89
90
91
 
# File 'lib/shiba/explain.rb', line 89

def no_matching_row_in_const_table?
  first_extra && first_extra =~ /no matching row in const table/
end

#other_paths ⇒ Object 



221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/shiba/explain.rb', line 221

def other_paths
  if Shiba.connection.mysql?
    @rows.map do |r|
      next [] unless r['possible_keys']
      possible = r['possible_keys'] - [r['key']]
      possible.map do |p|
        Explain.new(@query, @stats, force_key: p) rescue nil
      end.compact
    end.flatten
  else
    []
  end
end

#run_checks! ⇒ Object 



196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
 
# File 'lib/shiba/explain.rb', line 196

def run_checks!
  # first run top-level checks
  _run_checks! do
    :stop if @result.cost
  end

  return if @result.cost

  @result.cost = 0
  # run per-table checks
  0.upto(@rows.size - 1) do |i|
    check = Checks.new(@rows, i, @stats, @options, @query, @result)
    check.run_checks!
  end

  check_return_size
end

#select_row_size ⇒ Object 



170
171
172
173
174
175
176
177
178
 
# File 'lib/shiba/explain.rb', line 170

def select_row_size
  size = 0
  @select_fields.each do |table, fields|
    fields.each do |f|
      size += @stats.get_column_size(table, f) || 0
    end
  end
  size
end

#severity ⇒ Object 



93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/shiba/explain.rb', line 93

def severity
  case @result.cost
  when 0..0.01
    "none"
  when 0.01..0.10
    "low"
  when 0.1..1.0
    "medium"
  else
    "high"
  end
end