简介
MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
索引
索引的作用是提高查询效率,数据库默认查询方式是从第一条数据开始全表扫描直到找出数据,添加索引后,通过缩小查询范围并且把随机事件变成顺序事件,可以快速定位到指定位置去搜索数据.
索引依托于存储引擎的实现,每种存储引擎支持的索引都不一定完全相同,且每一种存储引擎都不一定支持所有索引类型.
创建索引时,需要考虑到哪些列会用于SQL查询,索引也是一种表,保存着主键或索引字段,以及一个能将每个记录指向实绩表的指针,数据库用户看不到索引,它只是用来加速查询.需要注意的是,不能滥用索引,虽然索引会提高查询效率,但是插入更新和删除的时候都会对索引进行操作,数据量如果很大的话,这将会是一个很耗时的操作,并且索引也是需要存储空间.
索引的优缺点
优点：
- 加快查询的速度
- 唯一索引可以保证数据库中每一行数据的唯一性
- 在实现数据的参考完整性方面,可以加速表和表之间的连接
- 在使用分组和排序子句进行数据查询时,可以显著减少查询中分组和排序的时间
缺点：
- 占用存储空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸
- 增删改时需要动态维护索引,损耗性能
索引的存储类型
MySQL中索引的存储类型有两种: BTREE 和 HASH
MyISAM和InnoDB: 仅支持BTREEMEMORY和HEAP: 支持BTREE和HASH两种.
索引类型
-
普通索引 (NORMAL INDEX)：允许为空也允许重复
-
唯一索引 (UNIQUE INDEX)：允许为空不可以重复
-
主键索引 (PRIMARY INDEX)：特殊的唯一索引,不允许为空,同时添加主键约束,一个表中只有一个主键
-
全文索引 (FULLTEXT INDEX)
支持全文查找,允许为空和重复,可以在char,varchar或text类型字段上创建,建立时需要考虑列查询的频率和列的区分度
MySQL 5.6版本之后InnoDB开始支持全文索引,5.7版本之后通过使用ngram插件开始支持中文.之前仅支持英文,通过空格作为分词的分隔符,对于中文来说是不合适的
-
组合索引
在表的多个字段上创建索引,只有当查询时使用到了这些字段的最左边字段时,索引才会被使用
索引的设计原则
- 索引并非越多越好,大量的索引会占用大量的磁盘空间,并且会影响
insert,update和delete语句的性能 - 避免对经常更新的表设计过多索引,并且索引中的列要尽可能少
- 对于经常查询的表要创建索引,但要避免添加不必要的索引
- 在条件表达式中经常用到的不同值较多的列上建立索引,不同值较少的列不要建立索引,比如性别字段
- 当唯一性是某种数据本身特性时指定唯一索引
- 在频繁排序或者分组的字段上建立索引,如果待排序的字段有多个,可以在这些列上建立组合索引
未使用索引的场景
-
以
%开头的like查询不能使用BTREE索引 -
MySQL预计索引查询时间比全表扫描慢
-
数据出现隐式转换的时候不会使用索引
如果索引列是字符串,但
Where条件中查询传值没有用引号括起来的话,是不会使用索引的,因为MySQL默认把输入的值进行转换以后才开始检索.Where查询中传值没有用引号括起来,可以看到虽然列出可能会用到的索引(possible_keys)有值,但实际查询的时候并没有使用索引(key)mysql> explain select * from t_user where phone = 18702100009 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_user partitions: NULL type: ALL possible_keys: TEL key: NULL key_len: NULL ref: NULL rows: 99550 filtered: 10.00 Extra: Using where 1 row in set, 3 warnings (0.00 sec)Where查询中传值用引号括起来了,可以看到查询时候使用到索引了mysql> explain select * from t_user where phone = '18702100009' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_user partitions: NULL type: const possible_keys: TEL key: TEL key_len: 33 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) -
不满足最左边原则不会使用组合索引
这里需要特别注意,最左边原则不是指查询条件的顺序,而是指查询条件中是否包含索引中的最左列
假设索引
index_name_phone_class包含列name,phone,class,最左侧列是name4.1 如果查询条件中没有
name,则不会使用这个索引,如下:mysql> explain select * from t_student where phone = '18702123254' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_student partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)4.2 如果查询条件中有
name,则会使用这个索引,如下:mysql> explain select * from t_student where email = '18701252412' and name = '白展堂' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_student partitions: NULL type: ref possible_keys: index_name_phone_class key: index_name_phone_class key_len: 140 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)4.3 无法跳过列使用后续索引,如下方第二种情况,使用了
name和class,但class字段没有使用索引mysql> explain select * from t_student where name = '白展堂' and phone = '18212452154' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_student partitions: NULL type: ref possible_keys: index_name_phone_class key: index_name_phone_class key_len: 140 ref: const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t_student where name = '白展堂' and class = '45' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_student partitions: NULL type: ref possible_keys: index_name_phone_class key: index_name_phone_class key_len: 107 ref: const rows: 1 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) mysql> explain select * from t_student where name = '白展堂' and phone = '18212452154' and class='45' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_student partitions: NULL type: ref possible_keys: index_name_phone_class key: index_name_phone_class key_len: 172 ref: const,const,const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec)4.4 范围查询后的列无法使用索引,假设
name使用了like查询,phone和class字段都没有使用索引>,<,between,like等都是范围查询mysql> explain select * from t_student where name like '白%' and phone = '18701421542' and class = '45' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t_student partitions: NULL type: range possible_keys: index_name_phone_class key: index_name_phone_class key_len: 172 ref: NULL rows: 1 filtered: 100.00 Extra: Using index condition 1 row in set, 1 warning (0.00 sec) -
查询条件使用
or条件,此项需要深入研究
存储引擎
MySQL支持 MyISAM、InnoDB、Memory、Heap 四种存储引擎，后两种不常用。需要注意的是，存储引擎是针对于表而不是针对于库的，一个库中的不同表可以使用不同的存储引擎。
MyISAM
MyISAM是MySQL 5.5 之前的版本的默认引擎,也是系统表,临时表的默认存储引擎,这里的临时表指的是在排序,分组等操作中,当数量超过一定的大小之后,由查询优化器建立的临时表.
MyISAM 存储结构
MyISAM存储引擎表由MYD(数据)、MYI(索引)和frm(表结构信息)组成
- 创建MyISAM引擎的表
mysql> create table myIsam(id int, c1 varchar(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
- 查看目录文件
root@homestead:/var/lib/mysql/imooc# ll
total 136
drwxr-x--- 2 mysql mysql 4096 Sep 23 22:40 ./
drwx------ 13 mysql mysql 4096 Sep 23 19:10 ../
-rw-r----- 1 mysql mysql 65 Sep 23 19:10 db.opt
-rw-r----- 1 mysql mysql 8582 Sep 23 22:40 myIsam.frm
-rw-r----- 1 mysql mysql 0 Sep 23 22:40 myIsam.MYD
-rw-r----- 1 mysql mysql 1024 Sep 23 22:40 myIsam.MYI
MyISAM特性
-
并发性与锁级别
MyISAM的锁是表级锁,读取和写入的操作是互斥的,对于只读操作性能可以接受,但对于读写混合的并发操作性能不好.
-
表损坏修复
使用mysql内命令
check和repair检查修复表.# 检查表 mysql> check table myIsam; +--------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+----------+ | imooc.myIsam | check | status | OK | +--------------+-------+----------+----------+ 1 row in set (0.00 sec) # 修复表 mysql> repair table myIsam; +--------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------+----------+----------+ | imooc.myIsam | repair | status | OK | +--------------+--------+----------+----------+ 1 row in set (0.00 sec)也可以使用
myisamchk命令修复MyISAM表,但修复前需要先停止MySQL服务,否则会损坏表. -
MyISAM支持全文索引(FullText Index),并支持对text,blob或者或者很长的VARCHAR类型等类型字段创建前缀索引
-
支持数据压缩(myisampack命令)
针对压缩的包只能进行读操作,不能进行写操作
root@homestead:/var/lib/mysql/imooc# myisampack -b -f myIsam.MYI -
不支持事务
MyISAM限制
- MySQL 5.0版本前默认表大小为4G,如果存储大表则要修改Max_Rows和AVG_ROW_LENGTH
- MySQL 5.0版本之后默认支持为256TB
MyISAM适用场景
- 非事务型应用
- 只读类应用
- 空间类应用
InnoDB
MySQL 5.5之后的版本默认引擎变为了InnoDB.它是事务级的存储引擎,适用表空间进行数据存储,完美支持行级锁及事务ACID特性,InnoDB适用合于大多数OLTP应用
InnoDB存储空间
InnoDB根据innodb_file_per_table的配置来决定使用什么表空间,强烈推荐使用独立表空间
- 值为ON: 适用独立表空间,名字是tablename.ibd
- 值为OFF: 适用系统表空间ibdataX
# 创建表
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> create table innodb(id int, c1 varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
# 查看数据表文件
root@homestead:/var/lib/mysql/imooc# ll
total 136
drwxr-x--- 2 mysql mysql 4096 Sep 23 23:08 ./
drwx------ 13 mysql mysql 4096 Sep 23 19:10 ../
-rw-r----- 1 mysql mysql 65 Sep 23 19:10 db.opt
-rw-r----- 1 mysql mysql 8582 Sep 23 23:08 innodb.frm
-rw-r----- 1 mysql mysql 98304 Sep 23 23:08 innodb.ibd
系统表空间和独立表空间的对比:
- 系统表空间无法简单的收缩文件大小,独立表空间可以通过
optimize table命令收缩系统文件 - 系统表空间会产生IO瓶颈,独立表空间可以同时向多个文件刷新数据
转移系统表空间中的表转移到独立表空间:
- 使用mysqldump导出所有数据库表数据,注意导出存储过程,触发器,计划事件等数据
- 停止MySQL服务,修改参数(innodb_file_per_table),并删除InnoDB相关文件
- 重启MySQL服务,重建InnoDB系统表空间
- 重新导入数据
InnoDB特性
- InnoDB是一种事务性存储引擎,完全支持事务的ACID特性
- InnoDB支持行级锁,可以最大程度的支持并发,行级锁是由存储引擎实现的
- 提供状态检查(
show engine innodb status)
InnoDB适用场景:
一般来说,除非是特定需要使用 MyISAM 的场景,一般都尽量使用 InnoDB.
事务
它是一组具有原子性的SQL语句,或是一个独立的工作单元,是数据库系统区别于其它一切文件系统的重要特性之一.
MyISAM存储引擎是不支持事务的,InnoDB存储引擎支持事务
事务的特性(ACID)
-
原子性(Atomicity)
一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作
-
一致性(Consistency)
一致性是指事务将数据库从一种一致性状态转换到另外一种一致性状态,在事务开始之前和事务结束后数据库中数据的完整性没有被破坏
-
隔离性(Isolation)
隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其它事务是不可见的.
SQL标准的四种隔离级别,隔离性由低到高,并发性由高到低.
-
未提交读(read uncommitted)不建议使用
事务对数据进行了修改,即便未提交,对其它事务也是可见
-
已提交读(read committed),又称不可重复读大多数数据库默认隔离级别,MySQL例外,
一个事务从开始到提交前对数据库的修改对其它事务都是不可见的,相对于同一个事务来说,多次读取同样的记录结果不一定是一致的
-
可重复读(repeatable read)(INNODB 默认)
相对于同一个事务来说,多次读取同样的记录结果是一致的
-
可串行化(SERIALIZABLE),最高级别,很少用到
会在读取的每一行数据都加锁,会造成锁超时和锁征用问题,很少用,除非要求严格数据一致性且没有并发量前提下
-
-
持久性(Durability)
一旦事务提交,则其所做的修改就会永久保存到数据库中,此时即使系统崩溃,已经提交的修改数据也不会丢失.
主从
原理
同步操作通过 3 个线程实现，其基本步骤如下：
- 主库在数据更新提交事务之前，将事件异步记录到binlog二进制日志文件中，日志记录完成后存储引擎提交本次事务
- 从库启动一个I/O线程与主库建立连接，用来请求主库中要更新的binlog。这时主库创建的binlog dump线程，这是二进制转储线程，如果有新更新的事件，就通知I/O线程；当该线程转储二进制日志完成，没有新的日志时，该线程进入sleep状态。
- 从库的I/O线程接收到新的事件日志后，保存到自己的relay log（中继日志）中
- 从库的SQL线程读取中继日志中的事件，并执行更新保存。
配置
-
主库
- server-id：服务器设置唯一ID，默认为1，推荐取IP最后部分；
- log-bin：设置二进制日志文件的基本名，默认不开启，配置后表示开启日志；
- log-bin-index：设置二进制日志索引文件名；
- binlog_format：控制二进制日志格式，进而控制了复制类型，三个可选值
- STATEMENT：SQL语句复制，优点：占用空间少，缺点：误删则无法恢复数据，在某些情况下，可能造成主备不一致
- ROW：行复制，优点：可以找回误删的信息，可以避免主备不一致的情况，缺点：占用空间大
- MIXED：混和复制，默认选项，混合statement,row。 Mysql 会判断哪些语句执行可能引起主备不一致，这些语句采用row 格式记录，其他的使用statement格式记录，当然这种形式的日志也没有办法恢复误删的数据。
- sync-binlog：默认为0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。一旦系统崩溃, binlog_cache中的所有binlog信息都会被丢失。为保证不会丢失数据，需设置为1，用于强制每次提交事务时，同步二进制日志到磁盘上。
- expire_logs_days：设置binlog保存时间，默认为0，也就是随着服务器运行，binlog会越来越大。看业务需求来配置binlog保存时间吧。结合每日的数据库备份功能，通过binlog，可以支持将数据库回溯到N天的任意时间点。
- max_binlog_size：binlog日志文件大小 默认大小1G
- binlog-do-db：binlog记录的数据库
- binlog-ignore-db：binlog 不记录的数据库
-
从库
- server-id：服务器设置唯一ID
- relay-log：中继日志
- relay-log-index：中继日志的索引文件
- read-only：是否只读，默认为0，为1表示只读
- replicate-do-db：同步的数据库
- replicate-ignore-db：不同步的数据库
- replicate-wild-do-table：同步的数据表
- replicate-wild-ignore-table：不同步的数据表
命令
-
从库配置主库账号密码
mysql> CHANGE MASTER TO MASTER_HOST='master', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='slave'; -
重置从库
mysql> RESET SLAVE; -
启动从库
mysql> START SLAVE; -
停止从库
mysql> STOP SLAVE; -
查询主库状态
mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000004 Position: 458 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) -
查询从库状态
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: master Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000031 Read_Master_Log_Pos: 3146 Relay_Log_File: 1ab49ece5ea5-relay-bin.000033 Relay_Log_Pos: 3359 Relay_Master_Log_File: mysql-bin.000031 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: data.% Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3146 Relay_Log_Space: 5580 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 0c77048c-e77d-11ea-9ba9-0242ac110002 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)上面标记的输出信息Slave_IO_Running: Yes和Slave_SQL_Running: Yes可以看到I/O线程和SQL线程已启动运行中。
数据处理
备份主库
若主从数据库都是刚刚装好且数据都是一致的，直接执行 show master status 查看日志坐标。
若主库可以停机，则直接拷贝所有数据库文件。
若主库是在线生产库，可采用 mysqldump 备份数据，因为它对所有存储引擎均可使用。
-
为了获取一个一致性的快照，需对所有表设置读锁
mysql> flush tables with read lock; Query OK, 0 rows affected (0.02 sec) -
获取二进制日志的坐标
mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000004 Position: 458 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) -
备份数据
# 针对事务性引擎 mysqldump -uroot -pmaster --all-database -e --single-transaction --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql # 针对 MyISAM 引擎，或多引擎混合的数据库 mysqldump -uroot -pmaster --all-database -e -l --flush-logs --max_allowed_packet=1048576 --net_buffer_length=16384 > /data/all_db.sql -
恢复主库的写操作
unlock tables;
导入从库
-
导入数据
mysql -uroot -p < /data/all_db.sql -
配置主库连接，设置二进制日志的坐标
mysql> CHANGE MASTER TO MASTER_HOST='master', MASTER_PORT=3306, MASTER_USER='slave', MASTER_PASSWORD='slave', master_log_file='mysql-bin.000004', master_log_pos=458;
常用命令
管理
查看MySQL进程
使用 ps -ef | grep mysqld 命令
vagrant@homestead:~$ ps -ef | grep mysqld
mysql 1373 1 0 Sep22 ? 00:03:49 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
vagrant 5429 1073 0 13:55 pts/1 00:00:00 grep --color=auto mysqld
启动MySQL服务器
path/to/mysqld_safe &
[root@iZ2zeisotv99f1nmihsv1fZ ~]# 180924 22:04:07 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
180924 22:04:07 mysqld_safe Starting mysqld daemon with databases from /data/mysql
关闭MySQL服务器
使用 mysqladmin -u [root] -p shutdown 命令
[root@iZ2zeisotv99f1nmihsv1fZ ~]# mysqladmin -u root -p shutdown
Enter password:
列出所有数据库
使用 show databases 命令
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| homestead |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
13 rows in set (0.00 sec)
切换数据库
使用 use [database] 命令
mysql> use test;
Database changed
列出当前数据库的所有表
使用 show tables 命令
mysql> show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| t_app |
| t_product |
| t_product_category |
| t_product_category_log |
| t_test |
| t_user |
| t_user_order |
| t_website |
+------------------------+
8 rows in set (0.00 sec)
显示某个表的字段信息
使用 show columns from [table] 命令
mysql> show columns from t_product;
+---------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| code | varchar(50) | NO | UNI | NULL | |
| categoryID | int(11) unsigned | NO | PRI | NULL | |
| stock | int(11) | NO | | NULL | |
| sale | int(11) unsigned | NO | | 0 | |
| price | int(11) | NO | | NULL | |
| shippingPrice | int(11) | NO | | NULL | |
| isShow | tinyint(1) | NO | | 1 | |
| isRecommend | tinyint(1) | NO | | 0 | |
| createTime | int(11) | NO | | NULL | |
| updateTime | int(11) | NO | | NULL | |
| ver | int(11) | NO | | 0 | |
+---------------+------------------+------+-----+---------+----------------+
13 rows in set (0.00 sec)
显示某个表的索引信息
使用 show index from [table] 命令
mysql> show index from t_product;
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_product | 0 | PRIMARY | 1 | id | A | 492481 | NULL | NULL | | BTREE | | |
| t_product | 0 | PRIMARY | 2 | categoryID | A | 492481 | NULL | NULL | | BTREE | | |
| t_product | 0 | code | 1 | code | A | 492481 | NULL | NULL | | BTREE | | |
| t_product | 1 | id | 1 | id | A | 492481 | NULL | NULL | | BTREE | | |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
查看表状态
使用 show table status [from database] [like 'pattern'] \G 命令
- 后面加上
\g表示查询结果按列打印
mysql> show table status from test like 't_product';
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+-----------+
| t_product | InnoDB | 10 | Compact | 492481 | 105 | 51986432 | 0 | 19988480 | 3145728 | 499001 | 2018-09-24 08:14:36 | NULL | NULL | utf8_general_ci | NULL | row_format=COMPACT | 商品表 |
+-----------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+--------------------+-----------+
1 row in set (0.00 sec)
后面加上 \G
mysql> show table status from test like 't_product' \G;
*************************** 1. row ***************************
Name: t_product
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 492481
Avg_row_length: 105
Data_length: 51986432
Max_data_length: 0
Index_length: 19988480
Data_free: 3145728
Auto_increment: 499001
Create_time: 2018-09-24 08:14:36
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options: row_format=COMPACT
Comment: 商品表
1 row in set (0.00 sec)
数据库操作
创建数据库
- 使用SQL语句创建
mysql> create database if not exists dota2 default charset=utf8 collate=utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.00 sec)
- 使用
mysqladmin创建
vagrant@homestead:~$ mysqladmin -u root -p create csgo
Enter password:
删除数据库
- 使用SQL语句删除
mysql> drop database dota2;
Query OK, 0 rows affected (0.00 sec)
- 使用
mysqladmin删除
vagrant@homestead:~$ mysqladmin -u root -p drop csgo
Enter password:
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'csgo' database [y/N] y
Database "csgo" dropped
数据表
创建数据表
mysql> create table if not exists t_article (
-> id int unsigned auto_increment,
-> category varchar(15) not null,
-> title varchar(100) not null,
-> content text not null,
-> created_at datetime not null,
-> updated_at datetime not null,
-> primary key (`id`),
-> index (`category`)
-> )
-> default charset=utf8 collate=utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)
删除数据表
mysql> drop table t_article;
Query OK, 0 rows affected (0.01 sec)
修改数据表引擎
mysql> alter table t_student engine=innodb;
Query OK, 0 rows affected (0.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
索引
创建表时添加索引
mysql> create table if not exists t_student (
-> id int unsigned auto_increment,
-> name varchar(35) not null default '',
-> phone char(11) not null default '',
-> code varchar(20) not null default '',
-> description varchar(255) not null default '',
-> primary key (`id`), // 添加主键索引
-> unique index index_code (`code`), // 添加唯一索引
-> index index_name_phone (`name`, `phone`), // 添加组合索引
-> fulltext index index_description (`description`) // 添加全文索引
-> )
-> engine=innodb
-> default charset=utf8 collate=utf8_general_ci;
Query OK, 0 rows affected (0.37 sec)
创建表后添加索引
alter table命令
mysql> alter table t_student
-> add primary key (`id`), // 添加主键索引
-> add unique key index_code (`code`), // 添加唯一索引
-> add index index_name_phone (`name`, `phone`), // 添加组合索引
-> add fulltext index index_description (`description`); // 添加全文索引
Query OK, 0 rows affected, 1 warning (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 1
create index命令
# 添加唯一索引
mysql> create unique index index_code on t_student (`code`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 添加组合索引
mysql> create index index_name_phone on t_student (`name`, `phone`);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 添加全文索引
mysql> create fulltext index index_description on t_student (`description`);
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除索引
alter table命令
mysql> alter table t_student
-> drop primary key, // 删除主键索引
-> drop index index_code, // 删除唯一索引
-> drop index index_name_phone, // 删除组合索引
-> drop index index_description; // 删除全文索引
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
drop index命令
# 删除唯一索引
mysql> drop index index_code on t_student;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除组合索引
mysql> drop index index_name_phone on t_student;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 删除全文索引
mysql> drop index index_description on t_student;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
数据
插入数据
mysql> insert into t_article (category, title, content, created_at, updated_at) values ('新闻', '第一篇新闻', '全国人民共同欢庆中秋节', '2018-09-24 23:45:00', '2018-09-24 23:45:00');
Query OK, 1 row affected (0.00 sec)
查询数据
mysql> select * from t_article where id>0 order by category limit 10 offset 0;
+----+----------+-----------------+-----------------------------------+---------------------+---------------------+
| id | category | title | content | created_at | updated_at |
+----+----------+-----------------+-----------------------------------+---------------------+---------------------+
| 1 | 新闻 | 第一篇新闻 | 全国人民共同欢庆中秋节 | 2018-09-24 23:45:00 | 2018-09-24 23:45:00 |
+----+----------+-----------------+-----------------------------------+---------------------+---------------------+
1 row in set (0.00 sec)
账号鉴权
设置密码
使用 mysqladmin -u [username] [-p] password [password] 命令.
vagrant@homestead:~/code/test$ mysqladmin -u root -p password "secret"
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
登录
使用 mysql -h [host] -u [username] -p 命令,host默认是本机,用户名默认是 root ,如果没有设置密码,且连接的是本机,直接使用 mysql 即可
vagrant@homestead:~$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1271
Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
添加用户授权
使用 grant [privileges] on [database].[table] to '[username]'@'[host]' identified by '[password]' with grant option; 命令
- privileges: 可使用的权限,全部权限可以设置为
all privileges, 权限列表可以参考官方权限说明 - database和table可以设置为
*表示全部 - host可以设置为
%表示所有主机 - identified by: 设置密码
- with grant option: 允许用户将自己的权限授权给其它用户
操作后必须使用 flush privileges 刷新权限,除非重启MySQL服务器
mysql> grant all privileges on *.* to 'guest'@'%' identified by 'guest' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
使用 guest 登录测试:
vagrant@homestead:~/code/test$ mysql -u guest -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22-0ubuntu18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
查看权限
使用 show grants for '[username]'@'[host]' 命令
mysql> show grants for 'guest'@'%';
+--------------------------------------------------------------+
| Grants for guest@% |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'guest'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 row in set (0.01 sec)
移除权限
使用 revoke [privileges] on [database].[table] from '[username]'@'[host]' 命令
mysql> revoke create,select on *.* from 'guest'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'guest'@'%';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for guest@% |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT, UPDATE, DELETE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'guest'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
用户重命名
使用 rename user '[username]'@'[host]' to '[new_username]'@'[new_host]' 命令
mysql> rename user 'guest'@'%' to 'guest_new'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select host, user from user where user = 'guest_new';
+-----------+-----------+
| host | user |
+-----------+-----------+
| localhost | guest_new |
+-----------+-----------+
1 row in set (0.00 sec)
修改密码
修改密码的方式有下面几种
- 直接更新
mysql.user表中数据
mysql> update user set authentication_string=password('111111') where user='guest_new';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 使用
set password for '[username]'@'[host]'=password('[password]')命令
mysql> set password for 'guest_new'@'localhost'=password('222222');
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 使用
mysqladmin -u [username] [-p] password [password]命令
vagrant@homestead:~$ mysqladmin -u guest -p password 123456
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
忘记密码
密码忘记后可以通过修改配置,设置无密码登录,登录后使用修改密码的第一种方法(更新库)进行修改
- 修改
my.cnf,在[mysqld]节点下增加skip-grant-tables
vagrant@homestead:~$ sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
...
[mysqld]
skip-grant-tables
- 重启MySQL服务
使用脚本重启
vagrant@homestead:~$ sudo /etc/init.d/mysql restart
或者先关闭再启动
# 关闭
vagrant@homestead:~$ mysqladmin -u root -p shutdown
Enter password:
# 启动
[root@iZ2zeisotv99f1nmihsv1fZ ~]# mysqld_safe &
[1] 19725
[root@iZ2zeisotv99f1nmihsv1fZ ~]# 180924 22:04:07 mysqld_safe Logging to '/data/mysql/mysql-error.log'.
180924 22:04:07 mysqld_safe Starting mysqld daemon with databases from /data/mysql
- 修改密码
mysql> update user set authentication_string=password('111111') where user='guest_new';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 还原第一步的配置修改
- 重启MySQL服务
删除用户
使用 drop user '[username]'@'[host]'
mysql> drop user 'guest'@'%';
Query OK, 0 rows affected (0.01 sec)
性能
影响MySql性能的因素
-
服务器硬件：CPU,内存,磁盘,网络
-
操作系统及文件系统
-
存储引擎
-
数据库参数配置：这项对性能的影响是最大的,比前三项加起来还要大
-
数据库结构设计和SQL语句
慢查询是大量数据库问题的罪魁祸首,很多效率低下的SQL就是因为数据库结构设计不合理造成的
数据库性能优化的重点就在于此项的优化
大促活动下影响数据库性能的因素
-
SQl查询速度
超高的QPS和TPS下效率低下的sql会极大影响性能
解决办法: 大多数的数据库问题都是由于慢查询造成的,都可以通过对sql的优化来解决
-
数据库连接数
大量的并发会造成数据库连接数被占满,数据库连接量大于实际操作量,造成大量连接sleep,反映到前端就是超时错误
解决办法: 在活动前调大连接数(max_connections 默认100)
-
CPU
超高的CPU使用率造成因CPU资源耗尽而出现宕机
解决办法: 提高CPU硬件
-
磁盘IO
大量的磁盘IO会造成磁盘性能突然下降,并且其它大量消耗磁盘性能的计划任务也会影响磁盘的使用
解决办法:
- 使用更快的磁盘设备
- 尽量不要在主数据库上进行备份
- 调整计划任务
- 平时做好磁盘维护,解决日常磁盘报警的问题
-
网卡流量
网卡IO被占满,造成后续业务无法连接数据库
解决办法:
- 减少从服务器的数量
- 进行分级缓存
- 避免使用 “select *”
- 分离业务网络和服务器网络
-
大表
大表的定义:
- 记录行数巨大,单表数据超过千万行
- 表数据文件巨大,表数据文件超过10G
大表对查询的影响:
- 慢查询: 很难在一定的时间内过滤出所需要的数据
来源少->区分度低->大量磁盘IO->降低磁盘效率
- 对DDL操作的影响
- 建立索引需要很长的时间
MuSQL版本<5.5建立索引会锁表,MySQL版本>=5.5虽不会锁表但会引起主从延迟
- 修改表结构需要长时间锁表
造成长时间的主从延迟,影响正常的数据操作,被阻塞
处理大表方法:
-
分库分表: 需要消耗大量人力物力,且会影响现有业务,有两个难点:
- 分表主键的选择
- 分表后跨分区数据的查询和统计
-
大表历史数据归档: 优点是会尽量减少对前后端业务的影响,可以将归档表单独存放服务器,有两个难点:
- 归档时间点的选择
- 如何进行归档操作(不能影响业务)
-
大事务
大事务是指运行时间比较长,操作的数据比较多的事务,比如理财产品每天计算全部用户前一天的收益
大事务的风险:
- 锁定太多的数据,造成大量的阻塞和锁超时
- 回滚所需要的时间比较长,且回滚中依然会锁定数据
- 执行时间常,容易造成主从延迟
处理大事务的方法
- 避免一次处理太多的数据,比如分批次处理
- 移除事务中不必要的select操作
分区
分区是一种表的设计模式，通俗地讲表分区是将一大表，根据条件分割成若干个小表。但是对于应用程序来讲，分区的表和没有分区的表是一样的。换句话来讲，分区对于应用是透明的，只是数据库对于数据的重新整理。
MySQL数据库在5.1版本时添加了对分区的支持，分区的过程是将一个表或索引分解为多个更小、更可管理的部分。就访问数据库的应用而言，从逻辑上讲，只有一个表或一个索引，但是在物理上这个表或索引可能由数十个物理分区组成。每个分区都是独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
MySQL数据库支持的分区类型为水平分区（指将同一个表中不同行的记录分配到不同的物理文件中），并不支持垂直分区（指将同一表中不同列的记录分配到不同的物理文件中）。此外，MySQL数据库的分区是局部分区索引，一个分区中既存放了数据又存放了索引。而全局分区是指，数据存放在各个分区中，但是所有数据的索引放在一个对象中。目前，MySQL数据库还不支持全局分区。
分区历史
-
MySQL 5.1版本开始支持基于整数列的分区表，
-
MySQL 5.5版本开始支持RANGE和LIST分区，支持TRUNCATE分区，新增COLUMNS关键词简化分区定义。
-
MySQL 5.6版本开始支持分区交换，支持显式分区查询，支持最大8182个分区或子分区。
-
MySQL 5.7版本引入本地分区策略，并标记弃用通用分区策略。
- 在MySQL 5.7.9版本中，InnoDB引入本地分区策略，由InnoDB存储引擎层内部管理访问分区表行为。
- 在MySQL 5.7.17版本中，MySQL将通用分区策略标记为弃用
- 在MySQL 8.0版本，不再允许MyISAM引擎使用分区表，因为MyISAM引擎不支持本地分区策略。
- 目前仅有InnoDB和NDB两种存储引擎支持本地分区策略。
分区策略
按照管理打开分区的行为可以将分区策略分为两类：
-
通用分区策略(Generic Partitioning), 由MySQL Server层负责控制访问分区。
通用分区策略问题：
- 当分区表第一次被访问时，无论该次访问需要操作多少个分区，都需要访问该分区表上所有分区，导致性能问题。当分区表上分区数量较大时，可能会因为打开文件数量超过参数open_file_limit限制而出错。
- 在对分区表进行维护时，需要同时维护原分区文件和新分区文件，如将分区表由100分区扩展至101分区时，需要2100+2101=402个文件描述符。
-
本地分区策略(Native Partitioning),由存储引擎层负责控制访问分区。
在MySQL开始支持分区表时，将分区表访问控制操作放在MySQL Server层实现，由于在文件管理/表管理等方面实现较为粗糙，存在严重性能问题。而不同存储引擎层使用不同存储机制/索引结构/访问控制(锁)，可以通过特殊设计来提升或优化特定的操作，将分区访问控制策略放置在存储引擎中实现更好。
分区优缺点
-
优点
在MySQL Server层分区表为一个表，而在MySQL存储引擎层分区表是多个表，因此有如下特点：
- 分区表对业务透明，只需要维护一个表的数据结构。
- DML操作加锁仅影响操作的分区，不会影响未访问分区。
- 通过分区交换快速将数据换入和换出分区表。
- 通过TRUNCATE操作快速清理特定分区数据。
- 通过强制分区仅访问特定分区数据，减少操作影响。
- 通过大数据量分区能有效降低索引层数，提高查询性能。
-
缺点
由于分区表在MySQL Server层为一个表，因此：
- DDL操作需要锁定所有分区，导致所有分区上操作都被阻塞。
- 当表数据量较小时，分区表和非分区表性能相近，分区表效果有限。
- 当表数据量较大时，对分区表进行DDL或其他运维操作难度大风险高。
- 分区表使用较少，存在未知风险多，BUG多，MySQL社区版本免费，横向扩展成本低，分库分表实现简单且中间件完善。
- 当单台服务器性能无法满足时，对分区表进行分拆的成本较高，而分库分表能很容易实现横向分拆。
- 当分区表操作不当导致访问所有分区时，会导致严重的性能问题，而分库分表操作不当仅影响访问的表。
- 使用分库分表可以有效降低运维操作影响，对1亿数据量表做DDL操作需要谨慎评估，而对10万数据量表做DDL操作可以默认其很快完成。
- 使用分库分表可以有效减小宕机或其他故障影响，将数据分库分表到10套集群上，一套集群发生故障仅影响业务的一成。
对于SQL Server和Oracle这些商业数据库，由于商业授权导致横向扩展成本较高，且分区表功能稳定，因此通过硬件扩展和分区来承担大数据量带来的负载，而对于MySQL，互联网企业有资源有能力将很多需求迁移到数据库外部实现，因此更追求MySQL使用过程中的简单稳定可靠，且通过堆服务器+分库分表更能有处理数据量爆炸式增长带来的性能问题。
分区类型
- RANGE分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段。
- LIST分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。
- HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
- KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
上述四种分区类型中，RANGE分区即范围分区是最常用的。RANGE分区的特点是多个分区的范围要连续，但是不能重叠，默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值。
分区操作
-
创建分区表并写入数据
当要分区的表有主键时，分区的字段必须是要包含在主键当中。这时候分区的字段要么是主键，要么把分区字段加入到主键中，从而形成复合主键。
# 创建分区表 mysql> create table staff ( -> id int(11) unsigned auto_increment comment 'ID', -> name varchar(15) not null comment '', -> birthday date not null comment '', -> primary key (`id`, `birthday`) -> ) engine = innodb default charset=utf8mb4 collate=utf8mb4_general_ci comment '' -> partition by range (year(birthday))( -> partition p0 values less than (1980), -> partition p1 values less than (1985), -> partition p2 values less than (1990), -> partition p3 values less than (1995), -> partition p4 values less than (2000), -> partition p5 values less than (2005), -> partition p6 values less than (2010), -> partition p7 values less than (2015), -> partition p8 values less than (2020), -> partition p9 values less than (2025), -> partition p10 values less than (2030) -> ); Query OK, 0 rows affected (0.16 sec) # 写入数据 mysql> INSERT INTO `staff` VALUES -> (1, 'A', '2003-10-15'), -> (2, 'B', '1997-11-05'), -> (3, 'C', '2009-03-10'), -> (4, 'D', '1989-01-10'), -> (5, 'E bike', '2014-05-09'), -> (6, 'F', '1987-06-05'), -> (7, 'G', '2011-11-22'), -> (8, 'H', '1992-08-04'), -> (9, 'I', '2006-09-16'), -> (10, 'J', '1998-12-25'); Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 # 分区表数据存储结构 root@90e65968cc28:/var/lib/mysql/data# ls -l total 1212 -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p0.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p1.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p10.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p2.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p3.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p4.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p5.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p6.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p7.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p8.ibd -rw-r----- 1 root root 98304 Aug 26 16:54 staff#P#p9.ibd -rw-r----- 1 root root 8626 Aug 26 16:54 staff.frm -
查询分区数据
mysql> select * from staff partition(p3); +----+------+------------+ | id | name | birthday | +----+------+------------+ | 8 | H | 1992-08-04 | +----+------+------------+ 1 row in set (0.01 sec) -
增加及删除分区
# 增加分区 mysql> alter table staff add partition (partition p11 values less than (2035)); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查询分区数据 mysql> select * from staff partition(p11); Empty set (0.00 sec) # 删除分区 mysql> alter table staff drop partition p11; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查询分区数据 mysql> select * from staff partition(p11); ERROR 1735 (HY000): Unknown partition 'p11' in table 'staff' -
拆分及合并分区
# 拆分分区 mysql> alter table staff reorganize partition p5 into ( -> partition s0 values less than (2002), -> partition s1 values less than (2005) -> ); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查询旧分区 mysql> select * from staff partition(p5); ERROR 1735 (HY000): Unknown partition 'p5' in table 'staff' # 查询新分区 mysql> select * from staff partition(s0); Empty set (0.00 sec) mysql> select * from staff partition(s1); +----+------+------------+ | id | name | birthday | +----+------+------------+ | 1 | A | 2003-10-15 | +----+------+------------+ 1 row in set (0.00 sec) # 合并分区 mysql> alter table staff reorganize partition s0,s1 into ( -> partition p5 values less than (2005) -> ); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 # 查询旧分区 mysql> select * from staff partition(s0); ERROR 1735 (HY000): Unknown partition 's0' in table 'staff' mysql> select * from staff partition(s1); # 查询新分区 ERROR 1735 (HY000): Unknown partition 's1' in table 'staff' mysql> select * from staff partition(p5); +----+------+------------+ | id | name | birthday | +----+------+------------+ | 1 | A | 2003-10-15 | +----+------+------------+ 1 row in set (0.00 sec) -
清空分区数据
# 查询分区数据 mysql> select * from staff partition(p5); +----+------+------------+ | id | name | birthday | +----+------+------------+ | 1 | A | 2003-10-15 | +----+------+------------+ 1 row in set (0.00 sec) # 清空分区数据 mysql> alter table staff truncate partition p5; Query OK, 0 rows affected (0.03 sec) # 查询分区数据 mysql> select * from staff partition(p5); Empty set (0.02 sec) -
交换分区
mysql5.6开始支持alter table..exchange partition语法，该语句允许分区或子分区中的数据与另一个非分区的表中的数据进行交换，如果非分区表中的数据为空，那么相当于将分区中的数据移动到非分区表中，若分区表中的数据为空，则相当于将外部表中的数据导入到分区中，即，哪边不为空，哪边就是被移出的，哪边为空，哪边就是装数据的。
要使用alter table…exchange partition语句，必须满足下面的条件：
- 要交换的表需要和分区表有着完全相同的表结构，但是要交换的表不能含有分区
- 在非分区表中的数据必须在交换的分区定义内（即要对上分区列的定义范围）
- 被交换的表中不能含有外键，或者其他的表含有对该表的外键引用
- 用户除了需要alter,insert,create权限外，还需要drop权限，此外，还有两个小的细节需要注意：
- 使用该语句时，不会触发交换表和被交换表上的触发器
- auto_increment列将被重置
# 创建相同结构的表 mysql> create table staff_archive ( -> id int(11) unsigned auto_increment comment 'ID', -> name varchar(15) not null comment '', -> birthday date not null comment '', -> primary key (`id`, `birthday`) -> ) engine = innodb default charset=utf8mb4 collate=utf8mb4_general_ci comment ''; Query OK, 0 rows affected (0.04 sec) # 查询分区数据 mysql> select * from staff partition(p3); +----+------+------------+ | id | name | birthday | +----+------+------------+ | 8 | H | 1992-08-04 | +----+------+------------+ 1 row in set (0.00 sec) # 查询新表数据 mysql> select * from staff_archive; Empty set (0.00 sec) # 交互分区 mysql> alter table staff exchange partition p3 with table staff_archive; Query OK, 0 rows affected (0.04 sec) # 查询新表数据 mysql> select * from staff_archive; +----+------+------------+ | id | name | birthday | +----+------+------------+ | 8 | H | 1992-08-04 | +----+------+------------+ 1 row in set (0.01 sec) # 查询分区数据 mysql> select * from staff partition(p3); Empty set (0.01 sec)
分区注意事项及适用场景
-
注意事项
- 分区字段必须是整数类型或解析为整数的表达式。
- 分区字段建议设置为NOT NULL，若某行数据分区字段为null，在RANGE分区中，该行数据会划分到最小的分区里。
- MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。
- Innodb分区表不支持外键。
- 更改sql_mode模式可能影响分区表的表现。
- 分区表不影响自增列。
-
适用场景
分区表适用于一些日志记录表。这类表的特点是数据量大、并且有冷热数据区分，可以按照时间维度来进行数据归档。这类表是比较适合使用分区表的，因为分区表可以对单独的分区进行维护，对于数据归档更方便。
项目开发中，分区表其实是很少用的，下面简单说明下几点原因：
- 分区字段的选择有限制。
- 若查询不走分区键，则可能会扫描所有分区，效率不会提升。
- 若数据分布不均，分区大小差别较大，可能性能提升也有限。
- 普通表改造成分区表比较繁琐。
- 需要持续对分区进行维护，比如到了6月份前就要新增6月份的分区。
- 增加学习成本，存在未知风险。
分库分表
基本思想就要把一个数据库切分成多个部分放到不同的数据库(server)上，从而缓解单一数据库的性能问题
-
垂直切分：指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面
优点：
- 拆分后业务清晰，拆分规则明确 - 系统之间整合或扩展容易 - 数据维护简单缺点：
- 部分业务表无法join，只能通过接口，增加系统复杂度 - 受业务不同存在单库性能瓶颈，不容易做数据扩展和提高性能 - 事务处理复杂 -
水平切分：将同一个表拆分到不同的库中
优点：
- 不存在单库大数据，高并发的性能瓶颈 - 对应用端透明，应用端改造比较少 - 合理拆分的话可以避免join操作跨库 - 提高了系统的稳定性和负载能力缺点：
- 拆分规则难以抽象。 - 分片事务一致性难以解决。 - 数据多次扩展难度跟维护量极大。 - 跨库join性能较差。
两种方式共同的缺点：
- 引入分布式事务的问题
- 跨节点Join 的问题
- 跨节点合并排序分页问题
- 横向扩容的问题
- 主键避重
分库
- 水平分库
以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中
- 每个库的结构都一样；
- 每个库的数据都不一样，没有交集；
- 所有库的并集是全量数据；
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解
- 垂直分库
以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
- 每个库的结构都不一样；
- 每个库的数据也不一样，没有交集；
- 所有库的并集是全量数据；
场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。
分表
- 水平分表
以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
- 每个表的结构都一样；
- 每个表的数据都不一样，没有交集；
- 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。
- 垂直分表
以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
- 每个表的结构都不一样；
- 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
- 所有表的并集是全量数据；
场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。
陕公网安备61011302000990号