MySQL

安装

mac下安装mysql 5.7，参考：https://gist.github.com/operatino/392614486ce4421063b9dece4dfe6c21

brew info mysql@5.7
brew install mysql@5.7

brew tap homebrew/services
brew services start mysql@5.7
brew services list
brew link mysql@5.7 --force
mysql -V

mysqladmin -u root password 'yourpassword'

启动和停止

# 启动
mysql.server start

# 停止
mysql.server stop

修改密码

mysqladmin -uroot password "new_password"

登录

mysql -uroot -p

MySQL 索引

B+ 树索引
最左匹配原则

1. 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
2. = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
3. 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。
————————————————
版权声明：本文为CSDN博主“许大侠0610”的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013568373/article/details/93891531

支持范围查询
支持模糊查询
MySQL中的B+树索引可分为聚集索引和非聚集索引：

  • 聚簇索引：使用表的主键（如无，系统会创建隐含的主键）为索引，叶子节点存储所有数据；
  • 非聚簇索引：使用主键以外的列值构建索引，叶子节点存放主键，需要通过主键用聚集索引再查询出数据（即回表的过程）。

InnoDB 中索引即数据，完整的记录存放在主键索引的叶子节点；而 MyISAM 中索引和数据分离，主键索引的叶子节点只存放数据的地址，需要通过地址去数据文件中加载。可以说，MyISAM 中的索引都是非聚簇索引。

Hash 索引
Memory引擎，InnoDB引擎的自适应Hash索引功能（当某些索引值频繁被使用时，会在B+树的基础上建立hash索引）
只能用于对等比较
不能使用范围查询
不支持联合索引最左侧原则（因为Hash 索引在计算 Hash 值的时候是将索引键合并后再一起计算 Hash 值，所以不会针对每个索引单独计算 Hash 值）
不支持 ORDER BY 排序
不能进行模糊查询 LIKE
当字段有大量重复值时效率低（因为存在大量hash碰撞）

全文索引
5.6 以前只有 MyISAM 支持全文索引, 5.6 开始InnoDB支持全文索引, 5.7.6 开始通过ngram插件支持中文全文索引
全文索引有三种检索方式：

  1. 自然语言全文检索 IN NATURAL LANGUAGE MODE ，按关键词出现的次数排序，超过 50% 的文档里有的词不会被索引
  2. 布尔全文检索 IN BOOLEAN MODE，通过使用前缀修饰符来定制化检索，比如：
    + 必须包含
- 必须排除
> 增加相关性
< 减少相关性
* 通配符
~ 否定该词对相关性的影响
“” 不可拆字
() 子表达式
  3. 查询扩展 WITH QUERY EXPANSION

InnoDB：事务安全型。行锁级。查询行数时会扫描整个表。支持外键。

Memory：临时表。

MyISAM：默认引擎，支持全文本搜索，不支持事务。表锁级。查询行数时不会扫描整个表，因为会记录行数。不支持外键。查询速度一般比 InnoDB 快，因为：

  • MyISAM 不需要缓存数据块，而只缓存索引
  • InnoDB 索引需要先查询块，再到数据行；而 MyISAM 直接记录文件offset，定位快；
  • InnoDB 需要维护 MVCC。

InnoDB 使用 Buffer Pool 缓存数据页，Buffer Pool 是一个 LRU 的内存缓存，默认使用 3/8 的内存放旧页，数据页插入位置为新旧页的交界处。

视图是（VIEW）SQL SELECT语句的封装，是算法的打包，可以用来简化和重用SQL语句，或者重新格式化显示结果，或保护基础数据（提供视图的权限而不是基础表的权限）。视图可以视为普通表用于检索数据，大多数视图不允许修改数据。

存储过程（PROCEDURE）是SQL语句的封装，不仅包含 SELECT 还可以包含对数据的修改。其实就是一个函数，使用 CALL 调用，函数的参数使用 IN 关键字指定，返回值使用 OUT 关键字指定变量（例如：@total），并将结果写入（例如：SELECT total INTO ototal），随后调用存储过程后可以通过变量名获取数据（例如：SELECT @total）。

存储过程还可以使用 DECLARE 定义局部变量，使用 IF...THEN...END IF 进行流程控制。

触发器（TRIGGER）用于对表执行特定的语句之前或之后触发执行的语句。目前仅支持表，不支持视图，且仅支持 DELETE 、INSERT、UPDATE 语句。每个表每个事件仅支持一个触发器，因此单个表最多支持 6 个触发器（每条 DELETE、INSERT和UPDATE的之前之后）。

如果 BEFORE 触发器执行失败，MySQL 不会支持 AFTER 触发器。

在INSERT触发器代码内，可引用一个名为NEW的虚拟表(其值可被更新)，访问被插入的行。以下触发器在插入新的订单时返回自动生成的新订单号。

CREATE TRIGGER neworder AFTER INSERT ON orders 
FOR EACH ROW SELECT NEW.order_num;

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
	INSERT INTO archive_orders(order_num, order_date, cust_id)
	VALUE(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

UPDATE 可以净化数据，例如：

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

事务处理，将一组操作当作整体，要么都执行完成，要么都不执行（回退），不存在中间状态。

  • 事务（transaction）：指一组SQL语句。
  • 回退（rollback）：撤销指定SQL语句的过程。包括 INSERT、UPDATE和DELETE，不包括CREATE和DROP。
  • 提交（commit）：指将未存储的SQL语句结果写入数据库表；
  • 保留点（savepoint）：指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（达到部分回退的目的）。
# 开始事务
START TRANSACTION;
DELETE FROM orders;
# 回退
ROLLBACK;
DELETE FROM orders WHERE order_id=1002;
# 增加保留点
SAVEPOINT delete1;
DELETE FROM orderitems WHERE order_id=1002;
# 提交
COMMIT;

安全管理

# 创建用户
CREATE USER ben IDENTIFIED BY '!#a3dsf';
# 重命名用户
RENAME USER ben TO ken;
# 删除用户
DROP USER ken;
# 更改密码
SET PASSWORD FOR Ken = Password('1234#abc');
# 查看赋予的权限
SHOW GRANTS FOR ken;
# 赋予某库所有表的查看和插入的权限
GRANT SELECT, INSERT ON mall_db.* TO ken;
# 撤销赋予某库所有表的插入权限
REVOKE INSERT ON mall_db.* TO ken;

数据库维护

# 刷新未写数据
FLUSH TABLES;
# 备份表到文件
SELECT * FROM mall_db.orders INTO OUTFILE 'C://tmp/orders.txt'

备份并恢复整个数据库：

# 备份并压缩
mysqldump -u [username] -p [dbname] | gzip > filename.sql.gz
# 解压缩并恢复
gunzip -c filename.sql.gz | mysql -u [username] -p [dbname]

分析表

# 检查表键和索引，会对表加只读锁，不允许新增数据
ANALYZE [LOCAL] TABLE orders;
# 当删除大量数据时可以使用以下命令回收所用的空间
OPTIMIZE TABLE orders;

分析日志：

  1. hostname.err 错误日志
  2. hostname.log 查询日志
  3. hostname-bin 二进制日志，更新数据的命令
  4. hostname-slow.log 慢查询日志

MySQL 5.7 起可以安装 X Plugin 来支持文档对象存储，MySQL 8 起默认安装了此插件。

性能提升：

  1. 使用 SHOW VARIABLES; 查看设置的内存分配和缓冲区大小，使用 SHOW STATUS; 查看数据库状态。
  2. MySQL 是一个多用户多线程的DBMS，某个任务执行缓慢会导致整体性能下降。通过 SHOW PROCESSLIST; 显示所有活动进程，并通过 KILL 命令终结某个特定进程。
  3. 使用多种方式（联结、并、子查询等）编写 SELECT 语句并使用 EXPLAIN 测试其中的性能。
  4. 一般来说，存储过程比单条执行语句快。
  5. 不要检索比需要等待多的数据（即不要使用 SELECT *）。
  6. 仅对经常被搜索到的数据建立索引。
  7. 最好使用 FULLTEXT 而不是 LIKE。
  8. 导入数据时，关闭自动提交，删除索引。在导入完成后再重建索引。

备注

  1. 默认的端口号为 3306
  2. Mac下简单易用的MySql管理工具：Sequel Pro

QA

Sequel Pro and MySQL connection failed

问题描述：

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 2): image not found

解决方案：

This is because Sequel Pro is not ready yet for a new kind of user login, as the error states: there is no driver. Quick fix for non-homebrew installs:

Apple Logo > System Preferences > MySQL > Initialize Database, then type your new password and select 'Use legacy password'

After restart you should be able to connect. Do it only on fresh installs, because you may lost your db tables otherwise.

posted @ 2019-08-11 11:52  東籬老農  阅读(127)  评论(0)  编辑  收藏  举报