Indexes

What is index

There are two ways to search tables in the database ：

• Full table scan ：（ When there is too much data in the table, there will be efficiency problems ）

• Index search ：（ Efficient ）

principle ： Narrow the scope of scanning files .

Be careful ----- Although index can improve retrieval efficiency , But you can't just add indexes , Because the index is also an object in the database , It also needs constant maintenance , With maintenance costs . Once the data is changed , The index may also be changed

When to add an index

• The amount of data is huge （ According to the needs of customers , According to the online environment ）
• This field is rarely dml operation .（ Because the field cannot be modified , Indexes also need to be maintained ）
• This field often appears in where clause .（ Often query according to a certain field ）

Try to retrieve according to the primary key

Create an index

create index < The index name > on < Table name >([ Field name ]);

mysql> create index yin on pay(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select stu_class,stu_name from t_student where stu_class = 2;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |    12.50 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Delete index

drop index < Index name > on < Table name >;

see sql Statement execution plan

explain < sentence >;

mysql> explain select * from pay;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | pay   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

Classification of indexes

• Single index ： Add an index to a single field

• Composite index ： Add a union index to multiple fields

• primary key ： An index is automatically added to the primary key

• unique index ： Yes unique Indexes are automatically added to the constrained fields

Invalidation of index

Fuzzy queries may invalidate index queries --------- The first wildcard uses %

---

View (views)

What is the view

Look at the data from different perspectives .（ Data from the same table , Look at... From different angles ）

Be careful ： Adding, deleting, modifying and querying through the view will affect the original table data

Create view

create view < View name > as select  Query statement ;

View operation

mysql> create view v_student as select stu_id,stu_name,stu_age from t_student;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v_student;
+--------+----------+---------+
| stu_id | stu_name | stu_age |
+--------+----------+---------+
|      1 | tom      |      18 |
|      2 | andy     |      17 |
|      3 | zhansan  |      16 |
|      4 | jery     |      20 |
|      5 | ldy      |      19 |
|      6 | lis      |    NULL |
|      7 | lby      |    NULL |
|     14 | mah      |      19 |
+--------+----------+---------+
8 rows in set (0.00 sec)

The function of view

---

The view can hide the implementation details of the table . A system with a high level of confidentiality , The database only provides related views , We can only operate on the view