mysql笔记

1、show variables like 'autocommit';  查看是否是自动提交

set autocommit=0; //设置不是自动提交

set autocommit=1; //设置为自动提交

 

2、mysql服务器层不管理事务， 事务是由下层的存储引擎实现的， 所以在同一个事务中， 使用多种存储引擎是不可靠的

 

如果在事务中混合使用事务型和非事务型的表(如InnoDB和MyISAM表)，正常提交的情况下不会有问题， 但是如果回滚，非事务型表上就无法撤消

 

3、隐式和显示锁定

隐式锁定：在事务执行过程中， 随时都可以执行锁定， 锁只有在执行commit或rollback时才释放， InnoDB会根据隔离级别在需要的时候自动加锁

 

InnoDB显式锁定：

select ... lock in share mode

select ... for update

 

mysql 也支持lock tables 和unlock tables语句，这是服务器层实现的， 和存储引擎无关， 他们有自己的用途，不能替代事务处理，如果应用需要用到事务， 还是应该选择事务存储引擎

 

4、多版本并发控制MVCC？

是行级锁的一个变种，避免加锁操作，大都实现非阻塞读操作， 写操作也只锁定必要的行

分为：乐观并发控制， 悲观并发控制

 

5、存储引擎

每个表会有同名的.frm文件，保存表的定义

 

6、show tables status;显示所有表的信息

 

show table status like 'iv_log'; //显示表iv_log表信息， 表名，存储引擎， 行数

index_length:索引的大小（以字节为单位）

auto_increment：下一个auto_increment的值

 

7、InnoDB引擎

InnoDB数据存储在表空间

采用MVCC支持高并发

InnoDB事务模型和锁

崩溃回复， MyISAM不能恢复

 

8、MyISAM存储引擎

不支持事务和行级锁

对整张表加锁， 而不是针对行

索引特性：对于blob、text等长字段， 也可以基于前500个字符创建索引

 

不建议混合使用多个存储引擎

对于只记录日志的表， 则可以使用MyISAM

对于只读多，写少， 可以使用MyISAM

 

不要轻易相信MyISAM比InnoDB快

 

9、修改表的存储引擎

alter table mytable engine = InnoDB;

问题：执行时间长，mysql会按行将数据从原表复制到一张新的表， 替代方案， 手工分批进行表复制

 

性能：响应时间

性能优化的副产品：吞吐量， 如果响应时间快了， 吞吐量就会多

 

搞清楚时间花在哪里

优化第二个原则：无法测量就无法有效地优化， 所以第一步应该测量时间花在什么地方

 

很多人在优化时， 都将精力放在修改一些东西上， 很少进行精确的测量。我们的做法完全相反，将90%的时间来测量响应花在哪里。如果通过测量没找到答案，要么是测量方式错了， 要么是测量的不够完整。

测出时间花费在哪里， 和知道为什么花在那里， 是两码事

 

10、set profiling = 1; //在服务器上执行的所有语句，都会测量其耗费的时间

当一条查询提交给服务器时，些工具会记录剖析信息到一张临时表

 

show profiles;//显示所有查询

 

show profile from query 1; //显示查询序号为1的查询详细，显示每个步骤及其花费时间

 

11、show processlist; //显示所有连接数据库的进程

 

12、数据类型的优化

更小的通常更好：尽量使用可以正确存储数据的最小数据类型， 因为占用更少的磁盘、内存、cpu

简单就好：简单数据类型操作需要更少的cpu周期， 如日期使用日期类型，ip地址使用整型

 

尽量避免NULL:因为查询中包含null的列，使得索引、索引统计和值比较都更复杂

当为NULL的列被索引时， 每个索引记录需要一个额外的字节

如果在该列上建索引，则设计成not NULL

 

13、整数类型

tinyint 占用空间:8

smallint 16

mediumint 24 

int 32

bigint 64

 

存储值的范围从：-2指数(N-1) 到 2指数(n-1)-1  n是存储空间的位数

 

可选属性：unsigned， 存储范围提高一倍

tinyint unsigned 0-255

有符号和无符号使用相同的存储空间， 并具有相同的性能，

 

mysql可以为整数类型指定宽度， 如int(11), 对大多数应用这是没意义的：它不会限制值的合法范围，只是规定了mysql的一些交互工具（客户端）用来显示字符的个数， 对于存储和计算来说， int(1)和int(20)都是相同的

 

14、实数类型

带有小数， 它们不只是为了存储小数部分, 也可以使用decimal存储比bigint还大的整数

float, double占用的比decimal空间少

 

decimal：存储精确的小数

对于数据量大时， 可以使用bigint替换decimal， 如将货币单位改为分

 

15、字段串类型

varchar、char存储与存储引擎有关

varchar:可变长字符串， 越短的字符串占用的空间越少， varchar需要使用一个或两个额外字节记录字符串的长度如果列的最大长度小于或等于255字节，则使用一个字节表示， 否则使用两个字节

如：使用latin1字符集，varchar(10)的列需要11个字符存储空间, varchar(1000)需要1002个字节

 

varchar节省了存储空间，所以对性能也有帮助， 但是行是变长的， 在update时可能使用行变得比原来更长，这导致做额外的工作。innoDB需要分裂页来使行可以放进页内

 

char:定长，适合存储很短的字符串， 长度固定， 如存储密码的MD5值， 不容易产生碎片

char(1):只需要一个字节， 但varchar(1)需要2个字节， 另外一个记录长度

 

16、blob、text类型

专门的存储区存储， 使用一个指针，指向实际的值

 

17、日期和时间类型

datatime:范围：1001年到9999，精度为秒， 使用8个字节

timestamp：范围：1970.1.1到2038, 使用8个字节

 

存放微秒怎么办？

没有提供合适的数据，可以使用bigint存储

 

18、设计的陷阱

太多的列：服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列

 

19、范式和反范式

范式的优点：更新操作比反范式的要快， 只修改更少的数据， 表小，

缺点：关联表查询

 

反范式的优点：避免关联

 

混用范式化和反范式化

 

20、缓存表和汇总表

 

21、计数器表

用户的朋友数

网站的访问量：一条数据，并发慢， 可以创建100数据， 然后随机更新其中一条数据，再统计100数据的sum

 

22、更快的读，更慢的写

增加额外索引

增加冗余列

创建缓存表和汇总表