php - Some questions about adding index to mysql database

Question

I have some questions about mysql indexes. I have consulted a lot of information, but the information is very vague. There are several types of indexes and I don’t know how to add them:
1. How to add groupby indexes? Add an index alone or add it together with search conditions?
2. How to add a query index with LEFT JOIN, for example

        SELECT a.id,FROM a
        LEFT JOIN b
        ON a.id = b.aid
        WHERE a.year = 1 AND b.month = 2
        GROUP BY b.a
        ORDER BY a DESC
        

3.Like there

SELECT a.id,SUM(a.q+a.w) as day_pv FROM a
ORDER BY day_pv DESC

How to add such an index?

Things about indexing and mysql statement optimization are very weak.

Or if there are any related articles or books you can recommend

Answer 1

Summary of mysql composite index and ordinary index

What about books: High-Performance MySQL (Third Edition) Indexing Part, the one translated by Taobao guys

Answer 2

Building indexes is mainly based on the query conditions of the table. In principle, indexes are created on fields with high data selectivity, such as person IDs, names, etc. Based on such conditions, the query result set can be greatly reduced.

Answer 3

High-performance mysql third edition, Chapter 5, specifically explains the creation of high-performance indexes. The indexes are mainly established for different query fields of the table, in order to shorten query time and improve performance

Answer 4

Generally, fields used for sorting need to be added with B-tree indexes, where fields need to be indexed, multiple conditions need to be combined with indexes, and the index order must match the conditions