Joint primary key (joint index) Index Analysis in SQL Server (MySQL)

Joint primary key (joint index) Index Analysis in SQL Server (MySQL)

Some people have recently asked this question, but they have not studied the specific use logic of the joint index before. They have read many articles and obtained some conclusions after tests.

Test environment: SQL Server 2008 R2

The test result is similar to that of the MySql joint index query mechanism. It can be considered that MySQL is the same principle.

========================================================== ================

The concept of joint index: when several fields in the system are frequently to be queried, and the data volume is large, reaching the million level, multiple fields can be indexed.

Rules:

1. The leftmost principle. Based on the index field, and from left to right (where field is very important, from left to right)

2. Or does not use the Union Index

3. the query field in the where statement contains all index fields. The field order is irrelevant and can be sorted randomly.

4. If the data volume is small, indexes are generally not used. The database itself determines whether to use indexes automatically.

========================================================== ==================

Test script (some scripts from other authors ):

/* Create a test data table */create table MyTestTable (id varchar (10) not null, parent varchar (40) not null, addtime datetime default (getdate ()), intcolumn int default (10), bitcolumn bit default (1 )) go/* add tens of thousands of random string test data in minutes */declare @ count int = 3557643 declare @ I int = 0 declare @ id varchar (10), @ parent varchar (40) while (@ I <@ count) beginselect @ id = left (newid (), 10) if (@ I % 20 = 0) beginselect @ parent = left (newid (), 40) endinsert MyTestTable (id, parent) values (@ id, @ parent) select @ I = @ I + 1 endgo

/× Query test without index creation ×/
Declare @ beginTime datetime = getdate () declare @ elapsedSecond int = 0 select * from MyTestTable where parent = 'f92d6a9d-hangzhou' and id = 'fd1_7f4-1' select @ elapsedSecond = DATEDIFF (MICROSECOND, @ beginTime, GETDATE () print 'query data consumption in microseconds when no index is created 'print @ elapsedSecondselect @ beginTime = GETDATE () select * from MyTestTable where parent = 'f535c18f-BD48-4D45-88DF-9653BB9B422D 'select @ elapsedSecond = DATEDIFF (MICROSECOND, @ beginTime, GETDATE ()) print 'data consumption in the second column when no index is created 'print @ elapsedSecond

/* Create Index */alter table MyTestTable add constraint PK_id_parent primary key (id asc, parent asc)/* query after index creation */declare @ beginTime datetime = getdate () declare @ elapsedSecond int = 0 select * from MyTestTable where parent = 'f92d6a9d-hangzhou' and id = 'fd1_7f4-1' select @ elapsedSecond = DATEDIFF (MICROSECOND, @ beginTime, GETDATE () print 'query data consumption in microseconds during index creation 'print @ elapsedSecond select @ beginTime = GETDATE () select * from MyTestTable where parent = 'f92d6a9d-4E9E-4980-8B46-8AD938CEDCB4 'select @ elapsedSecond = DATEDIFF (MICROSECOND, @ beginTime, GETDATE ()) print 'data consumption in the second column after indexing 'print @ elapsedSecond

/* Index use test conclusion */select * from MyTestTable where id = 'fd1_7f4-1' -- use the index select * from MyTestTable where id = 'fd1_7f4-1' and parent = 'f92d6a9d -Comment 'and intcolumn> 0 -- use the index select * from MyTestTable where id = 'fd1_7f4-1' and intcolumn> 0 and parent = 'f92d6a9d-Comment' -- use the index select * from myTestTable where id = 'fd1_7f4-1 'and intcolumn> 0 -- use the index select * from MyTestTable where parent = 'f92d6a9d-hangzhou' and id = 'fd1_7f4-1' -- use the index select * from MyTestTable where parent = 'f92d6a9d-hangzhou' and intcolumn> 0 -- select * from MyTestTable where parent = 'f92d6a9d-hangzhou' or id = 'fd1_7f4-1' -- no index

If you have any questions, please leave a message!