Video link: https://www.bilibili.com/video/BV1iF411z7Pu

1.3. 1. Basic operation of index

1.3. 1.1. Index overview

What is the index

Index is to build a data model through some algorithm to quickly find the row with a specific value in a column. Without index, MySQL must read the whole table from the first record until the relevant row is found. The larger the table, the more time it takes to query the data. If the column queried in the table has an index, MySQL can quickly reach a location to search for data files without having to view all the data, which will save a lot of time.

The index is very similar to the query by pinyin and radical in the dictionary. It is a fast way for us to quickly find Chinese characters.

Classification of indexes

According to the implementation, indexes can be divided into Hash indexes and B+tree indexes.

According to the function, the index can be divided into

Let's talk about the basic operation of index classification according to function.

1.3. 1.2. single column

What is a single column index

Simply put, it is to index a column. A table can have multiple single column indexes.

General index

There are no restrictions on the basic index types in MySQL. Duplicate values and null values are allowed to be inserted into the columns defining the index, just to query the data faster.

• Creation method

  There are three ways to create a common index:

1. Create a table directly when creating a table

   create database mydb5;
   use mydb5;
   
   -- Mode 1-Specify directly when creating a table
   create  table student(
       sid int primary key,
       card_id varchar(20),
       name varchar(20),
       gender varchar(20),
       age int,
       birth date, 
       phone_num varchar(20),
       score double,
       index index_name(name) -- to name Create index for column
   );
   
   

2. Create directly

   -- create index indexname on tablename(columnname); 
   create index index_gender on student(gender);          
   

3. Create when modifying the table structure

   -- alter table tablename add index indexname(columnname)
   alter table student add index index_age(age);
   

• View actions

1. View all indexes

   1,View all indexes of the database 
   -- select * from mysql.`innodb_index_stats` a where a.`database_name` = 'Database name'; 
   select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5';
   

2. View all indexes in the table

   -- select * from mysql.`innodb_index_stats` a where a.`database_name` = 'Database name' and a.table_name like '%Table name%'; 
   select * from mysql.`innodb_index_stats` a where a.`database_name` = 'mydb5' and a.table_name like '%student%';
   

3. View all indexes in the table

   -- show index from table_name; 
   show index from student;
   

• Delete index

  drop index Index name on Table name 
  -- or 
  alter table Table name drop index Index name
   example:
  drop index index_gender on student 
  -- or 
  alter table student drop index index_name
  

unique index

The unique index is similar to the previous ordinary index, except that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique.

• Creation method

  -- Mode 1-Specify directly when creating a table
  create  table student2(
      sid int primary key,
      card_id varchar(20),
      name varchar(20),
      gender varchar(20),
      age int,
      birth date, 
      phone_num varchar(20),
      score double,
      unique index_card_id(card_id) -- to card_id Create index for column
  );
  -- Mode 2-Create directly
  -- create unique index Index name on Table name(Listing) 
  create unique index index_card_id on student2(card_id);
  
  -- Mode 3-Modify table structure(Add index)
  -- alter table Table name add unique [Index name] (Listing)
  alter table student2 add unique index_phone_num(phone_num)
  

• Delete index

  drop index index_card_id on student2 
  -- or 
  alter table student2 drop index index_phone_num
  

primary key

Each table usually has its own primary key. When we create a table, MySQL will automatically create an index on the primary key column, which is the primary key index. The primary key is unique and cannot be NULL, so it is a special unique index.

The primary key index needs a view statement to display

1.3. 1.3. Composite index

Composite index is also called composite index, which means that we use multiple fields when creating an index, such as ID card and mobile phone number at the same time. Similarly, it can be established as a common index or a unique index.

The leftmost principle of composite index. It means that when querying, it will first match the leftmost column when creating the index, so as to judge whether to use the composite index

• establish

  -- Basic syntax for creating indexes 
  create index indexname on table_name(column1(length),column2(length)); 
  
  
  -- Composite index
  use mydb5;
  -- Basic syntax for creating indexes-- General index
  -- create index indexname on table_name(column1(length),column2(length)); 
  create index index_phone_name on student(phone_num,name);
  -- operation-Delete index
   drop index index_phone_name on student; 
  -- Basic syntax for creating indexes-- unique index
  create  unique index index_phone_name on student(phone_num,name);
  

Judge whether the following statements will use composite index according to the leftmost principle.

Conditions:
create index index_phone_name on student(phone_num,name);

Title:
select * from student where name = 'Zhang San';    Not used
select * from student where phone_num = '15100046637';   Can use

select * from student where phone_num = '15100046637' and name = 'Zhang San';  Can use
select * from student where name = 'Zhang San' and phone_num = '15100046637';   Will use (because sql Optimization, yes and Connect even and The preceding column is not the leftmost column of the index (composite index can also be used)

1.3. 1.4. Full text index

• The keyword for the full-text index is fulltext
• Full text index is mainly used to find the keywords in the text, rather than directly compare with the values in the index. It is more like a search engine, query based on similarity, rather than parameter matching of simple where statement.
• Fuzzy matching can be achieved with like +%. Why do you need full-text indexing? Like +% is appropriate when there are few texts, but it is unimaginable for a large number of text data retrieval. Full text indexing can be N times faster than like +% in front of a large amount of data, and the speed is not an order of magnitude, but there may be accuracy problems in full-text indexing.

Support of each storage engine

Use rules

The full-text index in MySQL has two variables, the minimum search length and the maximum search length. Words with a length less than the minimum search length and greater than the maximum search length will not be indexed. In other words, if you want to use full-text index search for a word, the length of the word must be within the range of the above two variables. The default values for these two can be viewed using the following command:

show variables like '%ft%';

​

You can see that innoDB requires a minimum length of 3 for full-text index queries. We have to enter at least three letters when querying.

• establish

  -- Add full-text indexes when creating tables
  create table t_article (
       id int primary key auto_increment ,
       title varchar(255) ,
       content varchar(1000) ,
       writing_date date -- , 
       -- fulltext (content) -- Create full text search
  );
  -- Data preparation
  insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
  insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02'); 
  insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
  insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
  insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
  insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
  insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');
  
  -- Another way to create
  
  -- Modify table structure and add full-text index
  alter table t_article add fulltext index_content(content)
   
  -- Add full text index directly
  create fulltext index index_content on t_article(content)
  
  -- use
  select * from t_article where match(content) against('you'); -- There are results
  

1.3. 1.5. Spatial index

• establish

  create table shop_info (
    id  int  primary key auto_increment comment 'id',
    shop_name varchar(64) not null comment 'Store name',
    geom_point geometry not null comment 'Longitude and latitude',
    spatial key geom_index(geom_point)
  );