(MySql优化方法)
硬件配置
- 对磁盘进行扩容
- 将机械硬盘换为SSD
- 把CPU的核数往上提一些，增强数据库的计算能力
- 内存扩容，让Bufffer Pool能吃进更多数据
成本高，见效快
参数配置
-
保证从内存读取
-
数据预热
-
降低磁盘的写入次数
-
增大redo log，减少落盘次数
-
通用查询日志、慢查询日志可以不开，binlog可开启
-
写redo log策略 innodb_flush_log_at_trx_commit 设置为 0 或 2
0：每隔 1 秒写日志文件和刷盘操作（写日志文件 LogBuffer --> OS cache，刷盘 OS cache --> 磁盘文件），最多丢失 1 秒数据
1：事务提交，立刻写日志文件和刷盘，数据不丢失，但是会频繁 IO 操作
2：事务提交，立刻写日志文件，每隔 1 秒钟进行刷盘操作
-
-
系统调优参数
- back_log
- wait_timeout
- max_user_connection
- thread_concurrency
- skip_name_resolve
- key_buffer_size
- innodb_buffer_pool_size
- innodb_additional_mem_pool_size
- innodb_log_buffer_size
- query_cache_size
- read_buffer_size
- sort_buffer_size
- read_buffer_size
- read_rndn_buffer_size
- record_buffer
- thread_cache_size
- table_cache
表结构设计
-
设计聚合表
-
设计冗余字段
-
分表
分表分为垂直拆分和水平拆分两种。
垂直拆分，适用于字段太多的大表，比如：一个表有100多个字段，那么可以把表中经常不被使用的字段或者存储数据比较多的字段拆出来。
水平拆分，比如：一个表有5千万数据，那按照一定策略拆分成十个表，每个表有500万数据。这种方式，除了可以解决查询性能问题，也可以解决数据写操作的热点征用问题。
-
字段的设计
- 使用可以存下数据最小的数据类型，合适即可
- 尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED；
- VARCHAR的长度只分配真正需要的空间；
- 对于某些文本字段，比如"省份"或者"性别"，使用枚举或整数代替字符串类型；在MySQL中， ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多
- 尽量使用TIMESTAMP而非DATETIME；
- 单表不要有太多字段，建议在20以内；
- 尽可能使用 not null 定义字段，null 占用4字节空间，这样在将来执行查询的时候，数据库不用去比较NULL值。
- 用整型来存IP。
- 尽量少用 text 类型，非用不可时最好考虑拆表。
SQL语句及索引（重要）
注：索引并不是越多越好，要根据查询有针对性的创建
索引创建和使用原则
-
单表查询：哪个列作查询条件，就在该列创建索引
-
多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段
-
不要对索引列进行任何操作（计算、函数、类型转换）
-
索引列中不要使用 !=，<> 非等于
-
字符字段只建前缀索引，最好不要做主键；
-
尽量不用UNIQUE，由程序保证约束
-
不用外键，由程序保证约束
-
索引列不要为空，且不要使用 is null 或 is not null 判断
-
索引字段是字符串类型，查询条件的值要加''单引号，避免底层类型自动转换
使用EXPLAIN分析SQL
select_type：查询类型
- SIMPLE 简单查询
- PRIMARY 最外层查询
- UNION union后续查询
- SUBQUERY 子查询
type：查询数据时采用的方式
- ALL 全表**（性能最差）**
- index 基于索引的全表
- range 范围 （< > in）
- ref 非唯一索引单值查询
- const 使用主键或者唯一索引等值查询
possible_keys：可能用到的索引
key：真正用到的索引
rows：预估扫描多少行记录
key_len：使用了索引的字节数
Extra：额外信息
- Using where 索引回表
- Using index 索引直接满足条件
- Using filesort 需要排序
- Using temprorary 使用到临时表
重点关注的是type，最直观的反映出SQL的性能
SQL语句尽可能简单
一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库。
对于连续数值，使用BETWEEN而不是IN
SQL 语句中 IN 包含的值不应过多
SELECT 语句必须指明字段名称
SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；减少了使用覆盖索引的可能性。
当只需要一条数据的时候，使用 limit 1
limit 相当于截断查询。
例如：对于select * from user limit 1; 虽然进行了全表扫描，但是limit截断了全表扫描，从0开始取了1条数据。
排序字段加索引
如果限制条件中其他字段没有索引，尽量少用or
尽量用 union all 代替 union
union和union all的差别就在于union会对数据做一个distinct的动作，而这个distanct动作的速度则取决于现有数据的数量，数量越大则时间也越慢。而对于几个数据集，要确保数据集之间的数据互相不重复，基本是O(n)的算法复杂度。
区分 in 和 exists、not in 和 not exists
如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
使用合理的分页方式以提高分页的效率
避免使用 % 前缀模糊查询
例如：like '%name'或者like '%name%'，这种查询会导致索引失效而进行全表扫描。但是可以使用like 'name%'，这种会使用到索引。
避免在 where 子句中对字段进行表达式操作
这种不会使用到索引：
select user_id,user_project from user_base where age*2=36;
可以改为：
select user_id,user_project from user_base where age=36/2;
任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
避免隐式类型转换
where 子句中出现的 column 字段要和数据库中的字段类型对应
必要时可以使用 force index 来强制查询走某个索引
使用联合索引时注意范围查询
对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
某些情况下，可以使用连接代替子查询
使用JOIN的优化
使用小表驱动大表，例如使用inner join时，优化器会选择小表作为驱动表
小表驱动大表，即小的数据集驱动大的数据集
#当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表
select * from A where id in (select id from B)
#当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)
主从
主从相对比较简单，从运维层面搭建好从库后，工程师要做的就是制定路由策略。
路由策略有如下两种：
读写分离模式，所有写操作和对实时性要求较高的by id查询走主库，剩下的都走从库，从库采用Round Robin模式。
链路隔离模式：写操作和核心操作对应的SQL走主库，耗时大、非核心操作的SQL走从库。
分库
分库策略需要根据业务场景制定，最常见的有两种：按照年月分库和按照角色分库。
按照角色分库，最经典的就是淘宝基于订单的买家库和卖家库。
