MySQL 建表的优化策略 小结

目录
1. 字符集的选择 1
2. 主键 1
3. 外键 2
4. 索引 2
4.1. 以下情况适合于创建索引 2
4.2. 以下的情况下不适合创建索引 3
4.3. 联合索引 3
4.4. 索引长度 4
5. 特殊字段 4
5.1. 冗余字段 4
5.2. 分割字段 4
5.3. BLOB和CLOB 5
6. 特殊 5
6.1. 表格分割 5
6.2. 使用非事务表类型 5

1. 字符集的选择
如果确认全部是中文，不会使用多语言以及中文无法表示的字符，那么GBK是首选。
采用UTF-8编码会占用3个字节，而GBK只需要2个字节。
2. 主键
尽可能使用长度短的主键
系统的自增类型AUTO_INCREMEN, 而不是使用类似uuid()等类型。如果可以使用外键做主键，则更好。比如1：1的关系，使用主表的id作为从表的主键。
主键的字段长度需要根据需要指定。
tinyint 从 2的7次方-1 ：-128 到 127
smallint 从 2的15次方-1 ：-32768 到 32767
mediumint 表示为 2的23次方-1： 从 -8388608 到8388607
int 表示为 2的31次方-1
bigint 表示为 2的63次方-1

在主键上无需建单独的索引，因为系统内部为主键建立了聚簇索引。
允许在其它索引上包含主键列。
3. 外键
外键会影响插入和更新性能，对于批量可靠数据的插入，建议先屏蔽外键检查。
对于数据量大的表，建议去掉外键，改由应用程序进行数据完整性检查。
尽可能用选用对应主表的主键作作为外键，避免选择长度很大的主表唯一键作为外键。
外键是默认加上索引的
4. 索引
创建索引，要在适当的表，适当的列创建适当数量的适当索引。在查询优先和更新优先之间做平衡。
4.1. 以下情况适合于创建索引
在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

4.2. 以下的情况下不适合创建索引
对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。
如果表数据很少，比如每个省按市做汇总的表，一般低于2000，且数据量基本没有变化。此时增加索引无助于查询性能，却会极大的影响更新性能。

当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当对修改性能的要求远远大于检索性能时，不应该创建索引。
4.3. 联合索引
在特定查询里，联合索引的效果高于多个单一索引，因为当有多个索引可以使用时，MySQL只能使用其中一个。
在查询里，同时用到了联合索引包含的前几个列名，都会使用到联合索引，否则将部分或不会用到。比如我们有一个firstname、 lastname、age列上的多列索引，我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时，MySQL将使用 fname_lname_age索引：
firstname，lastname，age
firstname，lastname
firstname
从另一方面理解，它相当于我们创建了(firstname，lastname，age)、(firstname，lastname)以及(firstname)这些列组合上的索引。
4.4. 索引长度
对于CHAR或者Varchar的列，索引可以根据数据的分布情况，用列的一部分参与创建索引。
create index idx_t_main on t_main(name(3));
这里就是指定name的前三个字符参与索引，而不是全部
最大允许的长度为1000个字节，对已GBK编码则是500个汉字
5. 特殊字段
5.1. 冗余字段
就是用空间换取时间。如果大表查询里经常要join某个基础表，且这个数据基本不变，比如人的姓名，城市的名字等。一旦基础表发生变动，则需要更新所有涉及到的冗余表。
5.2. 分割字段
如果经常出现以某个字段的某个局部进行检索和汇总(substring())，可以考虑将这一部分独立出来。
比如统计姓名里，每种姓氏的人数，可以考虑实现就按照姓和名分别保存，而不是一个字段。
还有就是某些上下级结构的实现，也可以考虑将不同的级别放在不同的字段里。
5.3. BLOB和CLOB
此类字段一般数据量很大，建议设计上数据库可以只保存其外部连接，而数据以其它方式保存，比如系统文件。
6. 特殊
6.1. 表格分割
如果一个表有许多的列，但平时参与查询和汇总的列却并不是很多，此时可以考虑将表格拆分成2个表，一个是常用的字段，另一个是很少用到的字段。
6.2. 使用非事务表类型
MySQL支持多种表类型，其中InnoDB类型是支持事物的，而MyISAM类型是不支持的，但MyISAM速度更快。对于某些数据，比如地理行政划分，民族等不可能参与事务的数据，可以考虑用MyISAM类型的表格。
但InnoDB的表，将无法用MyISAM表数据做外键约束了。
MyISAM表参与的事务，其InnoDB表可以正常的提交和回滚，但不影响MyISAM表。

赞 (0)

相关推荐

  • mysql建表常用sql语句个人经验分享

    连接:mysql -h主机地址 -u用户名 -p用户密码 (注:u与root可以不用加空格,其它也一样) 断开:exit (回车) 创建授权:grant select on 数据库.* to 用户名@登录主机 identified by \"密码\" 修改密码:mysqladmin -u用户名 -p旧密码 password 新密码 删除授权: revoke select,insert,update,delete om *.* from test2@localhost; 显示数据库:sh

  • MySQL查看、创建和删除索引的方法

    本文实例讲述了MySQL查看.创建和删除索引的方法.分享给大家供大家参考.具体如下: 1．索引作用 在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率.特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍. 例如,有3个未索引的表t1.t2.t3,分别只包含列c1.c2.c3,每个表分别含有1000行数据组成,指为1-1000的数值,查找对应值相等行的查询如下所示. SELECT c1,c2,c3 FROM t1,t2,t3

  • MYSQL索引无效和索引有效的详细介绍

    1.WHERE字句的查询条件里有不等于号(WHERE column!=...),MYSQL将无法使用索引2.类似地,如果WHERE字句的查询条件里使用了函数(如:WHERE DAY(column)=...),MYSQL将无法使用索引3.在JOIN操作中(需要从多个数据表提取数据时),MYSQL只有在主键和外键的数据类型相同时才能使用索引,否则即使建立了 索引也不会使用4.如果WHERE子句的查询条件里使用了比较操作符LIKE和REGEXP,MYSQL只有在搜索模板的第一个字符不是通配符的情况下才

  • MySQL常用的建表、添加字段、修改字段、添加索引SQL语句写法总结

    本文实例讲述了MySQL常用的建表.添加字段.修改字段.添加索引SQL语句写法.分享给大家供大家参考,具体如下: 建表: DROP TABLE IF EXISTS bulletin; CREATE TABLE bulletin( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, # 主键 uid INT(11) NOT NULL DEFAULT 0, # 创建者id context VARCHAR(600) NOT NULL DEFAULT '', # 公告

  • Mysql 建库建表技巧分享

    一.两表之间若有关联,你是否还在用主键进行关联? 比如现在有2张表,一张新闻栏目表,一张新闻表,现在两张表需要进行关联,我想大多数人的做法肯定是在新闻表里建一个新闻栏目id,然后把新闻栏目表里的主键ID(自增)写到这个字段里,通过这样进行两表关联. 如果你是这样做的,赶紧改掉这个习惯吧.也许你会问为什么,栏目id是主键啊,又是自增的,为什么这样操作不行?原因其实很简单,栏目我们会增加,也会删除,删除就会造成主键id之间会有断号的情况,由于主键设置为自增,也就是说你之前删掉的栏目,再进行添加,id

  • mysql 表空间及索引的查看方法

    1.查看索引 (1)单位是GB SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema LIKE 'database'; +------------------+ | Total Index Size | +------------------+ | 1.70 GB | +--

  • MySQL 索引分析和优化

    MySQL 索引分析和优化

    一.什么是索引? 索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记录数量越多,这个操作的代价就越高.如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置.如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍. 假设我们创建了一个名为people的表: CREATE TABLE people ( p

  • MySQL动态创建表，数据分表的存储过程

    复制代码 代码如下: BEGIN DECLARE `@i` int(11); DECLARE `@siteCount` int(11); DECLARE `@sqlstr` VARCHAR(2560); DECLARE `@sqlinsert` VARCHAR(2560); //以上声明变量 SELECT COUNT(0) into `@siteCount` FROM tbl_base_site; //计算表tbl_base_site的记录总条数 set `@i`=1; WHILE (`@i`-

  • mysql 添加索引 mysql 如何创建索引

    1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEX

  • MySQL SQL语句优化的10条建议

    1.将经常要用到的字段(比如经常要用这些字段来排序,或者用来做搜索),则最好将这些字段设为索引.2.字段的种类尽可能用int 或者tinyint类型.另外字段尽可能用NOT NULL.3.当然无可避免某些字段会用到text ,varchar等字符类型,最好将text字段的单独出另外一个表出来(用主键关联好)4.字段的类型,以及长度,是一个很考究开发者优化功力的一个方面.如果表数据有一定的量了,不妨用PROCEDURE ANALYSE()命令来取得字段的优化建议!(在phpmyadmin里可以在查

  • Mysql建表与索引使用规范详解

    一. MySQL建表,字段需设置为非空,需设置字段默认值.二. MySQL建表,字段需NULL时,需设置字段默认值,默认值不为NULL.三. MySQL建表,如果字段等价于外键,应在该字段加索引.四. MySQL建表,不同表之间的相同属性值的字段,列类型,类型长度,是否非空,是否默认值,需保持一致,否则无法正确使用索引进行关联对比.五. MySQL使用时,一条SQL语句只能使用一个表的一个索引.所有的字段类型都可以索引,多列索引的属性最多15个.六. 如果可以在多个索引中进行选择,MySQL通常

  • MySQL索引类型总结和使用技巧以及注意事项

    在数据库表中,对字段建立索引可以大大提高查询速度.假如我们创建了一个 mytable表: 复制代码 代码如下: CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL  ); 我们随机向里面插入了10000条记录,其中有一条:5555, admin. 在查找username="admin"的记录 SELECT * FROM mytable WHERE username='admin';时,如果在

随机推荐