I work a lot with developers and I've discovered that their experience with data modeling or just database work in general ranges from knowing nearly nothing to being experts beyond my own experience. Some have taken courses in college, some are self trained and some have just picked things up on the job.
One of the first things I did two years ago when I started working at my company was to create a list of standards that all new database development in MySQL would adhere to. For example, all tables would use InnoDB as the default storage engine, utf8 as the default collation, no more enums allowed, all tables must have primary keys and lots of other standards.
The item about developers not putting primary keys on tables has been a mystery to me. It just seems very basic to me. We've got a legacy app that largely uses MyISAM tables and the developers that created these tables long before I ever got hired never put primary keys on some of them. There was no database code review process at that time and so they just got away with it.
I've been preaching ever since I got here that all the tables need to be converted to InnoDB and those tables need primary keys. But 24 months later, it still hasn't changed. If it ain't broke, don't fix it right?
I don't have any benchmarks but this is Percona's take scalability problems if your InnoDB tables don't have primary keys:
https://www.percona.com/blog/2013/10/18/innodb-scalability-issues-tables-without-primary-keys/
https://dzone.com/articles/innodb-scalability-issues-due
Here are some others:
http://www.psce.com/blog/2012/04/04/how-important-a-primary-key-can-be-for-mysql-performance/
http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/
Finding tables is pretty easy, I copied some code from the data charmer blog to get this:
SELECT table_schema, table_name
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type in ('PRIMARY KEY'))
AND table_schema NOT IN ('information_schema', 'mysql');
Here I want to find all tables that do not have a PRIMARY key. Even if it has a UNIQUE key, I still want to get the table name.
One of the big use case on why the tables need to be converted is that several development managers want to try using Percona Cluster but having all these tables without Primary Keys isn't going to fly.
No comments:
Post a Comment