文章目录
Structured Query Language
ACID原子性（atomicity)、一致性（consistency)、隔离性（isolation）、持久性（durability）
开源关系型数据库relational database system
on myDB.* --代替myDB数据库的全部表
to tom@'%' --已知世界中所有主机的列表
sql 中使用单引号，为了避免其将字符串理解为保留关键字或已知的名称（如表名或者列名）而出现错误。
system clear;
\G #将行以列显示
show warnings;-
[tom@suse-tom uthread]$mysqladmin extended-status | grep -i 'uptime' #脚本查看服务器情况
| Uptime | 210804 |
| Uptime_since_flush_status
[tom@suse-tom uthread]$mysqladmin extended-status | grep -i 'qcache'
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1039864 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
[tom@suse-tom uthread]$
MariaDB root@(none):(none)> show status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
show variables like 'table_cache%'; --表缓冲
set gloable tabel_achce = 128;
MariaDB root@(none):world> show variables like '%open%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| have_openssl | YES |
| innodb_open_files | 2000 |
| open_files_limit | 32184 |
| table_open_cache | 2000 |
| table_open_cache_instances | 8 |
+----------------------------+-------+
MariaDB root@(none):world> show status; --简单show status 可以看到当前操作的库或表的各项信息列表。
MariaDB root@(none):(none)> show status like '%question%'
+---------------+-------+
| Variable_name | Value | --向服务器查询次数
+---------------+-------+
| Questions | 5 |
+---------------+-------+
1 row in set
show status like '%uptime%'
show status like '%connect%';
show processlist; --显示连接表
kill id --杀死连接
show status like '%com_%'; --查看使用命令数，可用于分析增删改。
show status like 'bytes_%'
MariaDB root@(none):(none)> show status like 'bytes_%'
+----------------+-------+
| Variable_name | Value | --数据收发字节
+----------------+-------+
| Bytes_received | 281 |
| Bytes_sent | 1098 |
+----------------+-------+
2 rows in set
连接
MariaDB root@(none):world> show variables like 'max_conn%'; --最大连接数
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| max_connect_errors | 100 |
| max_connections | 151 |
+--------------------+-------+
show status like 'qchache%'; --查询缓存
MariaDB root@(none):(none)> show status like '%connect%';
+-----------------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------------+-------+
| Aborted_connects | 0 | --异常退出连接
| Aborted_connects_preauth | 0 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 32 |
| Max_used_connections | 2 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Slave_connections | 0 |
| Slaves_connected | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 | --多线程连接
| wsrep_connected | OFF |
+-----------------------------------------------+-------+
权限与账户
help contents; server side help
help account management;
help rename user;
drop user ' '@' ';
rename user ' '@' ' to ' '@' ';
insert into mysql.user(host,user,password) vaules ('va* ',' ',PASSWORD('****'));
delete from mysql.user where user = '' and host=' ';
update mysql,user set user=' ' where user=' ' and host '*.*...';
grant all on pi.todolist to 'pi'@'localhost' INDENTFIED BY 'raspberry'
revoke on from --移除权限(先revoke相关权限，再drop user)
grant all privileges on world.city to 'root'@'%' identified by 'some_passwd' with grant option;
set global key_buffer_size = 128*1024*1024; --global 和 local local为即时生效 local 同 session(当前会话)
set @@global.key_buffer_size = .......
让mysql不区分表名大小写
vim /etc/my.cnf
[mysqld]
lower_case_table_names=1
重启数据库
flush privileges; --刷新对服务器的修改，刷新特权表
grant-table --特权表
select current_user(); --当前用户
| 权限表 | 内容 |
|---|---|
| columns_priv | 为一个表中的具体字段定义用户权限 |
| db | 为服务器上的所有数据库定义许可 |
| procs_priv | 为存储例程定义用户权限 |
| tables_priv | 为一个数据库中的具体表定义用户权限 |
| user | 为一个具体用户定义命令权限 |
describe ctable; --显示表结构，或者desc ctable;
select sleep();
MariaDB root@(none):(none)> select current_user;
+----------------+
| current_user |
+----------------+
| root@localhost |
+----------------+
1 row in set
Time: 0.051s
show variables like '%cache%';
show status like '%cache%';
show status like 'key_read%';
show engines;
#配置读取顺序，生效级别
~/.my.cnf
/etc/my.cnf
mysqld --verbose --help #查看各个配置项，可以命令行设置，也可以去掉--写在配置文件上
--defaults-file=# Only read default options from the given file #.
alter table city add index using btree(`ID`) --建立索引 还可以使用 using hash(`ID`)
alter table city drop index ID --删除索引
数据库导入导出
mysqldump -uroot -p world > world.sql #导出数据库
导出数据库
- 先建立一个空数据库
- mysql -uroot -p database_name < database_name.sql
或者
MariaDB root@(none):(none)> source /home/tom/populate.sql
或者
MariaDB root@(none):(none)> \. /root/world.sql
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Query OK, 0 rows affected
Time: 12.495s
启停mysql
suse-tom:~ # mysqladmin ping -uroot -plovetom
mysqld is alive
suse-tom:~ # mysqladmin ping
mysqld is alive
suse-tom:~ # mysqladmin shutdown
开启日志
https://blog.csdn.net/intelrain/article/details/80451120
mysql官方world数据库
https://dev.mysql.com/doc/index-other.html
https://dev.mysql.com/doc/
merg引擎
优点
- 可以在myisam单表受限时，可以结合多个表进行扩展。
- 可以用来统计所有的分表（假设按天分表）数据
缺点
- 索引查询慢，并发少，效率低
CREATE TABLE mrgtable(
a INT NOT NULL PRIMARY KEY
)ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; --此操作必须为myisam引擎可用
建表语句解释：
1）ENGINE=MERGE
指明使用MERGE引擎，有些同学可能见到过ENGINE=MRG_MyISAM的例子，也是对的，它们是一回事。
2）UNION=(t1, t2)
指明了MERGE表中挂接了些哪表，可以通过alter table的方式修改UNION的值，以实现增删MERGE表子表的功能。
3）INSERT_METHOD=LAST
INSERT_METHOD指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。
4）MERGE表及构成MERGE数据表结构的各成员数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。
merge表的一些问题：http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html#merge-table-problems
采用自增主键，插入数据开销小，否则会进行频繁的移动数据，分页操作会造成大量的碎片，需要optimize table重建表并优化填充页面。
memory引擎
驻留内存，减少了磁盘I/O，可以存一些固定的值。
设置配置初始化脚本，防止关闭内存丢失。
在my.cnf配置文件中
init-file=/var/lib/mysql/init.sql
init.sql内容为
insert into test.city select * from world.city;
innodb
innodb不保存表的具体行数。
外键使用
show variables like 'innodb_buffer%';
innodb_log_bufffer_size = 8M
--一般最大1M-8M
create table student
(
sid int unsigned not null primary key,
name varchar(33)
)engine = innodb;
create table score
(
sid int unsigned,
calss varchar(30),
score smallint,
index(sid), --本表sid
foreign key(sid)
references student(sid)
on update cascade --子表亦更新 沉叠
on delete cascade
)engine = innodeb;
| read-uncomitted | repeatable-read | read-committed | |
|---|---|---|---|
| dirty reads | ⭕ | ❌ | ❌ |
| non-repeatable | ⭕ | ❌ | ⭕ |
| phantoms | ⭕ | ❌ | ⭕ |
⭕:occur ❌:not
phantoms 幻影读，查询一会有一会无
set session transaction isolation level read uncommitted
set @@tx_isolation = 'READ_UNCOMMITTED';
myisam
[mysqld]
myisam-recover-option=BACKUP,FORCE,QUICK
show status like 'key_read%'
key_reads /key_read_requests --miss rate
1 - (key_reads/key_read_requests) --有效率
federated 联邦引擎
CREATE TABLE Co2(
code CHAR(3) NOTNULL,Name CHAR(52)NOT NULL,
Population INT(11) NOT NULL DEFAULT 0
)ENGINE=FEDERATED
CONNECTION='mysql://remote:redhat@192.168.1.129/test/country2'
修复表
MariaDB root@(none):(none)> help table maintenance;
+----------------------+----------------+----------------+
| source_category_name | name | is_it_category |
+----------------------+----------------+----------------+
| Table Maintenance | ANALYZE TABLE | N |
| Table Maintenance | CHECK TABLE | N |
| Table Maintenance | CHECK VIEW | N |
| Table Maintenance | CHECKSUM TABLE | N |
| Table Maintenance | OPTIMIZE TABLE | N |
| Table Maintenance | REPAIR TABLE | N |
| Table Maintenance | REPAIR VIEW | N |
+----------------------+----------------+----------------+
lock table city read local;
--没有optimize不能在并发插入，亦不能能delete，
unlock table
--能并发插入（读锁定）
知识
非聚集性索引等价于二级索引 ,innodb是聚集索引，myisam是非聚集索引。
b树每个节点都存储key和data，并且叶子指针为null，叶子结点不包含关键字信息。
b+树，所有叶子结点中包含了全部的关键字信息，指向含有这些关键字记录的指针，且叶子结点 本身依关键字的大小自小而大顺序链接。其作查询效率每个路径相同，查询效率更稳定。
优化
- 使用
ENUM，而不是varchar，例如性别、国家这些字段。 - 垂直分库，分开段，降低业务耦合，提高资源利用率。
- 水平分表，避免单表数据量过大，减少IO竞争。
- 使用varchar类型代替char，varchar会动态分配长度
- 避免表字段运行为null，建议设置默认值。
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
