집 >데이터 베이스 >MySQL 튜토리얼 >MySQL8.0의 새로운 기능 요약(코드 포함)
이 기사는 MySQL 8.0의 새로운 기능(코드 포함)에 대한 요약을 제공합니다. 도움이 필요한 친구들이 참고할 수 있기를 바랍니다.
1. 기본 문자 집합이 latin1에서 utf8mb4로 변경되었습니다.
버전 8.0 이전에는 기본 문자 집합이 latin1이었고, 8.0 버전의 기본 문자 집합은 utf8mb4였고, utf8도 utf8mb4를 가리켰습니다. 기본적으로.
(권장: MySQL 튜토리얼)
2. 모든 MyISAM 시스템 테이블을 InnoDB 테이블로 교체
모든 시스템 테이블은 MyISAM을 수동으로 생성하지 않는 한 트랜잭션 innodb 테이블로 교체됩니다. 테이블.
# MySQL 5.7 mysql> select distinct(ENGINE) from information_schema.tables; +--------------------+ | ENGINE | +--------------------+ | MEMORY | | InnoDB | | MyISAM | | CSV | | PERFORMANCE_SCHEMA | | NULL | +--------------------+ 6 rows in set (0.00 sec) # MySQL 8.0 mysql> select distinct(ENGINE) from information_schema.tables; +--------------------+ | ENGINE | +--------------------+ | NULL | | InnoDB | | CSV | | PERFORMANCE_SCHEMA | +--------------------+ 4 rows in set (0.00 sec)
8.0 이전 버전에서는 자동 증가 기본 키 AUTO_INCREMENT의 값이 max(기본 키)+1보다 크면 MySQL을 다시 시작한 후 AUTO_INCREMENT=max(기본 키)+ 1이 재설정됩니다. 이 현상은 경우에 따라 비즈니스 기본 키 충돌이나 기타 찾기 어려운 문제로 이어질 수 있습니다. 자동 증가된 기본 키 재시작 및 재설정 문제는 매우 초기에 발견되었으며(https://bugs.mysql.com/bug.ph...) 버전 8.0에서는 AUTO_INCREMENT 값을 유지합니다. MySQL을 다시 시작한 후에는 변경되지 않습니다.
InnoDB 테이블의 DDL은 성공이든 롤백이든 트랜잭션 무결성을 지원합니다. DDL 작업 롤백 로그는 롤백 작업을 위해 데이터 사전 데이터 사전 테이블 mysql.innodb_ddl_log에 기록됩니다. 쇼 테이블을 통해서는 볼 수 없습니다. 매개변수를 설정하면 ddl 작업 로그가 mysql 오류 로그에 인쇄될 수 있습니다.
mysql> set global log_error_verbosity=3; mysql> set global innodb_print_ddl_logs=1; mysql> create table t1(c int) engine=innodb; # MySQL错误日志: 2018-06-26T11:25:25.817245+08:00 44 [Note] [MY-012473] [InnoDB] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=41, thread_id=44, space_id=6, old_file_path=./db/t1.ibd] 2018-06-26T11:25:25.817369+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 41 2018-06-26T11:25:25.819753+08:00 44 [Note] [MY-012477] [InnoDB] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=42, thread_id=44, table_id=1063, new_file_path=db/t1] 2018-06-26T11:25:25.819796+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 42 2018-06-26T11:25:25.820556+08:00 44 [Note] [MY-012472] [InnoDB] InnoDB: DDL log insert : [DDL record: FREE, id=43, thread_id=44, space_id=6, index_id=140, page_no=4] 2018-06-26T11:25:25.820594+08:00 44 [Note] [MY-012478] [InnoDB] InnoDB: DDL log delete : by id 43 2018-06-26T11:25:25.825743+08:00 44 [Note] [MY-012485] [InnoDB] InnoDB: DDL log post ddl : begin for thread id : 44 2018-06-26T11:25:25.825784+08:00 44 [Note] [MY-012486] [InnoDB] InnoDB: DDL log post ddl : end for thread id : 44
또 다른 예를 살펴보겠습니다. 라이브러리에는 t1 테이블이 하나만 있습니다. 테이블 t1, t2를 삭제하려고 하면 5.7에서 실행 시 오류가 보고되지만 t1 테이블이 삭제됩니다. , 8.0에서 실행 시 오류가 보고됩니다. 그러나 t1 테이블은 삭제되지 않았으며 이는 8.0 DDL 작업의 원자성을 증명합니다. 모든 작업이 성공하거나 롤백됩니다.
# MySQL 5.7 mysql> show tables; +---------------+ | Tables_in_db | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> drop table t1, t2; ERROR 1051 (42S02): Unknown table 'db.t2' mysql> show tables; Empty set (0.00 sec) # MySQL 8.0 mysql> show tables; +---------------+ | Tables_in_db | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec) mysql> drop table t1, t2; ERROR 1051 (42S02): Unknown table 'db.t2' mysql> show tables; +---------------+ | Tables_in_db | +---------------+ | t1 | +---------------+ 1 row in set (0.00 sec)
MySQL 버전 8.0은 PERSIST 키워드를 추가하여 MySQL을 다시 시작할 때 수정된 매개변수를 새로운 구성 파일(mysqld-auto.cnf)에 유지할 수 있습니다. , 최신 구성 매개변수는 이 구성 파일에서 얻을 수 있습니다.
예를 들어 다음을 실행합니다.
set PERSIST 만료_logs_days=10;
시스템은 데이터 디렉터리에 mysqld-auto.cnf가 포함된 파일을 생성합니다. auto.cnf가 동시에 존재하는 경우 후자가 더 높은 우선순위를 갖습니다.
{ "Version": 1, "mysql_server": { "expire_logs_days": { "Value": "10", "Metadata": { "Timestamp": 1529657078851627, "User": "root", "Host": "localhost" } } } }
MySQL은 오랫동안 구문상 내림차순 인덱스를 지원해 왔지만, 실제로는 아래 MySQL 5.7에서 볼 수 있듯이 여전히 오름차순 인덱스를 생성하지만 c2 필드는 내림차순입니다. show create table에서 볼 수 있지만 여전히 오름차순입니다. 8.0에서는 c2 필드가 내림차순으로 정렬되어 있음을 알 수 있습니다.
# MySQL 5.7 mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc)); Query OK, 0 rows affected (0.03 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`,`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) # MySQL 8.0 mysql> create table t1(c1 int,c2 int,index idx_c1_c2(c1,c2 desc)); Query OK, 0 rows affected (0.06 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, KEY `idx_c1_c2` (`c1`,`c2` DESC) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC 1 row in set (0.00 sec)
실행 계획에서 내림차순 인덱스의 성능을 살펴보겠습니다. t1 테이블에 임의의 데이터 100,000개를 삽입하고 c1, c2 desc;별로 select * from t1 order의 실행 계획을 살펴보겠습니다. 실행계획을 보면 5.7의 스캔번호 100113이 8.0의 5라인보다 훨씬 크고, filesort를 사용하고 있음을 알 수 있다.
DELIMITER ;; CREATE PROCEDURE test_insert () BEGIN DECLARE i INT DEFAULT 1; WHILE i<100000 DO insert into t1 select rand()*100000, rand()*100000; SET i=i+1; END WHILE ; commit; END;; DELIMITER ; CALL test_insert(); # MySQL 5.7 mysql> explain select * from t1 order by c1 , c2 desc limit 5; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100113 | 100.00 | Using index; Using filesort | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+ 1 row in set, 1 warning (0.00 sec) # MySQL 8.0 mysql> explain select * from t1 order by c1 , c2 desc limit 5; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
내림차순 인덱스는 쿼리의 특정 정렬 순서에만 유효합니다. 부적절하게 사용하면 쿼리 효율성이 낮아집니다. 예를 들어 위 쿼리 정렬 조건은 c1 desc, c2 desc 순서로 변경됩니다. , 5.7의 실행 계획은 다음과 같이 분명히 8.0보다 좋아야 합니다:
# MySQL 5.7 mysql> explain select * from t1 order by c1 desc , c2 desc limit 5; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 5 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) # MySQL 8.0 mysql> explain select * from t1 order by c1 desc , c2 desc limit 5; +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+ | 1 | SIMPLE | t1 | NULL | index | NULL | idx_c1_c2 | 10 | NULL | 100429 | 100.00 | Using index; Using filesort | +----+-------------+-------+------------+-------+---------------+-----------+---------+------+--------+----------+-----------------------------+ 1 row in set, 1 warning (0.01 sec)
mysql 8.0은 더 이상 그룹 기준 필드에 대해 암시적으로 정렬되지 않습니다. by 절을 명시적으로 추가해야 합니다.
# 表结构 mysql> show create table tb1\G *************************** 1. row *************************** Table: tb1 Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `group_own` int(11) DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC 1 row in set (0.00 sec) # 表数据 mysql> select * from tb1; +----+------+-----------+ | id | name | group_own | +----+------+-----------+ | 1 | 1 | 0 | | 2 | 2 | 0 | | 3 | 3 | 0 | | 4 | 4 | 0 | | 5 | 5 | 5 | | 8 | 8 | 1 | | 10 | 10 | 5 | +----+------+-----------+ 7 rows in set (0.00 sec) # MySQL 5.7 mysql> select count(id), group_own from tb1 group by group_own; +-----------+-----------+ | count(id) | group_own | +-----------+-----------+ | 4 | 0 | | 1 | 1 | | 2 | 5 | +-----------+-----------+ 3 rows in set (0.00 sec) # MySQL 8.0.11 mysql> select count(id), group_own from tb1 group by group_own; +-----------+-----------+ | count(id) | group_own | +-----------+-----------+ | 4 | 0 | | 2 | 5 | | 1 | 1 | +-----------+-----------+ 3 rows in set (0.00 sec) # MySQL 8.0.11显式地加上order by进行排序 mysql> select count(id), group_own from tb1 group by group_own order by group_own; +-----------+-----------+ | count(id) | group_own | +-----------+-----------+ | 4 | 0 | | 1 | 1 | | 2 | 5 | +-----------+-----------+ 3 rows in set (0.00 sec)
MySQL 8은 JSON_EXTRACT() 함수를 추가하여 경로 쿼리 매개변수를 기반으로 JSON 필드에서 데이터를 추출하고 데이터를 각각 JSON 배열과 객체로 결합하는 등 JSON에 대한 지원을 크게 향상시켰습니다. .NET의 JSON_ARRAYAGG() 및 JSON_OBJECTAGG() 집계 함수
마스터-슬레이브 복제에서는 JSON 데이터의 전송 방법을 제어하기 위해 새로운 매개변수 binlog_row_value_options가 추가되었습니다. 이를 통해 수정된 부분만 binlog에 기록되므로 대규모 리소스에 미치는 영향이 줄어듭니다. 직업이 약간만 수정된 경우 json 데이터입니다.
재실행 및 실행 취소 로그의 암호화를 제어하려면 다음 두 매개변수를 추가하세요.
innodb_undo_log_encrypt
innodb_undo_log_encrypt
업데이트용 ... 선택, 공유용 ... 선택(8.0 새 구문) NOWAIT, SKIP LOCKED 구문을 추가하여 잠금 대기를 건너뛰거나 잠금 건너뛰기를 수행합니다.
버전 5.7 이하에서는 업데이트를 위해...를 선택하세요. 잠금을 얻을 수 없으면 innodb_lock_wait_timeout 시간이 초과될 때까지 기다립니다.
버전 8.0에서는 nowait를 추가하고 잠긴 구문을 건너뛰면 즉시 복귀할 수 있습니다. 쿼리된 행이 잠긴 경우 nowait는 즉시 오류를 반환하고, 건너뛰기 잠김도 즉시 반환하지만 반환된 결과에는 잠긴 행이 포함되지 않습니다.
# session1: mysql> begin; mysql> select * from t1 where c1 = 2 for update; +------+-------+ | c1 | c2 | +------+-------+ | 2 | 60530 | | 2 | 24678 | +------+-------+ 2 rows in set (0.00 sec) # session2: mysql> select * from t1 where c1 = 2 for update nowait; ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set. mysql> select * from t1 where c1 = 2 for update skip locked; Empty set (0.00 sec)
SQL 구문에 SET_VAR 구문을 추가하여 일부 매개변수를 동적으로 조정하면 명령문 성능이 향상됩니다.
使用INVISIBLE关键字在创建表或者进行表变更中设置索引是否可见。索引不可见只是在查询时优化器不使用该索引,即使使用force index,优化器也不会使用该索引,同时优化器也不会报索引不存在的错误,因为索引仍然真实存在,在必要时,也可以快速的恢复成可见。
# 创建不可见索引 create table t2(c1 int,c2 int,index idx_c1_c2(c1,c2 desc) invisible ); # 索引可见 alter table t2 alter index idx_c1_c2 visible; # 索引不可见 alter table t2 alter index idx_c1_c2 invisible;
优化器会利用column_statistics的数据,判断字段的值的分布,得到更准确的执行计划。
可以使用 ANALYZE TABLE table_name [UPDATE HISTOGRAM on col_name with N BUCKETS |DROP HISTOGRAM ON clo_name] 来收集或者删除直方图信息。
直方图统计了表中某些字段的数据分布情况,为优化选择高效的执行计划提供参考,直方图与索引有着本质的区别,维护一个索引有代价。每一次的insert、update、delete都会需要更新索引,会对性能有一定的影响。而直方图一次创建永不更新,除非明确去更新它。所以不会影响insert、update、delete的性能。
# 添加/更新直方图 mysql> analyze table t1 update histogram on c1, c2 with 32 buckets; +--------+-----------+----------+-----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------+-----------+----------+-----------------------------------------------+ | db.t1 | histogram | status | Histogram statistics created for column 'c1'. | | db.t1 | histogram | status | Histogram statistics created for column 'c2'. | +--------+-----------+----------+-----------------------------------------------+ 2 rows in set (2.57 sec) # 删除直方图 mysql> analyze table t1 drop histogram on c1, c2; +--------+-----------+----------+-----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------+-----------+----------+-----------------------------------------------+ | db.t1 | histogram | status | Histogram statistics removed for column 'c1'. | | db.t1 | histogram | status | Histogram statistics removed for column 'c2'. | +--------+-----------+----------+-----------------------------------------------+ 2 rows in set (0.13 sec)
能够让InnoDB根据服务器上检测到的内存大小自动配置innodb_buffer_pool_size,innodb_log_file_size,innodb_flush_method三个参数。
在错误信息中添加了错误信息编号[MY-010311]和错误所属子系统[Server]
# MySQL 5.7 2018-06-08T09:07:20.114585+08:00 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode. 2018-06-08T09:07:20.117848+08:00 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode. 2018-06-08T09:07:20.117868+08:00 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode. # MySQL 8.0 2018-06-21T17:53:13.040295+08:00 28 [Warning] [MY-010311] [Server] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode. 2018-06-21T17:53:13.040520+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode. 2018-06-21T17:53:13.040542+08:00 28 [Warning] [MY-010330] [Server] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
MySQL 8.0新增了一个资源组功能,用于调控线程优先级以及绑定CPU核。
MySQL用户需要有 RESOURCE_GROUP_ADMIN权限才能创建、修改、删除资源组。
在Linux环境下,MySQL进程需要有 CAP_SYS_NICE 权限才能使用资源组完整功能。
[root@localhost~]# sudo setcap cap_sys_nice+ep /usr/local/mysql8.0/bin/mysqld [root@localhost~]# getcap /usr/local/mysql8.0/bin/mysqld /usr/local/mysql8.0/bin/mysqld = cap_sys_nice+ep
默认提供两个资源组,分别是USR_default,SYS_default
创建资源组:
create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5;
将当前线程加入资源组:
SET RESOURCE GROUP test_resouce_group;
将某个线程加入资源组:
SET RESOURCE GROUP test_resouce_group FOR thread_id;
查看资源组里有哪些线程:
select * from Performance_Schema.threads where RESOURCE_GROUP='test_resouce_group';
修改资源组:
alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8;
删除资源组 :
drop resource group test_resouce_group;
# 创建资源组 mysql>create resource group test_resouce_group type=USER vcpu=0,1 thread_priority=5; Query OK, 0 rows affected (0.03 sec) mysql> select * from RESOURCE_GROUPS; +---------------------+---------------------+------------------------+----------+-----------------+ | RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY | +---------------------+---------------------+------------------------+----------+-----------------+ | USR_default | USER | 1 | 0-3 | 0 | | SYS_default | SYSTEM | 1 | 0-3 | 0 | | test_resouce_group | USER | 1 | 0-1 | 5 | +---------------------+---------------------+------------------------+----------+-----------------+ 3 rows in set (0.00 sec) # 把线程id为60的线程加入到资源组test_resouce_group中,线程id可通过Performance_Schema.threads获取 mysql> SET RESOURCE GROUP test_resouce_group FOR 60; Query OK, 0 rows affected (0.00 sec) # 资源组里有线程时,删除资源组报错 mysql> drop resource group test_resouce_group; ERROR 3656 (HY000): Resource group test_resouce_group is busy. # 修改资源组 mysql> alter resource group test_resouce_group vcpu = 2,3 THREAD_PRIORITY = 8; Query OK, 0 rows affected (0.10 sec) mysql> select * from RESOURCE_GROUPS; +---------------------+---------------------+------------------------+----------+-----------------+ | RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS | THREAD_PRIORITY | +---------------------+---------------------+------------------------+----------+-----------------+ | USR_default | USER | 1 | 0-3 | 0 | | SYS_default | SYSTEM | 1 | 0-3 | 0 | | test_resouce_group | USER | 1 | 2-3 | 8 | +---------------------+---------------------+------------------------+----------+-----------------+ 3 rows in set (0.00 sec) # 把资源组里的线程移出到默认资源组USR_default mysql> SET RESOURCE GROUP USR_default FOR 60; Query OK, 0 rows affected (0.00 sec) # 删除资源组 mysql> drop resource group test_resouce_group; Query OK, 0 rows affected (0.04 sec)
角色可以认为是一些权限的集合,为用户赋予统一的角色,权限的修改直接通过角色来进行,无需为每个用户单独授权。
# 创建角色 mysql> create role role_test; Query OK, 0 rows affected (0.03 sec) # 给角色授予权限 mysql> grant select on db.* to 'role_test'; Query OK, 0 rows affected (0.10 sec) # 创建用户 mysql> create user 'read_user'@'%' identified by '123456'; Query OK, 0 rows affected (0.09 sec) # 给用户赋予角色 mysql> grant 'role_test' to 'read_user'@'%'; Query OK, 0 rows affected (0.02 sec) # 给角色role_test增加insert权限 mysql> grant insert on db.* to 'role_test'; Query OK, 0 rows affected (0.08 sec) # 给角色role_test删除insert权限 mysql> revoke insert on db.* from 'role_test'; Query OK, 0 rows affected (0.10 sec) # 查看默认角色信息 mysql> select * from mysql.default_roles; +------+-----------+-------------------+-------------------+ | HOST | USER | DEFAULT_ROLE_HOST | DEFAULT_ROLE_USER | +------+-----------+-------------------+-------------------+ | % | read_user | % | role_test | +------+-----------+-------------------+-------------------+ 1 row in set (0.00 sec) # 查看角色与用户关系 mysql> select * from mysql.role_edges; +-----------+-----------+---------+-----------+-------------------+ | FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | +-----------+-----------+---------+-----------+-------------------+ | % | role_test | % | read_user | N | +-----------+-----------+---------+-----------+-------------------+ 1 row in set (0.00 sec) # 删除角色 mysql> drop role role_test; Query OK, 0 rows affected (0.06 sec)
위 내용은 MySQL8.0의 새로운 기능 요약(코드 포함)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!