SQL优化

show databases;
use mysql;
create database if not exists test_linux charset utf8mb4;
show grants for ‘xuhui’@‘%’;
use test_linux;
create table user(
id int primary key auto_increment comment ‘主键id’,
name varchar(10) comment ‘姓名’,
phone varchar(20) not null unique comment ‘电话’,
perfessional varchar(20) comment ‘专业’ ,
age int unsigned comment ‘年龄’,
status varchar(5) comment ‘状态’,
email varchar(20) comment ‘邮箱’
)comment ‘用户表’;
show tables;
desc user;
#name字段可能重复为其创建索引
create index name_index on user(name);
#phone非空且唯一，为其建立唯一索引
create unique index phone_index on user(phone);
#为perfessional,age,status创建联合索引
create index per_age_sta_unindex on user(perfessional,age,status);
#查看索引
show index from user;
#删除索引
drop index per_age_sta_unindex on user;
#sql性能分析:show [global|session] status 查看服务器状态信息，session表示查看当前用户，global表示查看全局
#show global status like ‘Com_______’;（7个下划线）查看使用频次
#慢查询日志：将记录所有执行时间超过指定参数（long_query_time,单位默认为秒）的所有sql语句日志，默认没有开启，在/etc/my.cnf中配置
#慢查询日志输出文件保存在 /var/lib/mysql/主机名-slow.log文件中
#profile详情，show variables like ‘have+profling’;查看是否支持profile set profiling=1,开启profile
#show profiles:查看当前已执行sql的耗时情况
#explain执行计划：语法：在任意的select语句前加 explain 关键字，获取mysql如何执行select,也可以加desc
#explain执行计划各字段含义
/*
id:select查询序列号，表示执行顺序，id相同从上到下依次执行，id不同，值越大，优先级越高，越先执行
selec-type:查询类型
type:表示连接类型，性能由好到差的连接类型NULL,system,const,eq_ref,ref,range,index,all，使用唯一索引会出现const,使用非唯一索引会出现ref
/
#索引使用，验证索引效率
#索引使用原则：
/
最左前缀法则（适用于联合索引）：指查询时从索引最左列开始，并且不跳过中间的列，若跳过，则该列后面的索引会失效
范围查询：使用<,>时，其右侧的索引将失效(可使用<=,>=规避)
索引列运算：不要在索引列上进行运算操作，否则索引将失效
字符串不加单引号，索引将失效
模糊匹配：若是尾部模糊，like ‘XX%’,索引不会失效，若是头部模糊，like ‘%XX’，索引将失效
or连接的条件:如果or的前面有索引，后面的列没有索引，则涉及的索引都不会使用
数据分布影响：如果mysql评估使用索引比全表搜索更慢，则不再使用索引
sql提示：优化数据库的重要手段，即在sql语句中加入认为提示达到优化操作的目的，如一个字段涉及多个索引，sql中可指定使用哪个索引
（use index(使用),ignore index（忽略）,force index（强制使用））
explain select * from user use index(phone_index) where phone=‘1234567890’;
explain select * from user ignore index(phone_index) where phone=‘1234567890’;
explain select * from user force index(phone_index) where phone=‘1234567890’;
覆盖索引与回表查询
前缀索引:当字符串的长度很长时，可以将字符串的前缀作为索引：语法：create index 索引名 on 表名（字段名（n））,n表示取几位前缀，可减少索引长度，提高索引效率
/
#使用联合索引
explain select * from user where perfessional=‘hsgd’ and age=18 and status=‘1’;
#与顺序无关
explain select * from user where age=18 and status=‘1’ and perfessional=‘hsgd’;
#将不使用联合索引
explain select * from user where age=18 and status=‘1’;
#插入数据优化：批量插入，手动事务提交，主键顺序插入，大批量插入使用load指令
#主键优化：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，称为索引组织表，主键乱序的情况下，会导致页分裂，降低效率
#order by优化：
/
Using filesort:通过全表扫描或者表的索引，读取满足条件的数据行，然后在缓冲区中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort
Using index:通过有序索引直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率高
例：create index idx_age_pho on user(age,phone);建立age_pho索引
explain select id,age,phone from user order by age,phone;此sql将使用using index
explain select id,age,phone from user order by phone,age;由于索引创建age在前，排序时phone在前，违背了最左前缀原则，此sql将出现Using filesort;Using index
explain select id,age,phone from user order by age asc,phone desc;age升序，phone降序将出现Using filesort,因为索引的默认排序方式为升序
create index idx_user_age_pho on user(age asc,phone desc);创建索引时可指定排序方式
explain select id,age,phone from user order by age asc,phone desc;此时将使用Using index
B+树的叶子节点是双向链表
*/