mysql 1 2 3_高性能mysql 第1，2，3章。

最新推荐文章于 2022-10-04 16:52:23 发布

李业云

最新推荐文章于 2022-10-04 16:52:23 发布

阅读量158

点赞数

文章标签： mysql 1 2 3

本文链接：https://blog.csdn.net/weixin_42389478/article/details/113904737

版权

一：第一章

1：使用事务

start transaction;select * fromt1;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';+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

| 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,0rows affected

mysql> show table status like 't1';+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

| 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 * fromt1;+----+

| id |

+----+

| 1 |

+----+

1 row in setmysql> explain select * fromt1;+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

+----+-------------+-------+--------+---------------+------+---------+------+------+-------+

1 row in set

14：当where，group by等后面的条件语句中，条件字段被覆盖索引，可以将条件语句存放到子查询中。

这样就会对索引先进行扫描，然后进行连接。而不扫描多余的行数据。

mysql> explain select * from salarie where name='wangwu'; -- 全表扫描，包含行数据。+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

1 row in setmysql> explain select * from salarie inner join (select id from salarie where name='wangwu') s on salarie.id=s.id;+----+-------------+------------+--------+---------------+------+---------+------+------+-------------+

+----+-------------+------------+--------+---------------+------+---------+------+------+-------------+

| 1 | PRIMARY | salarie | eq_ref | id | id | 4 | s.id | 1 | |

+----+-------------+------------+--------+---------------+------+---------+------+------+-------------+

3 rows in set

15：如果想重新使用索引并且它不会产生太多的条件组合，可以使用in() ，使用in()可以避免多个范围条件。

16：表修复命令 repair table

mysql> repair tablet1;+---------+--------+----------+---------------------------------------------------------+

+---------+--------+----------+---------------------------------------------------------+

+---------+--------+----------+---------------------------------------------------------+

1 row in set

17: 修改表的存储引擎：alter table t1 engine=innodb;

18:查询表的索引：show index from t1;

mysql> show index fromsalarie;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| salarie | 0 | id | 1 | id | A | 27 | NULL | NULL | | BTREE | | |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set

19:减少碎片和索引数据，可以使用optimize table 优化表，消除数据碎片

mysql> optimize table t1; --存储引擎不支持+---------+----------+----------+-----------------------------------------------------------+

+---------+----------+----------+-----------------------------------------------------------+

+---------+----------+----------+-----------------------------------------------------------+

1 row inset

+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

| 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 inset

mysql> alter table t1 engine=innodb; --修改存储引擎

Query OK,1row affected

Records:1Duplicates: 0 Warnings: 0

mysql>optimize table t1;+---------+----------+----------+-------------------------------------------------------------------+

+---------+----------+----------+-------------------------------------------------------------------+

+---------+----------+----------+-------------------------------------------------------------------+

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 23hourand 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,27rows affected

Records:27 Duplicates: 0 Warnings: 0mysql> show status like 'last_query_cost';+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| Last_query_cost | 10.499000 |

+-----------------+-----------+

1 row in setmysql> alter table salarie alter column id set default 5;

Query OK,0rows affected

Records:0 Duplicates: 0 Warnings: 0

24：快速建立MyISAM索引

高效加载MyISAM表的诀窍是：禁用键，加载数据，启动键

alter tablet1 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(*) 值。

李业云

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 1 2 3_高性能mysql 第1，2，3章。

一：第一章1：使用事务start transaction;select * fromt1;commit;2：查看事务状态mysql> show variables like 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON ...
复制链接

扫一扫