mysql中遇到的问题

mysql中复制表

Create table student_copy (Select * from student LIMIT 0,10000);

Insert into student_copy select * from student limit 1000000,1500000

当mysql中数据量过大时 

1. select count(*) from student 用了9秒 。当时表中除了主键，没有其他索引

用explain 查看 发现用的主键 进行查询，使用主键查询会比其他索引慢。

后来在其他字段上添加了索引

发现查询的字段变了，变成字段长度最小的，速度也只有0.6秒了

count(*) 查询的速度和索引和字段长度有关，mysql 会使用最小的字段长度进行查询。

2.联合索引 查询不能使用or 用了会索引失效

  explain select * from student t where  t.teacher_name = '声萨家' or t.cid = '964468287770387503'

3.当使用时间创建索引时，查询时间范围，如果数量过多 索引会失效

explain SELECT * from data_sensor_realtime where create_time BETWEEN '2018-08-27 14:30:27' and  '2018-08-28 00:00:00'

 SELECT count(*) from data_sensor_realtime where create_time BETWEEN '2018-08-27 14:30:27' and  '2018-08-28 00:00:00'

查了20万左右的数据索引直接失效了，因为数据库认为全表扫描比扫描索引更快

 explain SELECT * from data_sensor_realtime where create_time BETWEEN '2018-08-27 16:30:27' and  '2018-08-28 00:00:00'

如果数据量少，索引有效。

4. 根据时间统计方法 根据要求将时间转换为不同的格式

    。。。。

    <choose>
            <when test="dateType == 'miniute'">
              ddh.create_time addedTime  ,
            </when>
            <when test="dateType == 'time'">
                DATE_FORMAT(ddh.create_time,'%Y-%m-%d %H:00:00') addedTime,
            </when>
            <when test="dateType == 'day'">
                DATE_FORMAT(ddh.create_time,'%Y-%m-%d') addedTime,
            </when>
            <otherwise>
                DATE_FORMAT(ddh.create_time,'%Y-%m-01') addedTime,
            </otherwise>
        </choose>

。。。    GROUP BY dev.device_id ,addedTime //进行分组

5. 列转行

SELECT
device_data_hour_id,
MAX(CASE sensor_type_id WHEN 1 THEN sensor_value ELSE NULL END  ) temper,
MAX(CASE sensor_type_id WHEN 2 THEN sensor_value  ELSE NULL END) humidity,
MAX(CASE sensor_type_id WHEN 3 THEN sensor_value ELSE NULL END) pm25,
MAX(CASE sensor_type_id WHEN 4 THEN sensor_value  ELSE NULL END) pm10,
MAX(CASE sensor_type_id WHEN 12 THEN sensor_value ELSE NULL END) co
from data_sensor_hour
GROUP BY device_data_hour_id LIMIT 10

或者
SELECT
device_data_hour_id,
MAX(if(sensor_type_id = 1,sensor_value,null)) temper,
MAX(if(sensor_type_id = 2,sensor_value,null)) humidity,
MAX(if(sensor_type_id = 3,sensor_value,null)) pm25,
MAX(if(sensor_type_id = 4,sensor_value,null)) pm10,
MAX(if(sensor_type_id = 12,sensor_value,null)) co
from data_sensor_hour
GROUP BY device_data_hour_id LIMIT 10

使用GROUP_CONCAT 然后自己对值进行解析

select  device_data_realtime_id ,
GROUP_CONCAT(sensor_type_id  ,":"  ,sensor_type   ORDER BY sensor_type_id asc  Separator "-" ) 
from data_sensor_realtime GROUP BY device_data_realtime_id LIMIT 20

6 行转列

select user_name, '语文' as couser, cn_score as score from  test_tb_grade2
union  all select user_name, '数学' as couser,  math_score as score from  test_tb_grade2
union  all select user_name, '英语' as couser,  en_score as score from  test_tb_grade2
ORDER BY user_name
;

只要把列的别名同一起来并赋上自己需要的值就行。

7 int后面的长度

int 后面的长度 只是用来显示的 长度，并不影响他的长度和所占用空间，int(1)不是代表他只能存0和1，而是他在某些工具上的显示长度是一位，超过1的值 依然可以存储。