sql优化

sql优化

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件(where)、排序(orderby)、分组(group by)操作的字段建立索引。尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。56要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

insert优化

insert插入多条数据时将多条插入数据语句整合为1个insert语句，且数据量最好为500行，超过可分为多个insert语句，多个insert语句插入时最好手动开关事务，以免事务自动频繁开关降低性能

如果一次性需要插入几万大批量数据，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入

数据插入时主键最好为有序的，顺序插入性能比乱序性能好，乱序插入会导致页分裂，B+树的每个叶子节点的页上面保存有多条数据，当乱序时会根据当前插入的数据的主键的大小去比较然后找到所在顺序位置，将顺序位置页数据分割并与当前乱序的数据一起保存到新的页上，然后重新设置指针，而顺序插入是直接保存到最后页

主键优化

满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTOINCREMENT自增主键。尽量不要使用UUID做主键或者是其他自然主键，如身份证，因为主键乱序会导致页分裂

order by 优化

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引。
多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort buffer size(默认256k)。

group by 优化

根据分组字段建立合适的索引，多字段分组时，也遵循最左前缀法则。

limit优化

select id from users order by id limit 2000000,10；

一个常见又非常头疼的问题就是limit 2000000,10，此时需要mysql排序前2000010记录，仅仅返回2000000-2000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:一般分页查询时，通过创建覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

select u1.* from users u1, (select id from users order by id limit 2000000,10) u2 where u1.id = u2.id

update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁