Mysql基本函数

# 判断123是否为数值,如果是则返回0否则返回1
SELECT ('123' REGEXP '[^0-9.]');

# GROUP_CONCAT 指定分隔符
GROUP_CONCAT(uid SEPARATOR ';\n')

# GROUP_CONCAT 有长度限制,默认1024
SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;

清理包含指定条件的清理sql生成

SELECT delete_sql as '-- delete_sql' FROM (
SELECT distinct concat('DELETE from `',c.TABLE_SCHEMA,'`.`',t.TABLE_NAME,'` WHERE `pid`=1 AND `uid`=222;') AS 'delete_sql',count(1) as num
FROM information_schema.`TABLES` t
LEFT JOIN information_schema.`COLUMNS` c ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_SCHEMA=c.TABLE_SCHEMA
WHERE t.TABLE_SCHEMA != 'mysql' AND t.TABLE_SCHEMA != 'sys' AND t.TABLE_TYPE='BASE TABLE' AND c.COLUMN_NAME in ('pid','uid')
GROUP BY delete_sql
HAVING num=2
ORDER BY
c.TABLE_SCHEMA,c.TABLE_NAME,c.ORDINAL_POSITION
) aaaa

所有表字段信息

SELECT
    C.TABLE_SCHEMA AS '库名',
    T.TABLE_NAME AS '表名',
    C.COLUMN_NAME AS '列名',
    T.TABLE_COMMENT AS '表注释',
    C.COLUMN_COMMENT AS '列注释',
    C.ORDINAL_POSITION AS '列的排列顺序',
    C.COLUMN_DEFAULT AS '默认值',
    C.IS_NULLABLE AS '是否为空',
    C.DATA_TYPE AS '数据类型',
    C.CHARACTER_MAXIMUM_LENGTH AS '字符最大长度',
    C.NUMERIC_PRECISION AS '数值精度(最大位数)',
    C.NUMERIC_SCALE AS '小数精度',
    C.COLUMN_TYPE AS 列类型,
    C.COLUMN_KEY 'KEY',
    C.EXTRA AS '额外说明'
FROM
    information_schema.`TABLES` T
LEFT JOIN information_schema.`COLUMNS` C ON T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE
    T.TABLE_SCHEMA != 'mysql'
ORDER BY
    C.TABLE_NAME,
    C.ORDINAL_POSITION;

MySQL查询优化(分段查询优化索引的使用)

[toc]

实验环境

测试表的创建语句

CREATE TABLE `live_user_join_room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL COMMENT '用户id',
  `room_id` int(11) DEFAULT NULL COMMENT '直播间id',
  `is_out` int(11) NOT NULL DEFAULT '0' COMMENT '0 未退出 1 已退出',
  `time` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '时间',
  `course_id` bigint(20) DEFAULT NULL COMMENT '课程 id',
  `source` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '来源',
  `source_id` int(11) DEFAULT NULL COMMENT '来源ID',
  `created_by` int(11) NOT NULL DEFAULT '0' COMMENT '创建者',
  `updated_by` int(11) NOT NULL DEFAULT '0' COMMENT '更新者',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted_at` timestamp NULL DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`),
  KEY `a` (`uid`,`room_id`,`course_id`),
  KEY `b` (`room_id`,`uid`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=8376813 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='用户加入房间'

IN条件结果数量 1920 条记录

查询索引

SHOW KEYS FROM `test`.`live_user_join_room`; 

优化前的sql, 平均查询用时 8.36s 左右

SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` FORCE INDEX(`created_at`)
WHERE room_id IN (
    SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
    AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND created_at>'2020-11-07'
GROUP BY uid

优化前的explain的结果

优化后的sql, 平均查询用时 0.572s 左右

SELECT uid,COUNT(DISTINCT(room_id)) AS num FROM `live_user_join_room` USE INDEX(a)
WHERE room_id IN (
    SELECT id FROM live_im_rooms WHERE pid=1 AND room_status!=5 AND room_status!=6
    AND course_id NOT IN (SELECT id FROM courses WHERE pid=1 AND `type`=1 AND price=0)
)
AND id>(SELECT MIN(id) FROM `live_user_join_room` WHERE created_at>'2020-11-07')
GROUP BY uid

explain的结果

差异的原因个人理解为

MySQL 每次查询只能使用一个索引, 优化后的sql虽然扫描行数多, 但是消除了 Using filesort , 解决了慢查询的问题

• NOT IN 目标查询包含null的问题

select id from aa where id not in (select union_key from bb)
如果id为bb中的union_key不存在的值, 那么就应该可以正常数据, 但是, 但是
如果union_key包含null, 则这个查询永远没有结果了

• 视图关联的账户如果需要禁用数据库的权限, 可能需要下面的语句修改视图关联的账户, 注意: 阿里云的RDS需要修改为合适的且有所有数据库读写权限的账户,因为视图归属需要对应的视图写权限和查询的读取权限

SELECT TABLE_SCHEMA AS '库',TABLE_NAME AS '视图名',DEFINER AS '所属账户',CONCAT("alter DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") AS '修复语句' FROM information_schema.VIEWS 
WHERE TABLE_SCHEMA!='sys' AND TABLE_SCHEMA!='mysql' AND DEFINER!='root@%';