MySQL 常用技能

原创

半路出学

发布于 2022-01-12 19:26:12

4080

发布于 2022-01-12 19:26:12

文章被收录于专栏：深入浅出测开技术

1. 常用操作语句

连接

mysql -hxx -Pxx -uxx  -pxx

建表

 CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT '0',
  `pos` varchar(30) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_pos_phone` (`name`,`age`,`pos`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

操作表

select  *  from student left join score on student.id=score.s_id order by score_val desc  limit 15

select DISTINCT course_name from student_table where grade=1;

select * ，count(b) as sum  from table  where a='' group by s having sum>10 order by c desc  limit

select s.id, c.name from student s left join course c on s.id=c.student_id

insert into table (a,b) values (1,2)

delete from table where id>10

update table set a=1 where id=1

alter table app_apply add log_index varchar(255) default Null //变更、增加字段
alter table 表名 add column 列名 varchar(20) not null first;
alter table 表名 add column 列名 varchar(20) not null after user1;
alter table app_apply drop column def ;
alter table app_apply change  def unit char;

聚合函数

–COUNT：统计行数量

–SUM：获取单个列的合计值

–AVG：计算某个列的平均值

–MAX：计算列的最大值

–MIN：计算列的最小值

SELECT student_class,AVG(student_age) AS 平均年龄 FROM t_student GROUP BY (student_class) HAVING AVG(student_age)>20;

添加索引

//添加主键索引 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

//添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`) 

//添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

//添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 

//添加组合索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

##2. 慢查询

查看数据库状态

status;
show tables;
select * from information_schema.tables where table_name ='table_name'
show variables  like 'port'; 
show status like 'Threads%'; //查看数据库当前连接数，并发数
show variables like '%max_connections%'; //查看最大连接数
set global max_commections = 1000; //修改最大连接数 
show grants for 'root'@'localhost'; // 查看某个具体用户的权限

索引失效的情况

如果索引有多列（联合索引a,b,c）遵守最左前缀法则，即查询从索引的最左前列开始并且不跳过索引中的列
索引范围条件右边的列，< 、>索引范围条件右边的索引列会失效
select *
索引列上做操作，如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描
关键字 or 、not in 、!=、<>不等于
使用LIKE关键字的查询语句，如以通配符开头（'%abc'）索引失效。只有“%”不在第一个位置索引才会起作用
字符串不加单引号
order by 违反最左前缀法则，导致额外的文件排序（会降低性能）
group by 违反最左前缀法则，导致产生临时表（会降低性能）

通过explain分析sql语句

explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';

显示结果分析：

EXPLAIN列的解释：

table 显示这一行的数据是关于哪张表的

type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

rows 显示需要扫描行数

key 使用的索引

执行show processlist;

查看mysql正在运行的sql线程，可看到sql语句执行时间

show processlist

show full processlist

mysqladmin processlist

select * from information_schema.processlist //查看当前所有的process

select * from information_schema.processlist where info is not null order by time desc //查看当前正在进行的查询并按照已经执行时间倒排

查看mysql慢查询日志（默认情况下，mysql不开启慢查询日志）

日志分析工具mysqldumpslow

得到返回记录集最多的10个SQL。

mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

慢查询的数量保存在mysql库里面的slow_log表。

SELECT * FROM slow_log where start_time > '2019/05/19 00:00:00';

业务逻辑

数据巨大

深度分页 Limit导致全表扫描

数据分布不均

网络环境因素

一次请求执行30条Sql 传输时间增加

网络带宽网卡打满

丢包重试

https://segmentfault.com/a/1190000021464570

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

sql

数据库

云数据库 SQL Server

python

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度