选择性更高 mysql,MySQL

MySQL是3层还是4层？

2.为什么推荐id自增？

3.MRR：mult_range_read

4.FIC：fast index create

MySQL是3层还是4层？

取决于数据类型和数据量，索引越小越好；

前缀索引优化，减小索引；

为什么推荐id自增？

取决于数据库是不是分布式的，不建议

不是分布式，推荐，插入数据，顺序后再后面插入数据，中间插入数据，会导致页的分裂、合并，16k很快，但是并行操作时出问题；

回表：

使用二级索引(辅助索引)时，没有发生索引覆盖

索引覆盖：

select id from table where name = ?;

索引下推：

数据存储在磁盘中，mysql有自己的服务，要跟磁盘发生交互

没有索引下推：先从存储引擎中根据一个索引(name)拉取数据；再mysql server根据另一个字段(age)筛选；

缺点：IO量大

有索引下推：会根据name，age来获取数据，不需要server做任何的数据筛选；

缺点：需要在磁盘上多做数据筛选，原来的筛选是放在内存中的，现在放在磁盘，查找数据的环节，看起来成本比较高，但是数据是排序的，所有的数据是聚集存放的，所以性能不会有影响，而且整体的IO量会大大减少，反而提升性能。

最左匹配：

4.1 组合索引：(name,age)

select * from table where name = ? and age=?

select * from table where name = ?;

select * from table where age = ?; --不会走索引，不符合最左匹配

select * from table where age = ? and name = ? --会走索引,mysql优化器:

CBO:基于成本的优化

MRR：mult_range_read

内存排序，--》范围查找

FIC：fast index creation

插入和删除数据：

先创建一个临时表，将数据导入临时表；

把原始表删除

修改临时表的名字

给当前表添加一个share锁，不会创建临时文件的资源消耗，还是在源文件中，但是此时如果有人发起dml操作，很明显会导致数据不一致，索引添加share锁，读取时没有为题的，但是DML会有问题

覆盖索引

1、当发起一个被索引覆盖的查询时，在explain的extra列可以看到using index的信息，此时就使用了覆盖索引

2、在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以进一步的进行优化，可以使用innodb的二级索引来覆盖查询。

例如：actor使用innodb存储引擎，并在last_name字段又二级索引，虽然该索引的列不包括主键actor_id，但也能够用于对actor_id做覆盖查询

mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

partitions: NULL

type: ref

possible_keys: idx_actor_last_name

key: idx_actor_last_name

key_len: 137

ref: const

rows: 2

filtered: 100.00

Extra: Using index

1 row in set, 1 warning (0.00 sec)

前缀索引实例说明

​ 有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

​ 一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

案例演示：

--创建数据表

create table citydemo(city varchar(50) not null);

insert into citydemo(city) select city from city;

--重复执行5次下面的sql语句

insert into citydemo(city) select city from citydemo;

--更新城市表的名称

update citydemo set city=(select city from city order by rand() limit 1);

--查找最常见的城市列表，发现每个值都出现45-65次，

select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;

--查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数

select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;

select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;

--此时前缀的选择性接近于完整列的选择性

--还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了

select count(distinct left(city,3))/count(*) as sel3,

count(distinct left(city,4))/count(*) as sel4,

count(distinct left(city,5))/count(*) as sel5,

count(distinct left(city,6))/count(*) as sel6,

count(distinct left(city,7))/count(*) as sel7,

count(distinct left(city,8))/count(*) as sel8

from citydemo;

--计算完成之后可以创建前缀索引

alter table citydemo add key(city(7));

--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。

where 条件和 order by 使用相同的索引，并且order by 的顺序和索引的顺序相同，并且order by 的字段都是升序或降序。