网上关于SQL优化的教程很多，都是从理论或者实际操作经验直接入手，今天我将已实际项目过程为主线来谈一谈MySql的优化，部分内容也是参考引用网上资料以及经典的数据库参考书，其中有错误和不足的地方，还请大家纠正补充
我将从项目实施的两个阶段：程序业务设计阶段以及项目测试完成后来探讨：
程序业务设计阶段［数据库表设计优化］
很多人误以为数据库的优化就是优化查询语句，一般的做法就是添加索引，根据网上资料或者数据库慢查询日志优化复杂的复合查询，然而数据库的优化从数据库的设计就开始，高效并且符合规则的数据库设计更能达到事半功倍的效果。在项目的实施的过程中，我经常遇到在优化性能的时候，由于设计的失误而导致优化效果不理想，在实施阶段修改字段对项目的影响又不可估量，因此优化不完善，优化效果也大打折扣。
1. 选择字符集
在mysql（版本5.7）中创建数据库默认的字符集时是utf8,这里需要指出的是，当在使用一些数据库工具的时候要注意选择，否则MySQL升级或者迁移数据库时可能带来很大困扰。在使用sql语句创建数据库的时候，通过mysql_options设定MYSQL_SET_CHARSET_NAME属性为utf8设置默认的字符集，不要去通过修改默认配置来达到目的。
2. 选择存储引擎
这里我们需要去全面了解InnoDB与MyISAM
(一)MyISAM
它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
每个MyISAM在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：
.frm(存储表定义)
MYD(MYData，存储数据)
MYI(MYIndex，存储索引)
数据文件和索引文件可以放置在不同的目录，平均分配IO，获取更快的速度。要指定数据文件和索引文件的路径，需要在创建表的时候通过DATA DIRECTORY和INDEX DIRECTORY语句指定，文件路径需要使用绝对路径。
每个MyISAM表都有一个标志，服务器或myisamchk程序在检查MyISAM数据表时会对这个标志进行设置。MyISAM表还有一个标志用来表明该数据表在上次使用后是不是被正常的关闭了。如果服务器以为当机或崩溃，这个标志可以用来判断数据表是否需要检查和修复。如果想让这种检查自动进行，可以在启动服务器时使用--myisam-recover现象。这会让服务器在每次打开一个MyISAM数据表是自动检查数据表的标志并进行必要的修复处理。MyISAM类型的表可能会损坏，可以使用CHECK TABLE语句来检查MyISAM表的健康，并用REPAIR TABLE语句修复一个损坏到MyISAM表。
MyISAM的表还支持3种不同的存储格式：
静态(固定长度)表
动态表
压缩表
其中静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意：在某些情况下可能需要返回字段后的空格，而使用这种格式时后面到空格会被自动处理掉。
动态表包含变长字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁到更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。
压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。
(二)InnoDB
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。
(一)自动增长列：
InnoDB表的自动增长列可以手工插入，但是插入的如果是空或0，则实际插入到则是自动增长后到值。可以通过"ALTER TABLE...AUTO_INCREMENT=n;"语句强制设置自动增长值的起始值，默认为1，但是该强制到默认值是保存在内存中，数据库重启后该值将会丢失。可以使用LAST_INSERT_ID()查询当前线程最后插入记录使用的值。如果一次插入多条记录，那么返回的是第一条记录使用的自动增长值。
对于InnoDB表，自动增长列必须是索引。如果是组合索引，也必须是组合索引的第一列，但是对于MyISAM表，自动增长列可以是组合索引的其他列，这样插入记录后，自动增长列是按照组合索引到前面几列排序后递增的。
(二)外键约束：
MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。
在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括restrict、cascade、set null和no action。其中restrict和no action相同，是指限制在子表有关联的情况下，父表不能更新；casecade表示父表在更新或删除时，更新或者删除子表对应的记录；set null 则表示父表在更新或者删除的时候，子表对应的字段被set null。
当某个表被其它表创建了外键参照，那么该表对应的索引或主键被禁止删除。
可以使用set foreign_key_checks=0;临时关闭外键约束，set foreign_key_checks=1;打开约束。
总结
MyISAM
特性
不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据
适用场景
不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少（阻塞问题）
以读为主
数据一致性要求不是非常高
最佳实践
尽量索引（缓存机制）
调整读写优先级，根据实际需求确保重要操作更优先
启用延迟插入改善大批量写入性能
尽量顺序操作让insert数据都写入到尾部，减少阻塞
分解大的操作，降低单个操作的阻塞时间
降低并发数，某些高并发场景通过应用来进行排队机制
对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率
MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问
InnoDB
特性
具有较好的事务支持：支持4个事务隔离级别，支持多版本读
行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
读写阻塞与事务隔离级别相关
具有非常高效的缓存特性：能缓存索引，也能缓存数据
整个表和主键以Cluster方式存储，组成一颗平衡树
所有Secondary Index都会保存主键信息
适用场景
需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO
最佳实践
主键尽可能小，避免给Secondary index带来过大的空间负担
避免全表扫描，因为会使用表锁
尽可能缓存所有的索引和数据，提高响应速度
在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交
合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性
避免主键更新，因为这会带来大量的数据移动
在事务中混用存储引擎
在MySql服务器层（MySql三层逻辑架构，参考查阅《高性能MySql第三版》第一章）是不管理是事务的，事务是由下层的存储引擎实现的，所以在同一事务中，使用多种存储引擎是不可靠的。如果在事务中混合使用了InnoDB和MyISAM的表，在正常提交的情况下不会有问题，但是如果该事务需要回滚，MyISAM的表上的更新是无法撤销的，这样就会导致数据库处于不一致的状态，而且这种状态很难修复，事务的最终结果也将无法确定。
综上上述，在数据库引擎的选择上应更具实际业务需求选择对应的高效引擎，存在关联的数据表之间应该选择相同的数据库引擎
3. 表设计
(一)字段设计
字段设计要遵守两个原则
1.小
使用小的存储与资源 尽量存储关键的核心内容，比如URL地址，尽量存储短地址
不参与索引与查询的字段都可以添加到一个待扩展的property字段中
2.简单
常用的类型 优先级为 整形，时间戳，日期，日期时间，固定长度字符串，文本
尽量避免使用NULL。创建时尽量使用NOT NULL，因为NULL值会使该列的索引、索引统计、值的比较都更复杂，而且会使用更多存储空间。特别是一定要在列上建立索引时，一定是NOT NULL
(二)数据类型选择
整数类型：
整数类型主要包括 TINYINT（8位，-128~127）、SMALLINT（16位）、MEDIUMINT（24位）、INT（32位）、BIGINT（64位），他们的存储的值的范围为：-2^(N-1)至2^(N-1)-1，以上都可以使用UNSIGNED，取值范围为0～2^N-1，根据实际的整形数据大小选区最合适的整数类型
注意：MySQL可以为整数类型指定宽度，这只是显示的宽度，如INT(5)，显示5个字符宽度，如123，会显示00123，如果你存储的整形数据是以0开头的，在获取或者存储的过程中数据库会自动以非0的位数截取保存，在存储随机数的时候慎重使用，可以通过添加非0前缀避免
实数类型：
实数类型包括 FLOAT（不精确类型）、DOUBLE（不精确类型）、DECIMAL（精确类型），支持指定精度，如DECIMAL(18,9)，表示小数点两边各9个数字。
注意：由于指定精度，会使得MySQL悄悄地选择不同的数据类型，或者在存储时对值自动取舍，因此建议只指定数据类型，不指定精度，精度根据需要在前端取舍。
注意：如果真需要DECIMAL，建议使用乘以10^N取得整数后使用INT（或BIGINT）数据类型，前端显示时根据需要除以10^N获得正确的结果，或者将指数N对应以INT存入数据库，这样使MySQL得到优化。
字符串类型：
VARCHAR类型，用于存储可变长字符串，它比定长类型节省空间，因为它仅使用必要的空间，即，越短的字符串使用越少空间。除非使用 ROW_FORMAT=FIXED，每行定长，这样会浪费空间。另外，VARCHAR使用1～2个额外字节记录字符串的长度，如果列长度小于或等于255，使用1个字节表示（8位），如果大于255，使用2个字节。
VARCHAR类型，虽然节约了空间，但由于行是变长的，在UPDATE时可能会使行变得更长，这就导致额外的工作；如果行变长后，页内没有更多空间可以存储，这种情况下，就需要拆分片段（MyISAM）或分裂页（InnoDB）来处理。
VARCHAR类型，适用情况，列最大长度比平均长度大很多，且很少更新，因为碎片将不是问题。
CHAR类型，是定长类型，根据指定的字符串长度分配足够的空间；CHAR类型适合于存储很短的字符串，或者所有值都接近同一个长度。例如，CHAR类型非常适合于存储密码的MD5值，因为MD5是一个定长的值。另一方面，CHAR类型也适合存储经常变更的数据，因为定长的CHAR不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。如，用CHAR(1)存储只有Y和N的值，如果用单字节字符集只需要一个字节，但VARCHAR(1)在单字节情况下则需要两个字节，因为还有一个额外字节用于记录长度。
在可以估测数据长度的情况下，一般使用CHAR类型，随着数据量的扩大，CHAR的优势更加明显
日期和时间类型
DATETIME，存储范围1001年~9999年，封装格式为 YYYYMMDDHHMMSS，与时区无关，8个字节（64位？），使用ANSI标准定义的日期和时间表示方法显示，如“2014-01-11 22:05:33”，注意这是显示。
TIMESTAMP，时间戳，记录了一个累计秒数，从1970年1月1日午夜（格林尼治标准时间）以来。4个字节，与UNIXTIME相同。比DATETIME小很多了，最大记录从 1970年~2038年的时间。
TIMESTAMP值与时区有关，DATETIME与时区无关，DATETIME记录的是客户端提交到数据库的当地时间字符串。如果提交数据时不指定TIMESTAMP的值，则MySQL默认提供当前时间赋值，且默认NOT NULL。
在查询中可以使用FROM_UNIXTIME() 将时间戳转换为日期，UNIX_TIMESTAMP() 将日期转换为时间戳。
4. 使用Memcached 或Redis 缓从
本篇主要讨论数据库的优化，提醒设计者使用缓从技术，从而避免重复查询，加快数据获取速度，在这里不做过多的讨论。
项目测试完成后［数据库性能优化］
（一） 高性能索引创建
1.创建多列索引
除了最基本的创建常用查询的单列索引，还需要根据查询日志，对高频率的复合查询以及条件查询创建多列索引，如果是多列索引，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。
一个常见错误就是，为每个列创建独立索引，或者按照错误的顺序创建多列索引。
在多个列上创建索引大部分情况下并不能提高MySQL的查询性能。
例如，表film_actor在字段film_id和actor_id上各有一个单列索引。但对于下面的查询，这两个单列索引都不是好的选择：
mysql>SELECT film_id,actor_id FROM sakila.film_actor
->WHERE actor_id = 1 OR film_id = 1;
在MySQL 5.0 和更新的版本中，查询能同时使用这两个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR条件的联合(union)，AND条件的相交(intersection)，组合前两种情况的联合和相交。通过EXPLAIN中的Extra可以看到这一点：
mysql>EXPLAIN SELECT film_id,actor_id FROM sakila.film_actor
->WHERE actor_id = 1 OR film_id = 1\G;
********************1. row****************************
...
...
Extra: Using union(PRIMARY,idx_fk_film_id),Using where
索引合并策略有时候是一种优化的结果，但实际上更多时候说明表上的索引建得很糟糕。
当出现服务器对多个索引做相交操作时(通常有多个AND条件)，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要多个索引做联合操作时(通常有多个OR操作)，通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。
更加具体的分析请参考
（二） 常见查询语句优化
1 COUNT
1. COUNT的作用
· COUNT(table.filed)统计的该字段非空值的记录行数
· COUNT(*)或者是COUNT(not nullable field) 统计的是全表的行数
如果要是统计全表记录数，COUNT(*)效率会比COUNT(not nullable field)要高一点
2. MYISAM的COUNT
一般执行COUNT操作时需要扫描大量的记录，但是在MyISAM引擎的数据库中，数据库把表的记录数保存起来，所以COUN(*)会非常的快（前提是不包含where条件）
3. 当需要频繁的使用COUNT时，可以考虑使用汇总表的策略
4. 优化小例子
在MYISAM中进行范围查询时，可以减少检索行数的小技巧
原始的：select count(*) from dictionary where id>5.
优化后：select (select count(*) fromdictionary)-count(*) from dictionary where id<=5
减少查询次数
优化前：需要两条语句
Select count(*)from student where area=’SH’
Select count(*)from student where area=’BJ’
优化后：合并成一条
select count(area='SH') as shcount, count(area='BJ') as bjcount from student;
2 优化关联查询
1. 确保ON或USING的字句上有索引
2. 一般情况下只需要在第二个表上创建索引
3. 尽量使 Group by/Order by的表达式中只包含一个表的字段
3 优化子查询
尽量用关联代替子查询
4 优化Group by 以及Distinct
1. 当对关联查询执行group by操作时，使用查询表的标识列作为分组条件效率会比较高
2. 当需要查询的非group by指定的字段时，正常情况下是无法执行的，可以通过inner join 的形式来弥补
例如：
selectfirstname, lastname
from actor
inner join(select actor_id,count(*) as cnt from actor group by(actor_id))
using (actor_id)；
3. group by默认会对查询的结果进行排序，数据量很大的时候可能会比较耗资源，如果你不关心查询结果的顺序，可以通过order by null 避免这种不必要的浪费
5 LIMIT分页
在进行分页查询的时候往往是采用select * from table1 limit 100,20 的方式来提取数据，在处理的过程中会读取120条数据，然后扔掉100条的offset记录,最后返回20条记录给客户端。如果offset的值非常大，效率上可能会有影响，可以尝试
1. 可以通过覆盖索引+inner join的方式来重写sql
selectfield1,field2,field3
from table1
inner join
(select id from table1 limit 100, 20)as temp
using(id)
2. 如果可以计算出明确的开始点和结束点，可以转换成 between and 的方式,这种方式只会扫描指定的行数，效率比较高
Select * from table1 between 100 and 120.
3. 可以通过位置标签的方式，来减少需要检索的记录数
例如 从某个位置开始。 Select * from table1 whereid>100 limit 20
tips：在进行分页处理的时候往往需要知道记录的总数，然后用这些总数生成页码。获取总数往往是使用count或是伴随一次全表查询得到的，这个过程也是检索所有的记录，然后再丢掉。为了避免这种浪费可以采取两种策略
· 把页码换成“下一页”的方式，这样就只需要去取固定的条数
· 一次性读取1000条，当一千条使用完后，采用“获取更多记录”的方式再获取1000条
（三）重构查询（通过拆分或者重组，充分利用索引的高效的性，减少回原表去取数据的个数）
1.一个复杂查询 or 多个简单查询
设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。
2.切分查询
有时候对于一个大查询我们需要“分而治之”，将大查询切分为小查询，每个查询功能完全一样，只完成一小部分，每次
只返回一小部分查询结果。
3.分解关联查询
select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql'
可以分解成下面这些查询来代替：
> select * from tag where tag = 'mysql'
> select * from tag_post where tag_id = 1234
> select * from post where post_id in (123, 456, 567, 9098, 8904)
优势：
让缓存的效率更高。
将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
查询本身效率也可能会有所提升。
可以减少冗余记录的查询，
更进一步，这样做相当于在应用中实现了哈希关联，而不是使用mysql的嵌套循环关联。
网友评论