“This is the 14th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Introduction to MySQL

MySQL, the most popular relational database management system, is a product of Oracle. MySQL is one of the most popular Relational Database Management systems, and one of the best RDBMS (Relational Database Management System) applications in WEB applications.

MySQL is an open source relational database management system, The most commonly used database management language, Structured Query Language (SQL), or CRUD (short for add (Create), Retrieve, Update, and Delete), is used for database management.

Simply put, it is a repository for storing data. MySQL is the lifeblood of a website, and it can be run on almost any platform (UNIX, Linux, Windows).

There are two important concepts to know:

The database

From its name, database means a collection of data. The folders on our computers can be seen as a database; Using music software, the song resources are stored in the database; Social media posts are also stored in the database.

The electronic file cabinet is a collection of large amounts of data stored in a computer for a long time, which is organized, shareable and uniformly managed.

SQL (Structured Query Language)

SQL is a standard computer language for accessing and processing databases.

Structured Query Language, a special purpose programming language, is a database query and programming language, used to access data and query, update and manage relational database systems.

The Data Definition Language (DDL) part of SQL gives us the ability to create or delete tables. Example: CREATE DATABASE – Creates a new DATABASE

Index classification

Mysql index is divided into three categories: single-column index (normal index, unique index, primary key index), composite index (union index/multi-column index), and full-text index.

The index

Simply put, an index is a pointer to data in a table.

  • Indexes are usually kept separate from the corresponding table to improve retrieval performance.
  • Index creation and deletion do not affect the data itself, but affect the speed of data retrieval.
  • Indexes can also take up physical storage space, possibly larger than the table itself, so storage space is also a consideration when creating indexes.

Single index

Normal index

There are no restrictions, allowing duplicate and null values to be inserted in the column where the index is defined. There are 2 ways to create a single-column index:

1]create index index_name on tbl_name(index_col_name)2]alter table table_name add index index_nameon(index_col_name)
Copy the code

Here, create index indicates that an index is to be created, index_name indicates the name of the index, on tbl_name indicates the table to be indexed, and index_col_name indicates the column in the table to be indexed

The only index

The values in the index column must be unique, but null values are allowed, that is, no rows with the same index value are allowed.

CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
Copy the code

CLUSTERED INDEX myCLUMn_cindex a CLUSTERED INDEX named myCLUMn_cindex was created in the mycolumn field of mytable.

The primary key index

Is a special unique index that does not allow null values. A PRIMARY KEY consists of one or more columns that uniquely identify a record in a data table. A table can have no primary key, but only one primary key at most, and the primary key value cannot contain NULL.

Primary key = primary key index = clustered index

Example: The generic ID is the primary key of the table

 PRIMARY KEY (`id`).Copy the code

Combined index (combined index/multi-column index)

A joint index is an index of multiple columns on a table, which is also a B+ tree. Naming rules: table name_field name

  • 1, the field that needs to be indexed must be in the WHERE condition
  • 2. The fields with little data do not need to be indexed
  • 3, If there is an OR relation, add index does not work
  • 4, comply with the leftmost principle (where condition must have the first column of the union index)

Such as:

CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` varchar(2) NOT NULL,
  `sex` varchar(2) NOT NULL,
  `phone` varchar(12) NOT NULL,
  `email` varchar(25) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name_age_sex` (`name`,`age`,`sex`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
Copy the code

This is the joint index:

 `idx_name_age_sex` (`name`,`age`,`sex`) 
Copy the code

Index (name, age, sex); (name, age, sex); (name, age); (name, age); (name, age); (name, age); (name, age); Sex).

Example of specific SQL, the following will use the joint index:

 select  * from test where name = 'Joe' and age = '18' and sex = 'male';
 select  * from test where name = 'Joe' and age = '18';
 select  * from test where name = 'Joe' ;
 select  * from test where name = 'Joe' and sex = 'male';
 select  * from test where and sex = 'male' and age = '18' name = 'Joe';
Copy the code

Note that the fifth SQL statement is executable, and the joint index is independent of the order of the WHERE conditions!

Then look at a few that don’t go to the union index:

 select  * from test where  and age = '18' and sex = 'male';
 select  * from test where  and age = '18' or sex = 'male';
 select  * from test where  and age = '18' ;
 select  * from test where  and sex = 'male';
Copy the code

Cover index

An overwrite index is a special kind of joint index, that is, all the data of the field you query is on the index, and there is no need to go back to the table again. Such an index is an overwrite index.

 SELECT name,age,sex from test where name = '% thirty percent';
Copy the code

Full-text Index (fulltext/ inverted document technology)

It can only be used on MyISAM engine. Full-text indexes can only be used on CHAR,VARCHAR and TEXT fields. Full-text indexes are seldom used in normal business. But full-text indexing is the key technology of search engine. Also known as the inverted document technique.

Create full-text index instance:

CREATE TABLE test (
title VARCHAR(40).FULLTEXT(title)
);
Copy the code

Three types of full-text indexes

  1. Natural language search interprets the search string as a phrase in natural language.
  2. Boolean full text search
  3. Query extended search

Here is only a brief introduction, can be a simple understanding, not in-depth explanation.

Thank you

  1. Baidu encyclopedia
  2. 2. What is MySQL?
  3. w3school
  4. Index type classification, differences, advantages and disadvantages
  5. MySQL full-text indexing
  6. Mysql > select * from ‘Mysql’;