This deep-dive assumes you have a decent understanding of JavaScript fundamentals, including the differences between when synchronous and asynchronous code is executed, short-circuit expressions and short-circuit execution, and IIFEs.
The basics
The first thing you need to understand about functional components is that they are functions. For all intents and purposes, they execute every line of code, from top to bottom, on every render. That's why if you put a console.log() in the middle of a component body, outside of a hook, it seems to run almost constantly, every time any part of that component changes state - because it does. Every variable gets re-declared, every function gets re-invoked, every time. And yes, this means that on every render, every hook that you've invoked gets invoked again.
The magic of hooks is that they don't execute the exact same functionality on every invocation - instead, their function bodies are written such that they conditionally execute based on the context in which they were invoked, and the state of the component and of your app at large.
A functional component has three basic phases that it goes through:
Mount, the first invocation, when the component first renders to the page
Update, often referred to as Rerender, when the component re-invokes as its internal state changes
Unmount, the act of the component removing itself from the DOM