MySQL进阶

使用Docker安装一个MySQL5.7

[root@Shyee ~]# docker pull mysql:5.7
[root@Shyee ~]# docker images

#返回结果
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               5.7                 a70d36bc331a        13 days ago         449MB

#运行前需要一点配置
[root@Shyee ~]# docker run --name mysql5 -v /usr/local/MySQL5:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=密码 -d a70d36bc331a

[root@Shyee ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
7b829aa38925        a70d36bc331a        "docker-entrypoint.s…"   6 minutes ago       Up 6 minutes        0.0.0.0:3306->3306/tcp, 33060/tcp   mysql5

#进入服务
docker exec -it id /bin/bash

数据库目录: datadir=/var/lib/mysql
pid文件目录:–pid-file=/var/lib/mysql/ibdata01.pid

MySQL的文件结构

root@7b829aa38925:/var/lib/mysql# ls
auto.cnf    ca.pem	     client-key.pem  ib_logfile0  ibdata1  mysql	       private_key.pem	server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile1  ibtmp1   performance_schema  public_key.pem	server-key.pem

root@7b829aa38925:/var/lib/mysql# cd /usr/share/mysql
root@7b829aa38925:/usr/share/mysql# ls
bulgarian	errmsg-utf8.txt       innodb_memcached_config.sql  mysql-log-rotate		 mysqld_multi.server  serbian
charsets	estonian	      install_rewriter.sql	   mysql-systemd-start		 norwegian	      slovak
czech		fill_help_tables.sql  italian			   mysql_security_commands.sql	 norwegian-ny	      spanish
danish		french		      japanese			   mysql_sys_schema.sql		 polish		      swedish
dictionary.txt	german		      korean			   mysql_system_tables.sql	 portuguese	      ukrainian
dutch		greek		      magic			   mysql_system_tables_data.sql  romanian	      uninstall_rewriter.sql
english		hungarian	      mysql-helpers		   mysql_test_data_timezone.sql  russian

查询MySQL的编码

show variables like '%char%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
需要统一设置编码UTF-8
好麻烦，还是直接装在centOS上

CentOS8 安装MySQL5.5

下载

https://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-5.5.59-1.el7.x86_64.rpm

https://dev.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-5.5.59-1.el7.x86_64.rpm

上传到服务器

安装

无依赖安装（–nodeps）

rpm -ivh –nodeps MySQL-server-5.5.59-1.el7.x86_64.rpm

rpm -ivh –nodeps MySQL-client-5.5.59-1.el7.x86_64.rpm

启动

#验证
[root@Shyee MySQL5]# mysqladmin --version
mysqladmin  Ver 8.42 Distrib 5.5.59, for Linux on x86_64
#启动
[root@Shyee MySQL5]# service mysql start
Starting MySQL.Logging to '/var/lib/mysql/Shyee.err'.
.                                                          [  OK  ]
#配置开机自启
chkconfig mysql on

改密码

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

使用

mysql -u root -p

统一编码

改my-huge.cnf文件，复制到/etc，名字改为my.cnf

分别在mysql 和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)

mysql小知识

MySQL逻辑分层：连接层->服务层->引擎层->存储层

InnoDB与MyISAM

InnoDB :事务优先（适合高并发操作;行锁)

My ISAM :性能优先（表锁)

查询MySQL支持哪些引擎

mysql> show engines;


查询当前使用的引擎

show variables like ‘%storage_engine%’;

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

默认innoDB

创建一个myISAM引擎的表

mysql> create database myDB;
Query OK, 1 row affected (0.00 sec)

mysql> use myDB
Database changed
mysql> 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;
Query OK, 0 rows affected (0.01 sec)

sql优化

编写sql的过程

select distinct..from ..join ..on ..where ..group by ...having ..order by ..limit ..

MySQL执行代码的过程

from .. on.. join ..where ..group by ... having ...select distinct..order by limit ..

sql优化，重在优化索引

MySQL默认使用B+树做索引

如果一个字段是primary key，则改字段默认就是主键索引

索引的弊端:

1.索引本身很大，可以存放在内存/硬盘（通常为硬盘）

2.索引不是所有情况均适用:a.少量数据b.频繁更新的字段c.很少使用的字段

3.索引会降低增删改的效率

优势：

1提高查询效率（降低IO使用率)

2.降低CPU使用率(…order by age desc)

分类:

单值索引：单列，age ;一个表可以多个单值索引, name

唯一索引:不能重复。id

复合索引:多个列构成的索引（相当于二级目录)（可以有多个列）

索引的创建

方式一

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

--创建一个单值索引
mysql> create index dept_index on tb(dept);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

--创建一个唯一索引
mysql> create unique index name_index on tb(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

--创建一个复合索引
mysql> create index dept_name_index on tb(dept,name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

方式二

alter table 表名 add index 索引名(字段)

--添加一个单值索引
mysql> alter table tb add index dept_index(dept) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
--创建一个唯一索引
mysql> alter table tb add unique index name_index(name) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
--创建一个复合索引
mysql> alter table tb add index dept_name_index(dept,name) ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引

drop index 索引名 on 表名;

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

查询索引

show index from 表名;

show index from tb;


sql性能问题

a.分析SQL的执行计划: explain，可以模拟SQL优化执行SQL语句

b.MySQL查询优化其会干扰我们的优化

官网提供的解决方案：https://dev.mysql.com/doc/refman/8.0/en/optimization.html


explain

mysql> explain select * from tb;


id :编号

select_type :查询类型

table :表

type:类型

possible_keys:预测用到的索引

key :实际使用的索引

key_len:实际使用索引的长度

ref:表之间的引用

rows :通过索引查询到的数据量

Extra:额外的信息

实例

准备数据

create table course(
cid int(3),
cname varchar(10),
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');

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

mysql> select * from teacher t,course c,teachercard tc where c.tid=t.tid and t.tcid=tc.tcid and(c.cid=2 or tc.tcid=3);
+------+-------+------+------+-------+------+------+--------+
| tid  | tname | tcid | cid  | cname | tid  | tcid | tcdesc |
+------+-------+------+------+-------+------+------+--------+
|    1 | tz    |    1 |    2 | html  |    1 |    1 | tzdesc |
|    3 | tl    |    3 |    4 | web   |    3 |    3 | tldesc |
+------+-------+------+------+-------+------+------+--------+

explain

mysql> explain select * from teacher t,course c,teachercard tc where c.tid=t.tid and t.tcid=tc.tcid and(c.cid=2 or tc.tcid=3);


可以看到执行顺序，自上至下是t(三条记录) tc(三条记录) c(4条记录)

现在给teacher 插入几条记录

INSERT INTO teacher VALUES(4,'ta',4);
INSERT INTO teacher VALUES(5,'tz',5);
INSERT INTO teacher VALUES(6,'tv',6);

再次解释


可以看到执行顺序是tc(三条记录) c(四条记录) t(6条记录)

顺序改变的原因：笛卡尔积

中间过程(乘积)越小越好–数据小的表优先查询

查询课程名字为sql的教师描述

mysql> select tc.tcdesc from teacher t,course c,teachercard tc where c.tid=t.tid and t.tcid=tc.tcid and(c.cname='sql');
+--------+
| tcdesc |
+--------+
| twdesc |
+--------+
1 row in set (0.00 sec)

explain

explain select tc.tcdesc from teacher t,course c,teachercard tc where c.tid=t.tid and t.tcid=tc.tcid and(c.cname='sql');


如果是嵌套查询

SELECT tcdesc from teachercard where tcid=(select tcid from teacher where tid=(select tid from course where cname='sql'));

explain

mysql> explain SELECT tcdesc from teachercard where tcid=(select tcid from teacher where tid=(select tid from course where cname='sql'));


Id值不同时，id越大越优先查询

多表连接+嵌套查询

mysql> SELECT t.tname, tc.tcdesc from teachercard tc,teacher t where t.tcid=tc.tcid and t.tid=(select tid from course where cname='sql');
+-------+--------+
| tname | tcdesc |
+-------+--------+
| tw    | twdesc |
+-------+--------+
1 row in set (0.00 sec)

explain

mysql> explain SELECT t.tname, tc.tcdesc from teachercard tc,teacher t where t.tcid=tc.tcid and t.tid=(select tid from course where cname='sql');


id有相同有不同 所以执行顺序为 course tc t

select_type:

primary：主查询（最外层）

subquery：子查询（非最外层）

simple：简单查询

derived：衍生查询（查询时 使用到了临时表 在from中只有一张表）

Union

union result

type：索引类型

system->const->eq_ref->ref->range->index->all

越往左性能越高

system：只有一条数据的系统表或衍生表只有一条数据的主查询 才能达到

const：只能查到一条数据的SQL，用于primary key 或unique索引 （类型与索引类型有关）

eq_ref:唯一性索引：对于每个索引键的额查询，返回匹配唯一行数据（有且只有1个，不能多，不能0）常见于主键或者唯一

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

range：检索指定范围的行，where 后面是一个有索引的范围查询（between in < > |in有时候会失效)

index：查询全部索引的数据：

all：查询全部表中的数据：

system/const:结果只有一条数据

eq_ref:结果多条 但是每条数据都是唯一的

ref：结果多条，但是每条数据是0或多条

possible keys：可能使用到的索引

是一种预测，不准

keys：实际用到的索引

key_len:索引长度

用于判断复合索引是否被完全使用

创建一个用于实验的表

create table test_kl(name char(20) not null default '');
alter table test_kl add index index_name(name);
select * from test_kl where name='';


key_len:60

在utf-8中1个字符占3个字节

索引字段是20 所以长度一共为60

如果字段可以为空

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

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


如果字段可以为null MySQL 会用一个字节来标识

drop index index_name1 on test_kl;
drop index index_name on test_kl;

创建复合索引

alter table test_kl add index index_name1(name,name1);


60 +60+1

alter table  test_kl add column name2 varchar(20);
alter table test_kl add index name2_index(name2);


20*3 +1+2

mysql用两个字节标识可变长度

gbk 一个字符2字节

Latin 一个字符一个字节

ref

与type中的ref不同

指明当前表所参照的字段

rows

被索引优化查询的 数据个数

extra

using filesort：性能消耗大，需要额外的一次排序

常出现于order by

create table test02(a1 char(3),a2 char(3), a3 char(3),index idx_a1(a1),index idx_a2(a2),index idx_a3(a3));


复合索引：不能跨列（最佳最前缀）

where 和order by 按照复合索引的顺序使用 ，不要跨列和无序使用

using temporary：性能损耗大，用到了临时表

一般出现在group by中

减少跨列使用

using index：索引覆盖（性能提升）

没有读取源文件，只从索引中获取数据

using where

需要回表查询

impossible where

where 字句为false