What is the use of data index?

The purpose of database indexing is to query data efficiently.

What are the database indexes?

  1. Clustered index (primary key index) : In the database, all rows are sorted by the primary key index.

  2. Non-clustered index: index a common field.

  3. Joint index: an index consisting of several fields, called a joint index.

    Federated indexes follow the left-most prefix principle

3. Index related operations

  1. Create indexes when creating tables
CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);
Copy the code
  1. Add indexes after the table is created
CREATE INDEX index_name ON table_name (column_list)
Copy the code
  1. View the index in the table
SHOW INDEXES FROM employees;
Copy the code

Four, which columns do not go to the index?

Select * from student where age,name

key 'idx_age' ('age'),
key 'idx_name' ('name')
Copy the code
  1. % is not indexed before % and is indexed after %
A: select * from student where 'name' like 'the king %'
B: select * from student where 'name' like Small '%'
Copy the code
A goes to the index, B doesn't go to the indexCopy the code
  1. Use index column to calculate, do not follow the index
A:select * from student where age = 10+8
B:select * from student where age + 8 = 18
Copy the code
A goes to the index, B doesn't go to the indexCopy the code
  1. Use function on index column, do not walk index
A:select * from student where  concat('name'.'ah') ='Wang ha ha';
B:select * from student where name = concat('king ha'.'ah');
Copy the code
A doesn't go to the index, B goes to the indexCopy the code

4. Index column used! = no index, as follows:

select * from student where age ! = 18
Copy the code

For these cases, index merge is used, as explained in a later section

What is index merge?

According to the instructions in the official documents, we can learn:

  1. Index merging is the merging of the range scans of several indexes into one index.
  2. When indexes are merged, they are combined, interset, or interset and then combined to form a single index.
  3. The indexes to be merged must belong to only one table. Index merging cannot be performed on multiple tables.

How do I determine if index merge is used?

When operating on SQL statements using Explain, if index merge is used, index_Merge is displayed in the type column of the output and all indexes used are shown in the key column

mysql> explain select * from test where (key1_part1=4 and key1_part2=4) or key2_part1=4\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: index_merge
possible_keys: key1,key2
          key: key1,key2
      key_len: 8.4
          ref: NULL
         rows: 3
        Extra: Using sort_union(key1,key2); Using where
1 row in set (0.00 sec)
Copy the code

Why index B+ tree?

Mysql uses root resident memory to store 16KB per page, and page directories also store 16KB. Page directories do not store data, only leaf nodes store data.