MySQL 高级
一、mysql 的架构介绍
1、MySQL简介
- mysql 内核
- sql 优化工程师
- mysql 服务器的优化
- 各种参数常量的设定
- 查询语句优化
- 主从复制
- 软硬件升级
- 容灾备份
- sql 编程
2、MysqlLinux 版的安装
1)安装mysql 服务器端
[root@centos64 opt]# rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm
2)安装mysql 客户端
[root@centos64 opt]# rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm
注意:若安装时出现
error: Failed dependencies:
libaio.so.1 is needed by MySQL-server-5.5.48-1.linux2.6.i386
libaio.so.1(LIBAIO_0.1) is needed by MySQL-server-5.5.48-1.linux2.6.i386
libaio.so.1(LIBAIO_0.4) is needed by MySQL-server-5.5.48-1.linux2.6.i386
error: Failed dependencies:
libncurses.so.5 is needed by MySQL-client-5.5.48-1.linux2.6.i386
解决方案: 添加对应的依赖
yum -y install libncurses.so.5
yum -y install libaio.so.1
3)查看mysql的用户和mysql组
即为安装成功
4)mysql 服务的启+停
[root@centos64 opt]# service mysql start
[root@centos64 opt]# service mysql stop
5)ROOT密码设置
[root@centos64 opt]# /usr/bin/mysqladmin -u root password 123456
6) 自启动 mysql 服务
[root@centos64 opt]# chkconfig mysql on
7)mysql 安装位置
路径 | 解释 | 备注 |
---|---|---|
/var/lib/mysql | mysql数据库文件的存放路径 | /var/lib/mysql/atguigu.cloud.pid |
/uer/share/mysql/ | 配置文件目录 | mysql.server命令及配置文件 |
/usr/bin | 相关命令目录 | mysqladmin mysqldump等命令 |
/etc/init.d/mysql | 启停相关脚本 |
在 linux 下查看安装目录 ps -ef|grep mysql
8)修改字符集
① 拷贝文件 my-huge.cnf
[root@centos64 opt]# cd /usr/share/mysql/
[root@centos64 mysql]# cp my-huge.cnf /etc/my.cnf
② 重新启动 MySQL 服务
③ 查看字符集
show variables like '%char%'
默认的客户端和服务器端都用了 latin 1 ,所以会乱码
④ 修改字符集
-
[root@centos64 etc]# vim /etc/my.cnf
修改此三处
⑤ 重启 mysql
3、Mysql 配置文件
主要配置文件:
-
二进制日志文件log-bin
运用于主从复制 -
错误日志log-error
默认时关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等 -
查询日志log
默认关闭,记录查询的sql语句,如果开启会减低mysql的整体性能,因为记录日志也是需要消耗能量的 -
数据文件
① 两系统 :Windows Linux
② frm文件 :存放表结构
③ myd 文件:存放表数据
④ myi文件:存放表索引 -
如何配置
Windows :my.ini 文件
Linux:/etc/my.cnf 文件
4、Mysq逻辑架构
和其他数据库相比,mysql有点与众不同,它的架构可以在多种不同场景中应用并发挥良好的作用,主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其他系统任务以及数据的存储提取相分离,这种架构可以根据业务的需求选择合适的存储引擎
- 1)连接层
- 2)服务层
- 3)引擎层
- 4)存储层
5、Mysql存储引擎
1)用命令查看mysql现在已提供什么引擎
mysql> show engines;
2)看你的mysql当前默认的存储引擎
mysql> show variables like '%storage_engine';
对比项 | MyISAM | InnoDB |
---|---|---|
主外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作 | 行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作 |
缓存 | 只缓存索引,不缓存真实数据 | 不仅缓存索引还缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务 |
默认安装 | Y | Y |
二、索引优化分析
1、性能下降SQL慢、执行时间长、等待时间长
- 查询语句写的烂
- 索引失效(单值索引、复合索引)
- 关联查询太多join(设计缺陷或不得已的需求)
- 服务器调优及各个参数设置(缓冲、线程数等)
2、常见通用的Join查询
2.1、SQL执行顺序
1)手写
SELECT DISTINCT
< select_list >
FROM
< left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
< where_condition >
GROUP BY
< group_by_list >
HAVING
< having_condition >
ORDER BY
< order_by_condition >
LIMIT < limit number >
2)机读
1 FROM <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>
3)总结
2.2、Join图
2.3、建表SQL
CREATE TABLE `tbl_dept`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`locAdd` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_emp`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`deptld` INT(11) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `fk_dept_id` (`deptld`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptld`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptld) VALUES('Z3',1);
INSERT INTO tbl_emp(NAME,deptld) VALUES('Z4',1);
INSERT INTO tbl_emp(NAME,deptld) VALUES('Z5',1);
INSERT INTO tbl_emp(NAME,deptld) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptld) VALUES(`w6`,2);
INSERT INTO tbl_emp(NAME,deptld) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptld) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptld) VALUES('s9',5);
2.4、7种JOIN
3、索引简介
3.1、是什么
索引是帮助MySQL高效获取数据的数据结构
简单理解为:排好序的快速查找数据结构
我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。**其中聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引默认的都是使用B+树索引,统称索引。当然,除了B+树这种类型的索引之外,还有哈希索引(hash index)等
目的: 提高查找效率,可以类比字典。
3.2、优势、劣势
优势:
- 提高数据的检索效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低数据的排序成本,降低了CPU的消耗。
劣势:
- 实际索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引也是占空间的。一般而言,索引表占用空间是数据表的1.5倍。
- 虽然索引大大提高了查询速度,同时却降低了更新表的速度。如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或者优化查询
3.3、mysql 索引分类
分类:
- 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:即一个索引包含多个列
语法:
- 创建
CREATE [UNIQUE] INDEX indexName ON myTable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length))
- 删除
DROP INDEX [indexName] ON myTable;
- 查看
SHOW INDEX FROM tableName;
3.4、mysql 索引结构
1) BTree索引
2)Hash索引
3)full-text全文索引
4)R-Tree索引
3.5、哪些情况下需要或不需要创建索引
需要创建索引:
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录,还会更新索引,加重IO负担
- where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
不需要创建索引:
- 表记录太少
- 经常增删改的表
- Why:提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件
- 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意,如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果
4、性能分析
MySQL Query Optimizer
MySQL常见瓶颈
- CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈:top,free,iostat和vmstat来查看系统的性能状态
Explain
1)是什么:
- 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
2)能干嘛
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的应用
- 每张表有多少行被优化器查询
3)怎么玩
- Explain+SQL语句
- 执行计划包含的信息
4)各字段解释
id:
- select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
- 三种情况:
id相同,执行顺序由上至下
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
id相同不同,同时存在 - 衍生:DERIVED
select_type:
1)查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询
- SIMPLE:简单的select查询,查询中不包含子查询或者UNION
- PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为PRIMARY
- SUBQUERY:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里
- DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)。MySQL会递归执行这些子查询,把结果放在临时表里
- UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在FROM子句的子查询中,外层SELECT将被标记为:DERIVED
- UNION RESULT:从UNION表中获取结果的SELECT
table:
显示这一行的数据是关于哪些表的
type:
-
访问类型排序
type显示的是访问类型,是较为重要的一个指标,结果值从最好到最坏依次是:
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All -
显示查询使用了何种类型,从最好到最差依此是:
system>const>eq_ref>ref>range>index>All -
system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现,这个也可以忽略不计
-
const:表示通过索引一次就找到了,const用于比较primary key或则unique索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL就能将该查询转换为一个常量
-
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
-
ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
-
range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不会扫描全部索引
-
index:Full Index Scan,index与All区别为index类型只遍历索引树。这通常比All快,因为索引文件通常比数据文件小。(也就是说虽然all和index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)
-
all:Full Table Scan,将遍历全表以找到匹配的行
-
一般来说,得保证查询至少达到range级别,最好能达到ref。
possible_keys:
显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出。但不一定被查询实际使用
key:
实际使用的索引。如果为NULL,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中,不会出现在possible_keys列表中。(覆盖索引:查询的字段与建立的复合索引的个数一一吻合)
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段,外键关系建立索引
rows:
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra:
包含不适合在其他列中显示但十分重要的额外信息。
- Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”
- Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
- Using index:表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错!如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作
- Using where:表明使用了where过滤。
- Using join buffer:使用了连接缓存。
- impossible where:where子句的值总是false,不能用来获取任何元组。(查询语句中where的条件不可能被满足,恒为False)
- select tables optimized away:在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化
- distinct:优化distinct操作,在找到第一匹配的元组后即停止找相同值的动作
5、索引优化
5.1、单表索引优化分析
创建表:
- 建表SQL
CREATE TABLE IF NOT EXISTS article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
author_id INT(10) UNSIGNED NOT NULL,
category_id INT(10) UNSIGNED NOT NULL,
views INT(10) UNSIGNED NOT NULL,
comments INT(10) UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL,
content TEXT NOT NULL
);
INSERT INTO article(author_id,category_id,views,comments,title,content)
VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');
- 表中的测试数据
SELECT * FROM article;
+----+-----------+-------------+-------+----------+-------+---------+
| id | author_id | category_id | views | comments | title | content |
+----+-----------+-------------+-------+----------+-------+---------+
| 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 1 | 1 | 3 | 3 | 3 | 3 |
+----+-----------+-------------+-------+----------+-------+---------+
3 rows in set (0.00 sec)
查询案例:
5.2、两表索引优化
两表索引优化分析:主外键
创建表:
- 建表 SQL
CREATE TABLE IF NOT EXISTS class(
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE IF NOT EXISTS book(
bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
card INT(10) UNSIGNED NOT NULL,
PRIMARY KEY(bookid)
);
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));
查询案例:
实现两表的连接,连接条件是 class.card = book.card
SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+------+--------+------+
| id | card | bookid | card |
+----+------+--------+------+
| 1 | 12 | 6 | 12 |
| 2 | 13 | 8 | 13 |
| 2 | 13 | 9 | 13 |
| 2 | 13 | 12 | 13 |
| 3 | 12 | 6 | 12 |
| 4 | 17 | 3 | 17 |
| 5 | 11 | NULL | NULL |
| 6 | 3 | 4 | 3 |
| 7 | 1 | 2 | 1 |
| 7 | 1 | 11 | 1 |
| 7 | 1 | 15 | 1 |
| 8 | 16 | 1 | 16 |
| 8 | 16 | 18 | 16 |
| 9 | 17 | 3 | 17 |
| 10 | 16 | 1 | 16 |
| 10 | 16 | 18 | 16 |
| 11 | 9 | 17 | 9 |
| 12 | 17 | 3 | 17 |
| 13 | 18 | 7 | 18 |
| 14 | 16 | 1 | 16 |
| 14 | 16 | 18 | 16 |
| 15 | 7 | NULL | NULL |
| 16 | 8 | NULL | NULL |
| 17 | 19 | NULL | NULL |
| 18 | 9 | 17 | 9 |
| 19 | 6 | NULL | NULL |
| 20 | 5 | NULL | NULL |
| 21 | 6 | NULL | NULL |
+----+------+--------+------+
28 rows in set (0.00 sec)
- 使用 explain 分析 SQL 语句的性能,可以看到:驱动表是左表 class 表
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table