In the database table, the index can greatly improve the query speed. If we have created a TestIndex table: Create Table TestIndex (i_testid int not null, vc_name varchar (16) not null); we randomly insert 1000 records in the inside, one of which i_testid vc_name 555 ERQUAN is looking for vc_name = "Erquan" Record Select * from testIndex where vc_name = 'erquan';, if an index has been established on VC_NAME, mysql does not have to scan, that is, you can find the record! Instead, MySQL scans all records, that is, querying 1000 times ~~ can index to increase the query speed by 100 times. I. Index SLR Column Index and Combined Index Single Leit Index: The index contains only a single column, and a table can have multiple single column indexes, but this is not a combined index. Combined index: that is, a sum contains multiple columns. Second, introduce the type of index 1. Normal index. This is the most basic index, it doesn't have any restrictions. It has the following creating methods: (1) Creating an index: CREATE INDEX INDEXNAME ON TABLENAME (TableColumns (length)); if it is a char, varchar type, Length can be less than the field actual length; if it is a BLOB and TEXT type, you must specify Length , The same. (2) Modifying the table structure: Alter Tablename add index [indexname] on (TableColumns (length)) (3) That is specified in the creation table: Create Table Tablename ([...], Index [IndexName] (TableColumns (Length) ); Unique index. It is similar to the previous "normal index", different is: The value of the index column must be unique, but allows for an empty value. If it is a combined index, the combination of column values must be unique. It has the following Several creation methods: (1) Creating an index: CREATE UNIQUE INDEX INDEXNAME ON TABLENAME (TableColumn (length)) (2) Modify Table Structure: ALTER TABLENAME ADD UNIQUE [INDEXNAME] ON (TableColumns (Length)) (3) Created table Time to specify directly: CREATE TABLE TABLENAME ([...], UNIQUE [IndexName]; 3. Primary key index is a special unique index, not allowed to have an empty value. Generally in the table At the time, the primary key index is created: create Table TestIndex (i_testid int not null auto_increment, vc_name varchar (16) not null, primary key (i_testid)); of course, you can also use the alter command. Remember: A table can only have a primary key. 4 Full-literary index MySQL starts support full-text index and full-text search from version 3.23.23.
Not discussing here, huh, huh ~~ Delete the syntax of the index: Drop Index index_name on tablename 3, single column index and combination index for the image, built a table: Create Table MyIndex (i_testid int not null auto_increment, vc_name varchar (50) NOT NULL, VC_CITY VARCHAR (50) Not null, i_age int not null, i_schoolid int not null, primary key (i_testid)); 5 VC_NAME = "ERQuan" records in this 10000 records, 5 However, the combination of CITY, AGE, and SCHOOL is different. Come and see this T-SQL: SELECT I_TESTID from MyIndex WHERE VC_NAME = 'Erquan' and vc_city = 'Zhengzhou' and i_age = 25; first consider building a single column index: indexing on the VC_NAME column. When performing T-SQL, MySQL quickly locked the target on the 5 records of VC_Name = Erquan, and took it out to the intermediate result set. In this result set, first exclude VC_CITY to not equal the record of "Zhengzhou", and then exclude I_AGE does not equal 25 records, and finally screen the unique eligible record. Although indexing is established on VC_NAME, Mysql doesn't have to scan the finishing table when querying, and the efficiency has improved, but there is a certain distance from our requirements. Similarly, the efficiency of the single column index established separately in VC_CITY and I_AGE. In order to further extract MySQL efficiency, consider establish a combined index. Just build VC_NAME, VC_CITY, I_AGE to an index: ALTER TABLE MyIndex add index name_city_age (vc_name (10), vc_city, i_age); - pay attention, when the table, the VC_NAME length is 50, why use 10? Because the length of the name does not exceed 10 in general, this will accelerate the index query speed, and the size of the index file will be reduced, and the update speed of INSERT will be reduced. When performing T-SQL, mysql does not have to scan any records to find the only record! ! It is sure that someone should ask, if the single column index is established on VC_Name, VC_CITY, I_AGE, so that the table has three single-column indexes, when the query is the same as the combined index efficiency? Oh, big, far below our combination index ~~ Although there have been three indexes, MySQL can only use it. It thinks it seems to be the most efficient single column index. Establish such a combination index, is actually equivalent to the three combination indexes such as VC_NAME, VC_CITY, I_AGE VC_NAME, VC_CITY VC_NAME! Why don't you have a combination index such as VC_CITY, I_AGE? This is because the result of the MySQL combination index "the most left prefix". A simple understanding is only from the leftmost beginning.
