mysql索引与存储引擎-xdclass
2020年11月24日21:23:06-2020年11月28日19:41:00 by ddhhdd
01 mysql索引与存储引擎
1.1 mysql的存储引擎
（1）什么是数据库存储引擎？
数据库引擎是数据库底层软件组件，不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，使用不同的数据库引擎，可以获得特定的功能。
（2）如何查看引擎？
# 如何查看数据库支持的引擎
show engines;
# 查看当前数据库中表的引擎
show create table 表名\G
# 查看当前库所有表的引擎
show table status\G
# mysql的引擎可不止MyISAM和InnoDB
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| 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)
（3）建表时指定引擎
create table yingqin (id int,name varchar(20)) engine='InnoDB';
（4）修改表的引擎
alter table 表名 engine='MyISAM';
# 修改默认引擎
• vi /etc/my.cnf
• [mysqld]下面
• default-storage-engine=MyISAM
• 记得保存后重启服务
（5）MyISAM与InnoDB的区别
-
MyISAM：支持全文索引；不支持事务；表级锁；保存表的具体行数（即表总共有多少行）；奔溃恢复不好； -
InnoDB：支持事务；行级锁；不保存表的具体行数；奔溃恢复好；
note：行级锁效率要远高于表级锁；InnoDB以前的版本是不支持全文索引，但在5.6之后的版本就开始支持这个功能了，但功能不全；InnoDB并非绝对是行级锁，当执行sql语句时不能确定范围时，也会进行锁全表例如：update table set id=3 where name like 'a%';
（6）总结：什么时候选择什么引擎比较好
MyISAM：
- 一般来说MyISAM不需要用到事务的时候；
- 做很多count计算；
InnoDB：
- 可靠性要求高的，或者要求支持事务；
- 想要用到外键约束的时候；
推荐：
- 推荐用InnoDB
1.2 mysql索引
（1）什么是索引？
索引是一个单独的，存储在磁盘中上的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。
note：索引相当于书的目录，没有索引，则需要遍历整张表。
（2）索引的优点
- 通过创建唯一索引，来保证数据库表中的每一行数据的唯一性；
- 可以加快数据的检索速度；
- 可以保证表数据的完整性与准确性；
（3）索引的缺点
- 索引需要占用物理空间；
- 对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度；
（4）常见的索引类型
index：普通索引unique：唯一索引primary key：主键索引foreign key：外键索引full text：全文索引- 组合索引
（5）创建表的sql语句
create table test (
id int(7) zerofill auto_increment not null,
username varchar(20),
servnumber varchar(30),
password varchar(20),
createtime datetime,
primary key (id)
)DEFAULT CHARSET=utf8;
（6）生成百万甚至千万级别表的sql语句shell脚本
#!/bin/bash
echo "请输入字段servnumber的值："
read serber
echo "请输入创建sql语句的数量："
read number
# char=`head /dev/urandom | tr -dc 0-9 | head -c 11`
for (( i=0;i<$number;i++ ))
do
pass=`head /dev/urandom | tr -dc a-z | head -c 8`
let serber=serber+1
echo "insert into test(id,username,servnumber,password,createtime)
values('$i','user${i}','${serber}','$pass',now());" >>sql.txt
done
具体操作：随便在一个目录下创建test.sh脚本，我这里以/usr/local/software目录为例
cd /usr/local/software
# 编辑脚本
vim test.sh
# 执行脚本后会生成sql.txt
sh test.sh
# 在mysql中进行插入数据操作
mysql> source /usr/local/software/sql.txt
- 创建好的test表如下：
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 3722351 |
+----------+
1 row in set (0.66 sec)
mysql> select * from test limit 5;
+---------+----------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+----------+-------------+----------+---------------------+
| 0000001 | user0 | 13763946878 | xjrvoorp | 2020-11-24 22:53:31 |
| 0000002 | user2 | 13763946880 | atrsvjxo | 2020-11-24 22:53:31 |
| 0000003 | user3 | 13763946881 | dmsboqbn | 2020-11-24 22:53:31 |
| 0000004 | user4 | 13763946882 | nepqbpai | 2020-11-24 22:53:31 |
| 0000005 | user5 | 13763946883 | ersxlykr | 2020-11-24 22:53:31 |
+---------+----------+-------------+----------+---------------------+
5 rows in set (0.00 sec)
1.3 mysql普通索引与唯一索引
（1）什么是普通索引？
普通索引(index)：顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。其特点是允许出现相同的索引内容，允许空(null)值。
（2）什么是唯一索引？
唯一索引(unique)：顾名思义就是不可以出现相同的索引内容，但是可以为空(null)值。
# 不能为createtime添加唯一索引，因为createtime不唯一
mysql> alter table test add unique unique_createtime (createtime );
ERROR 1062 (23000): Duplicate entry '2020-11-24 22:53:31' for key 'unique_createtime'
（3）如何创建普通索引或者唯一索引？
- 创建表的时候创建
create table test (
id int(7) zerofill auto_increment not null,
username varchar(20),
servnumber varchar(30),
password varchar(20),
createtime datetime,
unique (id)
)DEFAULT CHARSET=utf8;
- 直接为表添加索引
alter table 表名 add index 索引名称 (字段名称);
# eg：alter table test add unique unique_username (username);
# 注意：假如没有指定索引名称时，会以默认的字段名为索引名称（字段名就是索引名）
- 直接创建索引
create index 索引 on 表名 (字段名);
# eg：create index index_createtime on test (createtime);
（4）查看索引
show index from 表名\G
# eg：show index from test\G
# 或
desc 表名;
# 或
show create table 表名\G;
（5）删除索引
drop index 索引名称 on 表名;
# eg：drop index unique_username on test;
alter table 表名 drop index 索引名;
# eg：alter table test drop index createtime;
（6）测试加索引前后的查询速度
mysql> desc test;
+------------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+----------------+
| id | int(7) unsigned zerofill | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| servnumber | varchar(30) | YES | | NULL | |
| PASSWORD | varchar(20) | YES | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+--------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from test order by id desc limit 2;
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
| 3722350 | user3722350 | 13767669228 | swpddcdd | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from test where password='dtzwdhqc';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (1.49 sec)
mysql> select * from test where password='dtzwdhqc';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (1.25 sec)
mysql> alter table test add index (password);
mysql> desc test;
+------------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+----------------+
| id | int(7) unsigned zerofill | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
| servnumber | varchar(30) | YES | | NULL | |
| PASSWORD | varchar(20) | YES | MUL | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+--------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select * from test where password='dtzwdhqc';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (0.00 sec)
mysql> select * from test where password='swpddcdd';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722350 | user3722350 | 13767669228 | swpddcdd | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (0.01 sec)
1.4 mysql主键索引
（1）什么是主键索引？
把主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique是允许为空值的）。指定为PRIMARY KEY。
主键：主键是表的某一列，这一列的值是用来标志表中的每一行数据的。
注意：每一张表只能拥有一个主键。
（2）创建主键索引
- 创建表的时候创建
- 直接为表添加主键索引
alter table 表名 add primary key (字段名);
# eg：alter table test add primary key (id);
（3）删除主键索引
alter table 表名 drop primary key;
注意：删除主键索引前需要先删除auto_increment
# 删除主键索引失败
mysql> alter table test drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
# 删除主键索引前需要先删除auto_increment
mysql> alter table test change id id int(7) unsigned zerofill not null;
Query OK, 3722351 rows affected (21.93 sec)
Records: 3722351 Duplicates: 0 Warnings: 0
mysql> desc test;
+------------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| id | int(7) unsigned zerofill | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
| servnumber | varchar(30) | YES | | NULL | |
| PASSWORD | varchar(20) | YES | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 删除主键索引成功
mysql> alter table test drop primary key;
Query OK, 3722351 rows affected (21.98 sec)
Records: 3722351 Duplicates: 0 Warnings: 0
mysql> desc test;
+------------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| id | int(7) unsigned zerofill | NO | | NULL | |
| username | varchar(20) | YES | | NULL | |
| servnumber | varchar(30) | YES | | NULL | |
| PASSWORD | varchar(20) | YES | | NULL | |
| createtime | datetime | YES | | NULL | |
+------------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
# 没有主键索引查询时间为(1.71 sec)
mysql> select * from test where id=3722351;
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (1.71 sec)
# 对比有主键索引的count(*)，InnoDB引擎是怎么执行查询count(*)的？
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 3722351 |
+----------+
1 row in set (1.67 sec)
mysql> alter table test add primary key (id);
Query OK, 0 rows affected (30.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 有主键索引查询时间为(0.01 sec)
mysql> select * from test where id=3722351;
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (0.01 sec)
# 为什么加了主键索引后会快一些？
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 3722351 |
+----------+
1 row in set (0.70 sec)
1.5 mysql全文索引
（1）什么是全文索引？
全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词。全文索引也是目前搜索引擎使用的一种关键技术。指定为fulltext。
note：MyISAM支持全文索引，InnoDB也支持啊！有啥区别？区别是：1、在mysql5.6之前，InnoDB是不支持全文索引的。2、InnoDB不支持中文全文索引，即‘检索内容’不能是中文，只能是英文。--ddh 2020年11月27日00:26:03
（2）创建练习表的sql
create table command (
id int(5) unsigned primary key auto_increment,
name varchar(10),
instruction varchar(60)
)engine=MyISAM;
insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory hierarchy');
insert into command values('6','cp','复制文件或者文件夹');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','修改文件名，移动');
insert into command values('9','停止词','is,not,me,yes,no ...');
（3）添加全文索引
- 创建表的时候创建全文索引
- 通过alter添加
alter table command add fulltext(instruction);
（4）使用全文索引
select * from 表名 where match (字段名) against ('检索内容');
# eg：select * from command where match(instruction) against ('sections');
note：单位是词的意思是，如果是英文，则填到‘检索内容’中可以查出（如果有的话，停顿词除外）；如果是中文，则是以分割符为标准（比如逗号），填一个字或者多个字（不是两个分割符之间的），则查不出来。
- 查看匹配度
select id, match(instruction) against ('directory') from command;
# 查看匹配度
mysql> select id, match(instruction) against ('directory') from command;
+----+------------------------------------------+
| id | match(instruction) against ('directory') |
+----+------------------------------------------+
| 1 | 1.2109839916229248 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 1.1976701021194458 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
+----+------------------------------------------+
9 rows in set (0.00 sec)
# 查询结果按匹配度排序
mysql> select * from command where match (instruction) against ('directory');
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 1 | ls | list directory contents |
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.00 sec)
- 停止词：出现频率很高的词，将会使全文索引失效
mysql> select * from command where match (instruction) against ('is');
Empty set (0.00 sec)
mysql> select * from command where match (instruction) against ('no');
Empty set (0.00 sec)
- in boolean mode 模式
# in boolean mode：意思是指定全文检索模式为布尔全文检索（可以简单理解为是检索方式）
select * from 表名 where match (字段名) against ('检索内容' in boolean mode);
# 查询
mysql> select * from command where match (instruction) against ('directory');
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 1 | ls | list directory contents |
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.01 sec)
# 要以词为单位
mysql> select * from command where match (instruction) against ('director*');
Empty set (0.00 sec)
# 相当于模糊查询
mysql> select * from command where match (instruction) against ('director*' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 1 | ls | list directory contents |
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.00 sec)
mysql> select * from command where match (instruction) against ('*irectory' in boolean mode);
Empty set (0.00 sec)
note：使用通配符*时，只能放在词的后边，不能放前边。
# `加号`表示这两个一定要出现才能匹配到
mysql> select * from command where match (instruction) against ('+directory +hierarchy' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from command where match (instruction) against ('+directory+hierarchy' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 1 | ls | list directory contents |
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.00 sec)
# `加号`表示一定要出现，`不填`表示可以出现或者不出现
mysql> select * from command where match (instruction) against ('+directory hierarchy' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 1 | ls | list directory contents |
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.00 sec)
# `减号`表示一定不能出现
mysql> select * from command where match (instruction) against ('directory -contents' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction |
+----+------+-------------------------------------------+
| 5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
1 row in set (0.00 sec)
（5）删除全文索引
alter table command drop index instruction;
（6）注意点总结
1、一般情况下创建全文索引的字段数据类型为char、varchar、text 。其它字段类型不可以。
2、全文索引不针对非常频繁的词做索引。比如is，no，not，you，me，yes这些，我们称之为停止词。
3、对英文检索时忽略大小写。
（7）测试
# 查询最后两条数据，用于测试
mysql> select * from test order by id desc limit 2;
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
| 3722350 | user3722350 | 13767669228 | swpddcdd | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
2 rows in set (2.00 sec)
# 创建全文索引
mysql> alter table test add fulltext(password);
Query OK, 0 rows affected, 1 warning (35.99 sec)
Records: 0 Duplicates: 0 Warnings: 1
# 查看表中的所有索引
mysql> show index from test\G;
*************************** 1. row ***************************
Table: test
Non_unique: 1
Key_name: PASSWORD
Seq_in_index: 1
Column_name: PASSWORD
Collation: NULL
Cardinality: 3704395
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
1 row in set (0.00 sec)
# 不使用全文索引，用时(2.31 sec)
mysql> select * from test where password like '%dtzwdhqc%';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (2.31 sec)
# 使用全文索引，用时(0.00 sec)
mysql> select * from test where match(password) against ('dtzwdhqc');
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (0.00 sec)
1.6 mysql外键约束
（1）什么是外键？
外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。
- 创建测试表sql
# 创建部门表
CREATE TABLE dept(
deptnu INT PRIMARY KEY comment '部门编号',
dname VARCHAR(50) comment '部门名称',
addr VARCHAR(50) comment '部门地址'
);
# 某个公司的员工表
CREATE TABLE employee(
empno INT PRIMARY KEY comment '雇员编号',
ename VARCHAR(50) comment '雇员姓名',
job VARCHAR(50) comment '雇员职位',
mgr INT comment '雇员上级编号',
hiredate DATE comment '雇佣日期',
sal DECIMAL(7,2) comment '薪资',
deptnu INT comment '部门编号'
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
# 创建工资等级表
CREATE TABLE salgrade(
grade INT PRIMARY KEY comment '等级',
lowsal INT comment '最低薪资',
higsal INT comment '最高薪资'
);
# 插入dept表数据
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');
# 插入emp表数据
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10);
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30);
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);
# 插入salgrade表数据
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);
（2）添加外键约束
- 创建表时添加
CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(30) DEFAULT NULL,
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`),
foreign key (deptnu) references dept(deptnu)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# 语法：foreign key (字段名) references 关联的表名(关联表的字段名)
# 注意：主键跟外键的字段类型一定要相同
- alter table的方法
# 添加外键约束：一个表的外键的字段--对应-->另一个表的主键的字段
# 注意：这里直接是KEY `deptnu` (`deptnu`)，没有`foreign`这个关键字，也没有约束，是因为`ENGINE=MyISAM`
mysql> alter table employee add foreign key (deptnu) references dept(deptnu);
mysql> show create table employee\G;
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`),
KEY `deptnu` (`deptnu`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 修改存储引擎为InnoDB
mysql> alter table employee engine='InnoDB';
Query OK, 15 rows affected (0.02 sec)
# 添加外键约束
mysql> alter table employee add foreign key (deptnu) references dept(deptnu);
Query OK, 14 rows affected (0.04 sec)
mysql> show create table employee\G;
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`),
KEY `deptnu` (`deptnu`),
CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
- 保证数据的准确性与完整性
mysql> select * from dept;
+--------+-----------+--------+
| deptnu | dname | addr |
+--------+-----------+--------+
| 10 | 研发部 | 北京 |
| 20 | 工程部 | 上海 |
| 30 | 销售部 | 广州 |
| 40 | 财务部 | 深圳 |
+--------+-----------+--------+
4 rows in set (0.00 sec)
mysql> select * from employee;
+-------+-----------+-----------+------+------------+----------+--------+
| empno | ename | job | mgr | hiredate | sal | deptnu |
+-------+-----------+-----------+------+------------+----------+--------+
| 1001 | 小乔 | 文员 | 1013 | 2018-12-17 | 8000.00 | 20 |
| 1002 | 牛魔王 | 销售员 | 1006 | 2018-02-20 | 16000.00 | 30 |
| 1003 | 程咬金 | 销售员 | 1006 | 2017-02-22 | 12500.00 | 30 |
| 1004 | 猪八戒 | 经理 | 1009 | 2001-04-02 | 29750.00 | 20 |
| 1005 | 后裔 | 销售员 | 1006 | 2011-09-28 | 12500.00 | 30 |
| 1006 | 猴子 | 经理 | 1009 | 2011-05-01 | 28500.00 | 30 |
| 1007 | 张飞 | 经理 | 1009 | 2011-09-01 | 24500.00 | 10 |
| 1008 | 诸葛亮 | 分析师 | 1004 | 2017-04-19 | 30000.00 | 20 |
| 1009 | 唐僧 | 董事长 | NULL | 2010-11-17 | 50000.00 | 10 |
| 1010 | 韩信 | 销售员 | 1006 | 2018-09-08 | 15000.00 | 30 |
| 1011 | 妲己 | 文员 | 1008 | 2018-05-23 | 11000.00 | 20 |
| 1012 | 安琪拉 | 文员 | 1006 | 2011-12-03 | 9500.00 | 30 |
| 1013 | 林俊杰 | 分析师 | 1004 | 2011-12-03 | 30000.00 | 20 |
| 1014 | 甄姬 | 文员 | 1007 | 2019-01-23 | 7500.00 | 10 |
+-------+-----------+-----------+------+------------+----------+--------+
14 rows in set (0.00 sec)
# 因为dept没有50这个部门，所以在employee中也不能插入数据，这就保证数据的准确性与完整性
mysql> insert into employee values('2000','李信','经理','1009','2020-11-28','30000.00','50');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`xdclass`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`))
（3）删除外键约束
# 删除外键所以前需要先把外键约束给删除，不然会报错
mysql> alter table employee drop index deptnu;
ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint
# 删除外键约束
mysql> alter table employee drop index deptnu;
Query OK, 0 rows affected (0.01 sec)
# 删除外键索引
mysql> alter table employee drop index deptnu;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table employee\G;
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
`empno` int(11) NOT NULL COMMENT '雇员编号',
`ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
`job` varchar(50) DEFAULT NULL COMMENT '雇员职位',
`mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
`hiredate` date DEFAULT NULL COMMENT '雇佣日期',
`sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
`deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（4）注意点总结
1丶两个表，主键跟外键的字段类型一定要相同；
2丶要使用外键约束表的引擎一定得是InnoDB引擎，MyISAM是不起作用的；
3丶在删除外键索引之前必须先把外键约束删除，才能删除索引；
1.7 mysql联合索引
（1）什么是联合索引？
联合索引又称组合索引或者复合索引，是建立在两列或者多列以上的索引。
（2）创建联合索引
alter table 表名 add index(字段1,字段2,字段3);
# eg：alter table test add index(username,servnumber,password);
# 索引名默认为第一个字段名
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(7) unsigned zerofill NOT NULL,
`username` varchar(20) DEFAULT NULL,
`servnumber` varchar(30) DEFAULT NULL,
`PASSWORD` varchar(20) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
KEY `username` (`username`,`servnumber`,`PASSWORD`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
（3）删除联合索引
alter table 表名 drop index 联合索引名;
（4）为什么要使用联合索引，而不使用多个单列索引？
联合索引的效率远远高于单列索引
# 创建了3个单列索引
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(7) unsigned zerofill NOT NULL,
`username` varchar(20) DEFAULT NULL,
`servnumber` varchar(30) DEFAULT NULL,
`PASSWORD` varchar(20) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
KEY `username` (`username`),
KEY `PASSWORD` (`PASSWORD`),
KEY `servnumber` (`servnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test where username like 'user3722%' and servnumber like '1376766%' and PASSWORD like 'dtzw%';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (0.00 sec)
# 如果是单列索引，mysql会根据算法选择一个最优的索引进行查询，通过这个索引锁定一个范围，在这个范围内就不用索引了
# possible_keys指可用的索引，key指该查询使用的索引
mysql> explain select * from test where username like 'user3722%' and servnumber like '1376766%' and PASSWORD like 'dtzw%';
+----+-------------+-------+------------+-------+------------------------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | range | username,PASSWORD,servnumber | PASSWORD | 63 | NULL | 6 | 0.83 | Using index condition; Using where |
+----+-------------+-------+------------+-------+------------------------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from test where username like 'user3722%' and servnumber like '1376766%' and PASSWORD like 'd%';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 3722045 | user3722045 | 13767668923 | diydylsx | 2020-11-25 01:00:39 |
| 3722056 | user3722056 | 13767668934 | denceouq | 2020-11-25 01:00:39 |
| 3722082 | user3722082 | 13767668960 | dzqpecuy | 2020-11-25 01:00:39 |
| 3722120 | user3722120 | 13767668998 | diabioph | 2020-11-25 01:00:39 |
| 3722135 | user3722135 | 13767669013 | dassrvvs | 2020-11-25 01:00:39 |
| 3722137 | user3722137 | 13767669015 | duvhxefb | 2020-11-25 01:00:39 |
| 3722142 | user3722142 | 13767669020 | duljpxgb | 2020-11-25 01:00:39 |
| 3722170 | user3722170 | 13767669048 | ddgoqjkq | 2020-11-25 01:00:39 |
| 3722236 | user3722236 | 13767669114 | dhdzwczi | 2020-11-25 01:00:39 |
| 3722304 | user3722304 | 13767669182 | dpczteei | 2020-11-25 01:00:39 |
| 3722318 | user3722318 | 13767669196 | dqozqloy | 2020-11-25 01:00:39 |
| 3722343 | user3722343 | 13767669221 | dbfyjhqp | 2020-11-25 01:00:40 |
| 3722351 | user3722351 | 13767669229 | dtzwdhqc | 2020-11-25 01:00:40 |
+---------+-------------+-------------+----------+---------------------+
13 rows in set (0.00 sec)
mysql> explain select * from test where username like 'user3722%' and servnumber like '1376766%' and PASSWORD like 'd%';
+----+-------------+-------+------------+-------+------------------------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+------------------------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | range | username,PASSWORD,servnumber | username | 63 | NULL | 463 | 0.04 | Using index condition; Using where |
+----+-------------+-------+------------+-------+------------------------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
- 联合索引的最左原则
# 创建联合索引
mysql> alter table test add index composite_index (username,PASSWORD,servnumber);
Query OK, 0 rows affected (27.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(7) unsigned zerofill NOT NULL,
`username` varchar(20) DEFAULT NULL,
`servnumber` varchar(30) DEFAULT NULL,
`PASSWORD` varchar(20) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
KEY `username` (`username`),
KEY `PASSWORD` (`PASSWORD`),
KEY `servnumber` (`servnumber`),
KEY `composite_index` (`username`,`PASSWORD`,`servnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 联合索引和单列索引都存在的情况下，并不一定走联合索引
mysql> explain select * from test where username like 'user3722%' and servnumber like '1376766%' and PASSWORD like 'd%';
+----+-------------+-------+------------+-------+----------------------------------------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------------------------------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | range | username,PASSWORD,servnumber,composite_index | username | 63 | NULL | 463 | 0.04 | Using index condition; Using where |
+----+-------------+-------+------------+-------+----------------------------------------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
# 删除单列索引
mysql> alter table test drop index username;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test drop index PASSWORD;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table test drop index servnumber;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(7) unsigned zerofill NOT NULL,
`username` varchar(20) DEFAULT NULL,
`servnumber` varchar(30) DEFAULT NULL,
`PASSWORD` varchar(20) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
KEY `composite_index` (`username`,`PASSWORD`,`servnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
# 走联合索引
mysql> explain select * from test where username like 'user3722%' and servnumber like '1376766%' and PASSWORD like 'd%';
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | composite_index | composite_index | 219 | NULL | 463 | 1.23 | Using index condition |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
# 不符合最左原则，不走联合索引
mysql> explain select * from test where servnumber like '1376766%' and PASSWORD like 'd%';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test | NULL | ALL | NULL | NULL | NULL | NULL | 3704395 | 1.23 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
（5）注意点总结
- 索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。
- 当表的数据量很大的时候，可以考虑建立索引。
- 表中经常查数据的字段，可以考虑建立索引。
- 想要保证表中数据的唯一性，可以考虑建立唯一索引。
- 想要保证两张表中的数据的完整性跟准确性，可以考虑建立外键约束。
- 经常对多列数据进行查询时，可以考虑建立联合索引。
02 mysql的sql语句优化思路
2.1 mysql的慢查询日志开启与问题定位
（1）第一步：查看是否已经开启了慢查询日志
mysql> show variables like 'slow%';
+---------------------+-----------------------------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/software/mysql/data/iZwz9bl327dijo79eslzmvZ-slow.log |
+---------------------+-----------------------------------------------------------------+
（2）第二步：开启慢查询日志
mysql> set global slow_query_log = on ;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow%';
+---------------------+-----------------------------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/software/mysql/data/iZwz9bl327dijo79eslzmvZ-slow.log |
+---------------------+-----------------------------------------------------------------+
# 日志路径也可以自定义，慢查询的sql都会记录到里面
set global slow_query_log_file = '路径';
（3） 第三步：查看慢查询的时间临界值
# 查询操作long_query_time：1s的sql都会记录在里面
mysql> show variables like '%long%';
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 1.000000 |
| performance_schema_events_stages_history_long_size | 1000 |
| performance_schema_events_statements_history_long_size | 1000 |
| performance_schema_events_transactions_history_long_size | 1000 |
| performance_schema_events_waits_history_long_size | 1000 |
+----------------------------------------------------------+----------+
（4） 第四步：设置慢查询的时间标准
mysql> set long_query_time=0.4;
mysql> show variables like '%long%';
+----------------------------------------------------------+----------+
| Variable_name | Value |
+----------------------------------------------------------+----------+
| long_query_time | 0.400000 |
| performance_schema_events_stages_history_long_size | 1000 |
| performance_schema_events_statements_history_long_size | 1000 |
| performance_schema_events_transactions_history_long_size | 1000 |
| performance_schema_events_waits_history_long_size | 1000 |
+----------------------------------------------------------+----------+
- 测试日志记录
# 没有主键索引，则用id来测试
mysql> show create table test\G;
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(7) unsigned zerofill NOT NULL,
`username` varchar(20) DEFAULT NULL,
`servnumber` varchar(30) DEFAULT NULL,
`PASSWORD` varchar(20) DEFAULT NULL,
`createtime` datetime DEFAULT NULL,
KEY `composite_index` (`username`,`PASSWORD`,`servnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from test where id=1000000;
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 1000000 | user1000000 | 13764946878 | jakonjlh | 2020-11-24 23:33:00 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (2.00 sec)
[root@iZwz9bl327dijo79eslzmvZ ~]# tail -f /usr/local/software/mysql/data/iZwz9bl327dijo79eslzmvZ-slow.log
/usr/local/software/mysql/bin/mysqld, Version: 5.7.20 (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 2020-11-28T09:52:00.291662Z
# User@Host: root[root] @ localhost [] Id: 170
# Query_time: 1.982413 Lock_time: 0.000140 Rows_sent: 1 Rows_examined: 3722351
use xdclass;
SET timestamp=1606557120;
select * from test where id=1000000;
- 注意：重启mysql服务会让在交互界面设置的慢查询恢复到默认
# 永久生效的设置方法：修改配置文件vim /etc/my.cnf
[mysqld]
slow_query_log = 1
long_query_time = 0.3
slow_query_log_file =/usr/local/software/mysql/mysql_slow.log
# 最后必须重启服务才能生效！
service mysqld restart
2.2 mysql的sql语句执行过程解析
（1）第一步：查看性能详情是否开启
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
（2）第二步：开启性能记录功能
mysql> set profiling = on;
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
（3）第三步：查看性能的记录
# (0.00 sec)
mysql> select * from test where id=1000000;
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 1000000 | user1000000 | 13764946878 | jakonjlh | 2020-11-24 23:33:00 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (0.00 sec)
# (1.15 sec)
mysql> select * from test where username='user1000000';
+---------+-------------+-------------+----------+---------------------+
| id | username | servnumber | PASSWORD | createtime |
+---------+-------------+-------------+----------+---------------------+
| 1000000 | user1000000 | 13764946878 | jakonjlh | 2020-11-24 23:33:00 |
+---------+-------------+-------------+----------+---------------------+
1 row in set (1.15 sec)
# 开启性能查询后，所有的查询记录都会记录在里面
mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 1 | 1.15016025 | select * from test where username='user1000000' |
| 2 | 0.00024650 | select * from test where id=1000000 |
+----------+------------+-------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
（4）第四步：查看语句的执行性能详情
# 查看性能详情
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000077 |
| checking permissions | 0.000006 |
| Opening tables | 0.000015 |
| init | 0.000024 |
| System lock | 0.000007 |
| optimizing | 0.000008 |
| statistics | 0.000014 |
| preparing | 0.000011 |
| executing | 0.000002 |
| Sending data | 1.149891 |
| end | 0.000013 |
| query end | 0.000010 |
| closing tables | 0.000009 |
| freeing items | 0.000027 |
| logging slow query | 0.000038 |
| cleaning up | 0.000012 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu, block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000077 | 0.000072 | 0.000000 | 0 | 0 |
| checking permissions | 0.000006 | 0.000006 | 0.000000 | 0 | 0 |
| Opening tables | 0.000015 | 0.000014 | 0.000000 | 0 | 0 |
| init | 0.000024 | 0.000024 | 0.000000 | 0 | 0 |
| System lock | 0.000007 | 0.000006 | 0.000000 | 0 | 0 |
| optimizing | 0.000008 | 0.000009 | 0.000000 | 0 | 0 |
| statistics | 0.000014 | 0.000013 | 0.000000 | 0 | 0 |
| preparing | 0.000011 | 0.000011 | 0.000000 | 0 | 0 |
| executing | 0.000002 | 0.000002 | 0.000000 | 0 | 0 |
| Sending data | 1.149891 | 1.113559 | 0.037418 | 0 | 0 |
| end | 0.000013 | 0.000006 | 0.000001 | 0 | 0 |
| query end | 0.000010 | 0.000009 | 0.000001 | 0 | 0 |
| closing tables | 0.000009 | 0.000008 | 0.000001 | 0 | 0 |
| freeing items | 0.000027 | 0.000023 | 0.000004 | 0 | 0 |
| logging slow query | 0.000038 | 0.000033 | 0.000004 | 0 | 8 |
| cleaning up | 0.000012 | 0.000010 | 0.000002 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
16 rows in set, 1 warning (0.00 sec)
- 性能线程的详细解释官方文档链接：
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
2.3 mysql语句优化的几个小建议
- 尽量避免使用
select * from，尽量精确到想要的结果字段； - 尽量避免条件使用
or； - 记得加上
limit限制行数，避免数据量过大消耗性能； - 使用模糊查询时，
%放在前面是会使索引失效； - 要小心条件字段类型的转换；
浙公网安备 33010602011771号