Shulou(Shulou.com)06/03 Report--

The following brings you about the index and view of the Mysql database, if you are interested, let's take a look at this article. I believe it is more or less helpful for you to read the index and view of the Mysql database.

The concept of index

The index of the database is similar to the catalogue in the book.

In a book, you can quickly find the information you need without reading the whole book.

The catalogue in the book is a list of words with the page number containing each word.

Database index

In a database, the index database program can find the data it needs without scanning the entire table.

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 uses GE's fast positioning technology, which can greatly speed up the query rate.

Especially when the table is very large, or when the query involves multiple tables, using indexes can make the query thousands of times faster.

It can reduce the IO cost of database, and the index can also reduce the sorting cost of database.

Ensure the uniqueness of data in the data table by creating a unique index

You can speed up the connection between tables

When using grouping and sorting, grouping and sorting time can be greatly reduced

Index classification

General index

This is the most basic index type, and it has no restrictions on uniqueness.

Uniqueness index

All values of the indexed column can only appear once, that is, they must be unique

Primary key

The primary key is a unique index, but it must be specified as "PRIMARY KEY"

Full-text index

A full-text index can be created on a column of type VARCHAR or TEXT

The principle of creating an index

The primary key of the table, the foreign key must have an index

Tables with more than 300 rows of data should have indexes

Tables that are often connected to other tables should be indexed on the join field

Fields with poor uniqueness are not suitable for indexing.

Fields that are updated too frequently are not suitable for index creation

Fields that often appear in Where sentences, especially those in large tables, should be indexed.

The index should be built on fields with high selectivity

The index should be built on small fields. For large text fields or even super-long fields, do not build indexes.

Index schematic diagram

General index structure statement

Create index index name on tablename (list of columns

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 5Server version: 5.7.20 Source distributionCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or'\ h' for help. Type'\ c' to clear the current input statement.mysql > show databases; # View all databases mysql > use school # create school database Database changedmysql > 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, name varchar (10) not null, # varchar string cannot be empty address varchar (50) default 'nanjing', # string is nanjingage int (3) not null by default) # int type Query OK, 0 rows affected (0.05sec) 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: 0mysql > 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 1mysql > 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 an index with a fixed collocation. The index_age index acts on the age column Query OK in the info table, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show index from info # View the index in the data table +-+ -+ | 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 Query OK of index_age in the data table, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > 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 structure statement

The name of the create unique index index on tablename (list of columns)

Mysql > create unique index unique_name on info (name); # create a unique index, and create unique index always matches a name to act as Query OK in the name column of info. 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > 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 name, which works in the name column name Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > 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 an index

1. Define directly when you create 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 name, Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > show index from info in the name column name + -+ | 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 '1percent,-> index index_score (score)) # you can define the index Query OK directly when creating a table, 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 (10L0) | NO | MUL | NULL | | hobby | int (2) | NO | | 1 | | +-+-+ 4 rows in set (0.00 sec) do two tables for indexing query | The two tables are viewed together # fill in the data mysql > insert into user (name,score,hobby) values ('test01',88,1), (' stu01',99,2), ('wangwu',77,3) corresponding to the column name Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > 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 a table to make a query connected to 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 (1Jing 'reading'), (2Jing 'sports'), (3Jing 'running') Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > select * from hob;+----+-+ | id | hob_name | +-+-+ | 1 | Reading | | 2 | Sports | | 3 | running | +-+-+ 3 rows in set (0.00 sec) mysql > insert into user (name,score,hobby) values ('zhaoliu',66,2) # insert another row of data Query OK in the user table, 1 row affected (0.00 sec) mysql > select * from user inner join hob on user.hobby=hob.id # add a table like user to the hob table The hobby of user corresponds to the id+----+----+-+ in hob | id | name | score | hobby | id | hob_name | +-+-- +-+ | 1 | | test01 | 88 | 1 | 1 | Reading | | 2 | stu01 | 99 | 2 | 2 | Motion | 3 | wangwu | 77 | 3 | 3 | you | 4 | zhaoliu | 66 | 2 | Motion | +-- +-+-- + 4 rows in | Set (0.00 sec) thinks that the data of this table is unreasonable. Now all you need is name and hobby mysql > select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id +-+-+ | name | hob_name | +-+-+ | test01 | Reading | | stu01 | Sports | | wangwu | running | | zhaoliu | Motion | +-+ + 4 rows in set (0.00 sec) Table name alias association query mysql > select u.name H.hob_name from user u inner join hob h on u.hobby=h.id +-+-+ | name | hob_name | +-+-+ | test01 | Reading | | stu01 | Sports | | wangwu | running | | zhaoliu | Motion | +-+-+ 4 rows in set (0.00 sec) View creation View structure statement

Create view View name as

The view establishes a mapping to present the results, and the real data is still in the original table.

Mysql > create view view_user as select u. Name rows affected h. Hobbyist name from user u inner join hob h on u. H. Obbyopia h. Id world query OK, 0 rows affected (0. 00 sec) mysql > select * from view_user +-+-+ | name | hob_name | +-+-+ | test01 | Reading | | stu01 | Sports | | wangwu | | zhaoliu | Sports | +-+-+ 4 rows in set (0.00 sec) full-text index to the long field The paragraph is indexed by mysql > create fulltext index full_addr on info (address) Query OK, 0 rows affected, 1 warning (0.21 sec) Records: 0 Duplicates: 0 Warnings: 1mysql > 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: 0mysql > 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)

Read the details of the index and view of the Mysql database above, and whether you have gained anything. If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.