Mysql DBA Advanced Operations Learning notes-indexing knowledge and multiple ways to create indexes combat

This is my study note for my old boy teacher's MySQL DBA Advanced OPS course, the old boy teacher spoke very well and thanked the old boy teacher very much. Just contact operations have a lot of knowledge, if I publish the article has incorrect place, please operation and maintenance industry elite, teacher timely guidance, hehe ~ I will also update their own learning Ysql DBA advanced operation and Maintenance Course study notes.

9.9.7 to create an index for a table field

Indexes are like catalogs of books, and if indexed on a field, you can speed up querying data when indexed.

9.9.7.1 creating a primary key index

Querying the contents of the database, the primary key query is the fastest, each table can have only one primary key, but there are multiple ordinary index columns, the primary key column requires that all content must be unique, and the indexed column does not require the content to be unique.
Whether we establish a primary key index or a normal index, we create it on the corresponding column of the table, and you can create an index on a single column or on multiple columns.
To establish a primary key cable method:

1. When creating a table, you can increase the creation of a primary key index statement

[email protected] 04:0932->create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> age tinyint(2) NOT NULL default ‘0‘,-> dept varchar(16) default NULL,-> primary key(id),-> KEY index_name(name)-> );

Tips:

A.auto_increment Self-increment

B.primary key (ID) primary key

C.key index_name (name) Name field Normal Index

Operation Demo

[email protected] 04:2146->drop table student;Query OK, 0 rows affected (0.00 sec)[email protected] 04:2333->create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> age tinyint(2) NOT NULL default ‘0‘,-> dept varchar(16) default NULL,-> primary key(id),-> KEY index_name(name)-> );Query OK, 0 rows affected (0.00 sec)[email protected] 04:2609->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(4)  | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | MUL | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

Hint: PRI is the identity of the primary key, Mul is the identity of the normal index.

2. Increase the primary key index by the ALTER command after establishing the table (this is not recommended)

A. Primary key columns cannot be created repeatedly, you must first delete the above configuration

[email protected] 04:3716->alter table student drop primary key;

B. Forget to add a primary key when building a table use the ALTER command to increment the ID column for the self-increment primary key columns

[email protected] 04:5948->alter table student change id id int primary key auto_increment;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected] 05:0324->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | MUL | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

Tip: You can use auto_increment only if the int type is primary key.

9.9.7.2 Creating a normal index

1. When building a table, you can add the following statement to create a normal index column:

[email protected] 04:0932->create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> age tinyint(2) NOT NULL default ‘0‘,-> dept varchar(16) default NULL,-> primary key(id),-> KEY index_name(name)-> );

Tips:

A.key index_name (name) Name field Normal Index

Operation Demo:

[email protected] 04:2146->drop table student;Query OK, 0 rows affected (0.00 sec)[email protected] 04:2333->create table student(-> id int(4) not null AUTO_INCREMENT,-> name char(20) not null,-> age tinyint(2) NOT NULL default ‘0‘,-> dept varchar(16) default NULL,-> primary key(id),-> KEY index_name(name)-> );Query OK, 0 rows affected (0.00 sec)[email protected] 04:2609->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(4)  | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | MUL | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

2. Use alter to increase the normal index after the table is built

Index_name index created when a table is deleted

[email protected] 05:0333->alter table student drop index index_name;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected] 06:0419->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

Add an index on the Name column with the index named Index_name

[email protected] 06:0431->alter table student add index index_name(name);Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected] 06:0456->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | MUL | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

9.9.7.3 creating a normal index on the first n characters of a field

When you encounter a larger column in a table, the first n characters of the column content are nearly unique across all content, so you can index the first n characters of the column without indexing the entire column, saving the system space that is used to create the index, and reducing and updating the system resources that are consumed by the maintenance.

syntax for creating indexes on the first n characters of a field:

Create index index_name on test (name (8)); The first n characters of the condition column create an index

Actual operation:

[email protected] 06:0838->create index index_dept on Student (Dept (8)); Query OK, 0 rows affected (0.19 sec) records:0 duplicates:0 warnings:0[email protected] 06:3650->DESC student; +-------+-------------+------+-----+---------+----------------+| Field | type| Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| id| Int (11) | NO | PRI | null| auto_increment | | name | char (20) | NO | MUL | null| | | Age | tinyint (2) | NO | | 0 | | | Dept | varchar (16) | YES | MUL | null| | +-------+-------------+------+-----+---------+----------------+4 rows in Set (0.00 sec) [email protected] 06:3847->show index from student\g*************************** 1. Row *************************** table:student non_unique:0key_name:primaryseq_in_index:1 column_name:id Collatio N:a cardinality:0sub_part:null Packed:NULLNull:Index_type:BTREE Comment: *************************** 2. Row *************************** Table:Student Non_unique:1key_name:index_nameseq_in_index:1 Column_name:name collation:a Cardinality:NULLSub_part:NULL Packed:NULLNull:Index_type:BTREE Comment: *************************** 3. Row *************************** table:student non_unique:1key_name:index_deptseq_in_index:1 column_name:dept Col Lation:a cardinality:nullsub_part:8 Packed:NULLNull:YES index_type:btree comment:3 rows in Set (0.00 sec)

9.9.7.4 Creating a federated index for multiple fields of a table

If the criteria for a query are multiple columns, we can create a federated index for the columns of multiple queries, and even create a federated index for the first n characters of multiple columns, as shown in the actual combat demo:

To create an index for multiple columns

[email protected] 06:4019->create index ind_name_dept on student (name,dept); Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0[email protected] 06:4726->show index FR Om student\g*************************** 1. Row *************************** table:student non_unique:0key_name:primaryseq_in_index:1 column_name:id Collatio N:a cardinality:0sub_part:null Packed:NULLNull:Index_type:BTREE Comment: *************************** 2. Row *************************** table:student non_unique:1key_name:index_nameseq_in_index:1 column_name:name Col Lation:a Cardinality:NULLSub_part:NULL Packed:NULLNull:Index_type:BTREE Comment: *************************** 3. Row *************************** table:student non_unique:1key_name:index_deptseq_in_index:1 column_name:dept Col Lation:a cardinality:nullsub_part:8 Packed:NULLNull:YES index_type:btree Comment: *************************** 4. Row *************************** table:student non_unique:1key_name:ind_name_deptseq_in_index:1 column_name:name collation:a cardinality:null Sub_part:null Packed:NULLNull:Index_type:BTREE Comment: *************************** 5.   Row *************************** table:student non_unique:1key_name:ind_name_deptseq_in_index:2 column_name:dept  Collation:a Cardinality:NULLSub_part:NULL Packed:NULLNull:YES index_type:btree comment:5 rows in Set (0.00 sec)

You can create a federated index on the first n characters of multiple columns, and the actual combat demo is as follows:

[email protected] 06:5144->drop index ind_name_dept on student; 删除索引的另一种方法。Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected] 06:5158->create index ind_name_dept on student(name(8),dept(10));Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0

Tips:

When querying data by Condition column, the Federated index is a prefix-effective attribute

Index (A,B,C) only a,ab,abc three query criteria can be indexed. B,bc,ac,c cannot walk the index

9.9.7.5 Creating a unique index (non-primary key index)

Create Unique index index_age on student (age);

A unique index is used to constrain the contents of a table and cannot be duplicated.

[email protected] 07:0828->create unique index uni_ind_name on student(name);Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0[email protected] 07:1010->desc student;+-------+-------------+------+-----+---------+----------------+| Field | Type| Null | Key | Default | Extra  |+-------+-------------+------+-----+---------+----------------+| id| int(11) | NO   | PRI | NULL| auto_increment || name  | char(20)| NO   | UNI | NULL||| age   | tinyint(2)  | NO   | | 0   ||| dept  | varchar(16) | YES  | MUL | NULL||+-------+-------------+------+-----+---------+----------------+4 rows in set (0.00 sec)

9.9.7.6 Index Table creation and effective conditions

Question 1: Since the index can speed up the query, is it necessary to index all the columns?

Answer: Because the index you not only occupy the system space, update the database also need to maintain the index data. So the index is a double-edged sword, not the more the better, for example: Dozens of to hundreds of rows of small tables do not need to create an index, update frequently, read less business to build less index.

2: What columns do I need to create an index on?

Answer: Select User,host from Mysql.user where host= .... The index must be created on the condition column after the where, not on the Select data column after select, and we try to make an index on a large table with a unique value. Create an index to discuss with the development.

9.9.7.7 Creating an index command collection summary

1. Create index-related command collection

Create a primary key index

alter table student change id id int primary key auto_increment;

Delete primary key index

alter table student drop primary key

Create a normal index

alter table student add index index_dept(dept(8))

Create an index based on the first n characters of a column

create index index_dept on student(dept(8))

To create a federated index from multiple columns

create index ind_name_dept on student(name,dept);

Create an index based on the first n characters of multiple columns

create index ind_name_dept on student(name(8),dept(10));

Delete Normal index

Alter table student drop index index_deptdrop index ind_name_dept on student

Create a unique index

create unique index uni_ind_name on student(name);

Base index:

A. To create an index on a column of a table.

B. The index speeds up the query, but it affects the speed of the update because the index is maintained.

C. The more index is not the better, to create an index on the condition column after the where of the frequently queried.

D. Do not index on columns with small or unique values, to create indexes on large tables and on columns with many different contents.

Mysql DBA Advanced Operations Learning notes-indexing knowledge and multiple ways to create indexes combat