第一篇：SQL优化
声明：本学习笔记是拜读“深入浅出MySQL数据库开发、优化与维护管理 第2版 唐汉明等著”
推荐大家购买阅读。
任何转转或引用等请注明书籍。
一、优化SQL的一般步骤
1.通过show [session|globa]lstatus命令了解各种SQL的执行频率。
默认session，当前连接的统计结果。Global自数据库上次启动至今的统计结果。
mysql> show status like 'Com_%';
Com_select:执行select操作的次数，一次查询只累加1。
Com_insert:执行insert操作的次数，对于批量插入的INSERT操作，只累加一次。
Com_update:执行update操作的次数。
Com_delete:执行DELETE操作的次数。
上述对所有存储引擎的表操作都会进行累计。
下面主要针对InnoDB存储引擎的，累加的算法也略有不同。
Innodb_rows_read: SELECT查询返回的行数。
Innodb_rows_inserted:执行INSERT操作插入的行数。
Innodb_rows_updated:执行UPDATE操作更新的行数。
Innodb_rows_deleted:执行DELETE操作删除的行数。
通过以上参数了解当前数据库的应用是以插入更新为主还是以查询操作为主。以及各类型的SQL大致的执行比例是多少。
对于更新操作的计数，不论是提交还是回滚都进行累加。
对于事务型的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于用户了解数据库的基本情况。
Connection： 试图连接MySQL服务器的次数
Uptime: 服务器工作时间Slow_queries: 慢查询的次数
2.定位执行效率较低的SQL语句。
两种方法：
通过慢查询日志--log-slow-queries[=file_name]选项启动时，Mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
慢查询日志在查询结束以后才记录，所以在应用反映执行效率慢的时候查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
3.通过EXPLAIN分析低效率SQL的执行计划。
通过以上步骤查询到效率低的SQL语句后，通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句在执行过程中表如何连接和连接的顺序。
常见访问类型：从左到右，性能由最差到最好。
ALL index range ref eq_ref const,system NULL
4.通过show profile分析SQL
检查当前数据库是否支持：
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set (0.00 sec)
默认profiling是关闭的，可以通过set语句在Session级别开启profiling:
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from user_gps_log;
mysql> show profiles
-> ;
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.03068625 | SELECT DATABASE() |
| 2 | 0.00245700 | show tables |
| 3 | 0.36816600 | select count(*) from user_gps_log |
+----------+------------+-----------------------------------+
3 rows in set (0.02 sec)
mysql> show profile for query 3;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000156 |
| Waiting for query cache lock | 0.000047 |
| checking query cache for query | 0.000096 |
| checking permissions | 0.000053 |
| Opening tables | 0.000079 |
| System lock | 0.000055 |
| Waiting for query cache lock | 0.000114 |
| init | 0.000060 |
| optimizing | 0.000050 |
| statistics | 0.000057 |
| preparing | 0.000055 |
| executing | 0.000083 |
| Sending data | 0.122287 |
| end | 0.000148 |
| query end | 0.000051 |
| closing tables | 0.000059 |
| freeing items | 0.000054 |
| Waiting for query cache lock | 0.000047 |
| freeing items | 0.000102 |
| Waiting for query cache lock | 0.000048 |
| freeing items | 0.000046 |
| storing result in query cache | 0.000048 |
| logging slow query | 0.000047 |
| cleaning up | 0.000048 |
+--------------------------------+----------+
24 rows in set (0.04 sec)
注意：Sending data是指MySQL线程开始访问数据行并把结果返回客户端，而不仅仅是返回结果给客户端，在此状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的。
为了更清晰地看到排序结果，可以查询INFORMATION_SCHEMA.PROFILING表并按照时间做个DESC排序：
mysql> select STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=@query_id ),2) AS Pct_r, count(*) as calls, SUM(DURATION) / COUNT(*) AS "R/call" FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id GROUP BY STATE ORDER BY Total_R desc;
+--------------------------------+----------+-------+-------+--------------+
| STATE | Total_R | Pct_r | calls | R/call |
+--------------------------------+----------+-------+-------+--------------+
| Sending data | 0.122287 | 98.71 | 1 | 0.1222870000 |
| Waiting for query cache lock | 0.000256 | 0.21 | 4 | 0.0000640000 |
| freeing items | 0.000202 | 0.16 | 3 | 0.0000673333 |
| starting | 0.000156 | 0.13 | 1 | 0.0001560000 |
| end | 0.000148 | 0.12 | 1 | 0.0001480000 |
| checking query cache for query | 0.000096 | 0.08 | 1 | 0.0000960000 |
| executing | 0.000083 | 0.07 | 1 | 0.0000830000 |
| Opening tables | 0.000079 | 0.06 | 1 | 0.0000790000 |
| init | 0.000060 | 0.05 | 1 | 0.0000600000 |
| closing tables | 0.000059 | 0.05 | 1 | 0.0000590000 |
| statistics | 0.000057 | 0.05 | 1 | 0.0000570000 |
| System lock | 0.000055 | 0.04 | 1 | 0.0000550000 |
| preparing | 0.000055 | 0.04 | 1 | 0.0000550000 |
| checking permissions | 0.000053 | 0.04 | 1 | 0.0000530000 |
| query end | 0.000051 | 0.04 | 1 | 0.0000510000 |
| optimizing | 0.000050 | 0.04 | 1 | 0.0000500000 |
| cleaning up | 0.000048 | 0.04 | 1 | 0.0000480000 |
| storing result in query cache | 0.000048 | 0.04 | 1 | 0.0000480000 |
| logging slow query | 0.000047 | 0.04 | 1 | 0.0000470000 |
+--------------------------------+----------+-------+-------+--------------+
19 rows in set (0.01 sec)
在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block、io、context、switch、page faults等明细类型来看MySQL在使用什么资源上耗费了过高的时间，例如，选择查看CPU的耗费时间：
mysql> show profile cpu for query 4;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000156 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000047 | 0.000000 | 0.000000 |
| checking query cache for query | 0.000096 | 0.000000 | 0.000000 |
| checking permissions | 0.000053 | 0.000000 | 0.000000 |
| Opening tables | 0.000079 | 0.000000 | 0.000000 |
| System lock | 0.000055 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000114 | 0.000000 | 0.000000 |
| init | 0.000060 | 0.000000 | 0.000000 |
| optimizing | 0.000050 | 0.000000 | 0.000000 |
| statistics | 0.000057 | 0.000000 | 0.000000 |
| preparing | 0.000055 | 0.000000 | 0.000000 |
| executing | 0.000083 | 0.000000 | 0.000000 |
| Sending data | 0.122287 | 0.123981 | 0.000000 |
| end | 0.000148 | 0.000000 | 0.000000 |
| query end | 0.000051 | 0.000000 | 0.000000 |
| closing tables | 0.000059 | 0.000000 | 0.000000 |
| freeing items | 0.000054 | 0.000000 | 0.000000 |
| Waiting for query cache lock | 0.000047 | 0.000000 | 0.000000 |
| freeing items | 0.000102 | 0.001000 | 0.000000 |
| Waiting for query cache lock | 0.000048 | 0.000000 | 0.000000 |
| freeing items | 0.000046 | 0.000000 | 0.000000 |
| storing result in query cache | 0.000048 | 0.000000 | 0.000000 |
| logging slow query | 0.000047 | 0.000000 | 0.000000 |
| cleaning up | 0.000048 | 0.000000 | 0.000000 |
+--------------------------------+----------+----------+------------+
24 rows in set (0.00 sec)
能够发现Sending data状态下，时间主要消耗在CPU上了。
而对于MyISAM表的COUNT(*)操作，executing之后直接就结束查询，完全不需要访问数据。
5.通过trace分析优化器如何选择执行计划。
6.确定问题并采取相应的优化措施。
比如创建索引。
二、索引问题
索引的存储分类：
索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。MySQL目前提供了以下4种索引。
B-Tree索引：最常见的索引类型，大部分引擎都支持B树索引。
HASH索引：只有Memory引擎支持，使用场景简单。
R-Tree索引：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text(全文索引)：全文索引也是MyISAM的一个特殊索引类型主要用于全文索引，InnoDB从MySQL5.6版本开始提供对全文索引的支持。
介绍一个前缀索引：（MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符进行索引，这个特性可以大大缩小索引文件的大小，但前缀索引也有缺点，在排序Order By和Group By操作的时候无法使用。）
Create index idx_title on film(title(10));
MySQL如何使用索引
1.匹配全值
Explain select * from rental where rental_date=’2016-07-08 17:22:00’ and inventory_id=300 and customer_id=222 G;
Type类型const表示是常量
2.匹配值得范围
Explain select * from rental where customer_id >= 373 amd customer_id < 400 G;
Type类型是range表示优化器选择范围查询。
3.匹配最左前缀
4.仅仅对索引进行查询
5.匹配列前缀
6.能够实现索引匹配部分精确而其他部分进行范围匹配
7.如果列名是索引，那么使用column_name is null就会使用索引。
8.5.6版本之后引入index condition pushdown （ICP）特性，进一步优化了查询。Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。
存在索引但不能使用索引的典型场景
1.以%开头的LIKE查询不能够利用B-Tree索引。
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]...
检查表的语法如下：
Check table tbl_name
[,tbl_name]...[option]...option={QUICK|FAST|MEDIUM|EXTENDED|CHANGED}
检查表的作用是检查一个或多个表是否有错误，CHECK TABLE对MyISAM和InnoDB表示有作用。
CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
定期优化表
OPTIMIZE [local|no_write_to_binlog] TABLE tbl_name [,tbl_name]...
如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE命令来进行表优化。
这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE命令只对MyISAM,BDB和InnoDB表起作用。
对于InnoDB引擎的表来说，通过设置innodb_file_per_table参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的idb文件，用于存储表的数据和索引，这样可以一定程度上减轻InnoDB表的空间回收问题。另外，在删除大量数据后，InnoDB表可以通过alter table但是不修改引擎的方式来回收不用的空间：
Alter table payment engine=innodb；
注意：ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。
四、常用SQL的优化
大批量插入数据
当用load命令导入数据的时候，适当的设置可以提高导入的速度。
MyISAM存储引擎的表优化略。
InnoDB：
1.因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
2.在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性检验，可以提高导入的效率。
3.如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
优化INSERT语句
当进行数据INSERT的时候，可以考虑采用以下几种优化方式。
1.如果同时从同一客户插入很多行，应尽量使用多个值表的INSERT语句，这种方式将大大缩减客户端与数据库之间的连接，关闭等消耗。使得效率比分开执行的单个INSERT语句快（在大部分情况下，使用多个值表的INSERT语句能比单个INSERT语句快上好几倍）。
例如：
Insert into test values(1,2),(1,3),(1,4)...
2.如果从不同客户插入很多行，可以通过使用INSERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正的写入磁盘，这比每天语句分别插入要快得多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完成后才进行插入。
3.将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。
4.如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对MyISAM表使用。
5.当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
优化ORDER BY语句
优化ORDER BY语句之前，首先来了解一下MySQL中的排序方式。先看customer表上的索引情况。
Show index from customerG;
1.MySQL中有两种排序方式
第一种：Explain select 语句就会看到extra: Using index,不需要额外的排序，操作效率较高。
第二种：Using filesort，所有不是通过索引直接返回排序结果的排序都叫filesort。
总结，下列SQL可以使用索引：
Select * from tabname order by key_part1,key_part2,...;
Select * from tabname where key_part1=1 order by key_part1 desc, key_part2 desc;
Select * from tabname order by key_part1 desc,key_part2 desc;
但是在以下几种情况下则不使用索引：
Select * from tabname order by key_part1 desc, key_part2 asc;
--order by 的字段混合ASC和DESC
Select * from tabname where key2=constant order by key1;
--用于查询行的关键字与order by中所使用的不相同。
Select * from tabname order by key1,key2;
--对不同的关键字使用order by；
2.Filesort的优化
1.两次扫描算法，sort buffer不够就会在temporary table中存储排序结果，结果导致大量随机I/O操作；优点是排序的时候内存开销较少。
2.一次扫描算法，一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。
MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小来判断使用哪种排序算法。如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种算法。
适当加大系统变量max_length_for_sort_data的值，能让MySQL选择更优化的filesort排序算法。当然max_length_for_sort_data值过大，会造成CPU利用率低和磁盘I/O过高，CPU和I/0利用平衡就足够了。
适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行；当然也不能无限制加大sort_buffer_size排序区，因为sort_buffer_size参数是每个线程独占的，设置过大，会导致服务器SWAP严重，要考虑数据库活动连接数和服务器内存的大小来适当设置排序区。
尽量只使用必要的字段，SELECT具体的字段名称，而不是SELECT*选择所有字段，这样可以减少排序区的使用，提高SQL性能。
优化GROUP BY语句
默认情况下，GROUP BY col1，col2,...的字段进行排序。这与在查询中指定ORDER BY col1,col2,...类似。
如果查询包括GROUP BY但用户想避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。
优化嵌套查询
子查询可以使用select语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。这样可以避免事务或者表锁死，但是，有些情况下，子查询可以被更有效率的连接JOIN替代。
子查询：
Explain select * from customer where customer_id not in (select customer_id from payment)G
JOIN查询：
Explain select * from customer a left join payment b on a.customer_id = b.customer_id where b.customer_id is nullG
连接JOIN之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
MySQL如何优化OR条件
对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；
如果没有索引，则应该考虑增加索引。
两个独立索引OR操作，可以正确地用到了索引，并且从执行计划的描述中，发现MySQL在处理含有OR字句的查询时，实际是对OR的各个字段分别查询后的结果进行了UNION操作。
但是当在建有复合索引的列做OR操作时，却不能用到索引。
优化分页查询
Limit1000,20 此时前1000条记录都会被抛弃，查询和排序的代价非常高。
第一种优化思路：
在索引完成排序分页的操作，最后根据主键关联回原表查询所需要的其它列内容。
原查询方式：explain select film_id, description from file order by title limit 50,5 G
安装索引分页后回表方式改下SQL后：
Explain select a.file_id, a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_idG
这种方式让MySQL扫描尽可能少的页面来提高分页效率。
第二种优化思路：
把LIMIT查询换成某个位置的查询。例如，假设每页10条记录，查询支付表payment中按照租赁编号rental_id逆序排序的第42页记录，能够看到执行计划走了全表扫描。
Explain select * from payment order by rental_id desc limit 410,10G
优化：翻页过程中通过增加一个参数last_page_record，用来记录上一页最后一行的租赁编号rental_id，例如第41页最后一行的租赁编号rental_id=15640：
Select payment_id, rental_id from payment order by rental_id desc limit 400,10;
可以根据第41页最后一条记录向后追溯，相应的SQL可以改写为：
Explain select * from payment where rental_id < 15640 order by rental_id desc limit 10G
注意:这样把LIMIT m，n转换成LIMIT n的查询，只适合在排序字段不会出现重复值得特点环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。
使用SQL提示
SQL提示是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
Select SQL_BUFFER_RESULTS * from...
这个语句强制MySQL生成一个临时结果集。
只要临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助，因为可以尽快释放锁资源。
1.USE INDEX
在查询语句中表名后面，添加use index来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。
Explain select count(*) from rental use index (idx_rental_date)G
2.IGNORE INDEX
忽略某个索引
3.FORCE INDEX
强制使用索引
五、常用SQL技巧
1.正则表达式的使用
1.使用正则表达式“$”和“[...]”进行匹配：
Select first_name, email from customer where email regexp “@163[,.]com$”;
而使用普通的like语句，则where条件需要写成如下格式：
Email like “@163%.com” or email like “@163%,com”
2.巧用RAND()提取随机行
Select * from category order by rand() limit 5;
随机抽取样本对总体的统计具有十分重要的意义，因此这个函数非常有用。
3.利用GROUP BY的with rollup子句。
利用GROUP BY的with rollup子句可以检索更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息。
1.在支付表payment中，按照支付时间payment_date的年月、经手员工编号staff_id列分组对支付金额amount列进行聚合计算如下：
Select date_format(payment_date, ‘%Y-%m’), staff_id, sum(amount) from payment group by date_fromat(payment_date, ‘%Y-%m’), staff_id;
优化后：
Select data_fromat(payment_date, ‘%Y-%m’), IFNULL(staff_id,’’), sum(amount) from payment group by date_fromat(payment_date, ‘%Y-%m’), staff_id with rollup;
第2个SQL语句的结果比第一个SQL语句的结果多了每个月每个员工经手的总支付金额。
其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
注意：当使用ROLLUP时，不能同时使用ORDER BY字句进行结果排序。换言之，ROLLUP和ORDER BY是互相排斥的。此外，LIMIT用在ROLLUP后面。
4.用BIT GROUP FUNCTIONS做统计
用一个字段表示顾客购买的商品的信息，但是这个字段是数值型的而不是字符型的，该字段存储一个十进制的数字，当它转换成二进制的时候，那么每一位代表一种商品，1表示购买，0表示没有购买。比如数值的第1位代表面包（规定从右向左开始计算）、第2位代表牛奶、第3位代表饼干、第4位代表啤酒，这样如果一个用户购物单的商品列的数值为5，那么二进制表示为0101，代表购买了面包和饼干。
Mysql> create table order_rab (id int, customer_id int,kind int);
Mysql> insert into order_rab values(1,1,5),(2,1,4);
Mysql>insert into order_rab values(3,2,3),(4,2,4);
Mysql> select * from order_rab;
下面用BIT_OR()函数与GROUP BY子句联合起来：
#mysql> select customer_id, bit_or(kind) from order_rab group by customer_id;
上面语句表示统计一下这两个顾客在这个超市一共都购买过上面商品。
#mysql> select customer_id, bit_and(kind) from order_rab group by customer_id;
上面语句表示每个顾客每次来本超市都会购买的商品。
5.数据库名、表名大小写问题。
Lower_case_tables_name=[0|1|2]
0表示表名和数据库名在硬盘上使用create table和create database语句指定的大小写进行保存，名称对大小写敏感。在unix系统中默认设置就是这个值。
1表示表名在硬盘上以小写保存，名称对大小写敏感。MySQL将所有表名转换为小写以便存储和查找。该值为windows和mac os x系统中的默认值。
2表示表名和数据库名在硬盘上使用create table和create database语句指定的大小写进行保存，但MySQL将它们转换为小写以便查找。此值只在对大小写不敏感的文件系统上适用。
在UNIX中使用lower_case_tables_name=0,而在windows中使用lower_case_tables_name=2，这样就可以保留数据库名和表名的大小写。
注意：在UNIX中将lower_case_tables_name设置为1并重启mysqld之前，必须先将旧的数据库名和表名转换为小写。尽管在某些平台中数据库名和表名对大小写不敏感，但是最好养成在同一查询中使用相同的大小写来引用给定的数据库名或表名的习惯。
6.使用外键需要注意的问题
InnoDB存储引擎支持对外部关键字约束条件的检查。而对于其他类型存储引擎的表，当使用REFERENCES tbl_name(col_name) 子句定义列时可以使用外部关键字，但是该子句没有实际的效果，只作为备忘录或注释来提醒用户目前正定义的列指向另一个表中的一个列。
InnoDB：
Create table users2(id int, name varchar(10), primary key(id)) engine=innodb;
Create table books2(id int,bookname varchar(10),userid int,primary key(id),constraint fk_userid_id foreign key(userid) references user2(id)) engine=innodb;
Insert into books2 values(1,’book’,1);会提示失败
可以使用show create table books2G;查看外键。
第二篇：优化数据库对象
一、优化表的数据类型
表使用何种数据类型是需要根据应用来判断的。
应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费磁盘存储空间，同时在应用程序操作时也浪费物理内存。
在MySQL中，使用函数PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。
以下是函数PROCEDURE ANALYSE()的使用方法：
select * from tbl_name procedure analyse();
select * from tbl_name procedure analyse(16,256);
输出的每一列信息都会对数据表中的列的数据类型提出优化建议。
以上第二行语句告诉procedure analyse()不要为那些包含的值多于16个或者256个字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。
根据PROCEDURE ANALYSE()函数的输出信息，用户可能会发现，一些表中的字段可以修改为效率更高的数据类型。
如果决定改变某个字段的类型，则需要使用alter table语句。
二、通过拆分提高表的访问效率
MyISAM类型的表：
1.垂直拆分，即把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。
适用一个表中某些列常用，而另一些列不常用，则可以采用垂直拆分。
优点：使得数据行变小，一个数据页就能存放更多的数据，查询的时候就会减少I/O次数。
缺点：需要管理冗余列，查询所有数据需要联合（JOIN）操作。
2.水平拆分，即根据一列或多列数据的值把数据行放到两个独立的表中。
使用场景：
表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
表中的数据本来就有独立性，例如，表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
需要把数据存放到多个介质上。
例如：最近3个月的账单数据存在一个表中，3个月前的历史账单存放到另外一个表中，超过1年的历史账单可以存储到单独的存储介质上，这种拆分是最常使用的水平拆分方法。
三、逆规范化
增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。
增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数。
重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
分割表：参考“通过拆分提高表的访问效率”
四、使用中间表提高统计查询速度
对于数据量较大的表，在其上进行统计查询通常会效率很低，并且还要考虑统计查询是否会对在线的应用产生负面影响。
中间表的创建：
创建和源表结构完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。来提高查询速度。
中间表在统计查询中经常会用到，其优点如下：
中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响；
中间表上可以灵活地添加索引或增加临时用的新字段，从而达到提高统计查询效率和辅助统计查询作用。
第三篇：锁问题
锁是计算机协调多个进程或线程并发访问某一资源的机制。
一、MySQL锁概述
MyISAM和MEMORY存储引擎采用的是表级锁；
BDB存储引擎采用的是页面锁；但也支持表级锁；
InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下。
表级锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁： 开销和加锁时间届于表锁和行锁之间；会出现死锁；锁定粒度届于表锁和行锁之间，并发度一般。
表级锁适合于以查询为主，只有少量按索引条件更新数据的应用，如web应用；而行级别锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
二、InnoDB锁问题
InnoDB支持事务，支持行级锁。
事务及其ACID属性：
原子性，事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性，在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构，（如B树索引或双向链表）也都必须是正确的。
隔离性，事务在不受外部并发操作影响的独立环境执行。
持久性，事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
银行转账就是事务的一个典型例子。
三、获取InnoDB行锁争用情况
show status like ‘innodb_row_lock%’;
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_avg的值比较高，可以通过查询Information_schema数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
1、通过查询Information_schema数据库中的表了解锁等待情况：
select * from innodb_locks G;
select * from innodb_lock_waits G;
2、通过设置InnoDB Monitors观察锁冲突 情况：
create table innodb_monitor(a INT) engine=INNODB;
然后就可以利用下面的语句来进行查看：
show engine innodb statusG;
监视器可以通过发出下列语句来停止：
drop table innodb_monitor;
第四篇：优化MySQL server
一、MySQL体系结构概览
MySQL实例由一组后台线程、一些内存块和若干服务线程组成。
默认情况下MySQL有7组后台线程，分别是1个主线程，4组IO线程，1个锁线程，1个错误监控线程。MySQL5.5之后又新增了一个purge线程。
这些线程的主要功能如下：
master thread:主要负责将脏缓存页刷新到数据文件，执行purge操作，触发检查点，合并插入缓冲区等。
insert buffer thread:主要负责插入缓冲区的合并操作。
read thread:负责数据库读取操作，可配置多个读线程。
write thread:负责数据库写操作，可配置多个写线程。
log thread:用于将重做日志刷新到logfile中。
purge thread：MySQL5.5之后用单独的purge thread执行purge操作。
lock thread：负责锁控制和死锁检测等。
错误监控线程：主要负责错误监控和错误处理。
我们可以通过show engine innodb status命令来查看这些线程的状态：
show engine innodb statusG;
二、MySQL内存管理及优化
内存是影响数据库性能的重要资源，也是MySQL性能优化的一个重要方面。
内存优化原则：
1.将尽量多的内存分配给MySQL做缓存，但是给操作系统和其他程序的运行预留足够的内存，否则如果产生SWAP页交换，将严重影响系统性能。
2.MyISAM的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。
3.排序区、连接区等缓存是分配给每个数据库会话session专用的，其默认值的设置要根据最大连接数合理分配，如果设置太大，不但浪费内存资源，而且在并发连接较高时会导致物理内存耗尽。
InnoDB内存优化
1.InnoDB缓存机制
InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存InnoDB的索引块，而且也用来缓存InnoDB的数据块，这一点与MyISAM不同。
在内部，InnoDB缓存池逻辑上由freelist ,flush list 和LRU list组成。顾名思义，free list是空闲缓存块列表，flush list是需要刷新到磁盘的缓存块列表，而LRU list是InnoDB正在使用的缓存块，它是InnoDB buffer pool的核心。
脏页的刷新存在于flushlist 和LRU list这两个链表，LRU上也存在可以刷新的脏页，这里是直接可以刷新的，默认BP（innodb_buffer_pool）中不存在可用的数据页的时 候，会扫描LRU list 尾部的INNODB_LRU_SCAN_DEPTH个数据页(默认是1024个数据页),进行相关刷新操作。从LRU list 淘汰的数据页会立即放入到FREE LIST中去。
InnoDB_buffer_pool_size
innodb_buffer_pool_size决定InnoDB存储引擎表数据和索引数据的最大缓存大小。在保证操作系统及其他程序有足够内存可 用的情况下，innodb_buffer_pool_size越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O越少，性能也就越高，一般在一个 专用的数据库服务器上，可以将80%的物理内存分配给InnoDB buffer pool但是一定要注意避免设置过大而导致页交换。
2.innodb_buffer_pool_size的设置
innodb_buffer_pool_size决定InnoDB存储引擎表数据和索引数据的最大缓冲区大小。
在保证操作系统及其它程序有足够内存可用的情况下，innodb_buffer_pool_size的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O就越少，性能也就越高。
在一个专用的数据库服务器上，可以将80%的物理内存分配给InnoDB buffer pool，但一定要注意避免设置过大而导致页交换。
通过以下命令查看buffer pool的使用情况：
mysqladmin -uroot -p -S /tmp/mysql.sock ext|grep -i innodb_buffer_pool
可以用下公式计算InnoDB缓冲池的命中率：
（1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request）X 100
如果命中率太低，则应考虑扩充内存，增加innodb_buffer_pool_size的值。
3.调整old subilst大小
在LRU list中，old sublist的比例由系统参数innodb_old_blocks_pct决定，其取值范围是5~95，默认是37(3/8)
mysql>show global variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37 |
+-----------------------+-------+
1 row in set (0.00 sec)
可以根据innoDB Monitor的输出信息来调整Innodb_old_blocks_pct的值。例如，在没有较大表扫描或者索引的情况下，如果young/s的值很 低，可以适当增加innodb_old_block_pct的值或者减少innodb_old_blocks_time的值。
4.调整innodb_old_blocks_time设置
innodb_old_blocks_time参数决定了缓存数据块由oldsublist转到young sublist的快慢，当一个缓存数据块被插入到midpoint后，至少要在old sublist 停留超过innodb_old_blocks_time(ms)后，才有可能被转移到new sublist。
如果non-young/s很低，young/s很高，就应该考虑将innodb_old_blocks_time适当调大，以防止表扫描将真正的热数据淘汰。这个值可以动态设置，如果要进行大的表扫描操作，可以很方便地临时做调整。
5.调整缓存池数量，减少对内部缓存池数据结构的征用
MySQL内部不同线程对InnoDB缓存池的访问在某些阶段是互斥的，这种内部竞争也会产生性能问题，尤其在高并发和buffer pool较大的情况下。为了解决这个问题，innoDB的缓存系统引入了innodb_buffer_pool_instances配置参数，对于较大的 缓存池，适当增加此参数的值，可以降低并发导致的内部缓存访问冲突。innodb_buffer_pool_size指定大小的缓存平分为 innodb_buffer_pool_instances个buffer pool
6.控制Innodb buffer刷新，延长数据缓存时间，减少磁盘I/O
在InnoDB找不到干净的可用缓存页或者检查点被触发等情况下，InnoDB的后台线程就会开始把“脏的缓存页”回写到磁盘文件中，这个过程叫做缓存刷新。
InnoDB buffer pool的刷新快慢主要取决于两个参数：
l innoDB_max_dirty_pages_pct，它控制缓存池中脏页的最大比例，默认值是75%，如果脏页的数量达到或者超过该值，InnoDB的后台线程开始缓存刷新。
l 另个是innodb_io_capactiy，它代表磁盘系统的IO能力，其值在一定程度上代表磁盘每秒可完成I/O的次数。默认是200如果是磁盘转速较低可以设置小一点，如果磁盘IO能力较强可以设置大一些。
innodb_io_capacity决定一批脏数页的数量，当缓存池脏页的比例达到innodb_max_dirty_pages_pct 时，InnoDB大约将Innodb_io_capacity个改变的缓存页面刷新到磁盘，当脏页比例小于 innodb_max_dirty_pages_pct时，如果innodb_adaptive_flushing的设置为true，innoDB将根据 函数buf_flush_get_desired_flush_rate返回的重做日志产生的速度来确定要刷新的脏页数。在合并插入缓存时，InnoDB 每次合并的页数是0.05*innodb_io_capactiy.
可以根据InnoDB Monitor的值来调整innodb_max_dirty_pages_pct和innodb_io_capacity。弱 innodb_buffer_pool_wait_free的值增长较快，则说明InnoDB经常在等待空闲缓存页，如果无法增大缓存池，那么应将 Innodb_max_dirty_pages_pct的值调小或将InnoDB_io_capactiy的值提高，以加快脏页的刷新。
7.InnoDB doublewrite（双写）
由于MySQL的数据页大小(一般16KB）与操作系统的IO数据页大小（4KB）不一致，无法保证InnoDB缓存页被完整、一致地刷新到磁盘，而 InnoDB的redo日志只记录了数据页改变的部分，并未记录数据页的完整前像，当发生部分写或断裂写（比如将缓存页的第一个4KB写入后服务器断电） 会出现页面无法恢复的问题，为了解决这个问题，InnoDB引入了doublewrite技术
默认情况下：InnoDB doublewrite是开启的
mysql>show global variables like '%doublewrite%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| innodb_doublewrite | ON |
+--------------------+-------+
1 row in set (0.00 sec)
四、调整用户服务线程排序缓存区
如果sort_merge_passes的值很大
mysql>show global status like '%sort_merge_passes%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Sort_merge_passes | 0 |
+-------------------+-------+
1 row in set (0.00 sec)
可以考虑调整参数sort_buffer_size的值来增大排序缓存区，以改善order by子句或group子句的SQL的性能。
对于无法通过索引进行连接的操作查询，可以尝试通过增大join_buffer_size的值来改善性能。不过需要注意的是sort buffer和join buffer都是面向session的，所以不可设置过大，尤其是join buffer，如果是多表关联的复杂查询，还可能会分配多个join buffer，因此最好的策略是设置较小的全局join_buffer_size，而需要复杂连接的session单独设置较大的 join_buffer_size。
五、InnoDB log机制及优化
支持事务的数据库系统都需要有一套机制来保证事务更新的一致性和持久性InnoDB与oracle等支持事务的关系数据库一样，也采用redo log机制来保证事务更新的一致性和持久性。
InnoDB重做日志
redo log是InnoDB保证事务ACID属性的重要机制。
当更新数据时，InnoDB内部的操作流程大致是：
1.将数据读入InnoDB buffer pool,并对相关记录加独占锁；
2.将UNDO信息写入undo表空间的回滚段中；
3.更改缓存页中的数据，并将更新记录写入redo buffer中；
4.提交时，根据innodb_flush_log_at_trx_commit的设置（0,1,2），用不同的方式将redo buffer中的更新记录刷新到InnoDB redo log file中，然后释放独占锁；
5.最后，后台IO线程根据需要择机缓存中更新过的数据刷新到磁盘文件中。
可以通过show engine innodb status命令查看当前日志的写入情况：
show engine innodb statusG;
LOG
---
Log sequence number 210685416315
Log flushed up to 210685416315
Last checkpoint at 210685367037
优化调整：
innodb_flush_log_at_trx_commit的设置
innodb_flush_log_at_trx_commit参数可以控制将redo buffer中的更新记录写入到日志文件以及将日志文件数据刷新到磁盘的操作时机。
通过这个参数可以在性能和数据安全之间做取舍。
如果这个参数设置为0，在事务提交时，InnoDB不会立即触发将缓存日志写到磁盘文件的操作，而是每秒触发一次缓存日志回写磁盘操作，并调用操作系统fsync刷新IO缓存。
如果这个参数设置为1，在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，并调用操作系统fsync刷新IO缓存。
如果这个参数设置为2，在每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件，但并不马上调用fsync来刷新IO缓存，而是每秒只做一次磁盘IO缓存刷新操作。
innodb_flush_log_at_trx_commit参数默认值是1，即每个事务提交时都会从log buffer写更新记录到日志文件，而且会实际刷新磁盘缓存，显然，这完全满足事务的持久化要求，是最安全的，但这样会有较大的性能损失。
在某些情况下，我们需要尽量提高性能，并且可以容忍在数据库崩溃时丢失小部分数据，那么通过将参数innodb_flush_log_at_trx_commit设置成0或2都能明显减少日志同步IO,加快事务提交，从而改善性能。
将此参数设置成0，如果数据库崩溃，最后1秒钟的事务重做日志可能会由于未及写入磁盘文件而丢失，这种方式是效率最高的，但也是最不安全的。
将此参数设置成2，如果数据库崩溃，由于已执行重做日志写入磁盘操作，只是没有做磁盘IO刷新操作，因此，只要不发生操作系统崩溃，数据就不会丢失，这种方式是对性能和数据安全的折中，其性能和数据安全性介于其他两种方式之间。
设置log file size,控制检查点
当应之日文件写满后，InnoDB会自动切换到另一个日志文件，但切换时会触发数据库检查点（Checkpoint），这将导致InnoDB缓存脏页的小批量刷新，会明显降低InnoDB的性能。
是不是将innodb_log_file_size设的越大越好呢？理论上是，但如果日志文件设置过大，恢复时将需要更长的时间，同时也不便于管理。一般来说，平均每半小时写满一个日志文件比较合适。我们可以通过下面的方法来计算innoDB每小时产生的日志量并估算合适的innodb_log_file_size的值。
首先，计算InnoDB每分钟产生的日志量：
mysql> pager grep sequence;
mysql> show engine innodb statusG select sleep(60); show engine innodb statusG
Log sequence number 210708268306
1 row in set (0.01 sec)
1 row in set (59.99 sec)
Log sequence number 210709020624
1 row in set (0.00 sec)
mysql> select ROUND((210709020624 - 210708268306)/1024/1024) as MB;
+------+
| MB |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
通过上述操作得到InnoDB每分钟产生的日志量是1MB。然后，计算半小时的日志量：
半小时日志量=30 x 1MB=30MB
这样，就可以得出innodb_log_file_size的大小至少应该是30MB。
调整innodb_log_buffer_size
innodb_log_buffer_size决定InnoDB重做日志缓冲池的大小，默认值是8MB。对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免InnoDB在事务提交前就执行不必要的日志写入磁盘操作。因此，对于会在一个事务中更新、插入或删除大量记录的应用，我们可以通过增大innodb_log_buffer_size来减少日志写磁盘操作，从而提高事务处理的性能。
六、调整MySQL并发相关的参数
从实现上来说，MySQL SERVER是多线程结构，包括后台线程和客户服务线程。
多线程可以有效利用服务器资源，提高数据库的并发性能。在MySQL中，控制并发连接和线程的主要参数包括max_connections、back_log、thread_cache_size以及table_open_cache等。
调整max_connections，提高并发连接
参数max_connections控制允许连接到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors_max_connections不为零，并且一直在增长，就说明不断有连接请求因数据库连接数已达到最大允许的值而失败。
MySQL最大可支持的数据库连接取决于很多因素，包括给定操作系统平台线程库的质量、内存大小、每个连接的负荷以及期望的响应时间等。在linux平台下，MySQL支持500~1000个连接不是难事，如果内存足够、不考虑响应时间，甚至能达到上万个连接。而在windows平台下，受其所用线程库的影响，最大连接数有以下限制：
（open tables x 2 + open connections） < 2048
每一个session操作MySQL数据库表都需要占用文件描述符，数据库连接本身也要占用文件描述符，因此，在增大max_connections时，也要注意评估open-files-limit的设置是否够用。
调整back_log
back_log参数控制MySQL监听TCP端口时设置的积压请求栈大小，5.6.6版本以前的默认值是50,5.6.6版本以后的默认值是50+（max_connections/5）,但最大不能超过900。
如果需要数据库在较短时间内处理大量连接请求，可以考虑适当增大back_log的值。
调整table_open_cache
每一个SQL执行线程至少都要打开1个表缓存，参数table_open_cache控制所有SQL执行线程可打开表缓存的数量。这个参数值应根据最大连接数max_conntctions以及每个连接执行关联查询中所涉及表的最大个数N来设定：
max_connections x N
在未执行flush tables命令的情况下，如果MySQL状态变量opened_tables的值较大，就说明table_open_cache设置得太小，应适当增大。增大table_open_cache的值，会增加MySQL对文件描述符的使用量，因此，也要注意评估open-files-limit的设置是否够用。
调整thread_cache_size
为加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制MySQL缓存客户服务线程的数量。
可以通过计算线程cache的失效率threads_created/connections来衡量thread_cache_size的设置是否合适。该值越接近1，说明线程cache命中率越低，应考虑适当增加thread_cache_size的值。
innodb_lock_wait_timeout的设置
参数innodb_lock_wait_timeout可以控制InnoDB事务等待行锁的时间，默认值是50ms，可以根据需要动态设置。对于需要快速反馈交互式OLTP应用，可以将行锁等待超时时间调小，以避免事务长时间挂起；对于后台运行的批处理操作，可以将行锁等待超时时间调大，以避免发生大的回滚操作。