1、描述视图,存储过程,函数,触发器分别是什么
MariaDB [hellodb]> select * from students; #查看students表中的数据
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> CREATE VIEW V_STUDENTS AS SELECT STUID,NAME,AGE FROM students WHERE Gender='M'; #创建视图view，名为V_STUDENTS
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SHOW TABLE STATUS LIKE 'V_STUDENTS'\G #查看视图状态
*************************** 1. row ***************************
Name: V_STUDENTS
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
MariaDB [hellodb]> DESC V_STUDENTS; #查看视图表结构
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| STUID | int(10) unsigned | NO | | 0 | |
| NAME | varchar(50) | NO | | NULL | |
| AGE | tinyint(3) unsigned | NO | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
MariaDB [hellodb]> SELECT * FROM V_STUDENTS; #查看视图中的数据
+-------+---------------+-----+
| STUID | NAME | AGE |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 11 | Yuan Chengzhi | 23 |
| 13 | Tian Boguang | 33 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
+-------+---------------+-----+
15 rows in set (0.00 sec)
MariaDB [hellodb]> SHOW CREATE VIEW V_STUDENTS\G #查看视图创建过程
*************************** 1. row ***************************
View: V_STUDENTS
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `V_STUDENTS` AS select `students`.`StuID` AS `STUID`,`students`.`Name` AS `NAME`,`students`.`Age` AS `AGE` from `students` where (`students`.`Gender` = 'M')
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> UPDATE hellodb.V_STUDENTS set AGE='30' where NAME='Shi Zhongyu';
Query OK, 1 row affected (0.00 sec) #更改视图中的数据，等于是对原表数据进行操作，会对原始数据产生影响
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [hellodb]> SELECT * FROM V_STUDENTS WHERE STUID=1;
+-------+-------------+-----+
| STUID | NAME | AGE |
+-------+-------------+-----+
| 1 | Shi Zhongyu | 30 |
+-------+-------------+-----+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students where STUID=1;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 30 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> DROP VIEW V_STUDENTS;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
- 函数
- 系统函数
- 自定义函数（user-defined function UDF）
- 保存再mysql.proc表中
- 创建UDF
- CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
- 参数可以有多个，也可以没有参数，必须有且只有一个返回值
MariaDB [hellodb]> create function simplefun() returns varchar(20) return "test function!";
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) #创建函数时若开启了二进制日志功能则需要将log_bin_trust_function_creators设置为1，否则会报错
MariaDB [hellodb]> set global log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> create function simplefun() returns varchar(20) return "test function!"; #创建一个无参函数，名称为simplefun，使用select调用后会返回字符串test function!
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select * from mysql.proc\G;
*************************** 1. row ***************************
db: hellodb
name: simplefun
type: FUNCTION
specific_name: simplefun
language: SQL
sql_data_access: CONTAINS_SQL
is_deterministic: NO
security_type: DEFINER
param_list:
returns: varchar(20) CHARSET utf8
body: return "test function!"
definer: root@localhost
created: 2019-06-16 14:59:36
modified: 2019-06-16 14:59:36
sql_mode:
comment:
character_set_client: utf8
collation_connection: utf8_general_ci
db_collation: utf8_general_ci
body_utf8: return "test function!"
1 row in set (0.00 sec)
MariaDB [hellodb]> show create function simplefun\G;
*************************** 1. row ***************************
Function: simplefun
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `simplefun`() RETURNS varchar(20) CHARSET utf8
return "test function!"
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> select simplefun(); #调用创建的函数
+----------------+
| simplefun() |
+----------------+
| test function! |
+----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> drop function simplefun; #删除创建的函数
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DELIMITER // #更改结尾符号
MariaDB [hellodb]> CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) #创建一个有参函数，调用时传递一个uid，会删除uid对应的行并返回剩下行的行数
-> BEGIN
-> DELETE FROM students WHERE stuid = uid;
-> RETURN (SELECT COUNT(stuid) FROM students);
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DELIMITER ;
MariaDB [hellodb]> show function status\G;
*************************** 1. row ***************************
Db: hellodb
Name: deleteById
Type: FUNCTION
Definer: root@localhost
Modified: 2019-06-16 15:15:50
Created: 2019-06-16 15:15:50
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> select deleteById(1);
+---------------+
| deleteById(1) |
+---------------+
| 24 |
+---------------+
MariaDB [hellodb]> select * from students where STUID=1;
Empty set (0.00 sec)
#自定义函数中定义局部变量语法
#DECLARE var1[,var2,...] VAR_TYPE [DEFAULT 默认值]
#局部变量的作用范围是再BEGIN...END程序中，而且定义局部变量语句必须在BEGIN...END的第衣阿华那个定义
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create function addtwonumber(x smallint unsigned,y smallint unsigned)
-> returns smallint
-> begin
-> declare a,b smallint;
-> set a=x,b=y;
-> return a+b;
-> end//
Query OK, 0 rows affected (0.00 sec)
#创建一个多变量函数，实现简单的加法运算
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> show function status\G;
*************************** 1. row ***************************
Db: hellodb
Name: addtwonumber
Type: FUNCTION
Definer: root@localhost
Modified: 2019-06-16 15:28:37
Created: 2019-06-16 15:28:37
Security_type: DEFINER
Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
MariaDB [hellodb]> select addtwonumber(1,2);
+-------------------+
| addtwonumber(1,2) |
+-------------------+
| 3 |
+-------------------+
- 存储过程
- 把经常使用的SQL语句或业务逻辑封装起来，预编译保存在数据库中，当需要时从数据库中直接调用，省去了编译的过程
- 可提高运行速度，降低网络传输数据量
- 存储过程与自定义函数的区别
- 存储过程实现的过程要复杂一些，而函数的针对性骄傲强
- 存储过程可以有多个返回值，而自定义函数只有一个返回值
- 存储过程一般独立执行，而函数往往时作为其他SQL语句的一部分来使用
- 创建存储过程
- CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter …]]) routime_body
- proc_parameter : [IN|OUT|INOUT] parameter_name type
- 其中IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure showtime()
-> begin
-> select now();
-> end //
Query OK, 0 rows affected (0.00 sec)
#创建存储过程，显示当前时间
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call showtime;
+---------------------+
| now() |
+---------------------+
| 2019-06-16 15:46:13 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure selectbyid(in uid smallint unsigned)
-> begin
-> select * from students where stuid=uid;
-> end //
Query OK, 0 rows affected (0.00 sec)
#创建带参数存储过程，实现传参数并查找对应记录
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call selectbyid(2);
+-------+------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
| 2 | Shi Potian | 22 | M | 1 | 7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter //
MariaDB [hellodb]> create procedure deletebyid( in uid smallint unsigned,out num smallint unsigned)
-> begin
-> delete from students where stuid >= uid;
-> select row_count() into num;
-> end //
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> call deletebyid(20,@line);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select @line;
+-------+
| @line |
+-------+
| 6 |
+-------+
1 row in set (0.00 sec)
- 触发器
- 触发器的执行不是由程序调用，也不是由手工启动，而是由时间来触发、激活从而实现执行
- CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body`
- trigger_name：触发器的名称
- trigger_time：{BEFORE|AFTER},表示在事件之前或之后出发
- trigger_event：{INSERT|UPDATE|DELETE},出发的具体时间
- tbl_name：该触发器作用在表名
MariaDB [hellodb]> create table student_info (
-> stu_id int(11) not null auto_increment,
-> stu_name varchar(255) default null,
-> primary key(stu_id)
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> create table student_count (
-> student_count int(11) default 0
-> );
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> insert into student_count values(0);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> create trigger trigger_student_count_insert after insert on student_info for each row update student_count set student_count=student_count+1;
Query OK, 0 rows affected (0.00 sec)
#创建触发器，在insert指令后执行，更新student_count中的计数器
MariaDB [hellodb]> create trigger trigger_student_count_delete after delete on student_info for each row update student_count set student_count=student_count-1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show triggers\G;
*************************** 1. row ***************************
Trigger: trigger_student_count_insert
Event: INSERT
Table: student_info
Statement: update student_count set student_count=student_count+1
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: trigger_student_count_delete
Event: DELETE
Table: student_info
Statement: update student_count set student_count=student_count-1
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.00 sec)
MariaDB [hellodb]> USE information_schema;
Database changed
MariaDB [information_schema]> SELECT * FROM triggers WHERE trigger_name='trigger_student_count_insert'\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: hellodb
TRIGGER_NAME: trigger_student_count_insert
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: hellodb
EVENT_OBJECT_TABLE: student_info
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: update student_count set student_count=student_count+1
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> desc student_info;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| stu_id | int(11) | NO | PRI | NULL | auto_increment |
| stu_name | varchar(255) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
MariaDB [hellodb]> insert into student_info (stu_id,stu_name) values (1,'zhao');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | zhao |
+--------+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> insert into student_info (stu_id,stu_name) values (2,'qian');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from student_count; +---------------+
| student_count |
+---------------+
| 2 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> delete from student_info where stu_id=1;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from student_info; +--------+----------+
| stu_id | stu_name |
+--------+----------+
| 2 | qian |
+--------+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
2、描述MySQL中有哪些存储引擎并描述各自的区别
- 常用的存储引擎：
- MyISAM：
- 特点：
- 不支持事务
- 表级锁
- 读写相互阻塞，读时不能写，写时不能读
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据快，占用资源少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- 适用场景：
- 引擎文件：
- tbl_name.frm：表格式定义
- tbl_name.MYD：数据文件
- tbl_name.MYI：索引文件
- InnoDB：
- 特点：
- 支持事务，适合处理大量短期事务
- 行级锁
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性好
- 支持MVCC高并发
- MySQL5.5后支持全文索引
- 所有InnoDB表的数据和索引放置与同一个表空间中
- 表空间文件：datadir定义的目录下
- 数据文件：idbdata1,idbdata2,…
- 每个表单独使用一个表空间存储表的数据和索引
- 两类文件放在数据库独立目录中
- 数据文件（存储数据和索引）：tb_name.ibd
- 表格式定义：tb_name.frm
- Performance_Schema：Performance_Schema数据库使用
- Memory ：将所有数据存储在RAM中，以便在需要快速查找参考和其他类似数据的环境中进行快速访问。适用存放临时数据。引擎以前被称为HEAP引擎
- MRG_MyISAM：使MySQL DBA或开发人员能够对一系列相同的MyISAM表进行逻辑分组，并将它们作为一个对象引用。适用于VLDB(Very Large Data Base)环境，如数据仓库
- Archive ：为存储和检索大量很少参考的存档或安全审核信息，只支持SELECT和INSERT操作；支持行级锁和专用缓存区
- Federated联合：用于访问其它远程MySQL服务器一个代理，它通过创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，而后完成数据存取，提供链接单独MySQL服务器的能力，以便从多个物理服务器创建一个逻辑数据库。非常适合分布式或数据集市环境
- BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性
- Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性
- CSV：CSV存储引擎使用逗号分隔值格式将数据存储在文本文件中。可以使用CSV引擎以CSV格式导入和导出其他软件和应用程序之间的数据交换
- BLACKHOLE ：黑洞存储引擎接受但不存储数据，检索总是返回一个空集。该功能可用于分布式数据库设计，数据自动复制，但不是本地存储
- example：“stub”引擎，它什么都不做。可以使用此引擎创建表，但不能将数据存储在其中或从中检索。目的是作为例子来说明如何开始编写新的存储引擎
3、描述索引的工作原理并创建各种索引
- 索引：适用于数据量大，读取多写入少的场景
- 特殊的数据结构，定义在查找时作为查找条件的字段，在MySQL中又称为键key，索引通过存储引擎实现
- 优点：
- 索引可以降低服务需要扫描的数据量，减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机IO转为顺序IO
- 缺点：
- B+ Tree
- 每个根节点与分支节点只存放索引，所有的原始数据都存放在叶子节点，且存在数据指针（链表），可大大提高范围搜索的效率，所有记录的查询效率是一样的。
- 红黑树
- B- Tree
- 平衡树，多叉树，每个节点直接存放数据，最差的结果查询次数一样
- 可以使用B+Tree索引的查询类型
- 全值匹配：精确所有索引列
- 匹配最左前缀：即只是用索引的第一列
- 匹配范围值；
- 精确匹配某一列并范围匹配另一列
- B+Tree索引的限制：
- 如不从最左列开始，则无法使用索引
- 不能跳过索引中的列
- 如果查询中某个列是为查询范围，那么其右侧的列都无法再使用索引
- 聚簇索引和非聚簇索引：
- 根据磁盘上保存索引的方式来划分
- 索引与数据是否放在一起
- 主键索引和二级索引
- 主键索引：
- 二级索引：
- 索引和主键放在一起，先确定主键，再通过主键索引确定数据位置
- 稠密索引、稀疏索引：
- 简单索引：
- 组合索引：
- 索引优化策略：
- 独立地使用列：尽量避免其参与运算，独立的列索引指索引列不能是表达式的一部分，也不能是函数的参数，再where条件中，始终将索引列单独放在比较符号的一侧
- 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
- 索引选择性：不重复的索引值和数据表的记录占总数的比值
- 多索引列：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
- 先择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
- 索引优化建议：
- 只要列中含有NULL值，就最好不要在此列设置索引，符合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该指定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或order by的子句，应该建立复合索引
- 对于like语句，以%或者-开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作，会导致全文索引
- sql语句性能优化
- 查询时，能不用就不用，尽量写全字段名
- 大部分情况下连接效率远大于子查询，inner join
- 多表连接时，尽量小表驱动大表，即小表join大表
- 在有大量记录的表分页时使用limit
- 对于经常使用的查询，可以开启缓存
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句进行优化
MariaDB [hellodb]>SET GLOBAL userstat=1; #开启索引统计功能
MariaDB [hellodb]> desc testlog;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(10) | YES | | NULL | |
| age | int(11) | YES | | 20 | |
+-------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> create index index_name on testlog(name(10));
Query OK, 0 rows affected (0.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> create index index_name_age on testlog(name,age);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> show indexes from testlog\G
*************************** 1. row ***************************
Table: testlog
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 100334
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: testlog
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 100334
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: testlog
Non_unique: 1
Key_name: index_name_age
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: testlog
Non_unique: 1
Key_name: index_name_age
Seq_in_index: 2
Column_name: age
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
4 rows in set (0.00 sec)
MariaDB [hellodb]> select * from testlog where age='100';
+-----+---------+------+
| id | name | age |
+-----+---------+------+
| 100 | wang100 | 100 |
+-----+---------+------+
1 row in set (0.01 sec)
MariaDB [hellodb]> select * from testlog where name='wang2';
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | wang2 | 2 |
+----+-------+------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from testlog where id = '1000';
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1000 | wang1000 | 1000 |
+------+----------+------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show index_statistics; #统计索引使用情况
+--------------+------------+----------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+----------------+-----------+
| hellodb | testlog | PRIMARY | 1 |
| hellodb | testlog | index_name | 10 |
| hellodb | testlog | index_name_age | 201113 |
+--------------+------------+----------------+-----------+
4、总结binlog备份方法，用脚本实现每小时备份binlog
- binlog：又称为归档日志
- 记录导致数据变化或潜在导致数据改变的SQL语句，可用于数据恢复
- 记录已提交的日志
- 不依赖于存储引擎类型
- 通过“重放”日志文件中的时间来生成数据副本
- 三种记录格式
- 基于语句型，默认模式statement，用于记录命令本身
- 基于行记录：row，用于记录数据，日志量较大
- 混合模式：mixed，让系统自行判定基于哪种方式进行
MariaDB [hellodb]> show variables like 'binlog_format'; #查看二进制日志格式
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
- 二进制日志文件的构成
- 日志文件：
- mysql|mariadb-bin.00001，二进制格式
- 索引文件：mysql|mariadb-bin.index
- mysqldump常见选项：
- -A， --all-databases 备份所有数据库，含create database
- -B , --databases db_name… 指定备份的数据库，包括create database语句
- -E, --events：备份相关的所有event scheduler
- -R, --routines：备份所有存储过程和自定义函数
- –triggers：备份表相关触发器，默认启用,用–skip-triggers，不备份触发器
- –default-character-set=utf8 指定字符集
- –master-data[=#]： 此选项须启用二进制日志
- 1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
- 2：记录为注释的CHANGE MASTER TO语句
- 此选项会自动关闭–lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启–single-transaction）
- -F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和–single-transaction或-x，–master-data 一起使用实现，此时只刷新一次日志
- –compact 去掉注释，适合调试，生产不使用
- -d, --no-data 只备份表结构
- -t, --no-create-info 只备份数据,不备份create table
- -n,–no-create-db 不备份create database，可被-A或-B覆盖
- –flush-privileges 备份mysql或相关时需要使用
- -f, --force 忽略SQL错误，继续执行
- –hex-blob 使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
- -q, --quick 不缓存查询，直接输出，加快备份速度
[root@localhost data]
logdir=/data/binlog
user=root
userpasswd=''
backdir=/data/backup/
/usr/bin/mysqladmin -u$user -p$userpasswd flush-logs
mysqldump -A --single-transaction -F --master-data=2 | gzip > $backdir/all_back_`date +%F-%T`.sql.gz
[root@localhost ~]
0 * * * * /bin/bash /data/mysqlbak.sh
515
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
