MySQL查询优化

1.1添加索引

1.普通索引 添加INDEX
ALTER TABLE table_name ADD INDEX index_name ( column )

下面演示下给user表的name字段添加一个索引

2.主键索引 添加PRIMARY KEY
ALTER TABLE table_name ADD PRIMARY KEY ( column )

3.唯一索引 添加UNIQUE
ALTER TABLE table_name ADD UNIQUE ( column )

4.全文索引 添加FULLTEXT
ALTER TABLE table_name ADD FULLTEXT ( column)

5.如何添加多列索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )



例子:
ALTER TABLE person ADD INDEX id_index (person_id);

1.2删除索引

ALTER TABLE table_name DROP INDEX index_name;

例子:
ALTER TABLE person DROP INDEX id_index ;

索引的性能:
	主键索引比普通索引快
	因为主键索引在B+树索引的叶子节点上存的是值,
	而普通索引上存的是主键索引的值,拿到主键索引的值再查询
	由于牵扯到回表的情况,索引性能比主键索引慢一点,相当于查询了两次

范围查询的效果
	查询一列原来没有索引的字段,再添加索引,前后对比
	没有索引,查询耗时500ms左右
	添加索引后,查询耗时17ms左右
	性能提升很明显

排序的索引效果
	先排序一列原来没有索引的字段,再添加索引,前后对比
	没有索引,查询耗时3s左右
	因为排序这个动作,需要创建一个中间表去存放所有数据进行排序
	添加索引后,查询耗时176ms左右
	性能提升很明显

索引很有效,但不能整张表都添加索引
索引对插入操作和修改操作影响也很大
因为插入一条数据,他会在每个索引节点上加入数据,会影响性能的开销
所以,对于经常查询的字段可以添加索引

2.高性能索引策略

1.独立的列

​ 如果查询中的列不是独立的,则mysql就不会使用索引

​ 独立的含义是指索引列不能是表达式的一部分,也不能是函数的参数

2.like查询不能以%开头

​ 如果查询中包含like以%开头,则索引会失效

​ 解决方法:

​ 1.模糊查询使用es中间件

​ 2.使用覆盖索引

>什么是覆盖索引?

MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。

不管是SQL-Server官网，还是MySQL官网，都表达了：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

如何实现索引覆盖?

常见的方法是：将被查询的字段，建立到联合索引里去。

create table user (

id int primary key,

name varchar(20),

sex varchar(5),

index(name)

)engine=innodb;

-- 查询语句一
select id,name from user where name='demo';

-- 能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。

Extra：Using index。

-- 查询语句二
select id,name,sex* from user where name='demo'

-- 能够命中name索引，索引叶子节点存储了主键id，但sex字段必须回表查询才能获取到，不符合索引覆盖，需要再次通过id值扫码聚集索引获取sex字段，效率会降低。

Extra：Using index condition。

如果把(name)单列索引升级为联合索引(name, sex)就不同了。
create table user (

id int primary key,

name varchar(20),

sex varchar(5),

index(name, sex)

)engine=innodb;

select id,name,sex* ... where name='demo'

-- 都能够命中索引覆盖，无需回表。

Extra：Using index。

哪些场景可以使用覆盖索引来优化sql?

1.全表count查询优化

​ 如果count查询一个不是索引的列,不能利用索引覆盖,开销很大,

​ 添加索引后,就能够利用索引覆盖提效

2.列查询回表优化

​ 如果查询多列,只有一个索引,也是不能利用索引覆盖

​ 将单列的索引升级成联合索引,即可避免回表,提升性能

3.分页查询

​ 也是将单列索引升级为联合索引,避免回表,提升性能

3.查询类型是字符串的列

​ 列的类型是字符串,一定要在条件中将数据使用引号引用起来

​ 不要让字段发生类型的转换,否则索引会失效

​ concut(‘1’’2’) 用concut字符串拼接不会使索引失效
​ 因为,他会先进行拼接,在进行查询

4.最左匹配原则

​ 组合索引(联合索引):可以在两个字段上添加索引

alter table '表名' add index '索引名' ('字段名','字段名')

​ 联合索引是有先后顺序的

​ mysql 底层 的优化器会对索引进行优化匹配,命中索引

​ 即便顺序颠倒也能命中索引

​ 联合索引什么时候不生效

​ (1)对一组联合索引,添加三个字段(name,age,address),三个字段按照name在前的依次顺序

​ 不对name进行查询,只对age,address进行查询

​ 此时,联合索引失效,无法命中,查询耗时较大

​ (2)对一组联合索引来说,如果遇到范围查询(>、<、between、like)就会停止匹配

​ 同时, 对一组联合索引,有两个字段(a,b),如果左边的列是精确查找的，右边的列可以进行范围查找

​ 解决方案:

​ 添加多个组合索引,排除最左边的字段不命中,导致索引失效

4.explain使用

explain可以查询索引是否生效

explain select * from t_user where address = '123'

explain 只会测试索引性能,不会执行SQL语句

-- table:显示这一行的数据是关于哪张表的
-- type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const, eq-reg, ref, range.indexhe和ALL
-- possible-keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个命适的语句
-- key:实际使用的索引。如果为NULL,则没有使用索引。很少的情况下, MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX (indexname)来强制使用一个索引或者用IGNOREINDEX (indexname)来强制MYSQL忽略索引
-- key-_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好
-- ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
-- rows: MYSQL认为必须检查的用来返回请求数据的行数
-- Extra:关于MYSQL如何解析查询的额外信息。

type 以下排序从上到下，性能由坏到好
a.ALL: Full Table Scan, MysQL将遍历全表以找到匹配的行
b.index: Full Index Scan, index与ALL区别为index类型只遍历索引树
c.range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between, <、>等的查询
d.ref:非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
e.eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
f.const, system:当MysQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中, MysQL就能将该查询转换为一个常量
		
g.NULL: MysQL在优化过程中分解语句,执行时甚至不用访问表或索引

-- 所以由type可以进行分析,如果是ref, eq ref那么索引命中且性能较好。