表：
course表： student表：
student_course表：
tb_user表：
employee表：
1、查看执行频次、慢查询日志
#索引-性能分析-查看执行频次、
##SQL性能分析
##MYSQL客户端连接成功后，通过show[session|global] status ##命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
SHOW GLOBAL STATUS LIKE 'Com_______';
#删除、插入、更新、查询
show global status like 'Com_______';
##慢查询日志：(可以定位哪些SQL语句查询效率比较低，进行优化)慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位：秒，默认10秒）的所有SQL语句的日志。MYSQL的慢查询日志默认没有开启，需要在MYSQL的配置文件(/etc/my.cnf)中配置如下信息：
#查看慢查询日志
show variables like 'slow_query_log';
#开启MySQL慢查询日志开关
show_query_log=1
#设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
#配置完毕之后，通过以下指令重新启动MySQ服务器进行测试，查看慢查询日志文件中记录的信息 /var/lib/mysql/localhost-slow.log
在xshell中实践，环境xshell+VMware+xftp+mysql
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 0 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 15 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
11 rows in set (0.01 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 0 |
| Com_delete | 0 |
| Com_import | 0 |
| Com_insert | 0 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 18 |
| Com_signal | 0 |
| Com_update | 0 |
| Com_xa_end | 0 |
+---------------+-------+
11 rows in set (0.01 sec)
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'slow_query_log';
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id: 8
Current database: user
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
完成以上内容需要在/etc/my.cnf配置以下内容：
2、profile详情、explain执行计划
#SQL性能分析：
##profile详情：
/*
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作：
select @@have_profiling;(yes支持，no不支持)
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling;
SET profiling=1;
##可以通过以下语句查看prifiling是否打开(0是关闭，1是打开)
select @@profiling;
*/
#执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
tail -f master-slow.log#查看文件尾部输出的内容、
#SQL性能分析：
##explain执行计划:EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接顺序
#语法：
#直接在select语句之前加上关键字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件；
#EXPLAIN执行计划各字段含义：
#id:select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）。
#select_type:表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION (UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
#type:表示连接类型，性能由好到差的连接类型为NULL、system、const（唯一性的时候会出现）、eq_ref、ref(非唯一性的时候会出现）、range、index、all
#possible_key:显示可能应用在这张表上的索引，一个或多个
#Key:实际使用的索引，如果为NULL，则没有使用索引
#Key_len:表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越长越好
#rows:MySQL认为必须执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
#filtered:表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
在xshell中实践，环境xshell+VMware+xftp+mysql
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> select count(*) from tb_user;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.06 sec)
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from tb_user where id=1;
+----+--------+--------------+-----------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+--------------+-----------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
+----+--------+--------------+-----------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> select * from tb_user where name='赵云';
+----+--------+--------------+-------------------+------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+--------------+-------------------+------------+------+--------+--------+---------------------+
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
+----+--------+--------------+-------------------+------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> select count(*) from employee;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.06 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------+
| 1 | 0.00044600 | select @@profiling |
| 2 | 0.00272050 | select * from tb_user |
| 3 | 0.00135900 | select * from tb_user where id=1 |
| 4 | 0.00245550 | select * from tb_user where name='赵云' |
| 5 | 0.05557775 | select count(*) from employee |
+----------+------------+-------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 5;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000247 |
| Executing hook on transaction | 0.000047 |
| starting | 0.000018 |
| checking permissions | 0.000014 |
| Opening tables | 0.000395 |
| init | 0.000025 |
| System lock | 0.000019 |
| optimizing | 0.000011 |
| statistics | 0.000026 |
| preparing | 0.000028 |
| executing | 0.054452 |
| end | 0.000033 |
| query end | 0.000012 |
| waiting for handler commit | 0.000118 |
| closing tables | 0.000037 |
| freeing items | 0.000053 |
| cleaning up | 0.000043 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu for query 5;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000247 | 0.000127 | 0.000133 |
| Executing hook on transaction | 0.000047 | 0.000008 | 0.000008 |
| starting | 0.000018 | 0.000008 | 0.000009 |
| checking permissions | 0.000014 | 0.000007 | 0.000007 |
| Opening tables | 0.000395 | 0.000402 | 0.000000 |
| init | 0.000025 | 0.000019 | 0.000000 |
| System lock | 0.000019 | 0.000018 | 0.000000 |
| optimizing | 0.000011 | 0.000011 | 0.000000 |
| statistics | 0.000026 | 0.000027 | 0.000000 |
| preparing | 0.000028 | 0.000028 | 0.000000 |
| executing | 0.054452 | 0.053756 | 0.000000 |
| end | 0.000033 | 0.000022 | 0.000000 |
| query end | 0.000012 | 0.000012 | 0.000000 |
| waiting for handler commit | 0.000118 | 0.000123 | 0.000000 |
| closing tables | 0.000037 | 0.000032 | 0.000000 |
| freeing items | 0.000053 | 0.000053 | 0.000000 |
| cleaning up | 0.000043 | 0.000043 | 0.000000 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.00 sec)
mysql> select * from tb_user where id=1;
+----+--------+--------------+-----------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+--------+--------------+-----------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
+----+--------+--------------+-----------------+--------------+------+--------+--------+---------------------+
1 row in set (0.00 sec)
mysql> explain select * from tb_user where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_user |
+----------------+
| course |
| employee |
| student |
| student_course |
| tb_user |
+----------------+
5 rows in set (0.01 sec)
mysql> select * from course;
+----+--------+
| id | name |
+----+--------+
| 1 | Java |
| 2 | PHP |
| 3 | MySQL |
| 4 | Hadoop |
+----+--------+
4 rows in set (0.01 sec)
mysql> select * from student;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 几卷的 | 2000100101 |
| 2 | 第四代 | 2000100102 |
| 3 | 大凡讲 | 2000100103 |
| 4 | 非的 | 2000100104 |
| 5 | 电饭锅 | 2000100105 |
| 6 | 量子 | 2000100106 |
| 7 | 算法 | 2000100107 |
| 8 | 大润发 | 2000100108 |
+----+-----------+------------+
8 rows in set (0.00 sec)
mysql> select * from student_course;
+----+-----------+----------+
| id | studentid | courseid |
+----+-----------+----------+
| 1 | 1 | 4 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 4 |
+----+-----------+----------+
6 rows in set (0.00 sec)
mysql> select a.*,b.*,c.* from student a,course b,student_course c where a.id=c.studentid and b.id=c.courseid;
+----+-----------+------------+----+--------+----+-----------+----------+
| id | name | no | id | name | id | studentid | courseid |
+----+-----------+------------+----+--------+----+-----------+----------+
| 1 | 几卷的 | 2000100101 | 4 | Hadoop | 1 | 1 | 4 |
| 1 | 几卷的 | 2000100101 | 2 | PHP | 2 | 1 | 2 |
| 1 | 几卷的 | 2000100101 | 3 | MySQL | 3 | 1 | 3 |
| 2 | 第四代 | 2000100102 | 2 | PHP | 4 | 2 | 2 |
| 2 | 第四代 | 2000100102 | 3 | MySQL | 5 | 2 | 3 |
| 3 | 大凡讲 | 2000100103 | 4 | Hadoop | 6 | 3 | 4 |
+----+-----------+------------+----+--------+----+-----------+----------+
6 rows in set (0.00 sec)
mysql> explain select a.*,b.*,c.* from student a,course b,student_course c where a.id=c.studentid and b.id=c.courseid;
+----+-------------+-------+------------+--------+--------------------------+---------+---------+------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------+---------+---------+------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | c | NULL | ALL | fk_courseid,fk_studentid | NULL | NULL | NULL | 6 | 33.33 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | user.c.studentid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+--------------------------+---------+---------+------------------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain select a.*,b.*,c.* from student a,course b,student_course c where a.id=c.studentid and b.id=c.courseid;
+----+-------------+-------+------------+--------+--------------------------+---------+---------+------------------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+--------------------------+---------+---------+------------------+------+----------+--------------------------------------------+
| 1 | SIMPLE | b | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | SIMPLE | c | NULL | ALL | fk_courseid,fk_studentid | NULL | NULL | NULL | 6 | 33.33 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | user.c.studentid | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+--------------------------+---------+---------+------------------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> select a.* from student a,(select id from course where name='MySQL') b,student_course c where a.id=c.studentid and b.id=c.courseid;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 几卷的 | 2000100101 |
| 2 | 第四代 | 2000100102 |
+----+-----------+------------+
2 rows in set (0.00 sec)
#查询选修MySQL课程的学生信息
mysql> select * from student where id in(select studentid from student_course c where c.courseid=(select id from course b where b.name='MySQL'));
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 几卷的 | 2000100101 |
| 2 | 第四代 | 2000100102 |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> select * from student s where s.id in(select studentid from student_course sc where sc.courseid=(select id from course c where c.name='MySQL'));
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 几卷的 | 2000100101 |
| 2 | 第四代 | 2000100102 |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> select id from course where name='MySQL';
+----+
| id |
+----+
| 3 |
+----+
1 row in set (0.00 sec)
mysql> select studentid from student_course where courseid=3;
+-----------+
| studentid |
+-----------+
| 1 |
| 2 |
+-----------+
2 rows in set (0.00 sec)
mysql> select * from student where id in(1,2);
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 几卷的 | 2000100101 |
| 2 | 第四代 | 2000100102 |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> select a.* from student a,(select id from course where name='MySQL') b,student_course c where a.id=c.studentid and b.id=c.courseid;
+----+-----------+------------+
| id | name | no |
+----+-----------+------------+
| 1 | 几卷的 | 2000100101 |
| 2 | 第四代 | 2000100102 |
+----+-----------+------------+
2 rows in set (0.00 sec)
mysql> explain select a.* from student a,(select id from course where name='MySQL') b,student_course c where a.id=c.studentid and b.id=c.courseid;
+----+-------------+--------+------------+--------+--------------------------+-------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+--------------------------+-------------+---------+------------------+------+----------+-------------+
| 1 | SIMPLE | course | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 25.00 | Using where |
| 1 | SIMPLE | c | NULL | ref | fk_courseid,fk_studentid | fk_courseid | 4 | user.course.id | 2 | 100.00 | NULL |
| 1 | SIMPLE | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | user.c.studentid | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+--------------------------+-------------+---------+------------------+------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)
mysql> explain select * from student s where s.id in(select studentid from student_course sc where sc.courseid=(select id from course c where c.name='MySQL'));
+----+--------------+-------------+------------+--------+--------------------------+-------------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+--------+--------------------------+-------------+---------+-----------------------+------+----------+-------------+
| 1 | PRIMARY | <subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
| 1 | PRIMARY | s | NULL | eq_ref | PRIMARY | PRIMARY | 4 | <subquery2>.studentid | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | sc | NULL | ref | fk_courseid,fk_studentid | fk_courseid | 4 | const | 2 | 100.00 | Using where |
| 3 | SUBQUERY | c | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+--------------+-------------+------------+--------+--------------------------+-------------+---------+-----------------------+------+----------+-------------+
4 rows in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where id=1;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select 'A';
+---+
| A |
+---+
| A |
+---+
1 row in set (0.00 sec)
mysql> explain select 'A';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)
mysql> show index from tb_user;
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tb_user | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 1 | profession | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 2 | age | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_status | 3 | status | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_email | 1 | email | A | 5 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.01 sec)
mysql> select * from tb_user;
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| id | name | phone | email | profession | age | gender | status | createtime |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
| 1 | 吕布 | 177799990000 | lvbu666@163.com | 软件工程 | 23 | 1 | 6 | 2001-02-02 00:00:00 |
| 2 | 曹操 | 177799990001 | caocao666@qq.com | 应用数学 | 53 | 1 | 0 | 2001-04-03 00:00:00 |
| 3 | 赵云 | 177799990003 | 177799990@139.com | 英语 | 34 | 1 | 2 | 2002-03-05 00:00:00 |
| 4 | 兰陵王 | 177799990002 | lanlw666@126.com | 工程造价 | 44 | 1 | 1 | 2001-04-19 00:00:00 |
| 5 | 大乔 | 177799990005 | daqiao666@sina.com | 舞蹈 | 22 | 2 | 0 | 2001-02-07 00:00:00 |
+----+-----------+--------------+--------------------+--------------+------+--------+--------+---------------------+
5 rows in set (0.00 sec)
mysql> explain select * from tb_user where phone='177799990002';
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | const | idx_user_phone | idx_user_phone | 201 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tb_user where name='兰陵王';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | tb_user | NULL | ref | idx_user_name | idx_user_name | 201 | const | 1 | 100.00 | Using index condition |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
716
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
