4、索引语法
创建索引:
create [unique|fulltext] index index_name on table_name (index_col_name,....);
查看索引:
show index from table_name;
删除索引:
drop index index_name on table_name;
练习:
- name字段为姓名字段,该字段的值可能会重复,为该字段创建索引。
- phone手机号字段的值,是非空,且唯一的,为该字段创建唯一索引。
- 为profession、age、status创建联合索引。
- 为email建立合适的索引来提升查询效率。
mysql> create index idx_user_name on tb_user(name);
mysql> create unique index idx_user_phone on tb_user(phone);
mysql> create index idx_user_pro_age_sta on tb_user(profession,age,status);
mysql> create index idx_user_email on tb_user(email);
# 结果
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 | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| tb_user | 0 | idx_user_phone | 1 | phone | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_name | 1 | name | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 1 | profession | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 2 | age | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_pro_age_sta | 3 | status | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
| tb_user | 1 | idx_user_email | 1 | email | A | 1 | NULL | NULL | YES | BTREE | | | YES | NULL |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.03 sec)
5、SQL性能分析
查看执行频次:
mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog | 0 |
| Com_commit | 8 |
| Com_delete | 2 |
| Com_import | 0 |
| Com_insert | 30 |
| Com_repair | 0 |
| Com_revoke | 0 |
| Com_select | 1017 |
| Com_signal | 0 |
| Com_update | 22 |
| Com_xa_end | 0 |
+---------------+-------+
11 rows in set (0.05 sec)
慢查询日志:
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.01 sec)
开启慢查询:
# 慢查询日志开启
slow_query_log = 1
long_query_time = 2
重新启动mysqld服务
mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
1 row in set (0.01 sec)
profile详情:
show profiles 能够在做SQL优化时帮助我们了解事件都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作:
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.00 sec)
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
set profiling = 1;
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.01 sec)
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
# 查看每一条SQL的耗时基本情况
show profiles;
# 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id 的SQL语句CPU的使用情况
show profile cpu for query query_id;
mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 1 | 0.00021200 | select @@profiling |
| 2 | 0.00012275 | select * from tb_user |
| 3 | 0.00023375 | SELECT DATABASE() |
| 4 | 0.00392925 | show databases |
| 5 | 0.00291425 | show tables |
| 6 | 0.00038200 | select * from tb_user |
| 9 | 0.00059875 | select * from tb_user where name = '吕布' |
| 10 | 0.00048100 | select * from tb_user where id = 1 |
| 11 | 0.04580075 | select count(*) from tb_user |
+----------+------------+-------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 11;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000105 |
| Executing hook on transaction | 0.000005 |
| starting | 0.000011 |
| checking permissions | 0.000009 |
| Opening tables | 0.000055 |
| init | 0.000007 |
| System lock | 0.000011 |
| optimizing | 0.000009 |
| statistics | 0.000023 |
| preparing | 0.000025 |
| executing | 0.045451 |
| end | 0.000018 |
| query end | 0.000006 |
| waiting for handler commit | 0.000013 |
| closing tables | 0.000012 |
| freeing items | 0.000027 |
| cleaning up | 0.000016 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu for query 11;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000105 | 0.000055 | 0.000043 |
| Executing hook on transaction | 0.000005 | 0.000003 | 0.000002 |
| starting | 0.000011 | 0.000006 | 0.000005 |
| checking permissions | 0.000009 | 0.000005 | 0.000003 |
| Opening tables | 0.000055 | 0.000031 | 0.000024 |
| init | 0.000007 | 0.000003 | 0.000003 |
| System lock | 0.000011 | 0.000007 | 0.000005 |
| optimizing | 0.000009 | 0.000005 | 0.000004 |
| statistics | 0.000023 | 0.000013 | 0.000010 |
| preparing | 0.000025 | 0.000014 | 0.000011 |
| executing | 0.045451 | 0.044543 | 0.000000 |
| end | 0.000018 | 0.000011 | 0.000000 |
| query end | 0.000006 | 0.000006 | 0.000000 |
| waiting for handler commit | 0.000013 | 0.000012 | 0.000000 |
| closing tables | 0.000012 | 0.000013 | 0.000000 |
| freeing items | 0.000027 | 0.000026 | 0.000000 |
| cleaning up | 0.000016 | 0.000017 | 0.000000 |
+--------------------------------+----------+----------+------------+
17 rows in set, 1 warning (0.00 sec)
explain执行计划:
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接顺序。
语法:
# 直接在select语句之前加上关键字explain/desc
EXPLAIN select 字段列表 from 表名 where 条件;
测试:
mysql> explain select * from tb_user;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tb_user | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
测试一个多表查询:
mysql> explain select * from student s,sc,course where s.sno = sc.sno and course.cno = sc.cno;
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
| 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 | 100.00 | NULL |
| 1 | SIMPLE | sc | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | s | NULL | eq_ref | PRIMARY | PRIMARY | 40 | gradem.sc.sno | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql> explain select * from student where student.sno in (select sc.sno from sc where sc.cno = (select cno from course where cname = "数据库"));
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------+
| 1 | PRIMARY | student | NULL | ALL | PRIMARY | NULL | NULL | NULL | 4 | 100.00 | NULL |
| 1 | PRIMARY | sc | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; FirstMatch(student); Using join buffer (Block Nested Loop) |
| 3 | SUBQUERY | course | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
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_keys | 显示可能应用在这张表上的索引,一个或多个 |
key | 实际使用的索引,如果为null,则没有使用索引 |
key_len | 表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好 |
rows | MySQL认为必须要执行查询的行数,在InnoDB引擎的表中,是一个估计值,可能并不总是准确的 |
filtered | 返回结果的行数占需读取行数的百分比,filtered的值越大越好 |
ref | |
Extra |