Mysql Index Optimization

2. Storage classifications for indexes
   MyISAM Storage Engine table data and indexes are automatically stored separately, each is the only one file
   The table data and indexes of the INNODB storage engine are stored in the same table space, but can consist of multiple files.
   Mysql does not currently support function indexing, but it can index a part of a column earlier
   For example, the Name field, which can be indexed by only the first 4 characters of name, reduces the size of the index file.
   Mysql> CREATE INDEX ind_company_name on company (name (4));
   Description: Ind_company_name index name, company table name
4. Using a like query, the index may be used if a constant is followed, and the% number is not the first character.
   Eg:mysql> SELECT * from T1 the where id like "3%"; Recommended
   Mysql> SELECT * from T1 the where id like "3%"; Not recommended (index not used)
6. If large text is indexed, full-text indexing is used instead of like "%....%"
8. If the column name is an index, use column_name is Null to use the index.
   Eg:mysql> DESC SELECT * From company where name is Null\g
10. Index is present but not used
12. If Mysql estimates that using an index is slower than a full table scan, the index is not used.
    For example, if the column key_part is evenly distributed between 1-100, it is not good to use the index when querying.
    Eg:mysql> SELECT * FROM table_name where key_part>1 and key_part<90;
14. If you use the Memory/heap table and the "=" in the Where condition does not apply to the indexed column, the index is not used. The Heap table uses the index only if the "=" condition is used.
16. The condition that is separated by or, if the column in the condition before or is indexed, and the subsequent column does not have an index, then the index involved is not used.
18. If it is not the first part of the indexed column, then this index in the query will not be used by Mysql.
    Eg:mysql> DESC SELECT * FROM sales where moneys=1\g
    Moneys has a composite index above it, but moneys is not the first column of the index.
19. If the column type is a string, but when the query copies a numeric constant to a character's column name name, the Name column is not used, although it is indexed.
    Eg:mysql> DESC SELECT * FROM company where NAME=123\G (string index using shaping data query, index not used)
    Mysql> DESC SELECT * FROM company where name= "123" \g (using the index above)
    View Mysql Index Usage
    Mysql> Show status like ' handle_read% ';
    If the index is working, the value of Handler_read_key will be high, which represents the number of times the index has been used.
    A high value of handler_read_rnd_next means that queries run inefficiently, and Index remediation should be established.
    
    

    2 Simple and practical optimization techniques
    Aperiodic analysis tables and checklists
    Mysql> ANALYZE [LOCAL | No_write_to_binlog] TABLE tab_name [, tab_name1] ...;
    This statement is used to analyze and store the keyword distribution of the table, and the results of the analysis can make the system get accurate statistical information. Enables SQL to generate the correct line plan.
    eg:mysql> Analyze table sales;
    The syntax for the parse table is as follows: (check one or more tables for errors)
    mysql> CHECK TABLE tab_name [, Tab1_name] ... [Option]...option={quick | FAST | MEDIUM | EXTENDED | CHANGED}
    eg:mysql> Check table sales;
    Bregularly optimize tables
    To optimize the syntax format of a table:
    Mysql> OPTIMIZE [LOCAL | No_write_to_binlog] tab_name [, tab_name1]..
    If you have deleted a large part of a table, or have made a lot of changes to a table with variable-length rows, you need to optimize it regularly.
    This command merges the space fragments in the table, but only works on the MyISAM, BDB, and Innodb tables.
    eg:mysql> optimize table sales; (Do not use in the case of Mysql access number)
    
    Optimization of common SQl
    Mass Insert Data
    When importing data with the load command, the appropriate settings can increase the import speed
    For myiasm storage engine tables, you can quickly import large amounts of data in a single way
    ALTER TABLE tab_name DISABLE KEYS loading the data
    ALTER TABLE tab_name ENABLE KEYS
    DISABLE keys and ENABLE keys are used to turn on or off updates to non-unique indexes on MyISAM tables, which can increase speed and invalidate Innodb.

Mysql Index Optimization