时间：2021-07-01 10:21:17 帮助过：2人阅读
声明：本学习笔记是拜读“深入浅出MySQL数据库开发、优化与维护管理 第2版 唐汉明等著”
推荐大家购买阅读。
任何转转或引用等请注明书籍。
默认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: 慢查询的次数
两种方法：
通过慢查询日志--log-slow-queries[=file_name]选项启动时，Mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
慢查询日志在查询结束以后才记录，所以在应用反映执行效率慢的时候查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
通过以上步骤查询到效率低的SQL语句后，通过EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句在执行过程中表如何连接和连接的顺序。
常见访问类型：从左到右，性能由最差到最好。
ALL index range ref eq_ref const,system NULL
检查当前数据库是否支持：
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之后直接就结束查询，完全不需要访问数据。
比如创建索引。
索引是在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));
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执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。
当用load命令导入数据的时候，适当的设置可以提高导入的速度。
MyISAM存储引擎的表优化略。
InnoDB：
1.因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
2.在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性检验，可以提高导入的效率。
3.如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
当进行数据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语句之前，首先来了解一下MySQL中的排序方式。先看customer表上的索引情况。
Show index from customer\G;
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 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 null\G
连接JOIN之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
对于含有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_id\G
这种方式让MySQL扫描尽可能少的页面来提高分页效率。
第二种优化思路：
把LIMIT查询换成某个位置的查询。例如，假设每页10条记录，查询支付表payment中按照租赁编号rental_id逆序排序的第42页记录，能够看到执行计划走了全表扫描。
Explain select * from payment order by rental_id desc limit 410,10\G
优化：翻页过程中通过增加一个参数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 10\G
注意:这样把LIMIT m，n转换成LIMIT n的查询，只适合在排序字段不会出现重复值得特点环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。
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
强制使用索引
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;
上面语句表示每个顾客每次来本超市都会购买的商品。
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之前，必须先将旧的数据库名和表名转换为小写。尽管在某些平台中数据库名和表名对大小写不敏感，但是最好养成在同一查询中使用相同的大小写来引用给定的数据库名或表名的习惯。
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 books2\G;查看外键。
表使用何种数据类型是需要根据应用来判断的。
应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费磁盘存储空间，同时在应用程序操作时也浪费物理内存。
在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年的历史账单可以存储到单独的存储介质上，这种拆分是最常使用的水平拆分方法。
增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。
增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数。
重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
对于数据量较大的表，在其上进行统计查询通常会效率很低，并且还要考虑统计查询是否会对在线的应用产生负面影响。
中间表的创建：
创建和源表结构完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。来提高查询速度。
中间表在统计查询中经常会用到，其优点如下：
中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响；
中间表上可以灵活地添加索引或增加临时用的新字段，从而达到提高统计查询效率和辅助统计查询作用。
锁是计算机协调多个进程或线程并发访问某一资源的机制。
MyISAM和MEMORY存储引擎采用的是表级锁；
BDB存储引擎采用的是页面锁；但也支持表级锁；
InnoDB存储引擎既支持行级锁，也支持表级锁，但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下。
表级锁： 开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁： 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁： 开销和加锁时间届于表锁和行锁之间；会出现死锁；锁定粒度届于表锁和行锁之间，并发度一般。
表级锁适合于以查询为主，只有少量按索引条件更新数据的应用，如web应用；而行级别锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
InnoDB支持事务，支持行级锁。
事务及其ACID属性：
原子性，事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性，在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构，（如B树索引或双向链表）也都必须是正确的。
隔离性，事务在不受外部并发操作影响的独立环境执行。
持久性，事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
银行转账就是事务的一个典型例子。
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 status\G;
监视器可以通过发出下列语句来停止：
drop table innodb_monitor;
MySQL实例由一组后台线程、一些内存块和若干服务线程组成。
默认情况下MySQL有7组后台线程，分别是1个主线程，4组IO线程，1个锁线程，1个错误监控线程。MySQL5.5之后又新增了一个purge线程。
这些线程的主要功能如下：
master thread:主要负责将脏缓存页刷新到数据文件，执行purge操作，触发检查点，合并插入缓冲区等。
insert buffer thread:主要负责插入缓冲区的合并操作。
read thread:负责数据库读取操作，可配置多个读线程。
write thread:负责数据库写操作，可配置多个写线程。
log thread:用于将重做日志刷新到logfile