1/ Why index

Indexing is the most important way of query optimization; Index optimization is the most important method of database optimization. Query methods: One is full table scan. If the table is large, the speed of this method is slow.Copy the code

2/ Why does indexing speed up queries

Database indexes can speed up queries because indexes use specific data structures (B-trees) to organize additional storage for specific columns (in this case, columns that are indexed), which speeds up record lookup by storage engines (indexes are storage engine implementations).Copy the code

3/ What is clustering index?

Index and data together. When a query statement uses an index (usually a WHERE condition matching index), it uses the structure of a B-tree to speed up the search. The index finds the position of the row in the table by value. A clustered index does not need to go back to the database table to query data. Usually, we need to go back to the database table and look up the data again, which requires additional disk IO. This is why a full table scan can sometimes be faster than using an index, such as reading data sequentially. If there is only one field in the query condition, create an index for that field. The optimization is to use prefix indexes for text BLOb fields. There is a big difference between a single-column index and a multi-column index when a query condition has multiple fields. If you use a multi-column index, the order of the fields in the WHERE condition is very important and needs to satisfy the left-most prefix column. What is the left-most prefix? The number of fields in the query condition must be smaller than or equal to the number of fields in the multi-column index. The intermediate fields cannot contain fields in the range query (<,like, etc.). This TYPE of SQL can use the multi-column index.Copy the code

4/Mysql index is divided into:

1. Plain indexes: Column values can be null or duplicate. Create using the keyword INDEX or KEY; 2. Unique value index: column values cannot be repeated; That is, the index column value must be unique, but can be null; Create using the keyword UNIQUE; 3. Primary key index: A primary key index is automatically created by the system and is unique. Different from unique indexes; Column values cannot be null; 4. Clustered index: refers to the physical storage order of data. Non-clustered index means that the index order has nothing to do with the physical order of data. A table can have only one clustered index. Currently only InoDB and solidDB support it. 5. Full-text index: can only be created on vARCHAR or TEXT columns; Build a full-text index to be able to look up columns in a full-text index.Copy the code

5/ Three ways to create an index

<1> create index index_fieldName on tableName(fieldName); <2> Mode 2: Create table basic_df(tid int, tname vARCHar (20), gender varchar(1), index [indexName] (tid)); <3> 表 2: ALTER table tableName add unique index indexName (fieldName);Copy the code

4/ What are single-column and multi-column indexes

1) Single-column index: an index contains only one column in a table; Than create an index of student ID; Create a single column index of a second name with name. That is, each index contains one column. 2) Composite index (composite index or multi-column index) : is two or more columns in a table to create an index; For example; An index created by user ID, user Name, and user Age is a federated index.Copy the code

5/ Common commands about indexes

   drop table if exists my_test;  -- Delete my_test if it exists
   create table my_test(
        id int(11) not null unique auto_increment,  
        name varchar(10) not null default 0,  
        age int(11) not null default 0 ) engine=innodb charset=utf8;
  
   Select * from my_test
   show index from my_test;
 
   Create index;
   -- Create the plain index name index_n for column name in table my_TEXT
   create index index_name on my_test(name); 
   -- Index age in descending order
   create index index_age on my_test(age DESC); 
   Select * from age; select * from name; select * from age; select * from name
   create index index_age_name on my_test(age,name DESC);
   
   Drop index
   Alter table my_test drop index my_test
   drop index index_name on my_test;
  
   -- Modify indexes, including dropping indexes and adding new indexes
   alter table my_test drop index index_age;
   alter table my_test add index index_age(age);
Copy the code

6/ Use examples to understand singleton indexes, multi-column indexes, and the left-most prefix principle

Mysql > select uid from basic_df where lname='Liu and fname =' Zhiqun' and age=26; Because we do not want to scan the entire table, we consider using indexes. ALTER TABLE basic_df add index index_lname (lname); Index_lname is the name of the single column index. The lNAME column is indexed so that the range is limited to result set 1 with lname='Liu'. Then the result set 1 is scanned to produce result set 2 with fname='Zhiqun'. The end result. The single-column index of the LNAME column was much more efficient than performing a full scan of the database table, but we still scanned far more records than we actually needed. Although we can drop the index on the lNAME column and create an index on the fNAME or AGE column, the search efficiency is still similar regardless of which column we create the index on. ALTER TABLE basic_df add index index_lname_fname_age (lame,fname,age); Index_lname_fname_age is the name of the composite index. To improve search efficiency, we need to consider using multi-column indexes. Since the index files are stored in b-tree format, we can get the final results without scanning any records. Mysql > select * from lname; select * from age; select * from lname; select * from age; Index_lname_fname_age = (lname,fname,age); (lname,fname); (lname,fname,age) Note: When creating a multi-column index, the most frequently used column in the WHERE clause is placed at the far left, based on business requirements.Copy the code