I've had this problem on one of my sites for a while now. We go to a category in our wholesale side of our site and multiple products are missing. It will still show some products (eg. 2 out of 24 products showing) and the page will work as normal but we cannot find the missing products.
If we view the category in the backend it shows all the products are still there but they are not showing on the front end. I have checked the products and they are all enabled, in the correct website and set to be visible in categories.
Further investigation showed that the issue is with the table catalog_category_product_index
If I do an sql check of the table catalog_category_product I can see all 24 products attached to the correct category.
If I do an sql check of the table catalog_category_product_index I can only see 2 products.
I am able to fix this issue by saving the category in the back end, this will force the catalog_category_product_index table to regenerate and include the missing products.
The problem is this issue is a recurring issue, multiple times we have had categories which are missing dozens of products and are only discovered by clients.
The last time this occurred I tried a few different methods of regenerating the table to fix the missing records but none of the bellow methods worked:
php bin/magento indexer:reindex catalog_category_product
php bin/magento indexer:reindex catalog_product_category
setting these two indexes as "invalid" in indexer_state then reindexing all.
As the client is creating these categories and adding these products via the admin regularly I am not sure how the problem initially occurs. When you save in admin it fixes the issue so I am unsure how them saving in admin could cause the issue. I have tried to investigate and while I can find the error between these two tables I have yet to find how this error keeps coming about and why it only affects some scattered categories/products.
If I cannot find why the problem keeps occurring an alternative would be to find a method that lets me remove the error. For example if there was a reindex command that I could create a cron job for.