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

This article mainly introduces the methods of Schema and data type optimization, the article is very detailed, has a certain reference value, interested friends must read it!

Schema is a collection of database objects, which contains various objects such as tables, views, stored procedures, indexes, and so on. In order to distinguish between different collections, different collections need to be given different names. By default, a user corresponds to a collection, and the user's schema name is equal to the user name and serves as the user's default schema. So the schema collection looks like a user name.

If you think of database as a warehouse, there are many schema in the warehouse, a schema represents a room, table can be regarded as a locker in each room, and user is the owner of every schema and has the right to operate every room in the database, that is to say, the user mapped in each database has the key of each schema (room). SQL server is different from Oracle mysql.

4.1 principles for selecting optimized data types:

1. Smaller data is better, using the smallest data type that can store data correctly (less disk memory CPU cache, less CPU cycles: faster), but can cover the data, it is embarrassing not to save it.

2. Simplicity is good: simple types (less CPU cycles), MySQL built-in type storage time, integer storage ip, integer lower cost than characters (character sets and proofreading sorting rules make characters more complex)

3. Avoid null as much as possible: it is best to specify not null.

*) null columns use more storage space, and special processing is required in mysql

*) null makes indexes, index statistics, and value comparisons more complex; additional bytes are required for each index record when columns that are indexed for null

Exception: InnoDB uses a single bit bit to store null,so, which is space efficient for sparse data (many values are null) and is not suitable for MyISAM

4.1.1 Integer Type [reference] Integer whole number

Tinyint (8-bit storage space) smallint (16) mediumint (24) int (32) bigint (64)

1. The range of stored values: n is the number of bits of storage space.

2. Unsigned: optional, negative values are not allowed, and the upper limit of positive numbers can be doubled: tinyint unsigned 0quota 255

3. With or without symbols, use the same storage space and the same performance

You can specify a width for an integer, such as INT (11), which is meaningless for most applications and does not limit the legal range of values, but only specifies the number of characters displayed by interactive tools. For storage and calculation, int (1) and int (20) are the same.

Real number real number: with decimal

Float and double,mysql use duble as the type of internal floating point calculation

Decimal: stores precise decimal places, implemented by the mysql server itself, decimal (18pr 9) 18 digits, 9 decimal places, 9 bytes (the first 4, 4: 1)

Try to use (extra space and computational overhead) only when calculating decimals accurately, such as financial data

When the amount of data is large, consider using bigint instead, multiplying the monetary units that need to be stored by the corresponding multiple according to the number of decimal places

Floating point:

Suggestion: only specify type, variable precision (mysql), these precision is non-standard, mysql will quietly select type, or save time to choose the value.

When storing values in the same range, there is less space than decimal, float4 bytes store double8 bytes (higher precision range)

4.1.3 string types varchar and char:

Premise: innodb and myisam engine, the main string type

Disk storage: the storage engine can not store in the same way as in memory and disk, so the mysql server's value from the engine needs to be reformatted.

Varchar:

1. Store variable strings and save space compared to fixed length (only use necessary space), but if the table uses row_format=fixed, rows will be stored in fixed length.

2. The length of the string should be recorded with 1 stroke and 2 extra bytes; 1) the length of the column max

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.