mysql

思维导图

根据慢查询日志优化

·开启慢查询功能:

打开my.cnf，加入以下配置
log-slow-queries 慢查询日志文件路径
long_query_time 超过多少秒的查询就写入日志

·慢查询日志参数:
Time执行时间
User@Host执行用户与地址
Query_time执行时间
Lock_time在MySQL服务器阶段等待表锁时间
Rows_sent查询返回行数
Rows_examined查询检查的行数
该语句

·慢日志分析工具：
mysqldumpslow（mysql自带）
mysqlsla
pt-query-digest

  ··如使用mysqlsla分析的返回结果：

    Count： sql的执行次数及占总的slow log数量的百分比.

    Time： 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比，95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

    Lock Time： 等待锁的时间，95% of Lock , 95%的慢sql等待锁时间.

    Rows sent： 结果行统计数量, 包括平均, 最小, 最大数量.

    Rows examined：扫描的行数量.

    Database： 属于哪个数据库

    Users： 哪个用户,IP, 占到所有用户执行的sql百分比

    Query abstract： 抽象后的sql语句

    Query sample： sql语句

存储引擎

·MyISAM

独立于操作系统，可以轻松地从windows系统迁移到linux系统
无法处理事物，适用于：
1、选择密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
2、插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

·InnoDB
为处理巨大数据量的最大性能设计的事务型存储引擎，适用于：
1、更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2、事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3、自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
4、外键约束。MySQL支持外键的存储引擎只有InnoDB。
5、支持自动增加列AUTO_INCREMENT属性。

·MEMORY
快速的响应速度，采用系统内存作为逻辑存储介质。当musqld守护进程崩溃是所有的MEMORY数据都会丢失。要求存储在MEMORY表中的数据使用长度不变的格式，即不能使用BLOB和TEXT这样的数据类型。
适用于：
1、目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。
2、如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。
3、存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

·MERGE

多个MyISAM表的聚合，用于将多个表合起来进行查询后删除表，删除merge表只删除表定义，对内部没有任何影响。

·ARCHIVE

归档，仅支持最基本的插入和查询。有很好的压缩机制，使用zlib压缩库

存储引擎的选择：

如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，选择InnoDB

如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

索引

创建索引示例：

例表：
CREATE TABLE mytable (
　id serial primary key,
　category_id int not null default 0,
　user_id int not null default 0,
　adddate int not null default 0
);
为category_id建立一个简单的索引：
CREATE INDEX mysql_categoryid ON mytable(category_id);
建立多重索引：
CREATE INDEX mysql_categoryid_userid ON mytable(category_id,user_id);

-------------------------------------------------------------------------------------------------------

一、选择索引的数据类型

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。
(3)尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替空值。

二、索引类型
·Btree索引
    (1)匹配全值(Match the full value)
    (2)匹配最左前缀(Match a leftmost prefix)
    (3)匹配列前缀(Match a column prefix)
    (5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part)
    (6)仅对索引进行查询(Index-only queries)
  ··使用B-tree索引有以下一些限制：
    (1) 查询必须从索引的最左边的列开始。
    (2) 不能跳过某一索引列。
    (3) 存储引擎不能使用索引中范围条件右边的列。
·hash索引
    仅memory存储引擎支持hash索引，采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
  ··使用hash索引有以下一些限制：
    (1)由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性能造成太大的影响。
    (2)不能使用hash索引排序。
    (3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。
    (4)Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。
·空间(R-Tree)索引
    MyISAM支持空间索引，主要用于地理空间数据类型，例如GEOMETRY。
·全文(Full-text)索引
    全文索引是MyISAM的一个特殊索引类型，主要用于全文检索。
三、索引策略
·聚簇索引
    聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。聚簇索引要比非聚簇索引查询效率高很多。
    聚集索引这种主+辅索引的好处是，当发生数据行移动或者页分裂时，辅助索引树不需要更新，因为辅助索引树存储的是主索引的主键关键字，而不是数据具体的物理地址。
·非聚簇索引
    非聚簇索引，类似于图书的附录，哪个专业术语出现在哪个章节，这些专业术语是有顺序的，但是出现的位置是没有顺序的。每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。

 

下表为何时应该使用聚簇索引与非聚簇索引:

动作	使用聚簇索引	使用非聚簇索引
列经常被分组排序	√	√
返回某范围内的数据	√	×
一个或极少不同值	×	×
小数目的不同值	√	×
大数目的不同值	×	√
频繁更新的列	×	√
外键列	√	√
主键列	√	√
频繁修改索引列	×	√

配置文件

[client]
port = 3306
socket = /tmp/mysql3306.sock

[mysql]
prompt="\u@\h:\p \R:\m:\s [\d]>"
#tee=/data/mysql/mysql3306/data/query.log
no-auto-rehash

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql                                             #使用给定目录作为根目录(安装目录)。
datadir = /data/mysql/mysql3306/data                         #从给定目录读取数据库文件。
port = 3306
socket = /tmp/mysql3306.sock                                     #为MySQL客户程序与服务器之间的本地通信指定一个套接字文件
#event_scheduler = 0 --

#tmp
tmpdir=/data/mysql/mysql3306/tmp

#timeout                                                                      #MySQL服务器支持最大连接数有上限，该配置项会断开闲置进程
interactive_timeout = 3600                                         #服务器关闭交互式连接前等待活动的秒数
wait_timeout = 3600                                                   #服务器关闭非交互连接之前等待活动的秒数

#character set
character-set-server = utf8

open_files_limit = 65535                                            # MySQL打开的文件描述符限制，默认最小1024;当open_files_limit没有被配置的时候，比较max_connections*5和ulimit -n的值，哪个大用哪个， 当open_file_limit被配置的时候，比较open_files_limit和max_connections*5的值，哪个大用哪个。
max_connections = 200                                             # MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySQL会为每个连接提供连接缓冲区，就会开销越多的内存，

                                                                                  #所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。
max_connect_errors = 100000                                 # 对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。

skip-name-resolve = 1                                               # 禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求

#logs
log-output=file
slow_query_log = 1 #开启慢查询
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 5                                                 #慢查询时间，超过5秒为慢查询
#log-slow-admin-statements = 1                               #记录那些慢的optimize table，analyze table和alter table语句
#log-queries-not-using-indexes = 1                          #记录所有没有使用到索引的查询语句
log-slow-slave-statements = 1                                  #记录由Slave所产生的慢查询

#binlog
binlog_format = row
server-id = 363306
log-bin = /data/mysql/mysql3306/logs/mysql-bin
binlog_cache_size = 4M                                            #在事务过程中容纳二进制日志SQL语句的缓存大小。二进制日志缓存是服务器支持事务存储引擎并且服务器启用了二进制日志(—log-bin选项)的前提下为每个客户端分配的内存，
                                                                                  #注意，是每个Client都可以分配设置大小的binlogcache空间。如果系统中经常会出现多语句事务的话，可以尝试增加该值的大小，以获得更好的性能。

                                                                                  #当然，我们可以通过MySQL的以下两个状态变量来判断当前的binlog_cache_size的状况：Binlog_cache_use和Binlog_cache_disk_use。
max_binlog_size = 1G                                               #Binlog日志最大值，一般来说设置为512M或者1G，但不能超过1G。
max_binlog_cache_size = 2G                                    #binlog能够使用的最大cache内存大小。
sync_binlog = 0                                                         #影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

                                                                                  #当sync_binlog=0时，事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

                                                                                  #当sync_binlog=n，每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
expire_logs_days = 10

#relay log
skip_slave_start = 1                                                  #复制进程不会随着数据库的启动而启动
max_relay_log_size = 1G                                          #标记relay log 允许的最大值，如果该值为0，则默认值为max_binlog_size(1G)；如果不为0，则max_relay_log_size则为最大的relay_log文件大小
relay_log_purge = 1                                                  #是否自动清空不再需要中继日志时。默认值为1(启用)。
relay_log_recovery = 1                                              #当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。

                                                                                  #默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能
log_slave_updates                                                    #从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作
#slave-skip-errors=1032,1053,1062                          #在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。

                                                                                  #Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

                                                                                  #--slave-skip-errors=[err1,err2,…….|ALL]

                                                                                  #但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本

explicit_defaults_for_timestamp=1                            #如果没有这个配置项，create table时指定timestamp类型的列，会生产默认情况。

#buffers & cache
table_open_cache = 2048                                        #指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M                                   #这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256                                         #Thread_Cache 中存放的最大连接线程数.

query_cache_size = 0                                             #查询缓冲大小，MySQL将SELECT语句和查询结果存放在缓冲区中，今后对于同样的SELECT语句（区分大小写），将直接从缓冲区中读取结果。
query_cache_type = 0                                             #指定是否使用查询缓冲

query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M                                              #指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。该比例应该尽可能的低
read_buffer_size = 2M 
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 512M                             #这个参数主要缓存innodb表的索引，数据，插入数据时的缓冲。
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 64M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

编写评论...