MySQL高级

MySQL 2.0

https://blog.csdn.net/qq_16183731/article/details/86516468

Linux 下 mysql安装

centOS7 安装  尽量图形
安装JDK
查看版本
java  -version 
https://dev.mysql.com/downloads/mysql/
查看当前是否有mysql

解压
tar -xvf [Mysql] -C [mysql]/

mysql配置
安装位置  /usr/local/mysql
数据文件目录  /mysql/data
占用端口   3306
socket 存放位置  tmp/mysql.sock  客户端与服务端的通信文件

运行软件需要用户和用户组  linux下软件的运行需要特定的用户
添加mysql 用户和mysql 用户组，设置  /usr/local/mysql 权限
没有指定用户组会创建同名的用户组
useradd -r -s /sbin/nologin mysql
修改权限
chown -R mysql.mysql /usr/local/mysql
查看是否成功
ll /usr/local/mysql


初始化库
进入mysql安装目录  
移除影响文件   yum remove mariadb-libs
/usr/local/mysql/scripts/mysql_install_db --user=mysql
查看数据是否初始化
ll data

添加mysql 启动管理命令到系统
cp support-files/mysql.server  /etc/init.d/mysql

启动服务
service mysql start

设置密码
bin/mysqladmin -u root password '123'
查看是否成功

添加密令到系统
ln  -s   /usr/local/mysql/bin/mysql    /usr/bin
mysql - u root -p 
123

netstat -tunlp |grep 3306

授权远程登陆
grant all privileges on *.* to 'root' @'%' identified by '123';
关闭防火墙
systemctl stop firewalld

可以使用

- 参数配置文件

log-bin 二进制日志  主从复制

log-error 错误日志

查询日志log  记录慢查询

数据文件
windows mysql\data
Linux 默认在 /var/lib/mysql 官方文档推荐 usr/local/mysql/data


- 如何配置
windows my.ini
linux /etc/my.conf

ps -ef|grep mysql

jdk 安装

下载rpm包

rpm -qa|grep jdk 
rpm -e --nodeps [名字]
rpm -ivh rpm包  安装

环境变量

/etc/profile

存储引擎

1，主要有两种 InnoDB MyISAM

在创建数据库时，库选项，需要指定字符集和校对规则

在创建表的时候，表选项

• 存储引擎
  • ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
• 字符集，表未指定使用数据库默认字符集
• 索引文件目录
  • INDEX DIRECTORY = '目录'
• 分区文件
  • PARTITION BY ...
• 表注释
  • COMMENT '部门ID' – 加到字段后面即可
• 自增起始数
  • AUTO_INCREMENT = 行数

2，两种存储引擎的区别和选择

查看MySQL支持的存储引擎 show engines;

查看默认的存储引擎 show variables like '%storage_engine%';

查看表的存储引擎 show table status like "table_name" ;

存储引擎区别

mysql > 5.5 InnoDB（事务性数据库引擎）

mysql <5.5 默认是 MyISAM

各种特性也还不错（比如全文索引、压缩、空间函数等）。但是，MyISAM 不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。

区别

• 是否支持行级锁、事务、外键 （MyISAM只支持表级锁,InnoDB都支持）
• 是否支持异常崩溃后的安全恢复
• 是否支持MVCC
• 有效减少加锁操作，提供性能
• 存储文件不同

mysql 数据库
存储引擎是myisam, 在data目录下会看到3类文件：.frm、.myi、.myd
（1）*.frm–表定义，是描述表结构的文件。

（2）*.MYD–"D"数据信息文件，是表的数据文件。

（3）*.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树

存储引擎是InnoDB, 在data目录下会看到2类文件：.frm、.ibd、.opt
（1）*.frm–表结构的文件。

（2）*.ibd–表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。

(3) .opt 文件是字符集和校对字符集

存储锁 InnoDB

• Record lock 记录锁
• Gap lock 间隙锁 （锁定范围）
• Next-key lock: record lock +gap 临键锁 ，锁定范围包括记录

查询缓存

-- my.cnf  开启缓存
query_cache_type=1
query_cache_size=600000
-- 或者
set global  query_cache_type=1;
set global  query_cache_size=600000;

事务

1，数据库事务 ，分布式事务

2，什么是事务

• 多个数据库指令，要么全执行，要么都不执行
• 具有四个特性 ACID
• 原子性
• 一致性
• 隔离性
• 持久性
• redo log 日志保证 持久性，使用undo log 保证原子性
• 锁机制保证 MVCC等 保证 隔离性
• 通过上面两个才能保证一致性

start transaction
commit  /  rollback

四个隔离级别

读未提交

读已提交

可重复读

串行化

-- 设置隔离级别
-– READ-UNCOMMITTED
-– READ-COMMITTED
-– REPEATABLE-READ
–- SERIALIZABLE

  transaction-isolation = READ-COMMITTED
  
  	-–	READ UNCOMMITTED
  	–-	READ COMMITTED
  	–-	REPEATABLE READ
  	–-	SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

数据库的分类

关系型 MYSQL 非关系型 Redis Memcache 分布式文件存储 MongoDB

MYSQL 索引

1， 索引是什么，优缺点？

• 用于快速查询和检索数据的数据结构，常用数据结构，b+树和 hash
• 优缺点
  • 加快大量数据的查询速度
  • 创建唯一索引，保证每行数据的唯一
  • 建立索引可以加快表与表之间的连接
  • 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序
  • 创建和维护需要成本（存储索引的空间，修改数据，动态修改索引）
  • 大数据量才能体现优势

2，使用索引

-- 主键索引
ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` );

-- 唯一索引
-- 唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）。
ALTER TABLE `table_name` ADD UNIQUE ( `column` );

-- 普通索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` );

-- 添加全文索引
-- 由于模糊查询需要，查询全表，所以建立全文索引，直接在索引中寻找，模糊查询的对象
-- 注意： 默认 MySQL 不支持中文全文检索！
-- MySQL 全文搜索只是一个临时方案，对于全文搜索场景，
-- 更专业的做法是使用全文搜索引擎，例如 ElasticSearch 或 Solr。
ALTER TABLE `table_name` ADD FULLTEXT ( `column` );

-- 添加多列索引
-- 复合索引遵守“最左前缀”原则
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`,`column2`,`column3` );

-- 创建表的时候

CREATE TABLE `news` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` varchar(255)  NOT NULL ,
    `content` varchar(255)  NULL ,
    `time` varchar(20) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(255))
)

DROP INDEX index_name ON table_name;

alter table `表名` drop index 索引名;

-- 查看索引使用情况
show status like ‘Handler_read%’;
-- handler_read_key:这个值越高越好，越高表示使用索引查询到的次数
-- handler_read_rnd_next:这个值越高，说明查询低效
-- 在where后使用or，导致索引失效（尽量少用or）

-- 对于查询索引

# 使用了index_name_email索引
EXPLAIN select * from students where email like '742981086@qq.com%'

# 没有使用index_name_email索引，索引失效
EXPLAIN select * from students where email like '%742981086@qq.com'

# 没有使用index_name_email索引，索引失效
EXPLAIN select * from students where email like '%742981086@qq.com%'

-- 最左匹配
create index index_email_phone on students(email,phone);

3，这里为什么要使用 EXPLAIN 关键字

EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

4，关于数据结构

mysql默认使用B+Tree，5.5版本之前使用B-Tree

一个sql语句在mysql里究竟是如何运行的呢？又是怎么去查找的呢？
其中就涉及到数据库（存储数据）以及查找算法。

• 查找算法：
  • 目录查找：索引
  • 遍历：暴力查找，尽量避免的方式
  • 二分：B+树的基础算法
  • 键查找：hash查找 ，hash冲突是个难题

5，为什么是b+tree

能做索引的数据结构有：数组、链表、红黑树、B树(B-树、B+树)。
那么哪种数据结构适合做 MySql 数据库的存储

• 链表数据占用空间，查找效率不稳定
• 那么多树， 为什么偏偏最后选择了B+Tree？
  • 二叉排序树结构在极端的情况下会退化成链表，降低效率
  • 红黑树的效率很高但是大数据量下
    • 树的再平衡会很麻烦（左旋和右旋）
    • 树还是属于二叉树，深度会很高
  • hash 不能部分查找和范围查找，但是非这两种情况控制好冲突效率很高

6，B+树具体实现结构

数据结构演示地址

N阶的BTree的几个重要特性：
节点最多含有N颗子树(指针)，N-1个关键字(数据存储空间) (N>=2);
除了根节点和叶子节点外，其它每个节点至少有M=N/2个子节点，
M向上取整，即分裂的时候从中间分开，分成M棵子树；
若根节点不是叶子结点，则至少有两颗子树
b-tree N=3



b+tree N=3