INSERT 语句

INSERT … ON DUPLICATE KEY UPDATE

MySQL 数据库中,如果在insert语句后面带上ON DUPLICATE KEY UPDATE 子句,而要插入的行与表中现有记录的唯一索引或主键中产生重复值,那么就会发生旧行的更新;如果插入的行数据与现有表中记录的唯一索引或者主键不重复,则执行新纪录插入操作。

如果一个表中既有 primary key 又有unique 索引, 仅仅是 pk 生效

影响

  • 如果是插入操作,受到影响行的值为1;
  • 如果更新操作,受到影响行的值为2;
  • 如果更新的数据和已有的数据一样(就相当于没变,所有值保持不变),受到影响的行的值为0
阅读更多

FAQ

[1071] Specified key was too long; max key length is 767 bytes

对业务数据进行重建, 需要从自建库更改到 RDS 库
在执行一条 sql 的时候出现

[42000][1071] Specified key was too long; max key length is 767 bytes

1
2
3
alter table contract
add constraint k_contract_id
unique (contract_id) comment '合同编号'

不是版本问题,不是字符集问题,是数仓MySQL的某些参数限定了这个建表的唯一索引,通过各种查看数据库的参数发现,数仓的MySQL有一个参数配置:innodb_large_prefix

这个参数的解释是:表的字段索引长度限制,他有两个值:(ON、OFF),可以控制是否允许单列的索引长度超过767字节。

分析一下这个限制跟我有啥关系?username varchar(255),众所周知在utf8mb4的情况下存储会多耗用空间,mb4就是most bytes 4,这个字符格式是可以用来支持4字节的unicode的,varchar最大存放65535 bytes,对于username这种一段一看就是存用户名的,用户名存在较长的可能,假如这个开关是OFF,那么它最大能支持存的字节数是767,而varchar(255)最大可以存65535缺的一位表示的是符号,那么明显已经超了,所以需要打开这个开关。

解决 :
Mysql只是去SET GLOBAL innodb_large_prefix = ON 是可以的,不过阿里云RDS需要在运维后台手动修改这个参数,然后提交并重启,找OPS同事开启这个参数为ON,重启,发现是可以建表了,说明使用上是没啥问题了

不过继续刚才的问题,打开ON,就可以支持存65535字节的索引了吗,并不是,这个参数深一点的含义是扩展单列索引的长度,在Mysql 5.7版本中ON的话可以支持3072长度的单列索引,复合索引长度不变(复合索引最大长度也是3072)。

所以,以后在使用过程中如果发现建表就报Specified key was too long; max key length is 767 bytes,需要想到会不会是限定了字段索引长度导致的,这在做数据同步和迁移时可能还会再遇到


原文地址 : FAQ
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

DataGrip 连接数据库问题

[08S01]
Communications link failure

The last packet successfully received from the server was 193 milliseconds ago.  The last packet sent successfully to the server was 172 milliseconds ago.

No appropriate protocol (protocol is disabled or cipher suites are inappropriate).

useSSL改成 false,如果不可以,在改成 no,就可以正确连接, 我的是改成 no就可以
image.png


原文地址 : DataGrip 连接数据库问题
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

8, 9. 聚合函数

8.1 各种统计函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 总数
Count(*)
# Count + If 组合
Count(If(DayOfWeek(trav_date) In (1, 7), 1, Null))
# 最小
Min()
# 最大
Max()
# 总数
Sum()
# 平均
Avg()
# 唯一值
Distinct()
# 子群/分组/聚类函数
# 聚类函数会忽略 Null 值
... Group By trav_date
# 子群/分组/聚类函数选择
# Having 的操作是在已经做好分组的基础上进行的筛选
... Group By trav_date Having Count(*) > 3

8.2 With RollUp 进行汇总统计

在分组统计数据的基础上再进行统计汇总,即用来得到 group by 的汇总信息
数据表操作对应信息

Information_Schema 表 Show
Schemata Show Database
Tables Show Tables
Columns Show Columns

9.1 如何正确的使用元数据

1
2
3
4
5
6
7
8
9
10
11
12
13
# 确定数据库是否存在
Select Schema_Name
From Information_Schema.Schemata
Where Schema_Name = ?
# 确定数据表是否存在
Select Table_Name
From Information_Schema.Tables
Where Schema_Name = ? and Table_Name = ?
# 列信息
Select *
From Information_Schema.Columns
Where Table_Schema = ? and Table_Name = ?
And Column_Name = ?

9.2 显示创建数据表结构

1
2
3
4
# 显示创建数据表结构
Show Create Table tbl_name;
# 显示列
Show Columns From tbl_name;

9.3 获取服务器元数据

语句 语句生成的信息
Select Version() 服务器版本
Select Database() 默认的数据库名称
Select User() 客户端连接时候给出的当前用户
Select Current_User() 用来检查客户端权限
Show [Global] Status 服务器的全局状态指示器, 没有 Global 显示当前
Show Variables 服务器配置变量

9.4 确定服务器支持的存储引擎

1
Show Engines;

原文地址 : 8, 9. 聚合函数
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

6. 使用日期和时间

6.1 选择合适的日期或者时间变量类型

1
2
3
4
5
6
CCYY-MM-DD   : 1000-01-01 - 9999-12-31
hh:mm:ss : -838:59:59 - 838:59:59
DateTime(CCYY-MM-DD hh:mm:ss)
: 1000-01-01 00:00:00 - 9999-12-31 23:59:59
Timestamp
: 1970-01-01 00:00:00 - 2037-12-31 23:59:59

6.2 MySQL 日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 进行日期的提取
Str_To_Date('May 13, 2007', '%M %D, %Y')
# 日期格式化
Date_Format(date, '%M %D, %Y');
# 时间格式化
Time_Format(time, '%T');
# 转换时区
Convert_Tz()
# 当前日期
CurDate()
# 当前时间
CurTime()
# 当前日期时间
Now()
# Utc
Utc_Date(), Utc_Time(), Utc_Timestamp()
格式化字符 含义
%Y 4 位年份
%y 2 位年份
%M 完整的月份名称(January-December)
%b 月份的前三个字母(Jan-Dec)
%m 数字月份, 带有前缀 0 (01-12)
%c 数字月份, 无前缀(1-12)
%d 数字日期, 带有前缀 0 (01-31)
%e 数字日期, 无前缀 (1, 31)
%W 工作日名称 (Sunday - Saturday)
%r 时间, 12 小时, 以 AM - PM 结尾
%T 时间, 24 小时制
%H 小时, 带有前缀 0 (00-23)
%i 分钟, 带有 0(00-59)
%s 秒 (00-59)
%% ‘%’ 文字字符

6.3 客户端

1
2
# 设置默认时区
mysqld --default-time-zone

6.4 Timestamp 的自动更新

1
2
3
4
5
# 根据设定来更新当前时间戳 + 更新当前时间戳
Create Table `t` (
ts timestamp Not Null Default Current_Timestamp
On Update Current_Timestamp
)

6.5 日期分离函数

函数 返回值
Year()
Month()
MonthName() 月完整名称 (January-December)
DayOfMonth() 1…31
DayName() 一周中的天数 (Sunday-Saturday)
DayOfWeek() 1-7, 对应 (Sunday - Saturday)
WeekDay() 0-6, 对应 (Monday - Sunday)
DayOfYear() 一年中的天数值 (1…366)
Hour() 小时数
Minute() 分钟数
Second() 秒数

6.6 日期组成函数

1
2
3
4
5
6
7
8
9
10
11
12
13
# 合成时间
MakeTime(10, 30, 30)
# 合成日期
MakeDate(2007, 60)
# 时间 + 秒的转换
Time_To_Sec()
Sec_To_Time()
# 时间 + 日期的转换
To_Days()
From_Days()
# 时间戳转换
Unix_TimeStamp()
From_UnixTime()

6.7 计算时间间隔

1
2
3
4
5
6
# 日期间隔
DateDiff(date1, date2)
# 时间间隔
TimeDiff(time1, time2)
# 时间戳间隔
TimestampDiff(Unit, dt1, dt2)

6.8 时间计算

1
2
3
4
5
6
7
8
9
10
# 时间加法
AddTime()
# 时间/日期时间 相加
Timestamp()
DateAdd(d, interval)
DateSub(d, interval)
d + Interval val unit
d - Interval val unit
# 计算生日
TimestampDiff(unit, birth, current)

原文地址 : 6. 使用日期和时间
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

1, 2. 使用 Mysql 客户端

1.1 建立 mysql 用户账号

1
2
3
4
5
6
7
# 创建用户
CREATE USER 'cbuser'@'%' IDENTIFIED WITH mysql_native_password AS 'cbpass';
GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON `1dailian\_v2`.* TO 'remote'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# 创建用户并授权
GRANT ALL ON daniu.* TO 'cbuser' @'localhost' IDENTIFIED BY 'cbpass';

1.2 创建数据库和样表

1
2
3
4
5
CREATE DATABASE cookbook;
USE cookbook;
CREATE TABLE limbs ( thing VARCHAR ( 20 ), legs INT, arms INT );
INSERT INTO limbs VALUES('human', 2, 2);
SELECT * FROM limbs;

1.3 启动和停止 MySQL

1
2
3
4
5
6
# 启动
$ mysql -h host -p -u cbuser
# 导出
$ mysqldump -h localhost -p -u cbuser cookbook > cookbook.sql
# 服务器维护
$ mysqladmin -p -uroot shutdown

1.4 使用可选项文件来指定链接

my.ini / my.cnf 可以进行相应的配置

  • 使用组的方式来组织数据
  • 只允许使用选项的长格式
  • = 前后允许存在空格
  • 选项没有值, 可以略去 =value 部分
  • 如果选项值中包含空格以及其他特殊符号, 可以使用 引号(单引号/双引号) 将数值包裹起来
  • 不需要某行则绿区即可
  • 文件中可以定义多个组
  • 多次出现, 最后出现的拥有最高优先级
  • #, ; 开头的数据会被忽略
    1
    2
    3
    4
    5
    [client]
    host=localhost
    ;user=root
    [mysql]
    skip-auto-rehash

1.5 保护选项文件不被其他用户读取

1
$ chmod 600 my.cnf

1.6 混合使用命令行参数和选项文件参数

命令行参数的优先级比选项文件中参数优先级高

1.7 找不到 mysql 应该怎么做

将 mysql 安装目录放到环境变量中.

1.8 发起 sql 语句

1
2
3
4
5
6
;     # 作为末尾输入
\g # 作为结束替代输入 '\go'
\c # 作为取消输入 '\cancel'
"> # 需要输入 双引号 来结束
'> # 需要输入 单引号 来结束
/*> # 需要输入 */ 来结束

1.9 从文件中读取语句

1
2
3
4
$ mysql cookbook < filename.sql
$ mysql -uroot -p
$ use cookbook
$ source filename.sql

1.10 打印错误

1
2
$ perror 24
OS error code 24: Too many open files

1.11 让 mysql 读取其他程序的 数据

1
2
3
4
5
6
7
8
# 从文件读取sql
$ mysql cookbook < limbs.sql
# 另外一种方法的文件读取
$ cat limbs.sql | mysql cookbook
# 跨境传输数据
$ mysqldump cookbook | mysql -h other.host.com cookbook
# 生成数据
$ gen-test-data.sh | mysql cookbook

1.12 一行输入所有 sql

1
2
3
4
5
6
7
8
9
10
11
12
13
$ mysql -uroot -e "select count(*) from mk_pam_account" daniu
# 多条数据
$ mysql -uroot -e "select count(*) from mk_pam_account;select now()" daniu
+----------+
| count(*) |
+----------+
| 2342 |
+----------+
+---------------------+
| now() |
+---------------------+
| 2019-04-10 23:44:49 |
+---------------------+

1.13 预防查询输出超出屏幕范围

1
2
3
4
5
6
7
8
9
10
11
# 指定 --pager 使其每次都显示一行输出
$ mysql -uroot --pager=/usr/bin/less
# 无参数代表使用系统默认分页
$ mysql -uroot --pager
# mysql 中使用分页
# 设置默认分页
mysql> \P
# 设置指定分页
mysql> \P /usr/bin/less
# 恢复基本输出
mysql> \n

1.14 查询结果输出

交互模式
交互模式下输出的是表格
非交互模式
输出的是以制表符分隔的数据体
-t or --table
如果输出格式强制使用 table 表格输出可以携带参数
-B or --batch
如果输出格式强制使用批量(定界符)输出, 可以使用
-H or --html
生成 html 格式
-X or --xml
使用 XML 格式输出
-ss or --skip-column-names
不在查询中输出包含列头部

1.15 查询结果二次转换

如果查询结果并不能按照我们的期望输出, 可以使用 sed, tr 来进行二次转换

1.16 使长输出行更具可读性

1
mysql> show full columns from limbs\G

-E or --vertical
进行垂直输出

1.17 冗长输出和简介输出

-v or -vv or -vvv
冗长输出
-s or -ss or -sss
进行简洁输出

1.18 记录操作步骤

-tee or \T or \t
进行脚本记录的输出, 这些输出可供记录并且可以用作批处理文件

1.19 用户自定义变量

1
2
3
4
5
# 从执行过程中获取值
mysql > select @id:=id from libms;
mysql > select * from abcolumsn where id = @id;
# 设置值使用 set
mysql > set @sum = 4+7;

1.20 mysql 数学函数

1
select 20/sqrt(64);

1.21 shell 脚本中使用 mysql

1
2
3
4
# 多行文本的输入并且支持变量
mysql cookbook <<< INPUT
select * from $1
INPUT

原文地址 : 1, 2. 使用 Mysql 客户端
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

3, 7. 从表中查询数据

3.1 列查询

1
2
mysql > select * from mail;
mysql > select mail, name from mail;

3.2 指定行查询(条件筛选)

1
2
3
mysql > select mail, name from mail where name='zhao';
mysql > select mail, name from mail where name like 'zh%';
mysql > select mail, name from mail where name='zhao' and area ='beijing';

3.3 格式化显示查询结果

这里利用各种的函数来格式化相应的位置

1
2
3
4
5
CONCAT()     : 链接字符串
MONTHNAME() : 月份名称
DayOfMonth() : 天
Year() : 年份
DateFormat : 日期格式化

使用 as 对列使用别名
列别名在 where 中不可用
使用 distinct 进行唯一性处理

3.4 调试比较表达式

1
2
# 这里的 name='good' 便是表达式
mysql > select mail, name = 'good' from name

3.5 null 值的比较

对于 null 值使用 null 的比较操作符
is null, is not null, <=>
这里有两个函数需要注意

1
2
If(expr1 is not null, expr1, expr2);
IfNull(expr1, expr2)

这两个函数等价.

3.6 排序/视图/多表查询

1
2
3
... order by a;
... order by a, b;
... order by a asc, b;

多表查询

1
2
3
4
5
... from profile 
inner join
profile_contact
on
profile.id = profile_contact.profile_id

子查询

1
2
3
... from profile_contact 
where
profile_id in (select id from profile where area='beijing')

查询头尾取出数据

1
2
... Limit 1
... Limit 2, 1

结果集再次排序

1
2
3
select * from 
(select name from profile order by birth desc limit 4) as t
order by birth asc;

7.1 使用 Order By

1
2
3
4
5
6
7
8
# 标准
... Order By name [Desc][, title [Asc]];
# 表达式排序
... Order By Floor((size + 1023) / 1024)
# 时间排序
... Order By date;
# 可以根据字串的某部分进行排序
... Order By SubString_Index(bsn, '-', -3)

7.2 查询条件

1
2
# If
... Order By If(a=b, 0, 1);

7.3 按照用户自定义排序

1
2
# Field
... Order By Field(name, 'Zhang', 'Li', 'Wang')

7.4 Enum 排序

1
2
3
# 使用 Cast 进行转换为字串
# 默认 Enum 存储的是数值
... Order By Cast(day as Char);

原文地址 : 3, 7. 从表中查询数据
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

4. 表管理

4.1 克隆表结构和数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 创建表结构
Create Table new_table Like ori_table
# 表数据插入
Insert Into new_table Select * From ori_table Where ...
# 表数据指定字段插入
Insert Into new_table(col_a, col_b) Select col_c,col_d From ori_table Where ...
# 查询并且新建
Create Table new_table Select col_a, col_b From ori_table
# 新建表并使用索引
Create Table new_table(Primary Key (id), Index (state, city)) Select id, state, city From ori_table
# 创建数据表并修改结构
Create Table new_table(Primary Key (id), Index (state, city)) Select id, state, city From ori_table;
Alt Table new_table Modify id Int Unsigned Not Null Auto_Increment;
# 修改表的自增数据
alter table tb_name AUTO_INCREMENT = 100000;

4.2 使用临时表

1
2
# 创建临时表
Create Temporary Table tbl_name (...col define ...)

不同的数据库连接可以创建同名的临时表, 这些表之间不互相影响. 临时表对数据库连接生效
如果临时表没有删除则在下次使用的时候会报错, 所以使用前最好先对数据表是否存在做相应的判定.

1
2
# 删除
Drop Temporary Table If Exists tbl_name
1
2
# 唯一性临时表
Select Connection_Id();

4.3 检查或者改变某个表的搜索引擎

1
2
3
4
5
6
7
8
# 显示引擎
# From Information_Schema
Select Engine From Information_Schema.tables
Where Table_Schema = 'cookbook' And Table_Name ='cookers';
# Status
Show Table Status Like 'cookers';
# Create Table
Show Create Table cookers;
1
2
# 修改引擎
Alt Table cookers Engine = InnoDb

原文地址 : 4. 表管理
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

10. 数据导入导出

10.1 使用 Load Data 和 mysqlimport 导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Load Data Local InFile 'file.txt' Into Table tbl_name
Fields Terminated By ':'
Enclosed By '"' # 指定引用
Escaped By '' # 指定转义
Lines Terminated By '\r'
Ignore 1 Lines; # 指定忽略
# 默认使用制表符 \t
# 换行代表一行数据
# 需要 File 权限
# 文件位于服务器机器上
# 相对路径的文件位于 数据库存储 位置
# 没有 Local 读取服务器文件
# 存在 Local 读取本地文件
# Replace Into 是对数据进行替代操作, 存在则不进行处理
# 使用 Show Warnings 来显示相关的警告信息
# 表格后可以指定列 tbl_name(col1, col2, col3), 默认全部
1
2
3
4
5
6
$ mysqlimport --local tbl_name file.txt
--fields-terminated-by=':'
--fields-enclosed-by=':'
--fields-escaped-by=':'
--lines-terminated-by='\r'
--columns=a,b,c

10.2 导入时候对数据进行预处理

1
2
3
4
5
6
Load Data Local InFile 'file.txt' Into Table tbl_name
(@cola, @colb, @colc)
Set
dt = concat(@cola, ' ', $colc),
df = substring_index(@colb, ' ', 1)
...;

10.3 mysql 导出

1
2
$ mysql -e "select ...." --skip-column-names tbl_name
>> data.txt

10.4 SQLMode

通过设置 sql_mode 变量来开启输入数据接收限制.

11.1 生成和使用序列

1
2
3
Auto_Increment # 列
id Int Unsigned Not Null Auto_Increment,
Primary Key(id)

12.1 在表中找到与另一个表中的行相匹配的行

使用 join
一个完全的连接将产生所有可能的行联合, 即笛卡尔积.正因为如此同时也很少使用两个以上表的联合, 连接通常会包含 On 或 Using 子句以指定如何在表间进行连接

1
2
3
4
5
6
Select * From artist, painting
where artist.a_id = painting.a_id
Select * From artist Inner Join painting
on artist.a_id = painting.a_id
Select * From artist Inner Join painting
on using(a_id)

Inner Join 所编写的连接为内部连接, 这意味着它们只为某个表中与其他表的值相匹配的值产生结果.
外部连接(Left Join/Right Join)同样可以产生这些匹配, 此外还可以为你显示某个表中的哪些值于另外一个表中的值是不相符的.
Left Join 的意思是左侧为基准, 右侧数据可以为空, Right Join 则相反.


原文地址 : 10. 数据导入导出
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

5. 与字符串共舞

5.1 字符串属性

字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 看看系统中有哪些字符集
mysql > Show Character Set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
...
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
...

Length 和 Char_Length 差别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> Set @s = Convert('abc' Using ucs2);
mysql> Select Length(@s), Char_Length(@s);
+------------+-----------------+
| Length(@s) | Char_Length(@s) |
+------------+-----------------+
| 6 | 3 |
+------------+-----------------+
mysql> Set @s = Convert('abc' Using utf8);
mysql> Select Length(@s), Char_Length(@s);
+------------+-----------------+
| Length(@s) | Char_Length(@s) |
+------------+-----------------+
| 3 | 3 |
+------------+-----------------+

Collation
如果没有指明哪种 Collation, 则 Default 为 Yes 的则为默认的字符集.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> Show Collation Like 'utf8\_%';
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_german2_ci | utf8 | 212 | | Yes | 8 |
| utf8_croatian_ci | utf8 | 213 | | Yes | 8 |
| utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 |
| utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
+--------------------------+---------+-----+---------+----------+---------+
27 rows in set (0.01 sec)

名称后缀的说明

  • ci : (case insensitive) 大小写不敏感
  • cs : (case sensitive) 大小写敏感
  • bin: (binary) 二进制

对于不同的语种也有不同的排序方法.

1
2
3
4
5
6
# 排序 A, a, b, B (大小写不敏感, a 在 b 前边即可)
Select c From tb_name Order By c Collate latin_swedish_ci;
# A, a, B, b (大小写敏感, A 在 a 前, B 在 b 前)
.... Collate latin_swedish_cs;
# A, B, a, b
.... Collate latin_swedish_bin;

5.2 选择字符串数据类型

根据这几个来进行判定

  • 是否二进制
  • 是否大小写敏感
  • 字串的最大长度是多少
  • 定长值还是变长值
  • 是否需要保留尾部空格
  • 是否有固定的允许值集合

字串长度

二进制数据类型 普通文本 最大长度 说明
Binary Char 255 2^8
VarBinary VarChar 65535 2^16
TinyBlob TinyText 255 2^8
Blob Text 65535 2^16
MediumBlob MediumText 16777215 2^24
LongBlob LongText 4294967295 2^32

字串空格保留
如果你想保留出现在储存的原始字符串尾部的填充值, 那就应该使用一个没有截除动作发生的数据类型, 例如, 如果你正存储可能以空格结尾的字串(普通文本), 并且想保留这个空格, 就应该使用 VarChar 或者 Text 数据类型之一.

5.3 正确设置连接字符集

配置文件

1
2
[mysql]
default-character-set=utf8

应用程序中使用

1
2
mysql> Set Names 'utf8';
mysql> Set Names 'utf8' Collate 'utf8-general_ci'

连接时候指定

1
jdbc:mysql://{host}/{db}?characterEncoding=UTF-8

5.4 查询字符串

字串放在单引号或者双引号内
启用 ANSI_QUOTES SQL 模式时候不得使用双引号, 一般来讲使用单引号会比较保险
16 进制字符的表示

1
2
3
0x61625ad43
x'61625ad43'
X'61625ad43'
1
2
3
4
5
6
mysql> select 0x61;
+------+
| 0x61 |
+------+
| a |
+------+

使用由字符集加下划线(‘_’) 前缀组成的引入器

1
2
_latin1 'abcd'
_ucs2 'abcd'

查询过程中使用转义

1
Select "He said, \"Boo!\"";

5.5 检查字符集和字符排序

使用 Charset()Collation() 函数来检查字符集和排序
使用 Convert() 转换字符串的字符集

1
2
3
4
5
6
7
8
9
mysql> Set @s1 = 'my';
mysql> Set @s2 = Convert(@s1 Using latin1);
mysql> Select Charset(@s1), Charset(@s2);
+--------------+--------------+
| Charset(@s1) | Charset(@s2) |
+--------------+--------------+
| utf8 | latin1 |
+--------------+--------------+
1 row in set (0.00 sec)

使用 Collate 操作符改变字符串的 Collation

1
mysql> Set @s2 = @s1 Collate latin1_spanish_ci;

使用 Binary 操作符来进行二进制转换, 同 Convert(@s1 Using binary) 相同.

5.6 字串操作函数

Upper() : 更改为大写
Lower() : 更改为小写
Left(str, 2) : 左侧截取
Right(str, 2) : 右侧截取
Mid(str, 3, 1) : 中间截取
SubString(str, 2, 3) : 字符随意截取
SubString_Index(str, c, n) : 左侧查找 c 在第 n 次出现的位置
Concat() : 字符串拼接
Locate(search, subject) : 确定一个字串中是否含有另一个字串

5.7 使用 SQL 模式进行匹配

1
2
3
4
5
6
7
8
9
10
11
12
# Like
... Like 'co%'; # 开头
... Like '%er'; # 结尾
... Like '%er%'; # 包含
# _
... Like '___per'; # 完全匹配
# 模式匹配和字串函数的使用比较
Year(d) = 1976 d Like '1976-%'
Month(d) = 4 d Like '%-04-%'
DayOfMonth(d) = 1 d Like '%-01'
str Like 'abc%' Left(str, 3) = 'abc'
str Like '%abc' Right(str, 3) = 'abc'

5.8 使用正则表达式进行匹配

1
2
3
4
5
6
7
8
9
10
^         : 开始
$ : 结束
. : 匹配任何字符
[...] : 匹配括号内任意字符
[^...] : 不匹配括号内任意字符
p1|p2|p3 : 多选1
* : 0+
+ : 1+
{n} : 匹配 n 次
{m, n} : 最少 m, 最多 n 个

使用方法, 使用 Regexp 关键词进行匹配

1
... Regexp '^co';

支持 Posix 类

对应说明
[:alnum:] Alphanumeric characters
[:alpha:] Alphabetic characters
[:blank:] Whitespace characters
[:cntrl:] Control characters
[:digit:] Digit characters
[:graph:] Graphic characters
[:lower:] Lowercase alphabetic characters
[:print:] Graphic or space characters
[:punct:] Punctuation characters
[:space:] Space, tab, newline, and carriage return
[:upper:] Uppercase alphabetic characters
[:xdigit:] Hexadecimal digit characters

正则表达式匹配不要求目标串与模式串完全匹配

5.9 使用 FullText 查询

标准语法

1
2
3
4
5
6
# 单列
... Match (Column) Against ('str');
# 多单词查询, 并不是单词越多越精确
... Match (Column) Against ('str1 str2 str3')
# 多列, 多词
... Match (Column, Column2) Against ('str1 str2 str3')

配置最小单词

1
2
[mysqld]
ft_min_word_len=3

禁止搜索的单词
这种方法可以匹配多词, 单词, 移除部分单词

1
... Match(Column) Against('+David -Mark' In Boolean Mode)

特殊字符匹配

1
... Match(Column) Against('David*' In Boolean Mode)

词组查询

1
... Match(Column) Against('"Disk Path"' In Boolean Mode)

原文地址 : 5. 与字符串共舞
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

Percona 系列工具介绍以及在 CentOS 安装

下载和安装 percona toolkit 的包

Step1 : 安装 Repo

1
2
3
4
# 清华源(稳定)
$ yum install https://mirrors.tuna.tsinghua.edu.cn/percona/yum/percona-release-latest.noarch.rpm
# 官方
$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm

Step2 : 替换 Release 更新地址并更新 yum 的 repo 缓存

1
2
3
4
# 腾讯云
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.cloud.tencent.com\/percona/g' /etc/yum.repos.d/percona-original-release.repo
# 清华
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.tuna.tsinghua.edu.cn/g' /etc/yum.repos.d/percona-original-release.repo

更新缓存, 启用 tools 工具

1
2
3
$ yum update
$ yum list | grep percona-toolkit
$ percona-release enable tools

更新 tools 的源地址

需要启用 tools 之后才能更新源地址

1
2
3
4
# 腾讯云
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.cloud.tencent.com\/percona/g' /etc/yum.repos.d/percona-tools-release.repo
# 清华
$ sed -i 's/http:\/\/repo.percona.com/https:\/\/mirrors.tuna.tsinghua.edu.cn\/percona/g' /etc/yum.repos.d/percona-tools-release.repo

Step3 : 安装工具

1
$ yum install -y percona-toolkit

percona-toolkit 介绍

使用文档 Percona Release Usage
percona-toolkit 是一组高级命令行工具的集合,可以查看当前服务的摘要信息,磁盘检测,分析慢查询日志,查找重复索引,实现表同步等等
percona-toolkit 源自 Maatkit 和 Aspersa 工具,这两个工具是管理 mysql 的最有名的 工具,!这些工具主要包括开发、性能、配置、监控、复制、系统、实用六大类,作为一个优秀的 DBA,里面有的工具非常有用,如果能掌握并加以灵活应用,将能极大的提高工作效率。
命令说明
有的 32 个命令,可以分为 7 大类

工具命令 工具作用 备注
pt-duplicate-key-checker 列出并删除重复的索引和外键
pt-online-schema-change 在线修改表结构
pt-query-advisor 分析查询语句,并给出建议,有 bug 已废弃
pt-show-grants 规范化和打印权限
pt-upgrade 在多个服务器上执行查询,并比较不同
pt-index-usage 分析日志中索引使用情况,并出报告
pt-pmp 为查询结果跟踪,并汇总跟踪结果
pt-visual-explain 格式化执行计划
pt-table-usage 分析日志中查询并分析表使用情况 pt 2.2 新增命令
pt-config-diff 比较配置文件和参数
pt-mysql-summary 对 mysql 配置和 status 进行汇总
pt-variable-advisor 分析参数,并提出建议
pt-deadlock-logger 提取和记录 mysql 死锁信息
pt-fk-error-logger 提取和记录外键信息
pt-mext 并行查看 status 样本信息
pt-query-digest 分析查询日志,并产生报告 常用命令
pt-trend 按照时间段读取 slow 日志信息 已废弃
pt-heartbeat 监控 mysql 复制延迟
pt-slave-delay 设定从落后主的时间
pt-slave-find 查找和打印所有 mysql 复制层级关系
pt-slave-restart 监控 salve 错误,并尝试重启 salve
pt-table-checksum 校验主从复制一致性
pt-table-sync 高效同步表数据
pt-diskstats 查看系统磁盘状态
pt-fifo-split 模拟切割文件并输出
pt-summary 收集和显示系统概况
pt-stalk 出现问题时,收集诊断数据
pt-sift 浏览由 pt-stalk 创建的文件 pt 2.2 新增命令
pt-ioprofile 查询进程 IO 并打印一个 IO 活动表 pt 2.2 新增命令
pt-archiver 将表数据归档到另一个表或文件中
pt-find 查找表并执行命令
pt-kill Kill 掉符合条件的 sql 常用命令
pt-align 对齐其他工具的输出 pt 2.2 新增命令
pt-fingerprint 将查询转成密文 pt 2.2 新增命令

上面是 pt 工具各个命令的基本功能介绍,可以使用 command –help 来查看每个命令的具体作用和使用方法;
有的命令也可以使用 man command 命令查询相关命令详细信息。

Xtrabackup

对于 Percona-xtrabackup , 有这几个版本

1
2
3
percona-xtrabackup-22
percona-xtrabackup-24 # for 5.6/5.7
percona-xtrabackup-80 # for 8.0

原文地址 : Percona 系列工具介绍以及在 CentOS 安装
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

Mysql 函数和关键字

  1. 函数

  2. 关键字
    $ select version();

version() 5.2.24-log
current_date
curdate()
2014-01-31
now() 2014-01-31 22:16:18
user() odbc@localhost
year(current_date)
year(curdate())
2014
right(curdate(),5) 02-01
month(curdate()) 2
dayofmonth(curdate()) 1 一个月中的第几天
MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH))
MOD(MONTH(CURDATE()), 12) + 1
3 下个月
datebase() test 数据库

原文地址 : Mysql 函数和关键字
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

Mysql开发笔记 - 优化篇

  1. 优化SQL的一般步骤
          SHOW STATUS 可以提供服务器状态信息
                    Com_select : 执行select 操作的次数
                    Com_insert : 执行insert 操作的次数
                    Com_update : 执行update 操作的次数
                    Com_delete : 执行delete 操作的次数
              
               Innodb
                Innodb_rows_read select : 查询返回的行数;
                        Innodb_rows_inserted     :  执行Insert 操作插入的行数;
                        Innodb_rows_updated       :  执行update 操作更新的行数;
                        Innodb_rows_deleted       :  执行delete 操作删除的行数;
                    
               事务操作
               Com_commit    : 提交型          
                    Com_rollback : 回滚, 回滚太多说明编写存在问题

           数据库的基本情况
            Connections           试图连接Mysql 服务器的次数
           Uptime                     服务器工作时间
           Slow_queries      慢查询的次数
      定位执行效率较低的sql
           使用慢查询日志来定位哪些执行效率较低的SQL

            –log-slowqueries[=file_name] 文件位置
                long_query_time   执行秒数
      SHOW PROCESSLIST
           显示当前的链接数据库的状态
通过explain 分析低效sql的执行计划
      explain select * from mg_news
       >
            select_type: select 类型
table: 输出结果集的表
type: 表示表的连接类型

  • 当表中仅有一行是type的值为system是最佳的连接类型;
  • 当select操作中使用索引进行表连接时type的值为ref;
  • 当select的表连接没有使用索引时,经常会看到type的值为ALL,表示对该表
  • 进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。

possible_keys: 表示查询时,可以使用的索引列.

key: 表示使用的索引
key_len: 索引长度
rows: 扫描范围
Extra: 执行情况的说明和描述

  1. 索引问题   
          位置存放         
              MyISAM 表的数据文件和索引文件是分开的.Innodb 是放到一个表空间中, 但是可以有多个文件组成  建立索引的语法       > 索引类型            MyiSAM : btree      Innodb : btree       memory/heap    : hash , btree 查看索引使用情况      SHOW STATUS LIKE ‘Handler_read%’;        >      handler_read_key                     如果索引正在工作,这个值将会很高, 表示这个行被索引值读的次数, 次数低表明增加索引得到的性能改善不高      Handler_read_rnd_next                      高值意味着低效, 含义是在数据文件中读下一行文件的请求数
    3.定期分析表       
    ANALYZE TABLE       >      用于分析和存储表的关键字分布, 在分析期间,读取锁定表, 这对于MyISAM, BDB, InnoDB表有用        > CHECK TABLE      检查一个或者多个表是否有错误, 对于MyISAM , InnoDB有作用, 同样也可以检查视图        > CHEKCSUM TABLE      报告一个表校验和        > OPTIMIZE TABLE       >      对表进行优化, 如果删除了表的一大部分, 或者varchar,blob, text列的表进行了很多更改,则应该进行优化;
    4.常用SQL的优化
          大量数据的插入
               MyIsAM
                        默认是禁用索引,然后导入数据,然后再开启索引
    ALTER TABLE tblname DISABLE KEYS;loading the dataALTER TABLE tblname ENABLE KEYS;      InnodB            Innodb类型的表示按照主键的顺序进行顺序保存de,可以尝试建立主键再进行导入            导入的时候关闭唯一性校验,完成之后再开启            导入的时候应该关闭自动提交,然后结束的时候开启自动提交 Insert        使用多个值表的insert       使用insert delayed 将需要加载的语句放到内存       Low_priorty 是在所有用户读取/写入完成之后才进行插入       将索引文件和数据文件在不同的磁盘上存放       如果进行批量插入,可以增加bulk_insert_buffer_size进行提高速度,仅仅适用于myisam       使用文本文件装载一个表的时候 Load Data Infile 比insert快20倍       根据情况使用replace替代insert       根据情况使用ignore忽略重复记录 GROUP BY      使用order by null 对group by 进行优化加速 JOIN      使用子查询可以一次性完成很多逻辑上使用许多个步骤才能完成的sql操作.同时也恶意避免事务或者表锁死, 但是可以被更有效的链接(JOIN)来替代, INSERT UPDATE DELETE      1. 写入操作优先于读取操作       2. 对于某张数据表的操作某一时刻只能发生一次,写入请求按照他们到达的次数来处理       3. 对某张表的多个读取操作可以同时进行             LOW_PRIORITY 关键字应用于DELETE, INSERT, LOAD DATA, REPLACE, UPDATE       HIGH_PRIORITY 关键字应用于 SELECT INSERT       DELAYED 关键字 应用于INSERT REPLACE SELECT SQL_BUFFER_RESULT      会让客户端稍微费点力气才能够处理的大的结果集的时候才会使用,以免表发生死锁 USE INDEX, IGNORE INDEX, FORCE INDEX      索引的使用
  2. 其他优化措施
          1.使用持久的连接数据库
          2.经常检查所有查询是否使用了必要的索引
          3.避免在频繁更新的表上执行复杂的select操作
          4.对于没有删除操作的myisam, 操作和查询可以并行进行,删除应当在空闲时间进行
          5.充分利用列有默认值的事实, 只有当插入的值不同于默认值时候才会进行插入,这有助于提高插入速度
          6.对于经常访问的可以重构的数据使用内存表,可以显著提高访问的效率
          7.通过复制可以提高某些操作的性能
          8.表的字段尽量不适用自增长变量,高并发的情况下该字段的自增可能对效率有比较大的影响
    6.优化表的数据类型
          1. 使用 PROCEDURE ANALYSE() 优化分析表
               对于输出的每一项提出优化建议
  3. 通过拆分表,提高访问效率这里主要指的是 MYISAM类型的表      纵向拆分将经常访问的字段和不经常访问的字段进行拆分,经常访问的字段尽量是定长的           横向拆分是按照应用的情况有目的的将数据横向拆分成几个表或者通过分区分到不同的分区3. 逆规范化      强调数据的独立性,数据尽可能减少冗余       但是对于查询很多的应用,一次查询可能需要访问多表进行, 这样可以考虑有效的使用冗余数据来提高效率 4.使用临时统计表      对于大表的统计分析,可以建立 [create temporary table]进行统计 5.选择更合适的表      如果应用出现较严重的锁冲突可以考虑更改存储引擎到 innodb, 如果查询很多,且对事务完整性要求不严格,可以考虑使用myisam
    7.锁问题
          获取锁等待状态

          获取表锁状态
                
          获取行锁等待状态
                     > 表锁和行锁的区别      表锁的优越性            1.很多操作都是读取表            2.严格条件的索引上读取和更新, 当更新或者删除可以用单独的索引来读取            3. Update tbl_name SET column=value WHERE unique_key_col = v                      4. DELETE FROM tbl_name WHERE unique_key = value                     5. select 和 insert的并发执行但是有很少的update和delete           6. 很多的扫描表和对全表的GROUP BY 操作,但没有任何写表        行锁的优缺点            优点            1.许多县城访问不同的行时候只有少量的冲突            2.回滚时候只有少量的更改            3.可以长时间锁定单一的行            缺点            1.比页锁和表所占用更多的内存            2.当大部分使用是,比页锁或者表锁慢            3.经常扫描整个表或者GROUP操作比表锁慢很多            4.用高级别锁定, 通过支持的不同的类型锁定,你可以很容易调解程序, 成本小于行级别锁定 insert…select 带来的问题          如果select 的表是innodb 类型的,不论insert 的表是什么类型的表,都会对select 的表的纪录进行锁定L:63>
    >
    >
    >

     


原文地址 : Mysql开发笔记 - 优化篇
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

[转] MySQL读写分离介绍


原文地址: MySQL读写分离介绍及搭建
MySQL读写分离基本原理是让master数据库处理写操作,slave数据库处理读操作。master将写操作的变更同步到各个slave节点。

MySQL读写分离能提高系统性能的原因在于:

  • 物理服务器增加,机器处理能力提升。拿硬件换性能。
  • 主从只负责各自的读和写,极大程度缓解X锁和S锁争用。
  • slave可以配置myiasm引擎,提升查询性能以及节约系统开销。
  • master直接写是并发的,slave通过主库发送来的binlog恢复数据是异步。
  • slave可以单独设置一些参数来提升其读的性能。
  • 增加冗余,提高可用性。
阅读更多

MySQL 复制夯住排查以及原理探讨 | 区块链罗宾

MySQL 复制夯住排查以及原理探讨

目录: 数据库 | 标签:数据库 ,Database ,MySQL ,复制 ,故障排查 | 发表时间: Aug 22, 2015.Viewd 3087 times.

目录


文/Robin


本站推广
币安是全球领先的数字货币交易平台,提供比特币、以太坊、BNB 以及 USDT 交易。

币安注册: https://accounts.binancezh.cc/cn/register/?ref=11190872
邀请码: 11190872


一 引子


研发反应,有台从库和主库不同步。由于业务读操作是针对从库的,数据不同步必定会带来数据的不一致,业务获取的结果会受影响,所以这个问题必须尽快解决。
登上服务器,查看 MySQL 的从库状态,并没有任何报错信息。刷新从库状态,发现状态没有任何变化,Exec_Master_Log_Pos 卡住不动。

二 故障分析


为了安全起见,此文略去 MySQL 版本以及其他可能会带来安全问题的信息。
接下来逐步分析问题。
首先查看从库状态:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000296
Read_Master_Log_Pos: 364027786
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: binlog.000283
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 594374863
Relay_Log_Space: 13803486573
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 256219
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)

此时的 Slave_IO_State 为 Queueing master event to the relay log,而不是正常状态下的 Waiting for master to send event。刷新多次,状态没有任何变化,Exec_Master_Log_Pos 不变,从而导致 Seconds_Behind_Master 一直不变。
接下来查看 PROCESSLIST 状态:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> SHOW FULL PROCESSLIST;
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
| 51378 | system user | | NULL | Connect | 1121183 | Waiting for master to send event | NULL |
| 88917 | system user | | NULL | Connect | 245327 | Reading event from the relay log | NULL |
| 106029 | userA | xxx.xxx.xxx.xxx:14057 | NULL | Sleep | 14504 | | NULL |
| 106109 | userA | xxx.xxx.xxx.xxx:15077 | databaseA | Sleep | 79 | | NULL |
| 106110 | userA | xxx.xxx.xxx.xxx:15081 | databaseA | Sleep | 13000 | | NULL |
| 106116 | userB | xxx.xxx.xxx.xxx:15096 | databaseA | Sleep | 357 | | NULL |
| 106117 | userB | xxx.xxx.xxx.xxx:15097 | NULL | Sleep | 12964 | | NULL |
| 106119 | root | localhost | NULL | Query | 0 | NULL | SHOW FULL PROCESSLIST |
| 106126 | userB | xxx.xxx.xxx.xxx:15173 | NULL | Sleep | 12856 | | NULL |
| 106127 | userB | xxx.xxx.xxx.xxx:15180 | databaseA | Sleep | 12849 | | NULL |
| 106766 | userA | xxx.xxx.xxx.xxx:17960 | databaseA | Sleep | 64 | | NULL |
+--------+-------------+------------------+--------------------+---------+---------+---------------+------------------+
11 rows in set (0.00 sec)

从以上的结果来看,没有任何异常。
既然从上述信息中得不到任何对排查问题有帮助的信息,那么我们可以试着分析 MySQL 的 binlog,看 Pos 为 594374863 的点发生了什么操作。
分析日志我们可以使用 mysqlbinlog 命令,指定 start-position 为夯住的那个点,并重定向到文件。

1
2
/usr/local/mysql/bin/mysqlbinlog --no-defaults -v --start-position="594374863" \
binlog.000283 > /XXX/binlog.sql

查看输出结果,发现端倪了,以下是摘抄的部分结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150814 17:43:15 server id 21 end_log_pos 107 Start: binlog v 4, server v x.x.xx-log created 150814 17:43:15
BINLOG '
M7jNVQ8VAAAAZwAAAGsAAAAAAAQANS41LjE5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 594374863
#150814 18:09:36 server id 21 end_log_pos 594374945 Query thread_id=210702841 exec_time=43 error_code=0
SET TIMESTAMP=1439546976/*!*/;
SET @@session.pseudo_thread_id=210702841/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 594374945
# at 594375036
# at 594376047
# at 594377085
# at 594378123
# at 594379152
# at 594380187
# at 594381165
# at 594382194
# at 594383223
# at 594384252
# at 594385269
# at 594386307
# at 594387282
# at 594388299
# at 594389265
# at 594390270
# at 594391299
# at 594392310
# at 594393327
# at 594394344
# at 594395340
# at 594396336
# at 594397332

从以上输出中,我们可以知道,从夯住的那个点开始,binlog 记录的信息就出现了异常,可以推测在主库有大操作。另外,针对出现问题库,查看主库和从库的表数量,发现从库的表数量多于主库,有几个临时表出现。可以推测的,主库有删表的操作,从库同步夯住,导致同步异常,主库删表的操作还没来得及同步到从库。
经过和研发沟通,确认了两点。第一,确实有大操作,程序有大量的批量插入,而且是用的 LOAD DATA LOCAL INFILE;第二,主库确实有删表的操作,这几张表都是临时表。

三 故障解决


既然问题找到了,那解决办法自然就有了。既然从库的表多于主库,而且这几张表是临时数据,我们可以过滤掉对这几张表的同步操作。具体思路如下:在主库备份临时表(虽然研发说数据不重要,但还是以防万一,DBA 还是谨慎为好),然后通知研发临时切走从库的流量,修改配置文件,指定 replicate-ignore-table 参数,重启 MySQL。
接下来就是具体的解决步骤,首先备份数据。备份时不加 –master-data 参数和 –single-transaction。究其原因,–master-data 禁用 –lock-tables 参数,在和 –single-transaction 一起使用时会禁用 –lock-all-tables。在备份开始时,会获取全局 read lock。 –single-transaction 参数设置默认级别为 REPEATABLE READ,并且在开始备份时执行 START TRANSACTION。在备份期间, 其他连接不能执行如下语句:ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。MySQL 同步夯住,如果加了上述参数,mysqldump 也会夯住。mysqldump 会 FLUSH TABLES、LOCK TABLES,如果有 –master-data 参数,会导致 Waiting for table flush。同样,有 –single-transaction 参数,仍然会导致 Waiting for table flush。另外,还可以看到 Waiting for table metadata lock,此时做了 DROP TABLE 的操作。此时可以停掉 MySQL 同步来避免这个问题。
为了保险起见,我们在主库加大 expire_logs_days 参数,避免 binlog 丢失。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL expire_logs_days=5;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE '%expire%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 5 |
+------------------+-------+
1 row in set (0.00 sec)

接着修改从库的配置文件:

1
vim /xxx/xxxx/xxx/my.cnf

在 mysqld 后,加入如下配置:

replicate-ignore-table=databaseA.tableA
replicate-ignore-table=databaseA.tableB
replicate-ignore-table=databaseA.tableC
replicate-ignore-table=databaseA.tableD

然后重启 MySQL:

1
/xxx/xxx/xxx/xxx/mysqld restart

登录 MySQL 从库,查看从库状态,并定时刷新状态,我们可以看到的是,Exec_Master_Log_Pos 在递增,Seconds_Behind_Master 在递减,证明问题已经解决了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: masterIP
Master_User: replUser
Master_Port: masterPort
Connect_Retry: 60
Master_Log_File: binlog.000319
Read_Master_Log_Pos: 985656691
Relay_Log_File: relaylog.000004
Relay_Log_Pos: 709043542
Relay_Master_Log_File: binlog.000284
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: databaseA.tableA,databaseA.tableB,databaseA.tableC,databaseA.tableD
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 709043399
Relay_Log_Space: 38579192969
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 258490
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: masterID
1 row in set (0.00 sec)

需要注意的是,待同步完成后,需要把从库配置文件中增加的 replicate-ignore-table 参数注释,并重启 MySQL。

四 原理探讨


在主库运行 LOAD DATA LOCAL INFILE,主库和从库时这样同步的:

  • 在主节点:
    1. 执行 LOAD DATA LOCAL INFILE;
    2. 拷贝使用的整个文本文件内容到二进制日志;
    3. 添加 LOAD DATA LOCAL INFILE 到最新的二进制日志。
  • 复制所有主库的二进制日志到从库的中继日志;
  • 在从节点:
    1. 检查中继日志中的文本文件;
    2. 从多个中继日志文件中读取所有的块;
    3. 文本文件存放在 /tmp 文件夹中;
    4. 从中继日志中读取 LOAD DATA LOCAL INFILE;
    5. 在 SQL 线程中执行 LOAD DATA LOCAL INFILE。

在从节点执行的 1-4 步骤中,IO 线程会呈现 Reading event from the relay log 状态,持续地为下一个 LOAD DATA LOCAL INFILE 命令提取 CSV 行。此时从库会持续落后,一旦从库落后时间较长,会导致 SQL 线程阻塞,呈现 Queueing master event to the relay log 状态,从而复制夯住。

五 小结


这样的故障,归根结底还是研发写的程序还有优化的余地。大批量的数据插入,这在 MySQL 中是不推荐使用的。我们可以这样:第一,一条 SQL 语句插入多条数据;第二,在事务中进行插入处理;第三,分批插入,在程序中设置 auto_commit 为 0,分批插入完成后,手动 COMMIT;第四,需要使用 LOAD DATA LOCAL INFILE 时,设置 sync_binlog 为 1。


本站推广
币安是全球领先的数字货币交易平台,提供比特币、以太坊、BNB 以及 USDT 交易。

币安注册: https://accounts.binancezh.cc/cn/register/?ref=11190872
邀请码: 11190872


–EOF–
题图来自:secaserver.com, By SecaGuy.
版权声明:自由转载-非商用-非衍生-保持署名(创意共享4.0许可证)



原文地址 : MySQL 复制夯住排查以及原理探讨 | 区块链罗宾
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

Mysql学习笔记 - 开发篇

1.mysql帮助的使用
      ? contents  
           目录
      ? data types
           目录下个一个类型
      ? int
           数据类型下个一个int类型
      ? show
           使用关键字 show 进行搜索
      ? create table
           使用语法 create table 进行搜索
2.表类型,存储引擎的选择
      支持的引擎  MyISAM(默认引擎),  InnoDB( **支持事务),  **BDB(**支持事务),**MEMORY,
Merge,Example,NDB Cluster,Archive,CSV,BlackHole,Federated

      常用表的比较
      

特点 Myisam BDB Memory InnoDb Archive





存储限制 没有 没有 64TB 没有





事务安全
支持
支持






锁机制 表锁 页锁 表锁 行锁 行锁





B树索引 支持 支持 支持 支持






哈希索引

支持 支持






全文索引 支持









集群索引


支持






数据缓存

支持 支持






索引缓存 支持
支持 支持






数据可压缩 支持


支持





空间使用 N/A 非常低





内存使用 中等





批量插入的速度 非常高





支持外键


支持






    
      Myisam  
           默认的mysql数据库存储引擎, 创建是创建三个文件 .frm(存储表定义), .myd(存储数据), .myi(存储索引), 数据文件和索引文件可以放置在不同的目录,平均分配io, 获取更快的速度, web,数据仓储和其他应用环境下最常用的引擎
      InnoDb
           具有,提交, 回滚, 崩溃回复能力 的事务安全, 但是对比myisam innoDb写的处理效果差些并且会占用更多的空间来存放数据和索引, 包括acid事务支持
      Memory
           将数据保存在 Ram 中, 需要快速查找引用和其他类似数据环境下, 提供极快的访问
      Merge
           允许mysqlDBA或开发人员将一系列的等同的MyISAM表以逻辑方式组合在一起, 并作为一个对象引用他们.对于数据仓储等VLDB环境十分适合
3. 选择合适的数据类型
      数据表的存储引擎和数据列
           MyISAM
                最好使用固定长度的数据列来代替可变的数据列
           Memory          
                目前都是用固定的数据行存储
           InnoDB
                建议使用varchar, 内部的存储格式没有区分char和varchar, 主要的性能因素是数据行使用的存储总量, 所以使用varchar来降低空间就是对性能好的优化
4. 数据值的对比
      char 和 varchar
           1.存储空间的对比
            
           例如最后一种情况, 如果数据运行在严格模式, 超出规定的值不会保存,而是报错
           2. 搜索的时候varchar不会忽略存储的空格,但是char可以忽略存储的空格.
            
      text和blog
                    1. blob 和 text 在执行大量删除和更新后会产生碎片,建议定期使用optimize table 功能对其进行碎片整理
           2. 使用合成的索引
           3. 避免检索大型的blob或text索引
           4. 把blob 或text 列分离到单独的表中
      浮点数和定点数
           插入的数值,如果是浮点数, 可能会造成不确定性
           数据库设计
            
           执行语句
            
           结果是
            
           在mysql中 float, double是浮点数, decimal是定点数

  - 浮点数会存在误差
  - 涉及到精度敏感的数据,应该用定点数来表示或者存储
  - 编程时候用到浮点数,尽量避免使用浮点数做比较
  1. 字符集的选择
          显示字符集
                    SHOW CHARACTER SET
          字符集和校对规则
               字符集规定了myusql存储字串的方式,校对规则定义了比较字串的方式, 每个字符集至少对应一个校对规则
                SHOW COLLATION LIKE ‘utf8%’   显示utf8 的校对规则
          怎样选择合适的字符集
               建议在满足应用的前提下,尽量使用小的字符集, 因为小的字符集意味着能够节省空间,减少网络传输字节数,同时由于存储空间的较小间接的提高了系统的性能          
          mysql字符集的设置
               字符集和校对规则有四个默认级别的默认设置, 服务器级别, 数据库级, 表级和字段级, 分别在不同的地方设置作用也不相同
               显示当前的服务器字符集和校对规则
                    SHOW VARIABLES LIKE ‘character_set_server’
    6.索引的设计和使用
          每个存储引擎支持每个表至少16个索引
          只有 MyISAM支持 fulltext 索引, 并且只是支持 char, varchar, text列
          设计原则
               搜索的索引列不一定是要选择的列, 适合索引的列是 where后的列, 而不是出现在select后的列
               使用唯一索引,考虑列的分部, 唯一值的列索引效果更好
               使用短索引, 如果有一个char(200)列, 如果在前10个或20个字符内,多数值是唯一的, 那么就不要对整个列进行索引, 因为短索引能够节省大量的空间,所以查询更快.
               最左前缀
               不要过度索引
               考虑列上的比较类型,索引可用字符和between运算
          BTree索引和hash索引
          如何使用索引
               索引用于快速找出在某个列上的特定值的行,如果不使用索引,mysql必须从第一行开始然后读完整个表直到找出相关的行
               大多数索引(Primary Key, Unique, Index, fulltext) 使用btree, 只是空间列类型的索引使用R-tree, 并且Memory还支持hash索引
    7. 锁机制和事务控制
          加锁
               Lock tables tbl_name
               unlock tables
               innodb提供 行级锁, 支持共享锁和排他锁.
          死锁
               Innodb自动检测事务的死锁, 并回滚一个或几个事务来防止死锁
          事务控制
               SET AUTOCOMMIT, START TRANSACTION, COMMIT, ROLLBACK
               事务的设置
                            select @@autocommit
                   当前的自动提交的值  
               事务的开始
                              START TRANSACTION或BEGIN语句可以开始一项新的事务
               事务的回滚
                    COMMIT和ROLLBACK用来提交或者回滚事务
               CHAIN 和 RELEASE 定义在事务提交或者回滚之后的操作
                    CHAIN 会立即启动一个新事务, 如果是事务处理模式,不适用这个CHAIN也会开始一个新事务,  
                    RELEASE 则会断开和客户端的链接
               SAVEPOINT
                    指定回滚事务的一个部分
                    SAVEPOINT test;
                    可以回滚到保存的一个部分
                    ROLLBACK TO SAVEPOINT test;
    8.SQL中的安全问题
          SQL注入
               利用数据库的外部接口把用户数据插入到实际的数据库操作语言中.
               ? username=angel’ or ‘1=1    逻辑运算
                    ?username=angel’/*          注释
                ?username=angel’%23         %23 变成#的注释
          预防
               prepareStatement + Bind-variable
                    使用mysqli中的prepared语句
                    绑定变量
               使用应用程序提供的转换函数
                    MySql修饰符
                                        escape 和quote 修饰
                    PHP
                                        mysql_real_escape_string()
               自定义函数来校验
                    让数据变得有效
                    拒绝非法输入
                    接收合法的输入
    9. SQL Mode(数据库模式)
          查看默认的sql_mode
                    SELECT @@sql_mode
          设置数据库模式
               set session sql_mode=”STRICT_TRANS_TABLES”
          常用的sql_mode
               ‘’: 默认是空的话,插入多余的字串会截断并且插入
               ANSI: 更改语法和行为,使其更符合标准SQL
                STRICT_TRANS_TABLES
                    如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1 行,则放弃该语句
                    TRADITIONAL
                              使MySQL 的行为象“传统”SQL 数据库系统
                    如果是非事务存储引擎, 出现错误会保留已经存储进数据库的数据.
          sqlmode和数据校验
               校验日期数据合法性
               零除错误(MOD(x,0))
               将 ‘“‘ 识别为 ‘`’
               禁用反斜线(‘') 作为字串内的退出字符
               将(||)视为 (+) 而不是 OR
    10. 常用sql技巧
          检索包含最大值和最小值的行
               min(column)
               max(column)
          巧用rand提取随机行
               select * from tbl_name order by rand();
          WITH ROLLUP 修饰符
                    GROUP BY子句允许一个将额外行添加到简略输出端 WITH ROLLUP 修饰符。这些行代表高层(或高聚集)简略操作
          Group by 子句
               根据某一个列来统计信息
          BIT_OR   BIT_AND   BIT_XOR
               位操作
    11. 其他需要注意的问题
          数据库名称的大小写
               平台不同, 对数据库或者表的大小写敏感度就不同
               为了平台之间的整合或者是相匹配,最好是都用同样的格式
          使用外键需要注意的地方
               对于除InnoDB类型的表,当使用 REFERENCES tbl_name(col_name) , 可以使用该子句, 只是作为备忘录或者注释来提醒.
                    
          

      


原文地址 : Mysql学习笔记 - 开发篇
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

mysql - 密码修改/重置密码

更新密码

1
2
3
4
5
-- set
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('*****');
-- upate
UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root';
FLUSH PRIVILEGES;

mysqladmin 方式

格式:mysqladmin -u用户名 -p旧密码 password 新密码

1
2
3
4
# 给root加个密码ab12, 开始时root没有密码,所以-p旧密码一项就可以省略
mysqladmin -u root -password ab12。
# 将root的密码改为djg345。
mysqladmin -u root -p ab12 password djg345

Mysql 重置 root密码

写这个东西的原因是我设置用户权限的时候将 root的localhost给干掉了, 结果连不上数据库了, 于是重置mysql的密码

  1. 关闭当前的mysql服务

    1
    > net stop mysql
  2. 使用管理员跳过权限的方法开启mysql服务

    1
    2
    3
    4
    5
    6
    # mysql5.1
    > mysqld --skip-grant-tables --user=mysql;
    # winserver
    > mysqld-nt --skip-grant-tables;
    # linux
    > mysqld_safe --skip-grant-tables
  3. 使用命令进入mysql控制台(更新密码)

    1
    2
    3
    4
    5
    6
    7
    8
    > mysql -uroot
    mysql > use mysql;
    # mysql <=5.6
    mysql > update user set password=password('some password') where user='root';
    # mysql 5.7+
    mysql > update user set authentication_string=password('some password') where user='root'
    mysql > flush privileges;
    mysql > exit;
  4. 添加用户

    1
    2
    3
    mysql > grant all privileges on *.* to 'yourname'@'%' identified by 'youpasswd'
    mysql > flush privileges
    mysql > exit
  5. 重启mysql服务器

    1
    2
    > net stop mysql
    > net start mysql;

    重试登陆OK


原文地址 : mysql - 密码修改/重置密码
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

MySQL数据库高并发优化配置-Holy-51CTO博客

MySQL数据库高并发优化配置

                [![](https://file.wulicode.com/yuque/202208/24/23/13249zGCzjzm.jpg?x-oss-process=image/resize,h_120)](https://blog.51cto.com/holy2010)				<br />				[holy2009](https://blog.51cto.com/holy2010)									                	                    [关注]()	                	            				[0人评论]()								[7119人阅读](#)				[2017-08-11 09:27:30](#)				

在Apache, PHP, mysql的体系架构中,MySQL对于性能的影响最大,也是关键的核心部分。对于Discuz!论坛程序也是如此,MySQL的设置是否合理优化,直接 影响到论坛的速度和承载量!同时,MySQL也是优化难度最大的一个部分,不但需要理解一些MySQL专业知识,同时还需要长时间的观察统计并且根据经验 进行判断,然后设置合理的参数。
 
 ``下面我们了解一下MySQL优化的一些基础,MySQL的优化我分为两个部分,一是服务器物理硬件的优化,二是MySQL自身(my.cnf)的优化。
 
一、服务器硬件对MySQL性能的影响
 
① 磁盘寻道能力(磁盘I/O),以目前高转速SCSI硬盘(7200转/秒)为例,这种硬盘理论上每秒寻道7200次,这是物理特性决定的,没有办法改变。 MySQL每秒钟都在进行大量、复杂的查询操作,对磁盘的读写量可想而知。所以,通常认为磁盘I/O是制约MySQL性能的最大因素之一,对于日均访问量 在100万PV以上的Discuz!论坛,由于磁盘I/O的制约,MySQL的性能会非常低下!解决这一制约因素可以考虑以下几种解决方案:  使用RAID-0+1磁盘阵列,注意不要尝试使用RAID-5,MySQL在RAID-5磁盘阵列上的效率不会像你期待的那样快。
 
②CPU 对于MySQL应用,推荐使用S.M.P.架构的多路对称CPU,例如:可以使用两颗Intel Xeon 3.6GHz的CPU,现在我较推荐用4U的服务器来专门做数据库服务器,不仅仅是针对于mysql。
 
③物理内存对于一台使用MySQL的Database Server来说,服务器内存建议不要小于2GB,推荐使用4GB以上的物理内存,不过内存对于现在的服务器而言可以说是一个可以忽略的问题,工作中遇到了高端服务器基本上内存都超过了16G。
 
二、 MySQL自身因素
 
当解决了上述服务器硬件制约因素后,让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。  由于my.cnf文件的优化设置是与服务器硬件配置息息相关的,因而我们指定一个假想的服务器硬件环境:
 
下面,我们根据以上硬件配置结合一份已经优化好的my.cnf进行说明:
 
 
#vim /etc/my.cnf以下只列出my.cnf文件中[mysqld]段落中的内容,其他段落内容对MySQL运行性能影响甚微,因而姑且忽略。
 ``代码如下   复制代码
[mysqld]
port = 3306
serverid = 1
socket = /tmp/mysql.sock
skip-locking
#避免MySQL的外部锁定,减少出错几率增强稳定性。
skip-name-resolve
#禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求!
back_log = 384
#back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。  如果系统在一个短时间内有很多连接,则需要增大该参数的值,该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
key_buffer_size = 256M
#key_buffer_size指定用于索引的缓冲区大小,增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意:该参数值设置的过大反而会是服务器整体效率降低!
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小。注意:该参数对应的分配内存是每连接独占,如果有100个连接,那么实际分配的总共排序缓冲区大小为100 × 6 = 600MB。所以,对于内存在4GB左右的服务器推荐设置为6-8M。
read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小,和sort_buffer_size一样,该参数对应的分配内存也是每连接独享。
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
# 指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的 情况;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,如果该值较小反而会影响效率,那么可以考虑不用查询缓 冲;Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示,则需要增大该参数值。
max_connect_errors = 10000000
wait_timeout = 10
#指定一个请求的最大连接时间,对于4GB左右内存的服务器可以设置为5-10。
thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量*2,在本例中,服务器有2颗物理CPU,而每颗物理CPU又支持H.T超线程,所以实际取值为4*2=8
skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式,如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项!否则将无法正常连接!
table_cache=1024
#物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M
#默认为2M
innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=2M
#默认为1M
innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M
#默认为218,调到128最佳
tmp_table_size=64M
#默认为16M,调到64-256最挂
read_buffer_size=4M
#默认为64K
read_rnd_buffer_size=16M
#默认为256K
sort_buffer_size=32M
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32M
 
 
如果从数据库平台应用出发,我还是会首选myisam.
 
PS:可能有人会说你myisam无法抗太多写操作,但是我可以通过架构来弥补,说个我现有用的数据库平台容量:主从数据总量在几百T以上,每天十多亿 pv的动态页面,还有几个大项目是通过数据接口方式调用未算进pv总数,(其中包括一个大项目因为初期memcached没部署,导致单台数据库每天处理 9千万的查询)。而我的整体数据库服务器平均负载都在0.5-1左右。
 
MyISAM和InnoDB优化:
 
key_buffer_size – 这对MyISAM表来说非常重要。如果只是使用MyISAM表,可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 — 记住,MyISAM表会使用操作系统的缓存来缓存数据,因此需要留出部分内存给它们,很多情况下数据比索引大多了。尽管如此,需要总是检查是否所有的 key_buffer 都被利用了 — .MYI 文件只有 1GB,而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表,那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。
 
innodb_buffer_pool_size – 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以,然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来,无需留给操作系统太多的内存,因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大,并且不会暴增,那么无需把 innodb_buffer_pool_size 设置的太大了。
 
innodb_additional_pool_size – 这个选项对性能影响并不太多,至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大),因此就需要看一下Innodb其他需要分配的内存有多少。
 
innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高,但是要注意到可能会增加恢复时间。我经常设置为 64-512MB,跟据服务器大小而异。
 
innodb_log_buffer_size 默 认的设置在中等强度写入负载以及较短事务的情况下,服务器性能还可 以。如果存在更新操作峰值或者负载较大,就应该考虑加大它的值了。如果它的值设置太高了,可能会浪费内存 — 它每秒都会刷新一次,因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
 
innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大?看来也许你忘了修改这个参数了。默认值是 1,这意味着每次提交的更新事务(或者每个事务之外的语句)都会刷新到磁盘中,而这相当耗费资源,尤其是没有电池备用缓存时。很多应用程序,尤其是从 MyISAM转变过来的那些,把它的值设置为 2 就可以了,也就是不把日志刷新到磁盘上,而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去,因此通常不会丢失每秒1-2次更新的消耗。如果设置 为 0 就快很多了,不过也相对不安全了 — MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
 
table_cache — 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁,所以通常要加大缓存数量,使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存,对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话,那么设置为 1024 也许比较合适(每个线程都需要打开表),如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。
 
thread_cache — 线程的创建和销毁的开销可能很大,因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大,那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。
 
query_cache — 如果你的应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不要把它设置太大了,因为想要维护它也需要不少开销,这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间,查看是否运行良好。在一定的负载压力下,如果缓存命中率太低了,就启用它。
 
sort_buffer_size –如果你只有一些简单的查询,那么就无需增加它的值了,尽管你有 64GB 的内存。搞不好也许会降低性能。


原文地址 : MySQL数据库高并发优化配置-Holy-51CTO博客
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

Fix: Cannot load from mysql.proc. The table is probably corrupted

Fix: Cannot load from mysql.proc. The table is probably corrupted

| | After upgrading MySQL from version 5.0 to 5.5 I started getting the following message trying to edit table structure in Navicat:
Cannot load from mysql.proc. The table is probably corrupted
Luckily there is an easy fix. Run the following command from the command prompt (on Windows you might need to run it from MySQL’s  bin  directory).
mysql_upgrade
In some cases you will need specify mysql root password and  –force  option (if you run upgrade procedure already).
mysql_upgrade.exe -uroot -p  –force |
| — |

|
| — |


原文地址 : Fix: Cannot load from mysql.proc. The table is probably corrupted
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

mysql百万级以上查询优化

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在  where  及  order  by  涉及的列上建立索引。

2.应尽量避免在  where  子句中对字段进行  null  值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:  

select  id  from  t  where  num  is  null  

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:  

select  id  from  t  where  num=0

3.应尽量避免在  where  子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在  where  子句中使用  or  来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:  

select  id  from  t  where  num=10  or  num=20  

可以这样查询:  

select  id  from  t  where  num=10  

union  all  

select  id  from  t  where  num=20

5.in  和  not  in  也要慎用,否则会导致全表扫描,如:  

select  id  from  t  where  num  in(1,2,3)  

对于连续的数值,能用  between  就不要用  in  了:  

select  id  from  t  where  num  between  1  and  3

6.下面的查询也将导致全表扫描:  

select  id  from  t  where  name  like  ‘%abc%’  

若要提高效率,可以考虑全文检索。

7.如果在  where  子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:  

select  id  from  t  where  num=@num  

可以改为强制查询使用索引:  

select  id  from  t  with(index(索引名))  where  num=@num

8.应尽量避免在  where  子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:  

select  id  from  t  where  num/2=100  

应改为:  

select  id  from  t  where  num=100*2

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:  

select  id  from  t  where  substring(name,1,3)=’abc’–name以abc开头的id  

select  id  from  t  where  datediff(day,createdate,’2005-11-30’)=0–‘2005-11-30’生成的id  

应改为:  

select  id  from  t  where  name  like  ‘abc%’  

select  id  from  t  where  createdate>=’2005-11-30’  and  createdate<’2005-12-1’

10.不要在  where  子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:  

select  col1,col2  into  #t  from  t  where  1=0  

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:  

create  table  #t(…)

13.很多时候用  exists  代替  in  是一个好的选择:  

select  num  from  a  where  num  in(select  num  from  b)  

用下面的语句替换:  

select  num  from  a  where  exists(select  1  from  b  where  num=a.num)

14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好,索引固然可以提高相应的  select  的效率,但同时也降低了  insert  及  update  的效率,因为  insert  或  update  时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新  clustered  索引数据列,因为  clustered  索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新  clustered  索引数据列,那么需要考虑是否应将该索引建为  clustered  索引。

17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

18.尽可能的使用  varchar/nvarchar  代替  char/nchar  ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用  select  *  from  t  ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用  select  into  代替  create  table,避免造成大量  log  ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create  table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先  truncate  table  ,然后  drop  table  ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27.与临时表一样,游标并不是不可使用。对小型数据集使用  FAST_FORWARD  游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置  SET  NOCOUNT  ON  ,在结束时设置  SET  NOCOUNT  OFF  。无需在执行存储过程和触发器的每个语句后向客户端发送  DONE_IN_PROC  消息。

29.尽量避免大事务操作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。


原文地址 : mysql百万级以上查询优化
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

高性能mysql

前言~软件版本和可用性:Percona Toolkit 是不可多得的mysql管理工具
1.1 mysql逻辑架构:mysql服务器逻辑架构图

基准测试工具~sysbench


原文地址 : 高性能mysql
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问

MySQL快速复制数据库的方法


某些时候,例如为了搭建一个测试环境,或者克隆一个网站,需要复制一个已存在的mysql数据库。使用以下方法,可以非常简单地实现。
假设已经存在的数据库名字叫db1,想要复制一份,命名为newdb。步骤如下:

  1. 创建新的数据库 newdb

阅读更多

mysql 数据去重

适用于重复比例较小的情况

总数据量:134W

  1. 创建临时表

    1
    2
    3
    create table dupeddata as(
    Select * from content group by tit having count(id) > 1
    )
  2. 删除表中的重复数据

    1
    Delete from content Where tit in ( select tit from dupeddata );
  3. 插入临时表中的数据到原数据表

    1
    Insert into content (Select * from dupeddata);

    4、删除临时表

    1
    Drop table dupeddata;

适用于重复比例较大的情况

1、创建临时表来存储有duplicate rows的记录

1
Create table dupeddata_dis as(Select distinct tit, content from content );

2、删除原表中的duplicate rows的记录

1
Delete from table;

3、把临时表中的记录插入到原表中。

1
Insert into table Select * from dupeddata;

4、删除临时表

1
Drop table dupeddata;

原文地址 : mysql 数据去重
本站是作者语雀文档的镜像站, 如对文章有任何疑问请移步语雀进行 提问