Best Practices for Database Indexing

How do database indexes work, and what are the best practices for implementing them in high-traffic applications? How can proper indexing improve query performance without negatively impacting write operations?