MySQL学习系列之MySQL索引原理学习

 

参考 MySQL索引原理以及查询优化：https://www.cnblogs.com/bypp/p/7755307.html

一，索引类型

mysql 索引：

1，index                普通索引                 作用：加速查找

2，primary key      主键索引                作用：加速查找 + 唯一约束，不为空

3，unique               唯一索引                作用：加速查找 + 唯一约束

-------------------------------------------------------------------------------------------------------------

@mysql 存储引擎engine:

1,myisam

2,innodb   支持事务

3,memory

4,NDB      支持事务

5,Archive

-------------------------------------------------------------------------------------------------------------

二，索引创建

创建索引：

create  index  字段名  on  表名(字段名)    # 添加普通索引

create  unique 字段名  on  表名(字段名)    # 添加唯一索引 

alter   table  表名  add primary key(id);  # 添加主键索引，给id字段增加一个主键约束

create  table  字段名   on  表名(id,name); # 添加普通联合索引

删除索引：

drop  index  索引名  on 表名  #删除唯一索引与删除普通索引一样

alter table1 表名  drop  primary key;  # 删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

帮助查看索引：

help create index;

--------------------------------------------------------------------------------------------------------------

三，存储过程创建

创建存储过程：

#1. 准备表

create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录

 

********************************* 存储过程 *********************************************************************

******************************************************************************************************************

#3. 查看存储过程
show create procedure auto_insert1\G 

#4. 调用存储过程
call auto_insert1(); 

#5  搜索添加的字段一般加上搜索

#6 创建联合索引，再查询

#sql_no_cache 在查询的时候，不使用缓存（ SQL_NO_CACHE的真正作用是禁止缓存查询结果，
                                      
                                          但并不意味着cache不作为结果返回给query ）
#sql_cache意思是说，查询的时候使用缓存

create index index_id_name from s1(id,name);
select sql_no_cache * from s1 where id =1 and name ='张三';

--------------------------------------------------------------------------------------------

四，索引合并与合并索引

索引合并：

#索引合并：把多个单列索引合并使用,在  索引1  和 索引2 的基础上创建组合索引
#合并索引：在2个列上单独创建索引，如果查询语句使用到，叫合并索引

#使用场景：@1 组合索引的效率要高于索引合并

                      @2 如果是单条件查，那么还是用索引合并比较合理

create index index_name_email on s1(name,email);  #组合索引

组合索引可以命中：
select * from s1 where name='egon' ;
select * from s1 where name='egon' and email='adf';

索引合并可以命中：
select * from s1 where name='egon' ;
select * from s1 where email='adf';
select * from s1 where name='egon' and email='adf';

---------------------------------------------------------------------------------------------------

五，MySQL索引最优原则

MySQL索引最优原则：

#1.最左前缀匹配原则，必须按照从左到右的顺序匹配,非常重要的原则

#在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

create  index ix_name_email on s1(name,eamil);

#2.  = 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器
会帮你优化成索引可以识别的形式

#3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)

#区分度与索引长度的权衡

索引长度越低，索引在内存中占的长度越小，排序越快，然而区分度就越低。这样不利于查找。

索引长度越长，区分度就高，虽然利于查找了，但是索引在内存中占得空间就多了。

#查询title中从左边数第一个不重复的汉字（也就是一个字符）有多少行
select count(distinct left(title,1)) from item; 

#4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’

create_time = unix_timestamp(’2014-05-29’); #使用这个 

------------------------------------------------------------------------------------------------------

六，索引无法命中问题与解决

索引无法命中的情况需要注意：

#1，like '%xx'

#2, 使用函数：reverse()等

#3，or 特别：当 or 条件中有未建立索引的列才失效

#4，类型不一致：  如果列是字符串类型，传入条件是必须用引号引起来

#普通索引的不等于不会走索引

#5，!= 

select * from s1 where eamil !='alex';

特别：如果是主键，则还是会走索引

#6， > 

select * from tb1 where email > 'alex'

  特别的：如果是主键或索引是整数类型，则还是会走索引
  
#7，排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by
    select name from s1 order by email desc;

#当根据索引排序时候，select查询的字段如果不是索引，则不走索引
 select email from s1 order by email desc;
    
#特别的：如果对主键排序，则还是走索引：
  select * from tb1 order by nid desc;
  
- 组合索引最左前缀：

    如果组合索引为：(name,email)
    
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引
    
- count(1)或count(列)代替count(*)在mysql中没有差别了

- create index xxxx  on tb(title(19)) #text类型，必须制定长度

-------------------------------------------------------------------------------

七，建表索引原则

建表，索引优化：

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

--------------------------------------------------------------------------------

八，慢查询优化的基本步骤：

1，select SQL_NO_CACHE * from s1  来查询，查询的速度是否是真的很慢

2，where条件单表查，锁定最小返回记录表。
   这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，
   单表每个字段分别查询，看哪个字段的区分度最高
   
3，explain 查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询

4.order by limit 形式的sql语句让排序的表优先查
5.了解业务方使用场景
6.加索引时参照建索引的几大原则
7.观察结果，不符合预期继续从0分析