mysql优化技巧

by 夏泽民 Jan 5, 2020

1、选取最适用的字段属性

MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。

另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能

2、使用连接（JOIN）来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETEFROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）..替代。例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

SELECT*FROMcustomerinfo

WHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT*FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表

MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

SELECTName,PhoneFROMclientUNION

SELECTName,BirthDateFROMauthorUNION

SELECTName,SupplierFROMproduct

4、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN; INSERTINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem=’book’;COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem=’book’;

…

UPDATEinventorySETQuantity=11WHEREItem=’book’;UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

CREATETABLEcustomerinfo( CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATETABLEsalesinfo( SalesIDINTNOTNULL,CustomerIDINTNOTNULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB;

注意例子中的参数“ONDELETECASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

下面是应该注意的几个方面。

首先，最好是在相同类型的字段间进行比较的操作。

在MySQL3.23版之前，这甚至是一个必须的条件。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

其次，在建有索引的字段上尽量不要使用函数进行操作。

例如，在一个DATE类型的字段上使用YEAE()函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

第三，在搜索字符型字段时，我们有时会使用LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
例如下面的查询将会比较表中的每一条记录。

SELECT*FROMbooks HEREnamelike”MySQL%”

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT*FROMbooks HEREname＞=”MySQL”andname＜”MySQM”
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

优化Mysql数据库的8个方法
本文通过8个方法优化Mysql数据库：创建索引、复合索引、索引不会包含有NULL值的列、使用短索引、排序的索引问题、like语句操作、不要在列上进行运算、不使用NOT IN和<>操作

1、创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2、复合索引
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
3、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5、排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
7、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’;
8、不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

百万级数据库优化方案
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

3.应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or Name = ‘admin’
可以这样查询：

select id from t where num = 10
union all
select id from t where Name = ‘admin’

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)
用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

6.下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’
若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num = @num
可以改为强制查询使用索引：

select id from t with(index(索引名)) where num = @num
.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100
应改为:

select id from t where num = 100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ –生成的id
应改为:

select id from t where name like ‘abc%’
select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

13.Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

14.对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

15.select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

16.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

17.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

18.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

19.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

20.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

21.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

避免频繁创建和删除临时表，以减少系统表资源的消耗。临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

实际案例分析：拆分大的 DELETE 或INSERT 语句，批量提交SQL语句
　　如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
　　Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
　　如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂了。
　　所以，如果你有一个大的处理，你一定把其拆分，使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法
　　
　　运维角度浅谈MySQL数据库优化

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的，它是随着用户量的增加，基础架构才逐渐完善。这篇博文主要谈MySQL数据库发展周期中所面临的问题及优化方案，暂且抛开前端应用不说，大致分为以下五个阶段：

1、数据库表设计

项目立项后，开发部根据产品部需求开发项目，开发工程师工作其中一部分就是对表结构设计。对于数据库来说，这点很重要，如果设计不当，会直接影响访问速度和用户体验。影响的因素很多，比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞（死锁）等。当然，有测试工程师的团队，会做压力测试，找bug。对于没有测试工程师的团队来说，大多数开发工程师初期不会太多考虑数据库设计是否合理，而是尽快完成功能实现和交付，等项目有一定访问量后，隐藏的问题就会暴露，这时再去修改就不是这么容易的事了。

2、数据库部署

该运维工程师出场了，项目初期访问量不会很大，所以单台部署足以应对在1500左右的QPS（每秒查询率）。考虑到高可用性，可采用MySQL主从复制+Keepalived做双击热备，常见集群软件有Keepalived、Heartbeat。

双机热备博文：http://lizhenliang.blog.51cto.com/7876557/1362313

3、数据库性能优化

如果将MySQL部署到普通的X86服务器上，在不经过任何优化情况下，MySQL理论值正常可以处理2000左右QPS，经过优化后，有可能会提升到2500左右QPS，否则，访问量当达到1500左右并发连接时，数据库处理性能就会变慢，而且硬件资源还很富裕，这时就该考虑软件问题了。那么怎样让数据库最大化发挥性能呢？一方面可以单台运行多个MySQL实例让服务器性能发挥到最大化，另一方面是对数据库进行优化，往往操作系统和数据库默认配置都比较保守，会对数据库发挥有一定限制，可对这些配置进行适当的调整，尽可能的处理更多连接数。

具体优化有以下三个层面：

3.1 数据库配置优化

MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。

表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。

行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。

为什么会出现表锁和行锁呢？主要是为了保证数据的完整性，举个例子，一个用户在操作一张表，其他用户也想操作这张表，那么就要等第一个用户操作完，其他用户才能操作，表锁和行锁就是这个作用。否则多个用户同时操作一张表，肯定会数据产生冲突或者异常。

根据以上看来，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本中默认存储引擎。每个存储引擎相关联参数比较多，以下列出主要影响数据库性能的参数。

公共参数默认值：

max_connections = 151

#同时处理最大连接数，推荐设置最大连接数是上限连接数的80%左右

sort_buffer_size = 2M

#查询排序时缓冲区大小，只对order by和group by起作用，可增大此值为16M

open_files_limit = 1024

#打开文件数限制，如果show global status like ‘open_files’查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或卡死

MyISAM参数默认值：

key_buffer_size = 16M

#索引缓存区大小，一般设置物理内存的30-40%

read_buffer_size = 128K

#读操作缓冲区大小，推荐设置16M或32M

query_cache_type = ON

#打开查询缓存功能

query_cache_limit = 1M

#查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖

query_cache_size = 16M

#查看缓冲区大小，用于缓存SELECT查询结果，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值

InnoDB参数默认值：

innodb_buffer_pool_size = 128M

#索引和数据缓冲区大小，一般设置物理内存的60%-70%

innodb_buffer_pool_instances = 1

#缓冲池实例个数，推荐设置4个或8个

innodb_flush_log_at_trx_commit = 1

#关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。

innodb_file_per_table = OFF

#默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。

innodb_log_buffer_size = 8M

#日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

3.2 系统内核优化

大多数MySQL都部署在linux系统上，所以操作系统的一些参数也会影响到MySQL性能，以下对linux内核进行适当优化。

net.ipv4.tcp_fin_timeout = 30

#TIME_WAIT超时时间，默认是60s

net.ipv4.tcp_tw_reuse = 1

#1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接，0表示关闭

net.ipv4.tcp_tw_recycle = 1

#1表示开启TIME_WAIT socket快速回收，0表示关闭

net.ipv4.tcp_max_tw_buckets = 4096

#系统保持TIME_WAIT socket最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息

net.ipv4.tcp_max_syn_backlog = 4096

#进入SYN队列最大长度，加大队列长度可容纳更多的等待连接

在linux系统中，如果进程打开的文件句柄数量超过系统默认值1024，就会提示“too many files open”信息，所以要调整打开文件句柄限制。

vi /etc/security/limits.conf #加入以下配置，*代表所有用户，也可以指定用户，重启系统生效

soft nofile 65535

hard nofile 65535

ulimit -SHn 65535 #立刻生效

3.3 硬件配置

加大物理内存，提高文件系统性能。linux内核会从内存中分配出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时（如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。也就是说物理内存越大，分配缓存区越大，缓存数据越多。当然，服务器故障会丢失一定的缓存数据。

SSD硬盘代替SAS硬盘，将RAID级别调整为RAID1+0，相对于RAID1和RAID5有更好的读写性能（IOPS），毕竟数据库的压力主要来自磁盘I/O方面。

4、数据库架构扩展

随着业务量越来越大，单台数据库服务器性能已无法满足业务需求，该考虑加机器了，该做集群了~~~。主要思想是分解单台数据库负载，突破磁盘I/O性能，热数据存放缓存中，降低磁盘I/O访问频率。

4.1 主从复制与读写分离

因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作，主流的负载均衡器有LVS、HAProxy、Nginx。

怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率比较高。另一个种方式通过代理程序实现读写分离，企业中应用较少，常见代理程序有MySQL Proxy、Amoeba。在这样数据库集群架构中，大大增加数据库高并发能力，解决单台性能瓶颈问题。如果从数据库一台从库能处理2000 QPS，那么5台就能处理1w QPS，数据库横向扩展性也很容易。

有时，面对大量写操作的应用时，单台写性能达不到业务需求。如果做双主，就会遇到数据库数据不一致现象，产生这个原因是在应用程序不同的用户会有可能操作两台数据库，同时的更新操作造成两台数据库数据库数据发生冲突或者不一致。在单库时MySQL利用存储引擎机制表锁和行锁来保证数据完整性，怎样在多台主库时解决这个问题呢？有一套基于perl语言开发的主从复制管理工具，叫MySQL-MMM（Master-Master replication managerfor Mysql，Mysql主主复制管理器），这个工具最大的优点是在同一时间只提供一台数据库写操作，有效保证数据一致性。

主从复制博文：http://lizhenliang.blog.51cto.com/7876557/1290431

读写分离博文：http://lizhenliang.blog.51cto.com/7876557/1305083

MySQL-MMM博文：http://lizhenliang.blog.51cto.com/7876557/1354576

4.2 增加缓存

给数据库增加缓存系统，把热数据缓存到内存中，如果缓存中有要请求的数据就不再去数据库中返回结果，提高读性能。缓存实现有本地缓存和分布式缓存，本地缓存是将数据缓存到本地服务器内存中或者文件中。分布式缓存可以缓存海量数据，扩展性好，主流的分布式缓存系统有memcached、redis，memcached性能稳定，数据缓存在内存中，速度很快，QPS可达8w左右。如果想数据持久化就选择用redis，性能不低于memcached。

工作过程：

wKiom1VukrqyM-JcAABPhCy-LOM409.jpg

4.3 分库

分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。如果业务量很大，还可将切分后的库做主从架构，进一步避免单个库压力过大。

4.4 分表

数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你就该考虑是否把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。

分表技术比较麻烦，要修改程序代码里的SQL语句，还要手动去创建其他表，也可以用merge存储引擎实现分表，相对简单许多。分表后，程序是对一个总表进行操作，这个总表不存放数据，只有一些分表的关系，以及更新数据的方式，总表会根据不同的查询，将压力分到不同的小表上，因此提高并发能力和磁盘I/O性能。

分表分为垂直拆分和水平拆分：

垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。

水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。

4.5 分区

分区就是把一张表的数据根据表结构中的字段（如range、list、hash等）分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能，实现比较简单。

注：增加缓存、分库、分表和分区主要由程序猿来实现。

5、数据库维护

数据库维护是运维工程师或者DBA主要工作，包括性能监控、性能分析、性能调优、数据库备份和恢复等。

5.1 性能状态关键指标

QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数

TPS，Transactions Per Second：每秒处理事务数

通过show status查看运行状态，会有300多条状态信息记录，其中有几个值帮可以我们计算出QPS和TPS，如下：

Uptime：服务器已经运行的实际，单位秒

Questions：已经发送给数据库查询数

Com_select：查询次数，实际操作数据库的

Com_insert：插入次数

Com_delete：删除次数

Com_update：更新次数

Com_commit：事务次数

Com_rollback：回滚次数

那么，计算方法来了，基于Questions计算出QPS：

mysql> show global status like ‘Questions’;

mysql> show global status like ‘Uptime’;

QPS = Questions / Uptime

基于Com_commit和Com_rollback计算出TPS：

mysql> show global status like ‘Com_commit’;

mysql> show global status like ‘Com_rollback’;

mysql> show global status like ‘Uptime’;

TPS = (Com_commit + Com_rollback) / Uptime

另一计算方式：基于Com_select、Com_insert、Com_delete、Com_update计算出QPS

mysql> show global status where Variable_name in(‘com_select’,’com_insert’,’com_delete’,’com_update’);

等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPS

TPS计算方法：

mysql> show global status where Variable_name in(‘com_insert’,’com_delete’,’com_update’);

计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。

经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com_*计算比较准确。

5.2 开启慢查询日志

MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。

mysql> set global slow-query-log=on #开启慢查询功能

mysql> set global slow_query_log_file=’/var/log/mysql/mysql-slow.log’; #指定慢查询日志文件位置

mysql> set global log_queries_not_using_indexes=on; #记录没有使用索引的查询

mysql> set global long_query_time=1; #只记录处理时间1s以上的慢查询

分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。

# mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log #查看最慢的前三个查询

也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。

分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log

分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql

pt-query-digest –type=binlog mysql-bin.000001.sql

分析普通日志：pt-query-digest –type=genlog localhost.log

5.3 数据库备份

备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂得！但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。

Xtrabackup备份工具使用博文：http://lizhenliang.blog.51cto.com/7876557/1612800

5.4 数据库修复

有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck。

myisamchk：只能修复myisam表，需要停止数据库

常用参数：

-f –force 强制修复，覆盖老的临时文件，一般不使用

-r –recover 恢复模式

-q –quik 快速恢复

-a –analyze 分析表

-o –safe-recover 老的恢复模式，如果-r无法修复，可以使用此参数试试

-F –fast 只检查没有正常关闭的表

快速修复weibo数据库:

# cd /var/lib/mysql/weibo

# myisamchk -r -q *.MYI

mysqlcheck：myisam和innodb表都可以用，不需要停止数据库，如修复单个表，可在数据库后面添加表名，以空格分割

常用参数：

-a –all-databases 检查所有的库

-r –repair 修复表

-c –check 检查表，默认选项

-a –analyze 分析表

-o –optimize 优化表

-q –quik 最快检查或修复表

-F –fast 只检查没有正常关闭的表

快速修复weibo数据库:

mysqlcheck -r -q -uroot -p123 weibo

5.5 另外，查看CPU和I/O性能方法

#查看CPU性能

wKiom1VtPFmCEtY9AADbdiZbn9A400.jpg

#参数-P是显示CPU数，ALL为所有，也可以只显示第几颗CPUwKioL1VtPpayB7WeAALQHX41buc367.jpg

#查看I/O性能

wKiom1VtPSXTsI4zAAMkfVf2r-I743.jpg

#参数-m是以M单位显示，默认K

#%util：当达到100%时，说明I/O很忙。

#await：请求在队列中等待时间，直接影响read时间。

I/O极限：IOPS（r/s+w/s）,一般RAID0/10在1200左右。（IOPS，每秒进行读写（I/O）操作次数）

I/O带宽：在顺序读写模式下SAS硬盘理论值在300M/s左右，SSD硬盘理论值在600M/s左右。

1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。

2、使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如，我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询，如下所示：

DELETE FROM customerinfo

WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：

SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID

WHERE salesinfo.CustomerID IS NULL

连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。下面的例子就演示了一个使用UNION的查询。

复制代码
SELECT Name,Phone FROM client UNION

SELECT Name,BirthDate FROM author UNION

SELECT Name,Supplier FROM product
复制代码

4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN;
INSERT INTO salesinfo SET CustomerID=14;UPDATE inventory SETQuantity=11 WHER Eitem=’book’;
COMMIT;

5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK
TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item=’book’;

…

UPDATE inventory SET Quantity=11 WHERE Item=’book’;
UNLOCKTABLES

6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

CREATE TABLE customerinfo(
CustomerID
INT NOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;
复制代码
CREATE TABLE salesinfo( SalesID INT NOT NULL,CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID,SalesID),
FOREIGN KEY(CustomerID)REFERENCES customerinfo(CustomerID)ON DELETE CASCADE)TYPE=INNODB;
复制代码
注意例子中的参数“ONDELETECASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。

7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

下面是应该注意的几个方面。

首先，最好是在相同类型的字段间进行比较的操作。

其次，在建有索引的字段上尽量不要使用函数进行操作。

SELECT * FROM books WHERE name like”MySQL%”

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT * FROM books WHERE name＞=”MySQL”and name＜”MySQM”
最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。

一、关系型数据库在项目中通常会遇到哪些问题或者说是瓶颈呢？？
这里简单的举例如下：

高并发读写操作数据库，比如我们的双十一购物场景 –> 解决：集群、分布式

海量数据的高效率读写，比如在一张包含海量数据的表中查询，插入速度会很慢（ex：淘宝，京东等商城项目中的商品表） –> 解决：分表，分库

高扩展性和可用性

在基于web的结构（即浏览器/服务器）当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。对于很多需要提供24小时不间断服务的网站来说，对数据库系统进行升级和扩展是非常痛苦的事情，往往需要停机维护和数据迁移.

也就是说我们：动态的去添加服务器，一台数据库服务器扩充到多台时，不下电情况下是很难做到的。
单点故障：一台数据库服务器挂了。业务就中断，期望去找还好的数据库继续提供服务。
二、那么关系型数据库如何优化？？
对关系型数据库的优化是一个综合性的技术，主要包括
1、找出执行效率低Sql（定位慢查询） -发现问题
2、分析慢Sql -分析问题
3、优化 -解决问题
3.1 单机（表结构、索引、Sql(代码问题)）：
表
合适表结构 3NF和反3NF
合适引擎
索引
分表(垂直分表和水平分表)
分区(了解)
Sql优化技巧
3.2 多机（IO瓶颈）：
读写分离（集群，让多台服务器提供服务）
分布式（把不同的业务分给不同的集群处理）
3.3 缓存方案
1、优化方案不仅可以适用在mysql,还可以使用于oracle等关系型数据库,只是命令不同罢了。
2、优化时先考虑单台数据库服务器，如果单台优化完成后,实在满足不了需求再考虑多机集群和分布式。(有的公司不会使用多台数据库)
1、定位慢查询 - 找出执行慢的sql
1.1 原理：

当我们使用数据库，实质上就是连接数据库，发送SQL、服务器执行SQL并返回结果、关闭连接。这期间所有的sql都是mysql执行，当它发现客户端执行sql慢的时候，会把结果记录下来。方便用户查找定定位。

1.2.1 查看数据库基本状态： (比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete… / 当前连接数/最大连接数等)

– 1.查看数据库运行时间（单位：秒）
show status like ‘uptime’

– 2.查看数据库crud次数（作为选择存储引擎的依据）
show status like ‘%Com_%’

crud：

show status like ‘%Com_select%’
show status like ‘%Com_insert%’
show status like ‘%Com_update%’
show status like ‘%Com_delete%’

Show session/global status like ‘%Com_select%’
☞ show [session|global] status like …. 如果你不写[session|global] 默认是session 会话(指取出当前窗口的执行)，如果你想看所有(从mysql 启动到现在)，则应该 global。

INNODB
show status like ‘%Innodb_rows%’;

– 3.查看所有连接数
show status like ‘connections’

– 4.查看服务器最大连接数（可根据此设置数据库最大并发连接数的依据，一般 mysql的最大连接数默认是100, 最大可以达到16384（理论上））
show status like ‘max_used_connections’

1.2.2 定位慢查询

– 1.查看慢查询，通常指花了2S以上的查询(默认10s)
show status like ‘slow_queries’

– 2.查看当前服务器慢查询的定义时间
show variables like ‘long_query_time’

– 3.修改服务器慢查询时间（注意：默认不写set是session，即在当前的的窗口中才有效果,如果关闭窗口后打开新窗口有效果则加上global关键字）
set long_query_time=0.5
set global long_query_time=0.5

– 4.将慢查询记录到日志中
#mysql安装的bin目录下执行
mysqld.exe –safe-mode –slow-query-log #[mysql5.5版本可以在my.ini指定]

注意事项：

必须在mysql的安装目录执行，因为执行的使用依赖于my.ini,
慢查询日志会输出到data.dir
以下文件不要乱删，乱修改，会导致mysql没法启动

1.2.3 步骤总结：

1）关闭原有mysql服务
2）以支持记录慢sql的方式来启动
3）设置慢查询时间阀值
4）构造慢sql
5）到日志文件中找慢sql（data.dir）

什么时候开启慢查询？ - ①②
系统中所有sql都执行一遍，才能判断是否有慢sql。什么时候开启能覆盖所有sql执行？
①开发者自验：开发完成后，需要统一打包，统一部署，统一验证。
②测试人员测试：测试人员需要测试所有功能。
③项目上线：用户用了所有功能。
2、分析慢查询 - 分析SQL语句
explain+sql语句 -》用于分析sql （主要看：type如果为all 表示全表扫描 possible_keys 可能用到的索引 keys 实际使用的索引 rows扫描的行数）

末尾加\G 表示格式化

3、单机优化
3.1 表结构设计

3.1.1 范式（规范）- 常见有三种规范 3NF

1NF:原子性列不可分割
2NF:记录具有唯一标识通常通过主键实现
3NF:表中尽量不要有冗余数据,能够通过其他表推导出来的数据不要单独设置字段
—
反3NF:没有冗余的数据库表未必是最好的数据库表，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
ex：单价和总价
3.1.2 存储引擎

分类 - mysql中： myisam，innodb，memory

优缺点：

MyISAM 和 INNODB的区别：

1.事务安全 — MyISAM不支持事务，INNODB支持
2.查询和添加速度 — MyISAM速度快，INNODB速度慢
3.支持全文索引 — MyISAM支持，INNODB不支持
4.锁机制 — MyISAM表锁 INNODB行锁
5.外键 — MyISAM:不支持外键， INNODB:支持外键. (通常不设置外键，通常是在程序中保证数据的一致)
使用场景:

MyISAM存储引擎:如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的发帖表，回复表.

INNODB存储引擎: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

Memory存储:比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.

操作:

创建表时指定存储引擎:
Create table 表名(字段列表) engine 存储引擎名称;

注意：如果不指定则使用默认的存储引擎，这个默认在my.ini配置

修改存储引擎：
alter table table_name engine=innodb;

3.2 索引

3.2.1 引入

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行个正确的‘create index’，查询速度就可能提高百倍千倍，美滋滋。可是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。  1 3.2.2 索引是什么？

索引是帮助DBMS高效获取数据的一种数据结构
原理：以空间换时间
组织数据方式，及访问数据的api的封装。-list，set，map数组

使用索引查询速度提高但以插入、更新、删除的速度为代价！！
索引算法：

MySQL常用引擎允许的索引类型

FullText全文索引算法，myisam只能能在char vachar text

hash就像Map,通过一个key直接就能找到value

B-tree算法

总结：使用索引把全表查找变为索引查找，减少查询次数，增加查询效率。而索引查找效率的取决于索引算法。也就是索引（Index）是帮助DBMS高效获取数据的数据结构

3.2.3 操作

mysql中索引的分类：普通索引，唯一索引，主键索引，全文索引
1.普通索引：允许重复的值出现，可以在任何字段上面添加
2.唯一索引：除了不能有重复的记录外，其它和普通索引一样，可以在值是唯一的字段添加（用户名、手机号码、身份证、email，QQ），可以为null,并且可以有多个null
3.主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会给该列创建索引。这就是主键索引.唯一且没有null值（也被称为非聚集索引）
4.全文索引：用来对表中的文本域(char，varchar，text)进行索引，全文索引针对MyISAM有用
CRUD：

1）添加
1.一般来说，普通索引的创建，是先创建表，然后在创建普通索引
create index 索引名 on 表 (列1,列名2,…);
alter table 表名 add index 索引名(列1,列名2,..);
ex:
create table aaa(id int unsigned,name varchar(32));
create index nameIndex on aaa(name);
alter table aaa add index index1(name);
2.唯一索引:
①当表的某列被指定为unique约束时，这列就是一个唯一索引
ex：create table bbb(id int primary key auto_increment , name varchar(32) unique); – 这时, name列就是一个唯一索引.
②在创建表后，再去创建唯一索引
create unique index 索引名 on 表名 (列1,列2,..);
alter table 表名add unique index 索引名 (列1,列2,..);
ex：create table ccc(id int primary key auto_increment, name varchar(32));
注意：unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.
主键字段不能为NULL,也不能重复.

3.主键索引：
①创建表时指定主键
ex：create table ddd(id int unsigned primary key auto_increment ,name varchar(32) not null defaul ‘’); – 这时id 列就是主键索引.
②如果你创建表时，没有指定主键，也可以在创建表后，再添加主键。
alter table 表名 add primary key (列名);
ex: create table eee(id int , name varchar(32) not null default ‘’);
alter table eee add primary key (id);

4.全文索引:
①创建表时定义：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=myisam charset utf8;
②创建表完成后定义
create fulltext index 索引名 on 表名(列1,列2);
alter table 表名add fulltext index 索引名 (列1,列2);
ex:
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT
)engine=myisam charset utf8;

Alter table article add fulltext index title_body_fulltext_index(title,body);

用法：
错误用法:
explain select * from articles where body like ‘%mysql%’; – 不会使用到全文索引
正确用法:
explain select * from articles where match(title,body) against(‘database’); – 会使用全文索引

2）查询
show index(es) from 表名

3）删除
alter table 表名 drop index 索引名;
alter table 表名 drop primary key 删除主键。 – 主键定义为auto_increment时不能删除

4）修改 : =先删除后添加
3.2.4 注意事项：

索引的代价:
1.占用磁盘空间。
2.对dml操作有影响,因为要维护索引，变慢。

在哪些列上适合添加索引?
较频繁的作为查询条件字段应该创建索引
select * from emp where empno = 1
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
select * from emp where sex = ‘男’
更新非常频繁的字段不适合创建索引
select * from emp where logincount = 1

总结: 满足以下条件的字段，才应该创建索引.
a: 肯定在where条件经常使用或者经常用来排序 order by后面的字段
b: 该字段的内容不是唯一的几个值(sex)
c: 字段内容不是频繁变化.
不会出现在WHERE子句中字段不该创建索引

3.2.5 小技巧：

根据索引列的多少分为复合索引和普通索引

普通索引（单列索引）：该索引只在一个列上面创建

复合索引（多列索引）：该索引只在多个列上面创建

1.对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
alter table dept add index my_indx (dname,loc); // dname 左边的列,loc就是右边的列
explain select * from dept where dname=’aaa’\G 会使用到索引
explain select * from dept where loc=’aaa’\G 就不会使用到索引
2.对于使用like的查询，查询如果是%aaa’不会使用到索引而‘aaa%’会使用到索引。
explain select * from dept where dname like ‘%aaa’\G不能使用索引
explain select * from dept where dname like ‘aaa%’\G使用索引
所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用全文索引->sphinx.
3.如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引.
explain select * from dept where dname = ‘aaa’;
explain select * from dept where loc = ‘aaa’;
select * from dept where dname=’xxx’ or loc=’xx’;
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
expain select * from dept where dname=’111’;
expain select * from dept where dname=111; （数值自动转字符串）
expain select * from dept where dname=qqq; 报错
也就是，如果列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来.
5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。
表里面只有一条记录

3.3 分表

分为水平分割(行)和垂直分割（列）
3.3.1 水平分表

一张表的数据太多时候要分表 ex:一张表有1000条数据水平拆分成10个100条数据

如果一张表中数据量巨大时，我们要经常查询。则可以按照合适的策略拆分为多张小表。尽量在单张表中查询，减少扫描次数，增加查询效率。如果拆分不好，经常组合（union）查询，还不如不拆分.

分表策略:
1.按时间分表:这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。
2.按区间范围分表:一般在有严格的自增id需求上，如按照user_id水平分表：
table_1 user_id从1~100w
table_2 user_id从100W+1~200w
table_3 user_id从200W+1~300w
3.hash分表:通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。
最简单hash算法： T_user + Id%100+1 复杂hash算法：…
3.3.2 垂直分表

如果一张表某个字段，信息量大且不经常查询，则可以考虑把这些字段，单独的放入到另一张表中，用外键关联。如果硬是要查询，就是用跨表查询(join)

3.4 分区

什么是分区？

不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。
1
查看数据库是否支持

SHOW VARIABLES LIKE ‘%partition%’;
1
分区作用？

从MySQL 5.1 中新增了分区(Partition)功能，优势也越来越明显了：
1.与单个磁盘或文件系统分区相比，可以存储更多的数据
2.很容易就能删除不用或者过时的数据
3.一些查询可以得到极大的优化可以并发查询
4.涉及到 SUM()/COUNT() 等聚合函数时，可以并发进行
5.IO吞吐量更大（多台服务器）
分区方式？

常见分区方式：
①Range（范围） –基于一个给定的连续空间，把数据分配到不同分区。1-10 11-20

②List（预定义列表） –类似Range分区，区别在List分区是基于枚举出的值列表分区，而 Range分区是根据给定的连续区间范围分区 1,2，3 4，5,6

③Hash（哈希）–这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。这个根据给定的分区个数，把数据分配到不同的分区。

④Key（键值）-上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
Composite（复合模式） –以上模式的组合使用　
分区空值处理：

1、range方式，默认放入最小值分区
2、list方式，必须指定null值匹配
3、hash方式，默认当成0
1
2
3
3.5 SQL优化小技巧变多次维护为一次维护

SQL：ddl dml dql

DDL优化：

1 、通过禁用索引来提供导入数据性能。这个操作主要针对有数据库的表，追加数据
//去除键
alter table test3 DISABLE keys;
//批量插入数据
insert into test3 select * from test;
//恢复键
alter table test3 ENABLE keys;
变多次索引维护为一次索引维护

2、关闭唯一校验
set unique_checks=0 关闭
//批量插入数据
insert into test3 select * from test;
set unique_checks=1 开启
变多次唯一校验为一次唯一校验

3、修改事务提交方式(导入)
set autocommit=0 关闭
//批量插入
set autocommit=1 开启
变多次事务提交为一次事务提交
DML优化：

insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);
//合并多条为一条
insert into test values(1,2),(1,3),(1,4)
变多次事务提交为一次事务提交
DQL优化：

放弃索引进行全表扫描要注意:!= <> is null or in not like模糊查询前置匹配

索引创建原则:在where或order by后面的列考虑建立索引经常被查询且唯一性高的字段不频繁更改的字段

1）1 order by优化
1.多用索引排序
2.普通结果排序（非索引排序）Filesort
索引本身就是排序的，所以多使用索引。
2）group by优化
查询某个时间的付款总和
explain
select DATE_FORMAT(payment_date,’%Y-%m’),sum(amount) from payment GROUP BY DATE_FORMAT(payment_date,’%Y-%m’) ;
explain
select DATE_FORMAT(payment_date,’%Y-%m’),sum(amount) from payment GROUP BY DATE_FORMAT(payment_date,’%Y-%m’) order by null;
在group by是使用order by null,取消默认排序

3） subQuery嵌套优化
在客户列表找到不在支付列表的客户
#在客户列表找到不在“支付列表”的客户 , 查询没买过东西的客户
explain
select * from customer where customer_id not in (select DISTINCT customer_id from payment); #子查询 – 这种是基于func外链

explain
select * from customer c left join payment p on(c.customer_id=p.customer_id) where p.customer_id is null – 这种是基于“索引”外链

4）or优化
在两个独立索引上使用or的性能优于
1. or两边都是用索引字段做判断，性能好！！
2. or两边，有一边不用，性能差
3. 如果employee表的name和email这两列是一个复合索引，但是如果是 :name=’A’ OR email=’B’ 这种方式，不会用到索引！

5）limit优化
select film_id,description from film order by title limit 50,5;

select a.film_id,a.description from filqm a inner join (select film_id from film order by title limit 50,5)b on a.film_id=b.film_id
30中sql语句优化
https://www.cnblogs.com/Little-Li/p/8031295.html
4、多机优化
4.1 集群(读写分离)

原理：

集群:主从同步+读写分离+主备切换
主从同步:在主服务器中配置生成二进制文件并授权给一个账户从服务器拿到该账户去访问二进制文件还原成自己的日志再还原成数据
读写分离:通过mysql_proxy来代理管理主从数据库服务器并对外暴露一个虚拟的端口(主从服务器中应该有同一个用户)
主备切换:防止单点故障
具体如何配置可自行百度~

4.2 分布式

按业务分要访问哪一个集群，即将不同模块的业务分到不同数据库集群上面

5、缓存
详情看redis部分

MySQL数据库优化
云吞铺子先模拟一下数据库访问流程，然后逐步的优化这些环节，可以从减少数据访问（减少磁盘访问）、返回更少数据（减少网络传输或磁盘访问）、减少交互次数（减少网络传输）、减少服务器CPU开销（减少CPU及内存开销）和利用更多资源（增加资源）几个方面来提升数据库性能。

可以从以下及方面优化MySQL数据库：

一：数据库结构优化
1）范式优化：表的设计合理化（符合3NF），比如消除冗余（节省空间）；
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表：分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法，可按月自动建表分区。
二：优化SQL语句
1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描；
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0
3）很多时候用exists代替in是一个好的选择；
4）用Where子句替换HAVING子句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤；
5）迅速的定位执行速度慢的语句、开启慢查询、设置慢查询时间、启用慢查询日志、通过mysqldumoslow工具对慢日志进行分类汇总；
6）析SQL语句，通过explain分析查询、通profiling可以得到更详细的信息；
7）创建索引（主键索引/唯一索引/全文索引/普通索引）；
8）避免Select * （不查询多余的列与行）；
9）使用视图（经常被查询的列数据，并且这些数据不被经常的修改，删除）；
三：分表技术（水平分割、垂直分割）、分区技术
如果遇到大表的情况下，SQL语句优化已经无法继续优化了，我们可以考虑分表和分区，目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

1）都能提高mysql的性能，在高并发状态下都有一个良好的表现；
2）分表和分区不矛盾，可以相互配合的，对于那些大访问量，并且表数据比较多的表，我们可以采取分表和分区结合的方式，访问量不大，但是表数据很多的表，我们可以采取分区的方式等；
3）分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系；
4）表分区相对于分表，操作方便，不需要创建子表。
四：读写分离
MySQL读写分离可以参考：阿里云MySQL读写分离详解，本文来说说抛开阿里云来实现读写分离的方法：

方法一：php程序上自己做逻辑判断，写php代码的时候，自己在程序上做逻辑判读写匹配。select，insert、update、delete做正则匹配，根据结果选择写服务器（主服务器）。如果是select操作则选择读服务器（从服务器器） mysql_connect(‘读写的区分’)
方法二：MySQL中间件，基本的原理是让主数据库处理写操作（insert、update、delete），而从数据库处理查询操作（select）。而数据库的一致性则通过主从复制来实现。所以说主从复制是读写分离的基础。
五：存储过程 [模块化编程，可以提高速度]
存储过程是SQL语句和控制语句的预编译集合，保存在数据库中，可有应用程序调用执行，而且允许用户声明变量、逻辑控制语句及其他强大的编程功能。包含逻辑控制语句和数据操作语句，可以接收参数、输出参数、返回单个或多个结果值及返回值。

使用存储过程的优点：模块化程序设计，只需创建一次，以后即可调用该存储过程任意次；执行速度快，效率高；减少网络流量；具有良好的安全性。

六：对mysql配置优化 [配置最大并发数, 调整缓存大小]
MySQL数据库优化大全方法汇总

MySQL数据库优化
七：MySQL服务器硬件升级
MySQL服务器硬件升级云吞铺子就不多赘述了，也可以使用云数据库，参考：阿里云MySQL云服务器详解

八：定时的去清除不需要的数据,定时进行碎片整理
1）查看表碎片的方法

2）Innodb存储引擎清理碎片方法：

3）Myisam存储引擎清理碎片方法：

注意：MySQL碎片整理尽量选择业务不繁忙时清理，一个月清理一次即可

Category storage