Mysql 高级
- 1 Mysql 框架介绍
- 2 索引优化分析
-
- 2.1 性能下降SQL慢、执行、等待时间长
- 2.2 常见通用的Join查询
- 2.3 索引
-
- 2.3.1 索引是什么
- 2.3.2 索引的优劣
- 2.3.3 索引的分类
- 2.3.4 索引数据结构
- 2.3.5 适合建索引的情况
- 2.3.6 不适合建索引的情况
- 2.3.7 Mysql性能分析---常见瓶颈
- 2.3.8 Mysql性能分析---Explain
- 2.3.9 索引优化案例
- 2.3.10 索引失效
- 3 查询截断分析
- 4 Mysql 锁机制
- 5 主从复制
- 6 Mycat
1 Mysql 框架介绍
1.1 Mysql Linux安装
rpm -qa|grep mysql # 检查是否已经安装
rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm # 安装server
rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm # 安装client
注意:
- 在腾讯云centos7服务器安装时会报“conflicts with file from package mariadb-libs-xxx”冲突,需要yum -y remove mariadb-libs-1:5.5.65-1.el7.x86_64 移除掉冲突的包,再重新安装。
- 安装过程中可能会报依赖某些.so文件,使用yum -y install xxx 安装即可。
1.1.1 启动和停止
service mysql start
service mysql stop
1.1.2 root密码设置
首次安装没有密码,直接可以登录:
设置root用户密码:
/usr/bin/mysqladmin -u root password 123456
1.1.3 mysql开机自启动
chkconfig mysql on
chkconfig --list | grep mysql
1.1.4 修改默认字符集
创建数据库和表,插入数据中包含中文字符,select查询会显示乱码,这是因为默认字符集不是utf8的问题。
show variables like "%char%";
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf # 拷贝mysql配置文件到/etc目录
修改配置文件中的字符编码:
[client]
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation_server=utf8_general_ci
[mysql]
default-character-set=utf8
重启mysql服务,新建数据库和表:
1.1.5 Mysql数据库相关目录
1.1.6 Mysql用户权限相关问题
使用远程访问会报没有权限,这是因为没有创建远程访问的用户。
1. 创建用户
create user zhang3 identified by '123123';
表示创建名称为zhang3的用户,密码设为123123;
2. 修改当前用户的密码:
set password =password('123456')
3. 修改某个用户的密码:
update mysql.user set password=password('123456') where user='li4';
flush privileges; #所有通过user表的修改,必须用该命令才能生效。
4. 修改用户名:
update mysql.user set user='li4' where user='wang5';
flush privileges; #所有通过user表的修改,必须用该命令才能生效。
5. drop user li4 ; 不要通过delete from user u where user='li4' 进行删除,系统会有残留信息保留。
授权命令:
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
该权限如果发现没有该用户,则会直接新建一个用户。
比如
grant select,insert,delete,drop on atguigudb.* to li4@localhost ;
#给li4用户用本地命令行方式下,授予atguigudb这个库下的所有表的插删改查的权限。
grant all privileges on *.* to joe@'%' identified by '123';
#授予通过网络方式登录的的joe用户 ,对所有库所有表的全部权限,密码设为123.
收回权限命令:
revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ;
REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;
#收回全库全表的所有权限
REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost;
#收回mysql库下的所有表的插删改查权限
必须用户重新登录后才能生效
查看当前用户权限
show grants;
查看某用户的全局权限
select * from user ;
查看某用户的某个表的权限
select * from tables_priv;
1.2 Mysql 配置文件
1.2.1 二进制日志log-bin
主要用于主从复制,log-bin 中存放了所有的操作记录(写),可以用于恢复,log-bin配置(默认关闭)
1.2.2 错误日志log-error
默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等。
1.2.3 慢查询日志
默认关闭,记录查询的sql语句,如果开启会减低mysql的整体性能。可自定义0-10秒之间的阈值,慢查询日志会将超过阈值的查询记录下来,方便找到需要优化的 sql 。
1.2.4 配置文件
windows:my.ini
linux:/etc/my.cnf
1.3 Mysql 架构介绍
MySQL插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离,这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.3.1 连接层
最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
1.3.2 服务层
1.3.2.1 Management Serveices & Utilities:
系统管理和控制工具
1.3.2.2 SQL Interface: SQL接口
接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface
1.3.2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
1.3.2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
用一个例子就可以理解: select uid,name from user where gender= 1;优化器来决定先查询还是先过滤。
1.3.2.5 Cache和Buffer: 查询缓存。
如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存,记录缓存,key缓存,权限缓存等。
缓存是负责读,缓冲负责写。
1.3.3.引擎层
存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
1.3.4.存储层
数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互。
1.4 Mysql 存储引擎
1.4.1 查看存储引擎
1.4.2 存储引擎介绍
-
InnoDB存储引擎
InnoDB是MySQL的默认事务型引擎,它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎,否则应该优先考虑InnoDB引擎。行级锁,适合高并发情况 -
MyISAM存储引擎
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住),有一个毫无疑问的缺陷就是崩溃后无法安全恢复。 -
Archive引擎
Archive存储引擎只支持INSERT和SELECT操作,在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。
根据英文的测试结论来看,Archive表比MyISAM表要小大约75%,比支持事务处理的InnoDB表小大约83%。 -
Blackhole引擎
Blackhole引擎没有实现任何存储机制,它会丢弃所有插入的数据,不做任何保存。但服务器会记录Blackhole表的日志,所以可以用于复制数据到备库,或者简单地记录到日志。但这种应用方式会碰到很多问题,因此并不推荐。 -
CSV引擎
CSV引擎可以将普通的CSV文件作为MySQL的表来处理,但不支持索引。
CSV引擎可以作为一种数据交换的机制,非常有用。
CSV存储的数据直接可以在操作系统里,用文本编辑器,或者excel读取。 -
Memory引擎
如果需要快速地访问数据,并且这些数据不会被修改,重启以后丢失也没有关系,那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快,如redis) -
Federated引擎
Federated引擎是访问其他MySQL服务器的一个代理,尽管该引擎看起来提供了一种很好的跨服务器的灵活性,但也经常带来问题,因此默认是禁用的。
1.4.3 MyISAM VS InnoDB
2 索引优化分析
2.1 性能下降SQL慢、执行、等待时间长
-
查询数据过多:能不能拆,条件过滤尽量少。
-
join太多:join 原理:用 A 表的每一条数据扫描 B表的所有数据,多张表join很耗性能。所以尽量先过滤。
-
没有利用到索引:对于经常查询的字段,建立索引。索引分为单值和复合(多个字段联合)索引。
-
参数调优:服务器调优及各个参数设置(缓冲、线程数等)(不重要DBA的工作)。
2.2 常见通用的Join查询
2.2.1 SQL执行顺序
- 手写:
- 机读:
2.2.2 七种join
2.2.2.1 七种Join 对应SQL
以下面两张表为例:
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);
2.2.2.1.1 两表共有
获取两张表的**共有(交集)**部分:
select * from t_emp e inner join t_dept d on d.id=e.deptId;
2.2.2.1.2 左表独有+共有
获取左表独有+共有部分,相对于inner join 增加了左表独有。
select * from t_emp e left join t_dept d on d.id=e.deptId;
2.2.2.1.3 右表独有+共有
获取右表独有+共有部分,相对于inner join 增加了右表独有。
select * from t_emp e right join t_dept d on d.id=e.deptId;
2.2.2.1.4 左表独有
获取左表独有部分,相对于left join 减少了共有,即增加where条件:右表id is null。
select * from t_emp e left join t_dept d on d.id=e.deptId where d.id is null;
2.2.2.1.5 右表独有
获取右表独有部分,相对于right join 减少了共有,即增加where条件:左表id is null。
select * from t_emp e right join t_dept d on d.id=e.deptId where e.id is null;
2.2.2.1.6 全外连接(左表独有 + 共有 + 右表独有)
获取左表独有 + 共有 + 右表独有部分,相对于左外连接 + 右外连接,通过union将两部分合并并去重(左外和右外都包含共有部分,因此需要去重)。
注意:mysql不支持full join,因此使用union将两部分合并。
select * from t_emp e left join t_dept d on d.id=e.deptId
union
select * from t_emp e right join t_dept d on d.id=e.deptId;
2.2.2.1.7 左表独有 + 右表独有
获取“左表独有 + 右表独有”,通过union将左表独有 和 右表独有合并。
select * from t_emp e left join t_dept d on d.id=e.deptId where d.id is null