释放双眼，带上耳机，听听看~！
1、描述视图,存储过程,函数,触发器分别是什么并实践
\G后面不能再加分号;，因为\G在功能上等同于;，如果加了分号，那么就是;;(2个分号)，SQL语法错误
- 视图 VIEW,虚表，保存有实表的查询结果
视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制。
修改视图也会修改基表中的内容。
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
2+-------+---------------+-----+--------+---------+-----------+
3| StuID | Name | Age | Gender | ClassID | TeacherID |
4+-------+---------------+-----+--------+---------+-----------+
5| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
6| 2 | Shi Potian | 22 | M | 1 | 7 |
7| 3 | Xie Yanke | 53 | M | 2 | 16 |
8| 4 | Ding Dian | 32 | M | 4 | 4 |
9| 5 | Yu Yutong | 26 | M | 3 | 1 |
10| 6 | Shi Qing | 46 | M | 5 | NULL |
11| 7 | Xi Ren | 19 | F | 3 | NULL |
12| 8 | Lin Daiyu | 17 | F | 7 | NULL |
13| 9 | Ren Yingying | 20 | F | 6 | NULL |
14| 10 | Yue Lingshan | 19 | F | 3 | NULL |
15| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
16| 12 | Wen Qingqing | 19 | F | 1 | NULL |
17| 13 | Tian Boguang | 33 | M | 2 | NULL |
18| 14 | Lu Wushuang | 17 | F | 3 | NULL |
19| 15 | Duan Yu | 19 | M | 4 | NULL |
20| 16 | Xu Zhu | 21 | M | 1 | NULL |
21| 17 | Lin Chong | 25 | M | 4 | NULL |
22| 18 | Hua Rong | 23 | M | 7 | NULL |
23| 19 | Xue Baochai | 18 | F | 6 | NULL |
24| 20 | Diao Chan | 19 | F | 7 | NULL |
25| 21 | Huang Yueying | 22 | F | 6 | NULL |
26| 22 | Xiao Qiao | 20 | F | 1 | NULL |
27| 23 | Ma Chao | 23 | M | 4 | NULL |
28| 24 | Xu Xian | 27 | M | NULL | NULL |
29| 25 | Sun Dasheng | 100 | M | NULL | NULL |
30+-------+---------------+-----+--------+---------+-----------+
3125 rows in set (0.01 sec)
32
33MariaDB [hellodb]> create view v_students as select stuid,name,age from students;
34Query OK, 0 rows affected (0.01 sec)
35
36MariaDB [hellodb]> show tables;
37+-------------------+
38| Tables_in_hellodb |
39+-------------------+
40| classes |
41| coc |
42| courses |
43| scores |
44| students |
45| teachers |
46| toc |
47| v_students |
48+-------------------+
498 rows in set (0.01 sec)
50
51MariaDB [hellodb]> show table status like 'v_students'\G
52*************************** 1. row ***************************
53 Name: v_students
54 Engine: NULL
55 Version: NULL
56 Row_format: NULL
57 Rows: NULL
58 Avg_row_length: NULL
59 Data_length: NULL
60Max_data_length: NULL
61 Index_length: NULL
62 Data_free: NULL
63 Auto_increment: NULL
64 Create_time: NULL
65 Update_time: NULL
66 Check_time: NULL
67 Collation: NULL
68 Checksum: NULL
69 Create_options: NULL
70 Comment: VIEW
711 row in set (0.01 sec)
72
73MariaDB [hellodb]> show create view v_students\G
74*************************** 1. row ***************************
75 View: v_students
76 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`
77character_set_client: utf8mb4
78collation_connection: utf8mb4_general_ci
791 row in set (0.00 sec)
80
81MariaDB [hellodb]> insert v_students values(26,'jack',30);
82Query OK, 1 row affected (0.01 sec)
83
84MariaDB [hellodb]> select * from students where stuid=26;
85+-------+------+-----+--------+---------+-----------+
86| StuID | Name | Age | Gender | ClassID | TeacherID |
87+-------+------+-----+--------+---------+-----------+
88| 26 | jack | 30 | F | NULL | NULL |
89+-------+------+-----+--------+---------+-----------+
901 row in set (0.00 sec)
91
92MariaDB [hellodb]> select * from v_students;
93+-------+---------------+-----+
94| stuid | name | age |
95+-------+---------------+-----+
96| 1 | Shi Zhongyu | 22 |
97| 2 | Shi Potian | 22 |
98| 3 | Xie Yanke | 53 |
99| 4 | Ding Dian | 32 |
100| 5 | Yu Yutong | 26 |
101| 6 | Shi Qing | 46 |
102| 7 | Xi Ren | 19 |
103| 8 | Lin Daiyu | 17 |
104| 9 | Ren Yingying | 20 |
105| 10 | Yue Lingshan | 19 |
106| 11 | Yuan Chengzhi | 23 |
107| 12 | Wen Qingqing | 19 |
108| 13 | Tian Boguang | 33 |
109| 14 | Lu Wushuang | 17 |
110| 15 | Duan Yu | 19 |
111| 16 | Xu Zhu | 21 |
112| 17 | Lin Chong | 25 |
113| 18 | Hua Rong | 23 |
114| 19 | Xue Baochai | 18 |
115| 20 | Diao Chan | 19 |
116| 21 | Huang Yueying | 22 |
117| 22 | Xiao Qiao | 20 |
118| 23 | Ma Chao | 23 |
119| 24 | Xu Xian | 27 |
120| 25 | Sun Dasheng | 100 |
121| 26 | jack | 30 |
122+-------+---------------+-----+
12326 rows in set (0.00 sec)
124
125
- 函数
系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
自定义函数 (user-defined function UDF)，保存在mysql.proc表中
说明：
参数可以有多个,也可以没有参数
必须有且只有一个返回值
最好不要有空值，空值需用is判断
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
2+----------+
3| count(*) |
4+----------+
5| 26 |
6+----------+
71 row in set (0.00 sec)
8
9MariaDB [hellodb]> select count(stuid) from students;
10+--------------+
11| count(stuid) |
12+--------------+
13| 26 |
14+--------------+
151 row in set (0.00 sec)
16
17MariaDB [hellodb]> create function simplefun() returns varchar(20) return "Hello World!";
18Query OK, 0 rows affected (0.01 sec)
19
20MariaDB [hellodb]> select simplefun();
21+--------------+
22| simplefun() |
23+--------------+
24| Hello World! |
25+--------------+
261 row in set (0.00 sec)
27
28MariaDB [hellodb]> show function status\G
29*************************** 1. row ***************************
30 Db: hellodb
31 Name: simplefun
32 Type: FUNCTION
33 Definer: root@localhost
34 Modified: 2019-08-04 16:40:13
35 Created: 2019-08-04 16:40:13
36 Security_type: DEFINER
37 Comment:
38character_set_client: utf8mb4
39collation_connection: utf8mb4_general_ci
40 Database Collation: utf8_general_ci
411 row in set (0.01 sec)
42
43MariaDB [hellodb]> use db1;
44Reading table information for completion of table and column names
45You can turn off this feature to get a quicker startup with -A
46
47Database changed
48MariaDB [db1]> show function status\G
49*************************** 1. row ***************************
50 Db: hellodb
51 Name: simplefun
52 Type: FUNCTION
53 Definer: root@localhost
54 Modified: 2019-08-04 16:40:13
55 Created: 2019-08-04 16:40:13
56 Security_type: DEFINER
57 Comment:
58character_set_client: utf8mb4
59collation_connection: utf8mb4_general_ci
60 Database Collation: utf8_general_ci
611 row in set (0.00 sec)
62
63MariaDB [db1]> select simplefun();
64ERROR 1305 (42000): FUNCTION db1.simplefun does not exist
65MariaDB [db1]> select hellodb.simplefun();
66+---------------------+
67| hellodb.simplefun() |
68+---------------------+
69| Hello World! |
70+---------------------+
711 row in set (0.00 sec)
72
73MariaDB [db1]> show create function hellodb.simplefun\G
74*************************** 1. row ***************************
75 Function: simplefun
76 sql_mode:
77 Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `simplefun`() RETURNS varchar(20) CHARSET utf8
78return "Hello World!"
79character_set_client: utf8mb4
80collation_connection: utf8mb4_general_ci
81 Database Collation: utf8_general_ci
821 row in set (0.00 sec)
83
84MariaDB [db1]> drop function hellodb.simplefun;
85Query OK, 0 rows affected (0.00 sec)
86
87MariaDB [db1]> show create function hellodb.simplefun\G
88ERROR 1305 (42000): FUNCTION simplefun does not exist
89
90
有参数UDF
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
2MariaDB [hellodb]> CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)
3 -> BEGIN
4 -> DELETE FROM students WHERE stuid = uid;
5 -> RETURN (SELECT COUNT(stuid) FROM students);
6 -> END//
7Query OK, 0 rows affected (0.00 sec)
8
9MariaDB [hellodb]> DELIMITER ;
10MariaDB [hellodb]> show function status\G
11*************************** 1. row ***************************
12 Db: hellodb
13 Name: deleteById
14 Type: FUNCTION
15 Definer: root@localhost
16 Modified: 2019-08-04 16:52:05
17 Created: 2019-08-04 16:52:05
18 Security_type: DEFINER
19 Comment:
20character_set_client: utf8mb4
21collation_connection: utf8mb4_general_ci
22 Database Collation: utf8_general_ci
231 row in set (0.01 sec)
24
25MariaDB [hellodb]> select deletebyid(26);
26+----------------+
27| deletebyid(26) |
28+----------------+
29| 25 |
30+----------------+
311 row in set (0.00 sec)
32
33MariaDB [hellodb]> DELIMITER //
34MariaDB [hellodb]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)
35 -> RETURNS SMALLINT
36 -> BEGIN
37 -> DECLARE a, b SMALLINT UNSIGNED;
38 -> SET a = x, b = y;
39 -> RETURN a+b;
40 -> END//
41Query OK, 0 rows affected (0.00 sec)
42
43MariaDB [hellodb]> DELIMITER ;
44MariaDB [hellodb]> select addtwonumber(10,20);
45+---------------------+
46| addtwonumber(10,20) |
47+---------------------+
48| 30 |
49+---------------------+
501 row in set (0.01 sec)
51
52
53MariaDB [hellodb]> set @a=10;
54Query OK, 0 rows affected (0.00 sec)
55
56MariaDB [hellodb]> select @a;
57+------+
58| @a |
59+------+
60| 10 |
61+------+
621 row in set (0.00 sec)
63
64MariaDB [hellodb]> select count(stuid) from students into @a;
65Query OK, 1 row affected (0.00 sec)
66
67MariaDB [hellodb]> select @a;
68+------+
69| @a |
70+------+
71| 25 |
72+------+
731 row in set (0.00 sec)
74
75
- 存储过程
类似mysql的脚本
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
提高了运行速度
同时降低网络数据传输量
存储过程与自定义函数的区别
存储过程实现的过程要复杂一些,而函数的针对性较强
存储过程可以有多个返回值,而自定义函数只有一个返回值
存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用
存储过程：存储过程保存在mysql.proc表中
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
2MariaDB [hellodb]> CREATE PROCEDURE showTime()
3 -> BEGIN
4 -> SELECT now();
5 -> END//
6Query OK, 0 rows affected (0.00 sec)
7
8MariaDB [hellodb]> delimiter ;
9MariaDB [hellodb]> CALL showTime;
10+---------------------+
11| now() |
12+---------------------+
13| 2019-08-04 17:12:22 |
14+---------------------+
151 row in set (0.00 sec)
16
17Query OK, 0 rows affected (0.00 sec)
18
19MariaDB [hellodb]> show procedure status\G
20*************************** 1. row ***************************
21 Db: hellodb
22 Name: showTime
23 Type: PROCEDURE
24 Definer: root@localhost
25 Modified: 2019-08-04 17:12:21
26 Created: 2019-08-04 17:12:21
27 Security_type: DEFINER
28 Comment:
29character_set_client: utf8mb4
30collation_connection: utf8mb4_general_ci
31 Database Collation: utf8_general_ci
321 row in set (0.01 sec)
33
34MariaDB [hellodb]> delimiter //
35MariaDB [hellodb]> CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
36 -> BEGIN
37 -> SELECT * FROM students WHERE stuid = uid;
38 -> END//
39Query OK, 0 rows affected (0.00 sec)
40
41MariaDB [hellodb]> delimiter ;
42MariaDB [hellodb]> call selectById(2);
43+-------+------------+-----+--------+---------+-----------+
44| StuID | Name | Age | Gender | ClassID | TeacherID |
45+-------+------------+-----+--------+---------+-----------+
46| 2 | Shi Potian | 22 | M | 1 | 7 |
47+-------+------------+-----+--------+---------+-----------+
481 row in set (0.00 sec)
49
50Query OK, 0 rows affected (0.00 sec)
51
52MariaDB [hellodb]> delimiter //
53MariaDB [hellodb]> CREATE PROCEDURE dorepeat(n INT)
54 -> BEGIN
55 -> SET @i = 0;
56 -> SET @sum = 0;
57 -> REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
58 -> UNTIL @i > n END REPEAT;
59 -> END//
60Query OK, 0 rows affected (0.00 sec)
61
62MariaDB [hellodb]> delimiter ;
63MariaDB [hellodb]> CALL dorepeat(100);
64Query OK, 0 rows affected (0.00 sec)
65
66MariaDB [hellodb]> SELECT @sum;
67+------+
68| @sum |
69+------+
70| 5050 |
71+------+
721 row in set (0.01 sec)
73
74MariaDB [hellodb]> delimiter //
75MariaDB [hellodb]> CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED)
76 -> BEGIN
77 -> DELETE FROM students WHERE stuid >= uid;
78 -> SELECT row_count() into num;
79 -> END//
80Query OK, 0 rows affected (0.00 sec)
81
82MariaDB [hellodb]> delimiter ;
83MariaDB [hellodb]> call deleteById(2,@Line);
84Query OK, 1 row affected (0.00 sec)
85
86MariaDB [hellodb]> SELECT @Line;
87+-------+
88| @Line |
89+-------+
90| 24 |
91+-------+
921 row in set (0.00 sec)
93
94MariaDB [hellodb]> select * from students;
95+-------+-------------+-----+--------+---------+-----------+
96| StuID | Name | Age | Gender | ClassID | TeacherID |
97+-------+-------------+-----+--------+---------+-----------+
98| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
99+-------+-------------+-----+--------+---------+-----------+
1001 row in set (0.00 sec)
101
102
- 触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
2 -> stu_id INT(11) NOT NULL AUTO_INCREMENT,
3 -> stu_name VARCHAR(255) DEFAULT NULL,
4 -> PRIMARY KEY (stu_id)
5 -> );
6Query OK, 0 rows affected (0.00 sec)
7
8MariaDB [hellodb]> CREATE TABLE student_count (
9 -> student_count INT(11) DEFAULT 0
10 -> );
11Query OK, 0 rows affected (0.00 sec)
12
13MariaDB [hellodb]> INSERT INTO student_count VALUES(0);
14Query OK, 1 row affected (0.03 sec)
15
16MariaDB [hellodb]> select * from student_info;
17Empty set (0.00 sec)
18
19MariaDB [hellodb]> select * from student_count;
20+---------------+
21| student_count |
22+---------------+
23| 0 |
24+---------------+
251 row in set (0.00 sec)
26
27MariaDB [hellodb]> CREATE TRIGGER trigger_student_count_insert
28 -> AFTER INSERT
29 -> ON student_info FOR EACH ROW
30 -> UPDATE student_count SET student_count=student_count+1;
31Query OK, 0 rows affected (0.00 sec)
32
33MariaDB [hellodb]> CREATE TRIGGER trigger_student_count_delete
34 -> AFTER DELETE
35 -> ON student_info FOR EACH ROW
36 -> UPDATE student_count SET student_count=student_count-1;
37Query OK, 0 rows affected (0.01 sec)
38
39MariaDB [hellodb]> insert student_info values(1,'a'),(2,'b'),(3,'c');
40Query OK, 3 rows affected (0.00 sec)
41Records: 3 Duplicates: 0 Warnings: 0
42
43MariaDB [hellodb]> select * from student_info;
44+--------+----------+
45| stu_id | stu_name |
46+--------+----------+
47| 1 | a |
48| 2 | b |
49| 3 | c |
50+--------+----------+
513 rows in set (0.00 sec)
52
53MariaDB [hellodb]> select * from student_count;
54+---------------+
55| student_count |
56+---------------+
57| 3 |
58+---------------+
591 row in set (0.00 sec)
60
61MariaDB [hellodb]> delete from student_info where stu_id=2 or stu_id=3;
62Query OK, 2 rows affected (0.01 sec)
63
64MariaDB [hellodb]> select * from student_info;
65+--------+----------+
66| stu_id | stu_name |
67+--------+----------+
68| 1 | a |
69+--------+----------+
701 row in set (0.00 sec)
71
72MariaDB [hellodb]> select * from student_count;
73+---------------+
74| student_count |
75+---------------+
76| 1 |
77+---------------+
781 row in set (0.00 sec)
79
80truncate 无针对命令无法触发
81
82MariaDB [hellodb]> show triggers\G
83*************************** 1. row ***************************
84 Trigger: trigger_student_count_insert
85 Event: INSERT
86 Table: student_info
87 Statement: UPDATE student_count SET student_count=student_count+1
88 Timing: AFTER
89 Created: NULL
90 sql_mode:
91 Definer: root@localhost
92character_set_client: utf8mb4
93collation_connection: utf8mb4_general_ci
94 Database Collation: utf8_general_ci
95*************************** 2. row ***************************
96 Trigger: trigger_student_count_delete
97 Event: DELETE
98 Table: student_info
99 Statement: UPDATE student_count SET student_count=student_count-1
100 Timing: AFTER
101 Created: NULL
102 sql_mode:
103 Definer: root@localhost
104character_set_client: utf8mb4
105collation_connection: utf8mb4_general_ci
106 Database Collation: utf8_general_ci
1072 rows in set (0.01 sec)
108
109MariaDB [hellodb]> use information_schema;
110Reading table information for completion of table and column names
111You can turn off this feature to get a quicker startup with -A
112
113Database changed
114MariaDB [information_schema]> select * from triggers where trigger_name='trigger_student_count_insert'\G
115*************************** 1. row ***************************
116 TRIGGER_CATALOG: def
117 TRIGGER_SCHEMA: hellodb
118 TRIGGER_NAME: trigger_student_count_insert
119 EVENT_MANIPULATION: INSERT
120 EVENT_OBJECT_CATALOG: def
121 EVENT_OBJECT_SCHEMA: hellodb
122 EVENT_OBJECT_TABLE: student_info
123 ACTION_ORDER: 0
124 ACTION_CONDITION: NULL
125 ACTION_STATEMENT: UPDATE student_count SET student_count=student_count+1
126 ACTION_ORIENTATION: ROW
127 ACTION_TIMING: AFTER
128ACTION_REFERENCE_OLD_TABLE: NULL
129ACTION_REFERENCE_NEW_TABLE: NULL
130 ACTION_REFERENCE_OLD_ROW: OLD
131 ACTION_REFERENCE_NEW_ROW: NEW
132 CREATED: NULL
133 SQL_MODE:
134 DEFINER: root@localhost
135 CHARACTER_SET_CLIENT: utf8mb4
136 COLLATION_CONNECTION: utf8mb4_general_ci
137 DATABASE_COLLATION: utf8_general_ci
1381 row in set (0.00 sec)
139
140删除基表后无触发器
141MariaDB [hellodb]> drop table student_info;
142Query OK, 0 rows affected (0.01 sec)
143
144MariaDB [hellodb]> show triggers\G
145Empty set (0.00 sec)
146
147
2、描述MySQL中有哪些存储引擎并描述各自的区别
https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html
MyISAM
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2 - MyISAM引擎特点
3 - 不支持事务
4 - 表级锁定
5 - 读写相互阻塞，写入不能读，读时不能写
6 - 只缓存索引
7 - 不支持外键约束
8 - 不支持聚簇索引
9 - 读取数据较快，占用资源较少
10 - 不支持MVCC（多版本并发控制机制）高并发
11 - 崩溃恢复性较差
12 - MySQL5.5.5前默认的数据库引擎
13 - MyISAM存储引擎适用场景
14 - 只读（或者写较少）、表较小（可以接受长时间进行修复操作）
15 - MyISAM引擎文件
16 - tbl_name.frm 表格式定义
17 - tbl_name.MYD 数据文件
18 - tbl_name.MYI 索引文件
19
20
InnoDB
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2 - InnoDB引擎特点
3 - 行级锁
4 - 支持事务，适合处理大量短期事务
5 - 读写阻塞与事务隔离级别相关
6 - 可缓存数据和索引
7 - 支持聚簇索引
8 - 崩溃恢复性更好
9 - 支持MVCC高并发
10 - 从MySQL5.5后支持全文索引
11 - 从MySQL5.5.5开始为默认的数据库引擎
12 - InnoDB数据库文件
13 - 所有InnoDB表的数据和索引放置于同一个表空间中
14 - 表空间文件：datadir定义的目录下
15 - 数据文件：ibddata1, ibddata2, ...
16 - 每个表单独使用一个表空间存储表的数据和索引
17 - 启用：innodb_file_per_table=ON
18 - 参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table
19 - ON (>= MariaDB 5.5)
20 - 两类文件放在数据库独立目录中
21 - 数据文件(存储数据和索引)：tb_name.ibd
22 - 表格式定义：tb_name.frm
23
24
- 查看mysql支持的存储引擎
show engines;
- 查看当前默认的存储引擎
show variables like ‘%storage_engine%’;
- 设置默认的存储引擎
vim /etc/my.conf
[mysqld]
default_storage_engine= InnoDB
- 查看库中所有表使用的存储引擎
show table status from db_name;
- 查看库中指定表的存储引擎
show table status like ’ tb_name ';
show create table tb_name;
- 设置表的存储引擎：
CREATE TABLE tb_name(… ) ENGINE=InnoDB;
ALTER TABLE tb_name ENGINE=InnoDB;
其他存储引擎
- 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、描述索引的工作原理并创建各种索引
索引
2
3
4
5
6
7
8
9
10
2 - 特殊的数据结构，定义在查找时作为查找条件的字段，在MySQL中又称为键key，索引通过存储引擎实现
3- 优点：
4 - 索引可以降低服务需要扫描的数据量，减少了IO次数
5 - 索引可以帮助服务器避免排序和使用临时表
6 - 索引可以帮助将随机IO转为顺序IO
7- 缺点：
8 - 占用额外空间，影响插入速度
9
10
-
红黑树
-
红黑树是平衡的二叉树：每个分支下都有两个节点
-
B-Tree
-
平衡树，多叉树，每个节点直接存放数据，最差的结果查询次数一样
-
B + Tree
-
每个根节点与分支节点只存放索引，所有的原始数据都存放在叶子节点，且存在数据指针（链表），可大大提高范围搜索的效率，所有记录的查询效率是一样的。
可以使用B+Tree索引的查询类型
2
3
4
5
6
2匹配最左前缀：即只是用索引的第一列
3匹配范围值；
4精确匹配某一列并范围匹配另一列
5
6
B+Tree索引的限制
2
3
4
5
2不能跳过索引中的列
3如果查询中某个列是为查询范围，那么其右侧的列都无法再使用索引
4
5
-
主键索引和二级索引
-
主键索引：
-
索引和数据都放在叶子节点上
- 二级索引：
-
索引和主键放在一起，先确定主键，再通过主键索引确定数据位置
-
稠密索引、稀疏索引：
-
是否索引了每一个数据项
-
简单索引：
-
对一个字段建立的索引
-
组合索引：
-
针对多个字段建立索引
-
索引优化策略：
-
独立地使用列：尽量避免其参与运算，独立的列索引指索引列不能是表达式的一部分，也不能是函数的参数，再where条件中，始终将索引列单独放在比较符号的一侧
- 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估
- 索引选择性：不重复的索引值和数据表的记录占总数的比值
- 多索引列：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
- 先择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
-
索引优化建议：
-
只要列中含有NULL值，就最好不要在此列设置索引，符合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该指定一个前缀长度
- 对于经常在where子句使用的列，最好设置索引
- 对于有多个列where或order by的子句，应该建立复合索引
- 对于like语句，以%或者-开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作，会导致全文索引
-
sql语句性能优化
-
查询时，能不用就不用，尽量写全字段名
-
大部分情况下连接效率远大于子查询，inner join
-
多表连接时，尽量小表驱动大表，即小表join大表
-
在有大量记录的表分页时使用limit
-
对于经常使用的查询，可以开启缓存
-
多使用explain和profile分析查询语句
-
查看慢查询日志，找出执行时间长的sql语句进行优化
-
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
2Query OK, 0 rows affected (0.01 sec)
3
4MariaDB [hellodb]> desc testlog;
5+-------+----------+------+-----+---------+----------------+
6| Field | Type | Null | Key | Default | Extra |
7+-------+----------+------+-----+---------+----------------+
8| id | int(11) | NO | PRI | NULL | auto_increment |
9| name | char(10) | YES | | NULL | |
10| age | int(11) | YES | | 20 | |
11+-------+----------+------+-----+---------+----------------+
123 rows in set (0.00 sec)
13
14MariaDB [hellodb]> create index index_age on testlog(age);
15Query OK, 0 rows affected (0.26 sec)
16Records: 0 Duplicates: 0 Warnings: 0
17
18MariaDB [hellodb]> create index index_name_age on testlog(name,age);
19Query OK, 0 rows affected (0.42 sec)
20Records: 0 Duplicates: 0 Warnings: 0
21
22MariaDB [hellodb]> show indexes from testlog\G
23*************************** 1. row ***************************
24 Table: testlog
25 Non_unique: 0
26 Key_name: PRIMARY
27 Seq_in_index: 1
28 Column_name: id
29 Collation: A
30 Cardinality: 100334
31 Sub_part: NULL
32 Packed: NULL
33 Null:
34 Index_type: BTREE
35 Comment:
36Index_comment:
37*************************** 2. row ***************************
38 Table: testlog
39 Non_unique: 1
40 Key_name: index_age
41 Seq_in_index: 1
42 Column_name: age
43 Collation: A
44 Cardinality: 200
45 Sub_part: NULL
46 Packed: NULL
47 Null: YES
48 Index_type: BTREE
49 Comment:
50Index_comment:
51*************************** 3. row ***************************
52 Table: testlog
53 Non_unique: 1
54 Key_name: index_name_age
55 Seq_in_index: 1
56 Column_name: name
57 Collation: A
58 Cardinality: 200
59 Sub_part: NULL
60 Packed: NULL
61 Null: YES
62 Index_type: BTREE
63 Comment:
64Index_comment:
65*************************** 4. row ***************************
66 Table: testlog
67 Non_unique: 1
68 Key_name: index_name_age
69 Seq_in_index: 2
70 Column_name: age
71 Collation: A
72 Cardinality: 200
73 Sub_part: NULL
74 Packed: NULL
75 Null: YES
76 Index_type: BTREE
77 Comment:
78Index_comment:
794 rows in set (0.00 sec)
80
81MariaDB [hellodb]> select * from testlog where age='100';
82+-----+---------+------+
83| id | name | age |
84+-----+---------+------+
85| 100 | wang100 | 100 |
86+-----+---------+------+
871 row in set (0.00 sec)
88
89MariaDB [hellodb]> explain select * from testlog where age='100';
90+------+-------------+---------+------+---------------+-----------+---------+-------+------+-------+
91| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
92+------+-------------+---------+------+---------------+-----------+---------+-------+------+-------+
93| 1 | SIMPLE | testlog | ref | index_age | index_age | 5 | const | 1 | |
94+------+-------------+---------+------+---------------+-----------+---------+-------+------+-------+
951 row in set (0.00 sec)
96
97MariaDB [hellodb]> select * from testlog where name='wang2';
98+----+-------+------+
99| id | name | age |
100+----+-------+------+
101| 2 | wang2 | 2 |
102+----+-------+------+
1031 row in set (0.01 sec)
104
105MariaDB [hellodb]> explain select * from testlog where name='wang2';
106+------+-------------+---------+------+----------------+----------------+---------+-------+------+--------------------------+
107| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
108+------+-------------+---------+------+----------------+----------------+---------+-------+------+--------------------------+
109| 1 | SIMPLE | testlog | ref | index_name_age | index_name_age | 31 | const | 1 | Using where; Using index |
110+------+-------------+---------+------+----------------+----------------+---------+-------+------+--------------------------+
1111 row in set (0.00 sec)
112
113MariaDB [hellodb]> select * from testlog where id = '1000';
114+------+----------+------+
115| id | name | age |
116+------+----------+------+
117| 1000 | wang1000 | 1000 |
118+------+----------+------+
1191 row in set (0.00 sec)
120
121MariaDB [hellodb]> explain select * from testlog where id = '1000';
122+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
123| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
124+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
125| 1 | SIMPLE | testlog | const | PRIMARY | PRIMARY | 4 | const | 1 | |
126+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1271 row in set (0.00 sec)
128
129MariaDB [hellodb]> show index_statistics;
130+--------------+------------+----------------+-----------+
131| Table_schema | Table_name | Index_name | Rows_read |
132+--------------+------------+----------------+-----------+
133| hellodb | testlog | PRIMARY | 2 |
134| hellodb | testlog | index_age | 1 |
135| hellodb | testlog | index_name_age | 1 |
136+--------------+------------+----------------+-----------+
1373 rows in set (0.00 sec)
138
139
4、总结binlog备份方法，用脚本实现每小时备份binlog
-
binlog：又称为归档日志
-
记录导致数据变化或潜在导致数据改变的SQL语句，可用于数据恢复
- 记录已提交的日志
- 不依赖于存储引擎类型
- 通过“重放”日志文件中的时间来生成数据副本
-
三种记录格式
-
基于语句型，默认模式statement，用于记录命令本身
- 基于行记录：row，用于记录数据，日志量较大
- 混合模式：mixed，让系统自行判定基于哪种方式进行
强烈推荐row，虽然占用空间大，但是求稳。
二进制日志和数据库分开存放，防止数据库奔溃影响或者误删二进制日志。
2
3
4
5
6
7
8
9
10
11
12
2#!/bin/bash
3logdir=/data/binlog
4user=root
5userpasswd=''
6backdir=/data/backup/
7/usr/bin/mysqladmin -u$user -p$userpasswd flush-logs
8mysqldump -A --single-transaction -F --master-data=2 | gzip > $backdir/all_back_`date +%F-%T`.sql.gz
9[root@centos7 ~]# crontab -e
100 * * * * /bin/bash /root/mysqlbak.sh
11
12
