Mysql性能优化

前言

Mysql性能优化是一个综合性技术的，优化不是一蹴而就，一个缓慢而漫长的过程，优化为了更快速响应，更持续，高效的运行，一些数据量大的表，面对一些慢查询，如果积累到一定量遇到高并发请求后，轻则造成服务繁忙，重则导致应用不可用直接宕机，一般存在于数据量慢慢增加显现出来的情况越明显，就像一个即将被引爆的炸弹一样，所以在项目上线时要严格检查，确保Mysql能够最优运行，优化有风险，执行需谨慎。

优化风险

优化是一步步排查定位的，是复杂的，一般是生产环境中体现，可能涉及很多关联，也有可能是代码不规范，逻辑循环过多导致，优化本能就存在很大的风险，任何一个技术的解决，都存在一定的风险，只是怎么去把控这风险降到最低可接受范围，如果保持现状，后期可能影响更大，早发现早解决更能降低风险，稳定性，业务的可持久性是一个系统的重中之重，优化可能需要多人员参与配合协调，一同完成。

Mysql查询主要步骤

1、客户端向MySQL服务器发送一条查询请求；
2、服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入一下阶段；
3、服务器进行SQL解析、预处理、再由优化器生成对应的执行计划；
4、MySQL 根据执行计划，调用存储引擎的API来执行查询；
5、将结果返回给客户端，同时缓存查询结果。

SQL的执行顺序

SQL的生命周期

服务器与数据库建立连接
数据库进程拿到请求sql
解析并生成执行计划，执行
读取数据到内存，并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉连接，释放资源

主要优化内容

1.硬件优化
2.软件优化
3.SQL优化
4.架构优化
5.流程制度化

硬件优化

1.服务主机选型：CPU个数，内存大小，磁盘大小，网卡
3.操作系统选型：Linux,Centos,Ubuntu
4.主机架构的稳定性：多台主机的及中间件的协调架设
5.I/O调度策略
6.网络带宽

软件优化

1.Mysql版本
2.Mysql存储引擎类型
对mysql配置优化 （配置最大并发数my.im，调整缓存大小）
3.将数据保存在内存中，从内存中读取,降低磁盘操作innodb_buffer_pool_size
4.刚启动数据库，将磁盘数据缓存到内存中预热。
5.降低磁盘写入操作
6.避免双写入缓冲
7.提高磁盘读写速度
8.定时清除不需要的数据，定时进行碎片整理（MyISAM）

SQL优化

1、尽可能字段设置为NOT NULL,将减少查询比较NULL值

alter table device add `disabled` tinyint(1)  NOT NULL COMMENT '使用状态';

2、对于某些字段尽量用过枚举 例如（开，关）ENUM类型被当作数值类型来处理，比文本处理更快快。

alter table device add `close` tinyint(1) DEFAULT '0' DEFAULT COMMENT '使用状态 0-关，1-开';

3、添加索引index （4种：普通索引，主键索引，唯一索引unique，全文索引）
1.普通索引 index

alter table device add index cell_index ( `cell` )

2.主键索引 primary key

alter table device add primary key ( `id` )

3.唯一索引 unique

alter table device add unique ( `imei` )

5.全文索引 fulltext

alter table device add fulltext(deviceType)

6.联合索引

alter table device add index nion_index ( `cell`, `imei`, `deviceType` )

4、避免函数索引查询

SELECT * FROM device WHERE LENGTH(name) >= 8;

5、用IN来替换OR

SELECT * FROM device WHERE imei = 1001 OR imei = 1002 OR imei = 1003;
SELECT * FROM device WHERE imei in (1001,1002,1003);

6、使用连接JOIN 代替子查询

# 效率低
SELECT * FROM device WHERE cell in (SELECT cell FROM user WHERE	city='北京');
# 效率高
SELECT d.* FROM device d  LEFT JOIN user u ON d.cell = o.cell where u.city='北京';

7、使用联合UNION代替创建临时表

SELECT imei,userName FROM device UNION
SELECT cityName,code FROM city UNION
SELECT name,cell FROM user

8、LIKE关键词双开无法使用到索引中

# 索引失效
SELECT * FROM device WHERE name '%铭琦%'
# 索引生效
SELECT * FROM device WHERE name '铭琦%'

9、数据量大尽量限制查询量 LIMIT M，N

# 非限制全表数据
SELECT * FROM device WHERE 1;
# 限制10条数据
SELECT * FROM device WHERE 1 LIMIT 10;

10、避免条件字段和条件数据类型不一致

# 错误
SELECT * FROM device WHERE id='8';
# 正确
SELECT * FROM device WHERE id=8;

11、避免默认排序耗时：Mysql默认GROUP By field1，field2，field3的…字段进行排序；想要避免排序结果的消耗，可以指定ORDER BY NULL禁止排序。

SELECT name,count(*) FROM device GROUP BY name ORDER BY NULL;

12、避免随机查询记录

SELECT * FROM device WHERE 1=1 ORDER BY RAND() LIMIT 4;

13、避免不必要的ORDER BY 排序

SELECT * FROM device WHERE 1=1 ORDER BY id DESC;

14、函数索引会导致全表扫描

SELECT * FROM t1 WHERE device >= CEIL(RAND()*1000) LIMIT 8;

15、删除百万级大数据表时先删除索引，在删除对应数据，后再重新建索引
16、组合索引要遵循最左匹配原则 如（k1,k2,k3），相当于（k1），（k1,k2），（k1,k2,k3）
三个索引，这就是最左匹配原则。
17、查询少用“ * ”仅取所需字段。