SQL Server 索引知识-结构,实现

9/1/2015来源:SQL技巧人气:3892

SQL Server 索引知识-结构,实现

索引的作用毋庸置疑,但他是如何组织,并实现提高语句访问效率的呢?本篇文章为大家做个详细的介绍.

聚集索引架构 B-tree

如图1-1

a.B-tree的结构,叶子节点为数据.数据按照聚集索引键有序排列.

b.每个表只能有一个聚集索引.

c.创建时如果未声明Unique,索引字段有重复值会内部添加唯一标识符(4字节)额外维护

PR+Q0ZsMZRkCACCU04eCoP/5ufy6FX7eKH8DKSicV/ygigCAkgSAJAMgQbm/LSRDACCQIIwDGJVBEsAUhFL2zhDCjIoAADIEMoQAApgCckKGSQDlEw0sKAOYAjAJQCYQpDJa+pHtLAUFBQWFUchpOugAgOm2SkoCspQ2w0nIEKb6Wz9yv1cG2jMZl5xQEYCnsAsY7vHwp1RQ+Efp6Qm7RI/Fwjud7lCoU5b7W+sKCgojyulUBDMqkiQAUhDGujsDZtynawxo6jq09R3ahk5dQ5euPqSt73Kwkc5AMtYnyyAJ5MRAp9vQQ4Juh6VDU9uta+jQNoW0qqCuoV1XF9LVdevrA9p6v7YupKsPaup8zZXdojW9yzn72hV+LgAAZFm2C66jR4v27jm6d8/Rffty9+w+rFYbkslUepMRvkQFhZ83P1y2ACCUAJQgdBO6qg/n6abegT32e/XYXzff9zvd/b9F7v91y32/Kpj596LPFrpRLYRQglIcSulxpiHIABq+XKq65d/19/++aewljff9VvPAb80Tr3HPvNX81J/rx/5P+diLm8b+qvGeXxbc8p/G9Z+cfCU/CnDSA6j0+f0sSSWlcG+0sKByz+6jBfmVRYVVRQVVuTnFO3fuczrdEA77d5OCgsJPx6lVBE4EyFCGMIg1ER/NYSZfh0+7UTf30eaXH2uZ85h2zqONM8YWPH1txbM3aD55I9LuBxAkBn61QX8vG4AQQhlCdPf6usljm2c91jj7CdXLTxnn/J2feqPjmT9SE29qmP1ozcyHNTMfbnnpkbLJD2CHdwy6EADlgS67TPEeONHl1/9Jkq70kzNlEScVTEAJQgnA7Oo/AKGs6OnCJhyOMoyQe6zy4NGy3Pzq/MKaoqLqwqKqvfuO4gQz0lenoKBwZipKD/5EqZa25TOZSTc6ju2Idgb6OgKRjkCkI9Ad9BPff2WY/VfDw5e78/bH49GELMtg8BFgZuwpFgmHg+2RUCDSEYx0hKJBP/nR7KJr/rfuk/mRjmAkFIiGAtFQIBwMxPvCQJIkKZWUgDy4XZMCKVlOAiBLgxUiQSBLspyCIAWjIJkAEsi8Omi7JEgXAcoAyABKAEqKiS5sQqGuisqmIzlVh/PrjxQ27DtacuR4aV5R1f7Dx1VqPVC6gRUURpofoyJG5/30RWzijWJlwZBt21mT8+hmZtJd5rUfdLWJyVRSHrjRaLCKTgb/bF7B5WNMXy48+fxDyrlj8XgiEYcQpqAclzN9/BKEiWg0GYsNbJaKQ5AAESkRT6uo/6TxWCwRS6RkCNPFGKkkAJIM01G4kAkGO8srGvfsKzpwrPrASEOt3x89lFt8rKBi35G8Jo1BAlBWVKSgMKKcVkWZzqvMb2qUNYorZmonXm8uOQohlGQIUhBKMJH5NY6h8x7SLHislaiT4tFMuVymgw4AKAOQqc7O1NpBACGUpBS7/MXmK8ewK+cCCCCUZCCl71aSIGgXRWN+LleW29pYSu5YW//54pZV7xI7vnZxRByAJIAedQ327WL045fxT98g139C7dvQZiUSEkikpGRUlhIyhLA31ErkbsO3fKL7/K2mzxc2f/mWreRgrLczKaUkIAMgS8pAwYVOKNRVXdOye2/+lu+Pb9+bv37b/kO5JYXlDZt3HahuaJaB0ipSUBhhzlBFEEIYZQ2+FTNMz91gK8uBEMoQShBKECYhTMmpSNBBzbqbXjI+SDVKsSiQoSz1j+xkbDZERRBCKEsSvXyW6sox7MpX0xUS6TtcIUhBCO11NQefGtv4+JXa8VcX/eW3xS/cc/hvV3x39x9cJk2vz8Mf32949oa8u36RO/7a/Cl/qbjvN1VX/i/DwifDXYIMYTIOEkmYSCX1Oz7Lu+vfq279PxWT7t454YYdYy8qufci+sDmNjspYKatpsjowqa7J9KoMhoxgbK1mcXOwormrbuPbNl9eNXG79RGLJV1l5uCgsKIcEYqAv0q8q6YTjx7nbPyGIQwBTO3tSYh9LMofXwbMv4655qlkVYhmUwAWQJyKktFYEBF2Z12aRU1nEJFTlVV/qT7TE9eop96k+GDaez+dY2fLsxbMLPL5/Gr6zQLXlA//qeGd6Zod32L5Xyne3tK09+van7yz+2mYlmOJyTY1xvpcVjr548re/zP2nlPcHvXqnZ9Xb7i1dpHr9QvmmIt2C9LkcxMEfLo66Lr7OzyuD08bxcE0S647HYXbxd5u2gTnEPC/1BsgmjjnTbeaeNFGy/aBNFmT8eZSXobu2izi4Ldabc77Han4BB5h+tE7OmI6Qh2UUhfj1209b848Hr/u05ecPB2B2938nanzZ5+7LDZHQOP/6E4+8/o5h1ep+jDSPPRY+UVtfpmo7nZZNl7pHTt1j0bduzfe6QQZ6zxlKyoSEFhZDltMXd/3Vn6TsAIbRA/nWGfcjnz1lPYV4uIz99gP1/AfbGQWz4fe/GvzU9ehi55PtrmSUGYAADCGASxoZXT2aVuEEIIZUliPp3VeNUY5otXIYQQSgBIQAYykCCEXk1pzUt3UuN/zX33cayvC0KYSMR7unv6ujq0bz5ddccv2A3L+iLh9AH7ujo6zKRpziOmBU946o/IELa21JgWTTdMut3VVNQbaktvFk+lQtVF2KOXaR69LEg0JCDsS6TAaBsrcNjFympVZU1Li5bUGVmdkdUbmWYN1qTBmrVYkwZr0mBNajQdlQZrVCONaqShxZROY3MmDc2mhmZjfZOxtslU22SqUZlqVMYalaFWZahR6YeLoUalr1bpajJJv2KsURmqGw3VjYaqel1VvbaqXlvVoKtq0FXW6yrq9RX1+sp6Q1W9fmjqtJV1mso6TVWdtqpOW1GnKa/TlNdpymrVZbXq8lp15WlSpxmS8hPRVtQbKusNlQ3GqkZTtQqpbjRV1OrLa3VldbqyWl15ra5Zx2Cs00jaCJZPpGSlWaygMLKc/r4iAKEMZTndaIgyetfnL9pn/Fn/1KUVD19R8+jV1X+/svKRy+v+fpX+4Uu1f/uV+u1nunzO/kqB4X+3weBP/RMqWvlK5hamjIpSEMJWdVnjtNuF56/uUlclIOyWYLpUgWvMqx73R3LKTREbASGU+9ttEMKOY1tMz1yDvju1t9NHbPu85qHLhM9nwcx+/dcgh62vP2i4+X9zW74OQ7kXRAEYtMF5Tkeoq7Cg0mZvb+9OuttTFbXafUeKqxv0vvZur79L9HW4Wzvdvk7RG0rH6Qk63EGHO2B3tTvcgfQDQWwTxDbe6eedfpvTb3G0WRxt5kz8ZrvfbPdxgpcTvJzgM9t9nKOVtbdaBA/He2l7O8G3UbyHsYmszcXa3IzFRVncpNlFmD2EuZU0+xiLi7G4KEsrYQkSlgBlbqfN7ZQ5gHNtONeGsW0Y20ZwPoLzEpwX5zw45yH6gzPuTNhThsjaPjuk2UuavZTFR1laKYuHtoi0xcnYvGZ7Oyf4zY52ixi0iUGnt9vt7zVRPEKaE0lJHm1/iygoXGD8KBXpXJ/NYCZfg7w/i/5+HbX9G2LrV9jWL+mdq+nPF+pnP6x69lpv3TF42tLYISPEP6Si8vopd9un3BTW1yUgDEtyUgIQQqxgl+6ZSwMz74jkf+fR15kbS7xNFQFdo9/Y1Lp2ET/hGur1cR1eK7Vhme7Jy/l3xrvUx71ofZum3tVS6zGo7Krj1Py/4A/8F/7NBxEgxWESyomz8L38SUgkknbBVVXR0haIdYYBQrp37T22ceu+PQfzSFZwuNpFb0daP3ZXezqC2CY42wSnn3e2pt3DO/1Wu89q91kEr0XwmgUvy/syEVpZwccKPoZ301YXbXUxNg9rc9OCl7L5zFbRYnFQVh9qDeBWH2V2k2Y3aXYTnJtgXTjroliRYkWKddJmgeachNmHmD2IWSQ4B2V2U5wPZ/0Y68cYP8r4ccaDsx6c9aCsG83SDEa70kGZUwZj3dkZoqj0VZGckzTbSbOdtLgoq5e2ehmbj+VbGd7HCa1WR5sBtyCkOZ5IKSpSUBhZflBFEpRlIEsQwj5G6/50mmnSDZaKQtBfs5BOIhbvdYnUKw8i8+4T63Ji3Z2u8hzL5g+ZnZ+hu1YSu77CNn/FVxeHPU5ZGtT+kCWJ/XSW6qoxzMpXwAkVyTJIQgi96srqqfdZpt7Qq68GEEpQSpc8CPm7XeMviz1/JTP+qsoJ15Y8d33tczcWPnfboWdvq5twvXbibXWvPRtwsfzGReyEy83PXbf/mSv2TrxKNenW6gljS56+p3TC9ZXPX1Mw7vLSL5emZAlCKINRM1YUCARNRhJDhGAo7vZ2Hs4t3nsgv7Ckfufuozl5ZTqEdvs6bI4TyuGdfpuj1WZvtQmtFsFn5j1m3mMRvBbea+G96acc72VtXtbmZWxexuphrG7G5qatLsoiUhaRtoqM1UVZ3ZjVw3CCleUsgo92hTlHyOoMWMWQxREgOTfBujDGjdNOkrYTrJ3geIIVEcavZQUdRxlZBOEElPUQtJek3STlJkgRoUSUdRtpl4lxm2gXQrtQ2oXSLoQUEVI0kaKROmVMtCs7A/tijBtj3CjjRFkHyjpRRkQZEeVEzCwSZpGyuBmrl7F6GZvX7GjV42aEMieSkjJWpKAwspyRiuSMinSeFdOwSdfaK44N2S5tCH7Va5pnL0HXvhX2edQr3ip89IrCSbcdeeH2vOfvPPrEraqvPwgSWjkRG7TjGajIPPW6Xn0VhFDKVI9DMX+3OP5P7ZOvRF95QPPWM4Y3n9EtGFf65rPH33iuYfHkxvdm1676IOTnzavnk0/+wT79vtz5Tx1eMM648FnDm1MaX5/U/MbTDUsn5b81sXr3RklKAQhSo+eDqK2t3WgkcNwe6oyL7sDBowW5+ZVV9bpDOSUHjxY1aTGXr8Nq92XbKKMi+2AVpdtDvMfMe8y8l+N9HO9jbR7W6mGtbtbmGlZFNCdwnNXiClK+iMXbYW8NOlo7nK0hC+8maDtKiwam1ci0GdlWlHVhrBth2vSsR8/aDAyjZdx6thWn3BTtpkgnTYuMI0DxPhMpGGmXiRIRUkRJESVFhHQipNNEOo3DhnAaCaeJFLMzsC9GuTDKhdIiyjhRVsy0olgR40SCE2mrh7X5WJuP430Wu9+AWTDKqqhIQWHE+SEVAUmGcgokIYRRVuv7dLJ54jWukqMQwkRm2AfIAMQBjEPoz/0Kefwi7SsPhVs9XFWBZuMnpl1fG79fhexaZdz8BVeZ2ykyshTvPzQEEEqSxH46q+mqMVxW2QKUAQAShNCjrqya+hfz1Bt69TUQQhnCJIQShELZIdNTfzZPvanNUNYb6Q2HQn1d7eGuULizMxbuivV1plKxvngY+eI1/d9+G/r45Z7OQHckHO0M9XW1h7vbo52d0e7OaHdXKpZMSqkkSEggeQ6/x2cNACHs7OxWa0xlNS09UdjWEcspqDqSV1Fa07J999G9B/JbtKTH35n2UFo/6Qx0x/W7Z2g4m5uzuVmrKzuMWWTMImMRGYvIWlyk1cuIgRqqde6a6knLiyd/dmzqp/unfnzk5dXVpXi7VWyjOdFEtyNMJ8F1sVwbw4gsKdC0H6MCGNVOcG6aszEkp8WDKOlW4Y7vG537ilUIQiCsz0g4MMKGEjxK8AhuSyf9dHCE/reE4d7NBCMFjLRjlB2j7DhtxxgHzjpJs4vmXDTnYjiX2eKx8a0mxEqSfCqlrF2koDDCnHaRCCADmK0ivfvTqabnrreX50EIpSGzIQDJuXyG7snfNS2dGgkFhj1iPGuOnTNSUUtl5ZS/mKfe2KOvyZxDkgGEIY9dO+Ue7vb/MK9dKkEIIIz3n8Kcu71ixaLa/bvCsYSl7FDlS/cbJ1zZrquDECYhjEGYhFDuasdXvlX55gum6tIkBDKQTl7077ylq6sHwZndB/ObdKSJEvbnlOzaf/z7g/lrN+3OOV6pNTBuXyfv8POONpvdPxCr3W+1+y1Cq5n3DhvO6uGsHtbizk669IAxu2izmzG7KZvP4u86bnL+x5Prx9yz6d/+vv7ix77490fWjXlw39h36wt1PEHzAu+0iT7S4tZzQYTv4GxOM0mzjI1yhEycH6PslMWlYjtQR+e6Iu7iB7be9+rRMrzVxPlMhB0nbAjJIyRvwm0m3GYibAjBnxRb+l1k4F1ymKCUgFICSgooKWDpx7QdZ50UJ6ZtxFk8VpvPiFhIkk+mgNIqUlAYWX6Mijij67MZqonXCzUlQzaV4tGow8JMv6dh0vW6bZ/1dXVmjpC9DYTJ4VTErZjVfM0Y85fDqMjbUlk19X7LtJt6DBkVySlZBjDc20sseM5y7Rj85Yci3a2Z1pKUjHf6m+Y/sfOBKw6880Y4EnOjLbUfv1w79he27SviwdY4hEkIExB2oQ21Yy/edemYiu2rUhDK4FTlfucjwWAHyVhzC2uO5FUcL67duuvwui17Nu44sOdgQVFpo0bPujwDKjoRq73Nam+zCH4z7xs2nNXLWb2sxZMdxuJmLG7G7KbNbtrsJm1eiy9YZOQveWHnv044+sgn1Qu31M9ep776tbpfPnfg29wWLWllxNZaQvi+xLAm33xQ2066AxaGttlsjWz7pnzbulyqyOBC3d2o2PnaRs2Ym7f9YXrZ2hKzhhBQwk4QwhAVmQj+pNiGvDusihBKQNIGogQ0/Zi2Y4yD5JxpFbEWj9nmMyBmXFGRgsJ5wI9QUZjRCR+9gI3/g/3DmaGj24P71gb3rQruXxU4tAH9YG75+DtVT9/AHt/V2x2IJ5MykAFMQZhMr84qgbgspyCUBtbV61dRilo2ve6SMfQnswYq6AZU5FaVlU64nXvuyh5dZXonKSUlEnICwk51pbD8FfVz1zcvfJpZs7R132rbyjeaZz5Q9+Rl/N6vumxkLJ7s6+3usFOqpc/VjbuiZc7DwuYPAvu/9WxdXjvtnuYXbsE/mNrpJJJASkij5bYSACEMBEMYyRpJG8GJKi1R1aCvaTLpUDNK2QuKG5vUlOjpttnbeEf7mauIsw2V0CAhmd3pj2/S4uH9nUUGx+8m7//F1MLZR9jjhG9Pk/dvy+r+MHXf99VoJdP66nbi8knbr3hh8zUvHbx25oGPD5qYQKzU5H5g3uHLZ5ZfMbP4jrmHt5RyOc32B5cUjnmm+f9Mbp694liTicQoO0k6TCRvInkjYTvzpHcZGkoYCJKlIoJxkKyT4kTG7OasXp2RwyhbUlJUpKAwwvxwMTfon8e0w4yTq5YyLz9EP3cbMuE207O3mybcappwq2HcTfXTxpYvehYv3C2lkjIE8VQqKaUkWQIgmV7lFUAZAPnk1oCSS9g3y4pvv5RYm16dCKRgehJTGUIo6pvy54w3vvxYh0md3lySUslkIp6SUwAkYhE0b0vDzAdbxt3aNO6mlul/a54/iczdLctJCGE0loxLEoSwVXTUfvF2wxvjDc/ego27Dhl3g/rdGS7KmIhGIIQJKZ6QIpIch6OEYDCEErQB5wJd8dZgpDUQ