id:1 select_type: SIMPLE table: NULL partitions: NULL type: NULL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL filtered: NULL Extra: Select tables optimized away1 row in set, 1 warning (0.01 sec)
经过优化之后，由于索引是按顺序排列的，MySQL 不需要查询表中的数据，而是通过查询索引最后的一个数据，就可以得知执行结果了。而且这个时候，不管表的数据量多大，查询 MAX() 所需要的时间都是基本固定的，这样就尽可能地减少了 IO 操作。
COUNT()
分析 SQL 语句：使用 COUNT() 函数在一条 SQL 中同时查出 2006 年和 2007 年电影的数量
SELECT count(release_year = '2006' OR NULL) AS '2006 年电影数量' , count(release_year = '2007' OR NULL) AS '2007 年电影数量'FROM film;
count(*) 包含空值，count(id) 不包含空值。上述语句就是优化 Count() 函数取值
子查询
分析 SQL 语句：查询 sandra 出演的所有影片
SELECTtitle ,>
通常情况下，需要把子查询优化为 join 查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。
GROUP BY
group by 可能会出现临时表、文件排序等，影响效率。可以通过关联的子查询，来避免产生临时表和文件排序，可以节省 IO。
group by 查询优化前：
EXPLAINSELECT actor.first_name , actor.last_name , Count(*)FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id)GROUP BY film_actor.actor_id;
执行结果如下：
id:1 select_type: SIMPLE table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: refpossible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 27 filtered: 100.00 Extra: Using index2 rowsin set, 1 warning (0.01 sec)
group by 查询优化后：
EXPLAINSELECT actor.first_name , actor.last_name , c.cnt FROM sakila.actor INNER JOIN( SELECT actor_id , count(*) AS cnt FROM sakila.film_actor GROUP BY actor_id) AS c USING(actor_id);
执行结果如下：
id:1 select_type: PRIMARY table: actor partitions: NULL type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 filtered: 100.00 Extra: NULL*************************** 2. row ***************************>
LIMIT
LIMIT 常用于分页处理，时常会伴随 ORDER BY 从句使用，因此大多时候会使用 Filesorts ，这样会造成大量的 IO 问题
优化步骤1：使用有索引的列或主键进行 Order By 操作
优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤（保证主键是自增且有索引）
索引优化
1.为合适的列建立索引
在 where 从句，group by 从句，order by 从句，on 从句中出现的列
索引字段越小越好
离散度的列放到联合索引的前面
例如：
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584;
上述 SQL 语句，是 index(staff_id,customer_id) 合理，还是 index(customer_id,staff_id) 合理。执行语句如下：
SELECT count(DISTINCT customer_id) ,
count(DISTINCT staff_id) FROM payment;-- 结果是 599 2
由于 customer_id 的离散度更大，所以应该使用
2.找到重复和冗余的索引
之所以要找到重复和冗余的索引，是因为过多的索引不但影响写入，而且影响查询，索引越多，分析越慢。那么为何重复索引、冗余索引？概念如下：
重复索引是指相同的列以相同的顺序建立的同类型的索引，如下表中 primary key 和> CREATE TABLE test(
id INT NOT NULL PRIMARY KEY ,
NAME VARCHAR(10) NOT NULL ,
title VARCHAR(50) NOT NULL ,
UNIQUE(id)) ENGINE = INNODB;
UNIQUE(ID) 和 PRIMARY KEY 重复了。
冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，例子如下：
KEY(NAME ,>
查找重复及冗余索引的 SQL 语句如下：
USE information_schema;
SELECTa.TABLE_SCHEMA AS '数据名' , a.table_name AS '表名' , a.index_name AS '索引1' ,b.INDEX_NAME AS '索引2' , a.COLUMN_NAME AS '重复列名'FROM STATISTICS a JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.table_name AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX AND a.COLUMN_NAME = b.COLUMN_NAME WHERE a.SEQ_IN_INDEX = 1AND a.INDEX_NAME b.INDEX_NAME
也可以使用工具 pt-duplicate-key-checker 检查重复索引和冗余索引,使用例如：
pt-duplicate-key-checker -uroot -p '123456' -h 127.0.0.1 -d sakila
执行结果如下：
##########################################################################Summary of indexes# #########################################################################Size Duplicate Indexes 118425374# Total Duplicate Indexes 24# Total Indexes 1439
3.删除不用的索引
目前 MySQL 中还没有记录索引的使用情况，但是在 PerconMySQL 和 MariaDB 中可以通过 INDEX_STATISTICS 表来查看哪些索引未使用，但在 MySQL 中目前只能通过慢查询日志配合共组 pt-index-usage 来进行索引使用情况的分析。
pt-index-usage -uroot -p '123456' /usr/local/var/mysql/luyiyuandeMacBook-Pro-slow.log; 数据库结构优化
选择合适的数据类型
使用可以存下你的数据的最小的数据类型
使用简单的数据类型。Integer 要比 varchar 类型在 MySQL 中处理更高效
尽可能使用 not null 定义字段
尽量少用 text 类型，非用不可时最好考虑分表
数据库表的范式化优化与反范式化优化
略
数据库表的垂直拆分
垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分就可以按以下原则进行：
把不太常用的字段单独存放到一个表中
把大字段独立存放到一个表中
把经常一起使用的字段放到一起
数据库表的水平拆分
当单表的数据量过大，导致增删查改等操作过慢，这时候需要对表进行水平拆分。水平拆分的表，每一张表的结构都是完全一致的。
常用的水平拆分方法为：
1. 对 customer_id 进行 hash 运算，如果要拆分成 5 个表则使用 mod(customer_id,5) 取出 0-4 个值
2. 针对不同的 hashID 把数据存到不同的表中
挑战：
跨分区表进行数据查询
统计及后台报表操作 系统配置优化
数据库系统配置优化
数据库是基于操作系统的，目前大多数 MySQL 都是安装在Linux 系统之上，所以对于操作系统的一些参数配置也会影响到 MySQL 的性能，下面列举一些常用到的系统配置。
网络方面的配置，要修改文件 /etc/sysctl.conf
#增加 tcp 支持的队列数net.ipv4.tcp_max_syn_backlog = 65535# 减少断开连接时，资源回收net.ipv4.tcp_max_tw_buckets = 8000net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_fin_timeout = 10
打开文件数的限制，可以使用 ulimit -a 查看目录的各项限制，可以修改文件 /etc/security/limits.conf ，增加以下内容以修改打开文件数量的限制
soft nofile 65535hard nofile 65535
除此之外最好在 MySQL 服务器上关闭 iptables，selinux 等防火墙软件。
MySQL 配置文件
MySQL 可以通过启动时指定配置参数和使用配置文件两种方法进行配置，在一般情况下，配置文件位于 /etc/my.cnf 或是 /etc/mysql/my.cnf，MySQL 查询配置文件的顺序是可以通过以下方法过的
常用参数说明
innodb_buffer_pool_size：用于配置 Innodb 的缓冲池
如果数据库中只有 Innodb 表，则推荐配置量为总内存的 75%
Innodb_buffer_pool_size >= Total MBSELECTENGINE ,round(sum(data_length + index_length) / 1024 / 1024 ,1) AS 'Total MB'FROM information_schema. TABLES WHERE table_schema NOT IN("information_schema" ,"performance_schema")GROUP BY ENGINE; innodb_buffer_pool_instances：MySQL 5.5 中新增参数，可以控制缓冲池的个数，默认情况下只有一个缓冲池。 innodb_log_buffer_size：Innodb 日志缓冲的大小，由于日志最长，每秒钟就会刷新，所以一般不用太大。 innodb_flush_log_at_trx_commit：对 Innodb 的 IO 效率影响很大。 innodb_file_per_table：控制 Innodb 每一个表都使用独立的表空间，默认为 OFF，也就是所有表都会建立在共享表空间中。 innodb_stats_on_metadata：决定 MySQL 在什么情况下会刷新 innodb 表的统计信息。
第三方配置工具使用 percona：https://tools.percona.com/ 服务器硬件优化
如何选择 CPU
MySQL 有一些工作只能使用到单核 CPU，选择高频
MySQL 对 CPU 核数的支持并不是越多越快，MySQL 5.5 版本不要超过 32 个核
硬盘 IO 优化
RAID 级别简介
RAID 0：也称为条带，就是把多个磁盘链接成一个硬盘使用，这个级别 IO 最好
RAID 1：也成为镜像，要求至少两个磁盘，每组磁盘存储的数据相同
RAID 1 + 0：就是 RAID 1 和 RAID 0的结合。同时具备两个级别的优缺点。一般建议数据库使用这个级别。
RAID 5：把多个（最少 3 个）硬盘合并成 1 个逻辑盘使用，数据读写时会建立奇偶校验信息，并且奇偶校验信息和相对应的数据分别存储在不同的磁盘上。当 RAID 5 的一个磁盘数据发生损坏后，利用剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。
QQ群⑧：
窥视卡
雷达卡
发表于 2018-10-6 11:37:52
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡