MySQL索引基本操作及说明

  • 1、索引基本操作
  • 2、什么时候创建索引?
  • 2.1 哪些情况需要创建索引?
  • 2.2 哪些情况不需要创建索引?
  • 3、分析sql语句之explain
  • 4、查询优化的原则


1、索引基本操作

查看索引： show index from user_auth
   创建索引： create unique index [索引名字] on [表名字] (字段)
   删除索引： drop index [索引名字] from [表名字]

2、什么时候创建索引?

2.1 哪些情况需要创建索引?

1、主键自动建立唯一索引
    2、频繁作为查询条件的字段应该创建索引
    3、查询中与【其它表关联的字段】，外键关系建立索引
    4、单键/组合索引的选择问题， 组合索引性价比更高
    5、查询中【排序的字段】，排序字段若通过索引去访问将大大提高排序速度
    6、查询中【统计或者分组字段】

2.2 哪些情况不需要创建索引?

1、表记录太少
	2、经常增删改的表或者字段
	3、Where条件里用不到的字段不创建索引
	4、过滤性不好(如性别 男 女)的不适合建索引

3、分析sql语句之explain

id：SQL语句中表的读取顺序
		id相同：执行顺序为 从上至下执行
		id不同：执行顺序为 id大的先执行
	select_type：查询操作类型
		主要是用于区别普通查询、联合查询、子查询等的复杂查询
	table：表的来源
    *type：访问类型 (重点)
    	是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：
    	--常见的顺序为 system > const > eq_ref > ref > range > index > all
    	一般来说，得保证查询 至少达到 range 级别，最好能达到 ref
    possible_key：可能用到的索引
    key_len：索引使用字节数
    	ken_len 越长，说明索引使用的越充分
    ref：显示被使用的索引的具体信息
    rows：被查询的行数 
   		rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！
   	Extra：额外重要信息
   		Using filesort：使用外部索引排序（未使用用户创建的索引）

4、查询优化的原则

一、单表查询优化
	1、全值匹配我最爱    建立复合索引 都建上
	2、最佳左前缀法则：即最左优先，在检索数据时从联合索引的最左边开始匹配。
	3、索引列上进行 (计算、函数、(自动 or 手动)类型转换), 索引失效
	4、范围之后全失效 (索引范围右边的字段 全失效) > <
	5、使用不等会失效 
	6、使用NULL值要小心   is not null   (使用负面的 如不等于 not null 都会失效)
	7、模糊查询加右边     like'%asdsad%'会因左边%失效
	8、字符串加单引号（类型转换失效）
=============================================================================
	8、覆盖索引多使用   
	10、尽量不用or查询
二、关联查询数据优化
	两个表关联查询 第一个表叫驱动表 第二个叫被驱动表 避免不了总有一个全表扫描
	一张表数据量大，一张表数据量小，把小表放在驱动表 （物理扫描行要少）
	
	1、保证被驱动表join字段已经被建立索引
	2、left join选择小表作为驱动表  大表作为被驱动表
	3、inner join mysql 自己会帮你把小结果集作为驱动表
	4、子查询尽量不要放在被驱动表，子查询为虚拟表，用不了索引
	5、能直接关联尽量直接关联，不要用子查询。
三、子查询优化
	尽量不要用not in 或者 not exists
四、分组查询优化
	无过滤，不索引
	顺序错，必排序
	方向反，必排序   要么升序 要么都降序