【高并发高性能高可用之海量数据MySQL实战-26】-MySQL索引及SQL语句优化

1、索引优化

1. 表记录很少不需创建索引 （索引是要有存储的开销）.

2. 一个表的索引个数不能过多。

（1） 空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。

（2） 时间：

更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。

所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。

3. 频繁更新的字段不建议作为索引。

频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。

4. 区分度低的字段，不建议建索引。（仅供参考）

比如性别，男，女；比如状态。区分度太低时，会导致扫描行数过多，再加上回表查询的消耗。  如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。

姓名，手机号就非常适合建索引。

5. 在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段。

主键索引树一个页节点是16K，主键字段越长，一个页可存储的数据量就会越少，比较臃肿，查  询时尤其是区间查询时磁盘IO次数会增多。辅助索引树上叶子节点存储的数据是主键值，主键值越  长，一个页可存储的数据量就会越少，查询时磁盘IO次数会增多，查询效率会降低。

6. 不建议用无序的值作为索引。

例如身份证、UUID

更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。

7. 尽量创建组合索引，而不是单列索引。

优点：

（1）1个组合索引等同于多个索引效果，节省空间。

（2） 可以使用覆盖索引

创建原则：组合索引应该把把频繁的列，区分度高的值放在前面。频繁使用代表索引的利用率高，  区分度高代表筛选粒度大，可以尽量缩小筛选范围。

2、字段数据类型

尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED VARCHAR的长度只分配真正需要的空间

使用枚举或整数代替字符串类型

单表不要有太多字段，建议在20以内

避免使用NULL字段，很难查询优化且占用额外索引空间用整型来存IP

3、LIMIT优化

如果预计SELECT语句的查询结果是一条，最好使用 LIMIT 1，可以停止全表扫描。

SELECT * FROM user WHERE username=''; -- username没有建立唯一索引2
SELECT * FROM user WHERE username='' LIMIT 1;

处理分页会使用到LIMIT ，当翻页到非常靠后的页面的时候，偏移量会非常大，这时LIMIT的效率会非常差。 LIMIT OFFSET , SIZE；

LIMIT的优化问题，其实是OFFSET 的问题，它会导致MySql扫描大量不需要的行然后再抛弃掉。 

解决方案：单表分页时，使用自增主键排序之后，先使用where条件 id > offset值，limit后面只写rows 

select * from (select * from tuser2 where id > 1000000 ORDER BY id) t limit 0, 20

4、查询优化

小表驱动大表，两个表做关联查询时建议使用join ，因为使用left join 的话，第一张表是必须全扫描的。

避免全表扫描，mysql在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描。在查询的时 候，如果对索引使用不等于的操作将会导致索引失效，进行全表扫描

避免mysql放弃索引查询，如果mysql估计使用全表扫描要比使用索引快，则不使用索引。（最典  型的场景就是数据量少的时候）

JOIN两张表的关联字段最好都建立索引，而且最好字段类型是一样的。 

SELECT * FROM orders o LEFT JOIN user u on o.user_id = u.id 2
orders表中的user_id和user表中的id，类型要一致

WHERE条件中尽量不要使用not in语句（建议使用not exists）

合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况。

5、阿里巴巴开发规约

不得使用外键与级联，一切外键概念必须在应用层解决

禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

数据订正时，要先 select，避免出现误删除，确认无误才能执行更新语句只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定

SQL 语句中表的别名前加 as，并且以 t1、t2、t3、...的顺序依次命名

in 后边的集合元素数量，控制在 1000 个之内使用 ISNULL()来判断是否为 NULL 值

NULL< >NULL 的返回结果是 NULL，而不是 false NULL=NULL 的返回结果是 NULL，而不是 true NULL< >1 的返回结果是 NULL，而不是 true

不要使用 count(列名)或 count(常量)来替代 count(*)

当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL count(distinct col) 计算该列除 NULL 之外的不重复行数

超过三个表禁止 join

 

如果您觉得文章好看，欢迎点赞收藏加关注，一连三击呀，您的肯定是我持续输出的动力，感谢！！☺☻