mysql优化初探

MySQL优化初探

这里用的是Mysql5.6.34win64版本

设计数据库

3F范式

分库分表

垂直分割

水平分割取模算法

修改配置

---

下面是我的配置文件

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

character_set_server = utf8

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=ON
#错误日志
#log_error=D:\Program Files\mysql-5.6.34-winx64\data\logs\err.log
#全查询日志 
#log_output=FILE  

#不启用的话慢日志查询会存在数据表中
general_log=on
general_log_file=query.log

#慢查询日志
slow_query_log=on   
long_query_time=2
#时间为2s
slow_query_log_file=slowquery.log


# These are commonly set, remove the # and set as required.
 basedir = D:\Program Files\mysql-5.6.34-winx64
# 日志存放目录
 datadir = D:\Program Files\mysql-5.6.34-winx64\data
 port = 3306
# server_id = .....

#二进制日志配置，第二、三行配置最后一个binlog-bin和binlog是不加后缀的文件名，不加后缀的话有妙用
server_id=1
#log_bin=D:\Program Files\mysql-5.6.34-winx64\data\bin_log\binlog-bin
#log_bin_index=D:\Program Files\mysql-5.6.34-winx64\data\bin_log\binlog

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

索引

当数据有重复的时候，如何降低幂等

唯一索引

定位慢查询

日志配置在上面，将慢查询时间设置好(默认是10秒)，重启mysql服务，之后如果超过设定时间，就会记录在log日志中。