MySql recommendation: Innodb table best practices
Source Link: https://www.cs.usfca.edu/~sjengle/cs212/javadoc/mysql/innodb-storage-engine.html
You probably know about features like transfers and international keys, if you have been using InnoDB for a long time. If not, in this chapter, read about them. To make a short story long:
- Define a primary key for each table using the column or columns that are most commonly queried, or an auto-increment value if there is no apparent primary key.
Specify a primary key: use one or more columns most commonly requested as the primary key; if not, use the auto-increment id as the primary key.2) Follow the notion of joins, where information is extracted from different tables based on similar ID values from those tables. Define foreign keys on the join columns for quick join results, and declare those columns in each table with the same data type. International keys also propagate deletes or changes to all of the affected tables and, if the corresponding IDs are not present in the parent table, prevent inclusion of data in the child table.
Embrace join: When data is extracted according to the same ID value from different tables, you can use foreign keys to boost join efficiency when using join..3) Switching off Autocommit Committing places a limit on output hundreds of times a second (limited by the write speed of your storage device)
Autocommit closed: apply hundreds of times per second performance limit.
(Note from the translator: it is recommended that the business system is not closed, otherwise the programming is more problematic, you should close it when batch data is imported, and then commit it periodically or quantitatively)4) Bracket sets of similar updates, logical work groups, with statements for START TRANSACTION and COMMIT. You also don’t want to issue massive batches of INSERT, UPDATE, or DELETEstatements that run for hours without committing, but you don’t want to commit very much. To stop repeated committing, use transaction commit to
5) Avoid using statements from a LOCK TABLE. InnoDB can manage several sessions at once, without losing reliability or high efficiency, both reading and writing to the same table. To obtain write-only access to a set of rows, use Pick… Syntax FOR UPDATE to lock only the rows that you want to update.
Innodb’s mechanism can accommodate high concurrent operations without loss of reliability and performance without using the LOCK TABLE operation. Recommend Pick to be used….FOR UPDATE
6) Allow the innodb file per table option to position the data and indexes in separate files for individual tables instead of in a single giant device tablespace. (Some of the other features, such as table compression and fast truncation, need to be used for this setting.)
Switch on the option innodb file per table
I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.
