डेटाबेस index कैसे काम करता है, और index जोड़ने से हालात कब बिगड़ जाते हैं?

index एक अलग sorted स्ट्रक्चर होता है, आम तौर पर B tree, जो column की वैल्यू को row की जगह से जोड़ता है ताकि इंजन स्कैन करने के बजाय सीधे seek कर सके। इससे उन columns पर filter या sort करने वाली reads तेज होती हैं। इसकी कीमत writes पर चुकानी पड़ती है, क्योंकि हर insert, update और delete को उसे बनाए रखना पड़ता है, और स्टोरेज भी लगता है। कम selectivity वाले columns और ऐसे index जिन्हें कोई query छूती ही नहीं, आम तौर पर घाटे का सौदा हैं।

index दरअसल एक या ज्यादा columns की sorted कॉपी है, जिसमें rows तक वापस जाने के pointers होते हैं, आम तौर पर B tree, ताकि इंजन हर page पढ़ने के बजाय binary search कर सके। इससे बड़ी table पर filter करना sequential scan से हटकर मुट्ठी भर page reads बन जाता है। कीमत यह है कि हर write को index भी अपडेट करना पड़ता है, तो आठ index वाली table insert पर आठ गुना चुकाती है, और bulk load एकदम धीमे पड़ जाते हैं। दूसरी चीज जो मैं देखता हूं वह है selectivity। अगर कोई column boolean है और नब्बे प्रतिशत rows में true है, तो planner आम तौर पर स्कैन ही करेगा, क्योंकि इतनी rows तक random access क्रम में पढ़ने से महंगा पड़ता है। मेरे सामने एक केस आया जहां किसी ने तीन वैल्यू वाले status column पर index लगाया और कुछ भी बेहतर नहीं हुआ। असल में जिस चीज ने ठीक किया वह था tenant id और created at पर composite index, जो filter और sort दोनों से मेल खाता था, तो डेटाबेस seek करके क्रम में पढ़ सका और अलग से sort करने की जरूरत ही नहीं पड़ी।