Pomelo Lee workspace

设置系统及myslq时区信息，避免程序时间不配置

星期五, 七月 29th, 2022 | JAVA-and-J2EE, linux | 没有评论

起因程序用了 date 做查询判断，因系统及mysql不一致，导致插入到数据库的时间和系统时间有差距

造成查询失误，重复插入的问题,故需要调成一致即可.

timedatectl set-timezone "America/New_York"
 
##在[mysqld]区域中加上
vim /etc/my.cnf 
default-time_zone = '-4:00'
###或者##
default-time_zone = 'America/New_York'
 
#/etc/init.d/mysqld restart ##重启mysql使新时区生效

Tags: java, mysql

关于如何解决mysql库名大写导致大小写敏感参数报错的问题

星期一, 七月 25th, 2022 | linux | 没有评论

网上很多都是说明mysql表大小写参数的控制，如果库名是大写，调整此参数会造成找不到数据库，切记.

调整方法：调整成敏感，再都转换成小写部分.

lower_case_table_names

表示表名是否大小写敏感，可以修改。

lower_case_table_names = 0时，mysql会根据表名直接操作，大小写敏感。

lower_case_table_names = 1时，mysql会先把表名转为小写，再执行操作。

注意此处有一个大问题

当设置了大小写不敏感的时候。。。此时库名是大写的。。。会产生找不到库的错误

这是因为 设置大小写不敏感 mysql去找库的时候 就会变成小写的名字去找。。。

然而 此时 数据目录中 数据库目录 还是大写的。。此时就报错了。。。

Tags: mysql

mysql5.5版本数据类型、索引、查询的优化技巧（5.7版本适用）

星期三, 四月 13th, 2022 | Database, JAVA-and-J2EE | 没有评论

mysql5.5版本数据类型、索引、查询的优化技巧（5.7版本适用）

总结一些MySQL的常见使用技巧，以供没有DBA的团队参考。以下内容以MySQL5.5为准，如无特殊说明，存储引擎以InnoDB为准。

最新版8.0已经优化很多了，比如尽量使用子查询等,不断实践才能验证，做好数据压测即可检测.

本文大纲：

MySQL的特点

数据类型优化

索引优化

查询优化

一、MySQL的特点

了解MySQL的特点有助于更好的使用MySQL，MySQL和其它常见数据库最大的不同在于存在存储引擎这个概念，存储引擎负责存储和读取数据。不同的存储引擎具有不同的特点，用户可以根据业务的特点选择适合的存储引擎，甚至是开发一个新的引擎。

MySQL的逻辑架构大致如下：

MySQL默认的存储引擎是InnoDB，该存储引擎的主要特点是：

支持事务处理

支持行级锁

数据存储在表空间中，表空间由一些列数据文件组成

采用MVVC(多版本并发控制)机制实现高并发

表基于主键的聚簇索引建立

支持热备份

其它常见存储引擎特点概述：

MyISAM：老版本MySQL的默认引擎，不支持事务和行级锁，开发者可以手动控制表锁；支持全文索引；崩溃后无法安全恢复；支持压缩表，压缩表数据不可修改，但占用空间较少，可以提高查询性能

Archive：只支持Insert和Select，批量插入很快，通过全表扫描查询数据

SCV：把一个SCV文件当做一个表处理

Memory：数据存储在内存中

还有很多，不再一一列举。

二、数据类型优化

选择数据类型的原则：

选择占用空间小的数据类型

选择简单的类型

避免不必要的可空列

占用空间小的类型更节省硬件资源，如磁盘、内存和CPU。

尽量使用简单的类型，如能用int就不用char，因为后者的排序涉及到字符集的选择，比使用int复杂。

可空列使用更多的存储空间，如果在可空列上创建索引，MySQL需要额外的字节做记录。

创建表时，默认都是可空，容易被开发者忽视，最好是手动改为不可空，如果要存储的数据确实不会有空值的话。

1、整型类型

整型类型包括：

tinyint
 
SMALLINT
 
mediumint
 
INT
 
BIGINT

它们分别使用8、16、24、32和64位存储数字，它们可以表示−2n−1−2n−1到2n−1−12n−1−1范围的数字，前面可以加unsigned修饰，这样可以让正数的可表示范围提高1倍，但是无法表示负数。

另外，为整型指定长度没什么卵用，数据类型定下来，长度也就相应定下来了。

2、小数类型

FLOAT
 
DOUBLE
 
DECIMAL

float和double就是通常意义上的float和double，前者使用32位存储数据，后者使用64位存储数据，和整型一样，为它们指定长度没什么卵用。

decimal类型比较复杂，支持精确计算，占用的空间也大，decimal使用每4个字节表示9个数字，如decimal(18,9)表示数字长度是18，其中小数位9个数字，整数部分9个数字，加上小数点本身，共占用9个字节。

考虑到decimal占用空间较多，以及精度计算很复杂，数据量大的时候可以考虑用bigint代替之，可以在持久化和读取前对真实数据进行一些缩放操作。

3、字符串类型

VARCHAR
 
CHAR
 
varbinary
 
BINARY
 
BLOB
 
text

枚举
varchar类型数据实际占用空间等于字符串的长度加上1个或2个用来记录字符串长度的字节（当row-format没有被设置为fixed时），varchar很节省空间。当表中某列字符串类型的数据长度差别较大时适合使用varchar。

char的实际占用空间是固定的，当表中字符串数据的长度相差无几或很短时适合使用chart类型。

与varchar和char对应的有varbinary和binary，后者存储的是二进制字符串，和前者相比，后者大小写敏感，不用考虑编码方式，执行比较操作时更快。

需要注意的是：虽然varchar(5)和varchar(200)在存储“hello”这个字符串时使用相同的存储空间，但并不意味着将varchar的长度设置太大不会影响性能，实际上，MySQL的某些内部计算，比如创建内存临时表时（某些查询会导致MySQL自动创建临时表），会分配固定大小的空间存放数据。

blob使用二进制字符串保存大文本，text使用字符保存大文本，InnoDB会使用专门的外部存储区来存放此类数据，数据行内仅存放指向他们的指针，此类数据不宜创建索引（要创建也只能正对字符串前缀创建），不过也不会有人这么干。

如果某列字符串大量重复且内容有限，可使用枚举代替，MySQL处理枚举时维护了一个“数字-字符串”表，使用枚举可以减少很多存储空间。

4、时间类型

YEAR
 
DATE
 
TIME
 
datetime
 
TIMESTAMP

datetime存储范围是1001到9999，精确到秒。

timestamp存储1970年1月1日午夜以来的秒数，可以表示到2038年。占用4个字节，是datetime占用空间的一半。timestamp表示的时间和时区有关，另外timestamp列还有个特性，执行insert或update语句时，MySQL会自动更新第一个类型为timestamp的列的数据为当前时间。

很多表中都有设计有一列叫做UpdateTime，这个列使用timestamp倒是挺合适的，会自动更新，前提是系统不会使用到2038年。

5、主键类型的选择

尽可能使用整型，整型占用空间少，还可以设置为自动增长。尤其别使用GUID，MD5等哈希值字符串作为主键，这类字符串随机性很大，由于InnoDB主键默认是聚簇索引列，所以导致数据存储太分散。

另外，InnoDB的二级索引列中默认包含主键列，如果主键太长，也会使得二级索引很占空间。

6、特殊类型的数据

存储IP最好使用32位无符号整型，MySQL提供了函数inet_aton()和inet_ntoa()进行IP地址的数字表示和字符串表示之间的转换。

三、索引优化

InnoDB使用B+树实现索引，举个例子，假设有个People，建表语句如下

CREATE TABLE `people` (
 
  `Id` INT(11) NOT NULL AUTO_INCREMENT,
 
  `Name` VARCHAR(5) NOT NULL,
 
  `Age` tinyint(4) NOT NULL,
 
  `Number` CHAR(5) NOT NULL COMMENT '编号',
 
  PRIMARY KEY (`Id`),
 
  KEY `i_name_age_number` (`Name`,`Age`,`Number`)
 
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

插入数据：

它的索引结构大致是这样的：

也就是说，索引列的顺序很重要，如果两行数据的Name列相同，则用Age列比较大小，如果Age列相同，则用Number列比较大小。先用第一列排序，然后是第二列，最后是第三列。

查询的使用应该尽量从左往右匹配，另外，如果左边列范围查找，右边列无法使用索引；还有就是不能隔列查询，否则后面的索引也无法使用到。如以下几个SQL是正面范例：

 
SELECT * FROM people WHERE Name ='Abel' AND Age = 2 AND NUMBER = 12312
 
SELECT * FROM people WHERE Name ='Abel'
 
SELECT * FROM people WHERE Name LIKE 'Abel%'
 
SELECT * FROM people WHERE Name = 'Andy' AND Age BETWEEN 11 AND 20
 
SELECT * FROM people ORDER BY NAME
 
SELECT * FROM people ORDER BY NAME, Age
 
SELECT * FROM people GROUP BY Name

以下几个SQL是反面范例：

 
SELECT * FROM people WHERE Age = 2
 
SELECT * FROM people WHERE NAME LIKE '%B'
 
SELECT * FROM people WHERE age = 2
 
SELECT * FROM people WHERE NAME = 'ABC' AND NUMBER = 3
 
SELECT * FROM people WHERE NAME LIKE 'B%' AND age = 22

1、一个使用Hash值创建索引的技巧

如果表中有一列存储较长字符串，假设名字为URL，在此列上创建的索引比较大，有个办法可以缓解：创建URL字符串的数字哈希值的索引。再新建一个字段，比如叫做URL_CRC，专门放置URL的哈希值，然后给这个字段创建索引，查询时这样写：

 
SELECT * FROM t WHERE URL_CRC = 387695885 AND URL = 'www.baidu.com'

如果数据量比较多，为防止哈希冲突，可自定义哈希函数，或用MD5函数返回值的一部分作为哈希值：

 
SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)

2、前缀索引

如果字符串列存储的数据较长，创建的索引也很大，这时可以使用前缀索引，即：只针对字符串前几个字符做索引，这样可以缩短索引的大小，不过，显然，此类索引在执行order by和group by时不起作用。

创建前缀索引时选择前缀长度很重要，在不破坏原来数据分布的情况下尽可能选择较短的前缀。举个例子，如果如果大部分字符串是以”abc”开头，那么如果限定前缀索引长度为4，索引值会包含太多的重复的”abcX”。

3、多列索引

上面提到的“People”上创建的索引即为多列索引，多列索引往往比多个单列索引更好。

对多个索引进行and查询时，应该创建多列索引，而不是多个单列索引。

可以试试这样写的效果：

SELECT * FROM t WHERE f1 = 'v1' AND f2 <> 'v2' UNION ALL SELECT * FROM t WHERE f2 = 'v2' AND f1 <> 'v1'

多列索引的顺序很重要，通常，不考虑排序和分组查询时，应该把选择性（选择性是指某表索引列不同数据的个数/总行数。选择性高意味着重复数据少）大的列放到前面。但也有例外，如果能确认某些查询是频繁执行的，则应该优先照顾这些查询的选择性，比如，如果上面的People表中Name的选择性大于Age，查询语句应该这样写:

SELECT * FROM people WHERE name = 'xxx' AND age = xx

Name列放了索引中的左侧比较合适，但是如果某个SQL执行的评率最高，比如：

SELECT * FROM people WHERE name = 'xxx' AND age = 20

当age=20的记录在数据库中非常少时，反而把age放到索引列的左端效率更高。把age放了索引左端可能对其它age不等于20的查询来说不公平，如果不能确定age=20是最非常频繁的查询条件，还是要综合考虑，把name放了左侧合适。

4、聚簇索引

聚簇索引是一种数据存储结构，InnoDB在主键的索引的叶子节点中直接保存了数据行，而不是像二级索引那样只是保存了索引列的值和所指向行的主键值。由于这个特性，一个表只能有一个聚簇索引。如果一个表没有定义主键也没有定义具有唯一索引的列，那么InnoDB会生成一个隐藏列，并且在此列设为聚簇索引列。

5、覆盖索引
简单地说，某些查询只需要查询索引列，那么就不用再根据索引B树节点记录的主键ID进行二次查询了。

6、重复索引和冗余索引

如果重复在某列创建索引，并不会带来任何好处，只有坏处，应该尽量避免。比如给主键创建唯一索引和普通索引就是多于的，因为InnoDB的主键默认就是聚簇索引了。

冗余索引和重复索引不同，比如某个索引是(A,B)，另一个索引是（A），这叫冗余索引，前者可以代替后者，后者不可以代替前者的作用。但是(A,B)和（B）以及（A,B）和（B,A）不算冗余索引，起作用谁也代替不了谁。

如果一个表中已经存在索引(A)，现在又想创建索引(A,B),那么只需扩展就的索引就可以，没有必要创建新的索引。需要注意的是如果已经存在索引(A)，那么也没有必要在创建索引(A,ID)，其中ID指主键，因为索引A默认已经包含了主键了，也算是冗余主键。

但是，有时候，冗余索引也是可取的，假设已经存在索引(A)，将其扩展为(A,B)后，因为B列是一个很长的类型，导致用A单独查询时没有以前快了，这时可以考虑新创建索引(A,B)。

7、不使用的索引

不使用的索引徒然增加insert、update和delete的效率，应该及时删除。

8、索引使用总结

索引的三星原则：

索引将查询相关的记录按顺序放在一起则得一星

索引中的数据顺序和查询结果的排序一致则得一星

索引中包含了查询所需要的全部列则得一星

第一个条原则的意思是where条件中查询的顺序和索引是一致的，就是前面说的从左到右使用索引。

索引不是万能的，当数据量巨大时，维护索引本身也是耗费性能的，应该考虑分区分表存储。

四、查询优化
› Continue reading

Tags: mysql

centos8版本快速部署nginx,mysql安装

星期二, 一月 21st, 2020 | JAVA-and-J2EE, linux | 没有评论

0.centos8版本快速部署nginx,mysql安装

1.系统预检 查看版本

cat /etc/redhat-release

初始系统为8.0版本，执行更新后为8.1：

yum update
cat /etc/redhat-release

CentOS Linux release 8.1.1911 (Core)
› Continue reading

Tags: centos, centos8, mysql

centos7下的php-fpm的优化

星期四, 十月 3rd, 2019 | linux, php | 没有评论

云主机只有1C1G的内存，既要运行wordpress还要运行一个java程序及mysql，内存实在吃紧，运行1天发现mysql内存不足被杀掉，随优化之。

1.优化mysql参数

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

详情可以参考 https://www.pomelolee.com/1304.html

2.php-fpm优化，减少php-fpm的数量

配置文件地址：/etc/opt/remi/php70/php-fpm.conf

实际修改配置文件：/etc/opt/remi/php70/php-fpm.d/www.conf

systemctl stop php70-php-fpm.service

pm = dynamic
pm.start_servers = 2
pm.min_spare_servers = 2
pm.max_spare_servers = 5

systemctl restart php70-php-fpm.service

Tags: centos7, mysql, php

wordpress版本升级到5.2.3记录

星期日, 九月 8th, 2019 | wordpress | 没有评论

阿里云老是提醒wordpress系统安全问题之类，提醒的烦了就准备升级下,小计.

1.到服务器下载最新版 https://wordpress.org/download/

最新版地址：https://wordpress.org/latest.zip

2.覆盖后升级，发现需要php5.6+，自己的php版本还是5.5+系列

3.折腾，顺便把服务器的centos6.5的也更换到centos7.6，后来发现被坑了一天才完成整改升级计划

4.服务器更新系统很快，安装nginx-1.17.3，mysql5.7，导入数据，

遇到的问题：

4.1：mysql5.7 兼容0000-00-00 00：00：00的日期格式，

4.2：移除wp-seccode插件（不兼容php7.0）

4.3： mysql_connect 更换成 mysqli_connect 等新函数（自己部署的其他应用部分）

5.总算正常进入及展示了，话说新的后台写文章的界面还真是漂亮

后台写文章界面如下图：

Tags: mysql, Nginx, wordpress

解决：mysql5.7 timestamp默认值‘0000-00-00 00:00:00’报错及Ubuntu Server下启动/停止/重启MySQL

星期六, 七月 27th, 2019 | JAVA-and-J2EE, linux | 没有评论

解决：mysql5.7 timestamp默认值‘0000-00-00 00:00:00’报错及Ubuntu Server下启动/停止/重启MySQL

1.Ubuntu Server下启动/停止/重启MySQL

启动mysql：
方式一：sudo /etc/init.d/mysql start 
方式二：sudo service mysql start
 
停止mysql：
方式一：sudo /etc/init.d/mysql stop 
方式二：sudo service mysql stop
 
重启mysql：
方式一：sudo/etc/init.d/mysql restart
方式二：sudo service mysql restart

2. ‘0000-00-00 00:00:00’报错的解决

使用 SELECT @@sql_mode; 查询当前的sql模式

更改模式版本 在my.cnf[mysqld]下添加 重启服务即可

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Tags: mysql

docker 下mysql时区8个小时的解决办法及JDBC连接数据库mysql serverTimezone useSSL时差

星期四, 六月 20th, 2019 | JAVA-and-J2EE, linux | 没有评论

问题起因：

1.应用启动初始化数据看了下插入的数据时间和本地时间不一致 差了8个小时

解决方法：
网上检索之发现最终引起的原因是新版JDBC驱动指定的serverTimezone的原因

1.修正docker下mysql时区的问题

	show variables like '%time_zone%';
 
	set global time_zone='Asia/Shanghai';
 
	set  time_zone='Asia/Shanghai';
 
	flush privileges;

实际上这么操作后，如果无法生效的话，还是要修改配置文件并重新启动mysql使其生效

docker下的路径在 /etc/mysql/conf.d/my.cnf

	修改my.cnf
 
	default-time-zone ='Asia/Shanghai'

查看 select now(); 等 如下图

2.因使用的新版的驱动需要调整的部分

› Continue reading

Tags: docker, mysql

mysql的全量(查询)日志general-log的开启

星期一, 五月 21st, 2018 | JAVA-and-J2EE, linux | 没有评论

怀疑有异常连接进来操作数据库则开启日志记录.

mysql> show global variables like '%general%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                         |
| general_log_file | /var/run/mysqld/mysqld.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)
 
mysql> set global general_log=on;
Query OK, 0 rows affected (0.00 sec)
 
mysql> set global general_log_file='/data/logs/mysql/mysql_general_log.log';
ERROR 1231 (42000): Variable 'general_log_file' can't be set to the value of '/data/logs/mysql/mysql_general_log.log'

1. 设置general log保存路径
2. 注意在Linux中只能设置到 /tmp 或 /var 文件夹下，设置其他路径出错
3. 需要root用户才有访问此文件的权限

4. 针对 2的一点注意 此版本是mysql-5.1.73 无法使用其他路径，可能新版本可以使用

已经尝试

chown -R mysql:mysql /data/logs/mysql/
chomd 777 /data/logs/mysql/mysql_general_log.log

Tags: mysql

支持emoji图标直接存储到数据库，更改MySQL数据库的编码为utf8mb4

星期一, 十月 23rd, 2017 | Database, JAVA-and-J2EE | 一条评论

随着emoji的频繁使用，对接微信后会带名字的emoji的很多，现在都需要支持.

utf-8编码可能2个字节、3个字节、4个字节的字符，但是MySQL的utf8编码只支持3字节的数据，而移动端的表情数据是4个字节的字符。

如果直接往采用utf-8编码的数据库中插入表情数据，java程序中将报SQL异常：

1
2
3
4
5
6
7
8
9

java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x92\x94’ for column ‘name’ at row 1 
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) 
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) 
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) 
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) 
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1662) 
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1581)

可以对4字节的字符进行编码存储，然后取出来的时候，再进行解码。但是这样做会使得任何使用该字符的地方都要进行编码与解码。

utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。

采用utf8mb4编码的好处是：存储与获取数据的时候，不用再考虑表情字符的编码与解码问题。

更改数据库的编码为utf8mb4:

1. MySQL的版本

utf8mb4的最低mysql版本支持版本为5.5.3+，若不是，请升级到较新版本。

2. MySQL驱动

5.1.34可用,最低不能低于5.1.13

3.修改MySQL配置文件
my.cnf一般在/etc/my.cnf位置。找到后请在以下三部分里添加如下内容：

1
2
3
4
5
6
7
8
9
10

[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4'
 
[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4

4. 重启数据库，检查变量

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
 
Variable_name	Value
character_set_client	utf8mb4
character_set_connection	utf8mb4
character_set_database	utf8mb4
character_set_filesystem	binary
character_set_results	utf8mb4
character_set_server	utf8mb4
character_set_system	utf8
collation_connection	utf8mb4_unicode_ci
collation_database	utf8mb4_unicode_ci
collation_server	utf8mb4_unicode_ci
collation_connection 、collation_database 、collation_server是什么没关系。
 
但必须保证
 
系统变量	描述
character_set_client	(客户端来源数据使用的字符集)
character_set_connection	(连接层字符集)
character_set_database	(当前选中数据库的默认字符集)
character_set_results	(查询结果字符集)
character_set_server	(默认的内部操作字符集)
这几个变量必须是utf8mb4。

5. 数据库连接的配置

数据库连接参数中:
characterEncoding=utf8会被自动识别为utf8mb4，也可以不加这个参数，会自动检测。
而autoReconnect=true是必须加上的。

6. 将数据库和已经建好的表也转换成utf8mb4

更改数据库编码：ALTER DATABASE xxdb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

更改表编码：ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
如有必要，还可以更改列的编码

› Continue reading

Tags: emoji, mysql, utf8mb