索引 2021-05-02 存储过程生成100W数据 1.创建数据库oldboy mysql> create database oldboy; 2.创建存储过程，并且创建100W条数据use oldboy use oldboy create table t100w (id int,num int,k1 char(2),k2 char(4),dt timestamp) charset utf8mb4 collate utf8mb4_bin; delimiter // create procedure rand_data(in num int) begin declare str char(62) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; declare str2 char(2); declare str4 char(4); declare i int default 0; while i<num do set str2=concat(substring(str,1+floor(rand()*61)....
information_schema 2021-04-29 information_schema 是虚拟库，以及一些固定好的视图。 视图：一条查询语句的别名 元数据 ----> “基表”（无法直接查询和修改的） ----> DDL 进行元数据修改 ----> show ,desc(show),information_schema(全局类的统计和查询) use information_schema desc tables; #tables表中存储着整个数据库所有表的元数据信息 TABLE_SCHEMA 表所在的库 TABLE_NAME 表名 ENGINE 表的存储引擎 TABLE_ROWS 表的行数 AVG_ROW_LENGTH 平均行长度 INDEX_LENGTH 索引的长度 示例 --- 查询整个数据库中所有的库对应的表名 SELECT table_schema,table_name FROM information_schema.tables; --- 查询world和school库下的所有表名 SELECT table_schema,table_name FROM information_schema.tables....
Mysql在线改表工具pt-osc 2021-04-27 背景 在mysql 5.6 版本以前，修改表结构如添加索引、修改列，需要锁表，期间不能写入，对于一个有几千万数据的大表来说，这个简直不能接收，对于分秒必争的互联网时代，服务中断很长时间的后果是很严重的。 升级Mysql 到5.6 版本后，情况会好转，支持online-DDL, 但是实际在Alter表的时候，还会有可能锁表。 pt-online-schema-change是Percona-toolkit一员， 支持在不锁表的情况下，在线改表。 pt-osc的工作原理 创建一个和要执行 alter 操作的表一样的新的空表，后缀默认是new。 在新表执行alter table 语句，因为是空表，执行速度很快。 在原表中创建触发器3个触发器分别对应insert,update,delete操作。 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，注意这里是Replace操作。 表明替换 将原表名table修改为 tableold, 将tablenew 表明修改为原表名table 如果有参考该表的外键，根据alter-forei....
SQL 2021-04-29 SQL标准 SQL92 SQL99 Mysql5.7版本之后启用了SQL_Mode 严格模式 SQL语句类型 SQL语言类型英文缩写作用 DDL：数据定义语言data definition language建库、建表、改表 DCL：数据控制语言data control languageGRANT 和 REVOKE 两个指令构成 DML：数据操作语言data manipulation languageINSERT、UPDATE、DELETE DQL：数据查询语言data query language查询数据 数据类型 制数据的规范性，让数据有具体含义，在列上进行控制 数据类型种类功能说明 char、varchar字符类型255 65535定长（固定存储空间） 变长（按需）varchar存数据时，首先进行字符串长度判断，按需分配存储空间，会单独占用一个字节来记录此次的字符长度，超过255之后，需要两个字节长度记录字符长度。 enum枚举类型enum('bj','sh','sz','cq','hb',......)数据行较多时，会影响到索引的应用数字类禁止使用enum....
Mysql安装、用户管理、初始化、多实例 2021-04-28 下载 https://downloads.mysql.com/archives/community/ mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz 安装方式 安装方式优点缺点 rpm安装卸载简单可定制性差 glibc可定制性相比rpm包灵活些安装相比rpm包复杂些，需要手动初始化数据库 源码安装可定制性最强，根据需求和功能定制安装麻烦，需要手动初始化数据库 RPM: mysql-community-server-5.7.28-1.el7.x86_64.rpm ，需要在特定linux版本下安装。 glibc: mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz，依赖glibc库，可以安装在通用的Linux系统下 源代码编译安装: mysql-5.7.31.tar.gz，通用的Linux下都可以编译安装 glibc方式安装 # 解压软件 [root@localhost software]# ls mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz [root@loc....
普通索引、唯一索引 2020-05-05 查询性能对比 假设维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。如果市民系统需要按照身份证号查姓名，就会执行类似这样的 SQL 语句： select name from CUser where id_card = 'xxxxxxxyyyyyyzzzzz'; 定会考虑在 id_card 字段上建索引。由于身份证号字段比较大，不建议把身份证号当做主键，那么现在你有两个选择，要么给 id_card 字段创建唯一索引，要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。 如果从性能的角度考虑，应该选择唯一索引还是普通索引呢？选择的依据是什么呢？ 假设字段 k 上的值都不重复。如下图所示： 分别对两种索引查询和更新语句的性能进行分析。 查询过程 假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。 *....
MVCC、事务隔离 2020-05-01 事务到底是隔离的还是不隔离的？ 可重复读隔离级别，事务 T 启动的时候会创建一个视图 read-view，之后事务 T 执行期间，即使有其他事务修改了数据，事务 T 看到的仍然跟在启动时看到的一样。也就是说，一个在可重复读隔离级别下执行的事务，好像与世无争，不受外界影响。 在发生行锁冲突的时候，一个事务要更新一行，如果刚好有另外一个事务拥有这一行的行锁，则该事务会被锁住，进入等待状态。问题是，既然进入了等待状态，那么等到这个事务自己获取到行锁要更新数据的时候，它读到的值又是什么呢？ 下面是一个只有两行的表的初始化语句。 mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; insert into t(id, k) values(1,1),(2,2); 需要注意的是事务的启动时机。begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句....
