The original concept of an array was pointer to an address in memory of the index of the first element of the array. Thus the index acts as an offset. This originated in C (the language the C# syntax is taken from) and probably even before.
So... in memory, lets say your array exists at 0xFFFF0000 (just a made up address) and contains { 4, 8, 7 }
You'd have the following...
0xFFFF0000 = 4
0xFFFF0004 = 8
0xFFFF0008 = 7
(the address goes up by four bytes because it's a 32-bit integer)
So when you access your array, index 0 means zero offset. Index 1 means 1 32-bit integer offset, thus 4 bytes. And so on.
I believe languages that use 1-indexed arrays are still doing this, they just shift the offset because some people feel it's more natural to think of 1 as the 1st item, not zero.
Note, this is the same with images... they all start at zero, not one. Something to be aware of :)
Hope that helps!
*edit: Sorry, the above examples are with an array of 32-bit integers. Generally the offset is calculated as: index * size_of_single_element. Which isn't terribly important... but that's why arrays are zero-indexed :D