Location>code7788 >text

Interesting Talk on Code Field #1: Index

Popularity：158 ℃/2025-03-27 19:15:58

Index: "GPS Navigation" for MySQL🧭

Have you ever searched for that precious record in the ocean of millions of rows of data? MySQL queries without indexes are like finding exits in a forest without maps - both tiring and inefficient!

What is index? 🤔

Simply put, the index is the "directory" in the database. Imagine if you were looking for a word in a 1000-page dictionary, what would you do?

  • No indexing method: Starting from page 1, turn page by page until you find (full table scan) 😫
  • There is an indexing method: Turn directly to the letter label on the side of the dictionary, quickly locate (index query) 😎

The internal secret of the index 🔍

MySQL: My index is a B+ tree structure~
 Other databases: What a cool installation, isn’t it just a tree!
 MySQL: But my leaf nodes are bidirectional linked lists, do you have one?  snort!

Advantages of B+ Tree Index

  1. Shallow level: Usually there are only layers 3-4, so finding data is very fast!
  2. Sequential access: The leaf nodes are connected to a linked list, and the scope query is not difficult!
  3. High fan-out: A node can store many indexes, saving space!

Common index types 🏷️

Index Type Personal description
Primary key index The "ID card" of the database is unique and non-empty, with the highest status
Unique index Have a cleanliness index, the same value? It doesn't exist!
Normal index Civilian index, no special requirements, easy to get along with
Joint index Team players, multiple fields join forces to attack
Full text index Literary enthusiasts, specialize in finding text content

Embarrassing moments of index 🙈

Not all the time the index is so powerful, and sometimes it also "strikes":

  1. UsedORAnd one field has no index

    -- MySQL: I chose to scan the full table for this query...
 SELECT * FROM users WHERE indexed_col = 5 OR non_indexed_col = 10;

  2. Using functions on index columns

    -- MySQL: What did you do with my index column?  !  I don't recognize it anymore!
 SELECT * FROM users WHERE UPPER(username) = 'ADMIN';

  3. use!=or<>Operator

    -- MySQL: There are too many negative conditions, so I'll scan the full table...
 SELECT * FROM products WHERE category_id != 3;

Maintenance cost of index 💰

DBA: Why is the server so slow?
 Development: I indexed each column so that the query is faster!
 DBA: *Sudden*

The more indexes, the better! Each index needs to occupy disk space, and it needs to maintain the index when inserting, updating, and deleting data, which will reduce the performance of write operations.

Tips for using indexes 💡

  1. Often appear inWHEREThe column of the clause should be indexed
  2. Columns with strong uniqueness are more suitable for indexing
  3. Short indexes are usually better than long indexes (with less space and fewer comparisons)
  4. Joint index follows the "leftmost prefix principle" (just milk tea must be used first before adding pearls and coconut fruit)

remember:A database without index is like a library without directories, it looks neat and crashes when used!

MySQL: I can't have a beautiful interface, but I must never have an efficient index!

I hope this article gives you a more interesting understanding of MySQL indexing! Next time the interviewer asks, you can smile and answer confidently! 😉

<Previous Next>