mysql性能优化

【mysql数据库优化】原因：性能不够，通过一些措施提高性能。
不同的阶段采用不同的措施，不同的措施借助不同的工具。
--【措施1：选取合适的存储引擎】--数据库设计阶段
>5.5 innodb 默认
<5.5 myisam 默认
myisam 和 innodb的区别？
【数据文件的存储格式】
① innodb由2个文件组成：*.frm表结构文件、*.ibd数据索引文件。
② myisam由3个文件组成：*.frm表结构文件、*.MYD数据文件、*.MYI 索引文件。
【数据的存储顺序】
① innodb如果有主键的话，数据会根据主键顺序进行存储。
② myisam，数据按照插入顺序进行存储。
【并发性】
① innodb并发处理能力更好一些，因为innodb支持行级锁定。
② myisam逊色一些，因为myisam只支持表锁。
【压缩性】--myisam独有的
注意：更新不频繁的数据适合进行压缩，例如：邮政编码。
【1】压缩
压缩后的影响：
① 需要重建索引。
② 数据表变成只读表。
【2】解压
【对事务的支持】
【对外键的支持】
--【措施2：优化sql语句，尽可能的使用上索引】--编码 和 项目上线运营阶段
查询的SQL语句有没有使用上索引。
【1】找出需要优化的sql语句（执行时间比较长的sql语句）
借助工具找。
① 慢查询日志（把执行时间超过一定阀值的sql语句给记录下来）
第1步，开启慢查询日志
① 临时开启，set slow_query_log =1;
② 修改配置文件，永久开启

第2步，设置时间阀值
set long_query_time = 0.5;
② profile机制（精确的记录sql语句执行的时间）
第1步，开启，set profiling = 1;
第2步，执行要分析的sql语句
第3步，查看时间：show profiles;
【2】对sql语句进行优化。提高sql语句的执行效率，尽可能让sql语句使用上索引。
【索引】一种特殊的数据。
① 作用：快速的定位要查询的数据。
② 类型：
普通索引：
唯一索引：要求关键字不能重复。
主键索引：要求关键字不能重复 且 不能为null
全文索引，字段内容中的一些单词提取出来，作为索引的关键字。
--创建语法：alter table articles add fulltext index(title);
--使用语法：select * from articles where  match(字段)  against(模糊内容);
注意：只支持英文，不支持中文。
复合索引（多列索引）：多个字段作为索引的关键字。
前缀索引：把字段内容的前N位作为索引的关键字。
创建语法：alter table 表名 add index (字段(N))
前n位，需要计算出来！
第1步，计算前n位的位数，前几位可以唯一标识字段的内容。
1）计算不重复记录的总条数（需要去除内容重复的记录）：select count(distinct 字段) from 表名;
2）计算前n位不重复记录的总条数： select count( distinct left(字段 , n) ) from 表名;
第2步，使用第1步计算出的位数，创建前缀索引。
alter table 表名  add key ( left( 字段名, n ) )。
③ 创建索引：
普通索引：index
唯一索引：unique key
主键索引：primary key
全文索引：fulltext index
④ 删除索引：
1）删除主键索引：alter table 表名 drop primary key;(注意auto_increment属性)
2）删除非主键索引：alter table 表名 drop index 索引名称；
【哪些字段适合创建索引？】
① 经常作为查询条件的字段
② 唯一性差的字段不适合。例如，性别。
③ 频繁更新的字段不适合。
【第2.1步，分析sql语句是否使用了索引】
借助工具：explain执行计划
使用：explain sql语句\G

【2.2步】sql语句是否符合索引使用的一些原则？
【① 索引覆盖（黄金索引）】
查询字段恰好是索引字段 或者 是索引字段的一部分,此时在索引区就获取到了数据，而不用去数据区。
采取的措施：一些经常查询的字段，建一个复合索引。

【② 列独立原则：】
索引字段要独立存在于  运算表达式的一侧，这时才有可能用上索引。
【③ like模糊查询：】
查询内容左边是固定的（like "张%"），这时才有可能用上索引。
【④ OR原则：】
参与or运算的字段都有索引，才有可能用上索引。
【⑤ 复合索引最左原则：】
条件字段是复合索引最左边的列，才有可能用上复合索引。

【⑥ mysql自身的智能选择。】
一般当取出的数据量超过表中数据的20%
【2.3 常见语句的优化】：
① group by语句优化：默认会进行排序。如果不需要排序，可以禁止排序，order by null;

--【措施3：使用mysql数据库自带的查询缓存功能】 select语句的结果给缓存起来。--上线运营阶段
1）开启缓存的配置：（修改配置文件my.ini）
a）开启缓存：query_cache_type=1
b）设置大小：query_cache_size=134217728（单位是字节Byte，设置128M的话就是128*1024*1024）bit位、B字节、KB千字节、M、G
2）无缓存的情况：
--情况1：【缓存失效】数据 或 表结构发生改变
update emp set job='123456' where empno=123456;
--情况2：【不缓存】sql语句中有变化表达式（随机数）
select ename,job,now() from emp where empno = 123456;
--情况3：【生成多份缓存】相同结果的sql语句，由于空格、大小写问题会生成多个缓存
select * from emp where empno=123456;
select * from emp WHERE empno=123456;
--情况4：【禁用缓存】
select sql_no_cache * from emp where empno=123456;
--【措施4：分区技术（逻辑分表）】--把数据分成多个区域是存储，对于客户端来说操作的还是一张表。
应用场景：单表的数据量比较大时，例如：1亿条记录。
分区字段：根据哪个字段的内容进行分区，哪个字段就是分区字段。
【注意】:① 表中有主键时，分区字段必须是主键或者主键的一部分。
② 表中有唯一索引时，分区字段必须是唯一索引或者一部分。
分区的依据（分区算法）：list、range、hash、key
创建分区语法：
create table 表名{
字段信息,
索引信息,
}engine=myisam 表选项
partition by 分区算法 (分区字段){
分区信息
}
分区管理：
① 删除分区
1）删除list或者range分区
注意：会造成数据的丢失。
2）删除hash或者key分区
实际上进行分区的合并操作。
② 增加分区
1）alter table 表名  add  partition partitions  数量;
2）alter table 表名 add partition(
partition 名称 values less than (常量)
  );
--【措施5：分表技术（物理分表）】
解决问题：单表数据量过大。
1）水平分表
手动创建多张结构相同的表。
2）垂直分表
把一个表的全部字段分别存储到不同的表里边。
把经常使用的字段存到 主表，不经常使用的字段存到 辅表中
--【措施6：修复数据碎片】--上线运营阶段
在长期的数据更改（更新、删除）过程中，索引文件和数据文件，都将产生空洞，形成碎片。
通常是按照周、月，在凌晨进行碎片修复。
修复的语法：optimize    table  表名;
--【措施7：逆范式/反三范式】--项目设计阶段
范式：创建数据表的时候遵循的规范。
1）故意违反三范式，在数据表中添加冗余字段，来 提高查询效率。
2）① 好处：可以提高了查询效率（单表查询优于连表查询）
  ② 弊端：增加了业务功能的复杂性，在插入、删除操作时要维护增加的字段。