mysqldecimal最⼤长度_⾼性能MySQL优化技巧
本⽂的内容是总结⼀些MySQL的常见使⽤技巧，以供没有DBA的团队参考。以下内容以MySQL5.5为准，如⽆特殊说明，存储引擎以InnoDB为准。
MySQL的特点
了解MySQL的特点有助于更好的使⽤MySQL，MySQL和其它常见数据库最⼤的不同在于存在存储引擎这个概念，存储引擎负责存储和读取数据。不同的存储引擎具有不同的特点，⽤户可以根据业务的特点选择适合的存储引擎，甚⾄是开发⼀个新的引擎。MySQL的逻辑架构⼤致如下：
MySQL默认的存储引擎是InnoDB，该存储引擎的主要特点是：
⽀持事务处理
⽀持⾏级锁
数据存储在表空间中，表空间由⼀些列数据⽂件组成
采⽤MVVC(多版本并发控制)机制实现⾼并发
表基于主键的聚簇索引建⽴
⽀持热备份
其它常见存储引擎特点概述：
MyISAM：⽼版本MySQL的默认引擎，不⽀持事务和⾏级锁，开发者可以⼿动控制表锁；⽀持全⽂索引；崩溃后⽆法安全恢复；⽀持压缩表，压缩表数据不可修改，但占⽤空间较少，可以提⾼查询性能
Archive：只⽀持Insert和Select，批量插⼊很快，通过全表扫描查询数据
SCV：把⼀个SCV⽂件当做⼀个表处理
Memory：数据存储在内存中
还有很多，不再⼀⼀列举。
数据类型优化
选择数据类型的原则：
选择占⽤空间⼩的数据类型
选择简单的类型
避免不必要的可空列
占⽤空间⼩的类型更节省硬件资源，如磁盘、内存和CPU。尽量使⽤简单的类型，如能⽤int就不⽤char，因为后者的排序涉及到字符集的选择，⽐使⽤int复杂。可空列使⽤更多的存储空间，如果在可空列上创建索引，MySQL需要额外的字节做记录。创建表时，默认都是可空，容易被开发者忽视，最好是⼿动改为不可空，如果要存储的数据确实不会有空值的话。
整型类型
整型类型包括：
tinyint
smallint
mediumint
int
bigint
它们分别使⽤8、16、24、32和64位存储数字，它们可以表⽰−2n−1−2n−1到2n−1−12n−1−1范围的数字，前⾯可以加unsigned 修饰，这样可以让正数的可表⽰范围提⾼1倍，但是⽆法表⽰负数。另外，为整型指定长度没什么卵⽤，数据类型定下来，长度也就相应定下来了。
⼩数类型
float
double
decimal
float和double就是通常意义上的float和double，前者使⽤32位存储数据，后者使⽤64位存储数据，和整型⼀样，为它们指定长度没什么卵⽤。
decimal类型⽐较复杂，⽀持精确计算，占⽤的空间也⼤，decimal使⽤每4个字节表⽰9个数字，如decimal(18,9)表⽰数字长度是18，其中⼩数位9个数字，整数部分9个数字，加上⼩数点本⾝，共占⽤9个字节。考虑到decimal占⽤空间较多，以及精度计算很复杂，数据量⼤的时候可以考虑⽤bigint代替之，可以在持久化和读取前对真实数据进⾏⼀些缩放操作。
字符串类型
varchar
char
varbinary
binary
blob
text
枚举
varchar类型数据实际占⽤空间等于字符串的长度加上1个或2个⽤来记录字符串长度的字节(当row-format没有被设置为fixed
时)，varchar很节省空间。当表中某列字符串类型的数据长度差别较⼤时适合使⽤varchar。
char的实际占⽤空间是固定的，当表中字符串数据的长度相差⽆⼏或很短时适合使⽤chart类型。
与varchar和char对应的有varbinary和binary，后者存储的是⼆进制字符串，和前者相⽐，后者⼤⼩写敏感，不⽤考虑编码⽅式，执⾏⽐较操作时更快。
需要注意的是：虽然varchar(5)和varchar(200)在存储“hello”这个字符串时使⽤相同的存储空间，但并不意味着将varchar的长度设置太⼤不会影响性能，实际上，MySQL的某些内部计算，⽐如创建内存临时表时(某些查询会导致MySQL⾃动创建临时表)，会分配固定⼤⼩的空间存放数据。
blob使⽤⼆进制字符串保存⼤⽂本，text使⽤字符保存⼤⽂本，InnoDB会使⽤专门的外部存储区来存放此类数据，数据⾏内仅存放指向他们的指针，此类数据不宜创建索引(要创建也只能正对字符串前缀创建)，不过也不会有⼈这么⼲。
如果某列字符串⼤量重复且内容有限，可使⽤枚举代替，MySQL处理枚举时维护了⼀个“数字-字符串”表，使⽤枚举可以减少很多存储空间。
时间类型
year
date
time
datetime
timestamp
datetime存储范围是1001到9999，精确到秒。timestamp存储1970年1⽉1⽇午夜以来的秒数，可以表⽰到2038年。占⽤4个字节，
是datetime占⽤空间的⼀半。timestamp表⽰的时间和时区有关，另外timestamp列还有个特性，执⾏insert或update语句时，MySQL
会⾃动更新第⼀个类型为timestamp的列的数据为当前时间。很多表中都有设计有⼀列叫做UpdateTime，这个列使⽤timestamp倒是挺合适的，会⾃动更新，前提是系统不会使⽤到2038年。
主键类型的选择
尽可能使⽤整型，整型占⽤空间少，还可以设置为⾃动增长。尤其别使⽤GUID，MD5等哈希值字符串作为主键，这类字符串随机性很⼤，
由于InnoDB主键默认是聚簇索引列，所以导致数据存储太分散。另外，InnoDB的⼆级索引列中默认包含主键列，如果主键太长，也会使得⼆级索引很占空间。
特殊类型的数据
存储IP最好使⽤32位⽆符号整型，MySQL提供了函数inet_aton()和inet_ntoa()进⾏IP地址的数字表⽰和字符串表⽰之间的转换。
索引优化
InnoDB使⽤B+树实现索引，举个例⼦，假设有个People，建表语句如下
CREATE TABLE `people` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(5) NOT NULL, `Age` tinyint(4) NOT NULL, `Number` char(5) NOT 插⼊数据：
它的索引结构⼤致是这样的：
也就是说，索引列的顺序很重要，如果两⾏数据的Name列相同，则⽤Age列⽐较⼤⼩，如果Age列相同，则⽤Number列⽐较⼤⼩。先⽤第⼀列排序，然后是第⼆列，最后是第三列。
查询的使⽤应该尽量从左往右匹配，另外，如果左边列范围查，右边列⽆法使⽤索引；还有就是不能隔列查询，否则后⾯的索引也⽆法使⽤到。如以下⼏个SQL是正⾯范例：
SELECT * from people where Name ='Abel' and Age = 2 AND Number = 12312
SELECT * from people where Name ='Abel'
SELECT * from people where Name like 'Abel%'
SELECT * from people where Name = 'Andy' and Age BETWEEN 11 and 20
SELECT * from people ORDER BY NAME
SELECT * from people ORDER BY NAME, Age
SELECT * from people GROUP BY Name
以下⼏个SQL是反⾯范例：
SELECT * from people where Age = 2
SELECT * from people where NAME like '%B'
SELECT * from people where age = 2
SELECT * from people where NAME = 'ABC' AND number = 3
SELECT * from people where NAME like 'B%' and age = 22
⼀个使⽤Hash值创建索引的技巧
如果表中有⼀列存储较长字符串，假设名字为URL，在此列上创建的索引⽐较⼤，有个办法可以缓解：创建URL字符串的数字哈希值的索引。再新建⼀个字段，⽐如叫做URL_CRC，专门放置URL的哈希值，然后给这个字段创建索引，查询时这样写：
select * from t where URL_CRC = 387695885 and URL = 'www.baidu'
如果数据量⽐较多，为防⽌哈希冲突，可⾃定义哈希函数，或⽤MD5函数返回值的⼀部分作为哈希值：
SELECT CONV(RIGHT(MD5('www.baidu'),16), 16, 10)
前缀索引
如果字符串列存储的数据较长，创建的索引也很⼤，这时可以使⽤前缀索引，即：只针对字符串前⼏个字符做索引，这样可以缩短索引的⼤⼩，不过，显然，此类索引在执⾏order by和group by时不起作⽤。
创建前缀索引时选择前缀长度很重要，在不破坏原来数据分布的情况下尽可能选择较短的前缀。举个例⼦，如果如果⼤部分字符串是以"abc"开头，那么如果限定前缀索引长度为4，索引值会包含太多的重复的"abcX"。
多列索引
上⾯提到的“People”上创建的索引即为多列索引，多列索引往往⽐多个单列索引更好。
对多个索引进⾏and查询时，应该创建多列索引，⽽不是多个单列索引
可以试试这样写的效果：
select * from t where f1 = 'v1' and f2 <> 'v2' union all select * from t where f2 = 'v2' and f1 <> 'v1'
多列索引的顺序很重要，通常，不考虑排序和分组查询时，应该把选择性(选择性是指某表索引列不同数据的个数/总⾏数。选择性⾼意味着重复数据少)⼤的列放到前⾯。但也有例外，如果能确认某些查询是频繁执⾏的，则应该优先照顾这些查询的选择性，⽐如，如果上⾯的People表中Name的选择性⼤于Age，查询语句应该这样写:
select * from people where name = 'xxx' and age = xx
Name列放了索引中的左侧⽐较合适，但是如果某个SQL执⾏的评率最⾼，⽐如
select * from people where name = 'xxx' and age = 20,
当age=20的记录在数据库中⾮常少时，反⽽把age放到索引列的左端效率更⾼。把age放了索引左端可能对其它age不等于20的查询来说不公平，如果不能确定age=20是最⾮常频繁的查询条件，还是要综合考虑，把name放了左侧合适。
聚簇索引
聚簇索引是⼀种数据存储结构，InnoDB在主键的索引的叶⼦节点中直接保存了数据⾏，⽽不是像⼆级索引那样只是保存了索引列的值和所指向⾏的主键值。由于这个特性，⼀个表只能有⼀个聚簇索引。如果⼀个表没有定义主键也没有定义具有唯⼀索引的列，那么InnoDB会⽣成⼀个隐藏列，并且在此列设为聚簇索引列。
覆盖索引
mysql下载32位
简单地说，某些查询只需要查询索引列，那么就不⽤再根据索引B树节点记录的主键ID进⾏⼆次查询了。
重复索引和冗余索引
如果重复在某列创建索引，并不会带来任何好处，只有坏处，应该尽量避免。⽐如给主键创建唯⼀索引和普通索引就是多于的，因为InnoDB的主键默认就是聚簇索引了。
冗余索引和重复索引不同，⽐如某个索引是(A,B)，另⼀个索引是(A)，这叫冗余索引，前者可以代替后者，后者不可以代替前者的作⽤。但是(A,B)和(B)以及(A,B)和(B,A)不算冗余索引，起作⽤谁也代替不了谁。
如果⼀个表中已经存在索引(A)，现在⼜想创建索引(A,B)，那么只需扩展就的索引就可以，没有必要创建新的索引。需要注意的是如果已经存在索引(A)，那么也没有必要在创建索引(A,ID)，其中ID指主键，因为索引A默认已经包含了主键了，也算是冗余主键。
但是，有时候，冗余索引也是可取的，假设已经存在索引(A)，将其扩展为(A,B)后，因为B列是⼀个很长的类型，导致⽤A单独查询时没有以前快了，这时可以考虑新创建索引(A,B)。
不使⽤的索引
不使⽤的索引徒然增加insert、update和delete的效率，应该及时删除
索引使⽤总结
索引的三星原则：
索引将查询相关的记录按顺序放在⼀起则得⼀星
索引中的数据顺序和查询结果的排序⼀致则得⼀星
索引中包含了查询所需要的全部列则得⼀星
第⼀个条原则的意思是where条件中查询的顺序和索引是⼀致的，就是前⾯说的从左到右使⽤索引。
索引不是万能的，当数据量巨⼤时，维护索引本⾝也是耗费性能的，应该考虑分区分表存储。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。