专栏首页后台及大数据开发mysql常用配置注意项与sql优化

mysql常用配置注意项与sql优化

建立数据库:

    • 建立数据库时编码字符集采用utf8
    • 排序规则:
      • 后缀"_cs"或者"_ci"意思是区分大小写和不区分大小写(Case Sensitive & Case Insensitve)
      • 后缀"_bin" 规定每个字符串用二进制编码存储,区分大小写,可以直接存储二进制的内容
      • utf-8有默认的排序规则: 命令:SHOW CHARSET LIKE 'utf8%';
      • 注意点:什么时候需要区分大小写需要在设计和使用时注意
      • 如果排序规则使用的是不区分大小写,但部分表字段需要区分大小写,则可以对该字段进行修改:
ALTER TABLE yourTableName MODIFY COLUMN columnName VARCHAR(255) BINARY CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;

建表:

  常见表字段选择请参考其它

  表建立完成后,可以插入一定量的,和业务真实基本一致的数据后,通过执行

SELECT * FROM yourTableName PROCEDURE ANALYSE(); 

  根据建议修改表字段定义

注意:此处建议知识针对表中数据,请合理取舍

测试环境 设置:

  模拟真实场景数据,放大 xxx 倍,作为上线一段时间后的业务数据预期值

  • 统计数据量以配置缓冲区大小
    • #查询全部库或者指定库数据量大小 select table_schema as DB_NAME, concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as DB_DATA_SIZE, concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as DB_INDEX_SIZE, concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as DB_TOTAL_SIZE from information_schema.TABLES where table_schema='bdjc_ls' #指定具体的库名 AND table_name='t_syxm'; #指定具体表名 group by table_schema order by sum(DATA_LENGTH + INDEX_LENGTH) desc ;
    #统计整个mysql数据量大小  
    select
    concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as ALL_DB_DATA_SIZE,
    concat(round(sum(INDEX_LENGTH/1024/1024),2),'MB') as ALL_DB_INDEX_SIZE,
    concat(round(sum((DATA_LENGTH + INDEX_LENGTH)/1024/1024),2),'MB') as ALL_DB_TOTAL_SIZE
    from information_schema.TABLES;
#统计指定库表的行数和数据量大小
select TABLE_SCHEMA as DB_NAME,TABLE_NAME,TABLE_ROWS,DATA_LENGTH
from information_schema.TABLES
where table_schema='bdjc_ls'          #指定具体的库名
-- AND table_name='t_syxm';            #指定具体表名
ORDER BY DATA_LENGTH DESC
  • 设置的innodb_buffer_pool_size 需要为 innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances 的倍数, 如果不是倍数,设置的innodb_buffer_pool_size会自动调整为倍数 innodb_buffer_pool_chunk_size是一个只读值, 当innodb_buffer_pool_size大于1G时,就应该将innodb_buffer_pool_instances值调大 执行 show engine innodb status 查看 Free buffers 大小,如果长时间很大,则可以调小innodb_buffer_pool_size,否则可以适当调大innodb_buffer_pool_size 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100 如果命中率低于99%,则可以考虑增加innodb_buffer_pool_size
#主表数据和索引数据的最大内存缓冲区,分配过大,会使Swap占用过多,致使Mysql的查询特慢
SELECT @@innodb_buffer_pool_size;  
  • 开启慢查询
    • mysqldumpslow 慢日志分析工具 命令: -s 按照那种方式排序 c:访问计数 l:锁定时间 r:返回记录 al:平均锁定时间 ar:平均访问记录数 at:平均查询时间 -t 是top n的意思,返回多少条数据。 -g 可以跟上正则匹配模式,大小写不敏感。 #得到返回记录最多的20个sql mysqldumpslow -s r -t 20 /data/log/mysql/slow_query.log #得到平均访问次数最多的20条sql mysqldumpslow -s ar -t 20 /data/log/mysql/slow_query.log #得到平均访问次数最多,并且里面含有ttt字符的20条sql mysqldumpslow -s ar -t 20 -g "ttt" /data/log/mysql/slow_query.log 如果出现如下错误,Died at /usr/bin/mysqldumpslow line 161, <> chunk 405659.说明要分析的sql日志太大了,请拆分后再分析 拆分的命令为: tail -10000 /data/log/mysql/slow_query.log>/data/log/mysql/slow_query_1_10000.log
    #记录sql执行日志
    SET GLOBAL general_log=1
    SET GLOBAL log_output='FILE';
    • 可以通过explain 解析执行对应的慢查询,通过调整表结构、调整索引、查询语句等常规方式优化查询
=============开启慢查询========================
vi /etc/my.cnf
[mysqld]
…
slow_query_log = 1                                         #无效(0或者OFF)、有效(1或者ON)
slow_query_log_file = /data/log/mysql/slow_query.log      #指定日志文件
long_query_time = 0.5                                    #超过指定时间的SQL会记录到日志文件(默认时间为10秒,默认单位为秒)

#或者全局设置
-----------------
set global slow_query_log = 1;
set global slow_query_log_file = '/data/log/mysql/slow_query.log';
set global long_query_time = 0.5;
  • mysql性能压力测试 mysqlslap
    --print-defaults        Print the program argument list and exit.
    --no-defaults           Don't read default options from any option file,
                            except for login file.
    --defaults-file=#       Only read default options from the given file #.
    --defaults-extra-file=# Read this file after the global files are read.
    --defaults-group-suffix=#
                            Also read groups with concat(group, suffix)
    --login-path=#          Read this path from the login file.
      -?, --help            Display this help and exit.
      -a, --auto-generate-sql
                          Generate SQL where not supplied by file or command line.
      --auto-generate-sql-add-autoincrement
                          Add an AUTO_INCREMENT column to auto-generated tables.
      --auto-generate-sql-execute-number=#
                          Set this number to generate a set number of queries to run.
      --auto-generate-sql-guid-primary
                          Add GUID based primary keys to auto-generated tables.
      --auto-generate-sql-load-type=name  
                          Specify test load type: mixed, update, write, key, or read; default is mixed.
      --auto-generate-sql-secondary-indexes=#
                          Number of secondary indexes to add to auto-generated
                          tables.
      --auto-generate-sql-unique-query-number=#
                          Number of unique queries to generate for automatic tests.
      --auto-generate-sql-unique-write-number=#
                          Number of unique queries to generate for auto-generate-sql-write-number.
      --auto-generate-sql-write-number=#
                          Number of row inserts to perform for each thread (default is 100).
      --commit=#          Commit records every X number of statements.
      -C, --compress      Use compression in server/client protocol.
      -c, --concurrency=name
                          Number of clients to simulate for query to run.
      --create=name       File or string to use create tables.
      --create-schema=name
                          Schema to run tests in.
      --csv[=name]        Generate CSV output to named file or to stdout if no file is named.
      -#, --debug[=#]     This is a non-debug version. Catch this and exit.
      --debug-check       This is a non-debug version. Catch this and exit.
      -T, --debug-info    This is a non-debug version. Catch this and exit.
      --default-auth=name Default authentication client-side plugin to use.
      -F, --delimiter=name
                          Delimiter to use in SQL statements supplied in file or command line.
      --detach=#          Detach (close and reopen) connections after X number of requests.
      --enable-cleartext-plugin
                          Enable/disable the clear text authentication plugin.
      -e, --engine=name   Storage engine to use for creating the table.
      -h, --host=name     Connect to host.
      -i, --iterations=#  Number of times to run the tests.
      --no-drop           Do not drop the schema after the test.
      -x, --number-char-cols=name
                          Number of VARCHAR columns to create in table if specifying --auto-generate-sql.
      -y, --number-int-cols=name
                          Number of INT columns to create in table if specifying --auto-generate-sql.
      --number-of-queries=#
                          Limit each client to this number of queries (this is not exact).
      --only-print        Do not connect to the databases, but instead print out what would have been done.
      -p, --password[=name]
                          Password to use when connecting to server. If password is not given it's asked from the tty.
      -W, --pipe          Use named pipes to connect to server.
      --plugin-dir=name   Directory for client-side plugins.
      -P, --port=#        Port number to use for connection.
      --post-query=name   Query to run or file containing query to execute after
                          tests have completed.
      --post-system=name  system() string to execute after tests have completed.
      --pre-query=name    Query to run or file containing query to execute before
                          running tests.
      --pre-system=name   system() string to execute before running tests.
      --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                          memory).
      -q, --query=name    Query to run or file containing query to run.
      --secure-auth       Refuse client connecting to server if it uses old
                          (pre-4.1.1) protocol. Deprecated. Always TRUE
      --shared-memory-base-name=name
                          Base name of shared memory.
      -s, --silent        Run program in silent mode - no output.
      -S, --socket=name   The socket file to use for connection.
      --sql-mode=name     Specify sql-mode to run mysqlslap tool.
      --ssl-mode=name     SSL connection mode.
      --ssl               Deprecated. Use --ssl-mode instead.
                          (Defaults to on; use --skip-ssl to disable.)
      --ssl-verify-server-cert
                          Deprecated. Use --ssl-mode=VERIFY_IDENTITY instead.
      --ssl-ca=name       CA file in PEM format.
      --ssl-capath=name   CA directory.
      --ssl-cert=name     X509 cert in PEM format.
      --ssl-cipher=name   SSL cipher to use.
      --ssl-key=name      X509 key in PEM format.
      --ssl-crl=name      Certificate revocation list.
      --ssl-crlpath=name  Certificate revocation list path.
      --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1
      -u, --user=name     User for login if not current user.
      -v, --verbose       More verbose output; you can use this multiple times to
                          get even more verbose output.
      -V, --version       Output version information and exit.
mysqlslap -h192.168.0.200 -P3306 -uroot -p123456 --number-char-cols=5 --number-int-cols=3 --concurrency=1 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb,myisam --number-of-queries=1 --auto-generate-sql-write-number=1 --only-print 

mysqlslap -h192.168.0.200 -P3306 -uroot -p123456 --concurrency=100,500,1000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000

mysqlslap -h192.168.0.200 -P3306 -uroot -p123456 --concurrency=2 --iterations=2 --create-schema=mysql --query="select * from user" --engine=innodb --number-of-queries=20

mysqlslap -h192.168.0.200 -P3306 -uroot -p123456 --concurrency=100 --iterations=1 --create-schema=mysql --query=/root/mysql/query.sql --engine=innodb --number-of-queries=5000

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • kubernetes集群搭建(8):常用命令

    kubectl create -f xxxx.yaml            #创建rc、deploy、svc等        

    肖哥哥
  • CentOS下 elasticsearch集群安装

    4.修改/root/elasticsearch-node3/config/elasticsearch.yml 为如下内容(注意红色部分为三个节点不一致的地方)

    肖哥哥
  • CentOS下 elasticsearch集群安装

    4.修改/root/elasticsearch-node3/config/elasticsearch.yml 为如下内容(注意红色部分为三个节点不一致的地方)

    肖哥哥
  • django开发中关于外键设置

    我们要把源码中的db_constraint设置成Flase这样就有关联关系,但是不会受他约数

    小小咸鱼YwY
  • IoTivitiy Architecture

    IoTivity is an open source software framework enabling seamless device-to-device...

    首席架构师智库
  • shFlags简介

    看到有脚本中使用了shFlags,于是google了一下,发现还是个挺方便的东西。

    zqb_all
  • [小程序]微信小程序登陆并获取用户信息

    1.小程序js端调用框架登陆API,获取到一个临时code,拿着这个code去调用自己的服务端接口

    陶士涵
  • Picasso 图片加载库

    Picasso 英文意思国外一个很有名的画家毕加索的名字,国外项目取名还是很有意思的! 从github新下载的picasso项目有依赖其他第三方开源项目okht...

    xiangzhihong
  • [Go] golang互斥锁mutex

    1.互斥锁用于在代码上创建一个临界区,保证同一时间只有一个goroutine可以执行这个临界区代码 2.Lock()和Unlock()定义临界区

    陶士涵
  • 数据库连接池DBUtils使用

      DBUtils简单说python实现的线程化数据库连接(连接池),DBUtils支持所有遵循DP-API 2规范的数据库连接模块,例如:mysql、sqls...

    用户2398817

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动