mysql存储引擎毫无疑问选择Innodb

2018年9月15日 12:25

网上观点

网上部分人在考虑如何选择MyISAM与InnoDB的依据是
* innodb 支持事务、行级锁. 适合写多读少
* myisam 表锁,适合读多写少
 

实际测试下查询速度

1千万(InnoDB)
select *        21秒
select id=xxx   0.026秒
count(id)       15.71秒(第一次16.71,第二次0秒,插入一条后16.476秒)
count(username) 16.956秒
avg(id)         18.41秒

1千万(Myisam)
select *        8.389秒
select id=xxx   0秒
count(*)        0秒
count(username) 1.643秒
avg(id)         3.793秒

myisam-->innodb 52.200秒
确实MyISAM比InnoDB快了3倍
 
可问题是实际中,都是通过索引分批取数据,有谁有一次取那么多的数据呢?而分批取数据性能都差不多。
 

存储引擎特性对比

下面是官方网站上mysql8.0的特性。可以看出MyISAM特性,InooDB都有,没有必须选择MyISAM的理由
| Feature                      | MyISAM       | InnoDB       |
| B-tree indexes               | Yes          | Yes          |
| Backup/point-in-time 恢复    | Yes          | Yes          |
| Cluster database support     | No           | No           |
| Clustered indexes            | No           | Yes          |
| Compressed data              | Yes (note 2) | Yes          |
| Data caches                  | No           | Yes          |
| Encrypted data (note 3)      | Yes          | Yes          |
| Foreign key support          | No           | Yes          |
| Full-text search indexes     | Yes          | Yes (note 5) |
| Geospatial data type support | Yes          | Yes          |
| Geospatial indexing support  | Yes          | Yes (note 6) |
| Hash indexes                 | No           | No (note 7)  |
| Index caches                 | Yes          | Yes          |
| Locking granularity          | Table        | Row          |
| MVCC                         | No           | Yes          |
| Replication support (note 1) | Yes          | Yes          |
| Storage limits               | 256TB        | 64TB         |
| T-tree indexes               | No           | No           |
| Transactions                 | No           | Yes          |
| Update statistics for data dictionary YesYes
 

官方网站怎么说MyISAM?

#原文
MyISAM: These tables have a small footprint. Table-level locking limits the performance in read/write workloads, 
so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.
#意思
MyISAM应用范围比较小。它是表级锁,对并发读写有限制。适合只读或者大部分是读的web应用或者配置性的数据仓库。
 

数据损坏

InnoDB会自动恢复,MyISAM需要定时检查
 

我的结论

* 从查询速度上,实际中是分批取数查询数据差不多
* 从特性上,InooDB是MyISAM的超集
* 从功能上,InooDB功能更强
 
所以网上说读多写少选择MyISAM是人云亦云。
当然存在肯定有理由,为了兼容性,还是什么?
 
此生必看的科学实验-水知道答案
精神病为什么治不好
百病之源

 

Tags: myisam
评论(0) 阅读(459)

思考大表统计的优化策略

2018年9月15日 10:02

前言

以InnoDB表的count()统计为例子
 

方法一、直接count,全表扫描

 
* 分析
#一千万条数据count()耗费几十秒,基本不用干活
select count(*) from user
 

方法二、分段count,全表扫描转变为范围扫描

加上where条件,将全部查询,拆分成多个小范围查询。
#全表扫描
select count(*) from user;

#范围扫描
select count(*) from user where id < 1000000;
select count(*) from user where id>=1000000 and id < 200000;
 
实际中需要打开多个数据库连接,实际操作不是很好。
 
当然分表也属于这类方法
 
* 原理
大问题拆成多个小问题,将压力分散。
 
* 分析
如果是同一张表,需要打开多个连接(多个进程)
如果分库了,需要连接多个数据库
虽然能减少时间,但是不能减小压力,count()次数多了,系统也得嗝屁
 

方法三、增加tinyint类型字段并建立二级索引用于统计

 
二级索引key是索引列,value是主键,一个数据页上可以存储更多数据,减少了分页于页内移动
 
#加了之后再统计,mysql解析器会自动选择二级索引进行count
select count(*) from user
 
* 原理
利用二级索引减少io
 
* 分析
能够减少时间,也能减小io压力,但是时间还是挺长的,我的机器上一千万数据从5秒编成了1秒,1秒还是比较长
假如加上where条件呢,这个方法就用不上了
 

方法四、维护一张统计表

凡事预则立,不预则废,设计时加一张tablename_stats表(两个字段key,value),平时就维护好统计数据,增加时加1,删除时减1。
 
key value 
count                   1111
hash值(表名+where条件) 1111
 
* 原理
提前准备
 
* 分析
毫无压力,效果完美
需要提前预测好可能的统计以及条件
缺点存在统计数据与实际不一致,在大数据量情况下,也不算什么问题
 

总结

最终发现还是类似myisam维护一个计数器是最好的方法。
只是我们扩展了功能,加了where条件也提前计数
 
 
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)
此生必看的科学实验-水知道答案

 

Tags: 大数据
评论(0) 阅读(370)

myisam锁表机制

2018年9月15日 06:34

 锁表机制

* 锁机制
a.加锁读(共享锁):不会阻塞读请求(select, insert),阻塞写(update, delete)请求
b.加锁写(独占锁):阻塞其它读写(select, delete, insert, update)请求
 
* 锁耗时多长?
取决于sql耗时。控制好sql耗时,锁表的问题并不大。
 

测试数据

生成1000万条测试数
表user(id, username, password, age, sex)
存储引擎MyISAM,id建立索引
 

加锁读,select,insert不阻塞,update,delete阻塞

打开两个客户端
客户端1全表扫描
select * from user
 
客户端2测试阻塞
select * from user limit 10;    #结果不阻塞

insert into user(username, password, age, sex)
values("aaaaa", "bbbb", 10, 1); #结果不阻塞

update user set username="aaa" where id =90001;  #结果阻塞

delete from user where id =10000006  #结果阻塞
 

加锁写,select,insert,update,delete都阻塞

 
客户端1全表扫描写
update set username="aaaaaaa"
where username="不存在的值让sql扫描全表"
 
客户端2测试阻塞
重复上面select, insert, update, delete结果都阻塞
 
此生必看的科学实验-水知道答案
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)

 

评论(0) 阅读(381)

flask自动重连数据库

2018年9月12日 07:43

情况描述

flask部署到服务器后,日志中经常出现错误
sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2006, "MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))")
 
看下mysql日志,大量的超时导致的连接中断
...
2018-09-10  6:45:41 768 [Warning] Aborted connection 768 to db: 'xxx' user: 'xxx' host: '172.22.0.2' (Got timeout reading communication packets)
 

问题原因

原因就是因为连接超时,数据库把它中断了,详细参见官方文档.
https://dev.mysql.com/doc/refman/8.0/en/communication-errors.html
 

起因

  • 查看数据库配置。不要混淆全局配置与会话配置
show global variables like '%timeout%'

connect_timeout      5
interactive_timeout  28800
wait_timeout      600
 
  • 配置文件my.cnf
connect_timeout         = 5
wait_timeout            = 600
 

错误复现

mysql错误日志两次中断间隔时间都大于600秒,有可能是该参数引起,将它设置小点看看,能否复现问题
set global wait_timeout=10
 
设置后,问题又出现了,可以缺点就是该参数引起的。
 

自动重连实现

无论wait_timeou设置多大,总会出现超时,该怎么避免呢?简单方法是定时重连接.
 
flask已经提供了重连参数。连接超过一定时间就将它回收。
SQLALCHEMY_POOL_RECYCLE=7200   # 默认2小时。该值一定要比数据库wait_timeout小,否则它不起作用,上面就是这个原因导致的
 
当然这个方法不会帮我们处理网络异常导致的连接失效时重连
 
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)
此生必看的科学实验-水知道答案

 

Tags: flask
评论(0) 阅读(905)

sysbench测试mysql性能瓶颈

2018年9月06日 05:56

前言

任何系统,业务越来越繁忙,最终都会遇到性能瓶颈。能够提前知道系统大概的瓶颈,是很重要的。下面介绍一个测试工具sysbench
 

安装

yum install sysbench
#下面测试中版本是1.0.9
 

github

https://github.com/akopytov/sysbench
 

测试mysql

sysbench --test=/usr/share/sysbench/oltp_insert.lua
--mysql-host=127.0.0.1
--mysql-port=3306
--mysql-user=root
--mysql-password=111111
--mysql-db=mytest
--db-driver=mysql  prepare
 
  • * 参数选项
--test 指定测试所用lua脚本
--mysql-host
--mysql-port
--mysql-user
--mysql-password
--mysql-db
--db-driver=mysql  #指定db类型
prepare 测试前准备工作
run 正式测试
cleanup 测试后删掉测试数据
 
  • * lua脚本的参数选项
详细参数看lua脚本/usr/share/sysbench/oltp_common.lua
下面是常用参数
--mysql_storage_engine=innodb   #表引擎
--table_size=10000              #表大小
--tables=10                     #表数量
 
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)
此生必看的科学实验-水知道答案
 

Tags: sysbench
评论(0) 阅读(528)

快速生成千万条mysql数据

2018年9月06日 05:33

目的

学习、测试mysql大数据场景,需要先生成大量数据。
 

思路

mysql官方文档说得很清楚。"load data infile"导入数据比insert要快20倍。所以我们先生成一千万条数据的文件。
然后将数据导入表中。
 
## 生成数据
假如有个用户表(id, username,password, age, sex),id是自动增长,我们现在需要生成username等信息
生成一千万条数据,速度还能接受,耗时236秒,文件大小315M。
 
import string
import random

def random_str(length=1):
    template = string.letters + string.digits
    chars = random.sample(template, length)
    return "".join(chars)

def generate_record():
    """
    username/password/age/sex
    """
    length = random.randint(6, 20)
    username = random_str(length)

    length = random.randint(6, 20)
    password = random_str(length)

    age = random.randint(10, 100)
    sex = random.choice([0, 1])
    return [username, password, age, sex]

def create_file(num=10000000):
    with open("user_data.txt", "w") as f:
        for i in range(num):
            row = generate_record()
            f.write(",".join(map(str, row))+"\n")

if __name__ == '__main__':
    import datetime
    start = datetime.datetime.now()
    create_file()
    end = datetime.datetime.now()
    cost = (end -start).total_seconds()
    print("cost: %s" % cost)
#一千万条,耗时236s,文件315M
 

导入

load data infile命令有安全限制,最好是把数据拿到mysql server端,再通过mysql -uxxx -pxxx进入命令,再导入。
我的虚拟机导入耗时57秒
 
load data infile "/user_data.txt" into table user
fields terminated by ','
lines terminated by '\n'
(username, password, age, sex);
 
 

其它方式

  • 测试工具sysbench
sysbench是批量insert,性能比不上导入。但是它更接近实际场景
 
  • 存储过程
速度很快,但是不如用熟悉的脚本方便
 
此生必看的科学实验-水知道答案
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)
 

Tags: python
评论(0) 阅读(486)

执著与淡然

2018年9月06日 05:03

执著的人靠谱,但是容易起冲突。
淡然的人,不会起冲突,可能不靠谱。

评论(0) 阅读(425)

彻底关闭win10自动更新

2018年7月15日 19:20

win10更新非常讨厌,一不注意,重启后自动更新,等半个小时都不能进系统。
 
发现大部分的方法是禁用服务。然后每次重启系统后,又发现它被改回来了,也不知道是被什么东西改的。
 

组织策略,禁止更新

运行 gpedit.msc
 

服务管理中禁止windows updates

 
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)

 

评论(0) 阅读(895)

pelican AttributeError: 'unicode' object has no attribute 'slug'

2018年7月08日 16:06

pelican生成html报错,原因是网上的教程有错误。pelican默认支持rst格式,创建md格式文章,根本没有编译,所以网上教程不会报错
 
如果支持markdown需要安装”pip install markdown”。安装之后编译出现如下错误。
 
(pelican) ➜  xuefo make html
pelican /home/wyq/me/workspace/xuefo/content -o /home/wyq/me/workspace/xuefo/output -s /home/wyq/me/workspace/xuefo/pelicanconf.py 
CRITICAL: AttributeError: 'unicode' object has no attribute 'slug'
make: *** [Makefile:65:html] 错误 1
 
错误方式
Title: 文章标题
Date: 2018-07-07
Category: 文章类别
Tag: 标签1, 标签2       #此处错误
 
正确方式
Tags: 标签1,标签2
 
《了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)

 

评论(0) 阅读(782)

基于gradle构建java应用程序

2018年6月30日 23:33

 
基于gradle构建java应用程序示例
 

目录

你需要什么
用户手册
初始项目
项目结构
编译项目
运行项目
总结
源码
 

你需要什么

  • 大约8分钟
  • 一个文本编辑器
  • 命令行终端
  • jdk1.7或更高版本
  • 最近版本的gradle

用户手册

gradle带有内置的init插件。它的文档地址
这个插件有一个task,名叫init,可以生成项目. init task使用内置的wrapper task生成wrapper脚本gradlew
 
运行init task
gradle init --type <name>
参数name可选值
  • java-application
  • java-library
  • scala-library
  • groovy-library
  • basic
这里我们选择java-application类型
 
首先创建项目目录
$ mkdir java-demo
$ cd java-demo
 

初始化项目

在项目目录中运行init task,并使用参数java-application
$ gradle init --type java-application
Starting a Gradle Daemon (subsequent builds will be faster)
> Task :wrapper
> Task :init

BUILD SUCCESSFUL
 
init task先运行wrapper task生成gradlew、gradlew.bat脚本,并创建下面的项目结构
├── build.gradle
├── gradle            #wrapper的文件
│   └── wrapper
│       ├── gradle-wrapper.jar
│       └── gradle-wrapper.properties
├── gradlew
├── gradlew.bat
├── settings.gradle
└── src
    ├── main
    │   └── java        #源文件
    │       └── App.java
    └── test            #测试
        └── java
            └── AppTest.java
 
 

项目结构

settings.gradle有很多注释,有效行只有一行
settings.gradle
rootProject.name='java-demo'
配置root project的名称java-demo
 
生成的build.gradle同样有很多注释,下面是有效部分
build.gradle
apply plugin: 'java'
apply plugin: 'application'

repositories {
    jcenter()      #公有仓库
}

dependencies {
    compile 'com.google.guava:guava:21.0'   #google的guava包
    testCompile 'junit:junit:4.12'          #junit测试包
}

mainClassName = 'App'    #main类
build文件中加了java和application两个插件。前者支持java项目,后者允许指定包含main方法的类,在编译时执行
 
src/main/App.java
public class App {
    public String getGreeting() {
        return "Hello world.";
    }

    public static void main(String[] args) {   #在运行application插件的run task时调用
        System.out.println(new App().getGreeting());
    }
}
 
src/test/java/AppTest.java
import org.junit.Test;
import static org.junit.Assert.*;

public class AppTest {
    @Test public void testAppHasAGreeting() {
        App classUnderTest = new App();
        assertNotNull("app should have a greeting",
            classUnderTest.getGreeting());
    }
}
 
编译项目
编译项目使用build命令。可以使用常用的gradle命令,但是使用wrapper脚本更好
$ ./gradlew build
> Task :compileJava
> Task :processResources NO-SOURCE
> Task :classes
> Task :jar
> Task :startScripts
> Task :distTar
> Task :distZip
> Task :assemble
> Task :compileTestJava
> Task :processTestResources NO-SOURCE
> Task :testClasses

> Task :test

> Task :check
> Task :build

BUILD SUCCESSFUL
注意:第一次运行时会比较慢
 
第一次运行build时,会检查.gradle目录是否存在guava、junit包。如果不存在,则下载并存到本地目录
下次运行build就使用缓存。build task编译class文件,运行test用例,生成测试报告
 
查看html报告build/reports/tests/test/index.html.
样例
 

运行项目

gradle构建使用了application插件,所以能在命令行运行程序。
看看有哪些task
$ ./gradlew tasks
:tasks

------------------------------------------------------------
All tasks runnable from root project
------------------------------------------------------------

Application tasks
-----------------
run - Runs this project as a JVM application

// ... many other tasks ...
 
run task会让gradle读取mainClassName属性指定的类,执行main方法
$ ./gradlew run
:compileJava UP-TO-DATE
:processResources NO-SOURCE
:classes UP-TO-DATE
:run
Hello world.


BUILD SUCCESSFUL
 

总结

通过上面介绍可以学到
  • 如何生成java应用程序
  • 如何生成build文件、java文件
  • 如何运行构建、查看测试报告
  • 如何使用application插件的run task执行程序

源码

github地址
 
参考资料
 
了凡四训》详解之改过之法
印光大师十念法(胡小林主讲第1集)

 

Tags: gradle
评论(0) 阅读(829)