本文内容是总结一些常见的MySQL使用技巧，供没有DBA的团队参考。以下内容以MySQL5.5为准。除非另有说明，否则存储引擎以InnoDB为准。MySQL的特点了解MySQL的特点有助于更好地使用MySQL。MySQL与其他常见数据库的区别在于存储引擎的概念，它负责存储和读取数据。不同的存储引擎具有不同的特点，用户可以根据业务特点选择合适的存储引擎，甚至开发新的引擎。MySQL的逻辑结构大致如下：MySQL默认的存储引擎是InnoDB，这个存储引擎的主要特点是：支持事务处理支持行级锁数据存储在表空间中，表空间由部分列数据文件，采用MVVC（多版本并发控制）机制实现高并发，发布基于主键的聚簇索引建立支持热备份其他常用存储引擎特性概述：MyISAM：老版本MySQL的默认引擎做不支持事务和行级锁，开发者可以手动控制表锁；支持全文索引；崩溃后无法安全恢复；支持压缩表，压缩表数据不可修改，但占用空间小，可以提高查询性能Archive：只支持Insert和Select，批量插入速度快，通过全表扫描查询数据SCV:把一个SCV文件当作atable内存：内存中存放的数据很多，就不一一列举了。数据类型优化选择数据类型的原则：选择占用空间小的数据类型选择简单的类型，避免不必要的可空列占用空间小的类型可以节省硬件资源，例如磁盘、内存和CPU。尽量用简单类型，能用int就不要用char，因为后者的排序涉及到字符集的选择，比用int复杂。可为空的列使用更多存储空间。如果在可空列上创建索引，MySQL需要额外的字节来记录。创建表的时候，默认都是可以为空的，这很容易被开发者忽略。如果要存储的数据没有空值，请手动将其更改为不可空。整数类型整数类型包括：tinyintsmallintmediumintintbigint它们分别用8、16、24、32和64位来存储数字，它们可以表示一个范围内的数字，前面可以加unsigned，这样正数可以表示范围加倍，但不能表示负数。此外，为整数指定长度是没有用的。当数据类型固定时，长度也相应固定。十进制类型floatdoubledecimalfloat和double就是通常意义上的float和double。前者使用32位存储数据，后者使用64位存储数据。像整数一样，为它们指定长度是没有用的。decimal类型比较复杂，支持精确计算，占用空间大。十进制使用4个字节来表示9个数字。例如decimal(18,9)表示数字长度为18，有9个小数位和9个整数。数字，加上小数点本身，一共占用9个字节。考虑到decimal占用空间大，精度计算很复杂，数据量大的时候可以考虑改用bigint，可以在持久化和读取之前对真实数据进行一些缩放操作。String类型varcharcharvarbinarybinaryblobtext枚举varchar类型数据实际占用的空间等于字符串的长度加上1或2个用于记录字符串长度的字节（当row-format没有设置为fixed时）,varchar节省空间。当表中某列字符串类型的数据长度变化较大时，适合使用varchar。char实际占用的空间是固定的，图表类型适用于表中字符串数据长度相差无几或很短的情况。varchar和char对应的是varbinary和binary。后者存储二进制字符串。与前者相比，后者区分大小写，与编码方式无关，执行比较操作更快。需要注意的是，虽然varchar(5)和varchar(200)在存储字符串“hello”时使用了相同的存储空间，但并不意味着将varchar的长度设置过大就不会影响性能。事实上，MySQL内部的一些计算，比如创建内存临时表（有些查询会导致MySQL自动创建临时表），会分配固定大小的空间来存储数据。Blob使用二进制字符串存储大文本，text使用字符存储大文本。InnoDB会使用一个特殊的外部存储区来存储这些数据。只有指向它们的指针存储在数据行中。此类数据不适合创建索引（只能为字符串前缀创建），但没有人会这样做。如果一列字符串重复很多，内容有限，可以用枚举代替。MySQL在处理枚举时会维护一张“数字-字符串”表。使用枚举可以减少很多存储空间。时间类型年日期时间日期时间时间戳日期时间存储范围是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+树来实现索引。例如假设有一个人，建表语句如下CREATETABLE`people`(`Id`int(11)NOTNULLAUTO_INCREMENT,`Name`varchar(5)NOTNULL,`Age`tinyint(4)NOTNULL,`Number`char(5)NOTNULLCOMMENT'number',PRIMARYKEY(`Id`),KEY`i_name_age_number`(`Name`,`Age`,`Number`))ENGINE=InnoDBAUTO_INCREMENT=14DEFAULTCHARSET=utf8;insertData:Itsindex结构大致是这样的：也就是说，索引列的顺序很重要。如果两行数据的Name列相同，则使用Age列比较大小，如果Age列相同，则使用Number列比较大小。先按列排序，然后是第二列，然后是第三列。查询的使用应尽可能从左到右匹配。另外，如果搜索的是左列的范围，则索引不能用于右列；并且不能每隔一列查询一次，否则后面的索引无法使用。例如，以下SQL是正例：SELECT*frompeoplewhereName='Abel'andAge=2ANDNumber=12312SELECT*frompeoplewhereName='Abel'SELECT*frompeoplewhereNamelike'Abel%'SELECT*frompeoplewhereName='Andy'andAgeBETWEEN11and20SELECT*frompeopleORDERBYNAMESELECT*frompeopleORDERBYNAME,AgeSELECT*frompeopleGROUPBYName下面的SQL是一个反例：SELECT*frompeople其中年龄=2选择*来自姓名为'%B'的人选择*来自年龄=2的人选择*来自姓名='ABC'和数字=3的人选择*来自姓名为'B%'和年龄的人=22一种使用哈希值创建索引的技术。如果表中有一个列存储了一个长字符串，假设名称为URL，则在该列上创建的索引比较大。有一种方法可以缓解它：创建一个URL字符串索引的数字哈希值。新建一个字段，比如URL_CRC，用来放置URL的哈希值，然后为这个字段建立索引。查询的时候写：select*fromtwhereURL_CRC=387695885andURL='www.baidu.com'如果数据很多，为了防止Hash碰撞，可以自定义hash函数，或者使用MD5的部分返回值作为哈希值的功能。前缀索引如果字符串列存储的数据很长，创建的索引也很大，那么可以使用前缀索引，即只索引字符串的前几个字符，这样可以缩短索引的大小。但是，显然，此类索引在执行orderby和groupby时不起作用。创建前缀索引时选择前缀长度非常重要，在不破坏原有数据分布的前提下，尽可能选择较短的前缀。例如，如果大多数字符串以“abc”开头，那么如果前缀索引长度限制为4，索引值将包含太多重复的“abcX”。多列索引上面提到的在“人物”上创建的索引就是多列索引，而多列索引往往比多个单列索引要好。当对多个索引执行AND查询时，您应该创建一个多列索引而不是多个单列索引。你可以试试这样写的效果：select*fromtwheref1='v1'andf2<>'v2'unionallselect*fromtwheref2='v2'andf1<>'v1'多列索引的顺序很重要。通常，在不考虑排序和分组查询的情况下，应该考虑选择性（选择性是指一个表索引列中不同数据的个数/总行数。选择性高意味着重复数据少）较大的列放在前面。但也有例外。如果你能确认一些查询被频繁执行，你应该优先考虑这些查询的选择性。比如上面People表中Name的选择性大于Age，那么查询语句应该这样写：select*frompeoplewherename='xxx'andage=xxName放在索引的左边比较合适，但是如果某条SQL执行率很高，比如select*frompeoplewherename='xxx'andage=20，age=20的记录在数据库中是非常少的，把age放在最左端效率更高的索引列。将age放在索引的左端可能对其他age不等于20的查询不公平，如果不确定age=20是最频繁的查询条件，将name放在左边还是合适的经过综合考虑。聚簇索引聚簇索引是一种数据存储结构。InnoDB直接将数据行保存在主键索引的叶子节点中，而不是只保存索引列的值和二级索引指向的行的主键值。.由于这一特性，一张表只能有一个聚簇索引。如果一张表没有定义主键，也没有定义带索引的列，那么InnoDB会生成一个隐藏列，并将该列设置为聚簇索引列。覆盖索引简单来说，有些查询只需要查询索引列，不需要根据索引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）。未使用的索引将白白增加插入、更新和删除的效率。应及时删除索引。使用汇总索引的三星级原则：索引将与查询相关的记录放在一起，以获得一星级索引。数据顺序和查询结果排序一致，一星索引包含查询需要的所有列，一星where条件下查询的顺序和索引一致，即也就是上面说的索引是从左到右使用的。索引并不适合所有情况。当数据量巨大时，维护索引本身在性能上也是有代价的。应考虑分区和分表存储。查询优化查询慢的原因是是否从数据库中请求了额外的行。例如，应用程序只需要10条数据，但它会从数据库中请求所有数据，并在将其显示在UI上之前丢弃大部分数据。是否从数据库请求额外的列。例如，应用只需要显示5列，但是通过select*from把所有的列都check出来了。是否重复执行同一个查询。应用程序可以考虑一个查询然后缓存它吗？查询到的记录以后可以使用。MySQL是否在扫描额外的记录通过查看执行计划，可以大致了解需要扫描的记录数。如果这个数量超出预期，尝试增加索引，优化SQL（这是本节的重点），或者改变表结构（比如新增一个单独的汇总表，专门用于某条语句查询）来解决.重构查询的方式是将一个复杂的查询分解为多个简单的查询，将一个大的查询分解为多个小的查询。每个查询的功能相同，只分解了一小部分查询。您可以将一个大型关联查询更改为分别查询几个表，然后在应用程序代码中处理杂项。Optimizecount()Count有两个作用，一个是统计指定的列或表达式，另一个是统计行数。如果参数传入的是列名或者表达式，那么count会统计结果不为NULL的行数，如果参数是*，那么count会统计所有行数。下面是一个传递表达式的例子：SELECTcount(namelike'B%')frompeople可以使用近似优化代替count()，比如执行计划中的行数。索引覆盖扫描增加了汇总表，增加了内存缓存中的记录数。关联查询的优化由MySQL优化器执行。关联表查询就是这样进行的。例如，两个表A和B通过列c关联。遍历的c列的值去B表找数据。总结一下，通常情况下，如果没有索引，只需要给B表的c列加一个索引，保证orderby和groupby涉及的列只属于一张表，这样就可以玩了索引和优化子查询的作用。对于MySQL5.5及以下版本，尽量使用连接而不是子查询。优化groupby和distinct如果可能，尽量将这两个操作应用到主键上。优化限制，例如SQLSELECT*fromsa_stockinfoORDERBYStockAccLIMIT400,5MySQL优化器会把405行的所有列数据都找出来，舍弃400行。如果可以使用覆盖索引查询，就不需要查询那么多列，先修改为:SELECT*FROMsa_stockinfoiJOIN(SELECTStockInfoIDFROMsa_stockinfoORDERBYStockAccLIMIT400,5)tONi.StockInfoID=t.StockInfoIDStockAcc上面有索引，这个查询会利用索引覆盖快速找出匹配条件的主键，然后做联合查询，数据量大时效果明显。如果不需要优化union，则必须使用关键字unionall，这样MySQL在将数据放入临时表时，就不会对某条记录是否存在进行校验。通常的做法是这样写selectcount(*)fromtwherecondition：SELECTIFNULL((SELECT1fromtableNamewhereconditionLIMIT1),0)参考《高性能MySQL》
