MYSQL-7-数据库优化、索引

一、索引

1. 索引类型：BTREE、HASH

   普通索引

   唯一索引 unique index

   主键索引

   组合索引

   全文索引 fulltext index

2. 创建索引

    CREATE TABLE table_name[col_name data type]
    [unique|fulltext][index|key][index_name](col_name[length])[asc|desc]
   

   1. unique|fulltext为可选参数，分别表示唯一索引、全文索引
   2. index和key为同义词，两者作用相同，用来指定创建索引
   3. col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
   4. index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
   5. length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
   6. asc或desc指定升序或降序的索引值存储

3. 索引类型

   1. 普通索引

       -- 创建
       alter table jobs add index index_salary_max(salary_max);
      
       create index index_salary_max on jobs(salary_max);
      
       create table table_name(
           ...
           index index_name (title(length))
       );
      
       -- 删除
       alter table jobs drop index index_salary_max;
      

   2. 唯一索引

       -- 创建
       alter table jobs add unique(id);
      
       create unique index unique_id on jobs(id);
      

   3. 主键索引

       -- 创建
       create table student(
           id int not null auto_increment,
           sname varchar(12) not null,
           primary key(id)
       );
      

   4. 组合索引

      只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，遵循最左前缀原则

       alter table student add sage_sdept(sage,sdept);
      

   5. 全文索引

      MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。

       select * from jobs
       where match(com_name) against('上海网络科技有限公司');
      

4. 不用索引

   explain … 查看语句查找方式

   1. != 会引起全表扫描
   2. order by 会引起全表扫描
   3. null 会引起全表扫描（可以 where num=0）
   4. or 连接会引起全表扫描（可以 select…union select…）
   5. like模糊查询 会引起全表扫描
   6. in 和 not in 会引起全表扫描（可以between…and…）
   7. where 子句使用参数会引起全表扫描 where num=@num
   8. where 子句对字段进行表达式操作会引起全表扫描where num/2=100
   9. where 子句对字段进行函数操作会引起全表扫描where substring(num)
   10. where 的=左侧不要运算，否则无法正确使用索引
   11. 当复合索引时，多列索引采用最左原则,且不跳过索引中的列
   12. exists代替in（看情况）
   13. 索引提高了select效率，但也降低了insert和update效率
   14. • 减少
   15. 适当用表变量替代临时表
   16. 减少游标，游标效率差
   17. 一旦估计检查的行数“太多”，范围查找优化将不会被使用。
   18. 索引列不应该作为表达式的一部分，即也不能在索引列上使用函数
   19. 尽量使用高选择性索引