十分钟带你明白mysql的优化---索引

alter table 表名 add index idx_staffs_nameA(name,age,pos);//创建索引

这个索引的顺序是name---》age---》pos

这个时候例如我们书写sql语句的时候就需要遵循最左前缀原则，也就是在sql语句的where后边条件需要是name---》age---》pos

select * from 表名 where name = “a” and age = 1 and pos = ”c“

假设name条件是a代替 age条件是b代替 pos条件用c代替，那么where后边的条件顺序就必须是abc

如果顺序是acb这样的话只有a条件起作用，bc的时候就全都不起作用，这和索引的B+tree树的存储有关。

全文索引：要求只有在MyISAM引擎才能够使用，只能在varchar、char、text类型的字段上使用全文索引，类似于模糊查询
空间索引：空间索引是对空间数据类型的字段建立索引。

按照之前的开发经验，四种索引类型中常用的是单列索引和组合索引。

1.4存储引擎中的三种日志区别

1、undo_log日志，这个日志中存储的是在执行sql语句之前的数据，方便后续事务的回滚，例如在使用seata实现分布式的时候，就需要通过undo_log日志来通过逆向sql来完成分布式事务的回滚

2、redo_log日志，这个就是用来记录执行的sql语句、记录binlog日志文件位置记录commit标记，并完成对于磁盘文件中数据的刷新，避免因为buffer pool缓存池的存在造成的脏数据。

3、bin_log日志就是用来记录日志，不会用来事务的恢复，最大的作用就是用来对mysql中从机的监控，同步数据。

1.5 索引的基本语法

创建索引：

 CREATE INDEX idx_username ON mytable (username);
 idx_username是索引的名字，mytable是数据表格名字，username是列名。
 
 alter table mytable add （UNIQUE、FULLTEXT） index un_username(username);
 也可以直接通过修改表格结构，添加索引

在创建索引的时候，在index前边添加UNIQUE的时候，标志这是一个唯一索引，在index前边添加FULLTEXT的时候，标志这是一个全文索引，如果不添加关键字的画就是普通索引

查询表格存在的索引

show index from mytable 
mytable是表名

删除索引

drop index idx_username on mytable;
idx_username是索引名字，mytable是表名
 alter table mytable drop index un_username;

1.6创建索引需要注意的情况

主键自动建立唯一索引
频繁作为查询条件的字段应该建立索引
where里边用不到的字段不建立索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

1.7 那些情况不需要创建索引

表记录太少不需要创建
经常增删改的表格不建立索引（索引的创建是为了提高查询速度，而增删改，mysql不仅需要保存数据，还要保存索引文件）
如果数据表中某字段数据重复很多，也没有建立索引的必要

1.8 查看数据库表中索引的执行情况

结果这个字段的含义

1.8.1 id含义

id表示的是表格的加载顺序，或者说是执行顺序

id如果相同，那么其执行顺序就是从上到下依次执行
id如果不同，那么其id值越大，就优先执行

1.8.2 select_type含义

这个表示的查询类型

simple 简单的查询，查询中不包含子查询
primary 查询中包含子查询部分的时候，最外层查询被标记为primary
Subquery 在select或where中包含子查询
Derived 在from中如果子查询语句生成一个临时表，在后续查询的时候，继续参与查询的时候

1.8.3 type含义

这个是访问类型，显示的是查询使用了那种类型

从好到差依次是：

system > const > eq_ref > ref > range > index > ALL

system可以忽略的，好的sql至少能够达到range级别，最好到ref级别

const: 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。

eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配

explain select * from t1,t2 where t1.id = t2.id;

ref: 非唯一索引扫描，返回匹配某个单独值的所有行，但是返回的结果是多个

range： 检索的给定范围的行，常出现在between，< ,> ，in等查询，一般是范围查找，这种范围扫描比全表扫描好，因为这种查询开始于索引的某一点，结束于另一点，不用全表扫描。

index：index类型的话就是只遍历索引树，从索引中读取数据，而all是从硬盘中读取数据。

二.索引的性能优化

2.1不要对索引做以下处理

计算，如+、—、*、/、！=、< 、>等算术运算
不要对索引使用sql中的内置函数
手动或者自动的类型转换，例如a=”1“，本来是数字的，写成字符串

2.2索引不要放在范围查询的右边

比如复合索引a--》b--》c，当使用where a= "" and b >10 and c = " ",这个时候用到a和b，c用不到索引，因为在范围之后索引就是失效了

2.3覆盖索引

例如select name from student where name like ”%李四%“；//覆盖索引就是select 字段和后边的条件字段对应，这样可以避免select * from student where name like ”%李四%“这样写的时候导致的索引失效。

另外的一种解决方法就是

select * from student where name like ”李四%“这样也可以避免索引失效

2.4order by优化

当查询语句中出现order by的时候，如果没有使用索引排序的时候，就会把整个数据库表格中整体排序也就是fileout文件内排序，但是这样的性能在大数据和高并发的时候是很低的，这个时候就需要使用到order by的优化

fileout出现的情况举例

order by的不是索引字段
select * from 表名 order by grade asc这个时候是因为没有使用覆盖索引，导致索引失效出现fileout排序
同时使用asc desc升序和降序排列的时候，也会出现fileout排序

例如：select a ，b from 表名 a desc，b asc；

不遵循最左前缀原则的时候也会出现fileoout排序

解决办法：

使用主键索引排序
按照最左前缀原则，并且使用索引排序，多个字段排序的时候，保持排序方向一致
在SQL语句中使用force index （索引名字）
不在数据库中排序

2.5索引的使用场景

group by
order by
where

这三者之间需要注意的地方是其执行顺序，在书写查询语句的时候，这部分是离不开的，其书写顺序是where .....group by ...... having(这个是用来过滤分组后的结果的)......order by ..... limit

select sex AVG(height) avg_height from student_tb where age >15 group by sex having 
       avg_height >10 order by avg_height desc limit 0,10

2.6索引使用的注意事项

1、索引不会包含有NULL列

单一索引只要列中包含NLL值，就不回被包含在索引中，对于复合索引中只要有一列中含有NULL值，那么这一列对于此复合索引都是无效的

2、索引列排序

mysql查询只使用一个索引，因此如果where中使用了索引，那在order by就不会使用索引，因此数据库默认排序如果可以符合要求就不要使用索引了，尽量不要对多个字段进行排序，如果需要就通过建立复合索引。

3、索引要建立在唯一的字段上边

4、在join操作的时候（需要从多个表格取数据）。mysql只有主键和外键的数据类型相同的时候，索引才会生效，不然即使创建了索引也不会使用

三.索引优化

1、like语句的前导模糊查询不使用索引：

select * from doc where title like '%XX'；   --不能使用索引
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引

2、负向条件查询不能使用索引：

负向条件有：!=、<>、not in、not exists、not like 等

例如下面SQL语句：（假设status的取值为0、1、2、3、4）

select * from doc where status != 1 and status != 2;     --不能使用索引

select * from doc where status in (0,3,4);                      --优化为 in 查询，可以使用索引

3、范围条件右边的列不能使用索引（范围列可以用到索引）：

范围条件有：<、<=、>、>=、between等。

索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (emp_no 、title、from_date )，那么下面的 SQL 中 emp_no 可以用到索引，而title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

4、在索引列做任何操作（计算、函数、表达式）会导致索引失效而转向全表扫描：

select * from doc where YEAR(create_time) <= '2016';         -- 不能使用索引
select * from doc where create_time<= '2016-01-01';            -- 可以使用索引
select * from order where date < = CURDATE()；                 -- 不能使用索引
select * from order where date < = '2018-01-2412:00:00';     -- 可以使用索引
select id from t where substring(name,1,3)=’abc’             -- 不能使用索引
select id from t where name like ‘abc%’                    -- 可以使用索引
select id from t where num/2=100                          -- 不能使用索引
select id from t where num=100*2                            -- 可以使用索引

5、强制类型转换会导致全表扫描：

字符串类型不加单引号会导致索引失效，因为mysql会自己做类型转换,相当于在索引列上进行了操作。

如果 phone 字段是 varchar 类型，则下面的 SQL 不能命中索引，因为内部发生的类型转换。

select * from user where phone=13800001234;      -- 不能使用索引

select * from user where phone='13800001234';     -- 可以使用索引

6、is null, is not null 在无法使用索引，不过在mysql的高版本已经做了优化，允许使用索引

select id from t where num is null;     -- mysql低版本不能使用索引

select id from t where num=0;           -- 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询

7、使用组合索引时，要符合最左前缀原则：

组合索引的字段数不允许超过5个。如果在a,b,c三个字段上建立联合索引 index(a,b,c)，那么他会自动建立 a、(a,b)、(a,b,c) 三组索引。

（1）建立联合索引的时候，区分度最高的字段在最左边：

（2）存在等号和非等号混合判断条件时，在建立索引时，把等号条件的列前置，如 where a > ? and b= ?，那么即使 a 的区分度更高，也必须把 b 放在索引的最前列。

（3）最左前缀查询时，并不是指SQL语句的where顺序要和联合索引一致，但还是建议 where 条件的顺序和联合索引一致。

（4）假如index(a,b,c), where a=3 and b like 'abc%' and c=4，a能用，b能用，c不能用。

8、利用覆盖索引来进行查询操作，避免回表，减少select * 的使用：

覆盖索引：被查询列要被所建的索引覆盖，被查询列的数据能从索引中直接取得，不用通过行定位符再到 row 上获取，加速查询速度。

例如登录业务需求，SQL语句如下。

Select uid, login_time from user where login_name=? and passwd=?

可以建立(login_name, passwd, login_time)的联合索引，由于 login_time 已经建立在索引中了，被查询的 uid 和 login_time 就不用去 row 上获取数据了，从而加速查询。