mysql表数据量很大创建索引时的问题 mysql 数据量多 不走索引

MySQL的学习笔记：数据与索引

目录

• 数据操作

• 插入数据

• 实例

• 删除数据

• 实例

• 修改数据

• 实例

• 索引

• 索引分类与设计原则
• 索引的操作
• 索引的优缺点

• MySQL基础系列

数据操作

数据的增删改查。

插入数据

语法格式一：INSERT INTO 表名[(字段列表)] VALUES(值列表)
• 插入一条记录，只对部分字段赋值
insert into 表名(字段A, 字段B, 字段C) values(值A, 值B, 值C);
• 插入一条记录，对所有字段都赋值
insert into 表名 values(值A, 值B, 值C..., 值F);
• 一次插入多条记录
insert into 表名[(字段列表)] values(值列表1),(值列表2),…(值列表n);

语法格式二：insert into 目标表名[(字段列表1)]
select (字段列表2) from 源表 where 条件表达式
该语句向表中插入从另一个或多个表查询的结果集。

字段列表1与字段列表2的长度必须相同，且结构尽量相似，如果两个表的结构完全相同，则可以省略字段列表。

插入有约束的数据

1. 向默认值约束字段插入数据时，字段值可以使用default关键字，表示插入的是该字段的默认值。
2. 表之间存在外键约束时，外键的值要么是null，要么是父表中主键字段值。
3. 向auto_increment字段插入数据时，建议插入NULL值，此时将向自增型字段插入下一个编号。

replace与insert的区别：使用replace向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则先删除已有记录，然后再插入新记录（注意：已有记录删除时也不能违背外键约束条件）。

实例

mysql> insert into department values
    -> ('201','艺术传媒学院'),
    -> ("202","管理经济学院"),
    -> ("301","化学工程学院");
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from department;
+---------------+-----------------+
| Department_id | Department_name |
+---------------+-----------------+
| 201           | 艺术传媒学院    |
| 202           | 管理经济学院    |
| 301           | 化学工程学院    |
+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> insert into department values('101','信息技术学院');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values("11","软件工程",2017,"101"),("12","信息工程",2017,"101"),("21","会计",2018,"202"),("22","金融工程",2016,"202");
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> create table class1 like classes;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from class1;
Empty set (0.01 sec)
！需要指出的是复制表结构语句是不会复制表数据的。

mysql> insert into class1 select * from classes where department_id='101';
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from class1;
+----------+------------+------+---------------+
| Class_id | Class_name | Year | Department_id |
+----------+------------+------+---------------+
| 11       | 软件工程   | 2017 | 101           |
| 12       | 信息工程   | 2017 | 101           |
+----------+------------+------+---------------+
2 rows in set (0.00 sec)

删除数据

语法格式:
delete from 表名 [where 条件表达式];
说明：如果没有指定where条件，将删除所有的记录；如果指定了where条件，将按照指定的条件进行删除。

从多表中删除有外键约束的记录时，外键级联规则选项设置为cascade 。

清空某个表，使用truncate语句，语法格式如下：
truncate [table] 表名；
说明：truncate table用于完全清空一个表。清空记录的表如果是父表，truncate命令将永远执行失败。

实例

mysql> delete from department where department_id='202';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`course`.`classes`, CONSTRAINT `fk_cla_depart` FOREIGN KEY (`Department_id`) REFERENCES `department` (`Department_id`))

mysql> alter table classes drop foreign key fk_cla_depart;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table classes add
    -> constraint fk_cla_depart foreign key(Department_id) references department(Department_id)
    -> on delete cascade on update cascade;
Query OK, 4 rows affected (0.08 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> delete from department where department_id='202';
Query OK, 1 row affected (0.01 sec)

修改数据

语法格式:
update 表名 set 字段名1=值1,字段名2=值2,….. ,字段名n=值n [where 条件表达式];
其中，set子句指定了要修改的字段以及该字段修改后的值。
where子句指定了表中需要修改的记录。若省略了where，则修改表中的所有记录。

实例

mysql> update classes set class_name='大数据' where class_id<20;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

索引

索引的本质是什么？
为数据建立一个类似字典的音序表、图书目录一样的存储结构，这就是索引。
其中，字段值就像目录中的标题，指针就相当于页码。

所以索引本质上是一种优化查询的数据结构。

索引分类与设计原则

Ø 普通索引（index）
index是MySQL的基本索引，允许字段值重复，一个数据表中可以有多个普通索引。

Ø 唯一性索引（unique）
相比于普通索引的特点在于，唯一性索引所对应列的值必须唯一，但允许空值。可以存在多个

Ø 主键索引（primary key）
特殊的唯一性索引，不允许有空值，且一个数据表只能有一个主键索引。

Ø 全文索引（fulltext）
full text类型，可以在char、varchar或text类型上创建，在中文语境下不太适用。

Ø 空间索引（spatial）

Ø 单列索引和组合索引
单列索引是只包含一个列的索引，组合索引是多个字段组合上创建的。

设计原则
Ø 索引并非越多越好
Ø 字段的离散度越高，该字段越适合选作索引的关键字
比如性别，仅有男女，就不适合制作索引。
Ø 经常排序和分组的数据列、两个表的连接字段应该建立索引
Ø 更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引

索引的操作

在创建表的同时创建索引

create table 表名 (
字段名1 数据类型 [约束条件],
字段名2 数据类型 [约束条件], …
[其他约束条件], …
[ unique | fulltext ] index [索引名] ( 字段名 [(长度)]
[ asc | desc ] )
) engine=存储引擎类型 default charset=字符集类型
//“asc”与“desc”为可选参数，分别表示升序与降序
[]可选，()必选

在已有表上创建索引

方法一：
create [ unique | fulltext ] index 索引名 on 表名 ( 字段名 [(长度)] [ asc | desc ] )
创建普通索引示例
Create INDEX teacher_id_index ON teacher( teacher_id );
创建唯一性索引示例
Create UNIQUE INDEX teacher_id_index ON teacher( teacher_id);

方法二：
alter table 表名 add [ unique | fulltext ] index 索引名 ( 字段名 [(长度)] [ asc | desc ] )

删除索引
drop index 索引名 on 表名

索引的优缺点

优点：

1. 可以大大加快数据库的的检索速度。
2. 可以加速表之间的连接。
3. 在使用分组和排序字句进行检索时，同样可以显著减少查询中分组和排序的时间。
4. 通过使用索引，可以提高系统性能。

缺点：

1. 创建索引和维护索引需要耗费时间。
2. 索引是需要占用物理内存的。
3. 当对表中的数据进行增加、删除和修改的时候，索引也需要进行动态维护，这样就降低了数据库的维护速度。

MySQL基础系列

【MySQL】1.0 数据库操作【MySQL】2.0 数据类型【MySQL】3.0 数据表操作【MySQL】4.0 数据与索引