MySQL学习记录day8

#索引失效情况一：
#1.在索引列上进行运算操作，例如提取/相加等
#2.字符串类型未加引号
#3.头部模糊匹配索引失效 '%xx'失效 'xx%'不会失效
#4.用or分割开的条件，如果or一侧条件有索引而后一侧的条件没有，则所有的索引都会失效，只有当前后的条件都有索引时才不会失效
#5.如果MySQL评估使用索引比不使用更慢，则索引失效，例如表中数据的分布

#SQL提示：在SQL语句中加入认为提示来达到优化操作的目的(例如指定使用哪个索引)
#use index:只是建议。不一定真采取
# explain select * from tb_user use index(idx_user_pro) where  profession = '软件工程';
#ignore index：忽略某索引
# explain select * from tb_user ignore index(idx_user_pro) where  profession = '软件工程';
#force index：强制使用某索引
# explain select * from tb_user force index(idx_user_pro) where  profession = '软件工程'

#覆盖索引：不需要回表查询。查询使用了索引且需要返回的字段在该索引中可以全部找到。可以减少使用select *
#Extra中，null和using index condition是查找使用了索引，但是需要回表查询数据
#using where,using index是查找使用了索引，但是需要的数据在索引列中都能找到，所以不需要回表查询数据
#非主键的索引是(辅助)二级索引，叶子节点是id，所以如果select id+索引字段也不需要回表查询
#根据id查询不需要回表，id是主键，叶子就是这一行的值
#先扫描辅助索引，再扫描聚集索引就是回表查询
#优化语句优先考虑不需要回表

#前缀索引：当字段类型为字符串(varchar,text)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO
#因此可以之将字符串的一部分前缀建立索引
# create index idx_xxx on table_name(column(n))指定前n个字符建立索引
#前缀长度：根据索引的选择性来决定，指不重复的索引值和数据表记录总数的比值
# select count(*) from tb_user;
# select count(distinct email) from tb_user;
# select count(distinct substring(email,1,10))/count(*) from tb_user;

#单列索引与联合索引
#单列索引：一个索引只包含单个列
#联合索引：一个索引包含多个列
#多个查询字段时使用联合索引

#索引设计原则：
#1.数据量大且查询比较频繁
#2.常作为查询条件(where)、排序(order by)和分组(group by)操作的字段建立索引
#3.尽量选择区分度高的列作为索引，区分度越高索引效率越高
#4.字符串类型的字段，字符长度较长，可以使用前缀索引
#5.尽量使用联合索引，减少单列索引
#6.控制索引的数量
#7.如果索引列不能存储null值，建表时使用not null约束它。

#其他sql的优化
#插入数据
#insert优化：
# 1.批量插入代替单个插入
# 2.手动提交事务；start transaction；commit
# 3.主键顺序插入
#如果一次性需要插入大量数据，此时可以使用load指令进行插入，每个字段之间使用符号分隔
#mysql --local-infile -u root -p
#set gloabal local_infile = 1;
#load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

#主键优化
#innodb中，表数据是根据主键顺序组织存放的，这种存储方式的表成为索引组织表(IOT)
#主键如果是乱序插入，则可能导致页分裂，链表也会重新排序
#如果删除了某行数据，并不会直接删除这行数据，而是加上删除的标识。删除多个后innodb会进行页面合并

#主键设计原则
#尽量降低主键长度
#插入数据时选择顺序插入，使用auto_increment自增主键
#尽量不要使用uuid或其他自然主键，如身份证号
#尽量不要修改主键

#order by优化
#1.using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序的都叫filesort排序
#2.using index:通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高
#意思就是为排序所使用的字段创建索引后就是using index，但如果顺序打乱的话还是会降低性能出现using filesort
#联合索引时同升同降都是只会使用到using index，但不同升降序会出现using filesort
#覆盖索引是前提，如果不是覆盖索引那么都是using filesort
#不可避免出现filesort时，在数据量大的情况下可以适当增大排序缓冲区大小sort_buffer_size

#group by优化
#为分组所使用的字段创建索引后也是uing index，但如果不符合最左前缀法则则会有using temporary
#group by只要求联合索引的字段被使用的顺序不变则为using index，例如where pro = '软件' group by age = 11;

#limit优化
#覆盖索引+子查询

#count优化
#count某个字段统计的是多少条记录不为null，主键不可能为null
#count(数字)=count(not null)
#count(*)不取值，直接进行累加
#尽量使用count(*)

#update优化
#当update时如果后面的条件字段没有索引那么会造成表锁，有索引则为行锁