MySql优化

Mysql优化

  1. 数据设计层面(表结构设计,E-R图、关系、引擎)
  2. SQL语句优化
  3. 索引
  4. 架构

优化原则

  1. 数据库在设计的时候严格按照范式设计,或者有些情况下进行反范式设计。
  2. 使用字段数据类型的时候,尽可能的用小的数据类
  3. 在数据库里面开启慢查询,帮我分析我的哪些SQL语句比较慢,将比较慢的SQL语查用日志记录方式记录下来再进行分析和处理
  4. 使用explain我的SQL语句进行分析,分析为什么比较慢改写SQL语句或者针对需要的字段适当的加上索引
  5. 我们还可以在应用层面进行优化,例如加上缓存(memcached和redis),或者页面静态化,让后面的请求不再查询数据库,这样效率更高,将效率分摊至前端方便扩展应用服务器
  6. 不使用数据库进行存储过程、储发器等复杂的业务逻辑,因为压力在数据库上面,将这些存储过程和储发器由前端业务进行处理,进一步减轻数据库服务器的压力
  7. 如果速度还是慢,我们可以在架构层面进行调整,我们可以使用主从、读写分离的方式减轻数据库服务器的压力,避免在单台机器上过度消耗资源。mysql集群
  8. 在使用的时候我对数据库的表进行纵切,垂直分表,将不经常读的内容和经常操作的内容放置不同的表中。不经常读取内容,降低磁盘IO。
  9. 如果还是慢我们可以使用mysql表分区技术,将一个表分成多个不同的文件。
  10. 如果内容特别多我们可以采用数据库中间件或者类似于分库分表分机器的技术将表分至不同的数据库或者数据库实例(服务器)中
  11. 另外还有一些小的优化技巧,这些小的优化技巧都是可以根据explain分析出来的。例如innodb,本身不支持rtree索引,而支持的btree索引,对统计计数不友好。我们可以将统计计数专门使用一个字段来维护或者是使用NoSQL设计来存储。也可以一部份经常读取的内容放至至NoSQL中,进行混合式存储。
  12. 选择存储引擎的时候,我们可以按照一些具体业务场景选择存储引擎(引擎都有各自的特信,你可以使用不同的引擎处理不同的业务)
  13. 小技巧,不要使用or,like,搜索的加上主键,不用或者少用全文索引
  14. 如果非要使用至全文索引,将全文索引专门建立全文索引服务器
  15. 优化相关配置的参数
  16. 可以提升机器性能例如用固态硬盘、用更大的内存

数据库的范式设计原则

第一范式

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性,本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式,如下表所示。

第二范式

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下表所示。

第三范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键,如下表所示。

存储引擎

1
2
##显示系统的存储引擎支持
SHOW ENGINES \G;

对比图

对比图

  1. MyISAM是MySQL默认的存储引擎。MyISAM不支持事务、也不支持外键,其优势是访问的速度快,对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别是:.frm(存储表定义);.MYD(MYData,存储数据);.MYI(MYIndex,存储索引)。数据文件和索引文件可以放置在不同的目录,平均分布IO,获得更快的速度。
  2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。下面将重点介绍存储引擎为InnoDB的表在使用过程中不同于使用其他存储引擎的表的特点。
  3. MEMORY存储引擎使用存在于内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件,格式是.frm。MEMORY类型的表访问非常地快,因为它的数据是放在内存中的,并且默认使用HASH索引,但是一旦服务关闭,表中的数据就会丢失掉。
  4. MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。

如何选择存储引擎

在选择存储引擎时,应根据应用特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。下面是几种常用存储引擎的适用环境。

MyISAM:默认的MySQL插件式存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

InnoDB:用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、删除操作,那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定,还可以确保事务的完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB都是合适的选择。

MEMORY:将所有数据保存在 RAM 中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问。MEMORY 的缺陷是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速得到访问结果。

MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用它们。MERGE表的优点在于可以突破对单个MyISAM表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。

注意:以上只是我们按照实施经验提出的关于存储引擎选择的一些建议,但是不同应用的特点是千差万别的,选择使用哪种存储引擎才是最佳方案也不是绝对的,这需要根据用户各自的应用进行测试,从而得到最适合自己的结果。

索引

所有MySQL列类型都可以被索引,对相关列使用索引是提高SELECT操作性能的最佳途径。根据存储引擎可以定义每个表的最大索引数和最大索引长度,每种存储引擎(如 MyISAM、InnoDB、BDB、MEMORY等)对每个表至少支持16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。MyISAM和InnoDB存储引擎的表默认创建的都是BTREE索引。MySQL目前还不支持函数索引,但是支持前缀索引,即对索引字段的前N个字符创建索引。前缀索引的长度跟存储引擎相关,对于MyISAM存储引擎的表,索引的前缀长度可以达到1000字节长,而对于InnoDB存储引擎的表,索引的前缀长度最长是767字节。请注意前缀的限制应以字节为单位进行测量,而CREATE TABLE语句中的前缀长度解释为字符数。在为使用多字节字符集的列指定前缀长度时一定要加以考虑。

MySQL 中还支持全文本(FULLTEXT)索引,该索引可以用于全文搜索。但是当前最新版本(5.0)中只有MyISAM存储引擎支持FULLTEXT索引,并且只限于CHAR、VARCHAR和TEXT列。索引总是对整个列进行的,不支持局部(前缀)索引。也可以为空间列类型创建索引,但是只有 MyISAM 存储引擎支持空间类型索引,且索引的字段必须是非空的。默认情况下,MEMORY存储引擎使用HASH索引,但也支持BTREE索引。

1
create index cityname on city (city(10));#创建前缀索引

索引的使用原则

搜索的索引列,不一定是所要选择的列。换句话说,最适合索引的列是出现在WHERE子句中的列,或连接子句中指定的列,而不是出现在SELECT关键字后的选择列表中的列。使用唯一索引。考虑某列中值的分布。索引的列的基数越大,索引的效果越好。例如,存放出生日期的列具有不同值,很容易区分各行。而用来记录性别的列,只含有“M”和“F”,则对此列进行索引没有多大用处,因为不管搜索哪个值,都会得出大约一半的行。

使用短索引。如果对字符串列进行索引,应该指定一个前缀长度,只要有可能就应该这样做。例如,有一个CHAR(200)列,如果在前10个或20个字符内,多数值是唯一的,那么就不要对整个列进行索引。对前10个或20个字符进行索引能够节省大量索引空间,也可能会使查询更快。较小的索引涉及的磁盘 IO 较少,较短的值比较起来更快。更为重要的是,对于较短的键值,索引高速缓存中的块能容纳更多的键值,因此,MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。

利用最左前缀。在创建一个n列的索引时,实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用,因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

不要过度索引。不要以为索引“越多越好”,什么东西都用索引是错误的。每个额外的索引都要占用额外的磁盘空间,并降低写操作的性能。在修改表的内容时,索引必须进行更新,有时可能需要重构,因此,索引越多,所花的时间越长。如果有一个索引很少利用或从不使用,那么会不必要地减缓表的修改速度。此外,MySQL 在生成一个执行计划时,要考虑各个索引,这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多,也可能会使MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。

慢查询

1
show status like 'Com_%';	#查询当前的运行结果

Com_xxx表示每个xxx语句执行的次数,我们通常比较关心的是以下几个统计参数。

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 大致的执行比例是多少。对于更新操作的计数,是对执行次数的计数,不论提交还是回滚都会进行累加。

此外,以下几个参数便于用户了解数据库的基本情况。

Connections:试图连接MySQL服务器的次数。

Uptime:服务器工作时间。

Slow_queries:慢查询的次数。

定位效率较低的慢查询

可以通过以下两种方式定位执行效率较低的SQL语句。

通过慢查询日志定位那些执行效率较低的SQL语句,用–log-slow-queries[= file_name]选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。具体可以查看本书第26章中日志管理的相关部分。慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用 show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。

EXPLAIN/DESC

explain 分析

1
EXPLAIN SELECT * FROM d_user;

select_type:表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个SELECT)等。table:输出结果集的表。

type:表示MySQL在表中找到所需行的方式,或者叫访问类型,ALL\index\range\ref\eq_ref\const,system\null

possible_keys:表示查询时可能使用的索引。

key:表示实际使用的索引。

key_len:使用到索引字段的长度。

rows:扫描行的数量。

Extra:执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

type类型说明

  1. type=ALL,全表扫描,MySQL遍历全表来找到匹配的行:
  2. type=index,索引全扫描,MySQL遍历整个索引来查询匹配的行:
  3. type=range,索引范围扫描,常见于<、<=、>、>=、between等操作符:
  4. type=ref,使用非唯一索引扫描或唯一索引的前缀扫描,返回匹配某个单独值的记录行
  5. type=eq_ref,类似ref,区别就在使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配;简单来说,就是多表连接中使用 primary key或者 unique index作为关联条件。
  6. type=const/system,单表中最多有一个匹配行,查询起来非常迅速,所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理,例如,根据主键 primary key或者唯一索引 unique index进行的查询。
  7. type=NULL,MySQL不用访问表或者索引,直接就能够得到结果

类型type还有其他值,如ref_or_null(与ref类似,区别在于条件中包含对NULL的查询)、index_merge(索引合并优化)、unique_subquery(in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似,区别在于 in 的后面是查询非唯一索引字段的子查询)等。

mysql索引存储类型

B-Tree索引:最常见的索引类型,大部分引擎都支持B树索引。

HASH索引:只有Memory引擎支持,使用场景简单。

R-Tree索引(空间索引):空间索引是MyISAM的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少,不做特别介绍。

Full-text(全文索引):全文索引也是 MyISAM 的一个特殊索引类型,主要用于全文索引,InnoDB从MySQL 5.6版本开始提供对全文索引的支持。

索引类型总结

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型

在数据库表中,对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表:

代码如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL );

我们随机向里面插入了10000条记录,其中有一条:5555, admin。

在查找username=”admin”的记录 SELECT * FROM mytable WHERE username=’admin’;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

MySQL索引类型包括:

一、普通索引

这是最基本的索引,它没有任何限制。它有以下几种创建方式:

1.创建索引

复制代码

代码如下:

1
CREATE INDEX indexName ON mytable(username(length));

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

2.修改表结构

代码如下:

ALTER mytable ADD INDEX [indexName] ON (username(length)) – 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

– 删除索引的语法:

DROP INDEX [indexName] ON mytable;

二、唯一索引

它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:

复制代码

代码如下:

CREATE UNIQUE INDEX indexName ON mytable(username(length))

– 修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

– 创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

三、主键索引

它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:

复制代码

代码如下:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );

当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

四、组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

1
CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   city VARCHAR(50) NOT NULL,   age INT NOT NULL  );

为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

1
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

usernname,city,age

usernname,city

usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

1
2
SELECT * FROM mytable WHREE username="admin" AND city="郑州"
SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到:

复制代码

代码如下:

1
2
SELECT * FROM mytable WHREE age=20 AND city="郑州"
SELECT * FROM mytable WHREE city="郑州"

五、建立索引的时机

到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

复制代码

代码如下:

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city=’郑州’

此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

复制代码

代码如下:

SELECT * FROM mytable WHERE username like’admin%’

而下句就不会使用:

复制代码

代码如下:

SELECT * FROM mytable WHEREt Name like’%admin’

因此,在使用LIKE时应注意以上的区别。

六、索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

1.虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

七、使用索引的注意事项

使用索引时,有以下一些技巧和注意事项:

1.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

3.索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

4.like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

5.不要在列上进行运算

复制代码

代码如下:

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:

复制代码

代码如下:

select * from users where adddate<‘2007-01-01’;

6.不使用NOT IN和<>操作

以上,就对其中MySQL索引类型进行了介绍。希望对大家有所帮助。

表分区

1
SHOW VARIABLES LIKE '%partition%';	#查看是否支持分区

Mysql分区支持下面四种类型

  1. RANGE分区:基于一个给定连续区间范围,把数据分配到不同的分区。
  2. LIST分区:类似RANGE分区,区别在LIST分区是基于枚举出的值列表分区,RANGE是基于给定的连续区间范围分区。
  3. HASH分区:基于给定的分区个数,把数据分配到不同的分区。
  4. KEY分区:类似于HASH分区。