导入数据

  • 使用2021微信大数据挑战赛的user_action,feed_info,test数据
  • 准备工作
    • mysql配置文件设置：在my.cnf的[mysqld]下添加secure_file_priv=/usr/local/mysql/data/csv用来指定存放csv数据表的目录，并给它file权限
    • 将相关csv文件放在该目录下
    • 创建user_action,feed_info,test表，表中列名和类型与csv表中列名和类型一致
  • load data infile '/usr/local/mysql/data/csv/user_action.csv' into table user_action fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;

image.pngimage.png

  • load data infile '/usr/local/mysql/data/csv/feed_info2.csv' into table feed_info2 fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;

image.pngimage.png

  • load data infile '/usr/local/mysql/data/csv/test.csv' into table test fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 rows;

image.pngimage.png

建立索引并研究其效果

单键索引

  • 建索引

    1. create index idx_useraction_userid on user_action(userid);
    2. create index idx_useraction_feedid on user_action(feedid);
    3. create index idx_useraction_date on user_action(date_);
    4. create index idx_useraction_play on user_action(play);
    5. create index idx_useraction_stay on user_action(stay);

  • 删索引

    1. drop index idx_useraction_userid on user_action;
    2. drop index idx_useraction_feedid on user_action;
    3. drop index idx_useraction_date on user_action;
    4. drop index idx_useraction_play on user_action;
    5. drop index idx_useraction_stay on user_action;

  • 查看索引show index from user_action;

image.png

查询

注：user_action0没有任何索引

  • select count(*) from user_action where userid=8;

image.pngimage.png

  • select count(*) from user_action where feedid=123;

image.png

  • ``select count(*) from user_action where date_=9;

image.png

复合索引

  • 先删去所有索引

  • 建复合索引useraction(userid,feedid,date)

    1. create index idx_useraction_ufd on user_action(userid,feedid,date_);

    复合索引使用注意事项

  • 复合索引是在多个列上建一个索引，按照列的顺序建立索引：先按第一列(userid)排序，若userid相同则按照feedid排序，若feedid相同则按照date_排序

  • select * from user_action where userid=8 and feedid=100 and date_=10;


  • 查询