mysql高级

MYSQL高级
Linux安装(RPM安装):
mysql服务端 注意:需要设置root用户密码。
mysql客户端
安装成功标志: mysqladmin --version #打印mysql版本信息

为了保证数据库目录为与文件的所有者为mysql登录用户,如果你是以root身份运行mysql服务,需要执行初始化命令。
mysqld --initialize --user=mysql
另外 --initialize选项默认以安全模式来初始化,则会为root用户生成一个密码并将该密码标记为过期,登录后,你需要设置一个新的密码。
查看初始密码: cat /var/log/mysqld.log

找到初始密码:
[root@localhost ~]# cat /var/log/mysqld.log |grep password
2019-06-02T08:39:38.448115Z 1 [Note] A temporary password is generated for root@localhost: dLZMCRv?s2q)
2019-06-02T08:40:41.870130Z 2 [Note] Access denied for user ‘root’@‘localhost’ (using password: NO)

登陆mysql:
[root@localhost ~]# mysql -u root -p
密码为上面找到的dLZMCRv?s2q)

修改设置密码的验证规则
set global validate_password_policy=0;
set global validate_password_length=1;

设置密码
SET PASSWORD = PASSWORD(‘密码’);

允许远程连接:
use mysql;
select Host,User from user \G;
update user set Host = ‘%’ where Host = ‘localhost’ and User = ‘root’;
FLUSH PRIVILEGES;

启动mysql:
service mysql start

停止mysql:
service mysql stop

查看mysql状态
systemctl status mysql

退出mysql客户端
exit

重启mysql
systemctl restart mysqld

top 命令,查看linux系统运行状态信息。例如运行时长

设置开机自启:
chkconfig mysql on

mysql服务默认自启动。
systemctl list-unit-files| grep mysqld

注意:mysql客户端中,执行sql指令需要 ; 分号结尾。执行 mysql客户端指令不需要 ;结尾也可以。

mysql中文乱码
1 修改配置文件:
vim /etc/my.cnf
character_set_server=utf8

2 修改已存在库,表的字符集。
alter database mydb character set ‘utf8’;
alter table mytbl convert to character set ‘utf8’;

mysql服务器开启远程连接:
创建用户:
create user 用户名 identified by ‘123123’;
授权:创建用户并授予所有权限( . ),允许用户任意主机远程连接mysql服务器 % ,用户密码:123456
grant all privileges on . to 用户名@’%’ identified by ‘123456’;

修改当前用户的密码:
set password=password(‘123456’)

修改某个用户的密码:(mysql.user ,系统库.系统表)
update mysql.user set password=password(‘123456’) where user=‘li4’;
flush privileges; #所有通过user表的修改,必须用该命令才能生效。

mysql密码安全策略设置(重启服务后,设置失效):
set global validate_password_policy=0;
set global validate_password_length=1;

注意:
group by 使用原则:select 后面只能放函数和group by 后的字段。

mysql底层原理:逻辑架构。执行过程
1 类似java程序访问mysql数据库。
2 访问语句与mysql连接池连接。
3 查询语句,先从mysql缓存中,查询是否有匹配值。(key,value: 查询语句sql作为key,进行匹配查询)
4 缓存中查找不到数据,从SQL接口分析sql.
5 解析器对复杂sql解析
6 优化器,不影响结果前提下,对sql进行优化。生成执行计划。
7 存储引擎按执行计划,进行查询。
8 返回查询结果给客户端(Java程序),并把查询结果(key,value),放入一份到缓存中。

注意:不建议表之间外键关系建立,操作麻烦,易出错,降低运行效率。

存储引擎:
MySQL中的数据用各种不同的技术存储在文件(或者内存)中。
这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。
通过选择不同的技术,你能够获得额外的速度或者功能,从而改善你的应用的整体功能

MyISAM 和 InnoDB(mysql默认使用存储引擎,存储机制B+Tree,平衡树)区别:
外键:不支持,支持
事务: 不支持,支持
行表锁: 表锁,即使操作一条记录也会锁住整个表,不适合高并发的操作。
行锁,操作时只锁某一行,不对其它行有影响,适合高并发的操作。但是:行锁会发生死锁。
缓存:
只缓存索引,不缓存真实数据
不仅缓存索引还要缓存真实数据,对内存要求较高,而且内存大小对性能有决定性的影响。

索引优化分析:
性能下降SQL慢
执行时间长
等待时间长

1 数据过多: 分库分表
2 关联了太多的表,太多join :SQL优化
3 没有充分利用到索引:索引建立
4 服务器调优及各个参数设置:调整my.cnf

表数据拼接(前提条件: select 字段列名一致,数量一致,顺序一致):
union all 不去重
union 去重

索引:索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质是,数据结构。

优势:
数据查询快
降低数据排序成本

劣势:
数据更新除了更新数据本身,还有维护对索引进行更新。
占用更多存储空间(索引文件)

索引数据结构:B+树。(平衡树一种)

平衡树有两种结构:
B树:
叶子节点数据结构:
1 数据
2 向下指针
3 指向数据的指针

B+树
叶子节点数据结构:
1 数据
2 向下指针

B+树与B树相比:占用更小内存,每次加载进入内存,的节点更多。
B+树可以有效的减少IO次数,更快速的获取需要的节点

时间复杂度:
O(N)
O(1)
O(logN) ->logN 默认以2为底。 2的3次方等于8。log8=3

聚簇索引:聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。术语’聚簇’ 表示数据行
和相邻的键值,聚簇的存储在一起。

非聚簇索引:术语’非聚簇’ 表示数据行。和索引的键值,分散分布。
MySQL数据库中innodb存储引擎,B+树索引可以分为聚簇索引(也称聚集索引,clustered index)和辅助索引(有时也称非聚簇索引或二级索引,secondary index,non-clustered index)。
这两种索引内部都是B+树,聚集索引的叶子节点存放着一整行的数据。
Innobd中的主键索引是一种聚簇索引,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引。

索引:
单值索引(普通索引) 单个字段
create Index 索引名 on 表名(列名);
唯一索引 单个字段
create unique index 索引名 on 表名(列名);
主键索引 单个字段
创建表或者设置表,某个字段为主键时自动创建。
复合索引 多个字段,组成一个索引
create Index 索引名 on 表名(列名1,列名2,列名3);

创建索引:
create Index 索引名 on 表名(列名);
查看索引:
show index from 表名;

创建索引情况:
1 主键自动建立唯一索引
2 频繁作为查询条件的字段应该创建索引
3 查询中与其它表关联的字段,外键关系建立索引。 两表关联字段需要建立索引,重要优化措施
4 单键/组合索引 的选择问题,组合索引性价比更高
5 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度 order by 列名 (建立索引,重要优化措施)
6 查询中统计或者分组字段 group by 列名 (建立索引,重要优化措施)

不建议创建索引情况:
1 表记录太少
2 经常增删改的表或者字段
3 where 条件里用不到的字段不创建索引
4 过滤性不好的不适合建索引

Explain: 查看执行计划。
使用Explain关键字可以模拟优化器(MySQL的逻辑架构中的优化器)执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句
或是表结构的性能瓶颈。

使用:返回结果为sql分析报告
Explain SQL语句;

分析报告,重要字段解析:

id 有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,越先执行。
注意:每个不同id值,代表一次独立查询。

type 显示的是访问类型,是较为重要的一个指标,如下表,从上到下效率越来越低,一般要求达到range或ref即可。
type 含义
NULL MySQL不访问任何表,索引,直接返回结果
system 表只有一行记录(等于系统表),这是const类型的特例,一般不会出现
const 表示通过索引一次就找到了,const 用于比较primary key 或者 unique 索引。因为只匹配一行数据,所以很快。如将主键置于where列表中,MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询,关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref 非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个)
range 只检索给定返回的行,使用一个索引来选择行。 where 之后出现 between , < , > , in 等操作。
index index 与 ALL的区别为 index 类型只是遍历了索引树, 通常比ALL 快, ALL 是遍历数据文件。
all 将遍历全表以找到匹配的行

explain 之 rows
实际扫描行的数量。越少越好。

explain 之 extra
其他的额外的执行计划信息,在该列展示 。

extra含义
using filesort order by 列名(排序列名未使用索引) 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取, 称为 “文件排序”, 效率低。
using temporary groub by 列名(分组列名未使用索引) 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by; 效率低
using index order by 列名 和 group by 列名 使用上了索引 表示相应的select操作使用了覆盖索引, 避免访问表的数据行, 效率不错。

key_len含义
命中索引的长度,表示查询过程中使用了多少个索引。长度越大使用索引越多。性能越高。

数据库优化:
建立索引:注意建立索引的限制,什么会导致索引失效问题。
where 字段使用函数,<>,is null, like %* (前面使用%),使索引失效等问题。

复合索引:建立索引全值更高效。
建立复合索引时,注意建立字段索引的顺序,对索引的影响(会导致部分索引字段失效)
例如:字段值为范围查询时,要放在复合索引的最后。否则导致范围查询字段后的索引字段失效。

复合索引:命中分层级,依据定义顺序,进行依次命中。(必须按照定义顺序进行命中,逐级命中,无法跨级命中。中间断开,则无法进行下一步)
命中越多字段,效率越高。

关联查询:
left join 注意: 驱动表与被驱动表位置。 左表,右表。左表无法避免全表扫描,右表建立索引得到优化效果。
注意:虚拟表无法建立索引
注意:能关联查询,不要使用子查询。

order by :无过滤,不索引。加上limt ,where 等order by 索引字段,才会生效。
顺序错,必排序。顺序:建立索引顺序. 必排序(索引失效)
方向反,必排序。 order 字段1 asc, 字段2 desc 。 必排序(索引失效)
注意:order by 索引字段限制条件,否则会导致索引失效问题。

列出全部人员,并增加一列备注“是否为掌门”,如果是掌门人显示是,不是掌门人显示否
select a.name,case when b.id is null then ‘否’ else ‘是’ end ‘是否为掌门’
from t_emp a
left join t_dept b on a.id=b.ceo;

列出全部门派,并增加一列备注“老鸟or菜鸟”,若门派的平均值年龄>50显示“老鸟”,否则显示“菜鸟”
select b.deptName
if (avg(a.age)>50,‘老鸟’,‘菜鸟’) as ‘老鸟or菜鸟’
from t_emp a
inner join t_dept b on a.deptId=b.id
group by b.id,b.deptName

优化sql语句定位:
慢查询日志:sql执行时间,超过设置阈值。则写入此日志中。默认:10s
默认关闭
show variables like ‘%slow_query_log%’;

开启慢查询日志:
set global slow_query_log=1;
查看慢查询日志默认时间:
show variables like ‘long_query_time%’;
设置慢的阈值时间(单位秒)
set long_query_time=1;

慢查询日志存放位置:cd /var/lib/mysql/mysql.sock

全局日志:默认关闭,查询无法定位sql问题。开启此日志,进行分析。

主从复制:

mysql单表数据瓶颈:500万。解决方案:
垂直/水平 分 库/表。
读写分离

Mycat
数据库中间件
1 读写分离
2 数据分片: 数据分库,分表(主键配置:1 数据库配置 2 自主生成)
3 多数据源整合

实现原理:拦截

限时福利1:原价 129 元,最后2天仅需 69 元!后天涨价至98元 限时福利2:购课进答疑群专享柳峰(刘运强)老师答疑服务 限时福利3:购课添加助教领取价值 800 元的编程大礼包 为什么需要掌握高性能的MySQL实战? 由于互联网产品用户量大、高并发请求场景多,因此对MySQL的性能、可用性、扩展性都提出了很高的要求。使用MySQL解决大量数据以及高并发请求已经是程序员的必备技能,也是衡量一个程序员能力和薪资的标准之一。 为了让大家快速系统了解高性能MySQL核心知识全貌,我为你总结了「高性能 MySQL 知识框架图」,帮你梳理学习重点,建议收藏! 【课程设计】 课程分为四大篇章,将为你建立完整的 MySQL 知识体系,同时将重点讲解 MySQL 底层运行原理、数据库的性能调优、高并发、海量业务处理、面试解析等。 一、性能优化篇: 主要包括经典 MySQL 问题剖析、索引底层原理和事务与锁机制。通过深入理解 MySQL 的索引结构 B+Tree ,学员能够从根本上弄懂为什么有些 SQL 走索引、有些不走索引,从而彻底掌握索引的使用和优化技巧,能够避开很多实战遇到的“坑”。 二、MySQL 8.0新特性篇: 主要包括窗口函数和通用表表达式。企业的许多报表统计需求,如果不采用窗口函数,用普通的 SQL 语句是很难实现的。 三、高性能架构篇: 主要包括主从复制和读写分离。在企业的生产环境,很少采用单台MySQL节点的情况,因为一旦单个节点发生故障,整个系统都不可用,后果往往不堪设想,因此掌握高可用架构的实现是非常有必要的。 四、面试篇: 程序员获得工作的第一步,就是高效的准备面试,面试篇主要从知识点回顾总结的角度出发,结合程序员面试高频MySQL问题精讲精练,帮助程序员吊打面试官,获得心仪的工作机会。
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页