Mysql Schema and Index

Field type selection
Generosity is unwise.
Use the same data type in the related tables, because the join may be
Select identifiers: Integers are usually the best choice, avoid using strings as much as possible
Approximate data type (number, string, time, etc.)
Choose to store smaller types, choose simpler types (such as integers better than strings), choose MySQL built-in time types instead of strings, select integers instead of strings to save IP
Try to avoid using null: Any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that contains null. This means that if a column has a null value, even indexing the column does not improve performance.
Determining Specific types
varchar (number of bytes) variable length string
VarChar content begins with 1 to 2 bytes representing the actual length (2 bytes longer than 255), so the maximum length cannot exceed 65535
MySQL after 5.0 retains the trailing space for both read and write
The insertion will be truncated but not an error.
Char fixed-length string
Write to it, will remove the trailing space
Comparison
VarChar is prone to fragmentation, and Char does not
The maximum length is greater than the average length and is applicable to varchar
Fixed length, or with a short maximum length, for Char
Integer tinyint (8bit) smallint (16bit) mediumint (24bit) int (32bit) bigint (24bit)
The signed and unsigned occupy the same space, the latter maximizing the maximum by a factor of
Integer type definition widths (such as int (unsigned)) have no effect on storage, but only affect some of the interactive tools present
Real TODO
Digital
String
Binary and varbinary: Save binary strings, they hold bytes instead of characters, and padding is instead of spaces
Blob and text: Cannot index the full length of these types, nor can I use an index for sorting
Sort by only a few bytes specified by Max_sort_length, or you can specify order by substring (column, length)
Enum
Specify this type when building a table: CREATE TABLE table_name (column_name enum (' A ', ' B ', ' C ') not NULL);
The field will be 1 to 2 bytes (all the most enumerators are 65535), the stored book number
The field value of the select will be a string
It can be converted to a number: SELECT Column_name + 0 ...
Internal sorting is based on numbers (so it should be noted in order when defined), or explicitly specified: Order by field (column_name, (' B ', ' A ', ' C '))
New enumeration types are required after the table is created only ALTER TABLE, all types not suitable for type indeterminate
Advantage: space-saving disadvantage: The join string is slower (there is conversion)
Date and time
Year
Date
DateTime 8-byte storage, and time zone independent, 1001 to 9999, precision seconds
Timestamp 4 bytes, which is associated with the time zone, representing the number of seconds since January 1, 1970
Usually should use timestamp, more space-saving
BIT TODO
SET TODO
Index
Index Type:
B-tree Index: Supported in addition to the archive engine
Hash index: Memory engine,
You can build your own hash index on other b-tree indexes: Add a column of indexed columns (as a hash key), the column allows a certain collision, need a hash function (such as CRC32), should not use strong cryptographic functions (such as SHA1 MD5, etc., collision low but the cost of space, Slow lookup)
R-tree Index: MyISAM support #TODO
Fulltext:myisam Support #TODO
High performance indexing strategy
Quarantine columns: Columns are not part of an expression, nor are they in a function
EXPLAIN
ID indicates the order of execution
ID from big to small, ID same from top down
Select_type Query Type
Simple: The query does not contain subqueries or union
If any complex sub-parts are included in the PRIMARY query, the outermost query is marked as PRIMARY
Subquery contains a subquery in the Select or where list, which is marked as subquery
Depedent subquery dependent subquery for external query
DERIVD the subquery contained in the From list is marked as derived (derived)
Union result a Select that gets the result from the Union table is marked as union result
Tables referenced by table record queries
TYPE:
The type of access that indicates how MySQL found the desired row in the table
From best to worst:
(unique or non-unique) the first part of a combined index: SELECT * FROM t where unique_or_not_unique_combined_index_1 = ' abc '
All of the non-unique combined or single-column indexes: SELECT * from t where not_unique_combined_index_1 = ' abc ' and not_unique_combined_index_2 = ' 123 '
Prefix matching for unique indexes:
Null:mysql decomposes statements during optimization, and does not even have access to tables or indexes, such as Id=-1, min (ID) max (ID)???? #TODO
SELECT * from deals where id=-1;
Select Max (ID) from deals;
System: Table has only one row of records (equals system tables). This is a special case of the const table join type, the following example is a const, the main query is system:
SELECT * FROM (SELECT * from deals where id=1) T;
Const: There is a maximum of one row of records in the table that will be read at the beginning of the query (Tengyun technology ty300.com). Because there is only one row of records, the value of the field recorded in the remaining optimizer can be treated as a constant value. The const table is very fast to query because it only reads once! Const used in cases where there is a fixed value comparison with primary KEY or unique index
SELECT * from Tbl_name where primary_key=1;
SELECT * from Tbl_name where primary_key_part1=1 and primary_key_part2=2;
Eq_ref: Unique index Scan, for each index key, only one record in the table matches it. Common to primary key or unique index scans. This is the best type of connection. It is used in all parts of the index to make a connection and this index is a primary key or a unique type. Eq_ref can be used to retrieve a field when making a "=" comparison. The following first User_trades full table scan (all) and then deals unique index scan Eq_ref
SELECT * from Deals, user_trades where deals.id=user_trades.deal_id;
SELECT * from ref_table,other_table where Ref_table.key_column_part1=other_table.column and Ref_table.key_column_ Part2=1;
Ref: A non-unique index scan that returns all rows that match a single value. Lookups that are common to non-unique prefixes that use non-unique indexes that are unique indexes
Ref_or_null: This type of connection is similar to ref, and the difference is that MySQL searches for additional records that contain null values when retrieving
SELECT * from ref_table where key_column=expr or key_column is null;
Subqueries using PRIMARY key queries in Unique_subquery:in
Value in (select Primary_key from single_table where some_expr)
Index_subquery: This type of connection is similar to Unique_subquery. However, it is used in cases where there are no unique indexes in the subquery:
Value in (select Key_column from single_table where some_expr)
Range: Index range scan, the scan of the index starts at a point, returns the row of the matching range, common in between, <
Index:full index Scan,index is different from all for index type only traversal index tree
All:full table Scan, MySQL will traverse the full table to find a matching row

Manuscripts: Diligent Learning qkxue.net

More about MySQL architecture and indexing

Mysql Schema and Index