Summary of MySQL related knowledge points (I.)

• Information_schema Library : The tables in the INFORMATION_SCHEMA library are mostly temporory tables, are read-only, cannot be updated, deleted, and inserted, and cannot be added to a trigger. Because they are actually just a view, not a basic table, there are no associated files. For example:

   Use Information_schema; Select  from where = ' Database name '  and = ' Table name '; 

  If the table is using the InnoDB engine, table_rows is just a rough estimate, to get the actual exact value or count (*) to read it.

  
  (This is also true if the InnoDB table is partitioned.)

• Explain parse SELECT statement Execution Plan : Explain is synonymous with DESC, through which you can parse how MySQL handles Select, providing a sequence of how tables are joined and joined, You can also know when you must index the table to get a faster select that uses the index to find records. In order to force the optimizer to have a SELECT statement in the join order in the table naming order, the statement should begin with straight_join instead of just select. Explain returns a row of information for each table used in the SELECT statement. Tables are listed in the order in which they will be read by MySQL during the process of querying. MySQL solves all joins in a way that scans multiple joins (single-sweep multi-join). This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the 3rd table, and so on. When all the tables have been processed, it outputs the selected columns and returns the list of tables until a table with more matching rows is found. Read the next line from the table and continue processing the next table. Explain can usually be used as follows:

  1. Explain select Id,name from the users where age > 20;2. or explain extended select Id,name from the users where age > 20;show warnings;

• join instead of subquery : MySQL subquery Most of the time execution is slow, and join efficiency is much higher, so choose to use the appropriate join type to replace the subquery
• The difference between binary/varbinary and BIT :

  1. Bit (m) stores the 0/1 sequence of M bits, m between 1~64, the value of a BIT numeric type must be specified by the B ' value ' notation, such as INSERT into t SET b = B ' 1010 ';
  2. The value of bit type is unreadable, "+ 0" or a function such as bin (), cast () can be converted to read, such as SELECT b+0, BIN (b), OCT (b), HEX (b) from T;
  3. Binary/varbianry similar to Char/varchar, except that the bianary/varbinary contains only byte stream, there is no concept of character set, and the sorting and comparison operations are based on the numeric value of bytes;
  4. BINARY (255)/Varbianry (65535) allows the maximum length to be the same as Char/varchar, but the former calculates the length in bytes rather than characters, the latter is character set-related, varchar if the UTF-8 character set encoding is used, the maximum length is probably less than 65535.

  below are the explanations for varchar ( 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 are 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 Chara Cter set can 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 this indicates the number of bytes in the value. 

• the difference between several indexes :

  1. Clustered index (Clustered index): Also known as a clustered index, the leaf page is stored in the full data row, is actually a table of data storage, such a table is also called Index Organization table (Index organized table, IOT). A InnoDB table usually has only one clustered index, which is defined on the primary key. MYISAM does not support the index type, its index file (. MYI) and data files (. MYD) are independent of each other.

  
  The primary key index of INNODB and MYISAM is compared to the level two index: it can be seen that there is no difference between the primary key index and the level two index of the MYISAM, the primary key index is just a unique non-null index called the primary key, so MYISAM can not set the primary key.

  
  2. Secondary index (secondary index): Also called a Level two index, refers to all indexes except the clustered index. The leaf node of the InnoDB level two index contains the primary key value instead of the row pointer (row Pointer), which reduces the overhead of maintaining a two-level index when moving data or splitting the data page, because InnoDB does not need to update the index's row pointers.
  3. Overwrite index: Refers to the index contains the data that satisfies the query result, another understanding that should also contain the fields involved in the query criteria. The biggest benefit is to avoid reading the disk data files in the row, InnoDB Secondary index leaf node contains the primary key column, so the primary key must be indexed by the index.

•   master-slave replication : MySQL writes the transaction serially to the binary log by Master before each transaction updates the data, even if the statements in the transaction are cross-executed, and then notifies the storage engine to commit the transaction. MySQL supports three kinds of replication methods, which realizes data distribution, Load balancing, Backups, high availability and failover features.  

  1. Statement-based replication : SQL statements executed on the primary server execute the same statement from the server. MySQL uses statement-based replication by default and is more efficient.

  2. row-based replication : MySQL5.0 begins to support copying the changed content, rather than executing the command on the server.

  3. Mixed Type replication : statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

   

  The replication process is done through three threads,
  1. Master Binlog output thread : 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, the thread is responsible for pulling binlog content from master into its own relay log;
  3. Slave SQL Thread : is responsible for executing the statements in the relay log.

• Max_ Connections and   max_user_connections : max_connections The defaults to 151, which improves performance when MySQL is used with the Apache Web server. Mysqld actually allows max_connections + 1 client connections, with an additional 1 connections reserved for accounts with super privileges. Linux or Solaris supports at least 500 to 1000 concurrent connections or up to 10,000 if available RAM, Windows limits open TABLESX2 + open connections < 2048. max_user_connections Specifies a value that is not 0 to limit the maximum number of connections that any account can open at the same time.
Setting account Resource Limits:mysql permits Limits for individual accounts on Use of these server resources, if you want to restrict a specific account, create user or alter user can be used, such as ALTER user ' Francis ' @ ' localhost ' with max_queries_ Per_hour 100.

   any statement. A client can issue counts against the query limit, unless its results is served from 
   the Quer Y cache. Only statements that modify databases or tables count against the update limit. 

   

MySQL related Knowledge points summary (i)