Module: SqlGenius::Core::QueryBuilders::Mysql

Extended by:
Mysql
Included in:
Mysql
Defined in:
lib/sql_genius/core/query_builders/mysql.rb

Overview

MySQL / MariaDB query builder. Contains all SQL previously embedded in the Analysis classes prior to PostgreSQL support being added.

Constant Summary collapse

QUERY_STATS_NOISE_FILTERS = 
<<~SQL
  DIGEST_TEXT NOT LIKE 'EXPLAIN%'
  AND DIGEST_TEXT NOT LIKE '%`information_schema`%'
  AND DIGEST_TEXT NOT LIKE '%`performance_schema`%'
  AND DIGEST_TEXT NOT LIKE '%information_schema.%'
  AND DIGEST_TEXT NOT LIKE '%performance_schema.%'
  AND DIGEST_TEXT NOT LIKE 'SHOW %'
  AND DIGEST_TEXT NOT LIKE 'SET STATEMENT %'
  AND DIGEST_TEXT NOT LIKE 'SELECT VERSION ( )%'
  AND DIGEST_TEXT NOT LIKE 'SELECT @@%'
SQL

Instance Method Summary collapse

Instance Method Details

#digest_column_available?(connection) ⇒ Boolean

Returns:

  • (Boolean) 


155
156
157
158
159
160
161
162
163
164
165
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 155

def digest_column_available?(connection)
  result = connection.exec_query(
    "SELECT COLUMN_NAME FROM information_schema.COLUMNS " \
      "WHERE TABLE_SCHEMA = 'performance_schema' " \
      "AND TABLE_NAME = 'events_statements_summary_by_digest' " \
      "AND COLUMN_NAME = 'DIGEST'",
  )
  !result.rows.empty?
rescue StandardError
  false
end

#digest_text_lookup(connection, digest:) ⇒ Object 



145
146
147
148
149
150
151
152
153
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 145

def digest_text_lookup(connection, digest:)
  quoted_digest = connection.quote(digest)
  <<~SQL
    SELECT DIGEST_TEXT
    FROM performance_schema.events_statements_summary_by_digest
    WHERE DIGEST = #{quoted_digest}
    LIMIT 1
  SQL
end

#drop_index_sql(table:, index_name:) ⇒ Object 



121
122
123
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 121

def drop_index_sql(table:, index_name:)
  "ALTER TABLE `#{table}` DROP INDEX `#{index_name}`;"
end

#query_history(connection, digest:) ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 125

def query_history(connection, digest:)
  quoted_digest = connection.quote(digest)
  quoted_db = connection.quote(connection.current_database)
  <<~SQL
    SELECT DIGEST_TEXT,
           COUNT_STAR AS calls,
           ROUND(SUM_TIMER_WAIT / 1000000000.0, 2) AS total_time_ms,
           ROUND(AVG_TIMER_WAIT / 1000000000.0, 2) AS avg_time_ms,
           ROUND(MAX_TIMER_WAIT / 1000000000.0, 2) AS max_time_ms,
           SUM_ROWS_EXAMINED AS rows_examined,
           SUM_ROWS_SENT AS rows_sent,
           FIRST_SEEN,
           LAST_SEEN
    FROM performance_schema.events_statements_summary_by_digest
    WHERE DIGEST = #{quoted_digest}
      AND SCHEMA_NAME = #{quoted_db}
    LIMIT 1
  SQL
end

#query_stats(connection, order_clause:, limit:, include_digest:) ⇒ Object 



42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 42

def query_stats(connection, order_clause:, limit:, include_digest:)
  digest_col = include_digest ? "DIGEST," : ""
  <<~SQL
    SELECT
      #{digest_col}
      DIGEST_TEXT,
      COUNT_STAR AS calls,
      ROUND(SUM_TIMER_WAIT / 1000000000, 1) AS total_time_ms,
      ROUND(AVG_TIMER_WAIT / 1000000000, 1) AS avg_time_ms,
      ROUND(MAX_TIMER_WAIT / 1000000000, 1) AS max_time_ms,
      SUM_ROWS_EXAMINED AS rows_examined,
      SUM_ROWS_SENT AS rows_sent,
      SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
      SUM_SORT_ROWS AS sort_rows,
      FIRST_SEEN,
      LAST_SEEN
    FROM performance_schema.events_statements_summary_by_digest
    WHERE SCHEMA_NAME = #{connection.quote(connection.current_database)}
      AND DIGEST_TEXT IS NOT NULL
      AND #{QUERY_STATS_NOISE_FILTERS.strip}
    ORDER BY #{order_clause}
    LIMIT #{limit}
  SQL
end

#query_stats_order_clause(sort) ⇒ Object 



67
68
69
70
71
72
73
74
75
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 67

def query_stats_order_clause(sort)
  case sort
  when "total_time"    then "SUM_TIMER_WAIT DESC"
  when "avg_time"      then "AVG_TIMER_WAIT DESC"
  when "calls"         then "COUNT_STAR DESC"
  when "rows_examined" then "SUM_ROWS_EXAMINED DESC"
  else "SUM_TIMER_WAIT DESC"
  end
end

#stats_reset_at(_connection) ⇒ Object

MySQL’s table_io_waits counters track since server start with no cheap way to surface that timestamp at query time, so we return nil and let the dashboard fall back to “since server restart” wording.



117
118
119
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 117

def stats_reset_at(_connection)
  nil
end

#stats_snapshot(connection, limit:) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
89
90
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 77

def stats_snapshot(connection, limit:)
  <<~SQL
    SELECT
      DIGEST_TEXT,
      COUNT_STAR,
      ROUND(SUM_TIMER_WAIT / 1000000000, 1) AS total_time_ms
    FROM performance_schema.events_statements_summary_by_digest
    WHERE SCHEMA_NAME = #{connection.quote(connection.current_database)}
      AND DIGEST_TEXT IS NOT NULL
      AND #{QUERY_STATS_NOISE_FILTERS.strip}
    ORDER BY SUM_TIMER_WAIT DESC
    LIMIT #{limit}
  SQL
end

#table_sizes(connection) ⇒ Object 



23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 23

def table_sizes(connection)
  <<~SQL
    SELECT
      table_name,
      engine,
      table_collation,
      auto_increment,
      update_time,
      ROUND(data_length / 1024 / 1024, 2) AS data_mb,
      ROUND(index_length / 1024 / 1024, 2) AS index_mb,
      ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb,
      ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb
    FROM information_schema.tables
    WHERE table_schema = #{connection.quote(connection.current_database)}
      AND table_type = 'BASE TABLE'
    ORDER BY (data_length + index_length) DESC
  SQL
end

#unused_indexes(connection, min_scans: 0) ⇒ Object 



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
 
# File 'lib/sql_genius/core/query_builders/mysql.rb', line 92

def unused_indexes(connection, min_scans: 0)
  threshold = [min_scans.to_i, 0].max
  <<~SQL
    SELECT
      s.OBJECT_SCHEMA AS table_schema,
      s.OBJECT_NAME AS table_name,
      s.INDEX_NAME AS index_name,
      s.COUNT_READ AS `reads`,
      s.COUNT_WRITE AS `writes`,
      t.TABLE_ROWS AS table_rows,
      NULL AS size_bytes
    FROM performance_schema.table_io_waits_summary_by_index_usage s
    JOIN information_schema.tables t
      ON t.TABLE_SCHEMA = s.OBJECT_SCHEMA AND t.TABLE_NAME = s.OBJECT_NAME
    WHERE s.OBJECT_SCHEMA = #{connection.quote(connection.current_database)}
      AND s.INDEX_NAME IS NOT NULL
      AND s.INDEX_NAME != 'PRIMARY'
      AND s.COUNT_READ <= #{threshold}
    ORDER BY s.COUNT_WRITE DESC
  SQL
end