MySQL数据库进阶--性能优化

MySQL进阶

索引

定义：索引是帮助Mysql高效获取数据的数据结构（有序）

索引的使用规则

最左前缀法则：是指查询从索引的最左列开始，并且不跳过索引中的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）如果索引了多列（联合索引），要遵守最左前缀法则

范围查询：联合索引中，出现范围查询（>,<)，范围查询右侧的索引失效，可以使用>=,<=规避

索引列运算：不能在索引列上进行运算操作（函数等），否则索引失效

字符串不加引号：使用字符串类型字段时，不加引号，索引将失效

模糊查询：如果仅仅是尾部模糊匹配（like 'xxx%'），索引不会失效；如果是头部模糊匹配(like '%xxx')，索引将失效

or连接的条件：用or分割开的条件，如果or前的字段有索引，后面的字段没有索引，则全部索引都不会用到

数据分布影响：如果Mysql评估使用索引比全表更慢，则不使用索引 （Mysql：快夸夸我！）

create index 索引名 on 表名(字段1,字段2...);  #创建索引
show index from 表名;  #查看索引
drop index 索引名 on 表名;  #删除索引

sql提示：优化数据库的一个重要手段，是在sql语句中加入一些人为的提示达到优化操作的目的

• use index:建议mysql使用这个索引

• ignore index:忽略这个索引

• force index:强制mysql使用这个索引

explain select *from 表名 use/ignore/force index(索引名) where...

覆盖索引：尽量使用覆盖索引，减少select *（容易导致回表查询，除非创建了包含所有字段的联合索引）；

using index condition:查找使用了索引，但需要回表查询数据

using where;using index:查找使用了索引，但需要的数据在索引列都能找到，所以不需要回表查询数据

前缀索引：当字段类型为字符串(varchar,text等)时，有时需要索引很长的字符串，查询时会浪费大量的磁盘IO，影响查询效率，此时可以截取字符串的一部分前缀建立索引

前缀长度可以根据索引的选择性（不重复的索引值和数据表的记录总数的比值）来决定，索引选择性越高则查询效率越高，唯一索引的选择性是1（是最好的性能）

create index 索引名 on 表名(column(n))  #创建前缀索引
select count(distinct substring(字段名,开始位置，截取个数))/count(*) from 表名;  #选择性计算 

单列索引与联合索引：单列索引指一个索引只包含单个列，联合索引指一个索引包含多个列

在业务场景中，如果存在多个查询条件，对于查询字段建立索引时，建议建立联合索引，查得多的字段放前面（最左前缀法则）

索引设计原则

综上，索引设计如下：

1. 对于数据量较大，且查询毕竟频繁的表建立索引
2. 对于常作为where，order by，group by操作的字段建立索引
3. 尽量选择区分度高的列作为索引（如身份证号），尽量建立唯一索引
4. 如果字符串类型的字段长度较长，可根据选择性建立前缀索引
5. 尽量使用联合索引，减少单列索引，因为联合索引在查询时很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 要控制索引的数量，不是越多越好，数量太多维护索引结构的代价越大，影响增删改的效率
7. 如果索引列不能存储null值，则使用not null约束它，当优化器知道每列是否包含null值时，可以更好地确定哪个索引最有效地用于查询

sql优化

insert优化

• 批量插入

insert into 表名 values(),(),()

• 手动提交事务：提高性能，否则每执行一次sql都会自动提交

start transaction
insert into 表名 values(),(),();
insert into 表名 values(),(),();
insert into 表名 values(),(),();
commit;

• 主键顺序插入

1 2 4 7 9

• **大批量插入数据：**使用insert语句性能较低，可以使用Mysql提供的load指令进行插入

mysql——local-infile -u root -p  #客户端连接数据库时，加上参数local-infile
set global local_infile=1;  #设置全局参数local——infile为1，开启从本地加载文件导入数据的开关
load data local infile '/文件路径' into table 表名 fields terminated by ',' lines teminated by '\n';  #执行load指令，加载到表结构中 字段间以','分隔,行数据以'\n'分隔

主键优化

• 满足业务需求的情况下，尽量降低主键的长度，太长导致检索时会耗费大量磁盘IO

• 插入数据时，选择顺序插入，选择AUTO_INCREMENT自增主键，乱序插入可能会出现页分裂现象

• 尽量不要使用UUID做主键或者其它自然主键，如身份证号

• 业务操作时避免对主键的修改

order by优化

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结构的排序都叫filesort排序

Using index:通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高

explain select id,age,phone from 表明 order by 字段1,字段2;  #查看mysql如何执行slect信息

• 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则

• 尽量使用覆盖索引

• 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(asc/desc)，需要避免filesort

• 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（默认256k)

group by优化

分组操作时，可以通过索引提高效率，需要注意最左前缀法则

limit优化

分页查询在数据量较大时，Mysql需要排序前面的记录，然后仅仅返回你查询的记录，查询效率太差，尤其是后面几页，此时可以创建覆盖索引加子查询形式进行优化

explain select *from 表1,(select id from 表1 order by id limit x,y) 表2 where 表1.id=表2.id

count优化

MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count（*）时会直接返回这个数，效率很高；

InnoDB引擎执行count（*）时，需要把数据一行一行从引擎里面读出来，然后累积计数

优化：自己计数

• count(主键):InnoDB遍历整张表，把每行的主键id值取出来，返回给服务层，服务层拿到主键后直接按行进行累加

• count(字段):InnoDB遍历整张表，把每行的字段值取出来，返回给服务层，服务处进行判断是否为null，非null的计数累加（如有not null约束则不用进行判断）

• count(1):InnoDB遍历整张表，但不取值，服务层对于返回的每一行放一个数字”1“进去，直接按行进行累加

• count(*):InnoDB不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

update优化

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

有索引是行锁，没索引是表锁