SQL performace; Insertion, selection and updation_问答_开发者

I have an operation开发者_运维百科 is sql insertion. I have another operation is sql selection and updation. Table size is 300K-1Million. If I put index, does that mean my insertion and updation would be slowed and selection would be faster. But normally how faster select would be, how slower insertion and updation would be? Generally, with 300K-1Million records, does general performanced would be enhanced or not?

My personal experience: huge speed gain when using index, small decrease in speed for update/insert.

Can't you create a test case with & without the index?

If only life were that simple, the answer as always is; it depends.

Imagine your table as your own "little black book" of people to you like. Each page has a different persons name, address, date of birth, etc. (Book = Table, Page = Record)

Now, you only want each person in there once, based on their first name and last name (unique key, possibly primary key). But you also want them in there in Order so that they're easy to look up. You decide to order them by their last name, and for people with the same last name, order them by their first name (Clustered Indexed, the physical order in which they are stored, benefits Selecting data)

Now, with that clustered index you can very quickly look up your friends in your book. But if you add a new person, you can't just add them to the end, you have to find the right place int he book to insert them. (Insert overhead of an index)

If they inconventiently change their name (like getting married), you have to find them, change the name, and move them to somewhere else in the book. Fortunately though, the index did make it easier to find them in the first place; it's a pain to move them to a new place in the book, but not half as bad as trying to find them in a randomly ordered book. (Update overhead of the index)

Eventually, there comes a day when you realise that you're awful at remembering birthdays. So you turn to your little back book. Unfortunately, they're ordered by their names, not their dates of birth; finding out who's birthday's are coming up is a real pain! So you create a little index at the back; an ordered list of birthdays and the name of each person born on that day. (Secondary index, benefits selects based on date of birth)

If you find someone who's date of birth you got wrong, however, you have to things to change; the DoB on their page, and the position in the DoB index. Likewise, when you add a new person to the book, you have to find the right page to insert them, but also the right place in the DoB Index to add their name. (Update and Insert overheads)

Fortunately, updateing their address isn't a problem. Just find them, change the address, and your done. Until the day comes that you create an Address Index as well...

In short...
1. An index can help specific kinds of Select performance
2. It always adds an Insert cost
3. It can speed up finding a record to Update
4. But can also add an overhead to Updates on Indexed fields

The trades and balances of your indexes depend on your use of the data. Lots of Inserts and Updates? Lots of Selects? Lots of different Selects warranting multiples indexes, etc?

You can do it all with science, but in practice it feels like an art.

Indexes are REQUIRED to achieve acceptable performance on selects when the table contains as many rows as those in your scenario. The index overhead which slows inserts and updates is noticeable only on large batch inserts/updates; the effect of index overhead on isolated insert/update transactions is negligible.

It depends on Index type you are going to create.

Clustered index increases selection speed (very good for large data ranges) but takes longer to insert new rows because reordering take place each time you are inserting new data (server keeps daat ordered physically to increase a data access speed). Also updating of key columns will affect non-clustered indexes so keep it in mind when creating non-clustered index on clustered tables.

Anyway when creating any Index you should do it accurately, so I would like to collect a statistic of more often queries which are run (SELECT or INSERT or UPDATE) and then decide which index on which column to create.