Oracle与MySQL的差异和对比：配套hands-on参考脚本。
方便客户针对培训课件内容进行动手实践，加强理解。
---------------------------------
-- 主题：Oracle与MySQL的差异和对比
-- 一、MySQL的基础特性
-- 二、重要特性差异对比
-- 三、性能对比和优化技巧
--------------------------------
Hands-on场景环境准备@MySQL实例
--------------------------------
-- Hands-on场景环境准备@MySQL实例
--------------------------------
--Create db & user
CREATE DATABASE demodb;
CREATE USER 'alfred'@'localhost' IDENTIFIED BY 'alfred123';
GRANT ALL PRIVILEGES ON demodb.* TO 'alfred'@'localhost';
GRANT SELECT ON information_schema.* TO 'alfred'@'localhost';
FLUSH PRIVILEGES;
--Create tables
mysql -ualfred -palfred123 -Ddemodb
CREATE TABLE t (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
Insert into t(name) values ('Alfred'),('Mcdull');
commit;
select * from t;
Hands-on场景1：模拟MDL等待
--------------------------------
-- Hands-on场景1：模拟MDL等待
--------------------------------
--T1@ session1:TRX
begin;
select id from t;
--T2@ session2:DDL【被阻塞】
alter table t add age int;
--T3@ session3:Select【被阻塞】
select id from t;
--T4@ session1:TRX
commit;
--Check process & trx info
show processlist;
select * from information_schema.innodb_trx\G
Hands-on场景2：模拟死锁
--------------------------------
-- Hands-on场景2：模拟死锁
--------------------------------
--T1@ session1
begin;
update t set age=20 where id=1;
--T2@ session2
begin;
update t set age=18 where id=2;
--T3@ session1【被阻塞】
update t set age=18 where id=2;
--T4@ session2【数据库监测到死锁，事务回滚】
update t set age=20 where id=1;
Hands-on场景3：DB读取最小单位
--------------------------------
-- Hands-on场景3：DB读取最小单位
--------------------------------
--Oracle
show parameter db_block_size
--MySQL
show variables like 'innodb_page_size';
Hands-on场景4：查看SQL索引使用情况
--------------------------------
-- Hands-on场景4：查看SQL索引使用情况
--------------------------------
INSERT INTO t (name, age)
SELECT
CONCAT('Name_', FLOOR(RAND() * 10000)),
FLOOR(RAND() * 100)
FROM
(SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t1
CROSS JOIN (SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t2
CROSS JOIN (SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t3
CROSS JOIN (SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t4
CROSS JOIN (SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t5
CROSS JOIN (SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t6
CROSS JOIN (SELECT 1 AS dummy UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t7
LIMIT 10000;
explain select * from t where name='Alfred';
CREATE INDEX idx_name_age ON t(name, age);
CREATE INDEX idx_age ON t(age);
CREATE INDEX idx_name ON t(name);
explain select * from t where name='Alfred';
DROP INDEX idx_name_age ON t;
explain select * from t where name='Alfred';
Hands-on场景5：MySQL默认存储引擎、binlog、redolog信息确认
--------------------------------------------------------
-- Hands-on场景5：MySQL默认存储引擎、binlog、redolog信息确认
--------------------------------------------------------
show engines;
SELECT @@default_storage_engine;
ps -ef|grep mysql
cd [--datadir]
ls -lrth mysql-bin.*
ls -lrth ib_logfile*
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
Hands-on场景6：查看MySQL业务表的详细状态信息
------------------------------------------
-- Hands-on场景6：查看MySQL业务表的详细状态信息
------------------------------------------
show table status like 't'\G
show index from t;