MySQL表空间

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

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表空间

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

1.全表扫描

2.索引扫描

解决方案

索引建立的规范（原则）

1.唯一索引

2.联合索引

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

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

5.尽量使用前缀索引

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

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

走索引和不走索引的情况

1.全表扫描（不执行）

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

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

删除索引，重建索引

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

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

MySQL存储引擎-innodb

查看存储引擎

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

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

innodb和myisam的物理区别

查看当前使用的存储引擎

修改配置文件

企业案例

innodb-表空间

共享表空间切割

重启数据库

开启独立表空间

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

MySQL表空间

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

1.全表扫描

2.索引扫描

解决方案

索引建立的规范（原则）

1.唯一索引

2.联合索引

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

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

5.尽量使用前缀索引

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

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

走索引和不走索引的情况

1.全表扫描（不执行）

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

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

删除索引，重建索引

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

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

MySQL存储引擎-innodb

查看存储引擎

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

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

innodb和myisam的物理区别

查看当前使用的存储引擎

修改配置文件

企业案例

innodb-表空间

共享表空间切割

重启数据库

开启独立表空间

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

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

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