mysql数据库查询优化

1. 尽量避免 select * 查询

        select * 的查询需要经过MySQL的优化器和分析器去将 * 替换为数据库表对应的所有字段名,*查询需要多走一步MySQL的优化分析

2.按需查询

        需要查询什么字段就只查询什么字段,无用字段的查询会增加网络传输过程的负担

3.尽量索引查询

-- 查看索引
  show index from table_name

-- sql添加索引
-- 添加普通索引,普通索引性能最低
  CREATE INDEX index_name ON table_name(column_name) include(score) -- 1
  alter table table_name add index index_name (column_name)         -- 2
-- 添加非空索引
  CREATE UNIQUE INDEX index_name ON table_name (column_name)        -- 1
  alter table table_name add unique (column_name)                   -- 2
-- 添加主键索引
  CREATE PRIMARY KEY INDEX index_name ON table_name (column_name)   -- 1
  alter table table_name add primary key (column_name)              -- 2


-- 删除索引
  drop index index_name on table_name -- 1
  alter table table_name drop index index_name -- 2
  alter table table_name drop primary key  -- 3

-- 查看SQL性能
  explain + SQL语句 -- 使用索引 type = ref ,不使用索引type= all
  
-- 对于关键字字段名,可以加反单引号``转义

3.减少查询量优化分页查询

-- 全表查询
select name from students limit 100,120           
-- 只查询where筛选后的数据,性能更好,查询更快 
select name from students where id>100,limit 120  

4.join查询要遵循小表驱动大表

      每个join都有一个单独的缓冲区即Join Buffer(连接缓冲区),它是一个的临时的，当我们需要比较两个或多个表的数据进行Join操作时，Join Buffer可以帮助MySQL临时存储结果，以减少磁盘读取和CPU负担，提高查询效率。

     进行join操作时,BNI算法会将驱动表数据(join前的表数据)加载到Join Buffer,然后再与非驱动表(join后的表)数据进行比对,如果驱动表数据过大,Join Buffer无法一次性装载数据,会分批次驱动表数据再跟非驱动表数据一个个比对(例如,假如表1有6条数据,Join Buffer最大可加载3条,则会先将表1的1-3条数据跟表2一个个比对,再清空Join Buffer,将4-6条数据加载如Join Buffer,跟表2数据一个个比对)

 select * from  table1 (数据量小的表)  left join table2 (数据量大的表) 
on table1.id = table2.id