This is the 7th day of my participation in the August More Text Challenge
Index concept
The index concept
Indexes are special files (indexes on InnoDB tables are part of the table space) that contain Pointers to all the records in the table. More generally, a database index is like a table of contents at the front of a book, which speeds up database queries.
1. Advantages of indexes:
- In order to speed up the search, reduce the query time.
- Indexes greatly reduce the amount of data that the server needs to scan.
2. Disadvantages of indexes:
1. Creating and maintaining indexes takes time, which increases with the volume of data. 2. The index needs to occupy physical space. In addition to the data table occupies data space, each index also needs to occupy a certain physical space. 3. When adding, deleting, or modifying data in a table, the index must be maintained dynamically, which reduces the data maintenance speed.
Because indexes are very memory intensive, they also need to be added carefully, and those fields need to be indexed.
Type of index:
1, common index: the most basic index, no restrictions, we often use the index. 2. Unique index: Similar to a normal index, except that the column value of a unique index must be unique, but null values are allowed.Copy the codeA primary key index is a special unique index that does not allow empty values.
3, FULLTEXT index: FULLTEXT index can only be used for MyISAM engine data table, CHAR, VARCHAR, TEXT data type column. 4. Composite index: Retrieve several columns as a single index, using the left-most matching principle.Copy the code1 Common Index
The most basic index, not unique, is to speed up the query
Create a common index: Method 1: Add an index when creating a table create table Name of a table (column definition index Name of an index (field) index Name of an index (field) Note: You can use key or index. Index Indicates the index name (field), which can be added or left unadded. Create test01 table
mysql> create table test01( id int primary key not null auto_increment, a1 char(10), a2 char(10), a3 char(10), a4 char(10), a5 char(10)); Query OK, 0 rows affected (0.01sec)Copy the codeInsert data
mysql> insert into test01(a1,a2,a3,a4,a5) values('wg01'.'wg02'.'wg03'.'wg04'.'wg05'); Query OK, 1 row affected (0.00 SEC) mysql> insert into test01(A1, A2, A3, A4,a5) values('zmedu01'.'zmedu02'.'zmedu03'.'zmedu04'.'zmedu05'); Query OK, 1 row affected (0.00 SEC) mysql> insert into test01(A1, A2, A3, A4,a5) values('xinsz01'.'xinsz02'.'xinsz03'.'xinsz04'.'xinsz05'); Query OK, 1 row affected (0.00 SEC) mysql> insert into test01(A1, A2, A3, A4,a5) values('zr01'.'zr02'.'zr03'.'zr04'.'zr05');
Query OK, 1 row affected (0.00 sec)
Copy the codemysql> select * from test01; +----+---------+---------+---------+---------+---------+ | id | a1 | a2 | a3 | a4 | a5 | +----+---------+---------+---------+---------+---------+ | 1 | wg01 | wg02 | wg03 | wg04 | wg05 | | 2 | zmedu01 | zmedu02 | zmedu03 | zmedu04 | zmedu05 | | 3 | xinsz01 | xinsz02 | xinsz03 | xinsz04 | xinsz05 | | 4 | zr01 | zr02 | zr03 | zr04 | zr05 | +----+---------+---------+---------+---------+---------+ 4 rowsin set (0.00 sec)
Copy the codeCreate an index: Method 1: After the table is created, use ALTER to add an index
mysql> alter table test01 add index idx(a1);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> desc test01;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| a1 | char(10) | YES | MUL | NULL | |
| a2 | char(10) | YES | | NULL | |
| a3 | char(10) | YES | | NULL | |
| a4 | char(10) | YES | | NULL | |
| a5 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
Copy the codeNote: If the Key is MUL, it is a generic index. The value of the column can be repeated. The column is the leading column of a non-unique index (the first column) or part of a unique index but can contain NULL. That means it’s a normal index. Let’s delete the index first
Method 2: Create the index directly at table creation time
mysql> create table demo( id int(4), name varchar(20), pwd varchar(20), index(pwd)); Mysql > create table demo1(id int(4), name varchar(20),pwd varchar(20), key(pwd));Copy the codemysql> alter table demo drop key pwd; Mysql > alter table demo add key(PWD);
2. Unique index
It is basically the same as a normal index, but with one difference: all values of the index column can only appear once, that is, must be unique, used to constrain the content, the field value can only appear once, should add a unique index. Uniqueness allows NULL < NULL >.
Select * from table where id = 1; create table where id = 1; create table where id = 1; Note: Only fields with values that cannot be repeated, such as username, phone number, or ID number.
mysql> create table `order`(id int(8) auto_increment primary key, uName varchar(20), uPwd varchar(20),unique index (uName));
Query OK, 0 rows affected (0.00 sec)
mysql> desc `order`;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| uName | varchar(20) | YES | UNI | NULL | |
| uPwd | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
Copy the codeAlter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table mysql> alter table order drop key uName; mysql> alter table order add unique(uName);
3. Primary key index
Query database, by primary key query is the fastest, each table can have only one primary key column, can have more than one common index column. A primary key column requires that everything in the column be unique, while an index column does not require that everything be unique and cannot be empty
- Create a primary key index
Method 1: Create a table create a primary key index
mysql> create table test2(id int(4) not null auto_increment, name varchar(20) default null ,primary key(id));
Query OK, 0 rows affected (0.00 sec)
mysql> desc test2;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
Copy the codemysql> show create table test2; mysql> show index from test2 \G
Method 2: After creating a table, add < not recommended >. If the data produced cannot be unique, an error occurs when creating a primary key, and then add and delete the test
Mysql > alter table demo5 change ID ID int(4) not null; Mysql > alter table demo5 drop primary key; Mysql > alter table demo5 change id ID int(4) not null primary key auto_increment; A primary key index must contain a unique index, and a unique index must not be a primary key. 3) A table can have at most one primary key, but multiple unique indexes can be created. 4) Primary keys are more suitable for unique representations that are not easily changed, such as auto-increment, id numbers, etc.
4. Composite indexes
An index can contain one, two, or more columns. An index on two or more columns is called a composite index
mysql> create table `m_user`(`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` char(32) NOT NULL, `age` tinyint(4) NOT NULL , `school` char(123) NOT NULL , `status` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`), KEY `name` (`name`,`age`,`status`)) ;
Query OK, 0 rows affected (0.00 sec)
mysql> desc `m_user`;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | char(32) | NO | MUL | NULL | |
| age | tinyint(4) | NO | | NULL | |
| school | char(123) | NO | | NULL | |
| status | tinyint(4) | NO | | 1 | |
+--------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> insert into `m_user` values(1.'zmedu'.'23'.'Tsinghua University'.'reading');
Query OK, 1 row affected, 1 warning (0.00 sec)
Copy the code5. FULLTEXT INDEX
Full-text indexing (also known as full-text retrieval) is a key technology used by search engines at present. It can use “word segmentation technology” and other algorithms to intelligently analyze the frequency and importance of keywords in the text, and then intelligently screen out the search results we want according to certain algorithm rules. ABSUWU like ‘% U_U ‘MySQL > select * from bName where bName like ‘% net %’ MySQL > select * from bName where bName like ‘% net %’ MySQL > select * from bName where bName like ‘% net %’ Ngram full text search plugin is built into MySQL 5.7.6 to support Chinese word segmentation
Mysql > show create table name;
Full-text indexes can only be used in VARCHAR text
Create a fulltext index. Create a fulltext key index.
Alter table alter table name add fullTEXT index name ALTER TABLE books ADD FULLTEXT
Note: MySQL’s full-text index can only be used for tables with MyISAM as the database engine. It is usually handed over to third-party software for full-text indexing. sphinxsearch.com/
Index design principles
- Do not overuse indexes. If a table has a large number of indexes, it will not only occupy disk space, but also affect inset, UPDATE, and DELETE performance.
- Do not index poorly differentiated fields, such as gender fields, because indexing is not optimal
- When uniqueness is characteristic of a field, making a unique index can speed up the query
conclusion
MySQL index learning is more boring, be sure to do the case several times.