MySQL表空间

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;

最后编辑于：2020.04.28 09:38:21

禁止转载，如需转载请通过简信或评论联系作者。

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,117评论 4赞 362
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,328评论 1赞 293
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 108,839评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,007评论 0赞 206
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,384评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,629评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,880评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,593评论 0赞 198
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,313评论 1赞 243
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,575评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,066评论 1赞 260
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,392评论 2赞 253
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,052评论 3赞 236
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,082评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,844评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,662评论 2赞 274
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,575评论 2赞 270