1.记录mysql运行时的sql语句
配置my.ini文件 在安装目录,linux下文件名为my.cnf
查找到[mysqld]区段,增加日志的配置,如下示例:
[mysqld]
log="C:/temp/mysql.log"
log_slow_queries="C:/temp/mysql_slow.log"
long_query_time=1
log指示日志文件存放目录;
log_slow_queries指示记录执行时间长的sql日志目录;
long_query_time指示多长时间算是执行时间长,单位s。
设置完成后重启服务。
2.查看mysql版本
mysql --version
或者登陆mysql 后status命令或者使用 select version();
关于数据库
数据库是文件的集合;是文件的集合。
数据库实例是应用程序,位于用户与操作系统之间的一层数据管理软件。
Mysql 由以下几块组成
连接池组件
管理服务和工具组件
SQL接口组件
查询分析器组件
优化器组件
缓冲组件
插件式存储引擎
存储引擎是基于表的而非库。引擎是其核心。
InnoDB支持事务,面向OLTP,行锁设计,支持外键,支持非锁定读。ibd文件中。提供插入缓冲,二次写,自适应哈希索引,预读等高可用等。InnoDB会为每行生成一个RowID.
MyISAM,官方提供的引擎,不支持事务,表锁和全文索引,OLAP操作快,非WIN系统的默认引擎。由MYD和MYI组成,MYD放数据,MYI放索引。可使用采用Huffman编码压缩的myisampack来压缩与解压文件,5后支持256T的单表数据,5前为4G。
NDB,类似Oracle RAC的集群存储引擎,数据全部放在内存,5.1后可将非索引数据放在磁盘上,所以PK查找非常快。但是复杂的连接会很慢。
Memory存储引擎,在内存中,适合临时存储数据,默认使用哈希索引。并发小,不支持TEXT,BLOB字段。Mysql使用该引擎存放中间结果段,如果中间结果集,将转换到myisam引擎表而存放到磁盘。
Archive存储引擎,只支持Insert,select,使用zlib算法可达1:10的压缩比,适合存储归档数据,如日志系统。提高高速的插入和压缩功能。
Federated存储引擎,不存放数据,指向远程Mysql的表,类似于SqlServer的链接服务器和Oracle的透明网关。
Maria存储引擎,目标是取代MyIsam。缓存数据和索引文件,行锁设——支持事务与非事务
物理文件
关于引擎更详细内容:http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html
Mysql连接
一般的进程通信方式有:管道,命名管道,命名字,TCP/IP套接字,Unix域名套接字
1.TCP/IP式
mysql -h127.0.0.1 --port=3306 -uroot -p
2.命名管道
针对Win系统的同一机器上的二个通信的进程通讯。SqlServer本地连接默认为命名管道。
Mysql中需要启用--enable-named-pipe选项来。
Mysql有--shared-memory使用内存的方式,连接时客户端使用-protocol=memory选项
命令行中直接输入mysql也可以进入 mysql,可以看到information_schema和test库
mysql 文件
1.参数文件 告诉mysql实例启动时在哪里可以找到数据库文件,并指定初始化参数.
orale找不到参数文件(二进制文件spfile和文本文件init.ora)是无法装载的,而mysql将会默认值来装载.
mysql> USE INFORMATION_SCHEMA
Database changed
mysql> select * from GLOBAL_VARIABLES;\G
一些参数是动态的可以在运行时修改如autocommit 可用set 来修改,而一些是只读的.
set read_buffer_size=131073 仅对当前会话有效,同autocommit
select @@session.read_buffer_size;\G 131072
select @@global.read_buffer_size;\G 131072
但是
set @@global.read_buffer_size=31074;
可以直接来修改,但是,它并不是对参数文件的修改,重启实例后将会失效.
2.日志文件 记录实例对某种条件做出响应时写入的文件.如错误,二进制日志文件,满查询日志文件,查询日志文件
错误文件
show variables like 'log_error'; 查看错误日志文件,一般为计算机名+err
慢查询文件
show variables like 'long_query_time'; 查看设置的慢查询的秒数,默认10秒钟
mysql> show variables like '%slow%';
+---------------------+-------------------------------------------+
| Variable_name | Value |
+---------------------+-------------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | C:\mysql-5.6.10-win32\data\clark-slow.log |
+---------------------+-------------------------------------------+
一条记录查询使用了0.5秒与0.05秒意义是不一样的,前者可能进行了表扫,后面可能走了索引.
mysql> show variables like 'log_queries_not_using_indexes%';\G OFF 这样将SQL语句运行的帐户和IP、运行时间、锁定的时间、返回行等。然后对之优化。
慢日志文件内容多时可使用mysqldumpslow mysqldumpslow -s -al -n 10 xxx.log 锁定用时最长的10条
也可以将慢查询放在mysql库中的slow_log. show create table mysql.slow_log;
show variables like 'log_output';\G 默认为FILE,如果改为TABLE,则就可在mysql.slow_log中查询到结果了
mysql.slow_log的引擎为CSV引擎,可以修改为myisam
查询日志
mysql.general_log 中,用于与slow_log同
二进制日志
二进制包含更改操作,但是不包括select,show这样的语句,二进制日志还包含执行时间等信息,有二种作用:恢复 与复制。
show variables like 'datadir';\G
show variables like '%binlog%';\G
mysql> show variables like '%binlog%';\G
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 | 未提交的二进制日志缓存,默认32KB,该值基于会话
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | STATEMENT | 5.1后引入,默认值与之前mysql版本一样,二进制日志文件记录的是日志的逻辑SQL。如果是ROW,则不是简单的SQL,而是表的行更改。如果是MIXED则会对这二种综合使用。
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 0 | 表示每写缓冲多少次就同步到磁盘。=1为同步方式写,不使用OS的缓冲。默认为0。
+-----------------------------------------+----------------------+
在分析binlog文件时使用 mysqlbinglog -vv --start-position=1011 xxx.0000002,可以清楚地看到语句的执行。
3.socket文件 当使用unix域套接字方式进行连接时需要的文件
Unix下可用。 show variables like 'socket';\G
4.pid文件 mysql实例的进程PID文件
同Apache,会在自己的目录下的机器名.pid文件中记录自己的PID
5.mysql表结构文件 存放mysql表结构定义文件
如.frm文件
6.存储引擎文件 每个存储引擎都会有自己的文件来保存各种数据.这些引擎真正存储了数据和索引等数据.
这些文件包括重做日志文件、表空间文件。
InnoDB引擎在存储设计上模仿了Oracle,将存储的数据按表空间存放,默认配置下有一个10MB的ibadata1的文件,即默认表空间。如果有多个文件组成可用innodb_data_file_path来指定,位于不同文件磁盘上时有利于提升性能。
show variables like 'innodb_file_per_table';\G 默认ON,将每个表放在单独的表空间中.
重做日志文件默认有二个ib_logfile0与ib_logfile1,它存储了事务日志。重做日志文件用于恢复到宕机前的状态。
show variables like 'innodb%log%';\G
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| innodb_api_enable_binlog | OFF |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 50331648 | 指定文件大小
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ | 路径
| innodb_mirrored_log_groups | 1 | 指定日志镜像文件组的数量,默认为1
| innodb_online_alter_log_max_size | 134217728 |
| innodb_undo_logs | 128 |
+----------------------------------+-----------+
InnoDB表空间
InnoDB 默认会将所有的数据库InnoDB引擎的表数据存储在一个共享空间中:ibdata1,这样就不很,增删数据库的时候,ibdata1文件不会自动收缩,单个数据库的备份也将成为问题。
通常只能将数据使用mysqldump 导出,然后再导入解决这个问题。
在MySQL的配置文件[mysqld]部分,增加innodb_file_per_table参数。可以修改InnoDB为独立表空间模式,每个数据库的每个表都会生成一个数据空间。
独立表空间:
优点:
1. 每个表都有自已独立的表空间。
2. 每个表的数据和索引都会存在自已的表空间中。
3. 可以实现单表在不同的数据库中移动。
4. 空间可以回收(除drop table操作处,表空不能自已回收)
a) Drop table操作自动回收表空间,如果对于统计分析或是日值表,删除大量数据后可以通过:alter table TableName engine=innodb;回缩不用的空间。
b) 对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。
c) 对于使用独立表空间的表,不管怎么删除,表空间的碎片不会太严重的影响性能,而且还有机会处理。
缺点:
单表增加过大,如超过100个G。
结论:
共享表空间在Insert操作上少有优势。其它都没独立表空间表现好。当启用独立表空间时,请合理调整一 下:innodb_open_files 。
InnoDB Hot Backup(冷备)的表空间cp不会面对很多无用的copy了。而且利用innodb hot backup及表空间的管理命令可以实现单现移动。
1.innodb_file_per_table设置.开启方法:
在my.cnf中[mysqld]下设置
innodb_file_per_table=1
2.查看是否开启:
mysql> show variables like '%per_table%';
3.关闭独享表空间
innodb_file_per_table=0关闭独立的表空间
Master 线程每10秒做一次full purge,把一些你回滚的事务中的UNDO空间(在共享表空间中)空间释放,但是其ibdata1文件大小仍为原来的大小,后续再UNDO日志时可使用这块释放的区域。
InnoDB表
表中均有主键,如果没显式地创建表则将会按以下二种方式自动创建一个:1.是否有非空的唯一索引,2如无则自动创建一个6字节大小的指针。
InnoDB引擎的逻辑存储结构和Oracle大致相同,所有数据被逻辑地存放在一个表空间中,表空间由段(segment) 区(extent) 页(page) 组成,页也称块(block);
表空间中存放的只是数据、索引和插入缓冲,其它如撤销信息、系统事务信息、二次写缓冲等还是存放在原来的共享表空间中.
段:组成表空间,段有数据段(B+树的页节点),索引段(InnoDB是索引组织的,故数据即索引,索引即数据,B+树的非页节点),回滚段。
并不是每个对象都有段,故,表空间是由分散的页和段组成。
区:由连续64个页组成的,每个页为16KB,即每个区为1MB。对于大数据段,InnoDB最多可申请4个区,来保证数据的顺序性能。
在启用innodb_file_per_table后,创建表的默认大小是96KB,区是至少1MB。这是因为每个段开始时,先有32个页大小的碎片页(fragment page)来存放数据,
当这些页用完后才是64个连续页的申请。
create table extentsite(col int not null auto_increment,col2 varchar(7000),primary key(col))engine=InnoDB;
这时的文件extentsite.ibd只有96KB。varchar(7000)保证一页中可存放2条记录。
insert into extentsite select NULL,repeat('a',7000); 大小96KB
insert into extentsite select NULL,repeat('b',7000); 大小96KB
同样的SQL,一共插入63条时,大小为576KB。共63条记录,32个页.
页:页是InnoDB磁盘管理的最小单位,SQLSERVER+ORACLE默认页大小为8KB,而InnoDB为16KB。且不可以更改。常见的页有:
数据页
UNDO页
系统页
事务数据页
插入缓冲位图页
插入缓冲空闲列表页
未压缩的二进制大对象页
压缩的二进制大对象页
行:数据按行存放,每页放的行的记录也有硬性定义,最多存放16KB/ 2~200行记录,即7992行记录。 MySQL infobright存储引擎是面向列的。面向列的对数据分析类特别有用,如
Sybase IQ,Google Big Table.
Row_format指定存放行记录的格式,Redundant为兼容之前版本保留的。默认为Compact行格式。
Compact 是5.1后引入的,设计目标是能高效存放数据。即,一个页中存放的行数据越多,性能越高。
变长字段长度列表+NULL标识位+记录头信息+列1数据+列2数据+…………
变长字段长度最长不可以超过2字节,即16位,即65535(实际为65532,官方指出这个65535是所有varchar字段的总长度),即varchar长度最长为65535。 NULL标识位,存放该行是否有NULL值用1表示。占字节为bytes。头信息 字节即40位。
每列除用用户定义的列外,还有二个隐藏列,即事务ID列和回滚指针列分别为6个字节和7个字节。如果未定义primaryKey 还会再增加一个6字节的RowID列.
Redundant是5.1之前的记录格式
字段长度偏移列表+记录头信息+列1+列2.……
create table rowTest(col int not null auto_increment,col2 varchar(7000),primary key(col))engine=InnoDB row_format=compact; row_format=redundant;
行溢出
将一些数据存储在真正的数据页面之外,如BLOB,LOB。
char的存储,在不同的字符集下,其长度是不定的,即,char(N)中的N是字符的长度而不是字节的长度。char_length(a),length(a)
查询十六进制select hex(a) from ...
约束:主键,外键,唯一键
set sql_mode='STRICT_TRANS_TABLES'
分区表
分区即是在将一个表或者索引分解成多个更小,更可管理的部分,就访问数据库的应用而言,从逻辑上讲,只有一个表或者一个索引,但是物理上这个表或者索引可能由数十个物理分区组成。
每个分区即是一个独立的对象,可以独自处理。支持水平分区,即将不同的行分布到不同的物理文件中。有RANGE分区,LIST分区,HASH分区,KEY分区。
是否启用分区
show variables like '%partition%';\G ??
create table t1(col int not null,xxxx)partition by range(col)(partition p0 values less than (10),partion p1 values less than(20));
当id小于10时,数据插入p0分区,当在10与20间时插入p1分区。 觉得用于日期分区较好。
100W的数据与1000W的数据本身构成的B+树的层次都是2,所以分区不能带来多少性能提高。即使100W层次为2,1000W层次为3,分区可以避免一次IO,但是扫描多个分区的IO……。
一般的B+树需要2~3次 IO;少有4层。
1000W并不是个大表。
set @@foreign_key_checks=0 忽略外键
show full processlist;\G 查看mysql数据库线程列表。
相关推荐
【狂神MySQL笔记】是一份全面且深入的MySQL学习资源,涵盖了从基础到高级的各种MySQL技术知识点。作为数据库领域的核心工具,MySQL因其高效、稳定、开源等特性,被广泛应用于各种规模的企业和项目中。这份笔记旨在...
尚硅谷的MySQL笔记可能是为了帮助学习者深入理解和掌握MySQL的相关知识而编写的。这份笔记可能包含了从基础概念到高级特性的全面讲解,包括但不限于SQL语言、数据类型、数据库设计、索引、事务处理、视图、存储过程...
这份"MySQL笔记"涵盖了其主要知识点,并且每个概念都配有实例,使得学习更为直观易懂。以下是对这些笔记的详细解析: 一、基础概念 MySQL的基础部分包括数据库、表、字段、记录等基本元素的创建、修改和删除。理解...
MySQL笔记
"狂神说Java"的MySQL笔记可能深入探讨了如何在Java编程环境中与MySQL进行交互,这对于我们理解数据库管理和开发至关重要。以下是对这个主题的详细阐述: 1. **MySQL基础**: - 数据库概念:MySQL是一个用于存储和...
mysql笔记
总的来说,这个MySQL笔记涵盖了数据库管理的各个方面,通过一天的学习,你可以建立起对MySQL的初步理解,并具备基本的数据库操作能力。然而,要想成为真正的MySQL专家,还需要不断实践和深入学习更高级的主题,如...
mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记
这份"mysql笔记及考试答案.zip"压缩包包含了深入学习MySQL的重要资源,包括一个PDF格式的MySQL笔记和一个包含考试试题及答案的文件,这对于复习MySQL知识或者准备相关考试非常有帮助。 MySQL笔记可能涵盖了以下几个...
MySQL笔记.md
老杜MySQL笔记总结.md(全)
这份"MySQL笔记资料"涵盖了全面的MySQL学习内容,包括基础理论、实战技巧以及优化策略,旨在帮助用户深入理解和掌握这个强大的数据存储与管理工具。 一、MySQL基础知识 在MySQL的学习之初,你需要了解其基本概念,...
这只是MySQL笔记的部分内容,实际上MySQL的功能远不止这些,还包括视图、索引、事务处理、存储过程、触发器、函数等多个高级特性。了解并掌握这些内容,将有助于在实际项目中更高效地管理和操作数据。
"MySQL笔记合集.pdf" MySQL 是一款开源的关系数据库管理系统(RDBMS),是一种relational database management system(RDBMS),是当下最流行的数据库管理系统之一。MySQL 的主要特点是开源、免费、跨平台、支持...
本笔记将深入探讨MySQL的基础知识,帮助你全面掌握这个强大的数据存储和管理工具。 首先,我们要理解数据库的基本概念。数据库是一个组织和存储数据的系统,允许用户通过结构化查询语言(SQL)进行数据的检索、更新...
- 如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果 - 如果只有INSERT和SELECT操作,可以选择Archive,...
MySQL笔记,老杜,初级MySQL笔记