· 9 years ago · Jan 25, 2017, 03:04 AM
1// This script will connect to the DB and look for potential missing indexes.
2// Author: Liam Mitchell
3var db = 'database here';
4
5var async = require('async');
6
7var mysql = require('mysql');
8var connection = mysql.createConnection({
9 host : 'host here',
10 user : 'username here',
11 password : 'password here',
12 database : db
13});
14
15// Connect to the database.
16connection.connect();
17
18
19// Get the tables.
20connection.query('SHOW TABLES', function (error, results, fields) {
21 if (error) throw error;
22
23 var tables = [];
24 for (var i=0; i< results.length; i++) {
25 var table = { name: results[i]['Tables_in_'+db], indexes: [], columns: [], suggestedChanges: [], fks: [] };
26 tables.push(table);
27 }
28
29 console.log('Read table names from '+db+' ('+tables.length+' tables).');
30
31 getIndexesOnTables(tables);
32});
33
34
35function getIndexesOnTables(tables) {
36 async.eachSeries(tables, function iteratee(table, callback) {
37 connection.query('SHOW INDEXES FROM '+table.name, function (error, results, fields) {
38 if (error) {
39 callback(error);
40 return;
41 }
42
43 if (results.length > 0) {
44 console.log('Table '+table.name+' has had '+results.length+' index(s) read.');
45 table.indexes = results;
46 } else {
47 console.log('Table '+table.name+' has no indexes.');
48 }
49
50 async.parallel([
51 getColumnsForTable.bind(null, table),
52 getFKForTable.bind(null, table),
53 getEngineInfoForTable.bind(null, table)
54 ], function(err, results) {
55 callback(err, results);
56 });
57 });
58 }, function(err, results){
59 if (err) {
60 console.error(err);
61 return;
62 }
63 console.log('All tables have had their indexes looked up.');
64 var tableNameLookup = {};
65 for (var i=0; i<tables.length;i++) {
66 tableNameLookup[tables[i].name] = tables[i];
67 }
68
69 // For each table check for missing indexes and missing PK or FK.
70 for (var i=0; i<tables.length;i++) {
71 var table = tables[i];
72 var keyCount = 0;
73 var hasPrimaryKey = false;
74 for (var j=0; j<table.columns.length; j++) {
75 var column = table.columns[j];
76 var columnName = column.Field;
77
78 if (column.Key !== '') {
79 keyCount++;
80
81 if (column.Key === 'PRI') {
82 hasPrimaryKey = true;
83 }
84 }
85
86 if (j===0 && hasPrimaryKey === false) {
87 console.log('Table '+table.name+' does not have a primary key as first column '+columnName);
88 }
89
90 if (columnName === 'id' || (columnName.endsWith('id') || columnName.endsWith('Id')) && !columnName.endsWith('paid') ) {
91 var indexAlreadyExistsOnThatKey = false;
92 for (var k=0; k<table.indexes.length; k++) {
93 var index = table.indexes[k];
94 if (index.Key_name === columnName) {
95 indexAlreadyExistsOnThatKey = true;
96 break;
97 }
98 }
99
100 if (indexAlreadyExistsOnThatKey === false) {
101 console.log('Index not found for '+table.name+'.'+columnName+' suggesting to add one.');
102 table.suggestedChanges.push('ALTER TABLE '+table.name+' ADD INDEX ('+columnName+');');
103 } else {
104 console.log('Index found for '+table.name+'.'+columnName+' skipping.');
105 }
106
107 var fkExistsAlready = false;
108 for (var k=0; k<table.fks.length; k++) {
109 var fk = table.fks[k];
110 if (fk.column_name === columnName) {
111 console.log('FK Already exists for '+table.name+'.'+columnName+ ' on ' +fk.referenced_table_name+'.'+fk.referenced_column_name);
112 fkExistsAlready = true;
113 break;
114 }
115 }
116
117 if (fkExistsAlready === false) {
118 var relationTableGuess = columnName.substr(0, columnName.length-2).trim();
119 if (relationTableGuess.length > 2) {
120 console.log('Trying to find relationship of '+table.name+'.'+columnName);
121 if (relationTableGuess.endsWith('_')) {
122 relationTableGuess = relationTableGuess.substr(0, relationTableGuess.length-1);
123 }
124
125 var otherTable = tableNameLookup[relationTableGuess];
126 if (otherTable === undefined) {
127 otherTable = tableNameLookup[relationTableGuess+'s'];
128 }
129
130 if (otherTable) {
131 console.log('Other table '+otherTable.name+' was found.');
132
133 if (otherTable.columns.length > 0) {
134 var isOtherTableFirstColumnPK = false;
135 if (otherTable.columns[0].Key === 'PRI') {
136 isOtherTableFirstColumnPK = true;
137 console.log('First column in otherTable is '+otherTable.columns[0].Field+' and it is a PRIMARY KEY.');
138 } else {
139 console.log('First column in otherTable is '+otherTable.columns[0].Field+' and it is not a PRIMARY KEY.');
140 }
141
142 if (table.engine !== otherTable.engine) {
143 console.warn('Tables '+table.name+' ('+table.engine+') and '+otherTable.name+' ('+otherTable.engine+') have different engines so a FK between them may not work.');
144 if (table.engine === 'MyISAM') {
145 table.suggestedChanges.push('ALTER TABLE `'+table.name+'` ENGINE=INNODB;');
146 }
147 if (otherTable.engine === 'MyISAM') {
148 otherTable.suggestedChanges.push('ALTER TABLE `'+otherTable.name+'` ENGINE=INNODB;');
149 }
150 }
151
152 // TODO: Check if there are any records that violate this constraint and create an appropriate commented out DELETE query or at least warn.
153 table.suggestedChanges.push('ALTER TABLE '+table.name+' ADD CONSTRAINT fk_'+table.name+'_'+columnName+' FOREIGN KEY ('+columnName+') REFERENCES '+otherTable.name+'('+otherTable.columns[0].Field+');');
154 }
155
156 }
157 }
158 }
159
160 }
161 }
162
163 table.hasPrimaryKey = hasPrimaryKey;
164 table.keyCount = keyCount;
165
166 if (table.keyCount === 0) {
167 console.log('Table '+table.name+' has no keys!');
168 } else if (hasPrimaryKey === false) {
169 console.log('Table '+table.name+' has no PK suggesting to add first key as primary key!');
170 table.suggestedChanges.push('ALTER TABLE '+table.name+' ADD PRIMARY KEY('+table.columns[0].Field+');');
171 }
172 }
173
174 console.log('-- Suggested changes to DB schema.');
175 console.log();
176 for (var i=0; i<tables.length;i++) {
177 var table = tables[i];
178 if (table.suggestedChanges.length > 0) {
179 table.suggestedChanges = unique(table.suggestedChanges);
180 console.log('-------------------------------------------');
181 console.log('-- Table '+table.name+' suggested changes.');
182 console.log(table.suggestedChanges.join("\n"));
183 console.log();
184 }
185 }
186
187 exit();
188 });
189}
190
191function getColumnsForTable(table, callback) {
192 connection.query('SHOW COLUMNS FROM '+table.name, function (error, results, fields) {
193 if (error) {
194 callback(error);
195 return;
196 }
197
198 table.columns = results;
199
200 callback(null);
201 });
202
203}
204
205function getFKForTable(table, callback) {
206 connection.query("SELECT column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE referenced_table_name IS NOT NULL AND table_schema = '"+db+"' AND table_name = '"+table.name+"'", function (error, results, fields) {
207 if (error) {
208 callback(error);
209 return;
210 }
211
212 table.fks = results;
213
214 callback(null);
215 });
216
217}
218
219function getEngineInfoForTable(table, callback) {
220 connection.query("SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"+db+"' AND TABLE_NAME = '"+table.name+"'", function (error, results, fields) {
221 if (error) {
222 callback(error);
223 return;
224 }
225
226 if (results.length > 0) {
227 table.engine = results[0].ENGINE;
228 }
229
230 callback(null);
231 });
232
233}
234
235
236
237// Exit
238function exit() {
239 console.log('-- Generated by a script on '+(new Date()));
240 connection.end();
241}
242
243
244/// Polyfills
245
246// Ends with
247if (!String.prototype.endsWith) {
248 String.prototype.endsWith = function(searchString, position) {
249 var subjectString = this.toString();
250 if (typeof position !== 'number' || !isFinite(position) || Math.floor(position) !== position || position > subjectString.length) {
251 position = subjectString.length;
252 }
253 position -= searchString.length;
254 var lastIndex = subjectString.lastIndexOf(searchString, position);
255 return lastIndex !== -1 && lastIndex === position;
256 };
257}
258
259function unique(arr) {
260 var hash = {}, result = [];
261 for ( var i = 0, l = arr.length; i < l; ++i ) {
262 if ( !hash.hasOwnProperty(arr[i]) ) { //it works with objects! in FF, at least
263 hash[ arr[i] ] = true;
264 result.push(arr[i]);
265 }
266 }
267 return result;
268}