mysql优化之路(初级）

2020-11-27 本文已影响0人以南之南_b9a1

一条查询语句是怎么执行的？

一条更新语句是怎么运行的?

什么是事务?什么是长事务?如何避免长事务?

如何启动一个事务?

mysql的隔离性和隔离级别?

什么是脏读，幻读，不可重复读?如何避免?

mysql的锁类型?

什么是死锁？msyql是如何解决死锁的？

mysql存储引擎的种类，以及他们的区别？使用场景？

如何查看表状态，如表大小，行数，索引大小，行格式...

什么是mysql基准测试

mysql基准测试的类型，不同的类型有哪些测试工具

基准测试完成后需要看那些指标

如何设计一个基准测试？并得到正确的结果，并绘制图形

如何使用sysbench对mysql进行压测

mysql基本框架.png

1.连接器：

负责连接客户端，客户端连接服务器，服务器都会分配给客户端一个线程，客户端断开时，服务器端不会销毁该线程，会校验用户名和密码。若正确，则连接成功，同时查询该客户端是否具有对哪张表的权限。

2.查询缓存

执行完成后，会将该select语句缓存起来，value就是该语句的查询结果，当下一次一模一样的selec语句是，查询缓存会直接缓存，不会走解析器和优化器步骤，但是，不建议使用，查询缓存的失效范围很广，一旦表中的数据由更新，缓存就会失效。

3.解析器

对sql语句进行解析，负责执行语句的词法解析和语法解析，对输入的sql语句是否满足语法。

4.优化器

一个mysql语句，会有多个执行步骤，效率也不会相同,选择最优的mysql。如

select * from t1 left join t2 on t1.id = t2.id where t1.name ='xxx' and t2.distance ='xxx'

选择1:先查询t1表的name 然后关联t2表，在查询t2.name ='xxx'
选择2:先查询t2表的name然后关联t1表，在查询t1.name ='xxx'
优化器包括重写查询，决定表的顺序，选择合适的索引.

5.执行器

执行该select语句，执行之前，校验该用户是否拥有对该表的权限，如果有，则会从第一行查询，如果有就返回，直到行末尾。

以上是对select语句执行过程的解析

那么更新语句呢？

mysql> update T set c=c+1 where ID=2;

更新语句稍微有点不同？但是mysql的架构是不变的.
1.客户端连接，连接器负责连接客户端。
2.解析器，负责词法语法解析
3.更新语句执行;mysql更新语句时，写入到redo log中，并更新内存数据，此时如果更新成功就返回，代表update更新成功了。等待mysql不那么忙的时候，在将脏数据刷新到磁盘上。也称为WAL技术(预写式日志技术)。

redo log和bing log的差异和不同

1.redo log是innodb 特有的，bing log是server特有的
2.redo log 是固定大小的一共有4G，一共4页，每页1G,环状数据结构，追加写的，当写完后，会从头开始。bing log追加写，写完后写入下一页。
3.redo log 是逻辑的记录数据页做了什么修改，binglog是物理了，记录了给id=2的这条数据c加1；

那么Mysql为什么会随时随地恢复数据呢？正因为有了redo log和bing log。

redo log和bing log通过两阶段提交，保证了数据的安全性。
下面分析一下上面update语句的执行流程：
1.msyql用存储引擎找id =2 这一行，如果内存中有该页，将直接返回，如果没有将从磁盘中加载到内存中，然后返回。
2。执行器，拿到存储引擎给的值，对c进行加1操作。同时调用存储引擎API接口写入这行数据。
3.引擎将这行数据写入到redo log中，然后更新内存，此时redo log处于prepare阶段；同时告诉执行器，更新成功
3.执行器，执行这个操作，并且生成binglog日志，将binglog写入磁盘
4.执行器调用存储引擎API接口，将redo log改成commit，运行这个sql语句。

update语句的更新流程.png

假设原来c的字段值为0
如果先写redo log 后写bing log 会有什么问题忙？
1.写完redo log 如果mysql 异常重启，由于写入了，redo log，mysql仍然讲数据恢复回来，所以恢复c列为1。
由于没有写入bing log ，用Binglog日志恢复mysql，那么bing log日志是丢失这条更新语句的，那么恢复出来的值是0和原来的库中的数据不同。
2.先写bing log 后写redo log
如果写完bing log mysql异常重启，由于没有写入redo log，mysql讲原来的c列恢复为0。
但是由于写入了bing log日志，用Bing log日志写入了这条更新语句，那么恢复出来的c列的值为1 ，那么与原来的库不同。

总结:mysql 通过两阶段提交，保证了事务执行的逻辑性，事务的逻辑状态保持一致。

什么是事务？长事务？如何查询长事务？长时间由于没有提交的事务。
事务：是一组具有原子性的sql语句单元，要么全都执行，要么要都不执行。
事务的启动方式有:

begin....commit/rollback
设置mysql 的autocommit = 1 mysql会自动提交一个事务
begin.....commit work and chain语法 mysql会自动提交一个事务，同时开启下一个事务

如果设置autocommit = 0，mysql不会自动提交事务，会导致长事务的方式，建议将配置文件的autocommit设置为1，

mysql的隔离性:

A原子性：整个事务的sql语句，要么要都提交，要么全都不提交
C一致性：是指数据库状态的一致性，从一个状态到另一个状态的改变
I隔离性：事务提交之前，修改的结果对其他事务不可见
D持久性，事务语句提交后，持久到磁盘上，不会因为崩溃而导致数据丢失

mysql的隔离级别:

读未提交:事务还没提交，修改的值就会被其他事务读取到，会导致脏读
读以提交:当前事务读取以提交事务的数据,即一个事务读取两次，查看到的结果是不一样的。会导致不可重复读
可重复读:事务开启的时候，会创建相当于一个快照，事务创建的时候，此时数据的列值，就已经确定了。一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的
串行化:顾名思义串行读取，读会加读锁，写会加写锁，整个sql语句的执行是串行执行。读写冲突时，后开启的事务会阻塞，必须等到前面的事务提交之后，才会提交。

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

image.png

上面这两个事务，不同的隔离级别，所读取到v1 v2和v3的值是不一样的

读未提交级别下 v1 v2 v3都是2 。
读已提交级别下 v1是1 ，v2是2，v3是2 由于b提交了事务，所有v2读取到的是事务B提交过的数据。
可重复读级别下 V1和V2都是1 ,V3是2.由于事务A启动时就c得值以及确定了，所以v1和V2读取的值是1。
串行化：V1,V2 都是1，V3是2，事务B将c的值由 1改成2会被阻塞，需要等待事务A提交之后，才会执行。

mysql的锁类型

分为排它锁和共享锁也叫写锁和读锁
写锁是排他的，会阻塞读取select操作，按照锁的粒度分为表锁和行锁，比如alter table会锁住表，行锁有可以分为乐观锁，和悲观锁，悲观锁为行锁，乐观锁为版本号MVCC实现。
读锁是共享的通过 select...for update 和....lock in share model实现，只能读不能写

死锁？

死锁时不同的事务，在同一个资源上相互竞争使用，并锁定对方占用的资源，导致恶性循环的现象。
解决方式:
mysql实现了死锁检测和死锁超时检测

检测死锁的发生，返回一个错误
放弃锁争用。
持有最小的排他行级锁的事务进行回滚。

mysql的存储引擎

innodb 和 myisam 和NDB 和Memory等等。
将innodb和myisam 比较区别如下：
myisam 只提供压缩，全文检索，空间函数等特性功能，缺点：不支持行级锁和事务，只支持表锁，存在严重的性能问题。
myisam 将数据和索引分开两个文件存储,分成.myd和.myi存储，

show table status like 'xxx'\G 显示表的相关信息

如何选择合适的存储引擎？

如果需要用到innodb不支持某些特定的功能，在一般情况下，都应该选择innodb.

事务: 如果必须要支持事务，innodb是非常不错的选择，不考虑事务那么myisam非常好，通常用于日志情况。
备份: 需要实时备份，innodb 也是非常不错的选择。
奔溃恢复: 如果需要在数据库崩溃后，迅速恢复，innodb也非常不错，myisam 损坏的几率要比innodb高。

基准测试

基准测试：是针对mysql设计的一种压力测试。基准测试的类型有集成式和单组件式。
集成测试针对整个应用，包括服务器，代码，数据等，而非单个测试。
单组件式针对mysql测试，比较mysql下的schema 不同，mysql的性能不同。
针对某个问题进行测试，了解问题出现的场景

基准测试工具

集成测试工具

ab
httpload
jmeter

组件式测试工具

sysbench

基准测试的指标

吞吐量
测试单位时间,每秒的事务数量（TPS),以及在线事务处理的吞吐量(OLTP)
并发性
用来表示有多少用户同一时间访问web站点，http协议是一个无状态的，只是简单的读取浏览器上的信息，不等同于web服务器的并发性，web服务器的并发性，不等于数据库的并发性。而web站点的并发性，实在任意时间有多少同时发生的并发请求。
响应时间
用于测试任务的所需的整体时间。

规划和设计一个基准测试
1.明确相应的目标，提出问题
2.选择合适的测试方案
3.获得生产数据集的快照
4.获得不同级别的查询，如果是集成式记录web服务器的Http请求。也可以打开mysql的查询日志。
5.记录测试数据，确定系统的配置，如何测量的，系统的预热方案。
6.使用gnuplot或r绘制图形。

使用sysbench对mysql进行测试

sysbench可以测量以下
1.CPU

sysbench --test=cpu --cpu-max-prime=20000 run

sysbench的cpu测试是在指定时间内，循环进行素数计算

--cpu-max-prime 素数生成的上线数量
--threads: 线程数
--time: 运行时长

CPU speed:
    events per second:   312.37  #所有线程每秒完成了312.37次event
General statistics:
    total time:                          10.0022s ##总共花费时间
    total number of events:              3125 ###10秒内线程总共计算了3125次event
Latency (ms):
         min:                                    2.79 ###完成一次event最少时间
         avg:                                    3.20 ###完成event 平均时间
         max:                                  223.82 ###完成1此最大时间消耗
         95th percentile:                        3.19 ###95%的线程在3.19毫秒内完成
         sum:                                 9997.49
Threads fairness:
    events (avg/stddev):           3125.0000/0.00
    execution time (avg/stddev):   9.9975/0.00 ###每个线程平均9.9975秒

2.磁盘IO
sysbench 的 file 测试需要也是 prepare、run 和 cleanup 三个阶段。 prepare 是准备阶段，产生需要的测试文件，run 是测试阶段，cleanup 是清理测试产生的文件

//prepare阶段
sysbench fileio --file-num=4  --file-total-size=2G prepare

运行完毕后，会在当前目录下生成4个文件，大小一共2G

sysbench fileio --time=180 --events=100000000 --threads=1 --file-num=4 --file-total-size=2G --file-test-mode=rndrw run

时间180秒，线程数 1 个，随机数请求 100000000 次，随机读性能

File operations:
    reads/s:                      1579.51 每秒读次数
    writes/s:                     1053.01 每秒写次数
    fsyncs/s:                     105.32 每秒从内存向磁盘同步的次数
Throughput: ###吞吐量
    read, MiB/s:                  24.68
    written, MiB/s:               16.45
General statistics:
    total time:                          180.0590s
    total number of events:              492973
Latency (ms):
         min:                                    0.00
         avg:                                    0.36
         max:                                   86.23
         95th percentile:                        1.01
         sum:                               179444.55
Threads fairness:
    events (avg/stddev):           492973.0000/0.00
    execution time (avg/stddev):   179.4445/0.00

3.mysql压测
//TODO 这个不是太会！！！！虽然网上一大堆教程

mysql性能优化:

1.性能定义：完成某件任务所需要的时间和度量。在myql中，数据库执行sql语句的时间可定义为响应时间，对性能的优化，是在一定的负载下尽可能降低响应时间。
2.对响应时间的优化：如何降低响应时间，就要明白为什么需要那么多时间。
3.性能优化工具:pt-query-digest对mysql慢查询日志进行分析

开启慢查询日志

mysql> show variables like '%slow%';
+---------------------------+-----------------------------------------+
| Variable_name             | Value                                   |
+---------------------------+-----------------------------------------+
| log_slow_admin_statements | OFF                                     |
| log_slow_slave_statements | OFF                                     |
| slow_launch_time          | 2                                       |
| slow_query_log            | ON                是否开启慢查日志         |
| slow_query_log_file       | /var/lib/mysql/iZ8ykv3uxiy4adZ-slow.log | 慢查询日志的存放地方

mysql> show variables like '%long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 | 当mysql的执行时间超过5秒时，会被记录到日志
+-----------------+----------+

使用pt-query-digest分析慢查询日志(Profile)

//TODO

mysql的数据类型

如何选择mysql选择合适的数据类型：
1.更小的数据通常情况下，会更好，更小的数据类型占用更少的内存，磁盘和CPU缓存
2.尽量避免为NULL.为NULL会使mysql索引的列统计，和值比较更难。
3.尽可能简单，一般用mysql内部基础的类型来存储，如：用dateTime或data等来存储时间，而不是用字符串来存储时间，用long来存储ip，而不是vatchar来存储ip

整数类型

分成整数和实数类型
整数类型
tinyint(1个字节),smallint(2个字节),mediumint(3个字节),int(4个字节) ,bigint(8个字节)
一个字节=8位，所以对于的取值范围为 -2^(n-1) ~ 2^(n-1) -1,其中n为字节。
其中tinyint 的取值范围为-128 ~ 127,其中整数数据类型可分为，有符号位和无符号位，无符号位(unsigned) 表示没有负数。如tinyint unsigned表示 0~ 255；有符号位tinyint表示 -128 ~ 127。
int(1)和int(10)有什么区别？
他不限制int的取值范围，而是用于mysql交互时(mysql命令行客户端)，用来显示的字符个数。
实数类型
实数类型代表有小数部分的数字。
float,double,decimal
float和double支持标准的浮点数运算简称浮点，decimal支持更高精度的运算;其中float占4个字节，double占8个字节，decimal需要额外的存储空间和计算,
可以指定小数点前后所允许最大的位数。但是可能会影响列空间消耗？如decimal(18,9)小数点两边各存储9个数字，每四个字节存储9个数字，小数点前面4个字节，小数点后面4个字节。小数点本身占一个字节。
建议只指定数据类型，不指定精度，精度的定义是非标准的???

字符串类型

分成char类型和varchar类型
char和varchar在存储在磁盘和内存上可能不一样。
varchar:
用于存储可变字符串，因为他使用必要的空间，比定长更节省空间，但无绝对，在一种情况下，当Row_format=Fixed的话(通过show table status like 'xxx'\G 来查看)，还是会定长存储,即使你使用的是varchar。如果列的最大长度小于或者等于255时，需要1个长度来存储字符串的长度，否则就需要2个长度来存储。如varchar(1000)就需要1002个字节，2个字节存储字符串长度。
因为行是变长的，所以在update操作可能会使原来的行变得比原来更长，如果没有空间存储的话，不同存储引擎操作是不一样的，myisam将不同的行拆成片段存储，innodb需要页分裂来存储。
varchar使用场景:

列的更新很少，所以就没有也分裂的场景
每个字符都是使用了不同的字节数来进行存储
在mysql5.0以上版本，mysql在存储和检索时会保留末尾空格。当字段过长时，innodb会将varchar变成Blob.

char
char类型是定长的，不容易产生碎片，存储空间更有效率，存储Y/N，char只需要一个字符。会删除末尾空格。mysql的vachar字段的类型虽然最大长度是65535，但是并不是能存这么多数据，最大可以到65533（不允许非空字段的时候），当允许非空字段的时候只能到65532。
char(10)如果字段的值不够10个字符时，char会采用末尾空格来进行填补。
varchar能存储多少个汉字和数字呢？

4.0版本以下，varchar(100)，指的是100字节，如果存放UTF8汉字时，只能存33个（每个汉字3字节）
5.0版本以上，varchar(100)，指的是100字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个

BloB和Text类型
存储字符串数据类型，BloB存储二进制方式存储，Text采用字符方式存储。
BloB类型有:tinyblob，smallblob,blob,mediumblob,longblob。没有排序规则或字符集
text类型有:tinytext,smalltext,text,mediumtext,longtext。有排序规则和字符集

Enum枚举

create table enum_text(
e enum('fish','apple','dog') not null
)

枚举列把一些不重复的列存储到一些不重复的集合。Mysql将内部中将枚举值保存为数字，在.frm中保存"数组-字符串"的映射.这三行实际存储为整数，而不是字符串。
查询实际值:

mysql> select e+0 from enum_text;
+-----+
| e+0 |
+-----+
|   1 |
+-----+
1 row in set (0.00 sec)

所以不建议用数字作为enum枚举常量。field()函数指定排序，导致mysql无法使用索引来消除排序.
弊出:枚举的列表值是固定的，所以添加和修改必须使用alter table语句。所以在添加枚举的值时，通常会添加末尾。

日期和时间类型

DateTime和timestamp
DateTime:占8个字节，存储的是1000~9999年精确度为秒，封装格式为YYYYMMDDHHMMSS的整数中。
Timestamp:占4个字节，存储的是从1970年1月1日以来的秒数，只能表示1970年到2038年。显示的值也依赖于时区。
插入或更新Timestamp，如果没有指定第一个timestamp的值，mysql则设置这个列的值为当前时间。timestamp 默认为 not null.除了特殊的行为外，应该尽量使用timestamp，因为他比datatime空间更高。

位数据类型

bit和set
bit列在一列中存储一个或多个true/false值，bit(1)定义一个单位的字段。最大长度是64个bit位。
set列保存很多个true/false值，缺点是：改变列的代价高，需要alter table.无法通过索引进行查找。

标识符

如何选择表的标识列？

整数类型
是最好的选择，使用auto increment.
enum和set
非常不建议选择，enum和set列只适合存储固定信息。
字符串类型
尽可能使用字符串类型，作为列的表示，因为它们很消耗空间，比数字类型慢。使用随机的字符串，因更加小心，随机生成的值任意分布在很大的空间内，导致insert 和select时，更新很慢.
1.插入时被索引随机的写入到不同的位置，导致插入很慢。会导致页分裂，磁盘随机访问。产生碎片。
2.select会变慢,逻辑上相邻的行分布在磁盘和内存的不同地方。
不建议使用UUID，如果使用则需要移除"-",通常使用unhex函数转换uuid为16位的字节数字。检索式通过hex来格式化16进制。

mysql计数器

有一下情况，在实际应用中，很常见，如查询一个用户的朋友数，文件下载的数量。

create table hit_counter(
  cnt int unsigned not null
)engine = innodb;

网站每次点击都会更新;

update hit_counter set cnt = cnt +1;

但是会有一个问题！这条记录上会有个全局的互斥锁，会使的这条更新语句串行执行！那么需要在增加一列，随机选择一列进行更新。

create table hit_counter(
 cnt int unsigned not null,
 slot tinyint unsigned not null primary key
) engine = innodb;

预先在表中插入100行数据，随机选择一个slot进行更新，

update hit_counter set cnt = cnt +1 where slot = RANDOM()*100;

查询统计结果这样操作

select count(cnt) from hit_counter;

如果希望每天一个开始一个新的计数器。

create table hit_counter(
day date not null,
cnt int unsigned not null,
slot tinyint unsigned not null 
primary key(day,slot)
)

同时使用on Duplicate key update 代替。

insert into daily_hit_counter(day,cnt,slot) values(CURRENT_DATE,RAND()*100,1) ON Duplicate key update cnt = cnt +1;

返回结果
day,slot,cnt
2020-10-01,1,1
2020-10-01,2,3
2020-10-01,4,1

如何加快alter table的速度

mysql的alter table的更新速度是个很大的问题，大部分的操作时，用新的结构创建一个空表，从旧表中查出所有数据插入新表。然后删除旧表。一般情况下，alter table都会导致Mysql服务中断。
以下三种优化方式，会加快alter table的速度。
1.实现在一台不提供服务的机器上执行alter table操作。然后和主库进行切换。
2.使用"拷贝",创建一张新的表，和原表无关，通过重命名和删表操作交换两张表。
3.修改表的默认值，或者删除或增加主键自增。一般情况下可以直接修改.frm文件，然后替换，因为列的默认值和主键自增都会存在.frm文件中。

步骤如下

创建一个和原来表有相同结构的空表，并进行修改。
执行flush tables with read lock.
交换.frm文件
unlock tables;

mysql的索引

索引的基本概念:是存储引擎用于快速查找记录的一种数据结构。
工作方式：现根据索引值找到对应的值，然后根据匹配的所有记录找到对应的数据行。
mysql的索引实在存储引擎层实现。

索引的基本类型

B-Tree索引：

B-Tree.png
特点：所有的值都是按顺序存放的。左节点指向子页的指针，左子页的值小于key，右子页的值大于根节点的值;很适合查找范围数据，全键值，键值范围，键前缀查找;顺序组织存储.
查找方式：使用索引不需要全表扫描来获取需要的数据，从索引的根节点开始搜索，根节点指向了子节点的指针，存储引擎按照这些指针按照下层查找，比较节点页的值和要查找的值进入到下层子节点。
适用查询范围:新建索引key(last-name,first-name,dob)

全值匹配：索引中的列进行全值匹配如 last-name = 'xxx' and first-name = 'xxx' and dob='xxx';
匹配最左前缀：last-name = 'xxx'
匹配范围值: last-name >'Allen' and last-name <''Barry
只访问索引的查询：称为覆盖索引。select last-name,first-name,dob from xxx;
弊端：
如果不是使用索引的最左列开始查找，无法使用索引。
不能跳过索引的某列。如 last-name = 'xxx' and dob= 'xxx'
如果查询中有某个列范围查询，右边的列都无法使用索引进行查找,如last-name = 'xxx' and first-name like 'j%' and dob = 'xxx';

哈希索引(只有memory引擎支持)

只有精确匹配的所有列的查询才会有效，哈希索引将所有的哈希码存储在索引中，在哈希表中执行每个数据行的指针。如果多个列的哈希值相同，索引会以链表的形式存放多个记录到同一个哈希目中。
特点:

哈希索引只包含哈希值和行指针，不存储字段值。
无法排序，不是按照索引值存储的。
不支持部分索引列匹配查找：如在数据列(A,B)建立哈希索引，如果使用A列，那么就无无法使用该索引。
只支持等值查找，如：= in ! 不支持范围查询。price >100

自适应哈希索引：如果一个索引值，被频繁的访问的话，哈希索引将会在内存中基于B-Tree索引上在创建一个哈希索引。
使用场景：
存储某个具体的url时，当url过长，如果使用B-Tree时，那么存储的内容就会非常大。新建表如下

create table personhash(
  id int unsigned not null auto_increment,
  url varchar(255) not null,
  url_crc int unsigned not null default 0,
  primary key(id)
)

url存储具体的url，在url_crc上使用一个hash索引，用来专门存储hash值，同时新建一个触发器，在每次添加url时，自动算出哈希值。

create trigger p_crc_ins before insert on personhash for each row begin
set new.url_crc = crc32(new.url)
end;
//使用插入语句
insert into personhash(url) values('https://www.baidu.com');
//查询时处理hash冲突
select * from personhash where url = 'https://www.baidu.com' and crc = CRC32("https://www.baidu.com")

不建议使用md5和sha函数，因为算出来会产生非常大的字符串，浪费空间。

空间数据索引

全文索引

查找的是文本中的关键字，不是直接比较索引的值。
........

索引的优点

减少了服务器需要扫描的的数量
避免排序和减少产生的临时表
将随机IO变成顺序IO

高性能的索引

mysql无法自动解析某些函数，自然就无法使用索引

如select * from acturo where acturid + 1= 5;

前缀索引和索引的选择性

有时候索引很长的字符串列，会让索引变得又大又慢，一种是用hash索引，还有另一种就是索引列的部分字符。
什么是索引的选择性：不重复的所有值和该行记录的总数(n)的比值，范围为 1/n ~ n，之间，索引的选择性越高，则查询效率越高。
对于mysql前缀索引，varchar长度很高的类型的列，必须满足前缀查询，那么如果保证选择性足够高，又能保证索引的长度足够长呢？
决定前缀的合适长度，需要找到最常见的值得列表，然后和最常见的前缀列表进行比较。如city字段

//找到出现次数最大的字段，
select count(*) as cnt,city from xxx group by city order by cnd desc limit 10; 
//使用left 截取，并计算 知道前缀的索引列，解决完整列的索引选择性
select count(*)  as cnt,left (city,3) from xxx group by pref order by cnt desc limit 10;
//计算完整列的索引选择性
select count(distinct city)/count(*) from xxx;
//添加索引
alter table xxx
add index index_city(city(7));

弊端：mysql无法利用前缀索引做order by 和group by操作无法使用前缀索引做覆盖扫描。
好处:索引更小，更快。

多列索引

为每个列都创建独立的索引，或者按照错误的顺序创建多列索引。都是一种错误的方式。
索引合并：当一个表中有多个列，都有索引时，mysql会使用单列索引来定位指定的行，如下在file_actor 中有两个字段,actor_id和film_id上都有索引。

select film_id,actor_id from film_actor where actor_id = 1 or film_id =1;

这条查询语句，在mysql老版本中，会全表扫描！在新版本不会，会先根据actor_id 查询结果，然后在根据film_id查询结果，然后将结果合并起来。
下面两种：
1.OR条件的联合
2.And条件的相交

情况优化：
1.当服务器出现多个索引做and条件时，通常需要建立一个相关列的多列索引，而不是单个索引。
2.当服务器出现多个索引做or条件时，需要消耗大量CPU和内存资源，在缓存，排序和联合操作上。
3.优化器不会关心这些，将这些计算到成本里面，导致该执行计划还不如全表扫描。

在多列索引中，如何选择合适的所有顺序？

将选择性最高的列放在最前列

在一个B-Tree索引中，索引的顺序决定order by 的顺序，group by和Distinct字句的顺序。
如果不考虑排序和分组和范围的条件，以及随机Io,将选择性最高的列放在最前列，那么大部分情况下是最优的。

聚簇索引

概念：并不是一种单纯的索引类型，而是一种数据存储方式。
InnoDb实际上在同一个数据结构中保存了B-tree索引和数据行，聚簇索引的叶子节点，实际上是把相邻的行和键值存储在一起。聚簇索引的叶子节点实际上是数据行。

聚簇索引.png

优点:

把相关数据保存在一起。减少磁盘IO
数据访问更快
使用覆盖索引扫描的查询可以直接使用页节点中的主键值

缺点：

基于主键插入时，如果主键被更新或者需要移动行的时候，可能面临页分裂。
更新的代价会更高，因为强制innodb将每个被更新的行移动到新的位置。
二级索引访问两次索引查找，可能需要查找两次，而不是一次。

myisam和innodb索引和数据分布区别

myisam的主键索引和二级索引和数据分布上没有太大的区别。主键索引和二级索引的叶子节点都存储的是行指针
在innodb中聚簇索引就是表，二级索引的叶子节点存储的不是“行指针”而是主键值。
好处：当出现行移动或者页分裂时，带来的二级索引的维护的工作。innodb在移动时，无需更新二级索引的指针。
坏处：二级索引会占据更多的空间。

InnoDb表顺序插入行

使用UUID来作为聚簇索引会出现什么问题？它是的聚簇索引的插入变得完全随机，使数据没有聚集的特性。主键更长，空间也大。顺序IO变成了随机IO,新插入的值不一定比前面插入的值大，也可能比前面的更小。所以就会导致也分裂的情况。页的最大填充因子是15/16,一旦超过下一条记录就会被写入到新的页。
缺点：

写入的页可能已经被或者没有没写入到磁盘中，写入到磁盘中的页，也会被加载到缓存中，导致大量IO修改。
写入乱序，导致大量的页需要移动，导致页分裂。
页分裂导致数据碎片。

覆盖索引

基本概念：如果一个索引包含所有需要查询的字段的值，我们就称为这个索引为覆盖索引。
好处：

索引的条目小于数据行的大小，sql的响应时间更多的花在拷贝上，更容易放在内存。
索引的值是按照顺序存放的，一次范围查询比随机从磁盘读取每一行数据的io要少的多。
二级主键能够覆盖查询，避免对聚簇索引的二次查找。

使用覆盖索引时，用Explain分析是Extra列，看到Using index的信息。
使用覆盖索引主要的场景：
1.如果使用select * 操作，innodb无法使用覆盖索引，
2.不能在索引中执行Like操作。

通过延迟关联，来优化对列的访问，到达使用覆盖索引的目的

select * from products where actor = 'SEVEN' and title like '%APOLLOG%'；

建立索引key(actor,title,prod_id);
select * from products join (
  select prod_id from products where actor = 'SEVEN' and title like '%APOLLOG%'
) as t on (t.prod_id = products.prod_id);

使用索引扫描来排序

mysql两种方式排序操作：
1.通过操作数据行排序操作，
2.按索引顺序扫描
Explain出来的type列的值为index，说明使用了索引扫描来排序。
使用索引来排序是由条件的：索引列的顺序和order by字句的顺序完全一致时。
order by 字句和查找型查询的限制是一样的，必须满足索引的最左前缀但是，有些时候order 是可以不需要满足的。就是前面的列为常量的时候。

key(rental_date,inventory_id,customer_id)
where rental_date = '2020-01-01' order by inventory_id desc;

第一列提供了常量条件，使用第二列进行排序，组合在一起，就满足了最左前缀。

where rental_date>'2020-05-25' order by rental_date,inventory_id

上面这个也满足索引的最左前缀的要求，order by的前两列就是做做前缀。
下面的情况：不能使用索引做排序的查询

//索引列都是正序排序的
where rental_date = '2020-01-01' order by inventory_id asc ,customer_id desc
//包含了一个不再索引的列
where rental_date = '2020-01-01' order by inventory_id ,staff_id
//不满足索引的最左前缀
where rental_date = '2020-01-01' order by customer_id
//查询在第一列是范围查询条件
where rental_date >'2020-01-01' order by inventory_id
//inventory_id in操作也是范围查询，对于排序来说不会走索引
where rental_date = '2020-01-01' and inventory_id in(1,2) order by customer_id

查询所有的使用频率，通过Information_schema.index_statistics 来查看索引的使用频率。
索引和锁：
使用索引可以较少行锁之间的竞争，减少扫描的行数。减少行锁的数量。Inndo只有在访问行的时候才会对其加锁，而索引减少innodb访问的行数。如果索引无法过滤无效的行，那么innodb在检索到数据返回给服务器层以后，才能应用到Where字句,这时已经无法避免行锁。

select actor_id from actor where actor_id <5 and actor_id != 1 for update;
mysql虽然返回2,3,4但是 同时也锁住了1. 这样的原因是执行计划是索引范围扫描

如果无法使用索引来查询有效行数，那么mysql就会全表扫描并且锁定所有的行，不管是否需要。

索引简单案例

1.实现一个在线陌生人交友网站，用户信息列有：国家，地区，城市，性别，眼睛等等。比如陌陌，
需求：根据性别和国家查询用户信息？索引该怎么建立呢？
根据以往经验，key(sex,country);作为前缀索引，如果我们直接根据国家查询用户信息，那么这个索引就不能用了。
这里有个诀窍：用IN查询，来避免，如何避免？新增and Sex In('m','f') 来让mysql 选择该索引，虽然不会过滤任何行，但是只有这样才能让mysql复合最左前缀。如果In的列表太长，就不适用了。In的组合不能滥用，会以指数的形式增长。
接下来需要考虑常见的where 条件的组合，key(sex,conuntry,age) 上根据性别，国家，年龄条件查询。和key(sex,contry,region,city,age) 性别国家，区域，城市，年龄，这样的组合索引。尽可能重用索引，而不是建立大量的组合索引。
age放在最后面，因为age多半是范围查询，而范围查询会使索引失效。

避免多个范围查询

当mysql出现多个范围查询时，如根据上面的案列，查询过去几个周上线过得用户。

where last_online >DATE_SUB(NOW(),INTERVAL_7_DAY)
and age betwen 18 and 25;

范围条件用Explain分析是type的值为range.
范围查询值和查询列表值？？？？如何区分？？？？从值的范围和多个等于条件来区分，而多个等值查询用In()代替范围,mysql可以使用索引

id:1
select_type:simple
table:actor
type:range

排序优化

如果一个查询，匹配的结果有上百万行，在排序会怎样，通常我们一般会建立索引，通过覆盖索引，然后排序，如一下sql

key(sex,cols);
select  cols from profiles where sex = 'm' order by cols limit 10;

但是如果需要翻页怎么办呢？

select cols from profiles where sex = 'm' order by cols limit 100000,10;

这样查询的效率起始还是特别慢的，尤其是翻到比较后面的页时，不管如何建立索引，Mysq总会扫描大量的数据然后丢弃掉。

优化这种sql关键在于延迟查询

select cols from profiles inner join(
select cols from profiles where x.sex = 'M' order by rating limit 100000,10
) as x

通过覆盖索引查询返回需要的主键，再根据这些主键获得需要的行。