索引：一种特殊的表
空表：只有结构，在DBMS里面有记录，在物理磁盘里面是不占有空间的
插入代价INSERT 插入后所有数据都要后移？不设置空余空间，有空间使用率这个参数(MySQL中对应那一个？)
注意：A B三个想设置为主键，B取外键，那么最好这样设置主键B、A,
这样外键索引包含主键索引里面了，节省了索引体积，方便查询
外键索引一般用在什么时候？连接查询的时候！join
要点：（1）硬盘有数个盘片，每盘片两个面，每个面一个磁头
（2）盘片被划分为多个扇形区域即扇区
（3）同一盘片不同半径的同心圆为磁道
（4）不同盘片相同半径构成的圆柱面即柱面
（5）公式：存储容量＝磁头数×磁道（柱面）数×每道扇区数×每扇区字节数
（6）信息记录可表示为：××磁道（柱面），××磁头，××扇区
在许多场合， 磁道和柱面可以互换使用
位-字节-字-块Block-桶Bucket-卷Volume
区：空间分配的基本单位
块：读写的基本单位
优化MySQL服务器：
1硬件优化 增加内存，提高硬盘的读写速度 使用多个磁盘存储数据（并行取数据快）
2优化MySQL参数 P393
以主键排列的记录，其物理位置基本都是连续的
先建外键，在建主键，这样才会有外键索引和主键索引
连接查询
select f.TerminalID,f.average,p.ProbeName
from finaldata_lowfreq_magn_copy f ,probe p where f.ProbeID = 60005
发现建立Time ProbeID的主键，ProbeID的外键索引就会存在
建立ProbeID Time 的主键，ProbeID的外键索引就会自动消失
ALTER TABLE finaldata_lowfreq_sound_copy DROP FOREIGN KEY finaldata_lowfreq_sound_copy;
ALTER TABLE finaldata_lowfreq_sound_copy
DROP PRIMARY KEY,
DROP INDEX finaldata_lowfreq_sound_copy;
ALTER TABLE finaldata_lowfreq_sound_copy
ADD PRIMARY KEY (ProbeID, Time);
ALTER TABLE finaldata_lowfreq_sound_copy ADD CONSTRAINT finaldata_lowfreq_sound_copy FOREIGN KEY (ProbeID) REFERENCES probe (ProbeID) ON DELETE RESTRICT ON UPDATE RESTRICT;
explain select t.Time,t.average,t.zero_crossing_rate,t.Ring_down_count,t.average_crossing_rate,t.energy_distribution1,t.energy_distribution2,t.energy_distribution3,t.energy_distribution4,t.peak_frequency,t.peak_amplitude
from finaldata_lowfreq_sound t
where t.TerminalID = 60010 and t.ProbeID = 60010 and t.Time >= 1472659200 and t.Time <=1475251200 order by t.Time asc;
explain select t.Time,t.average,t.zero_crossing_rate,t.Ring_down_count,t.average_crossing_rate,t.energy_distribution1,t.energy_distribution2,t.energy_distribution3,t.energy_distribution4,t.peak_frequency,t.peak_amplitude
from finaldata_lowfreq_sound_copy t
where t.TerminalID = 60010 and t.ProbeID = 60010 and t.Time >= 1472659200 and t.Time <=1475251200 order by t.Time asc;
explain select t.Time,t.average,t.zero_crossing_rate,t.Ring_down_count,t.average_crossing_rate,t.energy_distribution1,t.energy_distribution2,t.energy_distribution3,t.energy_distribution4,t.peak_frequency,t.peak_amplitude
from finaldata_lowfreq_sound_copy t
where t.ProbeID = 60010 and t.Time >= 1472659200 and t.Time <=1475251200 order by t.Time asc;
explain select t.Time,t.average,t.zero_crossing_rate,t.Ring_down_count,t.average_crossing_rate,t.energy_distribution1,t.energy_distribution2,t.energy_distribution3,t.energy_distribution4,t.peak_frequency,t.peak_amplitude
from finaldata_fullfreq_magn t
where t.TerminalID = 60010 and t.ProbeID = 60010 and t.Time >= 1472659200 and t.Time <=1475251200 order by t.Time asc;
explain select t.Time,t.average,t.zero_crossing_rate,t.Ring_down_count,t.average_crossing_rate,t.energy_distribution1,t.energy_distribution2,t.energy_distribution3,t.energy_distribution4,t.peak_frequency,t.peak_amplitude
from finaldata_fullfreq_magn_copy t
where t.TerminalID = 60010 and t.ProbeID = 60010 and t.Time >= 1472659200 and t.Time <=1475251200 order by t.Time asc;
explain select t.Time,t.average,t.zero_crossing_rate,t.Ring_down_count,t.average_crossing_rate,t.energy_distribution1,t.energy_distribution2,t.energy_distribution3,t.energy_distribution4,t.peak_frequency,t.peak_amplitude
from finaldata_fullfreq_magn_copy t
where t.ProbeID = 60010 and t.Time >= 1472659200 and t.Time <=1475251200 order by t.Time asc;
7.1 1467302400
8.1 1469980800
9.1 1472659200
10.1 1475251200
finaldata_acceleration_ibfk_1
外键建立失败，猜测该表中ProbeID出现了Probe中不存在的号码，用SQL语句把他找出来：
select ProbeID from middledata_fullfreq_magn where ProbeID not in (SELECT ProbeID from probe)
查出来是2575号
找出这条记录select * from middledata_fullfreq_magn where ProbeID = 2575
并删除之，再次执行添加外键的工作，大功告成！
若有收获，就点个赞吧
0 人点赞
