MYSQL优化

最新推荐文章于 2024-09-17 09:10:57 发布

qq_38375620

最新推荐文章于 2024-09-17 09:10:57 发布

阅读量138

点赞数

分类专栏： MYSQL 文章标签： mysql 优化

本文链接：https://blog.csdn.net/qq_38375620/article/details/81272163

版权

MYSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL设计局限与折衷

当使用MyISAM存储引擎时，MySQL使用极快速的表锁定，以便允许多次读或一次写。

所有列有默认值。请注意当运行在严格SQL模式(包括TRADITIONAL SQL模式)时，必须为NOT NULL列指定默认值。

· 如果向列内插入不合适的或超出范围的值，MySQL将该列设定为“最好的可能的值”，而不是报告错误。对于数字值，为0、可能的最小值或最大值。对于字符串，为空字符串或列内可以保存的字符串。

估计查询性能

在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B－树索引进行估计，将需要log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1次搜索才能找到行。

在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，这对于有一个长度为3(中等整数)的索引的500,000行的表，通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。

SELECT查询的速度

总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。

锁定方法

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。

在MySQL中对于使用表级锁定的存储引擎，表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

对WRITE，MySQL使用的表锁定方法原理如下：

如果在表上没有锁，在它上面放一个写锁。
否则，把锁定请求放在写锁定队列中。

对READ，MySQL使用的锁定方法原理如下：

如果在表上没有写锁定，把一个读锁定放在它上面。
否则，把锁请求放在读锁定队列中。

当一个锁定被释放时，锁定可被写锁定队列中的线程得到，然后是读锁定队列中的线程。

这意味着，如果你在一个表上有许多更新，SELECT语句将等待直到没有更多的更新。

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。

行级锁定的优点：

· 当在许多线程中访问不同的行时只存在少量锁定冲突。

· 回滚时只有少量的更改。

· 可以长时间锁定单一的行。

行级锁定的缺点：

· 比页级或表级锁定占用更多的内存。

· 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。

· 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。

· 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

在以下情况下，表锁定优先于页级或行级锁定：

表锁定事宜

对于InnoDB和BDB表，如果你用LOCK TABLES显式锁定表，MySQL只使用表锁定(而不是页、行或者列锁定)。对于这些表类型，我们建议你根本不要使用LOCK TABLES，因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事务隔离。

对于大表，对于大多数应用程序，表锁定比行锁定更好，但存在部分缺陷。

表锁定使许多线程同时从一个表中进行读取操作，但如果一个线程想要对表进行写操作，它必须首先获得独占访问。更新期间，所有其它想要访问该表的线程必须等待直到更新完成。

表更新通常情况认为比表检索更重要，因此给予它们更高的优先级。这应确保更新一个表的活动不能“饿死”，即使该表上有很繁重的SELECT活动。

表锁定在这种情况下会造成问题，例如当线程正等待，因为硬盘已满并且在线程可以处理之前必须有空闲空间。在这种情况下，所有想要访问出现问题的表的线程也被设置成等待状态，直到有更多的硬盘空间可用。

表锁定在下面的情况下也存在问题：

· 一个客户发出长时间运行的查询。

· 然后，另一个客户对同一个表进行更新。该客户必须等待直到SELECT完成。

· 另一个客户对同一个表上发出了另一个SELECT语句。因为UPDATE比SELECT优先级高，该SELECT语句等待UPDATE完成，并且等待第1个SELECT完成。

如果在MySQL中表锁定时遇到速度问题，可以将你的表转换为InnoDB或BDB表来提高性能。

使你的数据尽可能小

最基本的优化之一是使表在磁盘上占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快，并且在查询执行过程中小表的内容被处理时占用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。

可以使用下面的技术可以使表的性能更好并且使存储空间最小：

尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。
尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。
如果可能，声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免默认地在所有列上有它。
对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。
在MySQL/InnoDB中，InnoDB表使用更紧凑的存储格式。在以前版本的MySQL中，InnoDB记录包含一些冗余信息，例如列数目和每个列的长度，即使对于固定大小的列.
紧凑的InnoDB格式也改变了包含UTF-8数据的CHAR列的保存方式。在ROW_FORMAT=REDUNDANT格式中，UTF-8 CHAR(n)占用3*n字节，假定UTF-8编码的字符的最大长度是3字节。许多语言可以主要用单字节UTF-8字符来编写，固定的存储长度通常会浪费空间。通过根据需要剥离尾部的空格，ROW_FORMAT=COMPACT格式为这些列分配可变数量的n..3*n字节。
每张表的主索引应该尽可能短。这使一行的识别容易而有效。
只创建你确实需要的索引。索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好的索引压缩。
如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持对一个字符列的最左边部分创建一个索引。更短的索引会更快，不仅因为它们占较少的磁盘空间，而且因为它们将在索引缓存中提供更多的访问，因此磁盘搜索更少。

键所需的空间
MyISAM表使用B型树索引。你可以粗略地计算索引文件的大小为(key_length+4)/0.67, 加上所有的键之和。
InnoDB也支持外键约束。

InnoDB锁定模式
InnoDB实现标准行级锁定，在这里有两种类型的锁： locks:

· 共享的(S)锁允许一个事务去读一行（tuple）。

· 独占的锁(X)允许一个事务更新或删除一行。

默认mysql>提示符可以重新配置。定义提示符的字符串可以包含下面的特殊序列：

选项	描述
\v	服务器版本
\d	当前的数据库
\h	服务器主机
\p	当前的TCP/IP端口或套接字文件
\u	你的用户名
\U	你的全user_name@host_name账户名
\\	‘\’反斜线字符
\n	新行字符
\t	Tab字符
\	空格(反斜线后面的空格)
\_	空格
\R	当前的时间，24-小时军用时间(0-23)
\r	当前的时间，标准12-小时(1-12)
\m	当前时间的分钟
\y	当前的年，两位
\Y	当前的年，四位
\D	当前的日期
\s	当前时间的秒
\w	当前周的天，3字符格式(Mon，Tue，...)
\P	am/pm
\o	当前的月，数字格式
\O	当前的月，3字符格式(Jan，Feb，...)
\c	随发出的每个语句递增的计数
\S	分号
\'	单引号
\"	双引号