Detailed explanation of the usage and differences between indexes and views in MySQL

Preface

This article mainly introduces the use and differences of indexes and views in MySQL, and shares it for your reference and learning. Without further ado, 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 limited to char, varchar, text columns], spatial column index [only supported by MyISAM engine, and the indexed field must be non-empty], but does not support function index.

The MyISAM and InnoDB storage engine tables create BTREE indexes by default.
By default, a HASH index is created for a table using the MEMORY storage engine.

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 alter table to add an index. The syntax is:

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 show index from table; to view all current indexes of the table.

4. Delete index

drop index index_name on tbl_name;

5. BTREE index and HASH index

Tables with the MEMORY storage engine can choose to use BTREE indexes and HASH indexes

BTREE Index:

• When using the >, <, =, >=, <=, between, !=, <>, or like xxx (xxx does not start with a wildcard) operator, the BTREE index on the relevant columns can be used.

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

• Can only be used for equality comparisons using the = or <=> operators.
• The optimizer cannot use HASH indexes to speed up order by operations.
• MySQL cannot determine approximately how many rows there are before the two values, which will affect the efficiency of the query to a certain extent.
• Only one row can be searched using the entire keyword.

6. Principles of index design

The index columns to be searched are not necessarily the columns to be selected. The most suitable columns for indexing are those that appear in the where clause, not those that appear in the select statement.

Use a unique index. Choose columns whose values ​​are easily distinguishable for indexing. For example, an index on birthday is better than an index on gender because the birthday column has different values ​​and is easier to distinguish, while the gender column only has M and F. In this case, the index is not very useful and each index will return about half of the rows.

Use short indexes. A prefix length is usually specified for the prefix index of a string. If most values ​​are unique within the first 10 to 20 characters, you do not need to index the entire column, but instead index the first 10 to 20 characters. This can save index space, reduce I/O time, and improve query efficiency.

Don't over-index. Each additional index takes up additional space and reduces the performance of write operations. When the table is modified, the index needs to be updated and may even be rebuilt, so the more indexes there are, the longer it takes. In addition, MySQL takes various indexes into consideration when generating an execution plan. Redundant indexes make the query optimization task more arduous.

view

1. Overview

MySQL provides view function since version 5.0.1.

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

2. Create or modify a view <br /> To create a view, you need the create view permission and the select permission for the tables and columns involved in the query.

If you use the create or replace or alter permission to modify a view, you also need the drop permission for the 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 restrictions on view definitions. For example, the from keyword cannot contain a subquery, which is different from other databases.

3. Updatability of Views

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

• Contains aggregate functions (sum, min, max, count, etc.), distinct, group by, having, union, or union all.
• Constant view.
• The select contains a subquery.
• join.
• from a view that cannot be updated.
• The subquery in the where clause references the table in the from clause.

For example: The following views are not updateable

-- Contains aggregate functionsmysql > 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 subquerymysql > create view city_view as
 -> select ( select city from city where city_id = 1);

The with[cascaded|local] check option determines whether to allow updates to data that make records no longer meet the view conditions. The default is cascaded. This option is similar to the option in 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 the update can be performed.

4. 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, delete the view pay_view

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

5. View View

Starting from MySQL version 5.1, the show tables command will not only display the name of the table, but also the name of the view. 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

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: NULL
Max_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: VIEW
1 row in set (0.00 sec)

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

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 OPTION
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)

Finally, you can also view relevant information about the view by viewing the system table information_schema.views .

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: DEFINER
CHARACTER_SET_CLIENT: gbk
COLLATION_CONNECTION: gbk_chinese_ci
1 row in set (0.03 sec)

Q&A:

Can MySQL views use indexes?

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

An index is a database object stored in a schema. The function of an index is to increase the speed of table retrieval queries. An index is a fast access method to quickly locate data, thereby reducing disk read and write operations. An index is an object in the 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, etc., "MySQL in Simple Terms", Posts and Telecommunications Press, 2014

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.