Shulou(Shulou.com)06/01 Report--

Editor to share with you an example of Schema and data type optimization. I hope you will get something after reading this article. Let's discuss it together.

Traps in 4.2MySQL schema design 1. Too many columns

When MySQL storage engine api works, it needs to copy data in row buffer format in server layer and storage engine layer, and then decode the buffer content into columns in server layer. It is expensive to convert encoded columns into row data from row buffering, and the fixed length rows of myisam match the server row structure without conversion. However, the row structure of variable length row structure InnoDB always needs conversion, and the conversion cost depends on the number of columns.

2. Too many connections

Entity-attribute-value EAV: poor design pattern, mysql limits a maximum of 61 tables per association operation, but the EAV database requires a lot of self-association; a rough rule of thumb is that if you want a query to execute quickly and concurrency well, a single query is best associated within 12 tables

3. Prevent overuse of enumerations

Be careful to prevent overuse of enumerations; use foreign keys to associate to the dictionary table or look up tables to find specific values. In mysql, when you need to add values to the enumeration list, you need to do an earlier alter table;MySQL5.0 alter table blocking operation. In version 5.1 newer, adding values not at the end of the list will also require alter table.

4. Null of not invent here other than this invention

It is recommended that empty values can be replaced with 0, special values, or empty strings. Try not to null; but do not go to extremes. In some scenarios, it is better to use null:

Create table... (/ / all-zero (impossible date) will cause a lot of problems dt datetime not null default '0000-00-0000: 00001. )

MySQL stores null values in the index, Oracle does not

4.3 Paradigm and Anti-Paradigm 4.3.1 advantages and disadvantages

1. Stylized update operations are faster

2. When the data is well modeled, there is little duplicate data, and only less data needs to be modified.

3. The formatted table is smaller, can be better placed in memory, and performs operations faster.

4. Less redundant data, less distinct and group by statements are needed when retrieving list data.

Disadvantages:

Requires association, costs, and may invalidate the index

4.3.2 advantages and disadvantages of anti-paradigm

Avoid association, data is larger than memory may be much faster than association (avoid random Imax O)

4.4 cached tables and summary tables

Cache table:

Very effective for optimizing search and retrieval query statements

Store tables that can easily get data from other tables (slower each time)

Summary tables: save tables that aggregate data using group by statements

When using, decide whether to maintain data in real time or rebuild regularly, rebuild regularly: save resources, less fragmentation, sequentially organized index (efficient)

When rebuilding, to ensure that the data is still available during operation, through the "shadow table". Shadow table: a table created behind the real table. After the table creation operation is completed, the shadow table and the original table can be switched by atomic renaming operation.

4.4.1 materialized View

Tables that are pre-calculated and exist on disk can be refreshed and updated through various policies. Mysql does not natively support it, and can be implemented using Justin Swanhart tool flexviews:

Flexviews composition:

Change data fetching, reading server binary logs and parsing related row changes

A series of defined stored procedures that can help create and manage views

Some tools that can apply changes to materialized views in the database

Flexviews can incrementally recalculate the contents of the materialized view by extracting changes to the source table: there is no need to query the original data (efficiently)

4.4.2 counter table

Counter table: cache the number of friends of a user, the number of file downloads, etc. It is recommended to create an independent table storage counter to avoid query cache invalidation

Updates plus transactions can only be executed serially. For higher concurrency, the counter can be saved in multiple lines, and each time one row is randomly selected to update, and when you want to count the results, aggregate the query; (I read two or three sides of this, which may be stupid. It is that the same counter saves multiple points, one of which is updated each time, and the final sum seems to be not easy to understand. Read it several times.)

4.5 Speed up alter table operations

Most of the modified table structures of mysql are: create empty tables with new results, find out all data from old tables, insert new tables, and delete old tables.

Mysql5.1 and updates include support for some types of "online" operations. The whole process does not require fully locked tables. The latest version of InnoDB (MySQL5.5 and the only InnoDB in newer versions) supports indexing through sorting, faster and more compact layouts.

In general, most alter table causes mysql service disruption. For common scenarios, the techniques used are:

1. First perform the alter table operation on a machine that does not provide services, and then switch with the main library of the extraction service

2. Shadow copy, create a new table independent of the source table with the required table structure, and exchange two tables by renaming and deleting the table (above)

Not all alter table of all causes table reconstruction. Theoretically, you can skip the step of creating a table: column defaults actually exist in the .frm file of the table. So can modify this file directly without changing the table itself, but mysql has not adopted this optimization method. All's modify column will lead to table reconstruction.

Alter column: change column defaults through frm file: alter table allows you to use alter column and modify column change column to modify columns. The three operations are different.

Alter table sakila.film alter column rental_duration set default 5bot 4.5.1 modify only frm files

Mysql sometimes rebuilds tables when it is not necessary. If you are willing to take some risks, you can make other types of changes without rebuilding the table: the following operations may not work properly, so back up the data first.

There is no need to rebuild the table to do the following:

1. Remove the auto_increment of a column

2. Add, remove, or change enum and set constants. If the constant is removed, the query returns an empty string.

The basic technique creates a new frm file for the desired table result, and then replaces the frm file for the existing table with it:

1. Create an empty table with the same structure and make the necessary changes

2. Execute flush tables with read lock: close the table being used by all and prohibit any table from being opened

3. Exchange frm files

4. Execute the read lock of step 2 of unlock tables release

A brief example

4.5.2 Quick creation of myISAM index

1. In order to load data into the MyISAM table efficiently, common techniques are: first disable the index, load the data, and restart the index: because the work of building the index is delayed until the data is loaded, the index can be built by sorting, which makes the index tree less fragmented and more compact.

But invalid for unique index (disable keys), myisam constructs a unique index in memory and checks for uniqueness for each row loaded. Once the index size exceeds the valid memory, the loading operation will be slower and slower.

2. In the modern version of InnoDB, there is a similar technique: first delete the all non-unique index, then add new columns, and finally rebuild the deleted index (depending on innodb's fast online index creation feature) Percona server can do these operations automatically.

3. Speed up the operation like the hacker method of the former alter table, but do more work and take risks, which is useful for loading data from backups, such as already know all data is effective, and no need to do the unique check

Create a table with the desired table structure, excluding indexes (if you use load data file and the loaded table is empty, myisam can sort and index)

Load data into a table to build a MYD file

Create another empty table according to the desired structure, this time to include the index, will create a .frm .MYI file

Get the read lock and refresh the table

Rename the frm file MYI of the second table to make mysql think that this is the file of the first table

Release the read lock

Use repair table to re-index the table, which builds all indexes, including unique indexes, through sorting

4.6 Summary

Good schema design principles are common, but mysql has its own implementation details to pay attention to. In a nutshell: it's always good to keep everything as small and simple as possible; mysql likes simplicity (just right, me, too).

It is best to avoid using bit

Use small and simple appropriate types

Try to use integers to define identity columns

Avoid overdesign, such as schema designs that lead to extremely complex queries, or many columns

Null values should be avoided whenever possible, unless there is an exact need in the real data model

Try to use the same type to store similar and related values, especially the columns used in the association condition

Note that variable-length strings can lead to pessimistic allocation of memory by max length in temporary tables and sorting

Avoid using abandoned features, such as specifying the precision of floating-point numbers or the display width of integers

Be careful with enum and set. Although they are convenient to use, don't abuse them. Sometimes they will become traps.

Paradigms are good, but anti-paradigms are sometimes necessary; there are also great benefits from pre-calculating, caching or generating summary tables

In most cases, alter table locks the table and rebuilds the entire table (painful) this chapter provides some risky methods

After reading this article, I believe you have a certain understanding of "examples of Schema and data type optimization". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.