mysql高级

mysql中文乱码问题

1. 修改配置文件
2. 修改已经存在的数据库的配置文件

alter database mydb character set 'utf-8'

3. 修改已经存在表的配置文件

alter table mytb convert to character set 'utf-8'

查询每个机构年龄最大的人

CREATE TABlE mytbl2 (id INT,NAME VARCHAR(200), age INT,dept INT);
INSERT iNTO mytbl2 VALUES(1,'zhang3 ' ,33,101);
iNSERT INTO mytbl2 VALUES(2,'li4',34,101);
iNSERT INTo mytbl2 VALUES(3,'wangs',34,102);
INSERT INTO mytbl2 VALUES(4, ' zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);
-- 每个机构年龄最大   的人
-- 错误的 :5.7之后会报错
SELECT NAME dept, MAX(age) FROM mytbl2 GROUP BY dept ;

-- 正确的 
SELECT * FROM mytbl2 m iNNER JOIN(
	SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.dept AND m.age=ab.maxage

mysql的底层逻辑架构

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，
插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

mysql的存储引擎

myISAM与InnoDB的区别

创建一张表默认的存储引擎是InnoDB
系统表用的myISAM

两台不同的服务器上的表进行联合查询

在AB服务器之间再次增加一个数据中心服务器进行数据处理

性能下降SQL慢，执行时间长，等待时间长，优化

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

关联查询

名称	语句图解
内连接
左外连接
右外连接（实际工作中很少用到，经常用左外连接，因为工作中左右连接只能选择一种，不能在一个查询语句中都用，所以我么实际生活中经常使用左外连接）
取A表独有的数据
全连接（mysql没有全连接，只有Oracle有）
去同

索引简介

是什么

MySQL官方对索引的定义为：索引(Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。
你可以简单理解为"排好序的快速查找数据结构”。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

劣势

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

索引的结构

BTree索引

原理图
非叶子节点包含数据	1、数据 2.、向下的指针 3、指向数据的指针
时间复杂度

B+Tree索引

原理图
非叶子节点包含数据	1、数据 2.、向下的指针

聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。
如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

索引的分类

# 查看t_emp表的索引字段
SHOW INDEX FROM t_emp;

单值索引

即一个索引只包含单个列，一个表可以有多个单列索引

# 随表一起建索引customer_name
CREATE TABLE customer (
	id INT(10)UNSIGNED AUTO_INCREMENT,
	customer_no VARCHAR(200),
	customer_name VARCHAR(200),
	PRIMARY KEY(id),
	KEY(customer_name)
);
# 给t_emp表的NAME字段创建idx_name 单值索引
CREATE INDEX idx_name ON t_emp(NAME);
# 删除索引:
DROP INDEX idx_name on t_emp;

唯一索引

索引列的值必须唯一，但允许有空值

CREATE UNIQUE INDEX idx_empno ON t_emp(empno);
# 查看t_emp表的索引字段
SHOW INDEX FROM t_emp;

主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

复合索引

即一个索引包含多个列

CREATE INDEX idx_age_deptid_name ON t_emp(age,deptid,NAME);

# 查看t_emp表的索引字段
SHOW INDEX FROM t_emp;

哪些情况下需要建立索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题，组合索引性价比更高
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

哪些情况下不需要建立索引

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

性能分析Explain

是什么（查看执行计划）

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

能干什么

1. 表的读取顺序
2. 哪些索引可以使用
3. 数据读取操作的操作类型
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被物理查询

怎么用

Explain + SQL语句

如何在一张表中插入一百万条数据，速度更快一些？

方法一

1. 使用java语言拼接字符，使多条INSERT INTO 语句变成一条INSERT INTO 语句

INSERT INTO `category` (`id`, `name`, `parent_id`, `sorted`, `level`, `create_time`, `deleted`) VALUES
	(1, '互联网营销', 0, 1, 1, '2020-08-21 18:06:03', 0),
	(2, 'UI/UE交互设计', 0, 2, 1, '2020-08-21 18:08:28', 0),
	(3, 'Web前端', 0, 3, 1, '2020-08-21 18:08:45', 0),
	(4, '程序开发', 0, 4, 1, '2020-08-21 18:08:58', 0),
	(5, '三维可视化设计', 0, 5, 1, '2020-08-21 18:09:13', 0),
	(6, 'office', 0, 6, 1, '2020-08-21 18:09:23', 0)
	.......
	.......;

方法二

索引的优势是查询，排序快，但是插入，写操作会降低速度。所以我们在执行插入100万条数据之前，可以先把这张表的除主键索引外，其他索引都暂时删除掉。

方法三

关闭自动提交，当一百万条数据都执行完，改为手动提交，只提交一次。

方法四

java多线程插入数据。

本文地址：https://blog.csdn.net/qq_43925089/article/details/108775774