Mysql-related knowledge point sorting (1) and mysql-related knowledge points sorting

Mysql-related knowledge point sorting (1) and mysql-related knowledge points sorting

• Information_schema Database: The tables in the information_schema database are mostly temporory tables, both of which are read-only and cannot be updated, deleted, or inserted. triggers cannot be added because they are actually only a view, not a basic table, no associated file. For example:

  Use information_schema; select table_rows from TABLES where TABLE_SCHEMA = 'database name' AND TABLE_NAME = 'table name ';

  If the table uses the InnoDB engine, table_rows only roughly estimates the value. To obtain the actual exact value, use count (*) for statistics and reading.

  For InnoDB tables, the row count is only a rough estimate used in SQL optimization. 
  (This is also true if the InnoDB table is partitioned.)

   

•  EXPLAIN parse SELECT statement execution plan: EXPLAIN is synonymous with DESC. It can be used to parse how MySQL processes SELECT and provide the order of how tables are joined and joined, you can also know when to add an index to the table to obtain a faster SELECT statement that uses indexes to search for records. To force the optimizer to set a SELECT statement to join in the table naming order, the statement should start with STRAIGHT_JOIN, not just SELECT. EXPLAIN returns a row of information for each table in the SELECT statement. Tables are listed in the order they are read by MySQL during query processing. MySQL uses one-time scan for multiple connections (Single-sweep multi-join. This means that MySQL reads a row from the first table, finds a matching row in the second table, and then in 3rd tables. After all the tables are processed, It outputs the selected columns and returns the table list until it finds a table with more matching rows. Read the next row from the table and continue processing the next table. EXPLAIN can be used as follows:

  1. explain select id, name from users where age> 20; 2. Or explain extended select id, name from users where age> 20; show warnings;

• JOIN instead of subquery: MySQL subqueries are executed slowly most of the time, while the JOIN efficiency is much higher. Therefore, use the appropriate JOIN type to replace subqueries.
• Differences between BINARY/VARBINARY and BIT:

  1. BIT (M) stores the 0/1 sequence of M bits, where M ranges from 1 ~ Between 64, you must use the 'value' flag to specify a BIT value type, such as insert into t SET B = B '1010 ';
  2. BIT values are unreadable, and "+ 0" can be read only after being converted using functions such as BIN () and CAST (), for example, SELECT B + 0, BIN (B ), OCT (B), HEX (B) FROM t;
  3. BINARY/VARBIANRY is similar to CHAR/VARCHAR. Except for BIANARY/VARBINARY, which only contains byte streams and does not contain character sets, sorting and comparison operations are based on Byte numeric values;
  4. the maximum length allowed by BINARY (255)/VARBIANRY (65535) is the same as that of CHAR/VARCHAR, but the former is measured in bytes rather than characters, and the latter is Character Set-related, if the UTF-8 character set encoding is used for VARCHAR, the maximum length is likely to be less than 65535.

  Below is (M) And LONGTEXT:

  VARCHAR(M)
  A variable-length string. M represents the maximum column length in characters. 
  The range of M is 0 to 65,535. The effective maximum length of a VARCHAR is subject 
  to the maximum row size (65,535 bytes, which is shared among all columns) and the 
  character set used. For example, utf8 characters can require up to three bytes per 
  character, so a VARCHAR column that uses the utf8 character set can be declared to 
  be a maximum of 21,844 characters.
  
  LONGTEXT
  A TEXT column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. 
  The effective maximum length is less if the value contains multibyte characters. 
  The effective maximum length of LONGTEXT columns also depends on the configured 
  maximum packet size in the client/server protocol and available memory. Each LONGTEXT 
  value is stored using a 4-byte length prefix that indicates the number of bytes in the value.

   

• Differences Between Several Indexes:

  1. clustering Index: Also called clustering Index, stores complete data rows on the Leaf Page (Leaf Page). It is also a data storage method for tables, such a Table is also called an Index organization Table (IOT ). Generally, an InnoDB table can have only one clustered index, which is defined on the primary key. MYISAM does not support this index type. Its index files (. MYI) and data files (. MYD) are independent of each other.

  
  Comparison between the primary key index and secondary index of INNODB and MYISAM: we can see that the primary key index of MYISAM is no different from the secondary index. The primary key index is only a unique non-null index called the primary key, therefore, MYISAM does not have a primary key.

  
  2. Secondary Index (Secondary Index): A Secondary Index. It refers to all indexes except clustered indexes. The leaf node of the InnoDB secondary index contains the primary key value rather than the Row Pointer, which reduces the overhead of maintaining the secondary index when the mobile data or data page is split, because InnoDB does not need to update the index row pointer.
  3. Covering indexes: indexes contain data that meets the query results. In other words, they are considered to contain fields involved in the query conditions. The biggest benefit is to avoid reading the rows in the disk data file. The secondary index leaf node of Innodb contains the primary key column, so the primary key must be overwritten by the index.

•  Master-slave Replication: Before each transaction updates data, MySQL writes the transaction into the binary log in sequence by the Master. Even if the statements in the transaction are cross-executed, hybriddb for MySQL then notifies the storage engine to submit the transaction. MySQL supports three replication modes, including Data Distribution, Load Balancing, Backups, High Availability and Failover.

  1.Statement-based Replication: Execute the same SQL statement on the master server. MySQL uses statement-based replication by default, which is more efficient.

  2.Row-based Replication: MySQL5.0 allows you to copy the changed content instead of executing the command on the slave server.

  3.Hybrid Replication: Statement-based replication is used by default. If statement-based replication fails, row-based replication is used.

   

  The replication process is completed through three threads,
  1.Master binlog output thread: The Master creates a binlog output thread for each replication connection request to output the log content to the corresponding Slave;
  2.Slave I/O thread: After start slave, this thread is responsible for pulling binlog content from the Master and putting it into its own Relay Log;
  3.Slave SQL thread: Executes the statement in Relay Log.

• Max_connections andMax_user_connections:Max_connectionsThe default value is 151. when MySQL and Apache Web Server are used together, the performance can be improved. Mysqld actually allows max_connections + 1 client connection. An additional connection is reserved for accounts with super permissions. If Linux or Solaris has available RAM, it supports at least 500 to 1000 concurrent connections or even up to 10000. Windows limits open tables × 2 + open connections <2048.Max_user_connectionsSpecify a non-zero value to limit the maximum number of connections that can be opened by any account at the same time.
Setting Account Resource Limits: MySQL permits limits for individual accounts on use of these server resources. To restrict specific accounts, use Create user or Alter user, for example, alter user 'francis '@ 'localhost' WITH MAX_QUERIES_PER_HOUR 100.

  Any statement that a client can issue counts against the query limit, unless its results are served from 
  the query cache. Only statements that modify databases or tables count against the update limit.