mysql
下载安装
下载地址: https://www.mysql.com/downloads/
创建和使用数据库
索引
1
2
3
| show index from `表名`;
show keys from `表名`;
|
1
2
3
4
5
6
7
8
| -- 普通索引
create index index_username on `表名` (username);
-- 全文索引
create fulltext index index_username on `表名` (username);
-- 主键索引
alter table `表名` add primary key (id);
-- 外键索引
alter table `表名` add unique (id);
|
1
2
3
4
5
6
7
8
| drop index index_username on user;
alter table user drop index index_username;
-- 删除主键索引
alter table user drop primary key;
-- 删除外键索引
alter table user drop unique;
|
索引查询注意
- like时“%aa%”不会使用索引，而like “aa%”会使用索引
- where子句中使用了索引的话，order by中的列不会使用索引
字段操作
- 添加字段:
1
| alter table user add column extend varchar(10) after id;
|
- 修改字段:
1
2
| alter table user modify column extend varchar(11) not null comment '字段备注';
alter table user change column extend extend1 varchar(11) default null comment '字段备注';
|
- 删除表字段:
1
2
| alter table 表名 drop column 字段名;
create table 新表名 select 字段1, 字段2 from 原表名;
|
数据操纵语句(DML)
- 指定名称插入:
insert into user (name,credate,sal,deptno) values ('zhangsan','2018-01-01','2000',1)
- 不指定名称插入:
insert into user values ('zhangsan','2018-01-01','2000',1)
- 批量插入数据:
insert into user values(1,'dept1'),(2,'dept2')
- 修改记录:
update user set sal='4000',deptno=2 where ename='zhangsan'
- 删除记录:
delete from user where ename='zhangsan'
查询
- 查询所有记录：
select * from user
- 查询不重复的记录：
select distinct deptno from user
- 条件查询：
select * from user where deptno=1 and sal<3000
- 排序和限制：
select * from user order by deptno desc limit 2
- 分页查询(查询从第0条记录开始10条)：
select * from user order by deptno desc limit 0,10
- 聚合(查询部门人数大于1的部门编号)：
select deptno,count(1) from user group by deptno having count(1) > 1
- 连接查询：
select * from user e left join user2 d on e.deptno=d.deptno
- 子查询：
select * from user where deptno in (select deptno from user2)
- 记录联合：
select deptno from user union select deptno from user2
数据控制语句(DCL)
权限相关
- 授予操作权限(将test数据库中所有表的select和insert权限授予test用户)：
grant select,insert on test.* to 'test'@'localhost' identified by '123'
- 查看账号权限：
show grants for 'test'@'localhost'
- 收回操作权限：
revoke insert on test.* from 'test'@'localhost'
- 授予所有数据库的所有权限：
grant all privileges on *.* to 'test'@'localhost'
- 授予所有数据库的所有权限(包括grant)：
grant all privileges on *.* to 'test'@'localhost' with grant option
- 授予
SUPER PROCESS FILE权限（系统权限不能指定数据库）：grant super,process,file on *.* to 'test'@'localhost'
- 只授予登录权限：
grant usage on *.* to 'test'@'localhost'
帐号相关
- 删除账号：
drop user 'test'@'localhost'
- 修改自己的密码：
set password = password('123')
- 管理员修改他人密码：
set password for 'test'@'localhost' = password('123')
其他
字符集相关
- 查看字符集：
show variables like 'character%'
- 创建数据库时指定字符集：
create database test2 character set utf8
时区相关
- 查看当前时区（UTC为世界统一时间，中国为UTC+8）：
show variables like "%time_zone%"
- 修改mysql全局时区为北京时间，即我们所在的东8区：
set global time_zone = '+8:00';
- 修改当前会话时区：
set time_zone = '+8:00'
- 立即生效：
flush privileges
连接数(默认151 最大可以达到16384 设置在80% 13000左右比较合适)
MySQL: ERROR 1040: Too many connections
设置较大的 max_connections 值时，可能会导致数据库因接受了太多的连接而性能下降，甚至因为资源耗尽而无法响应的风险
查看最大连接数设置
1
| show variables like '%max_connections%';
|
查看当前实际最大使用连接数
1
| show global status like 'Max_used_connections';
|
设置最大连接数
1
2
3
4
| # 立即生效，重启后失效
set GLOBAL max_connections=1000;
# 修改mysql安装目录配置文件my.ini，“max_connections”的值，默认最大连接数是100重启后生效
|
查看当前进程
优化
- 表关联查询时务必遵循 小表驱动大表 原则；
- 使用查询语句 where 条件时，不允许出现 函数，否则索引会失效；
- 使用单表查询时，相同字段尽量不要用 OR，因为可能导致索引失效，比如：SELECT * FROM table WHERE name = ‘zhangsan’ OR name = ‘lisi’，可以使用 UNION 替代；
- LIKE 语句不允许使用 % 开头，否则索引会失效；
- 组合索引一定要遵循 从左到右 原则，否则索引会失效；比如：SELECT * FROM table WHERE name = ‘张三’ AND age = 18，那么该组合索引必须是 name,age 形式；
- 索引不宜过多，根据实际情况决定，尽量不要超过 10 个；
- 每张表都必须有 主键，达到加快查询效率的目的；
- 分表，可根据业务字段尾数中的个位或十位或百位（以此类推）做表名达到分表的目的；
- 分库，可根据业务字段尾数中的个位或十位或百位（以此类推）做库名达到分库的目的；
- 表分区，类似于硬盘分区，可以将某个时间段的数据放在分区里，加快查询速度，可以配合 分表 + 表分区 结合使用；
删除冗余和重复索引
1
2
3
4
5
| //反例
KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`,`age`)
//正例，删除userId索引，因为组合索引（A，B）相当于创建了（A）和（A，B）索引
KEY `idx_userId_age` (`userId`,`age`)
|
索引不宜太多，一般5个以内
where子句中考虑使用默认值代替null
1
2
3
4
| //反例
select * from user where age is not null;
//正例，设置0为默认值
select * from user where age>0;
|
避免在where子句中使用or来连接条件
1
2
3
4
5
6
7
8
| //使用union all
select * from user where userid=1
unionall
select * from use rwhere age = 18
//或者分开两条sql写：
select * from user where userid=1
select * from user where age = 18
|
优化like语句
把%放关键字后面
1
| select userId，namefrom user where userId like'123%';
|
避免死锁
- 理的设计索引，区分度高的列放到组合索引前面，使业务
SQL 尽可能通过索引定位更少的行，减少锁竞争。
- 调整业务逻辑
SQL 执行顺序， 避免 update/delete 长时间持有锁的 SQL 在事务前面。
- 避免大事务，尽量将大事务拆成多个小事务来处理，小事务发生锁冲突的几率也更小。
- 以固定的顺序访问表和行。比如两个更新数据的事务，事务
A 更新数据的顺序为 1，2;事务 B 更新数据的顺序为2，1。这样更可能会造成死锁。
- 在并发比较高的系统中，不要显式加锁，特别是是在事务里显式加锁。如
select … for update 语句，如果是在事务里（运行了 start transaction 或设置了autocommit 等于0）,那么就会锁定所查找到的记录。
- 尽量按主键/索引去查找记录，范围查找增加了锁冲突的可能性，也不要利用数据库做一些额外额度计算工作。比如有的程序会用到 “select … where … order by rand();”这样的语句，由于类似这样的语句用不到索引，因此将导致整个表的数据都被锁住。
- 优化 SQL 和表设计，减少同时占用太多资源的情况。比如说，减少连接的表，将复杂 SQL 分解为多个简单的 SQL。
::: danger 警告
在使用 MySQL 或 MariaDB，不要用“utf8”编码，改用“utf8mb4”。这里（https://mathiasbynens.be/notes/mysql-utf8mb4#utf8-to-utf8mb4）提供了一个指南用于将现有数据库的字符编码从“utf8”转成“utf8mb4”。
MySQL 的“utf8mb4”是真正的“UTF-8”。
MySQL 的“utf8”是一种“专属的编码”，它能够编码的 Unicode 字符并不多。
:::
缓存清理
查看是否生效
1
| select @@query_cache_type;
|
1.在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明
select sql_no_cache id,name from tableName;
2.当sql中用到mysql函数，也不会缓存
查看缓存的状态
1
| show status like '%Qcache%';
|
| 名称 |
备注 |
| Qcache_free_blocks |
目前还处于空闲状态的 Query Cache 中内存 Block 数目 |
| Qcache_free_memory |
目前还处于空闲状态的 Query Cache 内存总量 |
| Qcache_hits |
Query Cache 命中次数 |
| Qcache_inserts |
向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数 |
| Qcache_lowmem_prunes |
当 Query Cache 内存容量不够，需要从中删除老的 Query Cache 以给新的 Cache 对象使用的次数 |
| Qcache_not_cached |
没有被 Cache 的 SQL 数，包括无法被 Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache 的 SQL |
| Qcache_queries_in_cache |
目前在 Query Cache 中的 SQL 数量 |
| Qcache_total_blocks |
Query Cache 中总的 Block 数量 |
禁用/开启查询缓存
1
2
| set session query_cache_type=off;
set session query_cache_type=on;
|
缓存清理
1
2
3
| FLUSH QUERY CACHE; -- 清理查询缓存内存碎片。
RESET QUERY CACHE; -- 从查询缓存中移出所有查询。
FLUSH TABLES; -- 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。
|
参考资料
