mysql优化

一、存储引擎
对于MYSQL而言，它提供不同种存储引擎，我们可以根据对数据处理选择不同的存储引擎，从而更好的优化我们MYSQL数据库性能。
二、MYSQL存储引擎：
MyISAM(默认):不支持事物、不支持外键，意味有事物需求表不能采用MyISAM存储引擎。
MyISAM(锁)：MyISAM采用表锁(读锁、写锁)。
读锁指在并发条件下，它支持对同一个表的读操作，但是阻塞对同一个表写操作。
写锁指在并发条件下，对一个表写操作时，阻塞其它进程对表的读操作和写操作。
MyISAM适用场景：支持物事物需求表，并且查询和插入数据效率高。一般可以用作系统日志收集、管理邮件。
InnoDB:
InnoDB：支持事物、支持外键。
InnoDB(锁)：MyISAM采用是行锁(共享锁、排它锁、意向共享锁、意向排它锁)。
共享锁指允许一个进程读取数据集时，阻塞其它进程去获取相同数据集。
排它锁指允许一个进程更新数据集时，阻塞其它进程获取该数据集上面的共享锁和排它锁。
意向共享锁指事物在该数据集上面加意向共享锁时，必须获取该数据集的共享锁。
意向排它锁指物在该数据集上面加意向排它锁时，必须获取该数据集的排它锁。
对于UPDATE、DELETE、INSERT语句，InnoDB会自动加排它锁。
InnoDB使用场景：适合处理多并发请求；MYSQL支持外键只有InnoDB存储引擎，这样可以做到关系型数据库；在数据查询和写入肯定不如MyISAM效率高。
MEMORY：存储介质为内存，当MYSQL守护进程崩溃，会丢失数据，所以适用于临时表；MEMORY数据表只支持固定长度(CHAR等字段类型),所以只能存储小数据。
三、MYSQL索引(BTREE索引、HASH索引)
BTREE索引:说白了就是一棵平衡二叉树(右子树>根>左子树)，它每次查找都是从顶层节点一直往下找，检索相当麻烦，查找速度远不如HASH，但由于HASH缺陷，所以BTREE索引是用作最多索引。
HASH索引:通过把多个数据索引组织起来，求取HASH值再去生成HASH表，用于形成HASH值于数据之间关联，在查询数据时，可以通过HASH值查找数据。因此在查询数据时，查询某条数据是非常快，但是对于范围查询比较慢了，对于GROUP BY、ORDER BY语句也比较慢，因为GROUP BY、ORDER BY语句用的是真实数据而并非HASH值，所以HASH所以并没有作用。因此HASH索引只满足=、IN、<>。
位图索引：适用于重复数据较多字段。
四、索引的优化  
1、对于列中出现NULL值较多，尽量避免使用索引，否则此列索引失效，包括btree、hash索引
2、在where子句中使用的列，尽量添加索引。
3、对于where、order by多列，应该创建复合索引。
4、对于‘like’，‘%’，‘-’开头的不会使用索引。
5、尽量不要在where子句中进行运算where num/2=100。
6、尽量避免使用关联join查询，关联查询一方面在关联是它会比较关联字段，尤其是varchar类型，它会一个一个字符比较两个字符串是否相等，所以是比较耗费性能，再一个关联查询完全是跳过索引。
五、Sql优化
1、应该尽量避免where子句使用！=、OR、IS NULL、NULL、IN、NOT IN 、LIKE等这些都会导致全表扫描。
(1)select id from t where num != ‘1’
(2)select id from t where num = ‘1’ or num = ’2’
代替：
select id from t where num == ‘1’
union all
select id from t where num != ‘2’
(3)select id from t where num is null 或者 select id from t where num is not null
代替：
select id from t where num = 1
select id from t where num = 0
(4)select id from t where num like ‘%c%’
(5)select id from t where num in(1,2,3)
代替：
select id from t where num bewtten 1 and 3
select id from t where num = 1 
union all 
select id from t where num = 2 
union all 
select id from t where num = 3
2、尽量避免使用select *,返回无用字段，降低查询效率。
3、select查询子句中不要尽量用exists 代替in。
4、尽量避免使用where 1=1这样语句，导致全表扫描。
5、索引并不是越多越好，索引是种数据结构，在做更新操作时会使索引的效率很低。
6、记住数字类型性能远高于字符类型，无论是查询还是连接或是排序，都比字符类型好。
7、尽量使用varchar/nvarchar 代替char/nchar，节省存储空间，查询较小空间数据比查询较大空间效率高。
六、表优化
1、垂直分割：简化表结构的复杂度，将常用作查询字段，提取出来用作查询的表结构，将常做更新操作的字段提取出来用作更新表结构，在做两张表同时操作时，可以将操作放在一个事务里。
另外在查询是可能需要关联，关联是比较耗费系统性能的，但是我们可以两次查询操作。
2、水平分割：常见就是分库分表，读写分离，主要解决数据量大，查询较慢问题，减少索引维护时间时间。
3、表结构优化：尽量字段类型添加NOT NULL约束，在MYSQL中NULL是会影响索引结构的，是索引变得复杂，
另外NULL值也会占有一定的磁盘空间，所以尽量避免使用NULL值，可以用0实现代替。数值类型字段比较要比字符串类型比较效率高的多。
七、Mysql分页优化
假设有一个千万量级的表，取1到10条数据；
select * from table limit 0,10;
select * from table limit 1000,10;
这两条语句查询时间应该在毫秒级完成；
select * from table limit 3000000,10;
你可能没想到，这条语句执行之间在5s左右；
第一种简单粗暴，就是不允许查看这么靠后的数据，比如百度就是这样的
第二种方法，在查询下一页时把上一页的行id作为参数传递给客户端程序，然后sql就改成了
select * from table where id>3000000 limit 10;
select * from table where id>100*10 limit 10;
最后第三种方法：延迟关联
玄机就处在这个 * 里面，这个表除了id主键肯定还有其他字段  比如 name  age  之类的，因为select  *  所以mysql在沿着id主键走的时候要回行拿数据，走一下拿一下数据；
select id from table limit 3000000,10;
你会发现时间缩短了一半；然后我们在拿id分别去取10条数据就行了；
select table.* from table inner join ( select id from table limit 3000000,10 ) as tmp on tmp.id=table.id;