mysql入门篇

基本操作

登入与登出

#.不显示列名
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock -e"select @@hostname;" --skip-column-names 2>/dev/null

#.本地登录
mysql -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3306.sock

#.远程登录
mysql -h 192.168.31.104 -P3306 -udba_admin -pcf_rB1NKCzbaQuPH

实例的管理

#.查看mysql版本号.
mysql --version
select version();
show variables like '%version%';

#.查看安装的mysql
rpm -qa | grep -i mysql 

#.查看mysqld启动时的缺省选项
mysqld --print-defaults

#.查看配置文件
locate my.cnf
mysql --verbose --help | grep -A 1 'Default options'

#.查看服务启动时间
SELECT 'start_time' AS 'VARIABLE_NAME', NOW() - INTERVAL (SELECT variable_value 
FROM performance_schema.global_status 
WHERE variable_name = 'Uptime') SECOND AS VARIABLE_VALUE;

#.服务的启动
mysqld_safe --defaults-file=/etc/my.cnf &
systemctl start mysql
systemctl start mariadb           #.针对mariadb
service mysql start               #.老式的服务启停
/etc/init.d/mysqld start

#.服务的停止
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock shutdown
systemctl stop mysql
systemctl stop mariadb           #.针对mariadb
service mysql stop               #.老式的服务启停
/etc/init.d/mysqld stop

#.服务重载
mysqladmin -udba_admin -pcf_rB1NKCzbaQuPH -S /tmp/mysql_3406.sock reload

#.其他
show variables like '%char%';    #.查看字符集
show variables like '%port%';    #.查看端口号

#.查看mysql的登录历史
history | grep mysql

#.查看mysql执行的sql历史记录
sed 's/\\040/ /g' ~/.mysql_history

Windows系统

:: 启动服务
cd /d "C:\Program Files\MySQL\MySQL Server 5.7\bin"
mysqld.exe --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini"

数据库的管理

#.查看当前库名
select database();

#.查看数据库清单
show databases;
select schema_name from information_schema.schemata order by schema_name;
select distinct table_schema from information_schema.tables order by table_schema;

#.建库+建账号
CREATE DATABASE if not exists ppdai_pay_merchant DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
create user 'user_pay_merchant'@'%' identified by 'g9TPD_yQC3KWhm7o';
grant select,insert,update,execute on ppdai_pay_merchant.* to 'user_pay_merchant'@'%';
flush privileges;

#.修改库名
create database if not exists bbb;
select concat('rename table aaa.',table_name,' to bbb.',table_name,';') from information_schema.TABLES where TABLE_SCHEMA='aaa';

#.修改库名(5.1.7新增,5.1.23移除,据说会丢失数据)
# RENAME DATABASE aaa TO bbb

#.删除库
drop database if exists xxx

针对库的批量操作：

#.查看库大小
select table_schema
,round(sum(data_length+index_length)/1024/1024,2) as total_size_mb
,round(sum(data_length)/1024/1024,2) as data_size_mb
,round(sum(index_length)/1024/1024,2) as index_size_mb
from information_schema.tables
where table_schema not in('information_schema','mysql','percona_schema','performance_schema','sys')
group by table_schema
order by sum(data_length+index_length) desc limit 200;

#.批量加权限给user_etl_bi
select distinct concat('grant select on `',table_schema,'`.* to ''user_etl_bi''@''%'';') 
from information_schema.tables where table_schema like 'cloud%' order by table_schema;

#.批量删除
select concat('drop database if exists `',schema_name,'`;') from information_schema.schemata 
where schema_name not in('information_schema','mysql','percona_schema','performance_schema','sys') order by schema_name;

数据表的管理

#.创建表: 语法 CREATE [temporary] TABLE [ if not exists] [库名.]表名 (表的结构定义)[表选项];
CREATE TABLE IF NOT EXISTS users (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'PK',
  user_id bigint(20) NOT NULL comment '用户id',
  user_name varchar(10) NOT NULL comment '用户姓名',
  insert_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '插入时间',
  update_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  is_deleted tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除状态位,1删除0保留',
  PRIMARY KEY pk_users(id), 
  KEY idx_insert_time(insert_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户基础表' /*# Owner: 张开发; Manager: 李经理 #*/ ;

#.创建临时表
DROP TEMPORARY TABLE IF EXISTS temptable;
CREATE TEMPORARY TABLE temptable(id int);
INSERT INTO temptable (id) VALUES (11),(12),(13);

#.创建内存表
CREATE TEMPORARY TABLE memtable(id int) TYPE='HEAP';

#.查看当前db的数据表清单
select database();
show tables;

#.改表名
ALTER TABLE aaa RENAME TO bbb;

#.复制完整表结构和全部索引，有索引但无数据
CREATE TABLE 新表名 LIKE 要复制的表名;

#.复制表结构和数据，但没有索引，且开启gtid会报语法错误
CREATE TABLE 新表名 [AS] SELECT * FROM 要复制的表名;

#.删除表
DROP TABLE bbb;

#.修改表的注释
alter table bbb comment '新的表注释';

#.修改字段的注释
alter table bbb modify column user_id int comment '新的字段注释'; 

#.优化.
optimize table xxx;                       #.优化表(包括释放data_free空间)
analyze table xxxx;                       #.更新统计信息(校正 information_schema.tables.table_rows 信息)
alter table xxxx analyze partition p2;    #.更新某个分区的统计信息

#.快速返回行数
SELECT SQL_CALC_FOUND_ROWS 1 FROM xxxx limit 1; SELECT found_rows() AS rowcount;

#.查看指定库/表/视图/函数的创建语句
show create [database / table / view / function] 库名/表名/视图名/函数名;

#.显示当前数据库中所有表的名称
show tables;
show tables from database_name;
show create table student;

#.查看表
desc xxxx;
show tables like 'memory%';
show tables like 'memory%' from db001;
select table_schema,table_name,engine,table_rows,data_free,create_time,update_time,table_comment from information_schema.tables where table_schema='mysql';

针对表的批量操作：

#.比对表大小：
#.实测 information_schema.tables 返回的table_rows和DATA_LENGTH不一定准确
#.执行 analyze table xxxx; 更正统计信息后准确
select table_schema,table_name,table_rows
,sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as total_size
,sum(DATA_LENGTH)/1024/1024/1024 as data_size
,sum(INDEX_LENGTH)/1024/1024/1024 as index_size
,sum(DATA_FREE)/1024/1024/1024 as free_size 
from information_schema.tables where table_schema=database() group by table_schema,table_name order by table_rows desc limit 10;

#.找出可用空间最多的表,以便收缩表
select table_schema,table_name,concat('optimize table ',table_name,';') as optimize_table,table_rows
,sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as total_size
,sum(DATA_LENGTH)/1024/1024/1024 as data_size
,sum(INDEX_LENGTH)/1024/1024/1024 as index_size
,sum(DATA_FREE)/1024/1024/1024 as free_size 
from information_schema.tables where table_schema <> 'information_schema' 
group by table_schema,table_name order by DATA_FREE desc limit 20;

#.返回某个表的字段清单
select table_schema,table_name,column_name,column_type,column_comment,collation_name 
from information_schema.columns where table_name='ida_user' order by table_schema,table_name,ORDINAL_POSITION;

#.返回某个表的字段清单-DataX格式
select GROUP_CONCAT(column_name) from information_schema.columns where table_name='ida_user' order by ORDINAL_POSITION;
select GROUP_CONCAT("""",column_name,"""") from information_schema.columns where table_name='ida_user' order by ORDINAL_POSITION;

#.批量删除当前库下的表
select concat('drop table ',table_name,';') from information_schema.tables where table_schema=database() and table_name like '%2019%' order by table_name;
select concat(group_concat('drop table ', table_name separator ';'),';') from information_schema.tables where table_schema=database() order by table_name;

#.批量清空当前库的所有表
select concat(group_concat('truncate table ', table_name separator ';'),';') from information_schema.tables where table_schema=database() order by table_name;
select concat(group_concat('delete from ', table_name separator ';'),';') from information_schema.tables where table_schema=database() order by table_name;

#.批量清空当前库的所有表(排除空表)
select concat(group_concat('truncate table ', table_name separator ';'),';') from information_schema.tables where table_schema=database() and table_rows>0 order by table_name;

字段的管理

#.新增字段，使用 FIRST、BEFORE、AFTER 指定新列的位置
ALTER TABLE `aaa` ADD COLUMN `id` int DEFAULT 0 COMMENT '编号' FIRST;
ALTER TABLE `aaa` ADD COLUMN `name` VARCHAR(128) NOT NULL COMMENT '姓名' AFTER `id`;
ALTER TABLE `aaa` ADD COLUMN `birth` VARCHAR(128) NOT NULL COMMENT '生日' BEFORE `name`;

#.添加多个字段，建议用一条语句完成
ALTER TABLE `comment_usercomment` 
   ADD COLUMN `opeartorid` int DEFAULT 0 COMMENT '操作人编号'
  ,ADD COLUMN `operatorrole` varchar(200) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '操作人角色'
  ,ADD COLUMN `operatortime` timestamp(3) NULL DEFAULT CURRENT_TIMESTAMP(3)  COMMENT '操作时间';

#.新增自增字段+主键
ALTER TABLE aaa ADD COLUMN id INT NOT NULL AUTO_INCREMENT COMMENT '主键' FIRST;
ALTER TABLE aaa ADD PRIMARY KEY(id);
#.合起来就是  ALTER TABLE aaa ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键' FIRST;

#.使用 MODIFY 只能修改字段类型
ALTER TABLE aaa MODIFY COLUMN id int DEFAULT 0 COMMENT '编号';

#.使用 CHANGE 可以修改字段名称、类型、顺序(比如将c6调整到c2后面)
ALTER TABLE aaa CHANGE id id BIGINT NOT NULL;
ALTER TABLE aaa CHANGE COLUMN name name_new VARCHAR(20);
ALTER TABLE aaa CHANGE c6 c6 int(8) DEFAULT '0' AFTER c2;

#.查看某张表的字段
show columns from mysql.user;

#.删除字段
ALTER TABLE aaa drop COLUMN `columeName`;

关于 auto_increment 自增

#.auto_increment数据列必须有唯一索引，以避免序号重复；必须具备NOT NULL属性
#.若把auto_increment声明为UNSIGNED，这样序列编号可增加一倍。比如tinyint最大编号是127，加上UNSIGNED则变为255

#.查看 auto_increment 列<下一行>的自增值
select auto_increment from information_schema.tables where table_schema = database() and table_name='testid';

#.调整 auto_increment 自动增长约束(下一行的自增id为n,而不是n+1)
alter table aaa auto_increment = 10000;

#.调整 auto_increment_increment 漂移值(步长)
show variables like 'auto_increment%';
SET GLOBAL auto_increment_increment=10;
select @@auto_increment_increment;

#.实测的坑
#.innoDB引擎的表，执行delete清空之后表的auto_increment值不受影响；一旦重启Mysql数据库，auto_increment值将变成1； 执行truncate table(等于drop+create)清空之后表的auto_increment值将变为1；
#.MyISAM引擎的表，执行delete清空之后表的auto_increment值不受影响；重启Mysql数据库，auto_increment值不会受到影响；执行truncate table(等于drop+create)清空之后表的auto_increment值将变为1；

DROP TABLE IF EXISTS testid;
CREATE TABLE testid (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', isactive tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态位',PRIMARY KEY (id)) ENGINE=MyISAM AUTO_INCREMENT=1000;
CREATE TABLE testid (id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', isactive tinyint(1) NOT NULL DEFAULT '1' COMMENT '状态位',PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=1000;
INSERT INTO testid(isactive) VALUES(1);

#.为什么会出现上述情况呢？
#.这是因为，Mysql数据库的的auto_increment值是保存在内存中的，innoDB引擎的表的auto_increment在数据库服务停止时并不会做持久化操作，Mysql会在下次数据库重启的时候，相当于通过执行语句：
  select max(id) maxId from table;
  alter table auto_increment = maxId + 1;
来设置表table的auto_increment值。严格意义上来说这是Mysql的一个bug。这个bug将会在8.0版本中得到修复。

#.TRUNCATE与DELETE的区别：
  1．truncate 是删表再创建，delete 是逐条删除；
  2．truncate 重置auto_increment的值。而delete不会；
  3．truncate 不知道删除了几条，而delete知道；
  4．当被用于带分区的表时，truncate 会保留分区；

增删改查

#.插入
insert into tablename values(1,2),(1,3),(1,4);
replace into xxx select xxx;

#.利用load导入外部txt数据
mysql test0505 -e "load data infile '/tmp/r2.txt' into table test2;"

#.不是以数字开头
select * from mot_terms where name not REGEXP '^[0-9]'

#.不是以字母开头
select * from mot_terms where name not REGEXP '^[a-zA-Z]'

#.不查表显示
select '第1第1列' as '标题1','第1行第2列' as '标题2' union all select '第2第1列','第2行第2列';

#.以数字和特殊字符开头
select * from mot_terms where name REGEXP '^[@#$%&0-9]'

#.查找重复值
select card_number,count(1) as cnt from ucenter_device where serve_time >= '2020-05-06 00:00:00' group by card_number having count(*)>1;

#.批量更新
update top (1000) UserLogo set sLogoUrl = REPLACE(sLogoUrl,'http://p5.img.ymatou.com','http://pic1.ymatou.com/p5img')  
where sLogoUrl like 'http://p5.img.ymatou.com%'

#.更新语法
update t_user_001 a inner join t_basis_all b on a.account_id = b.account_id set a.status = b.status where a.account_id = 254;

#.批量删除
DELETE FROM history_uint WHERE clock<1480608000 LIMIT 10000;

账号的管理

#.创建业务账号 user_sam_register
CREATE USER 'user_sam_register'@'%' IDENTIFIED BY 'MXn896_VwTiek2_t';
GRANT SELECT,INSERT,UPDATE ON fusion_sam_register.* TO 'user_sam_register'@'%';
FLUSH PRIVILEGES;

#.创建备用管理员 dba_admin
CREATE USER IF NOT EXISTS 'dba_admin'@'%' IDENTIFIED BY 'cf_rB1NKCzbaQuPH';
GRANT ALL PRIVILEGES ON *.* TO 'dba_admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

#.创建复制账号 dba_repl
CREATE USER 'dba_repl'@'%' IDENTIFIED BY 'MA6RuouuZZn4x_Hd';
GRANT SELECT, RELOAD, FILE, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'dba_repl'@'%';
FLUSH PRIVILEGES;

#.创建大数据-抽取账号 user_bigdata_readonly
CREATE USER 'user_bigdata_readonly'@'%' IDENTIFIED BY 'dw_iuDIfQzWgVb9G';
GRANT SELECT ON fusion_sam_register.* TO 'user_bigdata_readonly'@'%';
FLUSH PRIVILEGES;

#.创建大数据-推送账号 user_bigdata_push
CREATE USER 'user_bigdata_push'@'%' IDENTIFIED BY 'dw_RX2a_6GemCf7u';
GRANT SELECT,INSERT,UPDATE,SHOW VIEW ON fusion_sam_register.* TO 'user_bigdata_push'@'%';
FLUSH PRIVILEGES;

#.通过加密串创建账号
create user 'catuser'@'%' identified by password '*DE48EDE8FA13F7FD90E8AD2DCAC433675620AC08';
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON cat.* TO 'catuser'@'%';
flush privileges;

#.初装mysql重置密码.
set password = password('uatMysql');
update mysql.user set host='%' where user='root' and host='localhost';
flush privileges;

#.mysql8更改密码
alter user 'root'@'localhost' identified with mysql_native_password by 'Aa@123456';
flush privileges;

#.有drop权限的账号.
select user,host,Drop_priv from mysql.user where Drop_priv='Y' and user not like 'dba_%';

#.查看当前登录的账号
select user();

#.重命名帐号(即可生效,不需flush privileges)
rename user 'root'@'localhost' to 'sa2'@'localhost';

#.禁用用户帐号.
update mysql.user set host = 'localhost' where user = 'root' and host='%';
flush privileges;

#.禁用/启用账号
ALTER USER 'jeffrey'@'%' ACCOUNT LOCK;
ALTER USER 'jeffrey'@'%' ACCOUNT UNLOCK;
flush privileges;

#.删除账号(即可生效,不需flush privileges)
delete from mysql.user where user='' or host='::1';
drop user 'testuser'@'10.1.6.%';

grant与revoke

#.重新授权
REVOKE ALL PRIVILEGES ON fusion_sam.* FROM 'user_sam'@'%';
GRANT SELECT,INSERT,UPDATE ON fusion_sam.* TO 'user_sam'@'%';
FLUSH PRIVILEGES;

#.授权格式
grant select,execute on dbmonitor.* to 'dba_test'@'%';
grant select,execute on dbmonitor.* to 'dba_test'@'10.%' identified by 'Aa123456';
flush privileges;

#.增加创建临时表权限
grant create temporary tables ON *.* TO 'user_pay_account'@'%';
flush privileges;

#.撤销权限
grant create temporary tables ON *.* TO 'user_pay_account'@'%';
revoke create temporary tables on *.* from 'user_pay_account'@'%';

#.增加建表权限
GRANT CREATE ON ppdai_cbd_boar.* TO 'user_cbd_boar'@'%';
flush privileges;

#.回收所有权限
REVOKE ALL PRIVILEGES ON ppdai_pay_tpad_order_01.* FROM 'dba_test'@'%';

针对账号的批量操作：

#.批量加权限给 user_bigdata_readonly
create user 'user_bigdata_readonly'@'%' identified by 'dw_iuDIfQzWgVb9G';
select concat('grant select on `',schema_name,'`.* to ''user_bigdata_readonly''@''%'';') from information_schema.schemata where schema_name not in ('information_schema','mysql','percona_schema','performance_schema','sys') order by schema_name;

#.批量迁移账号
select user,host, concat('CREATE USER ','''',user,'''@''',host,'''',' IDENTIFIED BY PASSWORD ','''',authentication_string,'''',';') from mysql.user where account_locked='N';
select concat('SHOW GRANTS FOR ','''',user,'''@''',host,'''',';') from mysql.user where account_locked='N';

#.批量禁用/启用账号
select user,host,account_locked,case when account_locked = 'Y' then '已禁用' when account_locked = 'N' then '启用' end as account_locked_desc from mysql.user;
select concat('ALTER USER ','''',user,'''@''',host,'''',' ACCOUNT LOCK;') from mysql.user where account_locked='N' and user not in ('root','dba_admin','dba_repl');

#.批量删除帐号(即可生效,不需flush privileges)
select concat('DROP USER ','''',user,'''@''',host,'''',';') from mysql.user where user like 'user%';

会话与连接数

#.统计.总的连接数
show processlist;
select count(1) from information_schema.processlist;
select @@max_connections,@@max_user_connections,count(*) as 'current_connections' from information_schema.processlist;

#.统计.每个账号的连接次数
SELECT USER,count(1) as cnt FROM information_schema.processlist
GROUP BY USER ORDER BY cnt DESC;

#.统计.每个db的连接次数
SELECT DB,count(1) as cnt FROM information_schema.processlist
GROUP BY DB ORDER BY cnt DESC LIMIT 20;

#.统计.每个IP的连接数
select left(host,instr(host,':')-1) as ip,count(1) as cnt from information_schema.processlist where user not like 'system%' 
group by left(host,instr(host,':')-1) order by cnt desc;

#.统计.每个账号、每个ip的连接次数
select USER,left(host,instr(host,':')-1),count(1) as cnt from information_schema.processlist
where command ='Sleep'
group by USER,left(host,instr(host,':')-1) order by cnt desc limit 20;

#.统计.每个ip、每个db的连接次数
select left(host,instr(host,':')-1),db,count(1) as cnt from information_schema.processlist
where command ='Sleep'
group by left(host,instr(host,':')-1),db order by cnt desc limit 20;

#.Threads_running是代表当前并发数
show global status where variable_name in ('Threads_running','Threads_connected');

#.查看关于连接数的配置
show variables where variable_name in ('max_connections','max_user_connections'); 
show global status where variable_name in ('Aborted_connects','Connections','Max_used_connections','Threads_connected');

针对会话的批量操作：

#.批量kill进程-使用指定user访问指定db,并排除管理员账号
select concat('kill ',id,';') from information_schema.processlist where db='ppdai_pay' and user='user_pay';
select concat(group_concat('kill ', ID separator ';'),';') from information_schema.processlist where user not in ('root','dba_admin','dba_repl');
select concat('kill ',id,';') from information_schema.processlist where user not in ('root','dba_admin') into outfile '/tmp/kill.sql';
source /tmp/kill.sql

事务

begin;
  insert into xxxx value(1);
commit;

存储过程与函数

以下是创建存储过程的一个示例：

#.create database
CREATE DATABASE IF NOT EXISTS bak;
USE bak;

#.create proc
DELIMITER $$
USE `bak`$$
DROP PROCEDURE IF EXISTS `proc_demo`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_demo`()
BEGIN
    SELECT user,host FROM mysql.user;
END$$
DELIMITER ;

关于存储过程与函数的常用操作：

#.查看存储过程清单
show procedure status where db='bak'; 

#.查看当前库的存储过程清单
SELECT routine_schema, routine_name, routine_type, definer,created, last_altered 
FROM information_schema.routines 
WHERE routine_type='PROCEDURE' and routine_schema=database();  

#.查找包含特定字符的存储过程
SELECT routine_schema, routine_name, routine_type, definer,created, last_altered 
FROM information_schema.routines 
WHERE routine_type='PROCEDURE' and routine_definition like '%mysql.user%';

#.查看特定存储过程的创建脚本
show create procedure proc_demo;

#.执行存储过程
call bak.proc_demo;

索引与调优

索引的创建与删除

#.创建索引
ALTER TABLE aaa1 ADD INDEX idx_host_port (host,port);       #.联合索引
ALTER TABLE aaa1 ADD PRIMARY KEY (id);                      #.主键=聚集索引
ALTER TABLE aaa1 ADD UNIQUE udx_id (id);                    #.唯一索引
ALTER TABLE aaa1 ADD INDEX idx_host(host(6));               #.前缀索引.在执行order by和group by时不起作用,也无法使用前缀索引做覆盖扫描

#.创建索引
ALTER TABLE aaa2 ADD PRIMARY KEY (id), ADD UNIQUE idx_aaa2_id (id);   #.可以不指定索引名字,支持同时创建多个
CREATE INDEX idx_aaa2_host_port ON aaa2 (host,port)                   #.必须指定索引的名称

#.针对前通配可以使用reverse函数索引（不是翻转键索引）
#.正常查询  SELECT * FROM t WHERE t.NAME LIKE '%张三';
#.创建索引  CREATE INDEX IDX2_t ON t(REVERSE(NAME));
#.改写语句  SELECT * FROM t WHERE REVERSE(t.NAME) LIKE '三张%';

#.查看索引
SHOW INDEXES FROM aaa1;
SHOW INDEXES FROM mysql.user;
SELECT INDEX_NAME,INDEX_TYPE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME='aaa1'; 

#.删除索引
ALTER TABLE aaa2 DROP PRIMARY KEY;
ALTER TABLE aaa2 DROP INDEX idx_aaa2_id;
DROP INDEX idx_aaa2_host_port ON aaa2;

#.查看执行计划
explain SELECT host,port,connnum FROM aaa1 WHERE port=30001 ORDER BY id DESC LIMIT 1; 

#.强制指定索引
SELECT host,port,connnum FROM aaa1 force index(idx_aaa1_host) WHERE port=30001 ORDER BY id DESC LIMIT 1;
explain SELECT host,port,connnum FROM aaa1 force index(idx_aaa1_host) WHERE port=30001 ORDER BY id DESC LIMIT 1;
explain SELECT host,port,connnum FROM aaa1 force index(`PRIMARY`) WHERE port=30001 ORDER BY id DESC LIMIT 1;

#.查看冗余索引最多的30张表（超过4个以上冗余字段）
select distinct table_schema,table_name,redundant_index_name,redundant_index_columns,sql_drop_index 
from sys.schema_redundant_indexes where length(redundant_index_columns)-length(replace(redundant_index_columns,',',''))+1 >= 4 limit 30;

#.查看索引最多的30张表（超过6个以上索引）
select database_name,table_name,count(*) as index_count 
from mysql.innodb_index_stats 
where stat_name='size' 
group by database_name,table_name 
having count(*) > 6 
order by index_count desc limit 30;

#.查看占用字段最多的30个索引（超过6个以上字段）
select database_name,table_name,index_name,count(*)-1 as column_count 
from mysql.innodb_index_stats 
where stat_value=0 
group by database_name,table_name,index_name 
having count(*) > 6
order by column_count desc limit 30;

假如设置了 log_queries_not_using_indexes=ON; 则会在慢日志看到很多 throttle: 2570 'index not used' warning (s) suppressed，可临时放开节流，在慢日志看到完整的全表扫描满 SQL。

注：如果业务全表扫描极多，慢日志会暴涨、占用大量磁盘！排查完一定要改回原来的值

-- 查看慢日志设置
show variables where variable_name in ('slow_query_log','slow_query_log_file','log_queries_not_using_indexes');

-- 查看每分钟最多记录多少条未使用索引的SQL
show variables like 'log_throttle_queries_not_using_indexes';

-- 关闭节流（0=不限制，所有未走索引SQL全部写入慢日志）
SET GLOBAL log_throttle_queries_not_using_indexes = 0;

-- 恢复限流, 排查完一定要改回原来的值, 示例改为 10
SET GLOBAL log_throttle_queries_not_using_indexes = 10;

关于全文索引

#.现在student行数 2410968
select * from student where name like '%黄琬凝%';

#.普通索引耗时2.09秒,全文索引耗时1.91秒,效果并不明显
ALTER TABLE student ADD FULLTEXT INDEX ft_idx_name (name) WITH PARSER ngram;

外键

如果一个实体的某个字段指向另一个实体的主键，就称为外键。
被指向的实体，称之为主实体（主表），也叫父实体（父表）。
负责指向的实体，称之为从实体（从表），也叫子实体（子表）

#.禁用与启用外键
SET foreign_key_checks = 0;
#..............................
SET foreign_key_checks = 1;

#.查看具有外键的表
select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' limit 10;

#.创建测试表和数据
drop table if exists student;
drop table if exists class;
create table student ( student_id int not null primary key, student_name varchar(10), class_id int );
create table class ( class_id int not null primary key, class_name char(10) not null, student_id int not null );
insert into student values(10001,'张三',1),(10002,'李四',2);
insert into class values(1,'语文',10001),(2,'数学',10002);
select * from student;
select * from class;

#.级联update操作：
alter table student add foreign key (class_id) references class (class_id) on delete cascade on update cascade;
update class set class_id=22 where class_name='数学';

#.级联delete操作：在删除外键时，将从表的外键值设置为null
alter table student add foreign key (class_id) references class (class_id) on delete set null;

其他

variables

#.查看配置参数
show global variables where variable_name in ('tmp_table_size','max_heap_table_size'); 

#.查看运行指标
show global status where variable_name in ('Created_tmp_tables','Created_tmp_disk_tables');

mysql 8 支持通过 PERSIST 进行全局参数持久化，写入 mysqld-auto.cnf

SET PERSIST innodb_buffer_pool_size=402653184;

几个常用的变量修改：

#.1.修改最大连接数(max_user_connections 限制每个用户的session连接个数; 而 max_connections 是对整个实例的用户限制)
show variables where variable_name in ('max_connections','max_user_connections'); 
set global max_connections=3000; 
set global max_user_connections=2500;

#.2.加快导入速度(导入mysqldump之前执行)
set global innodb_flush_log_at_trx_commit=0;
set global sync_binlog=500000;
show variables where variable_name in ('innodb_flush_log_at_trx_commit','sync_binlog'); 

#.3.修改当前会话的sql_mode(不改动全局配置)
set @@sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
select @@sql_mode;

#.4.动态调整Innodb_Buffer_Pool_size(会触发缓存清理, 5.7.5以上即刻生效)
SELECT @@innodb_buffer_pool_size,@@innodb_buffer_pool_chunk_size,@@innodb_buffer_pool_instances;
SELECT 1024*1024*1024*5;
SET GLOBAL innodb_buffer_pool_size=1024*1024*1024*5;
SELECT @@innodb_buffer_pool_size,@@innodb_buffer_pool_chunk_size,@@innodb_buffer_pool_instances;

区分会话变量与全局变量：

语句	级别	生效范围	生命周期
show variables	SESSION (会话)	当前连接	-
show session variables	SESSION (会话)	当前连接	-
show global variables	GLOBAL (全局)	所有新建的连接	-
set @@wait_timeout=10;	SESSION (会话)	当前连接 ✔, 其他连接 ✖	断开连接恢复原值
set wait_timeout=10;	SESSION (会话)	当前连接 ✔, 其他连接 ✖	断开连接恢复原值
set @@session.wait_timeout=10;	SESSION (会话)	当前连接 ✔, 其他连接 ✖	断开连接恢复原值
set session wait_timeout=10;	SESSION (会话)	当前连接 ✔, 其他连接 ✖	断开连接恢复原值
set global wait_timeout=10;	GLOBAL (全局)	之后新建的连接 ✔, 已经在线的连接 ✖	重启MySQL失效
set @@global.wait_timeout=10;	GLOBAL (全局)	之后新建的连接 ✔, 已经在线的连接 ✖	重启MySQL失效

SET 变量赋值

SET @xxx='aaa';
select @xxx;

#.变量赋值
set @new = md5('Admin_147');
select @new;
update szdw_user set passwd = @new where login_name ='sam_admin';

mysql入门篇

mysql入门篇

基本操作

登入与登出

实例的管理

数据库的管理

数据表的管理

字段的管理

关于 auto_increment 自增

增删改查

账号的管理

会话与连接数

事务

存储过程与函数

索引与调优

索引的创建与删除

外键

其他

variables

SET 变量赋值

results matching ""

No results matching ""