mysql选择从10到15的记录_记录 MySQL的学习过程 ( 10）索引

最新推荐文章于 2021-03-24 05:14:18 发布

weixin_39681621

最新推荐文章于 2021-03-24 05:14:18 发布

阅读量215

点赞数

文章标签： mysql选择从10到15的记录

本文链接：https://blog.csdn.net/weixin_39681621/article/details/113634720

版权

索引

1.索引的作用

类似于一本书的目录，起到优化查询的作用

2.索引的分类

根据算法来分类

BTree innodb的默认索引

RTree

hash

FullText

GIS

3.BTree索引算法的演变

上层节点保存的都是下层节点的最小值

b-tree 没有叶子节点，枝节点的双向指针

b+tree 有叶子节点没有枝节点的双向指针

b*tree 有叶子节点枝节点的双向指针

双向指针的作用是提高范围查找的命中速度

现在mysql使用的就是b*tree

4.BTree索引功能上的分类

a.辅助索引(二级索引)

辅助索引的工作流程

dba选择一个列创建辅助索引

mysql会自动将此列的值取出来

将此列的值自动排序

将排好序的数据，均匀的存储到索引的叶子节点

生成枝节点和根节点

当查找数据时

如果一张表只有一个辅助索引的话，会直接定位到所在的叶子节点的值，这个值会对应着原表数据中相应的页码

如果一张表既有辅助索引又有聚集索引的话

会直接定位到所在的叶子节点的值，这个值通常会对应聚集索引的值(主键的ID)

b.聚集索引

mysql会自动选择主键作为聚集索引列，没有主键会选择唯一键，都没有会生成隐藏主键

mysql进行存储数据时，会按照聚集索引列(主键ID)的顺序，有序存储数据行

聚集索引直接将原表的数据页作为叶子节点(即将整行数据存储到叶子节点，这样在查找时可以直接返回整行数据，而辅助索引的叶子节点中存储的是某一列值，这是辅助索引和聚集索引最大的不同)，随后向上生成枝和根

二者的配合使用

除非是直接通过聚集索引的值能直接查到原表数据中的某一行(如：id=1，然后在b树中直接通过枝节点再到叶子节点直接拿到原表数据中的某一行)，否则都是会通过二者配合使用来查询数据

即在辅助索引中找到对应着聚集索引列的值，进而在聚集索引中找到该行所在原表数据中的值

聚集索引和辅助索引的区别

a.表中任何一个列都可以创建一个或者多个辅助索引

b.在一张表中，只能有一个聚集索引，一般是主键,非空唯一

c.辅助索引的叶子节点只存储索引列的有序值+聚集索引列值(主键值)

d.聚集索引的叶子节点就是磁盘的数据行存储的数据页

e.MySQL是根据聚集索引,组织存储数据,数据存储时就是按照聚集索引的顺序进行存储数据

5.辅助索引的细分

a.普通的单列辅助索引

b.联合索引

多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询

c.唯一索引

索引列的值都是唯一的.

6.索引树高度

越低越好，3-4 最佳

a.数据行数较多的情况

pattion 现在很少使用

分表,分库,分布式现在的主流

b.字段长度

尽量使用字符长度短的列作为索引列

业务不允许的情况下，使用前缀索引

c.数据类型:

变长长度字符串,使用了char,解决方案:变长字符串使用varchar

enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)

7.查看索引

使用desc 命令后，查看Key 栏位可以发现是否使用了索引

desc City;

+-------------+----------+------+-----+---------+----------------+

+-------------+----------+------+-----+---------+----------------+

PRI 主键

MUL 辅助索引

UNI 唯一索引

show index from City 可以查看详细信息

如果显示不完整可以加上 \G 参数

show index from City\G

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

Table: City

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: ID

Collation: A

Cardinality: 4079

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.00 sec)

8.创建单列辅助索引

假如我们要在name列上创建索引

alter table 表名 add index 索引名(列名)

alter table City add index idx_name(name);

Query OK, 4079 rows affected (0.01 sec)

Records: 4079 Duplicates: 0 Warnings: 0

注意：建索引也是ddl操作，也会锁表，不要在业务高峰期使用

desc City;

+-------------+----------+------+-----+---------+----------------+

+-------------+----------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| Name | char(35) | NO | MUL | | |

可以发现name列的索引已经添加完成了

9.创建多列的联合索引

下面创建CountryCode Population 的联合索引

alter table City add index idx_c_p(CountryCode,Population);

Query OK, 4079 rows affected (0.01 sec)

Records: 4079 Duplicates: 0 Warnings: 0

10.下面创建一个唯一索引

创建唯一索引的前提是该列的数据必须是唯一的，否则创建时会报错

alter table City add unique index uidx_dis(district);

ERROR 1062 (23000): Duplicate entry 'Zuid-Holland' for key 'uidx_dis'

可以count一下数量再配合 distinct命令确认该列是否唯一

select count(district) from City;

+-----------------+

| count(district) |

+-----------------+

| 4079 |

+-----------------+

select count(distinct district) from City;

+--------------------------+

| count(distinct district) |

+--------------------------+

| 1367 |

+--------------------------+

11.创建一个字符串前缀索引(只保留district的前五位作为索引)

alter table City add index idx_dis(district(5));

12.删除索引

先用show 查看索引名Key_name

show index from City \G

Table: City

Non_unique: 1

Key_name: idx_name

Seq_in_index: 1

Column_name: Name

Collation: A

Cardinality: 4079

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

然后直接drop即可

alter table City drop index idx_name

13.索引小实验

未加索引前

mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=3000 -uroot -p123 -verbose

Warning: Using a password on the command line interface can be insecure.

Benchmark

Running for engine rbose

Average number of seconds to run all queries: 1684.821 seconds

Minimum number of seconds to run all queries: 1684.821 seconds

Maximum number of seconds to run all queries: 1684.821 seconds

Number of clients running queries: 100

Average number of queries per client: 30

k2列加索引后

Warning: Using a password on the command line interface can be insecure.

Benchmark

Running for engine rbose

Average number of seconds to run all queries: 2.416 seconds

Minimum number of seconds to run all queries: 2.416 seconds

Maximum number of seconds to run all queries: 2.416 seconds

Number of clients running queries: 100

Average number of queries per client: 30

可以看到效率提升了700多倍

14.执行计划

sql语句在执行前会在优化器中寻找最优方式执行，可以在执行前查看语句是否按照我们的想法去执行

以便于优化sql执行

获取执行计划

a. desc/explain 要执行的sql语句

desc select * from test.t100w where k2='MN89';

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

分析执行计划

table 在一些复杂查询中可以明确的定位到有问题的表

type

查询的类型

全表扫描 All

select * from xxx

select * from xxx where yyy yyy没有索引

select * from xxx where != not in like '%aa%'

索引扫描 index 全索引扫描要查询的数据结果集是需要遍历整棵索引树

select id from City

range 范围扫描要查询的数据结果是索引树的一部分范围

select * from City where id >< >= <= yyy

select * from City where id between yyy and zzz

select * from City where countrycode like 'CH%'

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

上面的这些性能会比下面的好，是因为b树的双向指针可以起到作用，而下面的双向指针就起不到作用了

select * from City where countrycode ='CHN' or contrycode='USA'

select * from City where countrycode in ('CHN','USA');

这种情况就只能改写了 union all

select * from City where countrycode='CHN' union all select * from City where countrycode='USA';

此时再看计划变成列 ref

注意：对于辅助索引来说 <> ！= 不等于 not in 是不走索引的

对于主键(聚集索引列)来说 <> ！= 不等于 not in 是走range的

like '%CH' like 前面加百分号的不走索引

ref 辅助索引等值查询

select * from City where countrycode='CHN'

eq_ref 多表连接时，子表使用主键列或者唯一键列作为连接条件

a join b a是驱动表 b是子表

on a.x=b.y 此时y为主键或者唯一键才会走 eq_ref

这一部分应该是在表开发阶段就设计好的

desc select b.name,a.name,a.population from City as a join Country as b on a.countrycode=b.code where a.population < 100

const(system) 主键或唯一键的等值查询

desc select * from City where id = 5

null 当使用id的聚集索引去查询时，查不到数据的，比如id最大为100 ，查询的却是1000

上述内容从上倒下性能依次变好

possible_keys 可能会用到的索引

key 实际选择了哪个索引，就算前面的type是一个好的选择，但是也要看实际的索引是否是我们希望走的索引

key_len 索引覆盖长度

为了更好的理解 key_len，先建一张表

create table test (id int,k1 char(2),k2 varchar(2),k3 char(4),k4 varchar(4));

Query OK, 0 rows affected (0.01 sec)

mysql> desc test;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| k1 | char(2) | YES | | NULL | |

| k2 | varchar(2) | YES | | NULL | |

| k3 | char(4) | YES | | NULL | |

| k4 | varchar(4) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

插入2行数据到表里

insert into test values(1,'aa','中国','aaaa','中国你好');

insert into test values(1,'bb','中国','bbbb','中国你好');

select * from test;

+------+------+--------+------+--------------+

| id | k1 | k2 | k3 | k4 |

+------+------+--------+------+--------------+

| 1 | aa | 中国 | aaaa | 中国你好 |

| 1 | bb | 中国 | bbbb | 中国你好 |

+------+------+--------+------+--------------+

然后把里面的每个列都创建一个单个的索引

alter table test add index id(id);

alter table test add index k1(k1);

alter table test add index k2(k2);

alter table test add index k3(k3);

alter table test add index k4(k4);

desc test;

+-------+------------+------+-----+---------+-------+

+-------+------------+------+-----+---------+-------+

| id | int(11) | YES | MUL | NULL | |

| k1 | char(2) | YES | MUL | NULL | |

| k2 | varchar(2) | YES | MUL | NULL | |

| k3 | char(4) | YES | MUL | NULL | |

| k4 | varchar(4) | YES | MUL | NULL | |

+-------+------------+------+-----+---------+-------+

desc select * from test where id=1;

+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

| 1 | SIMPLE | test | ref | id | id | 5 | const | 2 | NULL |

+----+-------------+-------+------+---------------+------+---------+-------+------+-------+

可以发现key_len 是5 ，这个5是因为 int实际的是占4，然后用于标记是否为null用了1个，所以是5

desc select * from test where k1='aa';

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

| 1 | SIMPLE | test | ref | k1 | k1 | 9 | const | 1 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

这里的key_len 是9 是因为这个字段是char(2) 在utf8mb4中一个汉字最大占用4位，所以按照最大计算8+1(标记null的1)

desc select * from test where k2='中国';

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

| 1 | SIMPLE | test | ref | k2 | k2 | 11 | const | 2 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

这个字段是varchar(2) 那为什么是11呢，因为vchar 有2位是标记开始和结束的，所以就差了2位

desc select * from test where k3='bbbb';

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

| 1 | SIMPLE | test | ref | k3 | k3 | 17 | const | 1 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

这里的char(4) 就是4x4+1 了

desc select * from test where k4='中国你好';

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

| 1 | SIMPLE | test | ref | k4 | k4 | 19 | const | 2 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

这里的varchar(4) 4x4+2+1 所以就过就是19

character set：utf8mb4=4,utf8=3,gbk=2,latin1=1

key_len主要应用于联合索引

先删除之前创建的索引id k1 k2 k3 k4

alter table test drop index 索引名

对于单列索引来说，key_len越短越好，但是对于联合索引来说就不一定了。

现在创建一个联合索引

alter table test add index idx(k1,k2,k3,k4)

建立这个联合索引的目的是为了以后的查询都要走这个联合索引，可以简单理解联合索引只需建立一个索引，就可以同时索引多个列，比单个索引更省空间。

下面来看

desc select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';

+----+-------------+-------+------+---------------+------+---------+-------------------------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------------------------+------+-----------------------+

| 1 | SIMPLE | test | ref | idx | idx | 56 | const,const,const,const | 1 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------------------------+------+-----------------------+

这里面的key_len 已经达到了56 ，56这个数字是 k1 k2 k3 k4加到一起的长度，所以当查询用到的key_lenth长度越全(越长)越好

我们来看下面的例子

desc select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';

desc select * from test where k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa'

只要查询相关的所有索引列都是等值条件查询，无关顺序，所以上面的2条 key_len都是56，这是因为优化器会自动排列查询条件

实际使用中的话，需要注意k1 k2 k3 k4的顺序,一般把唯一值多的列放到靠前面(左边)的位置

但是，如果是下面这样

desc select * from test where k3='aaaa' and k4='中国你好' and k1='aa';

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

| 1 | SIMPLE | test | ref | idx | idx | 9 | const | 1 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+

此时key_len 变成了9，这说明对于这种本应该是 k1 and k2 and k3 and k4的条件，一旦少了一个或者多个，只会走排序后的连续匹配上的(k1，k3,k4)，本例匹配的是k1，后面就不会匹配了，所以是9。

再看这个例子

desc select * from test where k2='中国' and k4='中国你好' and k1='aa';

+----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+

+----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+

| 1 | SIMPLE | test | ref | idx | idx | 20 | const,const | 1 | Using index condition |

+----+-------------+-------+------+---------------+------+---------+-------------+------+-----------------------+

1 row in set (0.00 sec)

因为这时是按照 k1,k2,k4 匹配的，k1+k2=20

由此可见，会先排序，然后根据排序后的结果，如果是顺序的就会计入key_len的结果，否则不会计入key_len的结果

所以上面两个例子的优化索引建法为

alter table test add index idx(k1,k3,k4)

alter table test add index idx(k1,k2,k4)

上面的2个例子假设k1字段唯一值多

注意：如果将表中的id k1 k2 k3 k4 这5列都做成联合索引，每个列作为查询条件都会走索引

刚才看的都是等值的案例，下面看一下不等值的例子

在where查询中如果出现 > < >= <= like 此类

desc select * from test where k1='aa' and k2>'中国' and k3='aaaa' and k4='中国你好';

+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

| 1 | SIMPLE | test | range | idx | idx | 20 | NULL | 1 | Using index condition |

+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+

这个例子中因为k2的条件变成了> 所以最终的key_len 变成了20，也就是说到了k2就不往下走了，但是包含k2

所以如果要优化这类sql，需要将k2的这个条件放到最后，所以语句会变成这样

select * from test where k1='aa' and k3='aaaa' and k4='中国你好' and k2>'中国';

相应的所以也需要修改为

alter table test add index idx(k1,k3,k4,k2)

对于多子句的情况

先清理下刚才建的索引

alter table test drop index idx123;

查看一下以确定现在没有任何索引

show index from test;

先加2条单列索引

alter table test add index idx1(k1);

alter table test add index idx2(k2);

desc select * from test where k1='aa' order by k2;

+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+

| 1 | SIMPLE | test | ref | idx1 | idx1 | 9 | const | 1 | Using index condition; Using where; Using filesort |

+----+-------------+-------+------+---------------+------+---------+-------+------+----------------------------------------------------+

此时我们发现，带有order by 的这一列没有计入 key_len 这一列

此时建立联合索引即可优化

alter table test add index idx0(k1,k2);

desc select * from test where k1='aa' order by k2;

+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+

+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+

| 1 | SIMPLE | test | ref | idx0 | idx0 | 9 | const | 1 | Using index condition; Using where |

+----+-------------+-------+------+---------------+------+---------+-------+------+------------------------------------+

1 row in set (0.00 sec)

虽然key_len 都是9 但是，观察 Extra列发现少了一个Using filesort，所以起到了优化的效果

对于多子句的查询，才创建联合索引时，要遵循

按照子句的执行顺序建立联合索引

Extra

这一列最需要关注的点就是 Using filesort ，如果出现就意味值出现了额外的排序，因为索引本身在构建的时候就已经是有序的了

所以可以说明索引的应用不合理，或者索引设计的不合理。就像上面的例子一样，在查询中有关排序的条件列没有合理的应用索引

涉及到Using filesort语句 order by

group by

distinct

union

15.关于数据占用的空间

以utf8mb4 为例

varchar(20)

能存20个任意字符

无论存储的是英文数字中文，一个字符的最大预留长度都是4个字节

对于中文，1个中文字符占4个字节

对于数字和字母，1个实际占用1个字节

可以使用 select length() from 表名来查询实际占用的字节数

explain(desc)使用场景

业务缓慢，从DBA的角度来分析问题的所在

分2种，第一种是一下子就卡住，变得很缓慢的

show process list 获取导致数据库卡住的语句

explain 分析sql的执行情况，是否走了索引，索引的类型

建索引，改sql

如果是第二种，在某一段时间才会变慢的话，

记录慢日志 slowlog 分析slowlog

explain 分析sql的执行情况，是否走了索引，索引的类型

建索引，改sql

附：编码表

https://www.runoob.com/mysql/mysql-data-types.html

16.索引的应用规范

索引的建立原则

a.建表必须要有主键，建索引一般是无关列，自增长

b.根据 where order by group by join on distinct 这些条件语句出现的频率，来给它们建立索引

c.最好使用唯一值多的列作为联合索引的前导列，其他的参考联合索引的优化细节进行优化

d.列值长度较长的索引列，建议使用前缀索引，以减少索引的存储空间的使用(就像一本书的目录，越短越好)

e.降低索引条目，一般是经常查询的列才做索引。一方面不创建没用的索引，另一方面不常使用的索引要清理，percona toolkit (Pt duplicate key checker) 另外 sqlyog 工具中选中某张表，看信息中，也会有冗余索引的提示

f.索引的维护要避免业务繁忙期

g.小表不需要建索引

17.不走索引的情况(开发规范)

a.没有查询条件或查询条件没有建立索引

select * from city;

select * from city where 1=1

b.查询结果集是原表中的大部分数据，大约是25%以上

主要是针对辅助索引扫描

c.索引本身失效，统计数据不真实

同一个语句突然变慢，统计信息过旧(如该行频繁变更)，导致索引失效

d.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括 + - × / ！等

desc select * from City where id=100;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

desc select * from City where id+100=101;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

e.隐式转换导致索引失败

create table aaa (id int,name varchar(64),telnum char(11));

insert into aaa values(1,'zs','110'),(2,'ls',120),(3,'ss','119');

alter table aaa add index idxtel(telnum);

此时

desc select * from aaa where telnum=120;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

desc select * from aaa where telnum='120';

+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+

+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+

| 1 | SIMPLE | aaa | ref | idxtel | idxtel | 45 | const | 1 | Using index condition |

+----+-------------+-------+------+---------------+--------+---------+-------+------+-----------------------+

可以发现 telnum=120 时没有走索引，这就是隐式转换，因为telnum 建立时是char类型，但是查询时却用的int类型，所以会进行隐式转换

f.<> not in 不走索引(辅助索引)

g.like "%aa" 百分号在最前面不走，如果业务中非要用就去用 ES

h.联合索引

weixin_39681621

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

mysql选择从10到15的记录_记录 MySQL的学习过程 ( 10） 索引

mysql选择从10到15的记录_记录 MySQL的学习过程 ( 10）索引