Usage and differences between indexes and views in MySQL

Usage and differences between indexes and views in MySQL

Preface

This article mainly introduces the usage and differences of indexes and views in MySQL, and shares the content for your reference, let's take a look at the detailed introduction.

Index

I. Overview

All Mysql column types can be indexed.

Mysql supports B-tree indexes, HASH indexes, prefix indexes, and full text indexes (FULLTEXT). [only supported by the MyISAM engine, and only supported by char and varchar, text column and spatial column index (only supported by the MyISAM engine and the index field must be non-empty), but function indexes are not supported.

MyISAM and InnoDB Storage engine tables create BTREE indexes by default,
By default, the MEMORY storage engine creates a HASH index for a table.

2. Create an index

The create index syntax 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 usealter tableAdd an index. 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 Indexes

Availableshow index from table;View All indexes of the table.

4. delete an index

drop index index_name on tbl_name;

V. B-tree index and HASH Index

For MEMORY storage engine tables, you can choose to use the BTREE index and HASH index.

BTREE index:

• When >,<=, >=, <=, ,! =, <> Or like xxx (xxx does not start with a wildcard) operator, you can use the BTREE index of the relevant column.

Notes for using HASH indexes: (related to the limitations of HASH tables)

• It can only be used for Equality comparison between = or <=> operators.
• The optimizer cannot use HASH indexes to accelerate the order by operation.
• Mysql cannot determine the number of rows before the two values, which may affect the query efficiency.
• Only one row can be searched using the entire keyword.

Vi. principles for designing Indexes

The index column to be searched is not necessarily the column to be selected. The column most suitable for indexing is the column that appears in the where clause, rather than the column that appears after the select clause.

Use a unique index. Select columns that are easy to distinguish between values for indexing. For example, the birthday index should be better than the Gender Index, because the birthday column has different values and is easier to differentiate, while the gender column only has M and F, so the index is of little use at this time, each index generates about half of the rows.

Use short indexes. A string prefix index usually specifies a prefix length. If multiple values are unique within the first 10 to 20 characters, you do not need to index the entire column, index the first 10 to 20 characters. This saves index space, reduces I/O time, and improves query efficiency.

Do not over-indexing. Each additional index occupies extra space to reduce the write performance. When a table is modified, the index needs to be updated or may even be restructured. Therefore, the more indexes, the longer it takes. In addition, MySQL takes into account various indexes when generating the execution plan, and redundant indexes make the query optimization work heavier.

View

I. Overview

Mysql provides the view function from version 5.0.1.

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

2. Create or modify a view
To create a viewcreate viewPermission, and you must have the select permission on the tables and columns involved in the query.

If you usecreate or replaceAlternatively, you must have the drop permission for the view to be modified with the alter permission.

View creation Syntax:

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

Modify the view syntax:

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

Mysql has some restrictions on the view definition. For example, subqueries cannot be included after the from keyword, which is different from other databases.

Iii. updatability of views

The updatability of a view is related to the definition of a View query. The following types of views are not updatable.

• Includes Aggregate functions (sum, min, max, count, etc.), distinct, group by, having, union, or union all.
• Constant view.
• Select contains subqueries.
• Join.
• From is a non-updatable view.
• The subquery of the where statement references the table in the from statement.

For example, the following views cannot be updated.

-- Includes the 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 contains the subquery mysql> create view city_view as-> select (select city from city where city_id = 1 );

with[cascaded|local] check optionThe option determines whether to allow data update so that the record no longer meets the view conditions. The default value is cascaded. This option is similar to the option in the Oracle database.

• Local: update as long as the conditions of this view are met
• Cascaded: all the conditions for all views under this view must be met before update.

Iv. delete a view

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

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

For example, deleting a view pay_view

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

5. view the view

From MySQL 5.1, the show tables command not only displays the table name, but also the view name, without the show views command.

You can also run the following command:

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

Example

mysql> show table status like 'pay_view' \G*************************** 1. row ***************************  Name: pay_view  Engine: NULL Version: NULL Row_format: NULL  Rows: NULL Avg_row_length: NULL Data_length: NULLMax_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW1 row in set (0.00 sec)

To view the definition of a view, you can useshow create viewView.

Example

mysql> show create view pay_view \G*************************** 1. row ***************************  View: pay_view  Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `pay_view` AS select `pay`.`pid` AS `pid`,`pay`.`amount` AS `amount` from `pay` where (`pay`.`amount` < 10) WITH CASCADED CHECK OPTIONcharacter_set_client: gbkcollation_connection: gbk_chinese_ci1 row in set (0.00 sec)

Finally, you can view the system tableinformation_schema.viewsView information.

Example

mysql> select * from information_schema.views where table_name = 'pay_view' \G*************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: mysqldemo  TABLE_NAME: pay_view VIEW_DEFINITION: select `mysqldemo`.`pay`.`pid` AS `pid`,`mysqldemo`.`pay`.`amount` AS `amount` from `mysqldemo`.`pay` where (`mysqldemo`.`pay`.`amount` < 10) CHECK_OPTION: CASCADED IS_UPDATABLE: YES  DEFINER: root@localhost SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: gbkCOLLATION_CONNECTION: gbk_chinese_ci1 row in set (0.03 sec)

Q &:

Can the MySQL view be indexed?

I think the answer is yes. Its index is based on the real table behind the view, rather than the view.

An index is a database object stored in a schema. The index is used to speed up table search and query. The index is used to quickly locate data through quick access, this reduces disk read/write operations. An index is an object of A database. It cannot exist independently and must depend on a table object.

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

References

Tang hanming, et al, goes deep into MySQL, people's post and telecommunications press, 2014

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.