文档章节

MySQL数据库优化(基于酒店2000w条数据)

java_speed
 java_speed
发布于 2014/02/11 10:26
字数 1205
阅读 1701
收藏 17

接上一篇博客:http://my.oschina.net/twinkling/blog/180619。确定了表设计:

CREATE TABLE `customer` (
  `Name` varchar(80) NOT NULL,
  `CardNo` varchar(10) DEFAULT NULL,
  `Descriot` varchar(100) DEFAULT NULL,
  `CtfTp` varchar(4) DEFAULT NULL,
  `CtfId` varchar(40) DEFAULT NULL,
  `Gender` varchar(8) DEFAULT NULL,
  `Birthday` int(9) DEFAULT NULL,
  `Address` varchar(100) DEFAULT NULL,
  `Zip` int(10) DEFAULT NULL,
  `Dirty` varchar(20) DEFAULT NULL,
  `District1` varchar(6) DEFAULT NULL,
  `District2` varchar(4) DEFAULT NULL,
  `District3` varchar(6) DEFAULT NULL,
  `District4` varchar(6) DEFAULT NULL,
  `District5` varchar(8) DEFAULT NULL,
  `District6` varchar(20) DEFAULT NULL,
  `FirstNm` varchar(50) DEFAULT NULL,
  `LastNm` varchar(20) DEFAULT NULL,
  `Duty` varchar(20) DEFAULT NULL,
  `Mobile` varchar(40) DEFAULT NULL,
  `Tel` varchar(40) DEFAULT NULL,
  `Fax` varchar(40) DEFAULT NULL,
  `EMail` varchar(60) DEFAULT NULL,
  `Nation` varchar(25) DEFAULT NULL,
  `Taste` varchar(100) DEFAULT NULL,
  `Education` varchar(20) DEFAULT NULL,
  `Company` varchar(80) DEFAULT NULL,
  `CTel` varchar(20) DEFAULT NULL,
  `CAddress` varchar(60) DEFAULT NULL,
  `CZip` int(8) DEFAULT NULL,
  `Family` int(10) DEFAULT NULL,
  `Version` datetime DEFAULT NULL,
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_Name_Id` (`Name`, `id`) USING BTREE,
  KEY `index_CtfId_Id` (`CtfId`, `id`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

1、参数设置

由于是查询为主的数据库,所以选用了MyISAM数据库引擎。开始使用前需要配置一下参数,参考文章:http://isky000.com/database/mysql-perfornamce-tuning-cache-parameter ,主要修改这个参数。

key_buffer_size (global) 
Key Buffer 可能是大家最为熟悉的一个 MySQL 缓存参数了,尤其是在 MySQL 没有更换默认存储引擎的时候,很多朋友可能会发现,默认的 MySQL 配置文件中设置最大的一个内存参数就是这个参数了。key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存,这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引,以尽可能提高性能。 此外,当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意,由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中,而不会缓存表数据库块。所以,我们的 SQL 一定要尽可能让过滤条件都在索引中,以便让缓存帮助我们提高查询效率。
所以,该值是越大越好,在/etc/my.cnf文件的[mysqld]头下,增加:

key_buffer_size=1024M #我的系统内存是2G

注意:1024M是根据系统的内存来设置,要留点内存给系统用。

为了避免乱码问题,全部采用utf8编码。

2、建表索引说明

关于为什么索引能够提高效率,请参看这篇文章: http://isky000.com/database/mysql-performance-tuning-index

之所以需要建立复合索引

  1. 需要通过索引关键字去查找id(确保这个过程是在内存中进行)
  2. 由于复合索引检索时只以第一个为准,所以检索关键字需要放在第一个位置,只能是(name, id) 不能是(id, name)

但是,在索引查询的时候需要注意几个问题:

  1. 条件过滤时,最好在索引上完成过滤,回表只是取出额外的数据字段;
  2. 过滤条件好的字段要更靠前;
  3. 当读取的数据量占整个数据比例很大,使用索引不一定优于全表扫描;注:在有些模糊查询中,索引检索结果有2百万左右时,再去回表查数据就很慢
  4. 一次数据访问一般只能利用到一个索引,也就是说并不是所有where条件都能用上索引。

3、检索时SQL语句优化

  1. 条件扫描时,不要搞这种:

    # 虽说是要走索引,但是回表操作太慢
    select * from customer where name like '陈%';
    # 没有关联的索引也不要使用, name 和 ctfid 之间没有关联
    select id, name, ctfid from customer_bk where name like '徐%' ;
  2. 慎用两个索引条件一起使用,同1,部分走索引,然后回表取数据,再对比

    explain select id from customer_bk where name like '徐%' and ctfid like '5%';
    # 哪个条件放前面都无所谓,MySQL内部优化了,取数据量少的索引,然后再对比其他条件
  3. 避免类似IN的子查询,不能出现如下的SQL语句:
  4. select * from customer where id in (select id from customer where name like '陈'); # 通过 explain 语句,可以分析其检索范围 mysql> explain select * from customer_bk where id in (select id from customer_bk where name like '陈'); +----+--------------------+-------------+-----------------+-----------------------+---------+---------+------+----------+-------------+ | id | select_type        | table       | type            | possible_keys         | key     | key_len | ref  | rows     | Extra       | +----+--------------------+-------------+-----------------+-----------------------+---------+---------+------+----------+-------------+ |  1 | PRIMARY            | customer_bk | ALL             | NULL                  | NULL    | NULL    | NULL | 20050144 | Using where | |  2 | DEPENDENT SUBQUERY | customer_bk | unique_subquery | PRIMARY,index_Name_Id | PRIMARY | 4       | func |        1 | Using where | +----+--------------------+-------------+-----------------+-----------------------+---------+---------+------+----------+-------------+ 2 rows in set (0.00 sec)


4、其他优化

  1. 表Customer含有大量不需要的字段,可以考虑分表。但是对于这个简单业务来说,可以忍受的。
  2. 编码问题,在只有中文和英文的情况下,可以考虑只使用latin1编码,减少数据存储量。


© 著作权归作者所有

共有 人打赏支持
java_speed
粉丝 112
博文 44
码字总数 17031
作品 1
成都
程序员
私信 提问
优酷面试题-mysql数据库相关问题,请教大神支招

mysql数据库中user表有2000w数据(只有id和name字段),现在内存中的list中有200w数据,有什么方法快速的找出,内存中存在,而数据库中不存在的数据,优酷面试题,跪谢...

梦蝶飘雪
2017/10/18
1K
26
数据库分库分表(sharding)系列(五) 一种支持自由规划无须数据迁移和修改路由代码的Sharding扩容方案

版权声明:本文由本人撰写并发表于2012年9月份的《程序员》杂志,原文题目《一种支持自由规划的Sharding扩容方案——主打无须数据迁移和修改路由代码》,此处作为本系列的第五篇文章进行转载...

bluishglc
2012/09/12
0
0
2000w条数据(sql文件)导入mysql, 在12000w时,命令行就承受不住了

2000w条数据存在于一个表内. memory类型; 提示,out of memory ! 配置文件能修改的都修改了呀, 比如 tmp_table_size = 1G max_heap_table_size = 1G 之前是用navicat导入的,提示the table is ...

蒙毅
2017/04/07
366
9
跪求2000W的开房记录的数据库,的mysql脚本,或者csv文件?

跪求2000W的开房记录的数据库,的mysql脚本,或者csv文件?

知行合一1
2017/02/09
1K
10
资金项目性能优化

一、项目背景 该项目是一个对资金还、回款改造的项目。以前的做法是,在签约或者发生标转让的时候生成回款信息,直接插入回款表,下次回款时从回款表里面查找。现在这张表有2亿+条数据,100...

liangxiao
2016/05/27
90
0

没有更多内容

加载失败,请刷新页面

加载更多

容器服务

简介 容器服务提供高性能可伸缩的容器应用管理服务,支持用 Docker 和 Kubernetes 进行容器化应用的生命周期管理,提供多种应用发布方式和持续交付能力并支持微服务架构。 产品架构 容器服务...

狼王黄师傅
昨天
3
0
高性能应用缓存设计方案

为什么 不管是刻意或者偶尔看其他大神或者大师在讨论高性能架构时,自己都是认真的去看缓存是怎么用呢?认认真真的看完发现缓存这一块他们说的都是一个WebApp或者服务的缓存结构或者缓存实现...

呼呼南风
昨天
12
0
寻找一种易于理解的一致性算法(扩展版)

摘要 Raft 是一种为了管理复制日志的一致性算法。它提供了和 Paxos 算法相同的功能和性能,但是它的算法结构和 Paxos 不同,使得 Raft 算法更加容易理解并且更容易构建实际的系统。为了提升可...

Tiny熊
昨天
2
0
聊聊GarbageCollectionNotificationInfo

序 本文主要研究一下GarbageCollectionNotificationInfo CompositeData java.management/javax/management/openmbean/CompositeData.java public interface CompositeData { public Co......

go4it
昨天
3
0
阿里云ECS的1M带宽理解

本文就给大家科普下阿里云ECS的固定1M带宽的含义。 “下行带宽”和“上行带宽” 为了更好的理解,需要先给大家解释个词“下行带宽”和“上行带宽”: 下行带宽:粗略的解释就是下载数据的最大...

echojson
昨天
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部