mysql使用

发表于 更新于

索引原理

MySQL使用的是B+树作为索引的数据结构

B树是一个分支内按顺序存放多个节点数据的数据结构；而B+树在此基础上，在分支内只存储索引，只在叶子节点存储数据（这样每一层可以存储更多索引，减少层数），并且在叶节点之间用指针互相连接，提高访问效率。

引擎

MyISAM，B+树存储的Data就是数据的地址（非聚集索引、稀疏索引）
InnoDB，直接存储数据（聚集索引）

为什么InnoDB建议每张表必须建立主键，并用自增整型？

ibd必须用B+树索引，而整型是天然的索引；否则ibd会自己维护一个唯一id行（隐藏的主键）。
因此UUID比较效率会比整型更低。
而自增则根本避免了重复，并且只在一端变化，已经有的数据无需做修改，减少了维持有序的成本。
如果不自增，而是随机添加，那么新增的数很可能会触发分裂、平衡，造成冗余索引。

B+树如何支持范围查询

Hash结构的索引，不支持范围查询；而B+树只用找到两端，然后顺着指针拿到所有节点就好了（叶节点是双指针连接的，并且有序）。

联合主键索引，为什么是最左前缀原则？

最左前缀原则：不能跳过左边的索引，必须从最左边索引开始，逐步增加条件。
因为联合主键索引底层的B+树就是按照主键顺序排序的，会从左到右进行比较；如果跳过了左边的主键，那就找不到了，因为第二个主键不一定是排好序的！。
首先按照第一个主键排序，然后按照第二个主键排序。在同一个主键内，二级主键是有序的，但是跳出这个圈，就是无序的。

索引优化原则

explain性能分析

explain extended: rows∗filtered/100rows * filtered/100可以估算出将要和explain中前一个表达式进行连接的行数。
show warning: Mysql的提示信息，可能会帮你优化。

字段解释

select_type

  • Primary 最外层的select。
  • Subquery 不在from语句中，包含在select中的子查询。
  • Derived 包含在from语句中的子查询。派生表。
    id
  • 表示执行顺序，顺序越靠后优先级越高
    type
    效率优先级 system > const > eq_ref > ref > range > index > ALL

一般来说range是及格线，最好达到ref

  • NULL mysql通过优化和底层原理，不访问表或索引就取到值。如求最小值，通过B+树直接拿到。
  • system, const system是const的特例，const表示常量查询。表只有一行，为const查询。只有一条元组匹配，为system查询。
  • eq_ref 主键关联查询，表有几行。
  • ref 使用非主键（不唯一）索引，表有很多行。使用普通索引或唯一索引的部分前缀。
  • range 范围查找，包括比较符号。
  • index 无查询条件，全选。扫描全索引就能拿到结果，一般扫描二级索引（Mysql优先选择同等条件下更小的索引）。
  • ALL 全表扫描。扫描聚集索引，比index更大

sql语句优化

  • 尽量使用覆盖索引
-- 推荐，其中name和age都有索引
select name, age from your_table where condition
-- 不推荐
select * from your_table where condition
  • 少用or或in；不对索引用函数；不让索引发生隐式转换
  • 减少搜索范围；范围过大，mysql会认为全表扫描更快，从而不走索引
  • force index(your_index) 强制索引（注意，不一定更快！可能更多回表）
  • 试试用like代替范围查询

缓存

Mysql8以后移除了缓存。

Mysql缓存的本质是KV Map。然而，对于高频修改的数据，Map缓存下来的是脏数据，因此不实用。

事务

原子性 Atomicity

BUSINESS
sql语句1
sql语句2
COMMIT

原子性：事务操作要么同时发生，要么同时失败，不存在中间情况

通过Undo Log回滚实现

一致性 Consistency

账户500元 -> 扣除1000元 -> 账户-500元
-- 非法操作

一致性：每个操作都必须是合法的，账户信息应该从一个有效状态到另一个有效状态。

隔离性 Isolation

商户1转账500元 -> 余额更新为500元
商户2转账500元 -> 余额更新为500元
-- 没有隔离性

隔离性：两个操作对同一个账户并发操作时，应该表现为不相互影响类似串行的操作。

持久性 Durability

转账500元到余额 --服务器宕机--> 余额0元

持久性：操作更新成功后，更新的结果应该永久地保留下来，不会因为宕机等问题而丢失。

使用

启动数据库

# 需要管理员权限
net start <mysql-service_name>

登录数据库

mysql -u <username> -p
$ <password>

导入数据库

-- 设置中文字符集
mysql> SET NAMES 'utf8mb4';
mysql> SET character_set_server = 'utf8mb4';
mysql> SOURCE /path/to/database.sql;

数据库

-- 创建数据库
CREATE DATABASE yourDatabase
  CHARACTER SET utf8mb4;

-- 查看所有数据库
show databases;

-- 进入数据库
use yourDatabase

表

-- 建表
CREATE TABLE yourTable (
  id   INT         AUTO_INCREMENT PRIMARY KEY,
  name varchar(50) NOT NULL       DEFAULT 'worker',
  age  INT         ,
  addr varchar(50) 
);

-- 删除表
drop <table>;

-- 查看表 
show tables;

-- 修改表
alter table <table>
add <col> char(20)
drop column <col>;

-- 删除整张表
drop table <table>;

查询

-- \G 参数单独展示每一行，避免过宽
select * from <table>\G

-- 查询多列
select <column1>, <column2>, <column3>
from <table>;

-- 只显示不同项
-- 注意，对多列使用时，两列完全一样才会被屏蔽
select distinct <column> from <table>;

-- 检错前5行
select <col> from <table> limit 5;

排序

ORDER BY 需要放在 WHERE 之后

-- 按列升序排序
select <col>
from <table>
order by <col> (asc); -- 默认Ascending

-- 降序排序。对多列排序，每列都要跟desc
select <col>
from <table>
order by <col1> desc, <col2> desc; -- Descending降序排序

筛选

-- 筛选col = value的字段
select <col>
from <table>
where <col> = <value>;

-- 筛选between 0 and 10的字段
select <col>
from <table>
where <col> between 0 and 10;

-- 筛选col为空的字段
select <col>
from <table>
where <col> is null;

逻辑操作符

优先级： AND > OR

-- AND, OR
select <col>
from <table>
where <cond1> or (<cond2> and <cond3>); -- 不加括号，结果会改变

-- IN, NOT
select <col>
from <table>
-- 相当于OR，性能稍好一点，也更直观；NOT主要配合IN使用
where <col> not in (<value1>, <value2>);

插入

-- 插入相应字段的值
insert into <table>(<col1>,
                    <col2>)
values(<val1>,
       <val2>);

insert into yourTable (name, email, age) values ('eric', 'example@email.com', 19);

更新

update <table>
set <col1> = <val1>,
    <col2> = <val2>
where <col3> = <val3>;

删除

delete from <table>
where <col> = <val>;

联结

设计数据库时，应遵循将数据分解到不同的数据表这一原则。
然而，在使用数据时，常常需要将多个表的数据一起检索出来。
这时就需要用到联结。

-- 使用WHERE联结
select <col1>, <col2>
from <table1>, <table2>
where <table1>.<col> = <table2>.<col>;

-- 使用ON联结
select <col1>, <col2>
from <table1> inner join <table2>
on <table1>.<col> = <table2>.<col>;

安装

压缩包安装

  1. 初始化
# 初始化，生成data文件夹，console参数可以看见密码
mysqld --initialize --console > mysql_info
# > mysql_info把命令行输出结果存到文件里，防止密码丢失
  1. 配置mysql.ini
[mysqld]

# 设置3306端口
port=3306

# 设置mysql目录路径
basedir=C:\\your\\path\\MySQL

# 设置mysql数据库的数据的存放目录
datadir=C:\\your\\path\\MySQL\\Data

# 允许最大连接数
max_connections=200

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10

# 服务端使用的字符集默认为UTF8
character-set-server=utf8

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

[mysql]

# 设置mysql客户端默认字符集
default-character-set=utf8

[client]

# 设置mysql客户端连接服务端时默认使用的端口
port=3306

default-character-set=utf8
  1. 安装服务
mysqld --install MySQL8 --defaults-file="C:\your\path\MySQL\mysql.ini" # 设置服务名

# 默认安装
mysqld install
  1. 修改密码
# 设置YourPassword为新密码，再输入旧密码确认修改即可
mysqladmin -u root -p password (YourPassword) --port 3306