高性能mysql 第1,2,3章。
一: 第一章
1:使用事务
start transaction; select * from t1; commit;
2:查看事务状态
mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set
mysql中事务是默认提交的,也就是每个查询视为一个事务单独执行。如果想关闭事务的自动提交,可以使用set autocommit=0; 或者set autocommit=off;
3:设置 事务的隔离级别。 例如设置事务隔离级别为读写提交:
set session transaction isolation level read committed;
4:mysql的存储引擎 , 如果查询所有表的存储引擎可以使用show table status; 如果查询user表的存储引擎,可以使用下面的语句
mysql> show table status like 'user'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ | user | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 4096 | 0 | 1 | 2018-07-14 20:33:02 | 2018-07-14 20:33:02 | NULL | utf8_general_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+ 1 row in set
改变表的存储引擎
mysql> alter table test engine=InnoDB; Query OK, 0 rows affected
5:想复制一张表的话,可以使用
mysql> create table test1 like test;
二: 第二章,测试
1:用http_load测试http下载
txt 文件
http://www.baidu.com/ https://map.baidu.com/ https://www.cnblogs.com/zhyunfe/p/6209074.html
sql
http_load -parllel 1 -seconds 10 test.txt;
2:慢查询日志
慢查询日志包括:被查询记录的时间,执行查询的用户,执行时间,查询语句,返回的行数。 可以分析可以的查询。
快查询日志包括:查询语句。
3: show profile; 可以显示查询的各个值。
mysql> show profile; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 3E-5 | | checking permissions | 6E-6 | | Opening tables | 0.001018 | | System lock | 1.7E-5 | | init | 3.6E-5 | | optimizing | 3E-6 | | statistics | 8E-6 | | preparing | 5E-6 | | executing | 2E-6 | | Sending data | 0.000215 | | end | 1E-5 | | query end | 4E-6 | | closing tables | 6E-6 | | freeing items | 8.4E-5 | | logging slow query | 2E-6 | | cleaning up | 4E-6 | +----------------------+----------+ 16 rows in set
第三章:数据库调优
1:整数类型:TINYINT,SAMLLINT,MEDIUMINT,INT,BIGINT 的位数分别为 8,16,24,32,64 ,修饰符:无符号数UNSIGNED,有符号数SIGNED
2:实数,float double decimal(10,5) 一般保存同样的数据,decimal占用空间更大。
3:varchar() 一般占用一到两个字节保存长度,如果varchar的长度为255。那么用一个字节表示长度。
4:text和blob, blob:类型有TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB
text类型有:TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT.
mysql把blob和text当成实体对象来处理。当数据过大时,innodb会使用单独的外部存储区域来进行保存。
mysql对blob和text的排序方式,是按照前若干个字节进行排序。不会按照完整长度进行排序。
5:将某些含有几个值的字段设置为enum类型, enum进行连接速度较快。
6:日期时间:datatime 是年月日,时分秒类型, timestamp是一个长整形。 date 年月日
7:位数据类型, BIT(1) 只占一位,可以保存true,false值,mysql将bit当做字符串类型。myisam中17位包装为3个字节
8:set, 在mysql中,集合set是指一个字段可以取集合的一个或者多个值
- create table test(field set('a','b'));
- //插入值可以为'a','b','1','2','3'
- //其中,1表示第一个值a,2表示第二个值b,3表示第一个值和第二个值,故随着集合内元素的增加,对应元素插入为1,2,4,8,16,32,64...
create table acl(perms set('can_read','can_delete','can_write') not null); insert into acl(perms) values('can_read','can_delete'); select perms from acl where find_in_set('can_read',perms);
enum和set适合用来保存订单的状态,产品的类别,或性别这样的信息。
9:选择标识符,整数通常是标识符的最佳选择,因为它速度快。能使用auto_increment;
10:常见索引:B+树,hash索引(memory引擎里面的)。
mysql> create table t(id int not null) engine=memory; Query OK, 0 rows affected mysql> show table status like 't1'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | t1 | MEMORY | 10 | Fixed | 0 | 8 | 0 | 16777216 | 0 | 0 | NULL | 2018-08-25 22:28:51 | NULL | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 1 row in set
innodb会使用自适应hash索引,当一些索引值被频繁访问的时候,会在b树的顶端建立hash索引。
11:hash索引和b树的索引比较
hash索引更快O(1),但是不能排序。
hash索引只包含了hash码和指针,但不包含值。只支持 =,<=>,in 的比较,不支持price>1000的范围查询。
12:高性能索引策略
a:使用索引,首先要隔离列,例如 select * from t1 where f1+2=5; where后面有运算,这样就没有隔离列,不能使用索引。
b: 前缀索引, 只使用某一字段的前n个字符简历索引。
c:聚集索引,innodb使用的是B+树的聚集索引,因为在节点中保存了索引和数据
d:覆盖索引,如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。只需要读取索引(因为索引叶子节点中有需要的数据),而不需要读取行数据。
13:explain 可以对执行的语句进行解释
mysql> select * from t1; +----+ | id | +----+ | 1 | +----+ 1 row in set mysql> explain select * from t1; +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | t1 | system | NULL | NULL | NULL | NULL | 1 | | +----+-------------+-------+--------+---------------+------+---------+------+------+-------+ 1 row in set
14: 当where,group by等后面的条件语句中,条件字段被覆盖索引,可以将条件语句存放到子查询中。
这样就会对索引先进行扫描,然后进行连接。而不扫描多余的行数据。
mysql> explain select * from salarie where name='wangwu'; -- 全表扫描,包含行数据。 +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | salarie | ALL | NULL | NULL | NULL | NULL | 27 | Using where | +----+-------------+---------+------+---------------+------+---------+------+------+-------------+ 1 row in set mysql> explain select * from salarie inner join (select id from salarie where name='wangwu') s on salarie.id=s.id; +----+-------------+------------+--------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 11 | | | 1 | PRIMARY | salarie | eq_ref | id | id | 4 | s.id | 1 | | | 2 | DERIVED | salarie | ALL | NULL | NULL | NULL | NULL | 27 | Using where | +----+-------------+------------+--------+---------------+------+---------+------+------+-------------+ 3 rows in set
15:如果想重新使用索引并且它不会产生太多的条件组合,可以使用in() , 使用in()可以避免多个范围条件。
16:表修复命令 repair table
mysql> repair table t1; +---------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+--------+----------+---------------------------------------------------------+ | test.t1 | repair | note | The storage engine for the table doesn't support repair | +---------+--------+----------+---------------------------------------------------------+ 1 row in set
17: 修改表的存储引擎:alter table t1 engine=innodb;
18:查询表的索引:show index from t1;
mysql> show index from salarie; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | salarie | 0 | id | 1 | id | A | 27 | NULL | NULL | | BTREE | | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set
19:减少碎片和索引数据,可以使用optimize table 优化表,消除数据碎片
mysql> optimize table t1; -- 存储引擎不支持 +---------+----------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+-----------------------------------------------------------+ | test.t1 | optimize | note | The storage engine for the table doesn't support optimize | +---------+----------+----------+-----------------------------------------------------------+ 1 row in set mysql> show table status like 't1'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ | t1 | MEMORY | 10 | Fixed | 1 | 8 | 258064 | 16777216 | 0 | 0 | NULL | 2018-08-26 08:39:11 | NULL | NULL | latin1_swedish_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+ 1 row in set mysql> alter table t1 engine=innodb; -- 修改存储引擎 Query OK, 1 row affected Records: 1 Duplicates: 0 Warnings: 0 mysql> optimize table t1; +---------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+----------+----------+-------------------------------------------------------------------+ | test.t1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.t1 | optimize | status | OK | +---------+----------+----------+-------------------------------------------------------------------+ 2 rows in set
20:正则化和非正则化
正则化,在正则化数据库中,每个因素只会被表达一次。非正则化总,信息是重复的。
这样就需要用到范式分解,如果正则化,每个表不能有重复信息。
第一范式:每个字段只能是原子域。A不能分解成字段B,C;
第二范式:不能存在部分函数依赖,例如字段A,B,C (A,B)主属性>C, 不能够存在(A)>B
第三范式:一个表中字段依赖,不能存在A(主属性)>B>C
BCNF: 在第三范式基础上,消除主属性对主属性的部分函数依赖与传递函数依赖,
第4范式: 多值依赖
正则化优缺点:a:跟新较快,因为表小,b:改动变小,因为没有重复数据 c: 容易装入内存,因为小 d:缺点:查询慢,需要连接。
21:缓存和汇总表
一般可以保留一个汇总表,以供使用,例如没半个小时对网站访问数量汇总一次,可以放到汇总表里面,缺点是不是实时准确的 ,优点是效率高。
select sum(cnt) from t1 where hr between concat(left(now(),14),'00.00')-interval 23 hour and concat(left(now(),14),'00.00')-interval 1 hour;
不精确的统计或使用小范围查询以弥补时间间隔的精确统计-都比统计t1表中全部行要高效的多。
缓存表对于优化搜索和获取数据的查询时有用的。
22:计数表,记录一个小的表,用于统计用户数。点击次数等。
23: 加速alter table , 当修改某一列时,可以使用alter column,这个命令更改了.frm文件并且没有改动表。 任何modify column都会导致表重建。
mysql> alter table salarie modify column id tinyint(3) not null; Query OK, 27 rows affected Records: 27 Duplicates: 0 Warnings: 0 mysql> show status like 'last_query_cost'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | Last_query_cost | 10.499000 | +-----------------+-----------+ 1 row in set mysql> alter table salarie alter column id set default 5; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0
24:快速建立MyISAM索引
高效加载MyISAM表的诀窍是:禁用键,加载数据,启动键
alter table t1 disable keys; -- load the data alter table t1 enable keys;
25:各个引擎的区别
myisam引擎:a: 表锁 b: 不支持自动数据恢复 c: 不支持事务 d: 只有索引被缓存在内存中 e: 紧密存储
memory引擎:a:表锁 b: 不支持动态行 c: hash索引是默认索引类型 d: 没有索引统计 e: 重启后丢失数据
innodb引擎: a:事务性 b:外键 c: 行级锁 d: 多版本 e: 按主键聚集 f: 所有索引包含主键列 ,索引按主键引用行。g: 优化的缓存 h:未压缩的索引 i:装载数据缓慢
j:阻塞auto_increment k:没有缓存的count(*) 值。