Mysql优化

Com_select：执行SELECT操作的次数，一次查询只累加1。

Com_insert：执行INSERT操作的次数，对于批量插入的INSERT操作，只累加一次。

Com_update：执行UPDATE操作的次数。

Com_delete：执行DELETE操作的次数。

Innodb_rows_read：SELECT查询返回的行数。

Innodb_rows_inserted：执行INSERT操作插入的行数。

Innodb_rows_updated：执行UPDATE操作更新的行数。

Innodb_rows_deleted：执行DELETE操作删除的行数。

Connections：试图连接MySQL服务器的次数。

Uptime：服务器工作时间。

Slow_queries：慢查询的次数。

通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[= file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。具体可以查看本书第26章中日志管理的相关部分。

慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。

table：输出结果集的表。

type：表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如上

possible_keys：表示查询时可能使用的索引。

key：表示实际使用的索引。

key_len：使用到索引字段的长度。

rows：扫描行的数量。

Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

通过profile，我们能够更清楚地了解SQL执行的过程。

通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间

show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

MySQL 5.6提供了对SQL的跟踪 trace，通过 trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。

使用方式：

通过性能工具识别出性能差在哪

然后再对定位的问题逐一优化

B-Tree索引：最常见的索引类型，大部分引擎都支持B树索引。

HASH索引：只有Memory引擎支持，使用场景简单。

R-Tree索引（空间索引）：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。

Full-text（全文索引）：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB从MySQL 5.6版本开始提供对全文索引的支持。

搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。

使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个CHAR(200)列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。

利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。

对于InnoDB存储引擎的表，记录默认会按照一定的顺序保存，如果有明确定义的主键，则按照主键顺序保存。如果没有主键，但是有唯一索引，那么就是按照唯一索引的顺序保存。如果既没有主键又没有唯一索引，那么表中会自动生成一个内部列，按照这个列的顺序保存。按照主键或者内部列进行的访问是最快的，所以InnoDB表尽量自己指定主键，当表中同时有几个列都是唯一的，都可以作为主键的时候，要选择最常作为访问条件的列作为主键，提高查询的效率。另外，还需要注意，InnoDB 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。

1．MySQL中能够使用索引的典型场景

2．存在索引但不能使用索引的典型场景

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

分析表的语法如下： ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] . . 本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。

检查表的语法如下： CHECK TABLE tbl_name [, tbl_name] . . [option] . . option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED} 检查表的作用是检查一个或多个表是否有错误。CHECK TABLE对MyISAM和 InnoDB表有作用。对于MyISAM表，关键字统计数据被更新

优化表的语法如下： OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] . . 如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE命令只对MyISAM、BDB和 InnoDB表起作用。

MyISAM

InnoDB

如果同时从同一客户插入很多行，应尽量使用多个值表的INSERT语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个INSERT语句快（在大部分情况下，使用多个值表的 INSERT 语句能比单个 INSERT 语句快上好几倍）。下面是一次插入多值的一个例子：insert into test values(1,2),(1,3),(1,4)…

如果从不同客户插入很多行，可以通过使用 INSERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完成后才进行插入。

将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对MyISAM表使用。

当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。

mysql排序方式

优化思路

Filesort优化

默认情况下，MySQL对所有GROUP BY col1,col2,…的字段进行排序。这与在查询中指定ORDER BY col1,col2,…类似。因此，如果显式包括一个包含相同列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。

如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序

使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

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

对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“limit 1000,20”，此时 MySQL排序出前 1020条记录后仅仅需要返回第 1001到 1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。

优化思路

SQL提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。下面是一个使用SQL提示的例子：SELECT SQL_BUFFER_RESULTS * FROM...

常见例子：

MySQL利用REGEXP命令提供给用户扩展的正则表达式功能，REGEXP实现的功能类似UNIX上GREP和SED的功能，并且REGEXP在进行模式匹配时是区分大小写的。

MySQL中，产生随机数的方法是RAND()函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND()能够把数据随机排序。 mysql> select * from category order by rand() limit 5;

在SQL语句中，使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息

其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。

注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER BY是互相排斥的。此外，LIMIT用在ROLLUP后面。

如何共同使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作。这两个函数的一般用途就是做数值之间的逻辑位运算，但是，当把它们与 GROUP BY子句联合使用的时候就可以做一些其他的任务。

所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。

列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX中对大小写敏感，但在Windows或Mac OS X中对大小写不敏感。

外键设置只对InnoDB引擎起作用

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：mysql> show status like 'table%';

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

分支主题

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。

通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。

通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

1.事务及ACID属性

2．并发事务处理带来的问题

实现事务隔离的方式

3．事务隔离级别

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：mysql> show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，可以通过查询 information_schema 数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

锁模式

加锁方法

InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3种情形。

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-Key锁。

四种复制模式

第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。

几种避免死锁的措施

CHAR和VARCHAR

TEXT、BLOB长文本

浮点数与定点数

日期类型

（1）第一种方法是垂直拆分，即把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果一个表中某些列常用，而另一些列不常用，则可以采用垂直拆分，另外，垂直拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作。

（2）第二种方法是水平拆分，即根据一列或多列数据的值把数据行放到两个独立的表中。

1NF：每列不可分割，原子性

2NF：非主属性依赖于主属性，唯一性

3NF：消除非主属性传递依赖，即任意两个表，不能出现重复的非主键字段，去冗余

增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。

增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数。

重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。

分割表

批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。

数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。

另一种方式就是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题比较好的办法。

可以同时获取多个数据在应用中使用

查询缓存相关的参数主要有以下几个：mysql> show variables like '%query_cache%';

通过show status监视查询缓存的使用情况

主从复制