Explanation of the Usage and Difference of Index and View in MySQL

Preface

This article mainly introduces the related contents about the use and difference of indexes and views in MySQL, and shares them for your reference and study. The following words are not much to say, let's take a look at the detailed introduction.

Index

1. Overview

All Mysql column types can be indexed.

mysql supports BTREE index, HASH index, prefix index, full-text index (FULLTEXT) "only supported by MyISAM engine, and only limited to char, varchar, text columns", spatial column index "only supported by MyISAM engine, and the fields of index must be non-empty", but does not support functional index.

The tables of the MyISAM and InnoDB storage engines create BTREE indexes by default,
The tables of the MEMORY storage engine create HASH indexes by default.

Step 2 Create an index

The syntax for create index is:

create [unique|fulltext|spatial] index index_name
 [using index_type]
on tbl_name(index_col_name, ...);

index_col_name:
 col_name [(length)] [asc/desc]

You can also use the alter table Add an index with the syntax:

ALTER [IGNORE] TABLE tbl_name
 alter_specification [, alter_specification] ...

alter_specification:
 ...
 ADD INDEX [index_name] [index_type] (index_col_name,...)
 ...

For example: Create a 10-byte prefix index for the city table

mysql> create index cityName on city(Name(10));
mysql> alter table city add index cityName(Name(10));

3. View the index

You can use the show index from table; View all current indexes of the table table.

Step 4 Drop the index

drop index index_name on tbl_name;

5. BTREE Index and HASH Index

The tables of the MEMORY storage engine can choose to use the BTREE index and the HASH index

BTREE Index:

When using > , < , =, > =, < =, between,! =, < > Or the like xxx (xxx does not start with a wildcard) operator, you can use the BTREE index on the related column.

Considerations for the use of HASH indexes: (Related to limitations of HASH tables)

Can only be used with = or < = > Equality comparison between operators. The optimizer cannot use the HASH index to speed up order by operations. mysql cannot determine the approximate number of rows before two values, which affects the efficiency of query 1. You can only search for 1 row using the entire keyword.

6. Principles for designing indexes

Indexed column searched, not 1 must be the column to be selected. The most suitable columns for the index are those that appear in the where clause, not those that appear after select.

Use only 1 index. Select columns that are easily numerically distinguishable for indexing. For example, the index on birthday is better than the index on gender, because the columns on birthday have different values and are easier to distinguish, while the columns on gender are only M and F, so the index is of little use at this time, and each index produces about 1.5 rows.

Use a short index. A prefix length is usually specified in the prefix index of a string. If multiple values are 1-only within the first 10 to 20 characters, you can index the first 10 to 20 characters instead of the whole column. This can save index space, reduce I/O time and improve query efficiency.

Don't over-index. Each additional index takes up extra space, degrades write performance, updates indexes when tables are modified, and may even be reconstructed, so the more indexes, the longer it takes. In addition, MySQL will consider each index when generating the execution plan, and the redundant index makes the query optimization work more arduous.

View

1. Overview

mysql has provided view functionality since version 5.0. 1.

A view is a virtual table that does not actually exist in the database. The row and column data comes from the table used in the query of the custom view, and is generated dynamically when the view is used.

2. Create or modify views
Creating a view requires the create view Permissions, and select permissions on the tables and columns involved in the query.

If you use create or replace Or alter permission, you also need drop permission for that view.

The syntax for creating a view is:

create [or replace][algorithm = {undefined|merge|temptable}]
 view view_name[(column_list)]
 as select_statement
 [with [cascade|local] check option]

Modify the view syntax to:

alter [algorithm = {undefined|merge|temptable}]
 view view_name[(column_list)]
 as select_statement
 [with [cascade|local] check option]

mysql has some limitations on the definition of views. For example, from keywords cannot contain subqueries, which is different from other databases.

3. View updatability

The updatability of views is related to the definition of queries in views, and the following types of views are non-updatable.

Contains aggregate functions (sum, min, max, count, etc.), distinct, group by, having, union, or union all. Constant view. Subqueries are included in select. join. from1 views that cannot be updated. The tables in the from statement are referenced in the subquery of the where statement.

For example, the following views are not updatable

--  Include aggregate functions 
mysql > create or replace view payment_sum as 
 -> select staff_id,sum(amount) 
 -> from payment
 -> group by staff_id;

--  Constant view 
mysql > create or replace view pi as 
 -> select 3.1415926 as pi;

-- select Include subqueries in 
mysql > create view city_view as
 -> select ( select city from city where city_id = 1);

with[cascaded|local] check option Option determines whether to allow the data to be updated so that the record no longer meets the conditions of the view, which defaults to cascaded. This option is similar to the one in the Oracle database.

local: Can be updated as long as the conditions of this view are met cascaded: All conditions for all views under this view must be met before it can be updated.

4. Delete the view

You can delete one or more views at a time, but you must have drop permission for that view.

drop view [if exists] view_name [,view_name] ... [restrict|cascaded]

For example, delete the view pay_view

mysql> drop view pay_view1,pay_view2;
Query OK, 0 rows affected (0.00 sec)

5. View the view

MySQL Beginning with version 5.1, the show tables command displays not only the name of the table, but also the name of the view, and there is no show views command that displays the view alone.

Similarly, you can also view it through the following command:

show table status [from db_name] [like 'pattern']

Example

ALTER [IGNORE] TABLE tbl_name
 alter_specification [, alter_specification] ...

alter_specification:
 ...
 ADD INDEX [index_name] [index_type] (index_col_name,...)
 ...

0

If you want to view the definition of a view, you can use the show create view Check.

Example

ALTER [IGNORE] TABLE tbl_name
 alter_specification [, alter_specification] ...

alter_specification:
 ...
 ADD INDEX [index_name] [index_type] (index_col_name,...)
 ...

1

Finally, you can also view the system tables by information_schema.views To view information about the view.

Example

ALTER [IGNORE] TABLE tbl_name
 alter_specification [, alter_specification] ...

alter_specification:
 ...
 ADD INDEX [index_name] [index_type] (index_col_name,...)
 ...

2

Q & A:

Can an MySQL view be indexed?

I think the answer is yes, and the index is built on the real table behind the view, not on the view.

Index is a database object stored in the schema (schema). The function of index is to improve the retrieval and query speed of tables. Index is to locate data quickly by quick access, thus reducing the reading and writing operations to disk. An index is an object of a database. It cannot exist independently and must depend on a table object.

A view is the result of a query on one or more tables. It is a virtual table because it does not store data.

References

Tang Hanming is waiting, "MySQL in a simple way", People's Post and Telecommunications Press, 2014

Summarize