How to optimize the join statement of MySQL

From MySQL performance optimization and optimization:
1. Minimize the total number of nested loop cycles in a join statement: A small result set is used to drive a large result set;
2. Optimize the inner loop of the nested loop: that is, the number of loops is reduced to many;
3. Ensure that the join field of the table driven by the join statement is indexed: it is also a method to optimize the inner loop of the nested loop;
4. When the koin field of the driven table cannot be indexed, set the value of join_buffer to a greater value, provided that there are sufficient resources;