1. Index type

   BTREE  :  b-tree , b+tree  , b*tree
   RTREE  
   FULLTExT
   HASH

2. Differences and relations between clustered index and auxiliary index

• difference:
  1. Generally, the primary key is selected to generate the cluster index. There can only be one table. If there is no primary key, the unique key is selected, and the hidden rowid is not selected, the hidden cluster index is automatically generated
  2. Clustering index leaf node stores the whole row of table data The range of ID values of branch node, root node and leaf node
  3. There can be multiple secondary indexes
  4. The secondary index, leaf node, stores the index column value + primary key

Relationship:

When executing a query, select * from t1 where name = 'bgx';

1. First, quickly lock the primary key ID of bgx according to the index of name
2. Query the cluster index back to the table according to the ID column value to obtain the whole row
3. Administration

 show  index from city;
   alter table city add index idx_name(name);
   alter table city add index idx_a_b_c(a,b,c);
   alter table city add index idx_a(a(10));
   alter table city add unique index idx_a(a(10));
   alter table city drop index idx_name;    

1. explain / desc
   type : ALL index range ref eq_ref const(system) NULL
   key_len
   Joint index application "road"*****
   – when creating a union index, select the leftmost column and the column with the least duplicate value
   alter table keyt add index idx(a,b,c);
   – example:

-- In which cases can the above index be applied perfectly.
desc select *from student where xname='Zhang San' and xage=11 and xgender='m';
desc select *from student where xage=11 and xgender='m' and xname='Zhang San' ;
desc select *from student where xgender='m' and xname='Zhang San' and xage=11 ;
-- Affecting the length of Federated index application.
-- The leftmost column of the union index is missing,No index
mysql> desc select *from student where xage=11 and xgender='m'  ;
-- Missing middle part,Only the index part before the missing part can be taken
mysql> desc select *from student where xname ='Zhang San'  and xgender='m'  ;
-- In query criteria,Unequal query occurred(> ,< ...like )
mysql> desc select *from student where xname ='Zhang San' xage<18 and xgender='m'  ;
The federated index applied truncation to columns with unequal lengths.
-- Multi clause
 according to select Create a federated index in clause order.
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose

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

1. Index application specification

1.1 conditions for index creation
(1) You must have a primary key. It is recommended to have a self growing ID column
(2) It is often used as the condition of where condition column order by group by join on, distinct (business: product function + user behavior)
(3) The column with multiple unique values is the leftmost column of the joint index
(4) For index columns with long column values, we recommend using prefix indexes
(5) Reduce index entries. On the one hand, do not create useless indexes and clean up infrequently used indexes. percona toolkit(xxxxx)
(6) Index maintenance should avoid busy business periods
(7) Do not build indexes on frequently updated columns
1.2 development specifications

• (1) There are no query criteria, or the query criteria are not indexed

mysql> desc select * from city; 
mysql> desc select * from city where true;
mysql> desc select * from city where 1=1;
mysql> desc select * from city where name='jinan';
mysql> desc select *from student where xage=11 and xgender='m'  ;

- (2) The query result set is most of the data in the original table. It should be 20-30%above.
1000w   200w-300w  ----> It may cause index invalidation.
Solution:  Add upper and lower limits to range query

- (3) The index itself is invalid and the statistics are untrue,Not updated in time
 It ran very fast a few days ago,Suddenly one day it slowed down.
desc select * from city where name='jinan';
Solution: 
        1. Manually trigger update statistics
        ANALYZE TABLE city;
        optimize table city;
        2. Rebuild index

- (4) Query conditions use functions on or operate on index columns, including(+，-，*，/，! etc.)

mysql> desc select * from city where id-1=9;

- (5) Index invalidation due to implicit conversion.This should be taken seriously.It is also a common mistake in development.
mysql> desc  select * from tab where telnum='110';

- (6) <> ，not in No index (secondary index)
- (7) like "%_" The percent sign is at the front
- (8) Joint index specification
 Joint index(a,b,c) ---->   bc  ---> c  No index   
Joint index(a,b,c) ---->  ac   Only part can be taken 
Joint index(a,b,c)  Inequality in the middle(> <  like)

2. Expansion

2.1 AHI adaptive hash index
mysql> select @@innodb_adaptive_hash_index;
Principle of Adaptive Hash Indexes
The InnoDB storage engine will monitor the search of the secondary index. If a secondary index is found to be accessed frequently, the secondary index will become a hot data. At this time, the establishment of hash index can improve the speed. The frequently accessed secondary index data will be automatically generated into the hash index (the data accessed for three consecutive times recently). The adaptive hash index is constructed through the B + tree of the buffer pool, so the establishment speed is very fast. Moreover, there is no need to build a hash index for the whole table. The InnoDB storage engine will automatically build a hash index for some pages according to the access frequency and mode.

View usage:

show engine innodb status ;
You can decide whether to use the adaptive hash index by observing the SEMAPHORES section in the show engine innodb status result. If you see a lot of threads in btr0sea If RW latch waiting is created on the C file, it is recommended to turn off the adaptive hash index. The competition caused by AHI in high concurrency mode needs to be closed

Set parameters

innodb_adaptive_hash_index=on/off
2.2 MySQL Insert Buffer technology
Insert buffer technology. For the insert and update operations of non clustered indexes, they are not directly inserted into the index page every time, but first inserted into memory.
The specific method is: if the index page is in the buffer pool, insert it directly; Otherwise, first put it into the insertion buffer, and then merge it with the index page at a certain frequency,
At this time, multiple inserts in the same index page can be merged into one IO operation, which greatly improves the write performance.
This design idea is similar to the LSM tree in HBase. It is modified in memory, reaches a certain amount, and then merged with the data in disk. The purpose is to improve the write performance

So how does insert buffer reduce random IO What about the?

Every time, the insert buffer will merge the secondary non unique indexes in the insert buffer.
Usually, it will merge N modifications into the index pages of the same btree index, thus saving a lot of IO operations.
After testing, the insert buffer can increase the insertion speed by 15 times.
After the transaction is committed, the insert buffer may still be merged and written.
Therefore, if the DB is restarted abnormally, in the recovery phase, when there are many secondary indexes that need to be updated or inserted, the insert buffer may take a long time, or even several hours.
At this stage, disk IO will increase, which will lead to significant performance degradation of IO bound queries.
2.3 Index Condition Pushdown (ICP)
mysql is an optimized way to retrieve row data from a table using an index, mysql 5 6 start support

Before MySQL 5.6, the storage engine would traverse the index to locate the rows in the base table, then return to the Server layer, and then filter these data rows according to the conditions after WHERE.
After mysql 5.6 supports ICP, the index can be used if the WHERE condition
MySQL will put this part of the filtering operation into the storage engine layer, and the storage engine will read the satisfied rows from the table through index filtering.
ICP can reduce the number of times the engine layer accesses the base table and the Server layer accesses the storage engine.
Joint index (a, B, c) - -- > AC can only take part
No ICP
A - > load the data satisfying the condition a from the disk into the memory, and then C filter the desired results = = = = > sql layer - >
With ICP
A ----- > A + C = = = = > sql layer

mysql> SET  @@optimizer_switch='index_condition_pushdown=on
mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
2.4 MRR My full name is Multi-Range Read
Optimization，Yes, the optimizer will randomly IO Convert to order IO To reduce the cost in the query process IO Overhead is a means of this pair IO-bound Type SQL Statement performance is greatly improved, which is suitable for range ref eq_ref Type query
MRR Several benefits of optimization
1.To change data access from random to order, query auxiliary index is to sort the query results according to the primary key and find bookmarks in the order of the primary key
2.Reduce the number of times pages in the buffer pool are replaced
3.Batch processing operations on key values

mysql> SET  @@optimizer_switch='mrr=on,mrr_cost_based=off';
mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)
2.5 Query for multi table join
Simple Nested Loops Join(SNL)，Simple nested loop algorithm
Index Nested Loops  Join(INL)，Index nested loop join
Block Nested Loops  Join(BNL)，Nested Block Loop Join 
Batched Key Access  join(BKA) ,  BNL+MRR

explain:

1. batched_key_access=on
2. mrr must be on, mrr=on,mrr_cost_based=off
3. Driven tables and associated columns must have indexes
   effect:

1. Reduced Nested Loops frequency
2. When a non driven table will be scanned,A large number of random IO Change to order IO

A
id   name   age      
1     zs     12
2     l4     13 
3     w5     14

B 
id    addr   telnum 
1     bj      110
2     sh      120 
3     tj      119    

select name,age,telnum
from a  join b  
on A.id=b.id
where name like  'Zhang%'
Improve table join Performance algorithm.
When be join When a table can use an index, it is first arranged in order, and then retrieved join My watch sounds like MRR Similar, actually MRR It can also be imagined as a secondary index and primary key of join
 If by Join If there is no index on the table, the old version is used BNL strategy(BLOCK Nested-loop)

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

mysql> show variables like 'optimizer_switch%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on, ,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
1 row in set (0.00 sec)