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,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

#统计整个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,

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 ;

#主表数据和索引数据的最大内存缓冲区，分配过大，会使Swap占用过多，致使Mysql的查询特慢

SELECT @@innodb_buffer_pool_size;  

#windows下可以修改my.ini文件进行设置，默认为8M  my.ini文件可以通过 select @@datadir 找到数据根路径， 然后在data上层目录中会发现my.ini
在[mysqld] 下进行修改，包括常见的端口，慢查询等各类参数，注意不要超过最大值

比如我的电脑上文件配置为：

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=3G

#linux下通过mysql --help|grep 'my.cnf' 查找配置文件，优先使用考前的，一般为/etc/my.cnf

设置的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值调大

#查看设置是否合理
#Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
show status like 'innodb_buffer_pool_read%';

如果Performance百分比很小，则表示够用

参考 https://www.cnblogs.com/wanbin/p/9530833.html

执行

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

开启慢查询

=============开启慢查询========================

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;

#记录sql执行日志

SET GLOBAL general_log=1

SET GLOBAL log_output='FILE';

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

可以通过explain 解析执行对应的慢查询，通过调整表结构、调整索引、查询语句等常规方式优化查询

mysql性能压力测试 mysqlslap

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

--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参数一览

巴特西

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

最新文章

热门文章