MySQL表空间

作者: 吃可爱长大鸭 | 来源:发表于2019-12-04 19:47 被阅读0次

MySQL InnoDB表空间加密
mysql表空间
MySQL表空间
mysql 表空间收缩_MySQL 清除表空间碎片
MySql--InnoDB的表空间
MySQL 临时表
mysql表占用空间
MySql 独立表空间和共享表空间
MySQL:8.0.21版本特性
MySQL（Mariadb）总结5 - MySQL(MariaD

1.主键索引

2.普通索引

*   联合索引

*   前缀索引

3.唯一索引

explain 分析执行效率(优化SQL语句)

1.全表扫描

select * from tb1;

mysql> explain select * from world.city;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

2.索引扫描

*   index：全索引扫描

mysql> explain select CountryCode from world.city;

*   range：范围查询

mysql> explain select * from world.city where population > 30000000;

*   ref:使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。

mysql> explain select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

*   eq_ref:连表查询(内连接)，并且等价条件是主键或者唯一键

join B 
on A.sid=B.sid

*   **const、system**：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。

mysql> explain select * from world.city where id=1;

*   null：查询的值，不在范围内（根本不工作）

explain select * from world.city where population > 3000000000000000000000000000000000000000000000;

Extra（扩展） Using temporary Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）

mysql> explain select * from city where countrycode='CHN' order by population;

解决方案

mysql> explain select * from city where population>30000000 order by population;

Using join buffer

注意：

* * *

row：越小越好

key_len:越小越好

使用前缀索引，控制key_len

索引建立的规范（原则）

1.唯一索引

select count(*) from world.city;
select count(distinct(countrycode)) from world.city;
select count(distinct(countrycode,population)) from world.city;

2.联合索引

3.为经常需要排序、分组和联合操作的字段建立索引

4.为常作为查询条件的字段建立索引

*   唯一索引

*   联合索引

*   普通索引

5.尽量使用前缀索引

6．限制索引的数目索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新

7.删除不再使用或者很少使用的索引表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

走索引和不走索引的情况

1.全表扫描（不执行）

mysql> explain select * from world.city；

2.查询结果集是原表中的大部分数据，应该是25％以上

mysql> explain select * from world.city where population > 3000000;

mysql> explain select * from world.city where population > 300 limit 10;

3.索引本身失效，或者损坏

删除索引，重建索引

4.使用列名，进行条件运算

mysql> explain select * from world.city where id-1=9;

5.隐式转换，不走索引

mysql> create table suibian(id int,QQ varchar(11));
mysql> insert into suibian values(1,'100'),(2,'110'),(3,120);
mysql> alter table suibian add index idx_qq(qq);

mysql> explain select * from suibian where qq='120';
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | suibian | ref  | idx_qq        | idx_qq | 36      | const |    1 | Using index condition |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-----------------------+

mysql> explain select * from suibian where qq=120;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | suibian | ALL  | idx_qq        | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

6.模糊查询like %在最前面的时候，不管结果集是多少

mysql> explain select * from world.city where countrycode like '%HN';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | city  | ALL  | NULL          | NULL | NULL    | NULL | 4188 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

建议，不使用MySQL，而是使用elasticsearch

7.<> 、 not in不走索引

mysql> explain select * from world.city where population <> 102361;

union all
limit

8.联合索引，单独引用联合索引里非第一位置的索引列

按照创建索引的顺序，查询数据

MySQL存储引擎-innodb

img

查看存储引擎

mysql> show engines;

查看整个数据库中，哪些表是innodb

mysql> select table_schema,table_name,engine from information_schema.tables where engine='innodb';

查看整个数据库中，哪些表是myisam

mysql> select table_schema,table_name,engine from information_schema.tables where engine='myisam';

innodb和myisam的物理区别

#myisam
[root@db01 mysql]# ll user.*
-rw-rw---- 1 mysql mysql 10684 11月 28 14:43 user.frm     #表结构
-rw-rw---- 1 mysql mysql  1408 12月  2 08:48 user.MYD     #表空间
-rw-rw---- 1 mysql mysql  2048 12月  2 08:48 user.MYI

#innodb
[root@db01 zls]# ll
-rw-rw---- 1 mysql mysql  8556 12月  4 11:02 zls1.frm    #表结构
-rw-rw---- 1 mysql mysql 98304 12月  4 11:02 zls1.ibd    #表空间

核心特性

*   MVCC 多版本并发控制

*   事务

*   行级锁

*   热备

*   CSR（自动故障恢复）

myisam

查看当前使用的存储引擎

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

mysql> show create table zls.zls1;

修改配置文件

default-storage-engine=<Storage Engine>

企业案例

**项目背景：**

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。

**小问题不断：**

*   1、表级锁：对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。

*   2、不支持故障自动恢复（CSR）：当断电时有可能会出现数据损坏或丢失的问题。

如何解决？？？？

1.准备新环境，使用mysql5.6.44

./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
</pre>

2.备份导出数据

mysqldump -B 程序库> /tmp/full.sql
</pre>

3.修改存储引擎

sed -i 's#MyISAM#InnoDB#g' /tmp/full.sql

vim /tmp/full.sql
:%s#MyISAM#InnoDB#g 
</pre>

4.将备份的数据导入新环境

mysql < /tmp/full.sql
</pre>

5.修改代码，连接数据库的IP

6.先停库

7.截取全备，到停库之间的新数据

8.恢复到新数据库，开启业务

innodb-表空间

1.共享表空间（5.5版本以后出现共享表空间概念）

*   系统数据

*   临时表

*   undo log（事务日志）

2.独立表空间

*   生产数据，用户数据

#共享表空间（默认大小12M）
[root@db01 data]# ll
-rw-rw---- 1 mysql mysql 79691776 12月  4 11:35 ibdata1

mysql> show variables like '%path%';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
| ssl_capath            |                        |
| ssl_crlpath           |                        |
+-----------------------+------------------------+

[root@db01 data]# du -sh ibdata1 
76M ibdata1

共享表空间切割

[root@db01 data]# vim /etc/my.cnf
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

重启数据库

[root@db01 data]# /etc/init.d/mysqld restart


报错原因：设置共享表空间的大小，50M小于实际表空间大小76M

解决方法：修改配置文件中的50M，76M

innodb_data_file_path=ibdata1:76M;ibdata2:50M:autoextend

注意：ibdata1 已产生的共享表空间大小一定要与配置文件中的大小一致，不能多，也不能少

开启独立表空间

mysql> show variables like '%per_table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+

企业案例

在没有备份数据的情况下，突然断电导致表损坏，打不开数据库。

解决思路：

1.准备新环境

2.将旧数据导入到新环境

3.需要知道建表语句（表结构）

管开发要建表语句

CREATE TABLE `city_new` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `idx_pop` (`Population`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;

#删除新表的表空间
mysql> alter table world.city_new discard tablespace;

#拷贝旧表空间
[root@db02 world]# cp -a city.ibd city_new.ibd

#导入表空间
mysql> alter table world.city_new import tablespace;

mysql> select * from world.city_new;

#删除旧表
[root@db02 world]# rm -fr city.ibd  city.frm

#新表改名
mysql> alter table world.city_new rename world.city;

MySQL InnoDB表空间加密
从 MySQL5.7.11开始，MySQL对InnoDB支持存储在单独表空间中的表的数据加密。此功能为物理表空间...
mysql表空间
表空间是一种逻辑结构，它位于表和数据文件之间 1. System Tablespace SYSTEM表空间包含的...
MySQL表空间
explain 分析执行效率(优化SQL语句) 1.全表扫描 2.索引扫描 Extra（扩展） Using te...
mysql 表空间收缩_MySQL 清除表空间碎片
MySQL 清除表空间碎片的实例详解碎片产生的原因 (1)表的存储会出现碎片化，每当删除了一行内容，该段空间就会...
MySql--InnoDB的表空间
MySql--InnoDB的表空间具体细节请去掘金购买《MySQL 是怎样运行的：从根儿上理解 MySQL》 ...
MySQL 临时表
临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。使用PHP脚本来创建MySQL临时表...
mysql表占用空间
MySql 独立表空间和共享表空间
独立表空间和共享表空间共享表空间: 数据库的所有表数据，索引文件全部放在一个文件中，默认共享表空间的文件路径在d...
MySQL:8.0.21版本特性
一、新特性 Mysql和Innodb增强： 1、undo可以新增表空间mysql> CREATE UNDO TAB...
MySQL（Mariadb）总结5 - MySQL(MariaD
MySQL存储引擎表类型: InnoDB: 处理大量的短期事务; 数据存储于"表空间(table space)"...