mysql索引、优化、sql性能分析

为什么InnoDB存储引擎选择使用B+tree索引结构?

相对于二叉树，层级更少，搜索效率高
对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
相对Hash索引，B+tree支持范围匹配和排序操作

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

SQL性能分析

1.SQL执行频率

show [globle|session] status

查看数据库访问频次

show globle status like 'Com_______';

2.慢查询日志

在/etc/my.cnf修改配置信息

#开启Mysq慢查询日志开关
show_query_log=1
#设置慢查询日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time = 2

修改完配置记得重启mysql服务
systemctl restart mysqld
 
在/var/lib/mysql/localhost-slow.log查看

3.profile详情

#查看当前Mysql是否支持profile操作：
select @@have_profiling;

#默认profiling是关闭的，可以通过set语句在session/global级别开启profiling
select @@profiling;
set profiling = 1;


#查看每一条sql的耗时基本情况
show profiles

#查看指定query_id 的SQL语句各个阶段的耗时情况
show profile for query query_id

#查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id

4.explain执行计划

explain执行计划各字段的含义：

字段	含义
id	select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行）
select_type	表示select的类型，常见的取值有simple(简单表，即不使用表连接或者子查询)、primary(主查询，即外层的查询)、union（uinon中的第二个或者后面的查询语句）、subquery（select/where之后包含了子查询）等
type	表示连接类型，性能由好到差的连接类型为null、system、const、eq_ref、range、index、all.
possiable_key	显示可能应用在这张表上的索引，一个或多个
key	实际使用的索引，如果为null，则没有使用索引
key_len	表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前体下，长度越短越好
rows	MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是精确的。
filtered	表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。

5.索引使用规则

5.1最左前缀法则

如果索引了多列(联合索引），要遵循最左前缀法则。即查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分失效(后面的部分索引失效)

5.2范围查询(针对联合索引)

联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效（建立索引时，该范围查询列右侧的列失效）

但 >= , <=不失效

5.3索引列运算

不要再索引列上进行运算操作，索引将失效

5.4字符串不加引号

字符串类型字段使用时，不加单引号，索引将失效

5.5模糊查询

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

select _ from t_user where name like '%三' -- 失效
select _ from t_user where name like '张%' -- 不失效

查看索引
show index from t_user
创建索引
create index idx_user_age on t_user(age);
删除索引
drop index idx_user_age on t_user

5.6 or连接的条件

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

5.7数据分布影响

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

is null
is not null 等

5.8sql提示

sql提示，是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些认为的提示来达到优化的目的

use index:建议MySQL使用某个索引
explain select * from t_user use index(idx_user_name) where name = '张三'
ignore index：不要使用某个索引
force index:强制使用某个索引

5.9覆盖索引

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

一张表，有四个字段（id name pwd status),由于数据量大，需要对以下sql语句进行优化，该如何进行优化才是最优方案
select id,name,pwd from t_user where name='zs'
给name和pwd创建一个组合索引即可

5.10前缀索引
当字段类型为字符串(varchar,text等)，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高查询效率。

语法
create index index_xxx on table_name(column(n));
前缀长度
可以跟剧索引的选择性来决定，选择性是指 不重复的索引值(基数) 和 数据表记录总数的 比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email) / count(*) from t_user
select count(distinct substring(email(5)) / count(*) from  t_user

5.11单列索引和联合索引

单列索引：即一个索引只包含单个列

联合索引：即一个索引包含了多个列

在业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引，而非单列索引

6.索引设计原则

1.针对数据量较大，且查询比较频繁的表建立索引
2.针对于常作为条件(where),排序(order by),分组(group by)操作的字段建立索引
3.尽量选择区分度搞得列作为索引，尽量建立唯一索引，区分度越高，使用索引得效率越高
4.如果时字符串类型的字段，字段得长度较长，可以针对字段的特点，建立前缀索引。
5.尽量使用联合使索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节约存储空间，避免回表，提高查询效率
6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引的等价也就越大，会影响增删改的效率。
7.如果索引列不能存储null值，请在创建表时使用NOT　NULL约束它，当优化器知道每列是否包含null值时，它可以更好的确定哪个索引最有效地用于查询

sql优化

1.插入数据

insert优化

批量插入
insert into t_user values(1,'tom'),(2,'Cat'),(3,'Jerry');
手动提交事务
start transaction;
insert into t_user values(1,'tom'),(2,'Cat'),(3,'Jerry');
insert into t_user values(4,'tom'),(5,'Cat'),(6,'Jerry');
commit;
主键顺序插入

大批量插入数据
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。

#客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
#执行load指令将准备好的数据，加载到表结构中
load data localinfile '/root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';

2.主键优化

数据组织方式

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

TableSpece:表空间
Segment:段
Extent:区 1M
Page:页 16K
Row:行

页分裂
页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列
页合并
当删除一行记录时，实际上并没有被物理删除，只是被标记(flaged)为删除并且它的空间变得允许被其他记录声明引用。
当野种删除的记录达到MERGE_THRESHOLD（默认为页的50%）,Innodb会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化

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

主键设计原则：
满足业务需求的情况下 ，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者时其他自然主键，如身份证
业务操作时，避免对主键的修改。

3.order by优化

(1)Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
(2)Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,不需要额外排序，操作效率高

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(asc/desc)
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size（m默认256K）

4.group by优化

在分组操作时，可以通过索引来提高效率

分组操作时，索引的使用也是满足最左前缀法则的。

5.limit优化

通过覆盖索引加子查询

mysql不支持在in后面用limit

优化思路：一般分页查询时，通过创建 覆盖索引能够比较好的提高性能，也可以通过覆盖索引加子查询的形式进行优化
explain select * from tb_user a, (select id from tb_user order by id limit 10,20) b where a.id = b.id