1.mysql main storage engine MyISAM and InnoDB

    MyISAM is suitable for some applications that require a lot of queries, but it is not very good for a lot of write operations. Even if you only need to update a field, the entire table will be locked, and other processes, even the reading process, cannot operate until the update operation is completed. In addition, MyISAM is super fast for calculations like SELECT COUNT(*), which works well if only used as a query. 

   InnoDB tends to be a very complex storage engine, and for some small applications, it will be slower than MyISAM. He is that it supports "row lock", so it will be better when there are more write operations. The main thing is to support affairs.

2. MySQL common commands

   SELECT `ID`, `USER`, `HOST`, `DB`, `COMMAND`, `TIME`, `STATE`, LEFT(`INFO`, 51200) AS `Info` FROM `information_schema`.`PROCESSLIST` / / show current process   

    show full processlist; //Display the current process   
    SHOW VARIABLES LIKE 'max_connections'; //View the maximum number of connections
    SHOW STATUS LIKE 'Threads_connected'; //View the current thread connection number
    SHOW STATUS LIKE 'Threads_running'; //View the current thread running connections Number
    set global max_connections=1000; //Can be modified directly by command 
   
   

3. Connection pool configuration
     spring.datasource.initialSize=5 //Number of initial connections
     spring.datasource.minIdle=5 //Minimum number of connections
     spring.datasource.maxActive=20 //Maximum number of connections
     spring.datasource.maxWait=60000 // Configure the timeout time for connection acquisition. After druid is configured with maxWait, the fair lock is enabled, and the concurrency efficiency will decrease. Generally,
     spring.datasource.timeBetweenEvictionRunsMillis=60000 is not configured. Connection, in milliseconds
     spring.datasource.minEvictableIdleTimeMillis=3000 //Configure the minimum survival time of a connection in the pool, in milliseconds
     spring.datasource.validationQuery=SELECT 1 //Configure the sql statement for check execution
     spring.datasource.testWhileIdle= true //check if idle      
     spring.datasource.testOnBorrow=false //Check whether to get the object from the connection pool      
     spring.datasource.testOnReturn=false //Check whether to put it back into the connection pool

 4.sql parsing order
    1.FROM clause assembles data from different data sources
    2.WHERE clause filters records based on specified conditions
    3.GROUP BY clause divides data into multiple groups 4.Use
    aggregate functions for calculation
    5. Use the HAVING clause to filter groups
    6. Evaluate all expressions
    7. Use ORDER BY to sort the result set

5.
      Not every field can be indexed. For example, if the text is too long, the maximum length of mysql InnoDB is 767 bytes, and full-text indexing is not supported. MyISAM only supports full-text indexing

      Often use explain to view the sql execution plan

      Single table query
      explain SELECT * from test_user where user_name = "haha 2017-02-15 14:39:09"
      CREATE index index_name on test_user(user_name) 231 milliseconds before adding index 2 milliseconds after adding index  

      Two-table query
      explain SELECT * from test_user user join test_teacher tracher on user.foreign_key=tracher.id limit 10,10; 106 milliseconds
      CREATE index index_foreign_key on test_user(foreign_key) 1 millisecond after adding index 

      Several cases of giving up the index

      1. Like when it is all fuzzy, like "%key%"
      2. Use the mysql function CONCAT(add index field, "abc") 3.
      where to judge the NULL value of the field key is null
      4. where use or key=1 or key1=2
      5.where use inequality key!=1 key<>1
      6.where use not in key not in (1,2,3) 7.Full
      table scan is faster than index scan

 6. Optimization of commonly used sql

     1. To optimize the query, try to avoid full table scan. First, you should consider building indexes on the columns involved in where and order by.

     2. It is best not to leave NULL for the field, and fill the database with NOT NULL as much as possible.

        Optimization scheme: set default value for each field

     3. Try to avoid using the != or <> operator in the where clause

        For example explain SELECT * from test_user where user_name !=" Haha 2017-02-15 14:39:09" 

        Optimization plan:
            explain SELECT * from test_user where user_name = "haha 2017-02-15 14:39:09" or user_name = "haha 2017-02-15 14:39:10"

     4. Try to avoid using or in the where clause to connect conditions

          Optimization plan: There is no way for business requirements. It must be ensured that all fields of or have indexes. If one of them is not added, the index will be discarded and a full table scan will be performed.

     5. Try to avoid using not in in the where clause 

         explain SELECT * from test_user where user_name not in ("Haha 2017-02-15 14:39:09","Haha 2017-02-15 14:39:10","Haha 12017-02-15 14:39:10 ")

         Optimization plan: Depending on the business, not in should be used rarely, change to other methods or use in.

     6. For continuous values, if you can use between, don't use in, you can use > and <, but also don't use in

     7. Try to use exists instead of in

     8. You should try to avoid performing expression operations on fields in the where clause, that is, functions or operations key-1=1 , oracle supports functional indexes, which seems to be supported after mysql5.7.
        explain SELECT * from test_user where CONCAT(user_name,"1")="haha 2017-02-15 14:39:09" 

        Optimization plan 
        explain SELECT * from test_user where user_name=CONCAT("haha 2017-02-15 14:39:09", "1")
    
     9. Update statement, if only 1 or 2 fields are changed, do not update all fields, otherwise Frequent calls will cause significant performance consumption, and will also update the index, while bringing a lot of logs. ,
          using hibernate and jpa save and update operations will be fully updated

          Optimization solution
           Internet high-concurrency projects are best not to use the cumbersome persistence framework like hibernate, and change the way of mybatis handwritten sql (this needs to test the programmer's sql writing ability).
           If hibernate is used, it is recommended to update by handwriting, and against for loop to come out one by one update and fully updated code

           10. The further the limit           is , the
           slower      it is, and the larger the data, the               slower
           

     11. SELECT COUNT(*) from test_user is best to use count(id)

     12. The more indexes the better, although the index can improve the efficiency of the query, it also reduces the efficiency of the insert and update, because the index may be rebuilt during insert or update, so how to build the index needs to be carefully considered, depending on the specific Depends. The number of indexes in a table should not exceed 6. If there are too many indexes, you should consider whether it is necessary to build indexes on some infrequently used columns. But the foreign key must be added such as loginId

     13. Try not to design the fields containing only numerical information as character type, which will reduce the performance of query and connection and increase the storage overhead. This is because the engine compares each character of the string one by one when processing queries and joins, whereas only one comparison is required for numbers.

     14. You must use varchar/nvarchar instead of char/nchar. After mysql5.5, the default character is Unicode encoding, so there is no nvarchar type

     15. Try to avoid using select *, you should replace * with a list of specific fields, and don't return any fields that are not used.

     16. Try to use limit paging when the number of returned items is large

     17. Avoid using list.size() to get the total number of items after SELECT * from test_user query

     18. When join is associated, the more where conditions in a single table, the faster the joint query

     19. It is best to use the mysql5.7 native json type to store json strings

     20. Use LIMIT 1 when there is only one row of data

     21. Each table must have an id primary key of self-increasing int. Using the VARCHAR type as the primary key will degrade performance. The performance and settings of the primary key become very important, such as cluster table splitting

     22. At present, the biggest bottleneck in the project is the database. If necessary, use memcached/redis cache to reduce the load of the database.

  With specification

     database boolean.....