mysql的information_schema和sys中索引应用

• informatio_schema 元数据信息
• performance_schema 资源的监控
• sys 对监控的数据进行整理，便于阅读

1.大有用处的STATISTICS和TABLES

STATISTICS：用于存放索引的信息

• TABLE_SCHEMA：存放数据库的名称
• TABLE_NAME：存放数据表的名称
• INDEX_NAME：存放索引的名称
• COLUMN_NAME：存放字段的名称
• SEQ_IN_INDEX：字段在索引中序列位置
• COLLATION：字段在索引中的排序 A升序 D降序
• CARDINALITY：估算索引中唯一值的数量

TABLES：用于存放库表的元数据信息

• TABLE_SCHEMA：存放数据库的名称
• TABLE_NAME：存放数据表的名称
• INDEX_NAME：存放索引的名称
• COLUMN_NAME：存放字段的名称
• TABLE_ROWS：表的行数统计
• AVG_ROW_LENGTH：平均行的大小
• DATA_LENGTH：表数据的大小
• INDEX_LENGTH：表中索引的大小

2判断索引创建是否合理

①从大量的数据中获取小部分数据，这样的索引才是合理

a=i_s.statistic.cardinality

select table_name,CARDINALITY from statistics where table_schema='dbt3' and table_name='orders' and index_name='i_o_orderDATE';

b=i_s.tables.tables_rows

select table_name,table_rows from tables where table_schema='dbt3' and table_name='orders';

a/b < 10%,则索引创建不合理

②索引创建的建议

。哪些字段不建议创建索引

• 字段的重复值比较多
• 字段经常更新
• 不经常查询的字段

。哪些字段建议创建索引

• where子句
• order by 子句
• group by 子句
• 聚合函数-统计最大值最小值
• 作为关联条件
• 经常查询的字段

3检查联合索引创建是否合理

(a,b):先找出联合索引中最左边的字段，然后通过这个字段，判断选择性

alter table orders add index i_key_date(o_orderkey, o_orderDATE);

①最左边的字段：o_orderkey

select index_name,column_name,seq_in_index from statistics where table_schema='dbt3' and table_name='orders' and index_name='i_key_date';

②对最左边的字段判断选择性

a=i_s.statistic.cardinality

select table_name,CARDINALITY from statistics where table_schema='dbt3' and table_name='orders' and index_name='i_key_date';

b=i_s.tables.tables_rows

select table_name,table_rows from tables where table_schema='dbt3' and table_name='orders';

a/b < 10%,则索引创建不合理

4有了联合索引(a,b),还需要单独创建a索引吗？

索引的应用：查找，排序，利用联合索引就可以满足查找和排序，就不用去单独创建a索引

辅助索引：键值和主键值；(name,id)

select * from t where name='ergou' order by id; // 可以单独创建id索引

5如何查找冗余索引

sys.schema_redundant_indexes

select * from schema_redundant_indexes where table_schema='dbt3' and table_name='orders' \G;

6如何查找产生额外排序的sql语句

①产生额外排序的sql语句会带来哪些影响

• 会造成sql语句性能下降

②什么情况下会产生额外排序

select * from t order by a desc; 5.7默认升序 8.0支持降序排序

③怎么统计产生额外排序的sql语句

sys.statements_with_sorting

select * from statements_with_sorting where db='dbt3' \G;

7如何查找产生临时表的sql语句

①临时表：

• 外部临时表：create temporay table 只对当前会话有效，关闭连接后直接删除
• 内部临时表：sql语句执行过程中产生的临时表

internal_tmp_disk_storage_engine=innodb

②临时表创建过程

• 先在内存中创建临时表，当超过max_heap_table_size时，就会转为基于磁盘的临时表
• 多表查询时有排序，则会先创建临时表，再在临时表中进行排序

③统计产生临时表的sql语句

sys.statements_with_temp_tables

select * from statements_with_temp_tables \G

8全表扫描带来的影响及统计产生全表扫描的sql语句

①产生全表扫描的sql语句会给我们带来哪些不好的影响

• 产生全表扫描的sql语句性能非常非常低
• 污染buffer pool中的数据

②统计产生全表扫描的sql

sys.statements_with_full_table_scans

select * from statements_with_full_table_scans \G;

9统计无用的索引

sys.schema_unused_indexes

select * from schema_unused_indexes \G;

10索引统计信息

①怎么样存储索引统计信息

• innodb_stats_persistent | ON 持久化存储索引统计信息
• innodb_stats_auto_recalc | ON 自动更新索引统计信息，当我们修改的数据超过10%数据量
• innodb_stats_method | nulls_equal 索引统计信息针对null值的算法
  • nulls_equal 把所有的null值看成一个
  • nulls_unequal 每一个null值被视为一个
  • nulls_ignored null值会被忽略
• innodb_stats_on_metadata | OFF 开启后，访问数据库的监控，都会更新索引的统计信息（不能开启）

②如何查看索引统计信息

• show index from orders;
• i_s.statistics
• mysql.innodb_index_stats