MySQL进阶

目录

一、索引

1. 为啥使用索引以及索引的作用

使用索引就是为了提高查询效率

2. 索引的本质

索引本质上是一个特殊的文件

3. 索引的底层原理

B+树

4. 索引的种类

  1. 主键索引：primary key

    特点：加快查找+不能重复+不能为空

  2. 唯一索引：unique(字段名)

    • 联合唯一索引：unique(字段名1，字段名2)

    特点：加快查找+不能重复

  3. 普通索引：index(字段名)

    • 联合索引：index(字段名1，字段名2)

    特点：加快查找

5. 索引的创建与删除

5.1 创建主键索引

  1. 在建表的时候创建：

    create table 表名(
	id int auto_increment primary key,
    name varchar(32)
)charset=utf8;

  2. 在已有的一张表上添加：

    1. alter table 表名 change 旧字段名 新字段名 类型约束 primary key;

2. alter table 表名 add primary key (字段名);

5.2 删除主键索引

alter table 表名 drop primary key;

5.3 创建唯一索引

  1. 在建表的时候创建：

    # 1.1 直接在创建字段语句后面加 unique，此时不用写索引名，默认索引名为字段名。
create table 表名(
	id int auto_increment primary key,
    name varchar(32) not null default '' unique
)charset=utf8;

# 1.2 在创建完所有字段后，再在之后添加唯一索引。
create table 表名(
	id int auto_increment primary key,
    name varcher(32) not null default '',
    unique u_name (name)
)charset=utf8;

  2. 在一张已有的表上添加唯一索引

    1. create unique index 索引名 on 表名 (字段名);

2. alter table 表名 add unique index 索引名 (字段名)；

3. alter table 表名 change 旧字段名 新字段名 类型约束 unique;

5.4 删除唯一索引

alter table t2 drop index 唯一索引名;

5.5 创建普通索引

  1. 在建表的时候创建：

    create table 表名(
	id int auto_increment primary key,
    name varcher(32) not null default '',
    index u_name (name)
)charset=utf8;

  2. 在一张已有的表上创建普通索引

    1. create index 索引名 on 表名 (字段名);

2. alter table 表名 add index 索引名 (字段名)；

5.6 删除普通索引

alter table t3 drop index 索引名；

6. 索引的优缺点

通过观察*.ibd文件(MySQL表文件)可知：

  1. 优点：索引加快了查询速度
  2. 缺点：加了索引后，文件的变大了很多，会占用更多的磁盘空间

7. 不会命中索引的情况

  1. 不能在SQL语句中，进行四则运算，不然会降低SQL的查询效率

  2. 使用函数也会命不中索引

    select * from t1 where reverse(id) = 123;

  3. 类型不一致

    如果列是字符串类型，传入条件是必须用引号引起来，不然可能会无法命中索引
    select * from t1 where name = tbw;

  4. order by

    排序条件为索引，则select字段必须也是索引字段，否则无法命中(order by)

    select name from t1 order by email desc;

    当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢

    select email from s1 order by email desc;

    特别的，如果对主键排序，则还是速度很快：

    select * from tb1 order by id desc;

  5. count(1)或count(列)代替count(*)在mysql中没有差别了

  6. 组合索引最左前缀

    如果组合索引为：ix_name_email (name,email)：

    • where name = 值1 and email = 值2; 命中索引
    • where name = 值1; 命中索引
    • where email = 值2; 未命中索引

    如果组合索引为：ix_name_email_age (name, email, age):

    • where name = 值1 and email = 值2 and age = 值3; 命中索引
    • where name = 值1 and email = 值2; 命中索引
    • where name = 值1 and age = 值3; 命中索引
    • where email = 值2 and age = 值3; 未命中索引
    • where age = 值3; 未命中索引

    结论：只要索引中有组合索引的最左索引字段，就可以命中索引（善用explain）

二、慢查询日志

1. 查看慢sql的相关变量

mysql> show variables like '%slow%'
	-> ;
+---------------------------+-----------------------------------------------+
| Variable_name             | Value                                         |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF                                           |
| log_slow_slave_statements | OFF                                           |
| slow_launch_time          | 2                                             |
| slow_query_log            | OFF   ### 默认关闭慢SQl查询日志， on              |
| slow_query_log_file       | D:mysql-5.7.28dataDESKTOP-910UNQE-slow.log | ## 慢SQL记录的位置
+---------------------------+-----------------------------------------------+
5 rows in set, 1 warning (0.08 sec)
				
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name                                            | Value     |
+----------------------------------------------------------+-----------+
| long_query_time                                          | 10.000000 |

2. 配置慢sql的变量

set global 变量名 = 值

set global slow_query_log = on;

set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log";

set global long_query_time=1;

三、事物

通俗的说，事物指一组操作，要么都执行成功，要么都执行失败。

1. 事物的使用方法

start transaction；
	sql语句
commit(提交)/rollback(回滚到事物开始的地方)

2. 事物的特性

  1. 原子性(Atomicity)：原子意为最小的粒子，即不能再分的事务，要么全部执行，要么全部取消。
  2. 一致性(Consistency)：指事务发生前和发生后，数据的总额依然匹配。
  3. 隔离性(Isolation)：简单点说，某个事务的操作对其他事务是不可见的。
  4. 持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤销，只能通过“补偿性事务”来抵消之前的错误。

3. 储存引擎

Innodb：支持事务，支持行锁

MyISAM：不支持事务，只支持表锁

四、视图

1. 增加视图

create view 视图名 as SQL语句；

2. 删除视图

drop view 视图名；

五、触发器

当两个操作同时发生时，其中一个操作可以触发另一个操作，如果该操作一直不完成，则另一个被触发操作则永远也不能执行。

1. 增加触发器

# 当向tb1表中添加一条数据的同时， 向tb2表添加一条数据
delimiter //

create trigger tri_before_insert_tb1 before insert on t2 for each row begin
	SQL语句：insert into t3(name) values ('aa');
end //

delimiter;

2. 查看触发器

show triggersG

3. 删除触发器

drop trigger 触发器名；

六、存储过程

像一个SQL函数

1. 创建过程

delimiter //

create procedure p1()
begin
	SQL语句：select * from user where id = 2;
end // 
delimiter

2. 使用这个过程

call p1()

3. 删除过程

drop procedure p1;

七、函数

  1. char_length(str)：返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
  2. concat(str1,str2....)：字符串拼接，如有任何一个参数为NULL ，则返回值为 NULL。
  3. format(X,D)：将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。(例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000')
  4. instr(str,substr)：返回字符串 str 中子字符串的第一个出现位置。
  5. left(str,len)：返回字符串str 从开始的len位置的子序列字符。
  6. lower(str)：变小写
  7. upper(str)：变大写
  8. ltrim(str)：返回字符串 str ，其引导空格字符被删除。
  9. rtrim(str)：返回字符串 str ，结尾空格字符被删去。
  10. substring(str,pos,len)：获取字符串子序列
  11. locate(substr,str,pos)：获取子序列索引位置
  12. repeat(str,count)：返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。
  13. replace(str,from_str,to_str)：返回字符串str 以及所有被字符串to_str替代的字符串from_str。
  14. reverse(str)：返回字符串 str ，顺序和字符顺序相反。
  15. right(str,len)：从字符串str 开始，返回从后边开始len个字符组成的子序列

八、备份

1. 用法

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 表名,  表名,.... > aaa.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

2. 重新导入

mysql> source (备份地址)D:/test3.sql;
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/bowendown/p/11788134.html