1、描述视图,存储过程,函数,触发器分别是什么并实践
\G后面不能再加分号;，因为\G在功能上等同于;，如果加了分号，那么就是;;(2个分号)，SQL语法错误
- 视图 VIEW,虚表，保存有实表的查询结果
视图中的数据事实上存储于“基表”中，因此，其修改操作也会针对基表实现；其修改操作受基表限制。
修改视图也会修改基表中的内容。
MariaDB [hellodb]> select * from 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.01 sec)
MariaDB [hellodb]> create view v_students as select stuid,name,age from students;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
| v_students |
+-------------------+
8 rows in set (0.01 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.01 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`
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
1 row in set (0.00 sec)
MariaDB [hellodb]> insert v_students values(26,'jack',30);
Query OK, 1 row affected (0.01 sec)
MariaDB [hellodb]> select * from students where stuid=26;
+-------+------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+------+-----+--------+---------+-----------+
| 26 | jack | 30 | F | NULL | NULL |
+-------+------+-----+--------+---------+-----------+
1 row in set (0.00 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 |
| 7 | Xi Ren | 19 |
| 8 | Lin Daiyu | 17 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 13 | Tian Boguang | 33 |
| 14 | Lu Wushuang | 17 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
| 26 | jack | 30 |
+-------+---------------+-----+
26 rows in set (0.00 sec)
- 函数
系统函数:https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
自定义函数 (user-defined function UDF)，保存在mysql.proc表中
说明：
参数可以有多个,也可以没有参数
必须有且只有一个返回值
最好不要有空值，空值需用is判断
MariaDB [hellodb]> select count(*) from students;
+----------+
| count(*) |
+----------+
| 26 |
+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(stuid) from students;
+--------------+
| count(stuid) |
+--------------+
| 26 |
+--------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> create function simplefun() returns varchar(20) return "Hello World!";
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> select simplefun();
+--------------+
| simplefun() |
+--------------+
| Hello World! |
+--------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show function status\G
*************************** 1. row ***************************
Db: hellodb
Name: simplefun
Type: FUNCTION
Definer: root@localhost
Modified: 2019-08-04 16:40:13
Created: 2019-08-04 16:40:13
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)
MariaDB [hellodb]> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [db1]> show function status\G
*************************** 1. row ***************************
Db: hellodb
Name: simplefun
Type: FUNCTION
Definer: root@localhost
Modified: 2019-08-04 16:40:13
Created: 2019-08-04 16:40:13
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [db1]> select simplefun();
ERROR 1305 (42000): FUNCTION db1.simplefun does not exist
MariaDB [db1]> select hellodb.simplefun();
+---------------------+
| hellodb.simplefun() |
+---------------------+
| Hello World! |
+---------------------+
1 row in set (0.00 sec)
MariaDB [db1]> show create function hellodb.simplefun\G
*************************** 1. row ***************************
Function: simplefun
sql_mode:
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `simplefun`() RETURNS varchar(20) CHARSET utf8
return "Hello World!"
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.00 sec)
MariaDB [db1]> drop function hellodb.simplefun;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show create function hellodb.simplefun\G
ERROR 1305 (42000): FUNCTION simplefun does not exist
有参数UDF
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20)
-> 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-08-04 16:52:05
Created: 2019-08-04 16:52:05
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 sec)
MariaDB [hellodb]> select deletebyid(26);
+----------------+
| deletebyid(26) |
+----------------+
| 25 |
+----------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE FUNCTION addTwoNumber(x SMALLINT UNSIGNED, Y SMALLINT UNSIGNED)
-> RETURNS SMALLINT
-> BEGIN
-> DECLARE a, b SMALLINT UNSIGNED;
-> SET a = x, b = y;
-> RETURN a+b;
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> DELIMITER ;
MariaDB [hellodb]> select addtwonumber(10,20);
+---------------------+
| addtwonumber(10,20) |
+---------------------+
| 30 |
+---------------------+
1 row in set (0.01 sec)
MariaDB [hellodb]> set @a=10;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> select @a;
+------+
| @a |
+------+
| 10 |
+------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select count(stuid) from students into @a;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select @a;
+------+
| @a |
+------+
| 25 |
+------+
1 row in set (0.00 sec)
- 存储过程
类似mysql的脚本
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程
提高了运行速度
同时降低网络数据传输量
存储过程与自定义函数的区别
存储过程实现的过程要复杂一些,而函数的针对性较强
存储过程可以有多个返回值,而自定义函数只有一个返回值
存储过程一般独立的来执行,而函数往往是作为其他SQL语句的一部分来使用
存储过程：存储过程保存在mysql.proc表中
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-08-04 17:12:22 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> show procedure status\G
*************************** 1. row ***************************
Db: hellodb
Name: showTime
Type: PROCEDURE
Definer: root@localhost
Modified: 2019-08-04 17:12:21
Created: 2019-08-04 17:12:21
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
1 row in set (0.01 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 dorepeat(n INT)
-> BEGIN
-> SET @i = 0;
-> SET @sum = 0;
-> REPEAT SET @sum = @sum+@i; SET @i = @i + 1;
-> UNTIL @i > n END REPEAT;
-> END//
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> delimiter ;
MariaDB [hellodb]> CALL dorepeat(100);
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SELECT @sum;
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.01 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(2,@Line);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> SELECT @Line;
+-------+
| @Line |
+-------+
| 24 |
+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
- 触发器
触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行
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.03 sec)
MariaDB [hellodb]> select * from student_info;
Empty set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 0 |
+---------------+
1 row in set (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)
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.01 sec)
MariaDB [hellodb]> insert student_info values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | a |
| 2 | b |
| 3 | c |
+--------+----------+
3 rows in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> delete from student_info where stu_id=2 or stu_id=3;
Query OK, 2 rows affected (0.01 sec)
MariaDB [hellodb]> select * from student_info;
+--------+----------+
| stu_id | stu_name |
+--------+----------+
| 1 | a |
+--------+----------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from student_count;
+---------------+
| student_count |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
truncate 无针对命令无法触发
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: utf8mb4
collation_connection: utf8mb4_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: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.01 sec)
MariaDB [hellodb]> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
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: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ci
DATABASE_COLLATION: utf8_general_ci
1 row in set (0.00 sec)
删除基表后无触发器
MariaDB [hellodb]> drop table student_info;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> show triggers\G
Empty set (0.00 sec)
2、描述MySQL中有哪些存储引擎并描述各自的区别
https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/storage-engines.html
MyISAM
- MyISAM引擎
- MyISAM引擎特点
- 不支持事务
- 表级锁定
- 读写相互阻塞，写入不能读，读时不能写
- 只缓存索引
- 不支持外键约束
- 不支持聚簇索引
- 读取数据较快，占用资源较少
- 不支持MVCC（多版本并发控制机制）高并发
- 崩溃恢复性较差
- MySQL5.5.5前默认的数据库引擎
- MyISAM存储引擎适用场景
- 只读（或者写较少）、表较小（可以接受长时间进行修复操作）
- MyISAM引擎文件
- tbl_name.frm 表格式定义
- tbl_name.MYD 数据文件
- tbl_name.MYI 索引文件
InnoDB
- InnoDB引擎
- InnoDB引擎特点
- 行级锁
- 支持事务，适合处理大量短期事务
- 读写阻塞与事务隔离级别相关
- 可缓存数据和索引
- 支持聚簇索引
- 崩溃恢复性更好
- 支持MVCC高并发
- 从MySQL5.5后支持全文索引
- 从MySQL5.5.5开始为默认的数据库引擎
- InnoDB数据库文件
- 所有InnoDB表的数据和索引放置于同一个表空间中
- 表空间文件：datadir定义的目录下
- 数据文件：ibddata1, ibddata2, ...
- 每个表单独使用一个表空间存储表的数据和索引
- 启用：innodb_file_per_table=ON
- 参看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table
- ON (>= MariaDB 5.5)
- 两类文件放在数据库独立目录中
- 数据文件(存储数据和索引)：tb_name.ibd
- 表格式定义：tb_name.frm
- 查看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、描述索引的工作原理并创建各种索引
索引
- 索引：适用于数据量大，读取多写入少的场景
- 特殊的数据结构，定义在查找时作为查找条件的字段，在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;
Query OK, 0 rows affected (0.01 sec)
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_age on testlog(age);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hellodb]> create index index_name_age on testlog(name,age);
Query OK, 0 rows affected (0.42 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_age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 200
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.00 sec)
MariaDB [hellodb]> explain select * from testlog where age='100';
+------+-------------+---------+------+---------------+-----------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+-----------+---------+-------+------+-------+
| 1 | SIMPLE | testlog | ref | index_age | index_age | 5 | const | 1 | |
+------+-------------+---------+------+---------------+-----------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from testlog where name='wang2';
+----+-------+------+
| id | name | age |
+----+-------+------+
| 2 | wang2 | 2 |
+----+-------+------+
1 row in set (0.01 sec)
MariaDB [hellodb]> explain select * from testlog where name='wang2';
+------+-------------+---------+------+----------------+----------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+----------------+----------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | testlog | ref | index_name_age | index_name_age | 31 | const | 1 | Using where; Using index |
+------+-------------+---------+------+----------------+----------------+---------+-------+------+--------------------------+
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]> explain select * from testlog where id = '1000';
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | testlog | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> show index_statistics;
+--------------+------------+----------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+------------+----------------+-----------+
| hellodb | testlog | PRIMARY | 2 |
| hellodb | testlog | index_age | 1 |
| hellodb | testlog | index_name_age | 1 |
+--------------+------------+----------------+-----------+
3 rows in set (0.00 sec)
4、总结binlog备份方法，用脚本实现每小时备份binlog
-
binlog：又称为归档日志
- 记录导致数据变化或潜在导致数据改变的SQL语句，可用于数据恢复
- 记录已提交的日志
- 不依赖于存储引擎类型
- 通过“重放”日志文件中的时间来生成数据副本
-
三种记录格式
- 基于语句型，默认模式statement，用于记录命令本身
- 基于行记录：row，用于记录数据，日志量较大
- 混合模式：mixed，让系统自行判定基于哪种方式进行
强烈推荐row，虽然占用空间大，但是求稳。
二进制日志和数据库分开存放，防止数据库奔溃影响或者误删二进制日志。
[root@centos7 ~]# vim mysqlbak.sh
#!/bin/bash
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@centos7 ~]# crontab -e
0 * * * * /bin/bash /root/mysqlbak.sh
123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
