Mysql优化部分总结
1. 存储引擎的选择
存储引擎：MySQL中的数据、索引以及其他对象的存储方式
5.1之前默认存储引擎是MyISAM,5.1之后默认存储引擎是Innodb。
差异：
| 区别 | MyISAM | Innodb |
| 文件格式 | 数据和索引是分别存储的，数据.MYD，索引.MYI | 数据和索引是集中存储的.ibd |
| 文件能否移动 | 能，一张表就对应.frm，MYD，MYI 3个文件 | 不能，关联的还有data下其他文件 |
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 空间碎片 | 产生；定时清理，使用命令optimize table表名实现 | 不产生 |
| 事务 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 锁颗粒 | 表级锁 | 行级锁 |
MyISAM引擎设计简单，数据以紧密格式存储，所以某些读取场景下性能很好。
如果没有特别的需求，使用默认的Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键保证数据完整性。比如OA自动化办公系统。
《高性能Mysql》一书中列举很多存储引擎，但是其强烈推荐使用Innodb即可
2. 字段设计
- 数据库设计3大范式
- 第一范式（确保每列保持原子性）
- 第二范式（确保表中的每列都和主键相关）
- 第三范式（确保每列都和主键列直接相关，而不是间接相关）
通常建议使用范式化设计,因为范式化通常会使得执行操作更快。但这并不是绝对的,范式化也是有缺点的,通常需要关联查询，不仅代价昂贵,也可能使一些索引策略无效。
- 单表字段不宜过多
建议最多30个以内
字段越多,会导致性能下降,并且增加开发难度(一眼望不尽的字段,我们这些开发仔会顿时傻掉的)
- 使用小而简单的合适数据类型
a.字符串类型
固定长度使用char,非定长使用varchar,并分配合适且足够的空间
char在查询时,会把末尾的空格去掉;
b.小数类型
一般情况可以使用float或double,占用空间小,但存储可能会损失精度
decimal可存储精确小数,存储财务数据或经度要求高时使用decimal
c.时间日期
-- datetime:
范围：1001年~9999年
存储：8个字节存储,以YYYYMMDDHHMMSS的格式存储
时区：与时区无关
-- timestamp:
范围：1970年~2038年
存储：4个字节存储,存储以UTC格式保存,与UNIX时间戳相同
时区：存储时对当前的时区进行转换,检索时再转换回当前的时区
通常尽量使用timestamp,因为它占用空间小,并且会自动进行时区转换,无需关心地区时差
datetime和timestamp只能存储最小颗粒度是秒,可以使用BIGINT类型存储微秒级别的时间戳
d.大数据 blob和text
blob和text是为存储很大的数据的而设计的字符串数据类型,但通常建议避免使用
MySQL会把每个blob和text当做独立的对象处理,存储引擎存储时会做特殊处理,当值太大,innoDB使用专门的外部存储区域进行存储,行内存储指针,然后在外部存储实际的值。这些都会导致严重的性能开销
- 尽量将列设置为NOT NULL
a.可为NULL的列占用更多的存储空间
b.可为NULL的列,在使用索引和值比较时,mySQL需要做特殊的处理,损耗一定的性能
建议:通常最好指定列为NOT NULL,除非真的需要存储NULL值
- 尽量使用整型做主键
a.整数类型通常是标识列最好的选择,因为它们很快并且可以使用AUTO_INCREMENT
b.应该避免使用字符串类型作为标识列,因为它们很消耗空间,并且通常比数字类型慢
c.对于完全"随机"的字符串也需要多加注意。例如：MD5(),SHAI()或者UUID()产生的字符串。这些函数生成的新值也任意分布在很大空间内，这会导致INSERT和一些SELECT语句很缓慢
3. 索引
- 使用索引为什么快
索引相对于数据本身,数据量小
索引是有序的，可以快速确定数据位置
Innodb的表示是索引组织表,表数据的分布按照主键排序
- 索引的存储结构
a.B+树
b.哈希(键值对的结构)
MySQL中的主键索引用的是B+树结构,非主键索引可以选择B+树或者哈希
通常建议使用B+树索引，因为哈希索引缺点比较多:无法用于排序、无法用于范围查询、数据量大时,可能会出现大量哈希碰撞,导致效率低下
- 索引的类型
按作用分类:
1.主键索引
2.普通索引:没有特殊限制,允许重复的值
3.唯一索引:不允许有重复的值,速度比普通索引略快
4.全文索引:用作全文搜索匹配,但基本用不上,只能索引英文单词,而且操作代价很大
按数据存储结构分类:
1.聚簇索引
定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
主键索引是聚簇索引,数据的存储顺序是和主键的顺序相同的
2.非聚簇索引
定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
聚簇索引以外的索引都是非聚集索引,细分为普通索引、唯一索引、全文索引,它们也被称为二级索引。
主键索引的叶子节点存储的是"行指针",直接指向物理文件的数据行。
二级索引的叶子结点存储的是主键值
覆盖索引:可直接从非主键索引直接获取数据无需回表的索引
比如:
假设t表有一个(clo1,clo2)的多列索引
select clo1,clo2 from t where clo = 1;
那么,使用这条sql查询,可直接从(clo1,clo2)索引树中获取数据,无需回表查询,因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。
多列索引:使用多个列作为索引,比如(clo1,clo2)
使用场景:当查询中经常使用clo1和clo2作为查询条件时,可以使用组合索引,这种索引会比单列索引更快
需要注意的是,多列索引的使用遵循最左索引原则
假设创建了多列索引index(A,B,C)，那么其实相当于创建了如下三个组合索引：
1.index(A,B,C)
2.index(A,B)
3.index(A)
这就是最左索引原则，就是从最左侧开始组合。
- 索引优化
1.索引不是越多越好,索引是需要维护成本的
2.在连接字段上应该建立索引
3.尽量选择区分度高的列作为索引,区分度count(distinct col)/count(*)表示字段不重复的比例,比例越大扫描的记录数越少，状态值、性别字段等区分度低的字段不适合建索引
4.几个字段经常同时以AND方式出现在Where子句中,可以建立复合索引,否则考虑单字段索引
5.把计算放到业务层而不是数据库层
6.如果有 order by、group by 的场景，请注意利用索引的有序性。
order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能
例如对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort(外部排序) 的情况，影响查询性能
对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)。
如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;索引(a,b)无法排序。
- 可能导致无法使用索引的情况
1.is null 和 is not null
2.!= 和 <> (可用in代替)
3."非独立列":索引列为表达式的一部分或是函数的参数
例如:表达式的一部分:select id from t where id +1 = 5 函数参数:select id from t where to_days(date_clo) >= 10
4.like查询以%开头
5.or (or两边的列都建立了索引则可以使用索引)
6.类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然无法使用索引
select * from tb1 where email = 999;
4. sql优化建议
1.首先了解一下sql的执行顺序,使我们更好的优化
(1)FROM:数据从硬盘加载到数据缓冲区，方便对接下来的数据进行操作
(2)ON:join on实现多表连接查询,先筛选on的条件,再连接表
(3)JOIN:将join两边的表根据on的条件连接
(4)WHERE:从基表或视图中选择满足条件的元组
(5)GROUP BY:分组，一般和聚合函数一起使用
(6)HAVING:在元组的基础上进行筛选，选出符合条件的元组（必须与GROUP BY连用）
(7)SELECT:查询到得所有元组需要罗列的哪些列
(8)DISTINCT:去重
(9)UNION:将多个查询结果合并
(10)ORDER BY：进行相应的排序
(11)LIMIT:显示输出一条数据记录
join on实现多表连接查询，推荐该种方式进行多表查询，不使用子查询(子查询会创建临时表,损耗性能)
避免使用HAVING筛选数据,而是使用where
order by后面的字段建立索引,利用索引的有序性排序,避免外部排序
如果明确知道只有一条结果返回，limit 1 能够提高效率
2.超过三个表最好不要 join
3.避免 SELECT *，从数据库里读出越多的数据，那么查询就会变得越慢
4.尽可能的使用 NOT NULL列,可为NULL的列占用额外的空间,且在值比较和使用索引时需要特殊处理,影响性能
5.用exists、not exists和in、not in相互替代
原则是哪个的子查询产生的结果集小，就选哪个
select * from t1 where x in (select y from t2)
select * from t1 where exists ( select null from t2 where y = x )
IN适合于外表大而内表小的情况；existx适合于外表小而内表大的情况
6.使用exists代替distinct
当提交一个包含一对多表信息（比如部门表和职员表）的查询时，避免在select子句中使用distinct，一般可以考虑使用exists代替，使查询更为迅速，因为子查询的条件一旦满足，立马返回结果
低效写法：
select distinct dept_no,dept_name from dept d,emp e where d.dept_no=e.dept_no
高效写法：
select dept_no,dept_name from dept d where exists (select 'x' from emp e where e.dept_no=d.dept_no) 备注：其中x的意思是：因为exists只是看子查询是否有结果返回，而不关心返回的什么内容，因此建议写一个常量，性能较高！ 用exists的确可以替代distinct，不过以上方案仅适用dept_no为唯一主键的情况，如果要去掉重复记录，需要参照以下写法：
select * from emp where dept_no exists (select Max(dept_no)) from dept d, emp e where e.dept_no=d.dept_no group by d.dept_no)
7. 避免隐式数据类型的转换
隐式数据类型转换不能适用索引，导致全表扫描！t_tablename表的phonenumber字段为varchar类型
以下代码不符合规范：
select column1 into i_l_variable1 from t_tablename where phonenumber=18519722169;
应编写如下：
select column1 into i_lvariable1 from t_tablename where phonenumber='18519722169';
8. 分段查询
在一些查询页面中，当用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。
5.explain分析执行计划
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。
例：
explain select user from mysql.user;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | user | NULL | index | NULL | PRIMARY | 276 | NULL | 6 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 标识符 | 含义 |
id |
select标识符；这是select的查询序列号 |
select_type |
select类型： simple,简单select（不使用union和子查询） primary,查询中包含任何复杂的子部分，最外层的select被标记为PRIMARY union,union中的第二个或后面的select语句 DEPENDENT UNION：一般是子查询中的第二个select语句（取决于外查询，mysql内部也有些优化） UNION RESULT：union的结果 SUBQUERY：子查询中的第一个select DEPENDENT SUBQUERY：子查询中第一个select，取决于外查询（在mysql中会有些优化，有些dependent会直接优化成simple） DERIVED：派生表的select（from子句的子查询） |
table |
显示数据来自于哪个表，有时不是真实的表的名字（虚拟表），虚拟表最后一位是数字，代表id为多少的查询 |
type |
这个字段值较多，这里我只重点关注我们开发中经常用到的几个字段:system,const,eq_ref,ref,range,index,all; 性能由好到差依次为：==system>const>eq_ref>ref>range>index>all==(一定要牢记) system：表只有一行记录，这个是const的特例，一般不会出现，可以忽略 const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快 eq_ref：唯一性索引扫描，表中只有一条记录与之匹配。一般是两表关联，关联条件中的字段是主键或唯一索引 ref：非唯一行索引扫描，返回匹配某个单独值的所有行 range：检索给定范围的行，一般条件查询中出现了>、<、in、between等查询 index：遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。all和index都是读全表，但index是从索引中检索的，而all是从硬盘中检索的。 all：遍历全表以找到匹配的行 |
possible_keys |
显示可能应用在这张表中的索引，但不一定被查询实际使用 |
key |
实际使用的索引，如果没有显示null |
key_len |
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。 |
ref |
表示哪一列被使用了，常数表示这一列等于某个常数。 |
rows |
大致找到所需记录需要读取的行数 |
filtered |
表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。 |
Extra |
一些重要的额外信息
|
