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
6.count优化
按照效率排序的话，count(字段) <count(主键id) < count(1) ≈ count(* ),所以尽量使用count(*)
7.update优化
在执行updte语句的时候，尽量根据索引字段进行更新，否则就会出现行锁升级为表锁的情况，锁表之后，并发性能就会降低
Innodb的行锁时针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会行锁升级为表锁
