Index and view details of Mysql database
The concept of index
The index of a database is similar to that of a Book
In a book, you can quickly find the required information by using the catalog without reading the whole book
The table of contents in the book is a list of words with page numbers containing each word
Database index
In the database, the index database program can find the required data in the whole table without scanning it
An index in a database is a collection of one or more columns in a table and a list of logical pointers to the data pages that physically identify these values
The function of index
After setting the appropriate index, the database can greatly speed up the query speed by using GE Zong's fast positioning technology
Especially when the table is large, or the query involves multiple tables, the use of index can speed up the query thousands of times
It can reduce the IO cost of the database, and the index can also reduce the sorting cost of the database
Guarantee the uniqueness of data table data by creating unique index
Can speed up the connection between tables
When grouping and sorting are used, the grouping and sorting time can be greatly reduced
Index classification
General index
This is the most basic index type, and it has no uniqueness restrictions
Uniqueness index
All values of indexed columns can only appear once, that is, they must be unique
Primary key
PRIMARY KEY is a unique index, but it must be specified as "PRIMARY KEY"
Full-text index
Full TEXT indexes can be created on columns of type VARCHAR or TEXT
Principle basis for index creation
Primary key of table, foreign key must have index
Tables with more than 300 rows of data should have indexes
Tables that are frequently joined to other tables should be indexed on join fields
Fields with poor uniqueness are not suitable for indexing
Fields updated too frequently are not suitable for index creation
Fields that often appear in Where sentences, especially in large tables, should be indexed
Indexes should be built on fields with high selectivity
Indexes should be built on small fields. For large text fields or even extra long fields, do not build indexes
Index schematic
General index
General index structure statement
create index index name on tablename
create index Index name on tablename(List of columns)
[root@localhost ~]# mysql -u root -p #Enter mysql database
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20 Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #View all databases
mysql> use school; #Create a school database
Database changed
mysql> create table info ( #Create data table
id int(4) not null primary key auto_increment, #int type integer is 4, cannot be empty, primary key index, value grows naturally
name varchar(10) not null, #varchar string cannot be empty
address varchar(50) default 'nanjing', #String default is nanjing
age int(3) not null); #int type
Query OK, 0 rows affected (0.05 sec)
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * form info; #View data table
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'form info' at line 1
mysql> select * from info;
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> desc info; #View table structure
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| address | varchar(50) | YES | | nanjing | |
| age | int(3) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> create index index_age on info (age); #Create index fixed collocation, and index "age index acts on age column in info table
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; #View indexes in Datasheet
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 1 | index_age | 1 | age | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index index_age on info; #Delete the index of index? Age in the data table
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)unique index
Unique index structure statement
Name of the create unique index index on tablename (list of columns)
mysql> create unique index unique_name on info (name); #To create a unique index, create unique index is used as a name in the name column of info Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | unique_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) mysql> alter table info add unique index index_name (name); #Another method: alter table info add unique index index name, which is used in the name column name Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from info; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec)
Three ways to define indexes
1. Define directly when creating a table
2.create index index name on table name (column name 1, column name 2); column names can be multiple
3.alter table table name add index index name (column name);
mysql> alter table info add unique index index_name (name); #Another method: alter table info add unique index index name, which is used in the name column name
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> create table user (
-> id int(4) not null primary key auto_increment,
-> name varchar(10) not null,
-> score decimal not null,
-> hobby int(2) not null default '1',
-> index index_score (score)); #When creating a table, you can directly define the index
Query OK, 0 rows affected (0.05 sec)
mysql> desc user;
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(10,0) | NO | MUL | NULL | |
| hobby | int(2) | NO | | 1 | |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)Make two tables for index query and view them together
#Fill in the data corresponding to the column name
mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user;
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 1 | test01 | 88 | 1 |
| 2 | stu01 | 99 | 2 |
| 3 | wangwu | 77 | 3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)Create another table to join the previous table
mysql> create table hob (
-> id int (2) not null primary key,
-> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> desc hob;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(2) | NO | PRI | NULL | |
| hob_name | varchar(10) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into hob (id,hob_name) values (1,'Read a Book'),(2,'motion'),(3,'Run');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from hob;
+----+----------+
| id | hob_name |
+----+----------+
| 1 | Read a Book |
| 2 | motion |
| 3 | Run |
+----+----------+
3 rows in set (0.00 sec)
mysql> insert into user (name,score,hobby) values ('zhaoliu',66,2); #Insert another row of data in the user table
Query OK, 1 row affected (0.00 sec)
mysql> select * from user inner join hob on user.hobby=hob.id; #Add the user table to the hob table. The hobby of the user corresponds to the id in the hob
+----+---------+-------+-------+----+----------+
| id | name | score | hobby | id | hob_name |
+----+---------+-------+-------+----+----------+
| 1 | test01 | 88 | 1 | 1 | Read a Book |
| 2 | stu01 | 99 | 2 | 2 | motion |
| 3 | wangwu | 77 | 3 | 3 | swim |
| 4 | zhaoliu | 66 | 2 | 2 | motion |
+----+---------+-------+-------+----+----------+
4 rows in set (0.00 sec)I don't think it's reasonable to look at the data in this table. Now I only need names and hobbies
mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id; +---------+----------+ | name | hob_name | +---------+----------+ | test01 | Read a Book | | stu01 | motion | | wangwu | Run | | zhaoliu | motion | +---------+----------+ 4 rows in set (0.00 sec)
Table name alias associated query
mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; +---------+----------+ | name | hob_name | +---------+----------+ | test01 | Read a Book | | stu01 | motion | | wangwu | Run | | zhaoliu | motion | +---------+----------+ 4 rows in set (0.00 sec)
Creative view
Create view structure statement
create view view name as
The view creates a map to present the results. The real data is still in the original table
mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; Query OK, 0 rows affected (0.00 sec) mysql> select * from view_user; +---------+----------+ | name | hob_name | +---------+----------+ | test01 | Read a Book | | stu01 | motion | | wangwu | Run | | zhaoliu | motion | +---------+----------+ 4 rows in set (0.00 sec)
Full-text index
Index long fields and paragraphs
mysql> create fulltext index full_addr on info (address); Query OK, 0 rows affected, 1 warning (0.21 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show index from info; +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | | | info | 1 | full_addr | 1 | address | NULL | 2 | NULL | NULL | YES | FULLTEXT | | | +-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 3 rows in set (0.00 sec)
Composite index
mysql> create index index_name_score on user (name,score); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from user; +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_score | 1 | score | A | 3 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 1 | name | A | 4 | NULL | NULL | | BTREE | | | | user | 1 | index_name_score | 2 | score | A | 4 | NULL | NULL | | BTREE | | | +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)