Mysql 优化 (二)

发表于 | 更新于 | 分类于

优化的方面

  • 索引相关
  • 分表设计
  • 架构设计
  • 慢查询日志收集
  • 其他优化

Index

  • 它是物理存在的

规则

字段独立原则
name (对)
name + 2 (错)
左原则
like ‘bill%’ (对)
like ‘%bill’ (错)
复合索引
不能直接使用第2个字段
or 原则
左右都有索引，会合并索引一起使用
一边有索引、一边没有索引，导致整体都没得索引使用

设计依据

被频繁使用的字段设置索引

* 字段被频繁用在where和order等条件里边。
* 数据表创建完毕，要预估那些字段被经常使用，就给其创建索引

执行时间长的sql语句考虑设计索引

* 可以利用”慢查询日志”收集这样的sql语句并优化设计索引
* slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。
* long_query_time:  什么样的SQL才会记录到慢查询日志里面呢？ 这个是由参数long_query_time控制

逻辑非常重要的sql语句考虑设计索引

* 例如商城系统里边，会员给自己账户充值就比较重要
 * 还有会员下订单购物，进行付款的时候也比较重要。

字段内容足够花样化，适合创建索引

* 反面教材，性别不能设计索引(内容的取值非常少)

前缀索引

什么是前缀索引：

如果一个字段内容的前边的n位信息已经足够标识当前的字段内容，就可以把字段的前n位获得出来并创建索引，通过字段内容前n位创建的索引就称为“前缀索引”。
语法： alter table 表名 add key (字段(前n 位 位数));

1
2
3
// 测试，取前几位，可以获取索引
alter table emp add key(password(3));
select count(distinct(substring())) from emp;
  • substring(字段,开始位置,长度n) 截取字段内容，字段下标从1开始

全文索引

把字段中的一些关键字单词出来制作索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
)engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

alter table articles add fulltext key (body);
  • 全文索引会解决like模糊查找不用索引的问题：
  • select * from 表名 like ‘%内容%’; //不会使用索引
  • 以上模糊查找如果必须使用索引，就可以使用全文索引
  • select * from 表名 where 字段 like ‘%内容%’; //不会用到全文索引
  • select * from 表名 where match(字段) against(内容) //会用到全文索引

全文索引注意

  • 字段类型必须为varchar/char/text类型
  • mysql 5.6.4之前只有Myisam支持，5.6.4之后则Myisam和innodb都支持。
  • mysql中的全文索引目前只支持英文(不支持中文)，如果需要支持中文可以使用sphinx
  • 生产活动中mysql的全文索引不常使用，sphinx常使用
  • mysql全文索引会自作聪明，对关键字的收录有自己的考虑(不是全部关键字都给收录)。
    例如生活常用单词、频繁使用单词都不给创建索引(比如 for when where run you等等)

索引结构

  • 索引就是数据结构， 数据以一定的结构组织起来，内部嵌入算法，算法可以保证数据被快速获取。
  • 数据结构：数据以一种规律的、规则的方式组织在一起。(例如0-9顺序排列数字、a-z顺序排列字母等)
  • 主要讨论两种：Myisam 和 Innodb..
    Mysql的索引数据结构都是B+tree结构(除此还有B-tree、二叉树等结构)

MySQL 聚簇索引&&二级索引&&辅助索引

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

以InnoDB来说，每个InnoDB表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键，该主键索引是聚集索引。如果你不定义为您的表的主键时，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚集索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

MySQL 回表

二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

explain

explain select id from hotel where id = 61\G

id: 自动分配一个唯一的标识符
select_type: SELECT 查询的类型
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引.指出MySQL能使用哪个索引在该表中找到行
key_len: 4 // 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值.原则上 rows 越少越好.
filtered: 表示此查询条件所过滤的数据的百分比
Extra: 额外的信息

select_type

SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)

type

system:表仅有一行(=系统表)。这是const联接类型的一个特例。
const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了.

Extra

Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

我们的索引是

KEY user_product_detail_index (user_id, product_name, productor)

但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort.
如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了

需要优化的：Using filesort，Using temporary

分表设计

一个数据表里边存储的记录信息太多了，例如记录的条数达到千万条信息，
该数据表的活性就大大降低，运行速度、效率大打折扣，影响mysql数据库的整体性能，
因此要给数据表进行分表设计，使得每个表存储少量的数据。

逻辑分表

注意：分表字段必须是主键 或 主键的一部分(复合主键索引的第二个字段也可以)

range 分表 [范围方式]

可以将一个表通过年份划分成三个分区，80年代(1980)的数据90年代(1990)的数据，以此类推

语法： partition by range(表达式 | 字段)(
partition 名称1 values less than (常量整数值),
partition 名称2 values less than (常量整数值),
… …
)

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `bill` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `create_at` int(11) NOT NULL COMMENT '记录时间',
  `comid` int(11) NOT NULL COMMENT '企业id',
  PRIMARY KEY (`id`),
  KEY `comid` (`comid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=328830 DEFAULT CHARSET=utf8 COMMENT='账单记录表'
partition by range (id)(
	partition id1 values less than (500),
	partition id2 values less than (1000),
	partition id3 values less than MAXVALUE // 最大值
)

range 添加分表

语法：alter table 表名 add partition(
partition 名称1 values less than (常量整数值),
… …
)

list 分表 [范围方式]

语法：partition by list(month(pubdate))(
partition 名称 values in (列表范围),
… …
)

list 添加分表

语法：alter table 表名 add partition(
partition 名称 values in (列表范围),
… …
)

range, list 删除分表

语法：alter table 表名 drop partition 分区名称

key 分表 [求余方式]

语法：partition by key(条件字段) partitions 数量;

hash 分表 [求余方式]

partition by hash (表达式|字段) partitions 数量

key, hash 添加分表

alter table 表名 add partition partitions 数量;

key, hash 删除分表

alter table 表名 coalesce partition 数量

物理分表

垂直分表

慢查询日志收集

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

1
2
show variables like '%slow_query_log%';
set global slow_query_log=1;

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件，增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器

日志分析工具mysqldumpslow

mysqldumpslow --help

-s, 是表示按照何种方式排序
c: 访问计数

l: 锁定时间

r: 返回记录

t: 查询时间

al:平均锁定时间

ar:平均返回记录数

at:平均查询时间

-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

比如:
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more