Mysql优化
2021-10-30 22:39:53 2 举报AI智能生成
登录查看完整内容
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：慢查询的次数。
通过 show status命令了解各种SQL的执行频率
通过慢查询日志定位那些执行效率较低的SQL语句，用--log-slow-queries[= file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。具体可以查看本书第26章中日志管理的相关部分。
慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
定位执行效率较低的sql语句
select_type：表示SELECT的类型，常见的取值有SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
table：输出结果集的表。
All：全表扫描，Mysql遍历全表来找到匹配的行
index：索引全扫描，Mysql遍历整个索引来查询匹配的行
range：索引范围扫描，常见于<、<=、>、>=、between等操作符
ref：使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值得记录行
eq_ref：类似ref，区别就在于使用的索引是唯一索引，对每个索引键值、表中只有一条记录匹配；简单来说就是多表连接中使用primary key或者unique index作为关联条件
const/system：单表中最多有一个匹配行，查询起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key或者唯一索引 unique index进行的查询。
NULL：mysql不用访问表或者索引，直接能够获得结果
type：表示MySQL在表中找到所需行的方式，或者叫访问类型，常见类型如上
possible_keys：表示查询时可能使用的索引。
key：表示实际使用的索引。
key_len：使用到索引字段的长度。
rows：扫描行的数量。
Extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
通过EXPLAIN分析低效SQL的执行计划
通过profile，我们能够更清楚地了解SQL执行的过程。
通过 show profile for query 语句能够看到执行过程中线程的每个状态和消耗的时间
show profile能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
通过show profile 分析SQL
MySQL 5.6提供了对SQL的跟踪 trace，通过 trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地理解优化器的行为。
2.接下来执行想做trace的SQL语句
3.检查INFORMATION_SCHEMA.OPTIMIZER_TRACE就可以知道MySQL是如何执行SQL的：mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\\G；最后会输出一个跟踪文件
使用方式：
通过 trace分析优化器如何选择执行计划
通过性能工具识别出性能差在哪
然后再对定位的问题逐一优化
确定问题并采取相应的优化措施
优化数据库步骤
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）匹配全值（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）的特性，进一步优化了查询。
1．MySQL中能够使用索引的典型场景
（1）以%开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引
（2）数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则即便这个列上有索引，MySQL 也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索。
（3）复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的
（4）如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
（5）用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
2．存在索引但不能使用索引的典型场景
Mysql如何使用索引
如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。
查看索引使用情况
索引优化
定期分析表和检查表
定期优化表
实用优化方法
注意：ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。
对于MyISAM存储引擎的表，可以通过以下方式快速地导入大量的数据。ALTER TABLE tbl_name DISABLE KEYS;loading the dataALTER TABLE tbl_name ENABLE KEYS;DISABLE KEYS和ENABLE KEYS用来打开或者关闭MyISAM表非唯一索引的更新。在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。
MyISAM
（1）因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
（2）在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
（3）如果应用使用自动提交的方式，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
InnoDB
大批量导入数据
如果从不同客户插入很多行，可以通过使用 INSERT DELAYED语句得到更高的速度。DELAYED的含义是让INSERT语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快得多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完成后才进行插入。
将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。
如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度，但是，这只能对MyISAM表使用。
当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。
优化Insert语句
第一种通过有序索引顺序扫描直接返回有序数据，这种方式在使用 explain 分析查询的时候显示为Using Index，不需要额外的排序，操作效率较高，
第二种是通过对返回数据进行排序，也就是通常说的 Filesort 排序，所有不是通过索引直接返回排序结果的排序都叫Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否使用了磁盘文件或临时表等，则取决于MySQL服务器对排序参数的设置和需要排序数据的大小。
mysql排序方式
尽量减少额外的排序，通过索引直接返回有序数据。WHERE条件和ORDER BY使用相同的索引，并且ORDER BY的顺序和索引顺序相同，并且ORDER BY的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现Filesort。
优化思路
两次扫描算法（Two Passes）：首先根据条件取出排序字段和行指针信息，之后在排序区 sort buffer中排序。如果排序区 sort buffer不够，则在临时表Temporary Table中存储排序结果。完成排序后根据行指针回表读取记录。该算法是MySQL 4.1之前采用的算法，需要两次访问数据，第一次获取排序字段和行指针信息，第二次根据行指针获取记录，尤其是第二次读取操作可能导致大量随机I/O操作；优点是排序的时候内存开销较少。
一次扫描算法（Single Pass）：一次性取出满足条件的行的所有字段，然后在排序区sort buffer中排序后直接输出结果集。排序的时候内存开销比较大，但是排序效率比两次扫描算法要高。
Filesort优化
优化order by语句
如果查询包括GROUP BY但用户想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序
优化group by语句
使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。
连接（JOIN）之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
优化嵌套查询
对于含有OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。
mysql如何优化or条件
1.在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。这种方式让MySQL扫描尽可能少的页面来提高分页效率。
优化分页查询
SQL提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。下面是一个使用SQL提示的例子：SELECT SQL_BUFFER_RESULTS * FROM...
在查询语句中表名的后面，添加USE INDEX来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。mysql> explain select count(*) from rental use index (idx_rental_date)\\G
1.USE INDEX
如果用户只是单纯地想让MySQL忽略一个或者多个索引，则可以使用 IGNORE INDEX作为HINT。同样是上面的例子，这次来看一下查询过程忽略索引ind_sales2_id的情况：mysql> explain select count(*) from rental ignore index (idx_rental_date)\\G
2.IGNORE INDEX
为强制MySQL使用一个特定的索引，可在查询中使用FORCE INDEX作为HINT。例如，当不强制使用索引的时候，因为大部分库存inventory_id的值都是大于1的，因此MySQL会默认进行全表扫描，而不使用索引，如下所示：mysql> explain select * from rental where inventory_id > 1\\G
3.FORCE INDEX
常见例子：
使用SQL提示
常用SQL语句优化
MySQL利用REGEXP命令提供给用户扩展的正则表达式功能，REGEXP实现的功能类似UNIX上GREP和SED的功能，并且REGEXP在进行模式匹配时是区分大小写的。
正则表达式使用
MySQL中，产生随机数的方法是RAND()函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND()能够把数据随机排序。mysql> select * from category order by rand() limit 5;
巧用rand()提取随机行
在SQL语句中，使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息
其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER BY是互相排斥的。此外，LIMIT用在ROLLUP后面。
利用group by的with rollup子句
如何共同使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作。这两个函数的一般用途就是做数值之间的逻辑位运算，但是，当把它们与 GROUP BY子句联合使用的时候就可以做一些其他的任务。
用bit group functions做统计
所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。
列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX中对大小写敏感，但在Windows或Mac OS X中对大小写不敏感。
数据库名、表大小写问题
外键设置只对InnoDB引擎起作用
使用外键需要注意的问题
常用SQL技巧
SQL优化
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
Mysql锁概况
可以通过检查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表显式加锁
如何加表锁
当concurrent_insert设置为0时，不允许并发插入。
当concurrent_insert设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
当concurrent_insert设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。
MyISAM 存储引擎有一个系统变量 concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2。
并发插入
通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
MyISAM锁调度
MyISAM表锁
原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
1.事务及ACID属性
脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除了！这种现象就叫做“不可重复读”。
幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
2．并发事务处理带来的问题
一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
实现事务隔离的方式
3．事务隔离级别
背景知识
可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：mysql> show status like 'innodb_row_lock%';
（1）通过查询information_schema数据库中的表了解锁等待情况：mysql> select * from innodb_locks \\G;
（2）通过设置 InnoDB Monitors观察锁冲突情况：mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
监视器可以通过发出下列语句来停止：mysql> DROP TABLE innodb_monitor;
如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，可以通过查询 information_schema 数据库中相关的表来查看锁情况，或者通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。
获取InnoDB行锁争用情况
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
锁模式
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
排他锁（X）：SELECT * FROM table_name WHERE ... FOR UPDATE。
加锁方法
用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。
InnoDB行锁模式及加锁方法
Record lock：对索引项加锁。
Gap lock：对索引项之间的“间隙”、第一条记录前的“间隙”或最后一条记录后的“间隙”加锁。
Next-key lock：前两种的组合，对记录及其前面的间隙加锁。
InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁。InnoDB行锁分为3种情形。
InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样！
InnoDB行锁实现方式
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的Next-Key锁。
Next-key锁
基于SQL语句的复制SBR：这也是MySQL最早支持的复制模式。
基于行数据的复制RBR：这是MySQL 5.1以后开始支持的复制模式，主要优点是支持对非安全SQL的复制。
混合复制模式：对安全的SQL语句采用基于SQL语句的复制模式，对于非安全的SQL语句采用居于行的复制模式。
使用全局事务ID（GTIDs）的复制：主要是解决主从自动同步一致问题。
四种复制模式
恢复和复制对InnoDB锁机制的影响
InnoDB在不同隔离级别下的一致性读及锁的差异
第一种情况是：事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。
第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁，减少数据库因事务回滚带来的开销。
什么时候使用表锁
（1）在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。在下面的例子中，由于两个 session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。
（2）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。
（3）在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。具体演示可参见20.3.3小节中的例子。
（4）前面讲过，在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题
（5）当隔离级别为 READ COMMITTED 时，如果两个线程都先执行 SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁
几种避免死锁的措施
关于死锁
InnoDB锁
事务、锁优化
在MySQL中，可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。
输出的每一列信息都会对数据表中的列的数据类型提出优化建议。以上第二个语句告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256个字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。
MyISAM存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或VARCHAR列都没有关系。两者都是作为CHAR类型处理。
InnoDB存储引擎：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。
CHAR和VARCHAR
独立垂直切表，与主表分离，减少主表容量
TEXT、BLOB长文本
尽量不要用浮点数，由于浮点数计算会丢失精读
非金融类财务或支付相关的金额数据最好使用定点数或以分为单位的整数
浮点数与定点数
根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。
如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。
如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。
日期类型
一些数据类型选择建议
优化表的数据类型
（1）第一种方法是垂直拆分，即把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果一个表中某些列常用，而另一些列不常用，则可以采用垂直拆分，另外，垂直拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作。
表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
表中的数据本来就有独立性，例如，表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
需要把数据存放到多个介质上。
水平拆分通常在以下几种情况下使用。
（2）第二种方法是水平拆分，即根据一列或多列数据的值把数据行放到两个独立的表中。
通过拆分提高表的访问效率
1NF：每列不可分割，原子性
2NF：非主属性依赖于主属性，唯一性
3NF：消除非主属性传递依赖，即任意两个表，不能出现重复的非主键字段，去冗余
三大范式
规范化越高，那么产生的关系就越多，关系过多的直接结果就是导致表之间的连接操作越频繁，而表之间的连接操作是性能较低的操作，直接影响到查询的速度，所以，对于查询较多的应用，就需要根据实际情况运用逆规范化对数据进行设计，通过逆规范化来提高查询的性能。
反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此，决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其他方法经常能够解决性能问题，而不必采用反规范这种方法。
增加冗余列：指在多个表中具有相同的列，它常用来在查询时避免连接操作。
增加派生列：指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数。
重新组表：指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
分割表
常用逆规范化手段
批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。
数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。
另一种方式就是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题比较好的办法。
逆规范技术需要维护数据的完整性。
逆规范化
中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响；
中间表上可以灵活地添加索引或增加临时用的新字段，从而达到提高统计查询效率和辅助统计查询作用。
使用中间表提高统计查询速度
表结构优化
由于建立数据库连接都比较昂贵，可以预先建立适当多个连接，放在一个i“池子里”，需要时获取连接使用，用完放回池子
使用连接池
可以同时获取多个数据在应用中使用
避免对同一数据做重复检索
have_query_cache表明服务器在安装时是否已经配置了高速缓存。
query_cache_size表明缓存区大小，单位为MB。
query_cache_type的变量值从0到2，含义分别为：0或者off（缓存关闭）、1或者on（缓存打开，使用SQL_NO_CACHE提示的SELECT除外）、2或者demand（只有带SQL_CACHE的SELECT语句提供高速缓存）。
查询缓存相关的参数主要有以下几个：mysql> show variables like '%query_cache%';
通过show status监视查询缓存的使用情况
使用查询缓存
增加CACHE缓存层
减少对Mysql的访问
主从复制
利用Mysql复制分流查询操作
采用分布式数据库架构
负载均衡
对于没有删除行操作的 MyISAM 表，插入操作和查询操作可以并行进行，因为没有删除操作的表查询期间不会阻塞插入操作。对于确实需要执行删除操作的表，尽量在空闲时间进行批量删除操作，并且在进行删除操作之后应该进行OPTIMIZE操作来消除由于删除操作带来的空洞，以避免将来的更新操作阻塞其他操作。
充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这会减少MySQL需要做的语法分析从而提高插入速度。
表的字段尽量不使用自增长变量，在高并发情况下该字段的自增可能对效率有比较大的影响，推荐通过应用来实现字段的自增长。
其他优化措施
应用层优化
Mysql结构体系
优化原则
MyISAM内存优化
InnoDB内存优化
调整用户服务线程排序缓存区
内存管理及优化
InnoDB log 机制及优化
调整Mysql并发相关参数
Mysql服务器优化
使用磁盘阵列
虚拟文件卷或软RAID
使用Symbolic Links分布IO
禁止操作系统更新文件的atime属性
用裸设备（Raw Device）存放InnoDB的共享表空间
调整IO调度算法
RAID卡电池充放电问题
NUMA架构优化
磁盘IO优化
Mysql优化
0 条评论
回复 删除
下一页
