찾다
집데이터 베이스MySQL 튜토리얼MySQL 인덱스 상세 설명+설명

MySQL 인덱스 상세 설명+설명

coldplay.xixi
coldplay.xixi
Nov 27, 2020 pm 04:57 PM
explainmysql색인

mysql 비디오 튜토리얼 오늘 칼럼은 인덱싱에 초점을 맞추고 인터뷰 준비를 설명합니다.

MySQL 인덱스 상세 설명+설명

무료 추천: mysql 동영상 튜토리얼

1. 인덱스 소개

  1. mysql에서 인덱스는 데이터 구조, 인덱스에 따라 정렬된 구조
  2. 인덱스를 사용하면 쿼리 속도는 빨라질 수 있지만 데이터 추가, 삭제, 수정 효율성이 떨어지게 됩니다.
  3. 웹사이트의 대부분은 쿼리이기 때문에 주로 select문을 최적화합니다.

2. MySQL의 인덱스 분류

  • 일반 인덱스key
  • 고유 키 고유 키 별칭 별칭은 무시 가능 별칭은 무시 가능
  • 기본 키 인덱스기본 키(필드)
  • 전체 텍스트 인덱스 myisam 엔진 지원(영어만 지원) Index, mysql 버전 5.6도 지원), sphinx(중국어 검색)
  • Hybrid index key key_index(제목, 이메일) 등 여러 필드로 구성된 인덱스
3. 기본 작업 index

1. 테이블 제공 index 추가

create table t_index(
    id int not null auto_increment,
    title varchar(30) not null default '',
    email varchar(30) not null default '',
    primary key(id),
    unique key uni_email(email) ,
    key key_title(title)
)engine=innodb charset=utf8;

View table

desc tablename

mysql> desc t_index;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| title | varchar(30) | NO   | MUL |         |                |
| email | varchar(30) | NO   | UNI |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
desc tablename
mysql> show create table t_index/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show create table t_index\G;
*************************** 1. row ***************************
       Table: t_index
Create Table: CREATE TABLE `t_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL DEFAULT '',
  `email` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_email` (`email`),
  KEY `key_title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

查看表的创建语句

show create table tbalename/G

mysql> alter table t_index drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

2、删除索引

  1. 删除主键索引

alter table table_name drop primary key;

注意：

mysql> alter table t_index modify  id int not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键不一定是自增长，但是自增长一定是主键。

删除逐渐之前先要把主键索引的自增长去掉。

mysql> alter table t_index drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

再来删除主键

mysql> alter table t_index drop key uni_email;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
  1. 删除普通和唯一的索引

alter table table_name drop key ‘索引的别名’

实际操作

mysql> alter table t_index drop key key_title;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
alter table t_index add key key_title(title);
alter table t_index add key uni_email(email);
alter table t_index add primary key(id);

3、添加索引

create table article(
id int not null auto_increment,
no_index int,
title varchar(30) not null default '',
add_time datetime,
primary key(id)
);

4、有无索引对比

mysql> insert into article(id,title,add_time) values(null,'ddsd1212123d',now());

mysql> insert into article(title,add_time) select title,now() from article;
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> update article set no_index=id;

插入数据

mysql> select * from article where no_index=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.28 sec)

有无索引查询数据对比

mysql> select * from article where id=1495298;
+---------+----------+-----------+---------------------+
| id      | no_index | title     | add_time            |
+---------+----------+-----------+---------------------+
| 1495298 |  1495298 | ddsd1123d | 2019-05-15 23:13:56 |
+---------+----------+-----------+---------------------+
1 row in set (0.01 sec)
mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1572824 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified

表结构

mysql> mysql> explain select * from article where no_index=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE//单表查询
        table: article//查询的表名
   partitions: NULL
         type: ALL//索引的类型，从好到坏的情况是：system>const>range>index>All
possible_keys: NULL//可能使用到的索引
          key: NULL//实际使用到的索引
      key_len: NULL//索引的长度
          ref: NULL
         rows: 1307580//可能进行扫描表的行数
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

四、explain分析

使用explain可以对sql语句进行分析到底有没有使用到索引查询,从而更好的优化它.

我们只需要在select语句前面加上一句explain或者desc.

1、语法

explain|desc select * from tablename G;

테이블 생성문 보기

show create table tbalename/ G

mysql> explain select * from article where id=1495298\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const//当对主键索引进行等值查询的时候出现const
possible_keys: PRIMARY
          key: PRIMARY//实际使用到的所有primary索引
      key_len: 4//索引的长度4 = int占4个字节
          ref: const
         rows: 1//所扫描的行数只有一行
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

2. 인덱스 삭제

기본 키 인덱스 삭제

  • alter table table_name drop 기본 키;
    • 참고: 
  • mysql> use mysql;
mysql> explain select * from user\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
    기본 키는 그렇지 않습니다. 반드시 자동 증가하지만 자동 증가가 기본 키여야 합니다.
    • 인덱스를 삭제하기 전에 먼저 기본 키 인덱스의 자동 증가를 제거해야 합니다. 
  • mysql> use test;
mysql> explain select * from article where id=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
    기본 키를 삭제하자
    mysql> explain select * from article where id>666666\G;
mysql> explain select * from article where id
  • 일반 및 고유 인덱스 삭제
  • alter table table_name drop key '인덱스 별칭'
    • 실제 작업
mysql> explain select id  from article \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified
mysql> alter table article add key key_no_index(no_index);
Query OK, 0 rows affected (1.92 sec)
Records: 0  Duplicates: 0  Warnings: 0

type为ref，应该是关联，但是ref是const
mysql> explain select * from article where no_index=666666\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: key_no_index
          key: key_no_index
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

速度飞跃
mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.00 sec)

3. 

mysql> explain select * from article order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select * from article where id >0  order by id\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 653790
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

ERROR: 
No query specified

4. 인덱스가 있는 것과 없는 것의 비교

mysql> alter table article  add key key_index(title);
Query OK, 0 rows affected (2.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

데이터 삽입

mysql> explain select * from article where title like 'a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: range//范围查询
possible_keys: key_index
          key: key_index
      key_len: 92//
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where title like '%a%'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表查询
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

인덱스가 있는 것과 없는 쿼리 데이터의 비교

mysql> explain select sql_no_cache  * from article limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ALL//全表
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1307580
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified

mysql> explain select sql_no_cache  * from article order by id  limit 90000,10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY//使用到了索引
      key_len: 4
          ref: NULL
         rows: 90010
     filtered: 100.00
        Extra: NULL
1 row in set, 2 warnings (0.00 sec)

ERROR: 
No query specified
mysql> select sql_no_cache  * from article limit 1000000,10;
+---------+----------+----------------+---------------------+
| id      | no_index | title          | add_time            |
+---------+----------+----------------+---------------------+
| 1196579 |  1196579 | ddsd12123123ad | 2019-05-15 23:13:56 |
| 1196580 |  1196580 | ddsd121231ad   | 2019-05-15 23:13:56 |
| 1196581 |  1196581 | ddsd1212123d   | 2019-05-15 23:13:56 |
| 1196582 |  1196582 | ddsd1123123d   | 2019-05-15 23:13:56 |
| 1196583 |  1196583 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196584 |  1196584 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196585 |  1196585 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196586 |  1196586 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196587 |  1196587 | ddsd1123d      | 2019-05-15 23:13:56 |
| 1196588 |  1196588 | ddsd1123d      | 2019-05-15 23:13:56 |
+---------+----------+----------------+---------------------+
10 rows in set, 1 warning (0.21 sec)

mysql> select t1.* from article as t1 inner join (select id as pid from article  limit 10000,10) as t2 on t1.id=t2.pid;
+-------+----------+----------------+---------------------+
| id    | no_index | title          | add_time            |
+-------+----------+----------------+---------------------+
| 13058 |    13058 | ddsd12123123ad | 2019-05-15 23:13:49 |
| 13059 |    13059 | ddsd121231ad   | 2019-05-15 23:13:49 |
| 13060 |    13060 | ddsd1212123d   | 2019-05-15 23:13:49 |
| 13061 |    13061 | ddsd1123123d   | 2019-05-15 23:13:49 |
| 13062 |    13062 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13063 |    13063 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13064 |    13064 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13065 |    13065 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13066 |    13066 | ddsd1123d      | 2019-05-15 23:13:49 |
| 13067 |    13067 | ddsd1123d      | 2019-05-15 23:13:49 |
+-------+----------+----------------+---------------------+
10 rows in set (0.00 sec)

테이블 구조

//给no_index和title创建一个复合索引
mysql> alter table article add key index_no_index_title(no_index,title);
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查看创建后的结构
mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//删除no_index和title的索引
mysql> alter table article drop key key_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table article drop key key_no_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//复合索引使用情况
mysql> explain select * from article where title='ddsd1123d' and no_index=77777\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 97
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where  no_index=77777\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)
4
분석 설명

explain을 사용하세요. 더 나은 최적화를 위해 인덱스 쿼리에 사용되거나 사용되지 않습니다.select 문 앞에 explain 또는 desc만 추가하면 됩니다.

1. Syntaxexplain|desc select * from tablename G;

2. 분석

지금 두 인덱스를 사용하여 비교하고 확인하세요

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | OFF                                              |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)
3.

explain

유형 항목 분석은 최고에서 최악으로 정렬됩니다.

system: 일반 시스템 테이블 레코드 행이 하나만 있는 경우에만 나타납니다.

const: id=666666과 같이 기본 키 값에 대해 동일한 값 쿼리를 수행할 때 나타납니다. range

: 인덱스 값에 대해 범위 쿼리를 수행할 때 나타납니다. 예를 들어 where id

index

: 쿼리한 필드가 인덱스 파일의 값일 때 나타납니다.

All: 피해야 할 최악의 상황

실제 테스트 

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | ON                                               |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)
//查看profile设置
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |//未开启状态
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

//开启操作
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//查看是否开启成功
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |//开启成功
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00150700 | show variables like '%profil%'              |
|        2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000291 |
| checking permissions | 0.000007 |
| Opening tables       | 0.012663 |//打开表
| init                 | 0.000050 |
| System lock          | 0.000009 |
| optimizing           | 0.000053 |
| statistics           | 0.001566 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000091 |//磁盘上的发送数据
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000037 |
| cleaning up          | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)
rrreee

쿼리된 필드가 인덱스 파일에 있으면 쿼리는 인덱스 파일에서 직접 수행됩니다. 인덱스 커버리지 쿼리.

전체 스캔으로 인해 피해야 할 모든 것이 나타납니다.

모두 필드에 일반 인덱스 쿼리를 추가할 수 있습니다

rrreee

4. 인덱스 사용 시나리오

🎜1 where 뒤에 자주 나타나는 필드의 경우 해당 필드에 인덱스를 추가해야 합니다🎜🎜2. order by 문 인덱스 최적화🎜rrreee🎜🎜인덱스를 사용해도 거의 풀테이블 스캔인 것을 알 수 있다. 🎜🎜🎜🎜where를 추가하면 절반으로 줄어듭니다🎜🎜🎜3. like🎜🎜🎜'%keyword%'와 같은 where 제목에 대한 퍼지 쿼리 인덱스 최적화 ====>전체 테이블 스캔🎜🎜🎜 🎜'키워드%'와 같은 제목 ===> 인덱스 쿼리가 사용됩니다🎜🎜🎜제목에 인덱스 추가🎜rrreee🎜🎜좋아요 키워드 쿼리의 가장 왼쪽에는 %가 표시되지 않으므로 인덱스 쿼리를 사용할 수 있습니다🎜 🎜🎜 🎜like 왼쪽에 %가 표시되는 한 전체 테이블 쿼리입니다🎜🎜rrreee🎜4. 제한문의 인덱스 사용 최적화🎜🎜제한문의 최적화를 위해 다음을 추가할 수 있습니다. order by index 필드가 앞에 있는 경우🎜🎜order by 필드가 인덱스이고 지정된 수의 데이터 행이 인덱스 파일에서 먼저 검색됩니다🎜rrreee🎜🎜한도에 대한 또 다른 최적화 방법:🎜🎜🎜인덱스 범위 + 지연 연관🎜🎜원리: 주로 인덱스 커버리지를 사용하여 쿼리합니다. 커버링 인덱스 쿼리에서 반환된 ID는 쿼리하려는 레코드의 ID와 연결됩니다. 🎜rrreee🎜5 가장 왼쪽의 복합 원칙(다중 열). index (인터뷰에서 자주 묻는 질문) 🎜🎜쿼리 중에 복합 인덱스의 가장 왼쪽 부분이 나타나면 기사 테이블의 필드만 인덱스 쿼리에 사용됩니다🎜🎜no_index와 기사 테이블 제목에 복합 인덱스를 생성합니다 :🎜
//给no_index和title创建一个复合索引
mysql> alter table article add key index_no_index_title(no_index,title);
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查看创建后的结构
mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `key_no_index` (`no_index`),
  KEY `key_index` (`title`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//删除no_index和title的索引
mysql> alter table article drop key key_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table article drop key key_no_index;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table article\G;
*************************** 1. row ***************************
       Table: article
Create Table: CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `no_index` int(11) DEFAULT NULL,
  `title` varchar(30) NOT NULL DEFAULT '',
  `add_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_no_index_title` (`no_index`,`title`)
) ENGINE=InnoDB AUTO_INCREMENT=1507299 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

//复合索引使用情况
mysql> explain select * from article where title='ddsd1123d' and no_index=77777\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 97
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from article where  no_index=77777\G; 
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: article
   partitions: NULL
         type: ref
possible_keys: index_no_index_title
          key: index_no_index_title
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

五、慢查询日志

1、介绍

我们可以定义(程序员)一个sql语句执行的最大执行时间,如果发现某条sql语句的执行时间超过我们所规定的时间界限,那么这条sql就会被记录下来.

2、慢查询具体操作

  1. 先开启慢日志查询

    查看慢日志配置

    mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | OFF                                              |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

    开启慢日志查询

    mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

    再次检查慢日志配置

    mysql> show variables like '%slow_query%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_query_log      | ON                                               |
| slow_query_log_file | /usr/local/mysql/data/caredeMacBook-Pro-slow.log |
+---------------------+--------------------------------------------------+
2 rows in set (0.00 sec)

  2. 去mysql配置文件my.ini中指定sql语句的界限时间和慢日志文件的路径

    慢日志的名称，默认保存在mysql目录下面的data目录下面

    log-slow-queries = 'man.txt'

    设置一个界限时间

    long-query-time=5

    重启

六、profile工具

1、介绍

通过profile工具分析一条sql语句的时间消耗在哪里

2、具体操作

  1. 开启profile

  2. 执行一条SQL，(开启之后执行的所有SQL语句都会被记录下来

    ，以查看某条sql语句的具体执行时间耗费哪里)

  3. 根据query_id查找到具体的SQL

实例：

//查看profile设置
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | OFF   |//未开启状态
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

//开启操作
mysql> set profiling = on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

//查看是否开启成功
mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |
| profiling              | ON    |//开启成功
| profiling_history_size | 15    |
+------------------------+-------+
3 rows in set (0.00 sec)

具体查询

mysql> select * from article where no_index=666666;
+--------+----------+-----------+---------------------+
| id     | no_index | title     | add_time            |
+--------+----------+-----------+---------------------+
| 666666 |   666666 | ddsd1123d | 2019-05-15 23:13:55 |
+--------+----------+-----------+---------------------+
1 row in set (0.02 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------+
| Query_ID | Duration   | Query                                       |
+----------+------------+---------------------------------------------+
|        1 | 0.00150700 | show variables like '%profil%'              |
|        2 | 0.01481100 | select * from article where no_index=666666 |
+----------+------------+---------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000291 |
| checking permissions | 0.000007 |
| Opening tables       | 0.012663 |//打开表
| init                 | 0.000050 |
| System lock          | 0.000009 |
| optimizing           | 0.000053 |
| statistics           | 0.001566 |
| preparing            | 0.000015 |
| executing            | 0.000002 |
| Sending data         | 0.000091 |//磁盘上的发送数据
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000006 |
| freeing items        | 0.000037 |
| cleaning up          | 0.000010 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

위 내용은 MySQL 인덱스 상세 설명+설명의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명
이 기사는 learnku에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제
관련 기사
InnoDB Redo Logs 및 Undo Logs의 역할을 설명하십시오.InnoDB Redo Logs 및 Undo Logs의 역할을 설명하십시오.Apr 15, 2025 am 12:16 AM

InnoDB는 Redologs 및 Undologs를 사용하여 데이터 일관성과 신뢰성을 보장합니다. 1. Redologs는 사고 복구 및 거래 지속성을 보장하기 위해 데이터 페이지 수정을 기록합니다. 2. 결점은 원래 데이터 값을 기록하고 트랜잭션 롤백 및 MVCC를 지원합니다.

설명 출력 (유형, 키, 행, 추가)에서 찾아야 할 주요 메트릭은 무엇입니까?설명 출력 (유형, 키, 행, 추가)에서 찾아야 할 주요 메트릭은 무엇입니까?Apr 15, 2025 am 12:15 AM

설명 명령에 대한 주요 메트릭에는 유형, 키, 행 및 추가가 포함됩니다. 1) 유형은 쿼리의 액세스 유형을 반영합니다. 값이 높을수록 Const와 같은 효율이 높아집니다. 2) 키는 사용 된 인덱스를 표시하고 NULL은 인덱스가 없음을 나타냅니다. 3) 행은 스캔 한 행의 수를 추정하여 쿼리 성능에 영향을 미칩니다. 4) Extra는 최적화해야한다는 Filesort 프롬프트 사용과 같은 추가 정보를 제공합니다.

설명에서 임시 상태를 사용하고 피하는 방법은 무엇입니까?설명에서 임시 상태를 사용하고 피하는 방법은 무엇입니까?Apr 15, 2025 am 12:14 AM

Temporary를 사용하면 MySQL 쿼리에 임시 테이블을 생성해야 할 필요성이 있으며, 이는 별개의, 그룹 비 또는 비 인덱스 열을 사용하여 순서대로 발견됩니다. 인덱스 발생을 피하고 쿼리를 다시 작성하고 쿼리 성능을 향상시킬 수 있습니다. 구체적으로, 설명 출력에 사용되는 경우, MySQL은 쿼리를 처리하기 위해 임시 테이블을 만들어야 함을 의미합니다. 이것은 일반적으로 다음과 같은 경우에 발생합니다. 1) 별개 또는 그룹을 사용할 때 중복 제거 또는 그룹화; 2) OrderBy가 비 인덱스 열이 포함되어있을 때 정렬하십시오. 3) 복잡한 하위 쿼리 또는 조인 작업을 사용하십시오. 최적화 방법은 다음과 같습니다. 1) Orderby 및 GroupB

다른 SQL 트랜잭션 격리 수준 (커밋되지 않은 읽기, 읽기, 커밋 가능한 읽기, 반복 가능한 읽기, 시리얼이즈 가능) 및 MySQL/innoDB에서의 의미를 설명하십시오.다른 SQL 트랜잭션 격리 수준 (커밋되지 않은 읽기, 읽기, 커밋 가능한 읽기, 반복 가능한 읽기, 시리얼이즈 가능) 및 MySQL/innoDB에서의 의미를 설명하십시오.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB는 4 개의 트랜잭션 격리 수준을 지원합니다. Readuncommitted, ReadCommitted, ReturableRead 및 Serializable. 1. READUCMITTED는 커밋되지 않은 데이터를 읽을 수 있으므로 더러운 판독 값을 유발할 수 있습니다. 2. ReadCommitted는 더러운 읽기를 피하지만 반복 할 수없는 독서가 발생할 수 있습니다. 3. RepeatableRead는 더러운 읽기와 반복 할 수없는 독서를 피하는 기본 레벨이지만 팬텀 독서가 발생할 수 있습니다. 4. 직렬화 가능한 것은 모든 동시성 문제를 피하지만 동시성을 줄입니다. 적절한 격리 수준을 선택하려면 균형 잡힌 데이터 일관성 및 성능 요구 사항이 필요합니다.

MySQL 대 기타 데이터베이스 : 옵션 비교MySQL 대 기타 데이터베이스 : 옵션 비교Apr 15, 2025 am 12:08 AM

MySQL은 웹 응용 프로그램 및 컨텐츠 관리 시스템에 적합하며 오픈 소스, 고성능 및 사용 편의성에 인기가 있습니다. 1) PostgreSQL과 비교하여 MySQL은 간단한 쿼리 및 높은 동시 읽기 작업에서 더 잘 수행합니다. 2) Oracle과 비교할 때 MySQL은 오픈 소스와 저렴한 비용으로 인해 중소 기업에서 더 인기가 있습니다. 3) Microsoft SQL Server와 비교하여 MySQL은 크로스 플랫폼 응용 프로그램에 더 적합합니다. 4) MongoDB와 달리 MySQL은 구조화 된 데이터 및 트랜잭션 처리에 더 적합합니다.

MySQL Index Cardinality는 쿼리 성능에 어떤 영향을 미칩니 까?MySQL Index Cardinality는 쿼리 성능에 어떤 영향을 미칩니 까?Apr 14, 2025 am 12:18 AM

MySQL Index Cardinality는 쿼리 성능에 중대한 영향을 미칩니다. 1. 높은 카디널리티 인덱스는 데이터 범위를보다 효과적으로 좁히고 쿼리 효율성을 향상시킬 수 있습니다. 2. 낮은 카디널리티 인덱스는 전체 테이블 스캔으로 이어질 수 있으며 쿼리 성능을 줄일 수 있습니다. 3. 관절 지수에서는 쿼리를 최적화하기 위해 높은 카디널리티 시퀀스를 앞에 놓아야합니다.

MySQL : 신규 사용자를위한 리소스 및 튜토리얼MySQL : 신규 사용자를위한 리소스 및 튜토리얼Apr 14, 2025 am 12:16 AM

MySQL 학습 경로에는 기본 지식, 핵심 개념, 사용 예제 및 최적화 기술이 포함됩니다. 1) 테이블, 행, 열 및 SQL 쿼리와 같은 기본 개념을 이해합니다. 2) MySQL의 정의, 작업 원칙 및 장점을 배우십시오. 3) 인덱스 및 저장 절차와 같은 기본 CRUD 작업 및 고급 사용량을 마스터합니다. 4) 인덱스의 합리적 사용 및 최적화 쿼리와 같은 일반적인 오류 디버깅 및 성능 최적화 제안에 익숙합니다. 이 단계를 통해 MySQL의 사용 및 최적화를 완전히 파악할 수 있습니다.

실제 MySQL : 예 및 사용 사례실제 MySQL : 예 및 사용 사례Apr 14, 2025 am 12:15 AM

MySQL의 실제 응용 프로그램에는 기본 데이터베이스 설계 및 복잡한 쿼리 최적화가 포함됩니다. 1) 기본 사용 : 사용자 정보 삽입, 쿼리, 업데이트 및 삭제와 같은 사용자 데이터를 저장하고 관리하는 데 사용됩니다. 2) 고급 사용 : 전자 상거래 플랫폼의 주문 및 재고 관리와 같은 복잡한 비즈니스 로직을 처리합니다. 3) 성능 최적화 : 인덱스, 파티션 테이블 및 쿼리 캐시를 사용하여 합리적으로 성능을 향상시킵니다.

See all articles

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

더보기

인기 기사

R.E.P.O. 에너지 결정과 그들이하는 일 (노란색 크리스탈)
4 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. 최고의 그래픽 설정
4 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
어 ass 신 크리드 그림자 : 조개 수수께끼 솔루션
2 몇 주 전ByDDD
R.E.P.O. 아무도들을 수없는 경우 오디오를 수정하는 방법
4 몇 주 전By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25 : Myrise에서 모든 것을 잠금 해제하는 방법
1 몇 달 전By尊渡假赌尊渡假赌尊渡假赌
더보기

뜨거운 도구

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

안전한 시험 브라우저

안전한 시험 브라우저

안전한 시험 브라우저는 온라인 시험을 안전하게 치르기 위한 보안 브라우저 환경입니다. 이 소프트웨어는 모든 컴퓨터를 안전한 워크스테이션으로 바꿔줍니다. 이는 모든 유틸리티에 대한 액세스를 제어하고 학생들이 승인되지 않은 리소스를 사용하는 것을 방지합니다.

SublimeText3 Linux 새 버전

SublimeText3 Linux 새 버전

SublimeText3 Linux 최신 버전

맨티스BT

맨티스BT

Mantis는 제품 결함 추적을 돕기 위해 설계된 배포하기 쉬운 웹 기반 결함 추적 도구입니다. PHP, MySQL 및 웹 서버가 필요합니다. 데모 및 호스팅 서비스를 확인해 보세요.

WebStorm Mac 버전

WebStorm Mac 버전

유용한 JavaScript 개발 도구

더보기

뜨거운 주제

Gmail 이메일의 로그인 입구는 어디에 있나요?
7507
15
Cakephp 튜토리얼
1378
52
Steam의 계정 이름 형식은 무엇입니까?
78
11
Win11 활성화 키 영구
52
19
NYT 연결 힌트와 답변
19
55
더보기