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 多数据源整合

实现原理：拦截