MySQL性能优化
- 建表原则
- 定长和边长分离：定长的表每一行的数据长度是固定的，查询的时候是可以提升查询速度的，如id、char(4)、time都是定长字段。
- 常用字段和不常用字段分离：
- 必要时候增加冗余字段提高来提高查询速度：例如需关联统计数据的时候，可以在主表增加统计字段，每次插入数据更新统计值，这样就无需关联其他表进行统计，牺牲空间换时间
- 列类型选择
- 整型（int、tinyint） > date、time > enum,char > varchar > blob、text
- 整型：无字符集差异和校对集差异
- date、time：考虑时区时不方便查询
- enum：内部用整型来存储，但是与char联查时，内部需要经历串与值的比较
- char：定长，需考虑字符集和校对集
- varchar：不定长，需考虑字符集和校对集，速度慢
- text/blob：无法使用内存临时表（排序等操作只能在磁盘上进行）
- 字段长度够用就行，不要浪费
- 列的选择避免使用NULL
- 整型（int、tinyint） > date、time > enum,char > varchar > blob、text
- 索引优化
-
Hash索引:当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，但是innodb和myisam之所以没有采用它，是因为它存在着好多缺点：
- 因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询
-
每次都要全表扫描
-
由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作
- 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。
- 当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的。
- Btree索引:联合所用的使用规则符合最左原则
- 聚簇索引和非聚簇索引：
- 聚集索引。表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。
- 非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。
- 总结一下：聚集索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚集索引，则是密集索引，在数据页的上一级索引页它为每一个数据行存储一条索引记录。
- 索引覆盖：覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
- 在常用查询字段上加联合索引
- 避免重复索引
- 在能保证索引唯一情况下索引长度尽量小，可以使用短索引，例如：表名test，字段名称为a，要建前3个字符的索引：mysql>alter table test add index `a`(`a`(3));
- 索引列不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的，like '%xxx%'无法使用索引但是like ‘xxx%’可以使用索引
-
- 索引碎片和维护
- 在长期的数据更改过程中，索引和数据都将产生孔洞，形成碎片
- 我们可以通过nop操作（不会对数据产生影响的操作），来修改表，比如innodb引擎，可以alter table xxx engine innodb；
- optimize table xxx，也可以修复
- 修复表数据和结构是很耗资源的，不建议频繁使用
- sql语句优化
- 切分插入：将大数据分几次插入
- 切分查询：将复杂sql分解成几个简单的sql进行查询
- 尽量走索引
- 尽量用join连接替换子查询
- 在join中，尽量用小的结果驱动大的结果，例如：SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id WHERE B.NAME=’XXX’可以修改为：SELECT* from (select A.id,A.name from A wehre id >10) T1 left join B onT1.id=B.ref_id
- limit优化
- SELECT * FROM A ORDER BY ID LIMIT 100000，10；当数据量很大时很慢，可以改成：SELECT * FROM A WHERE ID BETWEEN 100000 AND 100010
- 尽量避免SELECT * 操作
- 通过UNION替换OR语句，例如：SELECT NAME FROM A WHERE ID<5 OR ID>10可以修改为：SELECT NAME FROM A WHERE ID<5 UNION SELECT NAME FROM A WHERE ID>10
- 用UNION ALL代替UNION，这样可以减少排序和筛选的时间
- 避免类型转换
- 避免列上进行函数运算
- 避免使用NOT IN和<>操作，NOT IN可以NOT EXISTS代替，id<>3则可以使用id>3 or id <3;如果NOT EXISTS是子查询，还可以尽量转化为外连接或者等值连接
- SELECT * FROM A WHERE A.ID NOT IN (SELECT ID FROM B)修改为：SELECT * FROM A LEFT JOIN B ON A.ID=B.ID WHERE B.ID IS NULL
- 对多表联合查询采用视图方式