MySQL数据库原理学习(十七)
2023-06-13 09:13:59 时间
2.7 索引设计原则
1). 针对于数据量较大,且查询比较频繁的表建立索引。
2). 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
3). 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
4). 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
5). 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
6). 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
7). 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。
3. SQL优化
3.1 插入数据
3.1.1 insert
如果我们需要一次性往数据库表中插入多条记录,可以从以下三个方面进行优化。
insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....
1). 优化方案一
批量插入数据
Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
2). 优化方案二
手动控制事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
commit;
3). 优化方案三
主键顺序插入,性能要高于乱序插入。
主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3
主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89
3.1.2 大批量插入数据
如果一次性需要插入大批量数据(比如: 几百万的记录),使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。操作如下:
可以执行如下指令,将数据脚本文件中的数据加载到表结构中:
-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 执行load指令将准备好的数据,加载到表结构中CREATE TABLE `tb_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`birthday` DATE DEFAULT NULL,
`sex` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;elds
terminated by ',' lines terminated by '\n' ;
示例演示:
A. 创建表结构
CREATE TABLE `tb_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`birthday` DATE DEFAULT NULL,
`sex` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
B. 设置参数
-- 客户端连接服务端时,加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
C. load加载数据
load data local infile '/root/load_user_100w_sort.sql' into table tb_user
fields terminated by ',' lines terminated by '\n' ;
我们看到,插入100w的记录,17s就完成了,性能很好。
在load时,主键顺序插入性能高于乱序插入
相关文章
- MySQL签到表设计实现(mysql签到表设计)
- MySQL连接SQLServer:实现跨平台数据交互(mysql连接sqlserver)
- MySQL索引优化:提高性能的奥秘(mysql索引修改)
- MySQL 主从复制:实现数据安全可靠的分布式存储(mysql主从模式)
- 中的数据改变MySQL数据库中的数据:妙用技巧!(修改mysql数据库)
- MySQL数据库:行锁定的魔法(mysql数据库行锁)
- 管理MySQL分区管理:优化数据库性能(mysql的分区)
- 马士兵mysql视频学习,轻松掌握mysql数据库操作技巧(马士兵mysql视频)
- 阿里云启动MySQL:轻松搭建可靠的数据库服务(阿里云启动mysql)
- 学习MySQL数据库:第四版详解(mysql第4版)
- Mysql可视化界面:让数据库管理更轻松(mysql可视化界面)
- MySQL数据库的基础知识:一份文献资料简述(mysql数据库文献)
- 使用MySQL轻松建立数据库(mysql建数据库)
- MySQL数据表前缀谨慎使用的重要性(mysql数据表前缀)
- MySQL如何恢复数据库?(mysql如何还原数据库)
- 阿里云专注MySQL云数据库解决方案(mysql 阿里云)
- C语言使用MySQL实现数据库查询(c 与mysql连接查询)
- MySQL中的concat函数实现字段拼接(mysql中cancat)
- 如何在MySQL中存储和管理邮件正文(Body)(mysql 中 body)
- AMD处理器上部署Mysql快速创建数据库环境(AMD处理器装mysql)
- 2017年MySQL数据库的下载与安装(2017mysql下载)
- 千锤百炼10万数据导入MySQL(10万数据导入mysql)
- 使用cmd进入MySQL数据库简易指南(cmd进入mysql语句)
- 探索MySQL三维数据库如何实现更准确高效的数据存储和查询(mysql三维数据库)
- 学习如何安装 MySQL XP 数据库软件(mysql xp 安装包)
- 离线状态下如何使用MySQL数据库(mysql不联网不能用)
- 使用MySQL,如何避免长时间等待和慢查询(mysql。长时间)