优化数据库步骤
通过 show status命令了解各种SQL的执行频率
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语句
通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[= file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。具体可以查看本书第26章中日志管理的相关部分。
慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
通过EXPLAIN分析低效SQL的执行计划
select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
table：输出结果集的表。
type：表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如上
All：全表扫描，Mysql遍历全表来找到匹配的行
index：索引全扫描，Mysql遍历整个索引来查询匹配的行
range：索引范围扫描，常见于<、<=、>、>=、between等操作符
ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行
eq_ref：类似ref，区别就在于使用的索引是唯一索引，对每个索引键值、表中只有一条记录匹配；简单来说就是多表连接中使用primary key或者unique index作为关联条件
const/system：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引 unique index进行的查询。
NULL：mysql不用访问表或者索引，直接能够获得结果
possible_keys：表示查询时可能使用的索引。
key：表示实际使用的索引。
key_len：使用到索引字段的长度。
rows：扫描行的数量。
Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
通过show profile 分析SQL
通过profile，我们能够更清楚地了解SQL执行的过程。
通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间
show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
通过 trace分析优化器如何选择执行计划
MySQL 5.6提供了对SQL的跟踪 trace，通过 trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。
使用方式：
1.首先打开trace，设置格式为JSON，设置 trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。
mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
2.接下来执行想做trace的SQL语句
3.检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何执行SQL的：
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G；
最后会输出一个跟踪文件
确定问题并采取相应的优化措施
通过性能工具识别出性能差在哪
然后再对定位的问题逐一优化
索引优化
索引存储分类
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 表的普通索引都会保存主键的键值，所以主键要尽可能选择较短的数据类型，可以有效地减少索引的磁盘占用，提高索引的缓存效果。
Mysql如何使用索引
1．MySQL中能够使用索引的典型场景
（1）匹配全值（Match the full value），对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
（2）匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找
（3）匹配最左前缀（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找
（4）仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高
（5）匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
（6）能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）
（7）如果列名是索引，那么使用 column_name is null就会使用索引（区别于Oracle）。
（8）MySQL 5.6引入了 Index Condition Pushdown（ICP）的特性，进一步优化了查询。
2．存在索引但不能使用索引的典型场景
（1）以%开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引
（2）数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索。
（3）复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的
（4）如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
（5）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
查看索引使用情况
如果索引正在工作，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表起作用。
常用SQL语句优化
大批量导入数据
MyISAM
对于MyISAM存储引擎的表，可以通过以下方式快速地导入大量的数据。
ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS;
DISABLE KEYS和ENABLE KEYS用来打开或者关闭MyISAM表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。
InnoDB
（1）因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
（2）在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
（3）如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
优化Insert语句
如果同时从同一客户插入很多行，应尽量使用多个值表的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倍。
优化order by语句
mysql排序方式
第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用 explain 分析查询的时候显示为Using Index，不需要额外的排序，操作效率较高，
第二种是通过对返回数据进行排序，也就是通常说的 Filesort 排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。
优化思路
尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort。
Filesort优化
两次扫描算法（Two Passes）：首先根据条件取出排序字段和行指针信息，之后在排序区 sort buffer中排序。如果排序区 sort buffer不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL 4.1之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机I/O操作；优点是排序的时候内存开销较少。
一次扫描算法（Single Pass）：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。
优化group by语句
默认情况下，MySQL对所有GROUP BY col1,col2,…的字段进行排序。这与在查询中指定ORDER BY col1,col2,…类似。因此，如果显式包括一个包含相同列的ORDER BY子句，则对MySQL的实际执行性能没有什么影响。
如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序
优化嵌套查询
使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。
连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
mysql如何优化or条件
对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“limit 1000,20”，此时 MySQL排序出前 1020条记录后仅仅需要返回第 1001到 1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。
优化思路
1.在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。这种方式让MySQL扫描尽可能少的页面来提高分页效率。
2.把LIMIIT查询转换成某个位置的查询。这样把LIMIT m,n 转换成LIMIT n的查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。
使用SQL提示
SQL提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。下面是一个使用SQL提示的例子：SELECT SQL_BUFFER_RESULTS * FROM...
常见例子：
1.USE INDEX
在查询语句中表名的后面，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
mysql> explain select count(*) from rental use index (idx_rental_date)\G
2.IGNORE INDEX
如果用户只是单纯地想让MySQL忽略一个或者多个索引，则可以使用 IGNORE INDEX作为HINT。同样是上面的例子，这次来看一下查询过程忽略索引ind_sales2_id的情况：
mysql> explain select count(*) from rental ignore index (idx_rental_date)\G
3.FORCE INDEX
为强制MySQL使用一个特定的索引，可在查询中使用FORCE INDEX作为HINT。例如，当不强制使用索引的时候，因为大部分库存inventory_id的值都是大于1的，因此MySQL会默认进行全表扫描，而不使用索引，如下所示：
mysql> explain select * from rental where inventory_id > 1\G
常用SQL技巧
正则表达式使用
MySQL利用REGEXP命令提供给用户扩展的正则表达式功能，REGEXP实现的功能类似UNIX上GREP和SED的功能，并且REGEXP在进行模式匹配时是区分大小写的。
巧用rand()提取随机行
MySQL中，产生随机数的方法是RAND()函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND()能够把数据随机排序。
mysql> select * from category order by rand() limit 5;
利用group by的with rollup子句
在SQL语句中，使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息
其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER BY是互相排斥的。此外，LIMIT用在ROLLUP后面。
用bit group functions做统计
如何共同使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作。这两个函数的一般用途就是做数值之间的逻辑位运算，但是，当把它们与 GROUP BY子句联合使用的时候就可以做一些其他的任务。
数据库名、表大小写问题
所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX中对大小写敏感，但在Windows或Mac OS X中对大小写不敏感。
使用外键需要注意的问题
外键设置只对InnoDB引擎起作用
