In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)06/01 Report--
The best way to improve query performance is to create an index. An index item is like a pointer to a row in a table, allowing the query to quickly find the row to be queried through the WHERE condition. All data types of MySQL can be indexed.
Unnecessary indexes consume the space of the system and the time MySQL takes to determine which index to use. Indexing also increases the cost of DML operations, and a balance needs to be found between improving query speed and system resource consumption.
-- prefix index
For character fields, you can create only one index that contains only the first N characters of the field. This makes the index smaller. When you create an index on a BLOB or TEXT field, you must specify a prefix index.
The prefix length can be up to 1000 bytes (for InnoDB tables, unless the innodb_large_prefix parameter is turned on).
-- example ①
Mysql > show variables like'% prefix%'
+-+ +
| | Variable_name | Value |
+-+ +
| | innodb_large_prefix | ON |
+-+ +
1 row in set (0.39 sec)
CREATE TABLE test5 (blob_col BLOB, INDEX (blob_col (10))
Prefixes can be up to 1000 bytes long (bytes for InnoDB tables, unless you have innodb_large_prefix set).
Mysql > CREATE TABLE test5 (blob_col BLOB, INDEX (blob_col (10))
Query OK, 0 rows affected (0.27 sec)
Mysql > desc test5
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | blob_col | blob | YES | MUL | NULL |
+-+ +
1 row in set (0.06 sec)
Mysql > show keys from test5
+- -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+- -+
| | test5 | 1 | blob_col | 1 | blob_col | A | 0 | 10 | NULL | YES | BTREE |
+- -+
1 row in set (0.00 sec)
-- example ②
Queries that begin with% in the LIKE condition do not use the index
Mysql > create table emp (id int (2), name varchar (30))
Query OK, 0 rows affected (0.22 sec)
Mysql > insert into emp values (1000 years JiaJianning`)
Query OK, 1 row affected (0.18 sec)
Mysql > insert into emp values (2000 Magi JiaDingyi')
Query OK, 1 row affected (0.07 sec)
Mysql > insert into emp values (3000 JiaLiying`)
Query OK, 1 row affected (0.00 sec)
Mysql > insert into emp values (4000 mai JiaPeiyuan')
Query OK, 1 row affected (0.09 sec)
Mysql > create index idx_title on emp (name (5))
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > show keys from emp
+ -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+ -+
| | emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE |
+ -+
1 row in set (0.00 sec)
Mysql > explain select * from emp
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from emp where name like 'Jia%'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ALL | idx_title | NULL | NULL | NULL | 4 | 100.00 | Using where |
+-- +
1 row in set, 1 warning (0.16 sec)
Mysql > explain select * from emp where name like'% Jia%'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from emp where name like 'jia%'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ALL | idx_title | NULL | NULL | NULL | 4 | 100.00 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
-- example ③
Implicit conversion of data type
Mysql > desc emp
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (2) | YES | | NULL |
| | name | varchar (30) | YES | MUL | NULL |
+-+ +
2 rows in set (0.00 sec)
Mysql > show keys from emp
+ -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+ -+
| | emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE |
+ -+
1 row in set (0.00 sec)
Mysql > desc emp
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (2) | YES | | NULL |
| | name | varchar (30) | YES | MUL | NULL |
+-+ +
2 rows in set (0.00 sec)
Mysql > create index idx_emp_id on emp (id)
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > show keys from emp
+ -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+ -+
| | emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE |
| | emp | 1 | idx_emp_id | 1 | id | A | 4 | NULL | NULL | YES | BTREE |
+ -+
2 rows in set (0.00 sec)
Mysql > select * from emp
+-+ +
| | id | name |
+-+ +
| | 1000 | JiaJianning |
| | 2000 | JiaDingyi |
| | 3000 | JiaLiying |
| | 4000 | JiaPeiyuan |
+-+ +
4 rows in set (0.00 sec)
Mysql > explain select * from emp where id=1000
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ref | idx_emp_id | idx_emp_id | 5 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from emp where id='1000'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ref | idx_emp_id | idx_emp_id | 5 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from emp where id='1000' or id=8000
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ALL | idx_emp_id | NULL | NULL | NULL | 4 | 50.00 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
-- full-text index
Full-text index is used for full-text search. Only InnoDB and MyISAM storage engines support full-text indexing, and only apply to CHAR, VARCHAR, TEXT fields.
-- Spatial index
The MyISAM and InnoDB storage engines support R-tree indexing on spatial types.
-- Index on the MEMORY storage engine
The MEMORY storage engine uses hash indexes by default, but also supports BTREE indexes.
-- Joint index
A federated index can contain up to 16 fields. Mysql > CREATE TABLE test (
-> id INT NOT NULL
-> last_name CHAR (30) NOT NULL
-> first_name CHAR (30) NOT NULL
-> PRIMARY KEY (id)
-> INDEX name (last_name,first_name)
->)
Query OK, 0 rows affected (0.17 sec)
Mysql > insert into test values (1 recordings Terryalty pencils John')
Query OK, 1 row affected (0.07 sec)
Mysql > insert into test values (2)
Query OK, 1 row affected (0.02 sec)
Mysql > insert into test values (3 recordings, Lilystones, and Weber')
Query OK, 1 row affected (0.00 sec)
Mysql > insert into test values (4)
Query OK, 1 row affected (0.07 sec)
Mysql > insert into test values (5)
Query OK, 1 row affected (0.01sec)
Mysql > select * from test
+-- +
| | id | last_name | first_name | |
+-- +
| | 2 | Allice | Hanks |
| | 5 | David | Beckham |
| | 3 | Lily | Weber |
| | 4 | Lucy | Willis |
| | 1 | Terry | John |
+-- +
5 rows in set (0.00 sec)
Mysql > show keys from test
+- -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+- -+
| | test | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE |
| | test | 1 | name | 1 | last_name | A | 5 | NULL | NULL | | BTREE |
| | test | 1 | name | 2 | first_name | A | 5 | NULL | NULL | | BTREE |
+- -+
3 rows in set (0.00 sec)
Mysql > explain select * from test where last_name like 'All%'
+- -+
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -+
| | 1 | SIMPLE | test | NULL | range | name | name | 30 | NULL | 1 | 100.00 | Using where; Using index |
+- -+
1 row in set, 1 warning (0.09 sec)
Mysql > explain select * from test where last_name = 'All%'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | test | NULL | ref | name | name | 30 | const | 1 | 100.00 | Using index |
+-- +
1 row in set, 1 warning (0.07 sec)
Mysql > explain select * from test where last_name = 'Allice'
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | test | NULL | ref | name | name | 30 | const | 1 | 100.00 | Using index |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from test where last_name like 'All%' and first_name like' H%'
+- -+
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -+
| | 1 | SIMPLE | test | NULL | range | name | name | 60 | NULL | 1 | 20.00 | Using where; Using index |
+- -+
1 row in set, 1 warning (0.09 sec)
Mysql > explain select * from test where first_name like'H%'
+- -+
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -+
| | 1 | SIMPLE | test | NULL | index | NULL | name | 60 | NULL | 5 | 20.00 | Using where; Using index |
+- -+
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from test where first_name ='H%'
+- -+
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -+
| | 1 | SIMPLE | test | NULL | index | NULL | name | 60 | NULL | 5 | 20.00 | Using where; Using index |
+- -+
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from test where first_name = 'Hanks'
+- -+
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+- -+
| | 1 | SIMPLE | test | NULL | index | NULL | name | 60 | NULL | 5 | 20.00 | Using where; Using index |
+- -+
1 row in set, 1 warning (0.00 sec)
-- comparison between B-tree index and hash index
B-tree index applies to =, >, > =, desc T1
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | F1 | int (11) | YES | | NULL |
| | gc | int (11) | YES | MUL | NULL | STORED GENERATED |
+-+ +
2 rows in set (0.07 sec)
Mysql > show index from T1
+- -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+- -+
| | T1 | 1 | gc | 1 | gc | A | 0 | NULL | NULL | YES | BTREE |
+- -+
1 row in set (0.00 sec)
Mysql > insert into T1 (F1) values (1)
Query OK, 1 row affected (0.13 sec)
Mysql > insert into T1 (F1) values (2)
Query OK, 1 row affected (0.08 sec)
Mysql > insert into T1 (F1) values (3)
Query OK, 1 row affected (0.01sec)
Mysql > insert into T1 (F1) values (4)
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T1 (F1) values (5)
Query OK, 1 row affected (0.00 sec)
Mysql > select * from T1
+-+ +
| | F1 | gc |
+-+ +
| | 1 | 2 |
| | 2 | 3 |
| | 3 | 4 |
| | 4 | 5 |
| | 5 | 6 |
+-+ +
5 rows in set (0.00 sec)
Mysql > explain select * from T1 where F1 = 1
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | T1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+-- +
1 row in set, 1 warning (0.09 sec)
Mysql > explain select * from T1 where gc = 1
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | T1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.04 sec)
Mysql > explain select * from T1 where gc = 2
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | T1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from T1 where F1 = 2
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | T1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from T1 where F1 = 5
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | T1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from T1 where f1141 > = 2
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | T1 | NULL | ALL | gc | NULL | NULL | NULL | 5 | 100.00 | Using where |
+-- +
1 row in set, 1 warning (0.03 sec)
-- the index is used for scanning null values
Mysql > desc emp
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (2) | YES | | NULL |
| | name | varchar (30) | YES | MUL | NULL |
+-+ +
2 rows in set (0.07 sec)
Mysql > show index from emp
+ -+
| | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+ -+
| | emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE |
+ -+
1 row in set (0.00 sec)
Mysql > select * from emp where name is not null
+-+ +
| | id | name |
+-+ +
| | 1000 | JiaJianning |
| | 2000 | JiaDingyi |
| | 3000 | JiaLiying |
| | 4000 | JiaPeiyuan |
+-+ +
4 rows in set (0.01sec)
Mysql > select * from emp where name is null
Empty set (0.08 sec)
Mysql > explain select * from emp where name is not null
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ALL | idx_title | NULL | NULL | NULL | 4 | 100.00 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > explain select * from emp where name is null
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | emp | NULL | ref | idx_title | idx_title | 8 | const | 1 | 100.00 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
Mysql > show warnings
+- -- +
| | Level | Code | Message | |
+- -- +
| | Note | 1003 | / * select#1 * / select `fire`.`emp`.`id`AS `id`, `fire`.`emp`.`name`AS `name`from `fire`.`emp`where isnull (`fire`.`emp`.`name`) |
+- -- +
1 row in set (0.11 sec)
-- check the usage of the index
Handler_read_rnd_next
The number of requests to read the next line in the data file. If a large number of full table scans are performed, the value of this parameter will be very high. Typically, this parameter is used to suggest that the table is not properly indexed or that the query does not make good use of the existing index.
Mysql > show global status like 'Handler_read_rnd%'
+-+ +
| | Variable_name | Value |
+-+ +
| | Handler_read_rnd | 0 | |
| | Handler_read_rnd_next | 1521 | |
+-+ +
2 rows in set (0.00 sec)
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.
12
Report
