mysql索引,分区,导入相关.docxVIP

Mysql 索引，分区，导入相关说明1、数据的导入方式mysql -h localhost -u test -p123456 C:\Users\ljs\Desktop\images.sql 执行sql脚本,可以有2种方法: 第一种方法: 在命令行下(未连接数据库),输入 mysql -h localhost -u root -p123456 F:\hello world\niuzi.sql (注意路径不用加引号的!!) 回车即可. 第二种方法: 在命令行下(已连接数据库,此时的提示符为 mysql ),输入 source F:\hello world\niuzi.sql (注意路径不用加引号的) 或者 \. F:\hello world\niuzi.sql (注意路径不用加引号的) 回车即可2、分区所以查看1、查看Mysql表分区语句SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM INFORMATION_SCHEMA.partitions WHERE TABLE_SCHEMA = schema() AND TABLE_NAME=table; 2、查看mysql表的索引情况show index from table_name3、分区的增删修改1.测试添加分区和删除分区###添加删除range分区(1)创建一个分区：CREATE TABLE titles ( emp_no INT NOT NULL, title VARCHAR(50) NOT NULL, from_date DATE NOT NULL, to_date DATE, KEY (emp_no), PRIMARY KEY (emp_no,title, from_date)) partition by range columns(from_date)(partition p01 values less than (1985-12-31),partition p02 values less than (1990-12-31),partition p03 values less than (1995-12-31),partition p04 values less than (2000-12-31),partition p05 values less than (2005-12-31),partition p06 values less than (2010-12-31),partition p07 values less than (2015-12-31),partition p08 values less than (2020-12-31),partition p09 values less than (2025-12-31),partition p10 values less than (2030-12-31));导入数据mysql source titles.sql(2)添加分区：备注：不能超过p04的范围，严格递增每个分区，即最小不能小于前一个分区下面新曾了两个分区n01和n02 alter table titles reorganize partition p04 into( partition n01 values less than(1997-12-31), partition n02 values less than(1998-12-31), partition p04 values less than(2000-12-31) );(3)删除分区：删除分区即删除数据。mysql select count(*) from titles where from_date1985-12-31;+----------+| count(*) |+----------+| 18238 |+----------+1 row in set (0.09 sec)mysql alter table titles drop partition p01;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql select count(*) from titles where from_date1985-12-31;+----------+| count(*) |+----------+| 0 |+----------+1