MySQL补充知识

YhMjQx

已于 2024-08-24 23:30:35 修改

阅读量751

点赞数 11

分类专栏： MySQL和Redis 文章标签：数据库 mysql sql

于 2024-08-24 23:21:48 首次发布

本文链接：https://blog.csdn.net/YhMjQx/article/details/141507113

版权

MySQL和Redis 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

MySQL补充知识

索引

索引的作用：Index，目录，帮助数据库提升性能，避免全表扫描导致速度极慢，数据量越大越需要索引。
在数据库中，如何利用索引实现快速查询

一、MySQL索引分类

MySQL索引主要有两种结构：B+Tree索引和Hash索引

Hash索引：

MySQL中，只有Memory（Memory表只存在于内存中，断电会消失，适用于临时表）存储引擎显示只是Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。Hash索引把数据的索引以Hash形式组织起来，因此当查找某一条记录的时候，速度非常快，当时因为是hash结构，每个键只对应一个值，而且是散列的分布方式。所以他并不支持范围查找和排序等功能

B+Tree索引

B+Tree从MySQL5.5之后，就是使用最频繁的一个索引数据结构，是innoDB和MyISAM存储引擎模式的索引类型，相对Hash索引，B+Tree在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，索引他更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作

简要介绍一下原理：
- 磁盘1中的p1指向小于28的内容
- 磁盘2中的p1指向小于10的数据，p2指向10-17之间的数据，p3指向17-28之间的数据
- ...
- 就通过这样的索引来达到快速查找的功能

二、常见的索引类型

PRIMARY KEY（主键索引）ALTER TABLE table_name ADD PRIMARY (col)
UNIQUE(唯一索引) ALTER TABLE table_name ADD UNIQUE(col)

创建索引前：

创建唯一索引后：

使用关键字 EXPLAIN 查看查询语句的查询计划

key：索引类型

rows：表示查询的行数

使用 LIKE 或聚合函数时，无法使用索引

创建索引时，需要花费计算资源和存储资源。所以，索引不适用于频繁更新和插入数据的表，或者说：更新次数多于查询次数时没有必要使用索引

INDEX(普通索引) ALTER TABLE table_name ADD INDEX index_name(col)
FULLTEXT(全文索引) ALTER TABLE table_name ADD FULLTEXT(col)
组合索引 ALTER TABLE table_name ADD INDEX index_name (col1,col2,col3)

其他用法：

利用SQL语句写文件

前提是需要对目录有权限，而且，要写的文件不能已经存在

 select "string" into outfile ‘/tmp/xxx.txt’;

创建临时表

是保存在内存中的表，执行速度很快。断开本次连接，就会消失，无法使用手误 tables 查看到该表

 CREATE TEMPORARY TABLE table_name(
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(20),
     age TINYINT
 );

通过SQL语句来创建一张表

利用select语句创建一张表

配合关键字 create table

 create table table_name select customerid,name,sex,phone from customer where customerid<10;

创建好了之后还可以使用关键字 INSERT INTO 对表插入数据

 INSERT INTO table_name select customerid,name,sex,phone from customer where customerid=10;
 #相当于将customer表中customerid=10的数据再插入一遍

我们还可以利用此方式方式对表进行备份

 create table customer_bak select * from customer;

备份

当然，备份的方式有很多

上面所说的使用 create table table_name select ...
使用工具自带的备份

锁住全部表：在备份期间不允许修改，只允许查询

备份的本质就是备份建表和插入语句等这些最本质的东西

而且，我们还可以将备份后的文件提取出SQL

实际上就是这些语句

通过最本质的备份指令进行备份操作

进入系统终端操作

 #备份数据库到 /opt 目录
 mysqldump -h127.0.0.1 -uroot -ppass book > d:/backupfile.sql
 #压缩之后备份数据库到 /opt 目录
 mysqldump -h127.0.0.1 -uroot -ppass book | gzip > d:/backupfile.sql.gz
 #同时备份多个数据库
 mysqldump -h127.0.0.1 -uroot -ppass --databases book school > multibackupfile.sql
 #以上是备份指令
 #以下是还原数据库指令，首先需要创建一个数据库（可以先手工创建好一个数据库，也可以将创建数据库的指令放在被导入的文件中，则会自动创建数据库）
 mysql -h127.0.0.1 -uroot -ppass book < backupfile.sql
 #针对压缩文件进行导入
 gunzip < backupfile.sql.gz | mysql -h127.0.0.1 -uroot -ppass book

备份book数据库

还原book数据库

权限

任何情况下，先授予最小权限，再根据实际需要添加权限

 #%表示所有ip
 grant all *.* 'woniu'@'%' identify by '123456';  #给woniu以所有数据库的所有权限
 flush privileges;  #刷新权限
 
 revoke  权限1,权限2,... on 数据库名.对象名 from '用户名'@'允许其登录的地址';  #移除权限
 eg:
 revoke INSERT on *.* from 'woniu'@'%';
 flush privileges;