mysql
mysql
数据库
发布日期:   2019-06-08
文章字数:   8.8k
阅读时长:   47 分
阅读次数:  

安装

下载

MySQL :: Download MySQL Community Server (Archived Versions)

1559999836333

rzsz

[root@192 modules]# yum -y  install lrzsz

上传

[root@192 modules]# ll
total 66632
-rw-r--r--. 1 root root 17855952 Jun  8 20:04 MySQL-client-5.5.48-1.linux2.6.x86_64.rpm
-rw-r--r--. 1 root root 50372369 Jun  8 20:24 MySQL-server-5.5.48-1.linux2.6.x86_64.rpm

卸载

方式一

[root@192 modules]# rpm -qa | grep -i mysql
mysql-libs-5.1.73-7.el6.x86_64
[root@192 modules]# rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64

方式二

[root@192 modules]# yum -y remove mysql-libs-5.1.73-7.el6.x86_64

如果提示“GPG keys...”安装失败，解决方案：rpm -ivh rpm软件名 --force --nodoeps

服务端

[root@192 modules]# rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm 
warning: MySQL-server-5.5.48-1.linux2.6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]
190608 20:32:37 [Note] /usr/sbin/mysqld (mysqld 5.5.48) starting as process 26375 ...
190608 20:32:37 [Note] /usr/sbin/mysqld (mysqld 5.5.48) starting as process 26382 ...

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h 192.168.1.101 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/

客户端

[root@192 modules]# rpm -ivh MySQL-client-5.5.48-1.linux2.6.x86_64.rpm 
warning: MySQL-client-5.5.48-1.linux2.6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]

验证

[root@192 mysql]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.5.48, for Linux on x86_64

启动服务

方式一

[root@192 mysql]# service mysql start;
Starting MySQL..                                           [  OK  ]

方式二

  • 在计算机reboot后 登陆MySQL : mysql可能会报错： "/var/lib/mysql/mysql.sock不存在"
    • 原因：是Mysql服务没有启动
    • 解决 ： 启动服务
      1. 每次使用前 手动启动服务 /etc/init.d/mysql start
      2. 开机自启 chkconfig mysql on ,chkconfig mysql off
[root@192 modules]# /etc/init.d/mysql start
Starting MySQL..                                           [  OK  ]

方式三

  • 开启自启mysql服务
[root@192 mysql]# chkconfig mysql on
[root@192 mysql]# chkconfig mysql --list
mysql              0:off    1:off    2:on    3:on    4:on    5:on    6:off

关闭服务

[root@192 mysql]# service mysql stop
Shutting down MySQL...                                     [  OK  ]

重启服务

[root@192 mysql]# service mysql restart;
Shutting down MySQL.                                       [  OK  ]
Starting MySQL..                                           [  OK  ]

检查开机是否自动启动mysql服务

[root@192 mysql]# ntsysv

1560149842747

修改密码

[root@192 modules]# /usr/bin/mysqladmin -u root password '000000'

连接

[root@192 modules]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.48 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

授权

mysql> grant all privileges  on *.* to root@'%' identified by "000000";
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

坑

  • 客户端连接不上

    1560004124502

    • 关闭防火墙

      [root@192 ~]# service iptables stop;
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]
[root@192 ~]# service iptables status;
iptables: Firewall is not running.

  • rpm安装mysql，找不到/etc/my.cnf

    • 复制/usr/share/mysql目录下的my-huge.cnf文件到/etc目录，并改名为my.cnf即可
[root@192 mysql]# cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

数据库存放目录

[root@192 mysql]# ps -ef|grep mysql
root       5790   3618  0 14:13 pts/1    00:00:00 mysql -uroot -p
root       5802   4605  0 14:24 pts/3    00:00:00 mysql -uroot -p
root       7266      1  0 14:46 pts/0    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/192.168.1.101.pid
mysql      7615   7266  0 14:46 pts/0    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/192.168.1.101.err --pid-file=/var/lib/mysql/192.168.1.101.pid --socket=/var/lib/mysql/mysql.sock --port=3306
root       7672   4519  0 15:02 pts/0    00:00:00 grep mysql
  • 数据库目录datadir=/var/lib/mysql
  • pid文件目录：--pid-file=/var/lib/mysql/bigdata01.pid

MySQL核心目录

  • /var/lib/mysql :mysql 安装目录
  • /usr/share/mysql: 配置文件
  • /usr/bin：命令目录（mysqladmin、mysqldump等）
  • /etc/init.d/mysql启停脚本

MySQL配置文件

  • my-huge.cnf 高端服务器 1-2G内存
  • my-large.cnf 中等规模
  • my-medium.cnf 一般
  • my-small.cnf 较小

但是，以上配置文件mysql默认不能识别，默认只能识别/etc/my.cnf，因此需要拷贝一份以上配置文件

[root@192 mysql]# cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

注意：mysql5.5默认配置文件/etc/my.cnf；Mysql5.6默认配置文件/etc/mysql-default.cnf

mysql字符编码

查看字符编码

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

安装NPPFTP

设置—导入—-导入插件

1560301765932

修改字符编码为utf8

编辑配置文件vim /etc/my.cnf，追加

[mysql]
default-character-set=utf8
[client]
default-character-set=utf8

[mysqld]
character_set_server=utf8
character_set_client=utf8
collation_server=utf8_general_ci

再次查看字符编码

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

清屏

  • Ctrl+L
  • system clear

SQL语句查询

步步深入：MySQL架构总览->查询执行流程->SQL解析顺序 - AnnsShadoW - 博客园

语法顺序

  1. SELECT
  2. FROM
  3. LEFT JOIN
  4. ON
  5. WHERE
  6. GROUP BY
  7. HAVING
  8. ORDER BY
  9. LIMIT

执行顺序

SELECT * FROM user LEFT JOIN order ON user.id = order.uid WHERE order.price > 1000 GROUP BY user.name HAVING count(1) > 5 ORDER BY user.name LIMIT 0,10
  1. FROM（将最近的两张表，进行笛卡尔积），得到临时结果集VT1
  2. ON（将VT1按照它的条件进行过滤）—VT2
  3. LEFT JOIN（保留左表的记录）—VT3
  4. WHERE（过滤VT3中的记录）–VT4…VTn
  5. GROUP BY（对VT4的记录进行分组）—VT5
  6. HAVING（对VT5中的记录进行过滤）—VT6
  7. SELECT（对VT6中的记录，选取指定的列）–VT7
  8. ORDER BY（对VT7的记录进行排序）–游标
  9. LIMIT（对排序之后的值进行分页）

WHERE条件执行顺序（影响性能）

  • MYSQL：从左往右去执行WHERE条件的。
  • Oracle：从右往左去执行WHERE条件的。

结论：写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句。

1560151970456

多表之间关系

一对一

一对多

从表是：分类表。从表中，应该有一个字段去关联主表，而这个关联字段就是主键。

主表是：商品表。主表中，应该有一个字段去关联从表，而这个关联字段就是外键。

1560045917048

在互联网项目中，一般情况下，不建议建立外键关系。

多对多

  • 双向的一对多

存储引擎

  • 存储引擎是针对表的,MySQL 5.5之后，默认的存储引擎由MyISAM变为InnoDB。

查询数据库支持哪些引擎


mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看当前使用的引擎

mysql> show variables like '%storage_engine%' ;
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

指定数据库对象的引擎

    create table tb(
        id int(4) auto_increment ,
        name varchar(5),
        dept varchar(5) ,
        primary key(id)        
    )ENGINE=MyISAM AUTO_INCREMENT=1
     DEFAULT CHARSET=utf8   ;

日志文件、数据文件

日志位置

mysql> SHOW  GLOBAL VARIABLES LIKE '%log%';
+-----------------------------------------+----------------------------------+
| Variable_name                           | Value                            |
+-----------------------------------------+----------------------------------+
| back_log                                | 50                               |
| binlog_cache_size                       | 32768                            |
| binlog_direct_non_transactional_updates | OFF                              |
| binlog_format                           | MIXED                            |
| binlog_stmt_cache_size                  | 32768                            |
| expire_logs_days                        | 0                                |
| general_log                             | OFF                              |
| general_log_file                        | /var/lib/mysql/192.log           |
| innodb_flush_log_at_trx_commit          | 1                                |
| innodb_locks_unsafe_for_binlog          | OFF                              |
| innodb_log_buffer_size                  | 8388608                          |
| innodb_log_file_size                    | 5242880                          |
| innodb_log_files_in_group               | 2                                |
| innodb_log_group_home_dir               | ./                               |
| innodb_mirrored_log_groups              | 1                                |
| log                                     | OFF                              |
| log_bin                                 | ON                               |
| log_bin_trust_function_creators         | OFF                              |
| log_error                               | /var/lib/mysql/192.168.1.101.err |
| log_output                              | FILE                             |
| log_queries_not_using_indexes           | OFF                              |
| log_slave_updates                       | OFF                              |
| log_slow_queries                        | OFF                              |
| log_warnings                            | 1                                |
| max_binlog_cache_size                   | 18446744073709547520             |
| max_binlog_size                         | 1073741824                       |
| max_binlog_stmt_cache_size              | 18446744073709547520             |
| max_relay_log_size                      | 0                                |
| relay_log                               |                                  |
| relay_log_index                         |                                  |
| relay_log_info_file                     | relay-log.info                   |
| relay_log_purge                         | ON                               |
| relay_log_recovery                      | OFF                              |
| relay_log_space_limit                   | 0                                |
| slow_query_log                          | OFF                              |
| slow_query_log_file                     | /var/lib/mysql/192-slow.log      |
| sql_log_bin                             | ON                               |
| sql_log_off                             | OFF                              |
| sync_binlog                             | 0                                |
| sync_relay_log                          | 0                                |
| sync_relay_log_info                     | 0                                |
+-----------------------------------------+----------------------------------+
41 rows in set (0.00 sec)

二进制日志

  • 记录了数据库所有的ddl语句和dml语句，但不包括select语句内容
  • 用于恢复数据
mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
2 rows in set (0.00 sec)

错误日志

mysql>  SHOW VARIABLES LIKE 'log_error%';
+---------------+----------------------------------+
| Variable_name | Value                            |
+---------------+----------------------------------+
| log_error     | /var/lib/mysql/192.168.1.101.err |
+---------------+----------------------------------+
1 row in set (0.00 sec)

查询日志

mysql>  SHOW VARIABLES LIKE 'general_log%';
+------------------+------------------------+
| Variable_name    | Value                  |
+------------------+------------------------+
| general_log      | OFF                    |
| general_log_file | /var/lib/mysql/192.log |
+------------------+------------------------+
2 rows in set (0.00 sec)

慢查询日志

mysql> SHOW VARIABLES LIKE 'slow_query_log%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                         |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

数据位置

mysql> SHOW VARIABLES LIKE '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

SQL优化

B-tree - Wikipedia

MySQL :: MySQL 5.5 Reference Manual :: 8 Optimization

MySQL索引背后的数据结构及算法原理

BTree和B+Tree详解

SQL优化， 主要就是 在优化索引，索引就 相当于书的目录

索引(index)是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树(默认)、Hash树…）

B树索引

  • select * from student where age = 33如果不加索引，从上往下查找需要查找5次，而加了索引，查找的节点处于第三层，所以只需要查找3次

1560241574572

B+Tree

  • Btree数据全部放在叶子节点

1560241687522

分类

  • 主键索引： 不能重复，是一种特殊的唯一索引，不允许有空值
  • 唯一索引 ：不能重复，允许为空值
  • 单值索引 ： 单列， age ;一个表可以多个单值索引,name。
  • 组合索引 ：多个列构成的索引 ，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

创建索引

如果一个字段是主键primary key，则该字段默认就是主键索引,需要注意的是DDL语句会自动提交,我们不需要手动commit

方式一

create 索引类型 索引名 on 表(字段)

  • 单值索引
create index   dept_index on  tb(dept);
  • 唯一索引
create unique index  name_index on tb(name) ;
  • 组合索引
create index dept_name_index on tb(dept,name);

方式二

alter table 表名 索引类型 索引名（字段）

  • 单值索引
alter table tb add index dept_index(dept) ;
  • 唯一索引
alter table tb add unique index name_index(name);
  • 组合索引
alter table tb add index dept_name_index(dept,name);

删除索引

drop index 索引名 on 表名 ;

drop index name_index on tb ;

查询索引

  • show index from 表名 ;
  • show index from 表名 \G
mysql> show index from tb;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tb    |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tb    |          1 | dept_index |            1 | dept        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

优势

  • 提高查询效率（降低IO使用率）
  • 降低CPU使用率 （...order by age desc,因为 B树索引 本身就是一个 好排序的结构，因此在排序时 可以直接使用）

弊端

  • 索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
  • 索引不是所有情况均适用
    • 少量数据
    • 频繁更新的字段
    • 很少使用的字段
  • 索引会降低增删改的效率（增删改 查）

建议

  • 尽量创建组合索引（组合索引其实会默认按照最左前缀原则帮我们创建多组索引）
  • 组合索引（id,name,sex）会帮我们创建多组索引—–>[id],[id,name],[id,sex],[id,name,sex],[name,sex]

SQL故障排除

  • 慢查询日志:MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阀值的SQL语句 （long_query_time，默认10秒）
  • 慢查询日志默认是关闭的；建议一般开发调优时是打开，而最终部署时关闭。
  • 性能优化思路
    • 首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
    • 其次使用explain命令去查看有问题的SQL的执行计划
    • 最后可以使用show profile[s] 查看有问题的SQL的性能使用情况

查看慢查询

临时开启

方式一

mysql> show variables like '%slow_query_log%' ;
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                         |
| slow_query_log_file | /var/lib/mysql/192-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_log%' ;
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | ON                          |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

方式二

mysql> show variables like '%slow_query_log%' ;
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | OFF                         |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

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

mysql> show variables like '%slow_query_log%' ;
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_query_log      | ON                          |
| slow_query_log_file | /var/lib/mysql/192-slow.log |
+---------------------+-----------------------------+
2 rows in set (0.00 sec)

永久开启

编辑/etc/my.cnf 中追加配置：

vi /etc/my.cnf 
[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

查看慢查询阀值

mysql> show variables like '%long_query_time%' ;
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

临时设置阀值

  • 设置完毕后，重新登陆后起效 （不需要重启服务）
mysql> show variables like '%long_query_time%' ;
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

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

mysql> show variables like '%long_query_time%' ;
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> exit;
Bye
[root@192 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.48-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%long_query_time%' ;
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

永久设置阀值

编辑/etc/my.cnf中追加配置：

vi /etc/my.cnf 
[mysqld]
long_query_time=3

查看SQL执行计划

explain +SQL语句

mysql> explain  select  * from tb ;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
| id | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | Extra               |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
|  1 | SIMPLE      | tb    | system | NULL          | NULL | NULL    | NULL |    0 | const row not found |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------+
1 row in set (0.00 sec)
  • id : 编号
  • select_type ：查询类型
  • table ：表
  • type ：类型
  • possible_keys ：预测用到的索引
  • key ：实际使用的索引
  • key_len ：实际使用索引的长度
  • ref :表之间的引用
  • rows ：通过索引查询到的数据量
  • Extra :额外的信息

表结构

  • 课程表(course)
  • 教师表(teacher)
  • 教师课程描述表(teacherCard)

1560245341475

准备数据

create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);


insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc') ;
insert into teacherCard values(2,'twdesc') ;
insert into teacherCard values(3,'tldesc') ;

id

id值相同

  • id值相同，从上往下 顺序执行。

  • 数据小的表优先查询

  • 用wehre连接表之间的关系，表的执行顺序是因数量的个数改变而改变，当教师表新插入3条数据，教师表变成了最后执行的原因是遵循笛卡儿积，尽管3*4*6=72和6*4*3=72最终的条数是一样的，但是它们的中间结果是不一样的，查询3*4=12条效率明显比6*4=24高

查询课程编号为2或教师证编号为3的老师信息


mysql> EXPLAIN select * from teacher t,course c,teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)

mysql> insert into teacher values(4,'ts',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values(5,'tw',3);
Query OK, 1 row affected (0.01 sec)

mysql> insert into teacher values(6,'tl',3);
Query OK, 1 row affected (0.00 sec)

mysql> EXPLAIN select * from teacher t,course c,teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)

id值不同

  • id值越大越优先查询 (本质上在嵌套子查询时，先查内层 再查外层)

查询教授SQL课程的老师的描述（desc）

mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
    -> and t.tcid = tc.tcid and c.cname = 'sql' ;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using join buffer |
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.01 sec)

mysql> explain select tc.tcdesc from teacherCard tc where tc.tcid = 
    -> (select t.tcid from teacher t where  t.tid =  
    -> (select c.tid from course c where c.cname = 'sql')
    -> );
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
|  2 | SUBQUERY    | t     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
|  3 | SUBQUERY    | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)

id值有相同，又有不同

  • id值越大越优先；id值相同，从上往下 顺序执行

子查询+多表

mysql> explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid
    -> and t.tid = (select c.tid from course c where cname = 'sql') ;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | PRIMARY     | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | PRIMARY     | t     | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where; Using join buffer |
|  2 | SUBQUERY    | c     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where                    |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
3 rows in set (0.00 sec)

select_type

查询类型

  • PRIMARY:包含子查询SQL中的 主查询 （最外层）

  • SUBQUERY：包含子查询SQL中的 子查询 （非最外层）

  • simple:简单查询（不包含子查询、union）

  • derived:衍生查询(使用到了临时表)

    • 在from子查询中只有一张表

      mysql> explain select  cr.cname from ( select * from course where tid in (1,2) ) cr ;
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
|  2 | DERIVED     | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

  • 在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union

    mysql> explain select  cr.cname from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;
+----+--------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |    3 |             |
|  2 | DERIVED      | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
|  3 | UNION        | course     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
| NULL | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+----+--------------+------------+------+---------------+------+---------+------+------+-------------+
4 rows in set (0.00 sec)

  • union:上例

  • union result :告知开发人员，那些表之间存在union查询

type

索引类型、类型

  • 对type进行优化的前提：有索引

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

比较常见的system>const>eq_ref>ref>range>index>all,其中system,const只是理想情况；实际能达到 的是ref>range

system

  • 可以忽略: 只有一条数据的系统表 ；或 衍生表只有一条数据的主查询

    mysql> create table test01
    -> (
    -> tid int(3),
    -> tname varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> insert into test01 values(1,'a') ;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_kkb |
+---------------+
| course        |
| lock          |
| tb            |
| teacher       |
| teacherCard   |
| test01        |
| user          |
+---------------+
7 rows in set (0.00 sec)

mysql> alter table test01 add constraint tid_pk primary key(tid) ;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select * from (select * from test01 )t where tid =1 ;
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL          | NULL | NULL    | NULL |    1 |       |
|  2 | DERIVED     | test01     | ALL    | NULL          | NULL | NULL    | NULL |    1 |       |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

    const

    仅仅能查到一条数据的SQL ,用于Primary key或unique索引 （类型 与索引类型有关）

    
mysql> explain select tid from test01 where tid =1 ;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | test01 | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> alter table test01 drop primary key ;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> explain select tid from test01 where tid =1 ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test01 | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> create index test01_index on test01(tid) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select tid from test01 where tid =1 ;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test01 | ref  | test01_index  | test01_index | 4       | const |    1 | Using index |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

    
#### eq_ref

唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）

```sql
mysql> select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
+------+-------+------+
3 rows in set (0.00 sec)

mysql> select * from teacherCard;
+------+--------+
| tcid | tcdesc |
+------+--------+
|    1 | tzdesc |
|    2 | twdesc |
|    3 | tldesc |
+------+--------+
3 rows in set (0.00 sec)

mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                          |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
|  1 | SIMPLE      | t     | ALL  | NULL          | NULL | NULL    | NULL |    3 |                                |
|  1 | SIMPLE      | tc    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+
2 rows in set (0.00 sec)

mysql> alter table teacherCard add constraint pk_tcid primary key(tcid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table teacher add constraint uk_tcid unique index(tcid) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select t.tcid from teacher t,teacherCard tc where t.tcid = tc.tcid ;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | SIMPLE      | t     | index  | uk_tcid       | uk_tcid | 5       | NULL       |    3 | Using index |
|  1 | SIMPLE      | tc    | eq_ref | PRIMARY       | PRIMARY | 4       | kkb.t.tcid |    1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+
2 rows in set (0.00 sec)


  以上`SQL`，用到的索引是 `t.tcid`,即`teacher`表中的`tcid`字段；
  如果`teacher`表的数据个数 和 连接查询的数据个数一致（都是3条数据），则有可能满足`eq_ref`级别；否则无法满足。

#### ref

非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0行或者多行）

```sql
mysql> insert into teacher values(4,'tz',4) ;
Query OK, 1 row affected (0.00 sec)

mysql>  insert into teacherCard values(4,'tz222');
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
|    4 | tz    |    4 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from teacherCard;
+------+--------+
| tcid | tcdesc |
+------+--------+
|    1 | tzdesc |
|    2 | twdesc |
|    3 | tldesc |
|    4 | tz222  |
+------+--------+
4 rows in set (0.00 sec)

mysql> alter table teacher add index index_name (tname) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from teacher where tname = 'tz';
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | teacher | ref  | index_name    | index_name | 63      | const |    2 | Using where |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

range

检索指定范围的行 ,where后面是一个范围查询

  • between
  • < >=
  • 特殊的情况，in有时候会失效 ，从而转为 无索引，进行全表扫描all)
mysql> alter table teacher add index tid_index (tid) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
|    4 | tz    |    4 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> show index from teacher;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | uk_tcid    |            1 | tcid        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | index_name |            1 | tname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | tid_index  |            1 | tid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select t.* from teacher t where t.tid in (1,2) ;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | ALL  | tid_index     | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select t.* from teacher t where t.tid <3 ;
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | t     | range | tid_index     | tid_index | 5       | NULL |    1 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

index

查询全部索引中数据，tid 是索引， 只需要扫描索引表，不需要所有表中的所有数据

mysql> show index from teacher;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | uk_tcid    |            1 | tcid        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | index_name |            1 | tname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | tid_index  |            1 | tid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
|    4 | tz    |    4 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> explain select tid from teacher;
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | teacher | index | NULL          | tid_index | 5       | NULL |    4 | Using index |
+----+-------------+---------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

all

查询全部表中的数据

mysql> show index from course;
Empty set (0.00 sec)

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
|    1 | java  |    1 |
|    2 | html  |    1 |
|    3 | sql   |    2 |
|    4 | web   |    3 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> explain select * from course;
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | course | ALL  | NULL          | NULL | NULL    | NULL |    4 |       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

possible_keys

可能用到的索引，是一种预测，不准

mysql> alter table  course add index cname_index (cname);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
|    1 | java  |    1 |
|    2 | html  |    1 |
|    3 | sql   |    2 |
|    4 | web   |    3 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> show index from course;
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| course |          1 | cname_index |            1 | cname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from teacher;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | uk_tcid    |            1 | tcid        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | index_name |            1 | tname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | tid_index  |            1 | tid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> show index from teacherCard;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacherCard |          0 | PRIMARY  |            1 | tcid        | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc
    ->  where t.tcid= tc.tcid
    -> and t.tid = (select c.tid from course c where cname = 'sql') ;
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys     | key         | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-------------+
|  1 | PRIMARY     | t     | ref    | uk_tcid,tid_index | tid_index   | 5       | const      |    1 | Using where |
|  1 | PRIMARY     | tc    | eq_ref | PRIMARY           | PRIMARY     | 4       | kkb.t.tcid |    1 |             |
|  2 | SUBQUERY    | c     | ref    | cname_index       | cname_index | 63      |            |    1 | Using where |
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-------------+
3 rows in set (0.00 sec)

如果 possible_key/key是NULL，则说明没用索引

mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid
    -> and t.tcid = tc.tcid and c.cname = 'sql' ;
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-------------+
| id | select_type | table | type   | possible_keys     | key         | key_len | ref        | rows | Extra       |
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-------------+
|  1 | SIMPLE      | c     | ref    | cname_index       | cname_index | 63      | const      |    1 | Using where |
|  1 | SIMPLE      | t     | ref    | uk_tcid,tid_index | tid_index   | 5       | kkb.c.tid  |    1 | Using where |
|  1 | SIMPLE      | tc    | eq_ref | PRIMARY           | PRIMARY     | 4       | kkb.t.tcid |    1 |             |
+----+-------------+-------+--------+-------------------+-------------+---------+------------+------+-------------+
3 rows in set (0.00 sec)

mysql> drop index cname_index on course;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql';
+----+-------------+-------+--------+-------------------+---------+---------+------------+------+--------------------------------+
| id | select_type | table | type   | possible_keys     | key     | key_len | ref        | rows | Extra                          |
+----+-------------+-------+--------+-------------------+---------+---------+------------+------+--------------------------------+
|  1 | SIMPLE      | c     | ALL    | NULL              | NULL    | NULL    | NULL       |    4 | Using where                    |
|  1 | SIMPLE      | t     | ALL    | uk_tcid,tid_index | NULL    | NULL    | NULL       |    4 | Using where; Using join buffer |
|  1 | SIMPLE      | tc    | eq_ref | PRIMARY           | PRIMARY | 4       | kkb.t.tcid |    1 |                                |
+----+-------------+-------+--------+-------------------+---------+---------+------------+------+--------------------------------+
3 rows in set (0.00 sec)

key

实际使用到的索引

key_len

  • utf8:1个字符3个字节
  • gbk:1个字符2个字节
  • latin:1个字符1个字节

索引的长度, 用于判断复合索引是否被完全使用,key_len为60，在utf8：1个字符占3个字节

mysql> create table test_kl
    -> (
    -> name char(20) not null default ''
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test_kl add index index_name(name) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_kl where name ='' ; 
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_kl | ref  | index_name    | index_name | 60      | const |    1 | Using where; Using index |
+----+-------------+---------+------+---------------+------------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)

如果索引字段可以为Null,则会使用1个字节用于标识。因此key_len为61

mysql> alter table test_kl add column name1 char(20) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_kl add index index_name1(name1) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_kl where name1 ='' ; 
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_kl | ref  | index_name1   | index_name1 | 61      | const |    1 | Using where |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

增加一个复合索引 ,根据最左前缀原则，两次key_len分别为121和60

mysql> drop index index_name on test_kl ;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index index_name1 on test_kl ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_kl add index name_name1_index (name,name1) ; 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test_kl;
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_kl |          1 | name_name1_index |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test_kl |          1 | name_name1_index |            2 | name1       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select * from test_kl where name1 = '' ;
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table   | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test_kl | index | NULL          | name_name1_index | 121     | NULL |    1 | Using where; Using index |
+----+-------------+---------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select * from test_kl where name = '' ;
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
| id | select_type | table   | type | possible_keys    | key              | key_len | ref   | rows | Extra                    |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test_kl | ref  | name_name1_index | name_name1_index | 60      | const |    1 | Using where; Using index |
+----+-------------+---------+------+------------------+------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

索引字段为可变varchar类型,用2个字节 标识可变长度,1字节标识Null,因此20*3=60 + 1(null) +2(用2个字节 标识可变长度) =63

mysql> alter table test_kl add column name2 varchar(20) ; 
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_kl add index name2_index (name2) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test_kl where name2 = '' ;
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | test_kl | ref  | name2_index   | name2_index | 63      | const |    1 | Using where |
+----+-------------+---------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

ref

表之间的引用，注意与type中的ref值区分

指明当前表所参照的字段select ....where a.c = b.x ;(其中b.x可以是常量，const)

mysql> desc course;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid   | int(3)      | YES  |     | NULL    |       |
| cname | varchar(20) | YES  |     | NULL    |       |
| tid   | int(3)      | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table course  add index tid_index (tid) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from course c,teacher t where c.tid = t.tid  and t.tname ='tw' ;
+----+-------------+-------+------+----------------------+------------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys        | key        | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+----------------------+------------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t     | ref  | index_name,tid_index | index_name | 63      | const     |    1 | Using where |
|  1 | SIMPLE      | c     | ref  | tid_index            | tid_index  | 5       | kkb.t.tid |    1 | Using where |
+----+-------------+-------+------+----------------------+------------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

rows

被索引优化查询的 数据个数 (实际通过索引而查询到的 数据个数)

mysql> select * from course;
+------+-------+------+
| cid  | cname | tid  |
+------+-------+------+
|    1 | java  |    1 |
|    2 | html  |    1 |
|    3 | sql   |    2 |
|    4 | web   |    3 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> select * from teacher;
+------+-------+------+
| tid  | tname | tcid |
+------+-------+------+
|    1 | tz    |    1 |
|    2 | tw    |    2 |
|    3 | tl    |    3 |
|    4 | tz    |    4 |
+------+-------+------+
4 rows in set (0.00 sec)

mysql> show index from course;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| course |          1 | tid_index |            1 | tid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show index from teacher;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | uk_tcid    |            1 | tcid        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | index_name |            1 | tname       | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | tid_index  |            1 | tid         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from course c,teacher t  where c.tid = t.tid and t.tname = 'tz';
+------+-------+------+------+-------+------+
| cid  | cname | tid  | tid  | tname | tcid |
+------+-------+------+------+-------+------+
|    1 | java  |    1 |    1 | tz    |    1 |
|    2 | html  |    1 |    1 | tz    |    1 |
+------+-------+------+------+-------+------+
2 rows in set (0.00 sec)

mysql> explain select * from course c,teacher t  where c.tid = t.tid and t.tname = 'tz';
+----+-------------+-------+------+----------------------+------------+---------+-----------+------+-------------+
| id | select_type | table | type | possible_keys        | key        | key_len | ref       | rows | Extra       |
+----+-------------+-------+------+----------------------+------------+---------+-----------+------+-------------+
|  1 | SIMPLE      | t     | ref  | index_name,tid_index | index_name | 63      | const     |    2 | Using where |
|  1 | SIMPLE      | c     | ref  | tid_index            | tid_index  | 5       | kkb.t.tid |    1 | Using where |
+----+-------------+-------+------+----------------------+------------+---------+-----------+------+-------------+
2 rows in set (0.00 sec)

Extra

using filesort

using filesort ： 性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。

对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；，平常使用的话，where哪些字段，就order by那些字段

mysql> create table test02
    -> (
    -> a1 char(3),
    -> a2 char(3),
    -> a3 char(3),
    -> index idx_a1(a1),
    -> index idx_a2(a2),
    -> index idx_a3(a3)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> select * from test02 where a1 ='' order by a1 ;
Empty set (0.00 sec)

mysql> explain select * from test02 where a1 ='' order by a1 ;
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1        | idx_a1 | 10      | const |    1 | Using where |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select * from test02 where a1 ='' order by a2 ;
+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1        | idx_a1 | 10      | const |    1 | Using where; Using filesort |
+----+-------------+--------+------+---------------+--------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

复合索引：不能跨列（最佳左前缀），where和order by按照复合索引的顺序使用，不要跨列或无序使用。

mysql> drop index idx_a1 on test02;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_a2 on test02;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index idx_a3 on test02;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from test02;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test02 |          1 | idx_a1_a2_a3 |            1 | a1          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test02 |          1 | idx_a1_a2_a3 |            2 | a2          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test02 |          1 | idx_a1_a2_a3 |            3 | a3          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select *from test02 where a1='' order by a3 ;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra                                    |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1_a2_a3  | idx_a1_a2_a3 | 10      | const |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select *from test02 where a2='' order by a3 ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | test02 | index | NULL          | idx_a1_a2_a3 | 30      | NULL |    1 | Using where; Using index; Using filesort |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> explain select *from test02 where a1='' order by a2 ;
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key          | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test02 | ref  | idx_a1_a2_a3  | idx_a1_a2_a3 | 10      | const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+--------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select *from test02 where a2='' order by a1 ; 
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test02 | index | NULL          | idx_a1_a2_a3 | 30      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

using temporary

using temporary:性能损耗大 ，用到了临时表。一般出现在group by 语句中。

查询那些列，就根据那些列 group by

mysql> explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test02 | index | idx_a1_a2_a3  | idx_a1_a2_a3 | 30      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select a1 from test02 where a1 in ('1','2','3') group by a2 ;
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                                                     |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | test02 | index | idx_a1_a2_a3  | idx_a1_a2_a3 | 30      | NULL |    1 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

using index

性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）

mysql> show index from test02;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test02 |          1 | idx_a1_a2_a3 |            1 | a1          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test02 |          1 | idx_a1_a2_a3 |            2 | a2          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| test02 |          1 | idx_a1_a2_a3 |            3 | a3          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> explain select a1,a2 from test02 where a1='' or a2= '' ; 
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key          | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test02 | index | idx_a1_a2_a3  | idx_a1_a2_a3 | 30      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+--------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> drop index idx_a1_a2_a3 on test02;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test02 add index idx_a1_a2(a1,a2) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select a1,a3 from test02 where a1='' or a3= '' ;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test02 | ALL  | idx_a1_a2     | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

如果用到了索引覆盖(using index时)，会对possible_keys和key造成影响：

如果没有where，则索引只出现在key中；如果有where，则索引 出现在key和possible_keys中。

mysql> explain select a1,a2 from test02 where a1='' or a2= '' ;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test02 | index | idx_a1_a2     | idx_a1_a2 | 20      | NULL |    1 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> 
mysql> explain select a1,a2 from test02  ;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
|  1 | SIMPLE      | test02 | index | NULL          | idx_a1_a2 | 20      | NULL |    1 | Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

查看SQL性能使用情况

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

mysql> set profiling=1; 
Query OK, 0 rows affected (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> select sleep(20);
+-----------+
| sleep(20) |
+-----------+
|         0 |
+-----------+
1 row in set (20.00 sec)

mysql> show profiles;
+----------+-------------+--------------------+
| Query_ID | Duration    | Query              |
+----------+-------------+--------------------+
|        1 |  0.00007700 | select @@profiling |
|        2 | 20.00080200 | select sleep(20)   |
+----------+-------------+--------------------+
2 rows in set (0.00 sec)

mysql> show profile;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000033 |
| checking permissions |  0.000002 |
| Opening tables       |  0.000004 |
| init                 |  0.000006 |
| optimizing           |  0.000002 |
| executing            |  0.000006 |
| User sleep           | 20.000621 |
| end                  |  0.000012 |
| query end            |  0.000002 |
| closing tables       |  0.000002 |
| freeing items        |  0.000042 |
| logging slow query   |  0.000003 |
| logging slow query   |  0.000065 |
| cleaning up          |  0.000004 |
+----------------------+-----------+
14 rows in set (0.00 sec)

mysql> show profile cpu,swaps for query 2;
+----------------------+-----------+----------+------------+-------+
| Status               | Duration  | CPU_user | CPU_system | Swaps |
+----------------------+-----------+----------+------------+-------+
| starting             |  0.000033 | 0.000000 |   0.000000 |     0 |
| checking permissions |  0.000002 | 0.000000 |   0.000000 |     0 |
| Opening tables       |  0.000004 | 0.000000 |   0.000000 |     0 |
| init                 |  0.000006 | 0.000000 |   0.000000 |     0 |
| optimizing           |  0.000002 | 0.000000 |   0.000000 |     0 |
| executing            |  0.000006 | 0.000000 |   0.000000 |     0 |
| User sleep           | 20.000621 | 0.006000 |   0.005999 |     0 |
| end                  |  0.000012 | 0.000000 |   0.000000 |     0 |
| query end            |  0.000002 | 0.000000 |   0.000000 |     0 |
| closing tables       |  0.000002 | 0.000000 |   0.000000 |     0 |
| freeing items        |  0.000042 | 0.000000 |   0.000000 |     0 |
| logging slow query   |  0.000003 | 0.000000 |   0.000000 |     0 |
| logging slow query   |  0.000065 | 0.000000 |   0.000000 |     0 |
| cleaning up          |  0.000004 | 0.000000 |   0.000000 |     0 |
+----------------------+-----------+----------+------------+-------+
14 rows in set (0.00 sec)

参考

我以为我对Mysql索引很了解，直到我遇到了阿里的面试官-HollisChuang’s Blog

赏

   转载规则


《mysql》 由 shenlibing 采用 知识共享署名 4.0 国际许可协议 进行许可。
  目录