20160924-2--mysql Frequently Asked Questions collection

I. Data type-related issues 1, varchar (N) How much space is occupied

(1) The N in varchar (n) is the number of characters, not the number of bytes;

(2) Character type (varchar text blob, etc.) space = character actual length + field length;

(3) The space occupied by varchar (N):
In the case of the lantin1 character set, the maximum space =1n+ (1 or 2) bytes, because the Lantin1 1 characters occupy 1 bytes, and the subsequent addition of 1 or 2bytes is used to represent the length of the field, when it is possible to exceed 255 bytes, 2 bytes to indicate the length of the field;
If the UTF8 character set, the maximum space =3n+ (1 or 3) bytes, because the UTF8 1 characters occupy 3 bytes, followed by 1 or 2bytes is used to represent the length of the field, when it is possible to more than 255 bytes, 2 bytes to indicate the length of the field;

(4) when varchar (N) is likely to exceed 255 bytes, the field length = 2;
Example: varchar (100) When the character set is UTF8, ' aaaa ' occupies a few bytes respectively?
because: varchar (100) If the Chinese is stored, it will occupy 3>225 bytes, so 2 bytes is required to represent the field length
' AAAA ' occupies space =3100+2=302bytes

(5) Supplement:

2. The difference between char and varchar (N) Types:

(1) Char can represent a maximum of 255 characters, while the total length of varchar cannot exceed 65,535 bytes (bytes), the maximum number of supported characters varies according to the character set;
(2) Char truncates the trailing empty string, and varchar does not truncate the trailing empty string;
(3) "Char fixed length, varchar longer" is an error for the InnoDB storage engine.
InnoDB is determined by Row_format:

In addition to redundant, when processing a multibyte character set (GBK UTF8, etc.) char field, InnoDB is treated as a variable-length character, while a single-byte is still assigned a fixed-size space, that is, the UTF8 GBK character set char and varchar are consistent, both are variable , and the char type of the lantin1 character set is fixed-length.
Summary
Using the InnoDB storage engine, there is no difference between the commonly used character set GBK or Utf8,char and varchar, and the Varhcar type is recommended.

3, varchar and text can represent long characters, and are actually how many characters occupy how much space, that need to store how long string, is the choice of varchar or text it?

(1) Functional aspects:
The varchar length is limited, and the sum of all char and varchar fields cannot exceed 65535 bytes, and varchar can have default values.
The text type can store up to 4G, and the text cannot set the default value;
"How To Choose"
A, if there are too many long fields in the table, you may not be able to build all the fields as varchar (for example, all fields are established as Varhcar greater than 65535 bytes) and can be used in combination with varchar and text.
b, if the field needs to have a default value, then use varchar.

(2) Performance aspects:
One common argument is that "the text field is an overflow field, and varchar does not overflow, so varchar is more efficient" ====== this argument is not entirely true, because the text field does not necessarily overflow, only if an entire line length cannot be saved in the page, The longest field may be linked to the rest of the page, and the text and varchar are the same;

The text field cannot be sorted by tmp_table_size memory, direct disk ordering (using Filesort);

3, storage string recommended to choose to use varchar (n), n as small as possible, why?

varchar (10) and varchar (100) For example:
The engine layer (disk storage vs. buffer pool) uses the same two types of space;
The server tier does not allocate memory by actual size when processing data.

Some operations: sorting, table Ddl,varchar (100) will use more disk and memory space, the efficiency will be lower;
Because: the server layer does not know how the engine layer data is organized, the engine is organized differently, the server layer uses the length of the table definition when allocating memory, and some operations that need to process data in the server may be affected! For example, when sorting, indexing uses more memory (tmp_table_size) or disk space, performance is affected.

4, Int (11), what is the meaning of assigning a length to int?

Int (11) and int (4) are examples:
(1) There is no difference between int (11) and int (4), only the Zerofill property is added when the field is defined, and the value of the field is less than the specified width will be 0, but Zerofill is almost unused;
Added
The default int is int (11), which is a signed integer, or int (10) If it is an unsigned integral type.

===========================================

Second, lock-related issues

InnoDB Storage Engine Locks according to the lock granularity: row-level lock, table-level lock two, according to the lock mode: Shared lock, exclusive lock;
Row-level locks are divided into: Record lock, Gap lock (Gap key), Next-key Lock (Record lock + gap lock);

1, row-level lock (1) Concept:

Row-level locks are the least granular type of lock in MySQL, and can greatly reduce the conflict of database operations. But the smaller the granularity, the higher the cost of implementation. The MyISAM engine supports only table-level locks, while the InnoDB engine supports row-level locks.

(2) Three types of row-level locks for InnoDB:

(2.1) Record Lock: Is added to the index record;
(2.2) Gap Lock: Gap Lock, the scope of the index record lock, or add to the last index record in front or behind; Gap lock is mainly to prevent phantom reading, used in the Repeated-read (RR) isolation level, under the read-commited (RC), Generally there is no gap lock (exception in foreign key case). And the Gap lock appears only on the secondary index, and there is no gap lock for the unique index and primary key index. The gap lock, either s or X, only blocks the insert operation.
(2.3) Next-key Lock: The combination of record lock and Gap lock, the clearance lock lock record the range before the lock;

(3) Row-level locks are divided into shared and exclusive locks

InnoDB row-level locks have two types: Shared lock (S Lock) and exclusive (X Lock) lock. A shared lock allows multiple threads to read the same row of records and does not allow any thread to modify the row record. An exclusive lock allows the current thread to delete or update a row of records, and other threads cannot manipulate the record.

(4) Shared lock

"Usage": Select ... LOCK in SHARE MODE; MySQL adds a shared lock to each row in the query result set.
"Lock Request Prerequisites": No threads currently use exclusive locks on any rows in the result set, or the request is blocked.
"Operational Restrictions": The following are the restrictions on locking record operations using shared locks and threads that do not use shared locks

(4.1) A shared lock thread can read its locked record, and other threads can also read the locked record, and the two threads read the data in the same version.
(4.2) For write operations, threads that use shared locks need to be discussed in detail, when a thread is able to write to the record (including updates and deletions) only when it uses a shared lock on the specified record, because the thread has requested an exclusive lock on the record before the write operation When another thread also uses a shared lock on the record, the write operation is not allowed, and the system will have an error message. A thread that does not use a shared lock on a lock record, of course, is not writable and the write operation is blocked.
(4.3) Using the shared lock process to request a shared lock on the lock record again, the system does not make an error, but the operation itself does not make much sense. Other threads can also request a shared lock on a locked record.
(4.4) Use a shared lock process to request an exclusive lock on a record, while other processes cannot request an exclusive lock on a locked record, and the request is blocked.

(5) Exclusive lock:

"Usage": Select ... For UPDATE; MySQL adds an exclusive lock to each row in the query result set, and any update and delete operations to the record are automatically added to the exclusive lock in the thing operation.
"Lock Request Prerequisites": No threads currently use exclusive or shared locks on any rows in the result set, or the request is blocked.
"Operational Restrictions": The following are the restrictions on locking record operations using exclusive locks and threads that do not use exclusive locks

(5.1) using an exclusive lock thread can read its locked record, read the latest version of the current thing, and for a thread that does not use an exclusive lock, the same can be read operations, which is a consistent non-lock read. That is, for the same record, the database records multiple versions, and the update operation within the thing is reflected in the new version, and the old version is provided to other threads for read operations.
(5.2) Use an exclusive lock thread to write to its locked record, and for a thread that does not use an exclusive lock, writes to the locked record are disallowed and the request is blocked.
(5.3) using an exclusive lock process to request a shared lock on its locked record, but after requesting a shared lock, the thread does not release the original exclusive lock, so the record shows the nature of the exclusive lock, and the other thread is not required to request a shared lock on the locked record, and the request is blocked.
(5.4) using an exclusive lock process can request an exclusive lock on its locked record (which does not actually make sense), while other processes cannot request an exclusive lock on a locked record, and the request is blocked.

(6) Examples of lock usage in SQL

(6.1) Delete from T where id=10, which adds a record lock on the primary key index (id=10), locks the record line, and is an exclusive lock (intent exclusive lock, ix);
(6.2) SELECT * FROM T where id=10 Lock in shared mode, which adds a J record lock on the primary key index (id=10), locks the record, and is a shared lock (intent shared lock, is);
(6.3) insert into T values (12,1), the SQL will add an insert intention gap lock before insert, which is allowed concurrency but does not allow concurrent ID values. It is understood that other INSERT statements are allowed, but forbid is inserted concurrently with id=12, and the lock is recorded on (12,1) after insert.
(6.4) In RR mode create TABLE P as SELECT * from T; This SQL will lock the table T, add the shared Next-key lock, lock all the records of the T-table and the gap lock;
(6.5) RC mode CREATE TABLE P as SELECT * from t; This SQL does not lock the T table and does a consistent read to the T table (snapshot read);

2, table level Lock (1) concept

Table-level locks are the most granular type of lock in MySQL, which is simple, resource-intensive, and supported by most MySQL engines. The most commonly used MyISAM and INNODB support table-level locking.

(2) Type of table-level lock

Table-level locking is divided into two categories: Read lock and Write lock. Read locks are expected to read from the data table, and the guarantee table cannot be modified during lockout. A write lock is expected to update an operation on a data table, which guarantees that the table cannot be updated or read by other threads during the lock.

(3) Read lock

"Usage": Lock TABLE table_name [as alias_name] read; Specifies the data table, the lock type is read, the as alias is an optional parameter, and if you specify an alias, you also specify the alias line when you use it.
"Request read Lock Prerequisites": No threads are currently using write locks on the data table, otherwise the application will be blocked.
"Operational Restrictions": Other threads can use read locks on locked tables, and other threads cannot use write locks on locked tables

For a MySQL thread that uses a read lock, because the read lock does not allow any thread to modify the locked table, the thread can only read the table operation before releasing the lock resource, and the write operation will prompt for an illegal operation. For other MySQL threads that do not use locks, it is normal to read the lock table, but when a write occurs, the thread waits for the read lock to be released and the thread responds to the write operation when all read locks on the locked table are released.

(4) Write lock

"Usage": Lock TABLE table_name [as Alias_name] [low_priority] write; alias usage as with read locks, the write lock increases the function of the specified priority, and the join low_priority can specify a write lock as low priority.
"Apply for Write lock premise": When no thread is using write lock and read lock on the data table, the request is blocked.
"Operation Limit": No other MySQL thread can use write lock, read lock on lock table

For MySQL threads that use write locks, the current thread can read and write to the locked table. However, for other threads, reading and writing to the specified table is illegal and requires waiting until the write lock is released.

(5) Priority relationship of Read and write locks

Priority for lock assignment is: Low_priority Write < READ < write
(5.1) When more than one thread requests a lock, it is assigned to the write lock, the write lock is not present, and the read lock is assigned, low_priority write needs to wait until the write lock and read are freed before the opportunity is allocated to the resource.
(5.2) for the same priority lock application, the allocation principle is who first apply, who first assigned.

(6) Other precautions

(6.1) cannot manipulate (query or update) a table that is not locked.
For example: When only a table1 read lock is requested, the SQL statement contains an operation that is illegal for table2:

mysql> LOCK TABLE test READ;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM test_myisam;ERROR 1100 (HY000): Table ‘test_myisam‘ was not locked with LOCK TABLES

(6.2) cannot use two tables in one SQL (unless alias is used)
When a table is used more than once in an SQL statement, the system will error. For example:

mysql> LOCK TABLE test READ;Query OK, 0 rows affected (0.00 sec)mysql> SELECT * FROM test WHERE id IN (SELECT id FROM test );ERROR 1100 (HY000): Table ‘test‘ was not locked with LOCK TABLES 解决这个问题的方法是使用别名，如果多次使用到一个表，需要声明多个别名。mysql> LOCK TABLE test AS t1 READ, test AS t2 READ;Query OK, 0 rows affected (0.00 sec)mysql>  SELECT * FROM test AS t1 WHERE id IN (SELECT id FROM test AS t2);

(6.3) An alias is used when applying for a lock, and an alias must be added when using the lock table.

III. performance-related issues 1, how is null stored in B+tree? Why is it recommended to set a default value for each field without using null??

1), different data processing null method is different, but any database at query time to allow null value of field processing more complex.
2), for INNODB types of tables, the InnoDB record (index) header contains a null bitmap that flags which fields are in the Null,innodb field, the null value can exist in the index, and the null value in the index is treated as a value; Oracle does not, Null values are not included in the index in Oracle;
3), NULL cannot be compared (< >! =, etc.) may appear "data loss", only is NULL to judge!
Example: Select ... where a is null; This SQL can use the index in MySQL, but Oracle does not;
In general, the recommended field is set to the default value, without using NULL;

2, FOREIGN KEY constraints, in comparison with the program to make the judgment constraints, which performance is higher? Does the actual production environment recommend the use of FOREIGN key constraints??

Today's applications are all about fast iterations, the table structure changes very frequently, if the use of the primary foreign key will be very difficult and troublesome.
In the case of online loading or cleaning work, data maintenance can be cumbersome if the primary foreign key is used, and the primary foreign key sometimes does not overwrite the business logic constraints.
Comprehensive consideration is: High performance requirements, concurrency, data in a scenario, as far as possible, do not use foreign key constraints. Similar: primary foreign keys, triggers, functions, stored procedures, and so on are not recommended, try to keep the database "simple".

3. Sub-query VS join

1) Sub-query is divided into: non-related word query (no external dependencies in the subquery), Correlated sub-query (sub-query has external dependencies)

2) Sub-query writing is simple, good readability (sub-query when less), join to achieve sub-query function more difficult to write
Example: Sub-query rewritten as join
(non-associative subquery) SELECT * from T1 where t1.a in (select t2.a from T2 where t2.b=1);
Equivalent to
Select t1.* from T1, (select distinct a from T2 where b=1) t3
where t3.a=t1.a;

(Correlated subquery) SELECT * from T1 where t1.a in (select t2.a from T2 where t2.b=t1.b);
Equivalent to
Select t1.* from T1. (select distinct a from T2 where T2) T3 where t3.a=t1.a and t3.b=t1.b;
A subquery is rewritten as a join, but there is still a subquery (as a temporary table) in the join, and SQL is a lot more complicated after it is changed to join.

Summary
MySQL joins using the next loop method;
T1,T2 cannot use indexes correctly in subqueries;
Exterior driver (query) table, here T1 drive T2 table, to try to make the appearance of small data, if the appearance is large table will be very poor efficiency;
A reasonable index and driver table can be used for join,mysql without a tape query;
After mysql5.6, the subquery is optimized: The child table is checked only once and the result set is stored in the temporary hash table, which needs to be optimized by setting the optimizer parameter Optimizer_switch;

Recommendations
MySQL's sub-query efficiency is usually relatively low, can use (without Word query) join as far as possible join, otherwise it is recommended to query the very high-frequency complex query into a number of simple queries, by lifting the system concurrency to achieve optimization purposes.
It is also indicated that the join performance of the query is better than the subquery, and the join with the subquery is still poor in efficiency.

4, the performance of the view

Views are only recommended for use when doing permission control, not on-line business queries.

5. mysql Limit

1) is the MySQL table field generally more appropriate? How much more are there serious performance issues?
InnoDB supports up to 1000 fields, and performance problems are usually not simple because of the excessive number of fields, which is usually caused by the unreasonable combination of fields.
It is recommended to specify the most reasonable and concise table structure based on business query habits.

2) MySQL a database how many tables more appropriate?
MySQL allocates data files (stand-alone tablespace) and table definition files for each table;
Need more resources when too many tables, need to adjust some parameters (Innodb_open_file open_file_limit table_open_cache, etc.)
MySQL query will open the table and close the table. Too many tables can cause slow operations;
It is recommended to split into different instances according to the business module;

From for notes (Wiz)

20160924-2--mysql Frequently Asked Questions collection