参考 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分析