mysql表range分区

按天分区
CREATE TABLE `day` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `create_time` DATETIME NOT NULL COMMENT '入库时间',
  PRIMARY KEY (`id`,`create_time`),
) ENGINE=INNODB AUTO_INCREMENT=4190 DEFAULT CHARSET=utf8 COMMENT='网点系统接口请求日志表'
/*!50500 PARTITION BY RANGE  COLUMNS(create_time)
(PARTITION p20200904 VALUES LESS THAN ('2020-09-05') ENGINE = InnoDB,
 PARTITION p20200905 VALUES LESS THAN ('2020-09-06') ENGINE = InnoDB,
 PARTITION p20200906 VALUES LESS THAN ('2020-09-07') ENGINE = InnoDB,
 PARTITION p20200907 VALUES LESS THAN ('2020-09-08') ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

继续阅读“mysql表range分区”

mysql字段aes加解密

HEX()函数;二进制转换为16进制,方便存储

加密:加密对象 11 加密密钥:e10adc3949ba59abbe56e057f20f883e  结果:A13015D303BC1548608D6B4B8E900515

SELECT HEX(AES_ENCRYPT(’11’,’e10adc3949ba59abbe56e057f20f883e’));

解密:解密对象:A13015D303BC1548608D6B4B8E900515 解密密钥:e10adc3949ba59abbe56e057f20f883e 结果:11

SELECT AES_DECRYPT(UNHEX(‘A13015D303BC1548608D6B4B8E900515′),’e10adc3949ba59abbe56e057f20f883e’);

mysql更新大数据表-策略

大批量更新数据如果循环单条插入效率低下,时间大多耗费在mysql连接上,考虑减少链接。

方法1;使用CASE WHEN 拼成一条sql执行,减少数据库链接

UPDATE mytable SET
    myfield = CASE id
        WHEN 1 THEN 'value'
        WHEN 2 THEN 'value'
        WHEN 3 THEN 'value'
    END
WHERE id IN (1,2,3)

方法2;复制原表建立新表,处理好需要更新的数据,插入数据到新表,删除老表,改名新表

使用:

1,CREATE TABLE a6 LIKE a1;//复制原表结构和索引,不包含数据
2,CREATE TABLE a6 AS SELECT * FROM a1 ;//复制原表结构和数据,不包含索引

 

php-mysql-es数据写入效率

通过分析得出,查询和循环处理占时短,大部分时间花费在了es的bulk插入上,调优方向就是优化es批量

#sleep(1)的情况
100万数据 读5万 写1万 201秒
100万数据 读5万 写5万 207秒
100万数据 读10万 写1万 203秒
100万数据 读10万 写5万 203秒
100万数据 读10万 写10万 219秒
100万数据 读1万 写1万 344秒

#不加sleep的情况
100万数据 读5万 写1万 205秒
100万数据 读5万 写5万 209秒
100万数据 读10万 写1万 208秒
100万数据 读10万 写5万 198秒
100万数据 读10万 写10万 200秒
100万数据 读1万 写1万 227秒
100万数据 读1万 写5000 226秒

mysql查询效率还可以,es批量写入造成缓慢:
100万数据查询插入消耗250秒

调增’refresh_interval’ => ’30s’,100万数据查询插入230

调增’refresh_interval’ => ’60s’,100万数据查询插入221

调增’refresh_interval’ => ’60s’,100万数据查询插入229

每次插入1000查询插入240

100万数据查询12秒

100万数据查询逻辑循环18秒

500万查询73

500万查询逻辑循环92

MyISAM与InnoDB 的区别

区别:

1. InnoDB支持事务,MyISAM不支持,对于InnoDB每一条SQL语言都默认封装成事务,自动提交,这样会影响速度,所以最好把多条SQL语言放在begin和commit之间,组成一个事务;

2. InnoDB支持外键,而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败;

3. InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。

MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说:InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

 

4. InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快(注意不能加有任何WHERE条件);

那么为什么InnoDB没有了这个变量呢?

因为InnoDB的事务特性,在同一时刻表中的行数对于不同的事务而言是不一样的,因此count统计会计算对于当前事务而言可以统计到的行数,而不是将总行数储存起来方便快速查询。InnoDB会尝试遍历一个尽可能小的索引除非优化器提示使用别的索引。如果二级索引不存在,InnoDB还会尝试去遍历其他聚簇索引。
如果索引并没有完全处于InnoDB维护的缓冲区(Buffer Pool)中,count操作会比较费时。可以建立一个记录总行数的表并让你的程序在INSERT/DELETE时更新对应的数据。和上面提到的问题一样,如果此时存在多个事务的话这种方案也不太好用。如果得到大致的行数值已经足够满足需求可以尝试SHOW TABLE STATUS
5. Innodb不支持全文索引,而MyISAM支持全文索引,在涉及全文索引领域的查询效率上MyISAM速度更快高;PS:5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁

InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。潜台词是,如果访问没有命中索引,也无法使用行锁,将要退化为表锁。不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁

锁的粒度不同。MyISAM仅支持表锁。每次操作锁住整张表。这种处理方式一方面加锁的开销比较小,且不会出现死锁,但另一方面并发性能较差。InnoDB支持行锁。每次操作锁住一行数据,一方面行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源,速度较慢,且可能发生死锁,但是另一方面由于锁的粒度较小,发生锁冲突的概率也比较低,并发性较好。此外,即使是使用了InnoDB存储引擎,但如果MySQL执行一条sql语句时不能确定要扫描的范围,也会锁住整张表

MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表(没用索引)。

8、InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有

9、Innodb存储文件有frm、ibd,而Myisam是frm、MYD、MYI

Innodb:frm是表定义文件,ibd是数据文件

Myisam:frm是表定义文件,myd是数据文件,myi是索引文件

10. 缓存机制不同。MyISAM仅缓存索引信息,而不缓存实际的数据信息。而InnoDB不仅缓存索引信息,还会缓存数据信息。其将数据文件按页读取到缓冲池,然后按最近最少使用的算法来更新数据

 

如何选择:

1. 是否要支持事务,如果要请选择innodb,如果不需要可以考虑MyISAM;

2. 如果表中绝大多数都只是读查询,可以考虑MyISAM,如果既有读也有写,请使用InnoDB。

3. 系统奔溃后,MyISAM恢复起来更困难,能否接受;

4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM),说明其优势是有目共睹的,如果你不知道用什么,那就用InnoDB,至少不会差。

 

InnoDB为什么推荐使用自增ID作为主键?

答:自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。

 

innodb引擎的4大特性

插入缓冲(insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)

版权声明:本文为CSDN博主「Chackca」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_35642036/java/article/details/82820178

mysql类型长度剖析int,char(索引explain里面的key_len计算)

int

mysql中int(1)和int(11)区别呢,按理来讲int定义之后长度不受我们设置的限制了,那么mysql中int(1)和int(11)区别是什么呢?

mysql字段定义中INT(x)中的x仅仅指的是显示宽度。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度(前提 zerofill选项必须选择才会填充)int(5)如果插入的是1,并且定义zerofill,这数据库显示为00001。显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示。所以x的定义与存储空间没有任何关系都是4个字节

原文链接:https://blog.csdn.net/qq_34129814/java/article/details/80015434

char

char(x)和varchar(x),x代表可存储的长度,超过长度报错,这点和int不同

 

key_len计算

变长字段需要额外的2个字节,固定长度字段不需要额外的字节。而null都需要1个字节的额外空间,所以:索引字段最好不要为NULL,因为NULL让统计更加复杂,并且需要额外一个字节的存储空间。

key_len的长度计算公式:
int类型的是4,如果允许空则4+1 
varchr(10)变长字段且允许NULL      : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
varchr(10)变长字段且不允许NULL    : 10*(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL        : 10*(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)
char(10)固定字段且不允许NULL        : 10*(Character Set:utf8=3,gbk=2,latin1=1)

mysql索引左前缀的坑

1.建联合索引 a b c
2.使用到联合索引abc
EXPLAIN SELECT * FROM `people` t WHERE t.a=1 AND t.`b`=1 AND t.`c`=1;

 

3.虽然where里面的字段顺序和索引顺序不一致,但mysql优化器处理后,还是使用了联合索引abc
EXPLAIN SELECT * FROM `people` t WHERE t.c=1 AND t.`b`=1 AND t.`a`=1;
4.索引左前缀原则,where条件里没有a字段,所以查询没有走索引
EXPLAIN SELECT * FROM `people` t WHERE t.`b`=1 AND t.`c`=1;


5.mysql自动优化后,使用到了a字段索引,但没有使用联合索引abc
EXPLAIN SELECT * FROM `people` t WHERE t.c=1 AND t.`a`=1;

 

 

 

 

mysql主从复制和读写分离

一般通过主从复制的方式来同步数据,实现数据安全,再通过读写分离来提升数据库的高并发负载能力。

主从复制:异步复制,半同步复制,同步复制

复制步骤:

1. master在二进制日志记录这些改变,通知存储引擎提交事物。

2. Slave将master的binary log复制到其中的中继日志。

首先从mysql服务器开始一个工作线程I/O线程,I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master。他会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

3.Sql线程从中继日志中读取事件,重放其中的事件而更新slave的数据,使其与master的数据一致。

异步复制:MySQL默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理,这样就会有一个问题,主如果crash掉了,此时主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。
全同步复制:只当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能必然会收到严重的影响。
半同步复制:介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个TCP/IP往返的时间。所以,半同步复制最好在低延时的网络中使用。

读写分离:

读写分离就是只在mysql主服务器上写,只在mysql从服务器上读。基本原理是让主数据库处理事务性查询,而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到集群中的数据库。

目前较为常见的mysql读写分离有两种:

1、 基于程序代码的内部实现

在代码中根据select、insert进行路由分类,这类方法也是目前生产环境中较为常用的,优点是性能较好,因为在程序代码中实现,不需要增加额外的设备作为硬件开支;缺点是需要研发人员来实现,运维人员无从下手。

2、 基于中间代理层实现

代理一般位于客户端和服务器之间,代理服务器接收到客户端请求后通过判断后转发到后端数据库。如下有两个常用代理:

Mysql-proxy:其为mysql的开源项目,通过其自带的lua脚本进行sql判断,虽然是mysql官方产品,但是mysql官方并不建议其使用到生产环境中。

Amoeba:由陈思儒开发,该程序由Java语言进行开发。这个软件致力于mysql的分布式数据库前端代理层,它主要为应用层访问mysql的时候充当sql路由功能。Amoeba能够完成多数据源的高可用、负载均衡、数据切片等功能。

提高order by 速度

前提:order by已经使用索引 using index级别,排序类型一致,要么升序要么降序

1.不使用 select *

原因:

(1)当query的字段不是TEXT|BLOB切大小小于max_length_for_sort_data时,会使用单路排序,否者使用双路排序.

(2)数据大小可能超出sort_buffer_size,导致创建临时表合并排序,多次IO

2.提高sort_buffer_size的值

3.提高max_length_sort_data的之

查看linux是否安装某个软件

以mysql为例:

1.

[root@yang php74]# cat /etc/passwd|grep mysql
mysql:x:27:27:MySQL Server:/var/lib/mysql:/sbin/nologin

2.[root@yang php74]# cat /etc/group|grep mysql
mysql:x:27:

3.或者

[root@yang php74]# mysqladmin –version
mysqladmin Ver 8.0.17 for Linux on x86_64 (Source distribution)

4.或者查看端口号(前提是服务已开启)

[root@yang php74]# lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 1175 mysql 32u IPv6 34458 0t0 TCP *:mysql (LISTEN)