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

The main content of this article is to explain "what are the compliance MySQL check database design", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what are the compliant MySQL check database designs"?

As a typical representative of relational database, MySQL has experienced wind and rain in the domestic environment, and has formed a set of norms in terms of development and operation and maintenance. These specifications make it easier to understand and use MySQL, and play a good role in preventing some problems in the later stage.

1. View the large table information of the database

Count the size of each table under a library, do not have too large table information. The memory allocated by itself is limited, too large tables will constantly refresh new and old data, and IO delivery is frequent, resulting in performance degradation.

SELECT TABLE_SCHEMA, TABLE_NAME TABLE_NAME, TABLE_ROWS, CONCAT (ROUND (data_length / (1024 * 1024), 2),'M') data_length, CONCAT (ROUND (index_length / (1024 * 1024), 2),'M') index_length, CONCAT (ROUND (data_length + index_length) / (1024 * 1024), 2) 'M') total_size, engine FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN (' INFORMATION_SCHEMA', 'performance_schema',' sys', 'mysql') ORDER BY (data_length + index_length) DESC LIMIT 10 +-+-- +-+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ ROWS | data_length | index_length | total_size | ENGINE | +-+ | | employees | salaries | 1910497 | 64.59m | 0.00m | 64.59m | InnoDB | | employees | employees | 299556 | 14.52m | 10.03m | 24.55m | InnoDB | | employees | employees01 | 101881 | 5.52m | 8.55m | 14.06m | InnoDB | | employees | t_temp | | | 95374 | 5.52m | 5.52m | 11.03m | InnoDB | | db3 | t_temp | 1000 | 0.08m | 0.13m | 0.20m | InnoDB | | db3 | transportorder | 3 | 0.02m | 0.06m | 0.08m | InnoDB | | db3 | | | transportorderwaybill | 3 | 0.02m | 0.05m | InnoDB | | db1 | pt1 | 10 | 0.06m | 0.00m | 0.06m | InnoDB | | db1 | city | 2 | 0.02m | 0.03m | 0.05m | | | InnoDB | | db2 | tabname | 30 | 0.02m | 0.03m | 0.05m | InnoDB | +- -+ 10 rows in set (0.20 sec) 2. Storage engine

Storage engine distribution, innodb engine is most suitable because it supports transaction, row lock level.

SELECT TABLE_SCHEMA, ENGINE, COUNT (*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA',' PERFORMANCE_SCHEMA', 'SYS',' MYSQL') AND TABLE_TYPE='BASE TABLE' GROUP BY TABLE_SCHEMA, ENGINE Non-INNODB storage engine tables SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION, ENGINE, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA',' SYS', 'MYSQL' 'PERFORMANCE_SCHEMA') AND TABLE_TYPE='BASE TABLE' AND ENGINE NOT IN (' INNODB') ORDER BY TABLE_ROWS DESC 3. Primary key

No primary key, no unique key table. Copying the primary key is the most important, and the data operation primary key is efficient.

SELECT T1.TABLE_SCHEMA, T1.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME WHERE T1.TABLE_SCHEMA NOT IN ('SYS',' MYSQL', 'INFORMATION_SCHEMA' 'PERFORMANCE_SCHEMA') AND T2.TABLE_TYPE='BASE TABLE' GROUP BY T1.TABLE_SCHEMA, T1.TABLE_NAME HAVING group_concat (COLUMN_KEY) NOT REGEXP' PRI | UNI' 4. Not utf8 table

Obscure words are garbled and emoticons are invalid.

SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.TABLES WHERE TABLE_COLLATION NOT LIKE 'utf8%' AND table_schema NOT IN (' information_schema', 'mysql','performance_schema',' sys'); Character set verification

The Join character set between tables is asymmetrical, resulting in index invalidation.

See the system character set:

Mysql > show global variables like 'collation%'

A database that is different from the system character set:

SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME from information_schema.SCHEMATA b WHERE b.SCHEMA_NAME not in ('information_schema',' mysql','performance_schema', 'sys') AND b.DEFAULT_COLLATION_NAME@@collation_server

Tables and fields that are different from the system character set:

Select distinct tschema,tname,tcoll from (select a.TABLE_SCHEMA as tschema, a.TABLE_NAME as tname,a.TABLE_COLLATION as tcoll from information_schema.TABLES a WHERE a.TABLE_SCHEMA not in ('information_schema',' mysql','performance_schema', 'sys') and a.TABLE_COLLATION@@collation_server union select a.TABLE_SCHEMA as tschema, TABLE_NAME as tname A.COLLATION_NAME as tcoll from information_schema.COLUMNS a WHERE a.TABLE_SCHEMA not in ('information_schema',' mysql','performance_schema', 'sys') and a.COLLATION_NAME@@collation_server) as aa 6. Stored procedure & function

The view of stored procedures and functions does affect the processing power of MySQL, and it is difficult to maintain later.

# # MySQL5.7 SELECT db,type,count (*) FROM mysql.proc WHERE db not in ('mysql','information_schema','performance_schema','sys') AND type='PROCEDURE' GROUP BY db,type; # # MySQL8.0 SELECT Routine_schema, Routine_type FROM information_schema.Routines WHERE Routine_schema not in (' mysql','information_schema','performance_schema','sys') AND ROUTINE_TYPE='PROCEDURE' GROUP BY Routine_schema, Routine_type;7. Statistical view

Statistical views do affect the processing power of MySQL and are difficult to maintain later. In particular, you should pay attention to ddl changes.

SELECT TABLE_SCHEMA, COUNT (TABLE_NAME) FROM information_schema.VIEWS WHERE TABLE_SCHEMA not in ('mysql','information_schema','performance_schema','sys') GROUP BY TABLE_SCHEMA; 8. Self-adding primary key view

The main consideration is that the self-increasing key is out of range and needs to be checked.

SELECT infotb.TABLE_SCHEMA, infotb.TABLE_NAME, infotb.AUTO_INCREMENT, infocl.COLUMN_TYPE, infocl.COLUMN_NAME FROM information_schema.TABLES as infotb INNER JOIN information_schema.COLUMNS infocl ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA AND infotb.TABLE_NAME = infocl.TABLE_NAME AND infocl.EXTRA='auto_increment'

Self-increase primary key usage statistics:

SELECT infotb.TABLE_SCHEMA, infotb.TABLE_NAME, infotb.AUTO_INCREMENT,infocl.COLUMN_TYPE FROM information_schema.TABLES as infotb INNER JOIN information_schema.COLUMNS infocl ON infotb.TABLE_SCHEMA = infocl.TABLE_SCHEMA AND infotb.TABLE_NAME = infocl.TABLE_NAME AND infocl.EXTRA='auto_increment';9. Partition table

Try to avoid partition table, partition table performance problems: reflected in the partition lock, the initial access to load all partitions.

View the information about the partition table in the instance:

SELECT TABLE_SCHEMA, TABLE_NAME, count (PARTITION_NAME) AS PARTITION_COUNT, sum (TABLE_ROWS) AS TABLE_TOTAL_ROWS, CONCAT (ROUND (SUM (DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH, CONCAT (ROUND (SUM (INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH CONCAT (ROUND (ROUND (SUM (DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024), 2),'M') TOTAL_SIZE FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA NOT IN ('sys',' mysql', 'INFORMATION_SCHEMA',' performance_schema') AND PARTITION_NAME IS NOT NULL GROUP BY TABLE_SCHEMA TABLE_NAME ORDER BY sum (DATA_LENGTH + INDEX_LENGTH) DESC +-+ | TABLE_SCHEMA | TABLE _ NAME | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE | +-+- -+-+ | db | T1 | 365 | 0 | 5.70m | 17.11m | 22.81m | | db | T2 | 391 | 0 | 6.11m | 0.00m | | 6.11m | | db | T3 | 4 | 32556 | 2.28m | 0.69m | 2.97m | | db | T4 | 26 | 0 | 0.41m | 2.44m | 2.84m | | db | | | T5 | 4 | 0 | 0.06m | 0.00m | 0.06m | | db | T6 | 4 | 0 | 0.06m | 0.00m | 0.06m | +-| -+ 6 rows in set (1.04 sec)

View the details of a partition table. Here, take the partition table with the library name db and the table name e as an example:

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_METHOD, PARTITION_DESCRIPTION, TABLE_ROWS, CONCAT (ROUND (DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH, CONCAT (ROUND (INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH CONCAT (ROUND (ROUND (DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024), 2),'M') TOTAL_SIZE FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA NOT IN ('sys',' mysql', 'INFORMATION_SCHEMA' 'performance_schema') AND PARTITION_NAME IS NOT NULL AND TABLE_SCHEMA='db' AND TABLE_NAME='e' +-- -+ | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE | +-- -+-- -+-+ | db | e | p0 | id | RANGE | 50 | 4096 | 0.20m | 0.09m | 0.30m | | db | e | p1 | id | | RANGE | 6144 | 0.28m | 0.13m | 0.41m | | db | e | p2 | id | RANGE | 150 | 6144 | 0.28m | 0.13m | 0.41m | | | db | e | p3 | id | RANGE | MAXVALUE | 16172 | 1.52m | 0.34m | 1.86m | +-| +-- -+ 4 rows in set (0.00 sec) 10. Schedule a task

Unconsciously, it executes automatically. Confirm that it cannot be maintained.

SELECT EVENT_SCHEMA,EVENT_NAME FROM information_schema.EVENTS WHERE EVENT_SCHEMA not in ('mysql',' information_schema', 'performance_schema',' sys'); at this point, I believe you have a better understanding of "which compliant MySQL check database design", so you might as well do it in practice! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.