Shulou(Shulou.com)05/31 Report--

This article introduces the high-level database application example analysis in the data architecture design, the content is very detailed, interested friends can refer to, hope to be helpful to you.

1. How to design data recovery backup

The way of full backup

Use crontab combined with mysqldump to do scheduled backups

Incremental time point compensation

How to compensate

Consider changes to be modified: update, delete

With our binlog

# the first step is to make a full backup first. You can regularly process 18 20 * mysqldump-uroot-p123456-databases icoding_admin > / usr/local/bak/bak.sql16 20 * rm-rf / usr/local/bak/bak.sql# step 2, and enable binlong statement/row/mixed# to export data to binlog # scenario 1: if the amount of data is small, such as a key piece of data In row mode, you can manually process mysqlbinlog-- base64-output=decode-rows-- start-position=9623-- stop-position=9858-vv mysql-bin.000002# scenario 2: the amount of data is very large, so you need to export for processing mysqlbinlog-- start-position=9623-- stop-position=9858 mysql-bin.000002 > ist.sql#. The third step is to restore the incremental data and turn off the current binlog record mysql > set sql_log_bin=0 as needed. Mysql > source ist.sql

The data must be fully backed up regularly and binlog is enabled.

Job 1: realize the combination of automatic full backup and incremental backup by yourself

2. MySQL's SQL optimized index hit rule

# execution plan explain select * from pms_product where id=1;# composite index must be the leftmost matching principle # if you build a lot of composite indexes on the table, the index file is inflated, modification, deletion and update will be slow

Columns suitable for establishment

Frequently queried columns

Columns with foreign key association

Columns that are not suitable for establishment

Columns with little change in value

Frequently updated

Tables have fewer records: for example, configuration tables

How to ensure that the database is dead and not stiff

Just create table when you come up.

Make the Emurr diagram first.

The design should meet the third paradigm.

In practical application, we will take the initiative to break the third paradigm and improve query efficiency.

Show processlist

Kill id; 3. The design of database architecture. Steps for database schema design

Logical design

Physical design

The database structure is 3.2. Database naming

Database name: lowercase underlined

Database names prohibit the use of reserved words

See the name and know the meaning

Temporary table: tmp_tablename_20200620

Backup form: bak_tablename_20200620

All stores the same column name and type length must be the same 3.3. Database design specification

After using innoDB,v5.6 as much as possible, innoDB has become the default engine

Character set Unified UTF-8 (varchar (255) UTF8 255cm 360765 bytes)

Be sure to annotate the column

Control the amount of data in a single table

Controls the width of the table, with a column limit of 4096

Prohibit the creation of reserved fields in the table: ext_float_1,ext_char_2

It is prohibited to store pictures, files and binary streams in the database.

Have to save: separate the content data from the file process data (foreign key table) and associate it when needed

Select * will bring out large fields, wasting memory and Icano.

Pressure testing of online databases is prohibited

It will generate a lot of junk data and log files.

It is forbidden to connect to test or production database 3.4 from the development environment. Design specification of database index

It is recommended that the number of indexes in a single table should not exceed 5. If there are more columns, you can increase it as appropriate.

Every innodb table should have a primary key, and innodb is an index organization table

The table data is stored in the order of primary keys.

If there is no primary key, mysql will first choose a non-empty unique index for sorting

If there is no non-empty unique index, mysql will generate a 36-byte primary key of its own, but the performance is not good

Do not use strings such as UUID,MD5,HASH as the primary key, it is recommended to use the growth sequence as the primary key

The field matching of a combined index is from left to right

Generally, the column with the highest degree of differentiation is placed on the far left of the combined index.

Put the field with small length on the far left.

Most frequently on the far left.

Avoid creating redundant and duplicated indexes (index (amembpenc) index (bmeme c) index (a))

Try to avoid using foreign key constraints 3.5. Database field design specification

Give priority to the minimum data type that meets the storage

Avoid using TEXT, BLOB types

Avoid ENUM types: alter statements are required to modify enumerated type values

Try to define the column as NOT NULL

Date format is recommended to use timestamp or int to save 3. 6. Development specification of database

The SQL that the program connects to the database must use Preparement

Reduce repetitive execution of lexical and grammatical analysis

Prevent SQL injection

Try to avoid the use of index before and after%

Use join or exists to optimize in operations

Different applications use different accounts to access the database

Prohibit the use of insert without column names

Avoid subqueries (indexes cannot be used in subquery result sets)

Avoid using JOIN to connect too many tables. Ali manual recommends no more than 3 tables.

Reduce the number of database interactions by 3.7. Database operation code of conduct

Batch write operations of more than 100w lines are carried out in batches

Prohibit granting super permissions to program users

Grant all privileges

Follow the principle of minimum authority when authorizing

When the database connection is slow, MySQL leaves a reserved connection for super

2. MySQL database partition table application 2.1. Introduction to Partition Table

MySQL itself supports logical partitioning of tables.

Check whether the database supports partitioned tables

````sqlmysql > show plugins Why use partition tables:-whether tens of millions of large tables are encountered-query difficulties, historical data is not very concerned about-if historical data is to be archived, remove the data from the original database, if there is a file organization form, put the data in 2017 into a file, put 2018 in one, and one in 2019. At this time in 2020, the partition type of the partition table can be realized through the partition table provided by MySQL-HASH partition-LIST partition-RANGE partition-KEY partition # # 6.2. HASH partitions-partition keys are calculated according to MOD and divided into formulation table areas-can be basically evenly distributed-HASH partition keys must be of INT type Or transfer the function to INT``sql``shellCREATE TABLE `login_ log` (`login_ id`int (10) unsigned NOT NULL COMMENT 'login user ID', `login_ time`datetime NOT NULL COMMENT' user login time', `login_ ip`int (10) unsigned NOT NULL COMMENT 'login IP', `login_ type`tinyint (4) NOT NULL COMMENT' login type: 0 unsuccessful 1 success') ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY HASH (customer_id) PARTITIONS 4 Insert into customer_login_log values (1); insert into customer_login_log values (2); insert into customer_login_log values (3); insert into customer_login_log values (4).

Create future content

-rw-r- 1 mysql mysql 8767 Jun 20 22:30 customer_login_log.frm-rw-r- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p0.ibd-rw-r- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p1.ibd-rw-r- 1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p2.ibd-rw-r -1 mysql mysql 98304 Jun 20 22:30 customer_login_log#P#p3.ibd to check whether the partition table has been created successfully ```shell``sqlmysql > explain partitions select * from customer_login_log Query the number of data per partition ``sql``sqlselect table_name,partition_name,partition_description,table_rows from information_schema.PARTITIONS where table_name='customer_login_log'; query specific partition data ````sqlselect table_name,partition_name,partition_description,table_rows from information_schema.PARTITIONS where table_name='customer_login_log'; (p1je p2); select * from customer_login_log partition (p3) where customer_id=3;## 6.3. LIST partitions-partitions are enumerated according to partition keys-the list of partitions cannot be repeated-each row of data must find the corresponding partition before inserting data ```shellCREATE TABLE 'customer_login_log_ list` (`partition id` int (10) unsigned NOT NULL COMMENT' login user ID', `login_ time` datetime NOT NULL COMMENT 'user login time', `login_ ip` int (10) unsigned NOT NULL COMMENT 'login IP' `login_ type` tinyint (4) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY LIST (login_type) (PARTITION jishu VALUES in (1) 3, 5, 7, 9), PARTITION oushu VALUES in (2, 4, 6, 8)) Insert into customer_login_log_list values (1); insert into customer_login_log_list values (2); insert into customer_login_log_list values (3); insert into customer_login_log_list values (4).

If the partition key is not in the partition

Mysql > insert into customer_login_log_list values; ERROR 1526 (HY000): Table has no partition for value 02.4. RANGE partition

Put data in different files according to different range values of the partition

Multiple partitions should be continuous and should not overlap

Need a sealed MAXVALUE.

CREATE TABLE `login_ int (10) unsigned NOT NULL COMMENT 'login user ID', `login_ time` datetime NOT NULL COMMENT' user login time', `login_ ip`int (10) unsigned NOT NULL COMMENT 'login IP', `login_ type`tinyint (4) NOT NULL COMMENT' login type: 0 unsuccessful 1 success') ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE (YEAR (login_time)) (PARTITION y2017 VALUES LESS THAN (2017), PARTITION y2018 VALUES LESS THAN (2018) PARTITION y2019 VALUES LESS THAN (2019), PARTITION y2020 VALUES LESS THAN (2020), PARTITION maxyear VALUES LESS THAN MAXVALUE) Insert into customer_login_log_range values (1); insert into customer_login_log_range values (2); insert into customer_login_log_range values (3); insert into customer_login_log_range values (3); insert into customer_login_log_range values (5). Insert into customer_login_log_range values (6); insert into customer_login_log_range values (7); insert into customer_login_log_range values (8); insert into customer_login_log_range values (4); insert into customer_login_log_range values (9).

If we need to add range partitions later, we can't seal maxvalue.

CREATE TABLE `login_ int (10) unsigned NOT NULL COMMENT 'login user ID', `login_ time` datetime NOT NULL COMMENT' user login time, `login_ ip`int (10) unsigned NOT NULL COMMENT 'login IP', `login_ type`tinyint (4) NOT NULL COMMENT' login type: 0 unsuccessful 1 success') ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE (YEAR (login_time)) (PARTITION y2017 VALUES LESS THAN (2017), PARTITION y2018 VALUES LESS THAN (2018) PARTITION y2019 VALUES LESS THAN (2019), PARTITION y2020 VALUES LESS THAN (2020)) Alter table customer_login_log_range1 add PARTITION (PARTITION y2021 VALUES LESS THAN (2021), PARTITION y2022 VALUES LESS THAN (2022), PARTITION y2023 VALUES LESS THAN (2023))

A table that does not create a partition

CREATE TABLE `customer_login_log_range_ no` (`login_ id` int (10) unsigned NOT NULL COMMENT 'login user ID', `login_ time` datetime NOT NULL COMMENT' user login time', `login_ ip` int (10) unsigned NOT NULL COMMENT 'login IP', `login_ type` tinyint (4) NOT NULL COMMENT' login type: 0 failed and 1 success') ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into customer_login_log_range_no values. Insert into customer_login_log_range_no values (2); insert into customer_login_log_range_no values (3); insert into customer_login_log_range_no values (5); insert into customer_login_log_range_no values (5); and insert into customer_login_log_range_no values (6). Insert into customer_login_log_range_no values (7 recorder 2021-06-2022: 30); insert into customer_login_log_range_no values (8); insert into customer_login_log_range_no values (9). Alter table customer_login_log_range_no PARTITION BY RANGE (YEAR (login_time)) (PARTITION y2017 VALUES LESS THAN (2017), PARTITION y2018 VALUES LESS THAN (2018), PARTITION y2019 VALUES LESS THAN (2019), PARTITION y2020 VALUES LESS THAN (2020), PARTITION y2021 VALUES LESS THAN (2021), PARTITION y2022 VALUES LESS THAN (2022), PARTITION maxyear VALUES LESS THAN MAXVALUE) # data will be reassembled according to partition rules The data will enter the corresponding partition.

If you want to delete a partition and use the command, you cannot delete the collection directly.

Alter table customer_login_log_range drop partition y2017; this is the end of the analysis of high-level database application examples in data architecture design. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.