Mysql index, character set, data type learning summary
The index
The establishment of mysql index is very important for the efficient operation of mysql, index can greatly improve the speed of mysql retrieval. Take the Chinese dictionary’s catalogue page (index) as an example, we can quickly find the word we need according to the catalogue (index) sorted by pinyin, strokes, radicals, etc. Index can be divided into single column index and composite index.
Single-column index, that is, an index contains only a single column. A table can have multiple single-column indexes. Composite indexes, in which an index contains multiple columns.
advantages
- Indexes reduce the amount of data that the server needs to scan and thus speed up the retrieval of data, which is the main reason for creating indexes.
- An index can turn random IO into sequential IO
- By creating unique indexes, you can ensure that each row of data in a database table is unique.
- When using grouping and sorting clauses for data retrieval, you can also reduce the grouping and sorting time in queries
disadvantages
- Both index creation and index maintenance take time, which increases with the volume of data.
- Indexes take up physical space. In addition to table data, each index takes up a certain amount of physical space.
- When the data in the table is added, deleted, or modified, the index must be maintained dynamically, which increases the time cost.
Index reference criteria
- You can speed up searches on columns that need to be searched frequently.
- Enforces changes to the uniqueness of columns and organizes the arrangement of data in the table on columns that act as primary keys.
- Create indexes on columns that often need to be searched by ranges (<, <=, =, >,>=, between, in) because indexes are sorted and specify contiguous ranges.
- Create an index on an order by DE column that often needs to be sorted because the index is sorted so that the query can take advantage of the index’s sorting and speed up the sorting time.
- Create indexes on columns that often use the WHERE clause to speed up the determination of conditions.
- Indexes should not be added for data that has very little data or is most duplicate.
- Indexes should not be created when column modification performance requirements are greater than retrieval performance. (Conflicting when modifying performance and retrieving performance)
The index type
- Plain indexes: These are the most basic types of indexes, and they have limitations such as uniqueness.
- Unique index: This kind of index is basically the same as the “normal index” before, but with one difference: all values of the index column must appear only once, that is, they must be unique.
- Primary key index: This is a special unique index
- Full-text indexing: MySQL has supported full-text indexing and full-text retrieval since version 3.23.23. In MySQL, full-text indexes are of the index type FULLTEXT. Full-text indexes can be created on VARCHAR or TEXT columns. It can be created using the CREATE TABLE command, ALTER TABLE or CREATE INDEX command. For large data sets, it is faster to CREATE full-text indexes with the ALTER TABLE (or CREATE INDEX) command than to insert records into an empty TABLE with full-text indexes.
Combined index (combined index)
Union indexes follow the leftmost matching principle: if your SQL statement uses the leftmost variant of the union index, the SQL statement can use the union index to match, and will stop matching when a range query (>, <, between, like) is encountered.
Example:
MySQL > alter table associative index
ALTER TABLE
table_nameADD INDEX (col1,col2,col3);
If there is more than one SQL, two conditions are used
SELECT * FROM
user_infoWHERE username=’XX’,password=’XXXXXX’;
When the index retrieves the password field, the data volume is greatly reduced, reducing the index hit ratio and increasing the index efficiency.
The volume of an index matching an index is smaller than that of a single index, and it is only an index tree, saving time and space complexity more than an index matching a single column
When creating (COL1, COL2, COL3) union index, create (COL) single column index, (CLO1, CLO2) union index and (COL1, COL2, COL3) union index. Only col1 and COL1, COL2 and COL1, COL2 and COL3 can be used; Of course, col1 and col3 combinations can also be used, but actually only the index of col1 is used,col3 is not used!
A joint index is equivalent to a phone book by last name — first name. Only the last name can be matched before the index is hit. The following statements can be matched correctly (= and IN fields can be out of order, MySQL query optimizer can be optimized to index recognition).
-- Only hit COL1, col2
SELECT * FROM `table_name` WHERE `col1`='XX';
-- Hit COL1, col2. Col1, col2 can be reversed
SELECT * FROM `table_name` WHERE `clo1`='XX',`clo2`='XXX';
SELECT * FROM `table_name` WHERE `clo2`='XXX', `clo1`='XX';
Hit col1, COL2,col3. Similarly, the order of the three columns can be reversed
SELECT * FROM `table_name` WHERE `col1`='X',`col2`='XX',`col3`='XXX';
SELECT * FROM `table_name` WHERE `col1`='X',`col3`='XX',`col2`='XXX';
SELECT * FROM `table_name` WHERE `col2`='X',`col3`='XX',`col1`='XXX';
Copy the codeCharacter set UTF8 and UTF8MB4
- The original UTF8 standard for characters is byte length
- Variable length UTF8 is not good for retrieval, so mysql makes it fixed length (3 bytes).
- The original 3-byte UTF8 was able to cover every language in the world until symbols like emoji expanded the character set so much that utF8 began to have four-byte symbols
- Mysql can’t change the standard (otherwise there would be compatibility problems in all sorts of fields) and has to treat utF8 as a new character set called UTF8MB4
If you want to store 4-byte UTF-8 characters in MySQL, you need to use utF8MB4 encoding, but note that only MySQL after 5.5.3 supports it (check version command: select version()). For better compatibility, it is recommended to use UTF8MB4 instead of UTF8. For CHAR data, UTF8MB4 consumes a bit more space, but according to MySQL official advice, you can use VARCHAR instead of CHAR.
The data type
Numeric types
MySQL supports all standard SQL numeric data types.
These types include strictly NUMERIC data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as approximate NUMERIC data types (FLOAT, REAL, and DOUBLE PRECISION).
The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.
The BIT data type holds BIT field values and supports MyISAM, MEMORY, InnoDB, and BDB tables.
As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT. The table below shows the storage and range required for each integer type.
| type | The size of the | Range (signed) | Scope (unsigned) | use |
|---|---|---|---|---|
| TINYINT | 1 byte | (128127) | (0255). | A small integer value |
| SMALLINT | 2 bytes | (-32 768，32 767) | ,65 (0 535). | Large integer value |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | Large integer value |
| INT or an INTEGER | 4 bytes | (-2,147,483,648, 2,147,483,647) | (0，4 294 967 295) | Large integer value |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | Maximum integer value |
| FLOAT | 4 bytes | (-3.402 823 466 E+38, -1.494 369 E+38), 0, (1.494 369 E+38, 3.402 823 466 369 E+38) | 0, (1.175 494 369 E-38, 3.402 823 466 E+38) | Single-precision floating point value |
| DOUBLE | 8 bytes | (-1.797 693 134 862 391 7e +308, -2.797 858 858 391 4 e-308), 0, (2.797 858 858 391 4 e-308, 1.797 693 134 862 315 7 E+308) | 0, (1.797 693 134 862 315 7 E+308) | Double – precision floating – point value |
| DECIMAL | For DECIMAL(M,D), M+2 if M>D otherwise D+2 | Depends on the values of M and D | Depends on the values of M and D | Small numerical |
| ###### Date and time type | ||||
The date and time type representing the time value isDATETIME,DATE,TIMESTAMP,TIME and YEAR. |
Each time type has a valid value range and a “zero” value, which is used when specifying invalid values that MySQL cannot represent.
The TIMESTAMP type has a proprietary auto-update feature, which is described later.
| type | Size (bytes) | The scope of | format | use |
|---|---|---|---|---|
| DATE | 3 | The 1000-01-01/1000-01-01 | YYYY-MM-DD | Date value |
| TIME | 3 | ‘- 838:59:59’/’ 838:59:59 ‘ | HH:MM:SS | Time value or duration |
| YEAR | 1 | 1901/2155 | YYYY | Year value |
| DATETIME | 8 | The 1000-01-01 00:00:00/1000-01-01 23:59:59 | YYYY-MM-DD HH:MM:SS | Mixes date and time values |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 Ends at 2147483647 seconds, 2038-1-19 11:14:07 Beijing time or 03:14:07 GMT on January 19, 2038 | YYYYMMDD HHMMSS | Mix date and time values, time stamps |
| ###### String type | ||||
String type refers toCHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM and SET. This section describes how these types work and how to use them in queries. |
| type | The size of the | use |
|---|---|---|
| CHAR | 0-255 bytes | Fixed length string |
| VARCHAR | 0-65535 bytes | Variable length string |
| TINYBLOB | 0-255 bytes | A binary string of up to 255 characters |
| TINYTEXT | 0-255 bytes | Short text string |
| BLOB | 0-65 535 bytes | Long text data in binary form |
| TEXT | 0-65 535 bytes | Long text data |
| MEDIUMBLOB | 0-16 777 215 bytes | Medium length text data in binary form |
| MEDIUMTEXT | 0-16 777 215 bytes | Medium length text data |
| LONGBLOB | 0-4 294 967 295 bytes | Very large text data in binary form |
| LONGTEXT | 0-4 294 967 295 bytes | Maximal text data |
Note:char(n) andvarchar(n) The n in brackets represents the number of characters, not bytes. For example, CHAR(30) can store 30 characters. |
The CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of maximum length and whether trailing Spaces are reserved. There is no case conversion during storage or retrieval.
BINARY and VARBINARY are similar to CHAR and VARCHAR except that they contain BINARY strings rather than non-binary strings. That is, they contain byte strings instead of character strings. This means that they have no character set and sort and compare numeric values based on column value bytes.
A BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They differ in the range of storage that can be accommodated.
There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. The maximum storage length of the four BLOB types varies according to the actual situation.
Varchar, text, and BLOB
Text and BLOb are two data types that are designed to store large data. The maximum size of a single row in MySql is 64K.
Text and vARCHar are a set of different and related data types. The connection is that when the M of varchar(M) is greater than some value, the vARCHar automatically converts to text:
- Change to tinyText when M>255
- Change to text when M>500
- Change to mediumText when M>20000
Varchar (M) is the same as text (M).
- A single line of 64K is 65535 bytes of space, vARCHar can only use 63352/65533 bytes, but text can use all 65535 bytes
- Text can specify text(M), but it doesn’t matter what M is equal to
- Text is not allowed to have default values, vARCHar is allowed to have default values
Varchar and text are two data types. If you can use varchar, use varchar instead of text (high storage efficiency). The M length of varchar(M) has a limit.
Text and blob, where text stores strings and blob stores binary strings, blobs are used to store binary data for files like pictures, audio and video.
The resources
Blog.csdn.net/wangfeijiu/… www.cnblogs.com/wongdw/p/12… www.runoob.com/mysql/mysql…