MySQL进阶篇2-索引的创建和使用以及SQL的性能优化

索引

mkdir mysql
tar -xvf mysqlxxxxx.tar -c myql
cd mysql
rpm -ivh   .....rpm
yum install openssl-devel
​
systemctl start mysqld
​
gerp 'temporary password' /var/log/mysqld.log
​
mysql -u root -p
mysql>
show variables like 'validate_password.%'
set global validate_password.policy = 0;
set global validate_pawwword.length = 4;
alter user 'root'@'localhost' identified by '1234'
​
create user 'root'@'localhost' identified with mysql_native_password by '1234';
​
​
​

索引概述

高效获取数据的有序数据结构。数据库除了存储原始的数据外，还要存储索引这种数据结构。 通过这些数据结构来指向原始的数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

select * from emp where age > 35;

无索引状况：

1、根据条件逐条查找，直到最后一条记录。【全表扫描，性能极低】

有索引状况：

1、对年龄二叉树【二叉排序树】进行维护。

优点：

1、提高数据检索效率，降低数据库IO成本。

2、通过索引对数据排序，降低数据怕徐成本，降低CPU消耗。

缺点：

1、索引也是占用空间的。

2、虽然提高了查询效率，但是要维护索引，降低了表更新的速度。

索引数据结构

二叉树缺点：顺序插入时，一直往左侧插入，形成一个单向链表，查询性能大大降低。数据量较大的情况下，层级较深，检索速度慢。

【红黑树，需要自平衡的二叉树。减少了层级】，但是数量大的情况下，检索也会较慢。

B树：

多路平衡查找树，不止两个子节点。指针比父节点的元素+1。【根据阶数，满阶就向上分裂。p68级有完整演示】

B+树：

所有的元素都会出现在叶子节点。上边的节点起到了索引的作用，叶子节点是用来存放数据的。

MySQL的B+树索引结构

哈希索引

采用一定的哈希算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储到hash表中。

1、先算出这张表当中，每一行数据的hash值，接下来再拿到name字段的所有值，针对于name字段通过它内部的hash函数去计算每一个name值它应该落在哪一个hash槽位上。

【hash表的槽位上存储：字段属性值+字段属性值所在的行的hash值。】

如果产生了哈希冲突，那么则在槽位上存储一个链表来记录新值。

特点：

1、只能用于等值匹配(=,in)，不能用户范围查询。【between < > ... 】

2、无法利用索引完成排序操作

3、查询效率较高，通常一次检索就够了，效率通常要高于B+树索引。

4、目前只有Memory存储引擎支持hash索引。【但是InnoDB引擎会在指定条件下，自动将B+树索引构建为hash索引】

思考：

为什么InnoDB引擎选了B+树索引，而不是其他的呢？

二叉树：顺序插入，形成了链表，搜索时造成性能下降。

红黑树：本周上也是一棵二叉树，变成了平衡树。

B树：叶子节点和非叶子节点都存放数据，都存放到一页中，但是页的大小是有限的，为16K，这将导致一页当中存储的数据更少，指针跟着减少，只能增加树的高度，导致性能降低。放到B+树中，能存储更多的数据，层数更少，所以性能更优。

B+树：相对于二叉树来说，层级更少，搜索效率更高。【无论如何，都到叶子节点中查找值，搜索效率稳定。并且是双向链表，搜索效率相对高一点，便于范围内搜索和排序】

hash索引：对于hash索引来说，只支持等值匹配。【不支持范围内匹配和排序操作。】

索引分类

聚集索引选取规则：

1、如果存在主键，主键索引就是聚集索引

2、如果不存在主键，将使用第一个唯一索引作为聚集索引

3、如果表没有主键，或者是没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

select * from emp where ename='pshdhx';

回表查询：

1、先根据二级索引查询到name值的位置，在其叶子节点下有改行的id值

2、根据id值再跑到聚集索引中，然后找到改行的值。

InnoDB的B+树索引有多高

索引语法

--创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,....);
​
create index idx_user_name on tb_user(name);
​
​
--查看索引 加上\G之后，由原来的一行转为1列，看的清楚，不会错行。
show index from table_name\G;
--删除索引
drop index index_name on table_name;

SQL性能分析

SQL执行频率

【是插入，修改，删除为主，还是查询为主】

查看当前数据库的访问频次：是不是以增删改为主。

show [session|global] status 命令可以提供服务器状态信息

show global status like 'com_ _ _ _ _ _ _'; 7个字符

慢查询日志

慢查询日志默认没有开启，需要在/etc/my.cnf中开启配置

show variables like 'slow_query_log';

show_query_log=1

long_query_time=2

配置完毕之后，通过以下指令启动MySQL服务器进行测试，

systemctl restart mysqld

查看慢查询日志记录的信息/var/lib/mysql/localhost-slow.log

tail -f localhost-slow.log

只要尾部有内容追加进去，就可以输出出来。

profile详情

show profiles能够在做SQL优化时，帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作。

select @@have_profiling

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling；

select @@profiling;
set profiling = 1;
show profiles; --查看各个语句的执行情况[时间]
show profile for query query_id;  
--比如第16条语句执行慢,可以看看时间浪费在什么地方
show profile for query 16;
show profile cpu for query 16; --查看每个执行阶段cpu的耗费情况

explain执行==desc执行

explain select * from user where id = 1;
desc select * from user where id = 1;

1、id

select查询的序列号。id相同【联表查询会产生多个id】，执行顺序从上到下。id不同（子查询），值越大，越先执行。

2、select_type

表示select的类型，常见的取值有simple（简单表，不用联表和子查询）、primary（主查询，即外层的查询）、union、subquery（select /where之后 包含的子查询）等。

3、type

表示连接类型。性能由好到差的连接类型为 null、system、const、eq_ref、ref、range、index、all

null：不访问任何表，就是null，在业务系统中不可能出现。

system：访问系统表，才是system

const：访问主键，或者是唯一索引，才是const

ref：当我们使用非唯一性的索引查询时，会出现ref

index：用了索引，但是也会带索引进行扫描，遍历整个索引数

all：全表扫描。

4、possible_key：显示可能应用在这张表上的索引，一个或者多个

5、key：展示实际使用到的索引，如果为null，则没有使用索引

6、key_len：标识索引使用的字节数，该值为索引字段最大可能长度，并非实际长度。

7、rows：执行查询的行数。预估值。

8、filtered：查询返回的行数，占读取总行的百分比。值，自然越大越好。

索引使用

验证索引效率：

1000万条数据，某字段没有索引，以该字段为条件，查询效率很慢。

create index idx_sku_sno on sku(sno);

此条指令执行很慢，因为需要为1000万条数据，创建索引，构造索引二叉树。

最左前缀法则

如果索引了多列【联合索引】，要遵循最左前缀法则。查询从索引的最左列开始，并且不跳过索引中的列。【索引中的最左侧字段得存在，跟放的位置没有关系。】

例如：三个字段加上了同一个索引，要想索引生效，必须从左到右，不能跳过字段。只筛选1 、筛选1 2、筛选1 2 3，索引生效。如果只有1和3，那么3的索引是不生效的。3 21 也生效，1存在就生效。

范围查询

联合索引中，出现范围查询> <号时，范围右侧的列索引失效。

如果是>=，那么列索引也生效。

索引列运算

不要在索引列上进行运算操作【包含函数运算操作】，索引将失效。

explain select * from user where substring(phone,10,2) = '15';

这样的操作，索引将会失效，进行全表扫描。

字符串不加引号

虽然字符串不加单引号是能查询出来的，但是索引是不生效的。

模糊查询索引

如果仅仅是尾部模糊查询匹配，索引在不会失效。但是，如果是头部模糊查询匹配，索引失效。

例如：'软件%' 索引生效

'%工程' 索引失效。

or-索引

用or分割开的条件，如果or前的条件中的列有索引，而后边的列中没有索引，那么涉及到的索引都不会被用到。

    只有两侧都有索引的时候，所以才会被用到。

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引。

例如：1 2 3 4.。。 都是顺序递增的。 筛选条件是 >=1 ，所以MySQL认为走索引还不如直接走全表扫描效率高呢，故使用explain没用到索引。

【绝大多数数据满足，就不走索引。否则，就走索引】

SQL提示

如果某个字段有多个索引，例如有联合索引和普通索引，MySQL会自动选择一个合适的索引进行查找。

但是我们也可以人为用哪个索引。

use index【建议】 、ignore index 、force index【强制】

explain select * from user use index(idx_user_name) where name = '软件工程';

覆盖索引

尽量使用覆盖索引【查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到】，减少使用select *

Extra：Using where using index 【性能高】不需回表（根据name找主键，根据主键找rowid）。

Extra：Using inedex condition【性能低】查询的时候，使用了索引，但是需要回表查询。

联合索引是属于二级索引的，二级索引当中，叶子节点挂的就是id值，所以查询id不需要回表，但是查询name，需要回表。

id name password status

select id name password form emp where name="pshdhx";

需要在name和password建立联合索引，id就在叶子节点上。不用回表查询。

前缀索引

当字段类型为字符串（varchar、text等）时 ，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。

此时可以将字符串的一部分前缀，建立索引。这样可以大大节约索引空间，从而提高索引效率。

create index idx_tablename_idxName on tableName(column(n));

只需要指定一个n，就可以建立前缀索引。n代表该字段的前几个字符。

回表查询之后，拿出row，再看看该行的值是否与过滤条件的值完全相同。

单列索引&联合索引

1、select id ,phone , name from emp where phone="xxx" and name="xxxxx";

通过explain，可以看到只走了phone的索引。但是查询不到name的值，所以会回表查询。

但是如果使用了联合索引，就不会产生回表查询了，所以建议使用联表索引。

索引设计原则

1、针对于数据量较大，且查询较为频繁的表建立索引。

2、针对于常作为查询条件，where，order by ，group by操作的字段建立索引或联合索引。

3、选择区分度高的字段建立索引，尽量建立唯一索引。区分度越高，索引的使用效率越高。

4、字符串类型字段，建立前缀索引。

5、尽量使用联合索引，减少单列索引。联合索引很多时候可以覆盖索引，避免回表，提高查询效率。

6、控制索引的数量，索引越多，维护索引结构的代价越大，会影响增删改的效率。

7、如果要建立索引的列不能为null值，建议使用非空约束。有利于索引查询。

SQL优化

插入数据

insert 优化

1、执行批量插入insert into emp values(1,'pshdhx'),(2,'pshdhx2')

2、手动提交事务。否则，执行insert之前开启事务，执行之后关闭事务，性能较低。

3、主键顺序插入。顺序插入的性能要高于乱序插入。因为MySQL的组织结构。

大批量数据插入：load指令

<<<<<<< HEAD ​

======= ​

主键优化

数据组织方式：

在InnoDB存储引擎当中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。【index organized table IOT】

    就像是二级索引，主键从左到右存放。

列分页：

页可以为空，也可以填充一半，也可以填充100%，固定大小为16K。段固定大小为1M，有64页。每页包含了2-N行数据（如果一行数据大，会行溢出），根据主键排列。

主键顺序插入：

主键乱序插入：

1号页满，1号页数据分裂，形成3号页，然后分裂的数据和新插入的数据到3号页，页面指针改为1 3 2。

页合并：

当删除一行记录时，实际上记录并没有被物理删除，只是被标记为删除。当标记的数量达到页的50%时，InnoDB会尝试有没有合并页的可能，优化空间使用。

合并页的阈值，可以自己设置，在创建表或者创建索引时指定。

主键设计原则

1、满足业务需求的情况下，尽量降低主键的长度。

在二级索引中挂的是主键， 主键比较长，二级索引占得空间比较大。 在搜索的时候占用较多的磁盘IO。

2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。

3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号码。

4、业务操作时，尽量避免对主键的修改。 修改主键还需要修改索引的结构。

order by优化

1、Using filesort：通过表的索引或者是全表扫描，读取满足条件的数据行，然后在排序缓冲区sort Buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序，都叫file sort排序。

2、Using Index：通过有序索引顺序扫描，直接返回有序数据。不需要额外排序，操作效率高。

优化掉filesort

1、对order by后边的字段建立索引，如果是单字段，建立单字段索引。如果是多字段，建立联合索引。

2、注意：索引默认是asc排序，desc排序时，extra显示需要倒序索引 。一个字段asc 一个字段desc，会显示这种情况。

3、如果我就是想使用desc，可以创建索引时指定。

create index idx_user_age_phone_asc_desc on user(age asc,phone desc);

以上说的这一切，前提是**覆盖索引。**多字段排序时，也遵循最左前缀法则。

show variables like 'sort_buffer_size'; 默认256K

如果需要可以调大一点。

group by优化

explain select profession,count(*) from user group bu profession;

extra:Using temporary：使用到了临时表，效率较低。

如果对profession创建了联合索引（profession_age_status），Extra优化为了 Using index

如果不遵循最左前缀法则，Extra：Using index ; Using temporary

explain select age,count(*) from user where profession = '软件工程' group by age;

虽然group by没有最左前缀法则，但是where里边有，所以Extra还是Using index

limit优化

select * from sku limit 1000000,10;

select * from sku limit 10000000,10; 越往后越耗时。【19秒】

此时，MySQL排序前100000010记录，仅仅返回10000000,到10000010的记录，其他记录丢弃，查询排序 的代价非常大。

select id from sku order by id limit 10000000,10; 先查询10条的id【10秒】

select * from sku where id in (select id from sku order by id limit 10000000,10) ;【语法报错 子查询中不允许有limit】

select s.* from sku , (select id from sku order by id limit 10000000,10) tmp where s.id = tmp.id ;【10秒，比起直接分页查询，提高了近9秒】

优化思路：

一般分页查询时，通过创建覆盖索引，能够比较好的提高性能。可以通过通过覆盖索引加子查询形式进行优化。

count优化

MyISAM存储引擎，把一个表的总行数记录到了磁盘上，因此执行count(*)的时候会直接返回总数，效率很高。

InnoDB，它在执行count(*)的时候，需要把数据一行一行地从存储引擎里边读出来，然后计算个数。

优化思路：自己计数。

count(*)：InnoDB引擎并不会把数字全部取出来，而是专门做了优化，不取值，服务层直接按照行进行累加。

count(主键)：主键不可能是null，遍历整张表，把每行的主键取出来，返回给服务层，服务层进行累加操作。

count(字段)：遍历整张表，取字段，还有判断是不是null。看看not null约束。

count(1)： InnoDB会遍历整张表，但是不取值。服务层对于返回的每一行，放一个数字1，然后按行进行累加。什么数字都行，不一定是1。

效率：count(字段)<count(主键)<count(1)≈count(*)

所以尽量使用count(*)；

update优化

session1： update user set name="pshdhx" where id = 1;[开启事务begin，提交commit]

session2：update user set name = "pshdhx2" where id = 3;[开启事务begin，提交commit]

此时是没有问题的，InnoDB锁住的是行。

但是： 如果存在

update user set name="pshdhx3" where name="pshdhx";[开启事务begin，提交commit]

update user set name = "pshdhx4" where id = 3;[开启事务begin，提交commit]

此时就会出问题，因为name没有索引，行锁升级为表锁。

总结：

update时，并发事务时，要根据索引字段进行更新，否则就会锁住表。