information_schema 数据库相当于一个数据字典，保存了表的元信息。
mysql> use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------------------------------+| Tables_in_information_schema |+---------------------------------------+| CHARACTER_SETS || COLLATIONS || COLLATION_CHARACTER_SET_APPLICABILITY || COLUMNS || COLUMN_PRIVILEGES || ENGINES || EVENTS || FILES || GLOBAL_STATUS || GLOBAL_VARIABLES || KEY_COLUMN_USAGE || OPTIMIZER_TRACE || PARAMETERS || PARTITIONS || PLUGINS || PROCESSLIST || PROFILING || REFERENTIAL_CONSTRAINTS || ROUTINES || SCHEMATA || SCHEMA_PRIVILEGES || SESSION_STATUS || SESSION_VARIABLES || STATISTICS || TABLES || TABLESPACES || TABLE_CONSTRAINTS || TABLE_PRIVILEGES || TRIGGERS || USER_PRIVILEGES || VIEWS || INNODB_LOCKS || INNODB_TRX || INNODB_SYS_DATAFILES || INNODB_FT_CONFIG || INNODB_SYS_VIRTUAL || INNODB_CMP || INNODB_FT_BEING_DELETED || INNODB_CMP_RESET || INNODB_CMP_PER_INDEX || INNODB_CMPMEM_RESET || INNODB_FT_DELETED || INNODB_BUFFER_PAGE_LRU || INNODB_LOCK_WAITS || INNODB_TEMP_TABLE_INFO || INNODB_SYS_INDEXES || INNODB_SYS_TABLES || INNODB_SYS_FIELDS || INNODB_CMP_PER_INDEX_RESET || INNODB_BUFFER_PAGE || INNODB_FT_DEFAULT_STOPWORD || INNODB_FT_INDEX_TABLE || INNODB_FT_INDEX_CACHE || INNODB_SYS_TABLESPACES || INNODB_METRICS || INNODB_SYS_FOREIGN_COLS || INNODB_CMPMEM || INNODB_BUFFER_POOL_STATS || INNODB_SYS_COLUMNS || INNODB_SYS_FOREIGN || INNODB_SYS_TABLESTATS |+---------------------------------------+61 rows in set (0.00 sec)
查看索引信息。
mysql> select * from key_column_usage limit 3\G -- 显示了哪个索引使用了哪个列*************************** 1. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: burn_testCONSTRAINT_NAME: PRIMARYTABLE_CATALOG: defTABLE_SCHEMA: burn_testTABLE_NAME: Orders -- 表名COLUMN_NAME: order_id -- 索引的名称ORDINAL_POSITION: 1POSITION_IN_UNIQUE_CONSTRAINT: NULLREFERENCED_TABLE_SCHEMA: NULLREFERENCED_TABLE_NAME: NULLREFERENCED_COLUMN_NAME: NULL*************************** 2. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: burn_testCONSTRAINT_NAME: product_nameTABLE_CATALOG: defTABLE_SCHEMA: burn_testTABLE_NAME: Orders_MVCOLUMN_NAME: product_nameORDINAL_POSITION: 1POSITION_IN_UNIQUE_CONSTRAINT: NULLREFERENCED_TABLE_SCHEMA: NULLREFERENCED_TABLE_NAME: NULLREFERENCED_COLUMN_NAME: NULL*************************** 3. row ***************************CONSTRAINT_CATALOG: defCONSTRAINT_SCHEMA: burn_testCONSTRAINT_NAME: child_ibfk_1TABLE_CATALOG: defTABLE_SCHEMA: burn_testTABLE_NAME: childCOLUMN_NAME: parent_idORDINAL_POSITION: 1POSITION_IN_UNIQUE_CONSTRAINT: 1REFERENCED_TABLE_SCHEMA: burn_testREFERENCED_TABLE_NAME: parentREFERENCED_COLUMN_NAME: id3 rows in set (0.04 sec)
可以通过 STATISTICS 表的数据信息，分析索引的 Cardinality 数据。
---- 在 information_schema.STATISTICS 中记录了相关的信息--mysql> use information_schema;Database changedmysql> show create table STATISTICS\G*************************** 1. row ***************************Table: STATISTICSCreate Table: CREATE TEMPORARY TABLE `STATISTICS` (`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', -- 表所在的库`TABLE_NAME` varchar(64) NOT NULL DEFAULT '', -- 表名`NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',`INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',`INDEX_NAME` varchar(64) NOT NULL DEFAULT '', -- 索引名`SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0', -- 索引的序号`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',`COLLATION` varchar(1) DEFAULT NULL,`CARDINALITY` bigint(21) DEFAULT NULL, -- 这里我们找到了Cardinality`SUB_PART` bigint(3) DEFAULT NULL,`PACKED` varchar(10) DEFAULT NULL,`NULLABLE` varchar(3) NOT NULL DEFAULT '',`INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',`COMMENT` varchar(16) DEFAULT NULL,`INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT '') ENGINE=MEMORY DEFAULT CHARSET=utf81 row in set (0.00 sec)
比如我们查看 employees.salaries 表中的索引信息：
---- 之前我们可以通过 show index from table_name的方式查看索引--mysql> show index from employees.salaries\G*************************** 1. row ***************************Table: salariesNon_unique: 0Key_name: PRIMARYSeq_in_index: 1 -- 索引序号为1Column_name: emp_noCollation: ACardinality: 286271 -- Cardinality值Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:*************************** 2. row ***************************Table: salariesNon_unique: 0Key_name: PRIMARYSeq_in_index: 2 -- 索引序号为2Column_name: from_dateCollation: ACardinality: 2760952 -- Cardinality值Sub_part: NULLPacked: NULLNull:Index_type: BTREEComment:Index_comment:2 rows in set (0.00 sec)---- 现在可以通过STATISTICS表查看某张表的信息--mysql> select * from STATISTICS where table_name='salaries'\G*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: employeesTABLE_NAME: salariesNON_UNIQUE: 0INDEX_SCHEMA: employeesINDEX_NAME: PRIMARYSEQ_IN_INDEX: 1 -- 索引序号为1COLUMN_NAME: emp_noCOLLATION: ACARDINALITY: 286271 -- Cardinality值SUB_PART: NULLPACKED: NULLNULLABLE:INDEX_TYPE: BTREECOMMENT:INDEX_COMMENT:*************************** 2. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: employeesTABLE_NAME: salariesNON_UNIQUE: 0INDEX_SCHEMA: employeesINDEX_NAME: PRIMARYSEQ_IN_INDEX: 2 -- 索引序号为2COLUMN_NAME: from_dateCOLLATION: ACARDINALITY: 2760952 -- Cardinality值SUB_PART: NULLPACKED: NULLNULLABLE:INDEX_TYPE: BTREECOMMENT:INDEX_COMMENT:2 rows in set (0.00 sec)------ 可以看出，上面两个方法得到的Cardinality的值是相等
通过 information_schema 数据库可以方便的分析表的元信息。
检查表的索引创建的情况，判断该索引是否有创建的必要。
---- 1. 表的信息如table_schema, table_name, table_rows等-- 在information_schema.TABLES中--mysql> show create table TABLES\G*************************** 1. row ***************************Table: TABLESCreate Table: CREATE TEMPORARY TABLE `TABLES` (`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', -- 表所在的库`TABLE_NAME` varchar(64) NOT NULL DEFAULT '', -- 表名`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',`ENGINE` varchar(64) DEFAULT NULL,`VERSION` bigint(21) unsigned DEFAULT NULL,`ROW_FORMAT` varchar(10) DEFAULT NULL,`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL, -- 表的记录数`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,`DATA_FREE` bigint(21) unsigned DEFAULT NULL,`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,`CREATE_TIME` datetime DEFAULT NULL,`UPDATE_TIME` datetime DEFAULT NULL,`CHECK_TIME` datetime DEFAULT NULL,`TABLE_COLLATION` varchar(32) DEFAULT NULL,`CHECKSUM` bigint(21) unsigned DEFAULT NULL,`CREATE_OPTIONS` varchar(255) DEFAULT NULL,`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT '') ENGINE=MEMORY DEFAULT CHARSET=utf81 row in set (0.00 sec)---- 2.information.STATISTICS 中存在 table_schema 和 table_name 信息--mysql> show create table STATISTICS\G*************************** 1. row ***************************Table: STATISTICSCreate Table: CREATE TEMPORARY TABLE `STATISTICS` (`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '', -- 表所在的库`TABLE_NAME` varchar(64) NOT NULL DEFAULT '', -- 表名`NON_UNIQUE` bigint(1) NOT NULL DEFAULT '0',`INDEX_SCHEMA` varchar(64) NOT NULL DEFAULT '',`INDEX_NAME` varchar(64) NOT NULL DEFAULT '', -- 索引名`SEQ_IN_INDEX` bigint(2) NOT NULL DEFAULT '0',`COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',`COLLATION` varchar(1) DEFAULT NULL,`CARDINALITY` bigint(21) DEFAULT NULL,`SUB_PART` bigint(3) DEFAULT NULL,`PACKED` varchar(10) DEFAULT NULL,`NULLABLE` varchar(3) NOT NULL DEFAULT '',`INDEX_TYPE` varchar(16) NOT NULL DEFAULT '',`COMMENT` varchar(16) DEFAULT NULL,`INDEX_COMMENT` varchar(1024) NOT NULL DEFAULT '') ENGINE=MEMORY DEFAULT CHARSET=utf81 row in set (0.00 sec)---- 3. 将TABLES 和 STATISTICS 表中的table_schema和table_name相关联-- 通过Cardinality和table_rows 计算，即可得到对应索引名的选择性------ 3.1 因为存在复合索引，所以我们要取出复合索引中seq最大的哪个值-- 这样取出的cardinality值才是最大的--mysql> select-> table_schema, table_name, index_name,-> max(seq_in_index) -- 取出最大的seq号后，选出index_name等信息-> from-> STATISTICS-> group by table_schema , table_name , index_name\G-- -----------省略其他输出-----------*************************** 10. row ***************************table_schema: burn_testtable_name: test_index_2index_name: idx_mul_ab -- 这个是上次测试复合索引建立的indexmax(seq_in_index): 2 -- 取出了最大的seq-- -----------省略其他输出--------------- 3.2得到了最大的seq，从而可以取出对应的cardinality--mysql> select-> table_schema, table_name, index_name, cardinality-> from-> STATISTICS-> where-> (table_schema , table_name, index_name, seq_in_index) in-> (select-> table_schema, table_name,-> index_name, max(seq_in_index)-> from-> STATISTICS-> group by table_schema , table_name , index_name)\G*************************** 1. row ***************************table_schema: burn_testtable_name: Ordersindex_name: PRIMARYcardinality: 5*************************** 2. row ***************************table_schema: burn_testtable_name: Orders_MVindex_name: product_namecardinality: 3*************************** 3. row ***************************table_schema: burn_testtable_name: childindex_name: par_indcardinality: 0*************************** 4. row ***************************table_schema: burn_testtable_name: parentindex_name: PRIMARYcardinality: 1*************************** 5. row ***************************table_schema: burn_testtable_name: t4index_name: PRIMARYcardinality: 4-- -----------省略其他输出--------------- 3.3 最后通过table_schema和table_name 让上述的信息和TABLES表进行关联--SELECTt.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, TABLE_ROWS,CARDINALITY/TABLE_ROWS AS SELECTIVITY -- 得到选择性FROMTABLES t, -- 查询的表一，TABLES(SELECTtable_schema,table_name,index_name,cardinalityFROM STATISTICSWHERE (table_schema,table_name,index_name,seq_in_index) IN (SELECTtable_schema,table_name,index_name,MAX(seq_in_index)FROMSTATISTICSGROUP BY table_schema , table_name , index_name )) s -- 查询的表二，就是上面3.2的查询结果WHEREt.table_schema = s.table_schema -- 通过库关联AND t.table_name = s.table_name -- 再通过表变量AND t.table_schema = 'employees' -- 指定某一个库名ORDER BY SELECTIVITY;+--------------+--------------+------------+-------------+------------+------------+| TABLE_SCHEMA | TABLE_NAME | index_name | cardinality | TABLE_ROWS | SELECTIVITY |+--------------+--------------+------------+-------------+------------+------------+| employees | dept_emp | dept_no | 8 | 330400 | 0.0000 || employees | salaries | PRIMARY | 286271 | 2760952 | 0.1037 || employees | dept_manager | dept_no | 9 | 24 | 0.3750 || employees | titles | PRIMARY | 296887 | 440887 | 0.6734 || employees | dept_emp | PRIMARY | 298761 | 330400 | 0.9042 || employees | titles | PRIMARY | 440166 | 440887 | 0.9984 || employees | salaries | PRIMARY | 2760952 | 2760952 | 1.0000 || employees | dept_manager | PRIMARY | 24 | 24 | 1.0000 || employees | titles | PRIMARY | 440887 | 440887 | 1.0000 || employees | departments | PRIMARY | 9 | 9 | 1.0000 || employees | employees | PRIMARY | 298124 | 298124 | 1.0000 || employees | dept_emp | PRIMARY | 330400 | 330400 | 1.0000 || employees | dept_manager | PRIMARY | 24 | 24 | 1.0000 || employees | departments | dept_name | 9 | 9 | 1.0000 |+--------------+--------------+------------+-------------+------------+------------+---- 通过最后一列的SELECTIVITY是否接近1，判断该索引创建是否合理-- 注意：-- Cardinality和table_rows的值，都是通过随机采样，预估得到的-- 当analyze前后，Cardinality值相差较多，说明该索引是不应该被创建的(页上的记录数值分布不平均)---- 推荐 SELECTIVITY 15% 以上是适合的---- 索引使用情况--mysql> select * from x$schema_index_statistics limit 1\G*************************** 1. row ***************************table_schema: employeestable_name: employeesindex_name: PRIMARY -- 索引名字rows_selected: 300024 -- 读取的记录数select_latency: 370177723990 -- 使用该索引读取时总的延迟时间370毫秒（单位是皮秒）rows_inserted: 0 -- 插入的行数insert_latency: 0rows_updated: 0 -- 更新的行数update_latency: 0rows_deleted: 0delete_latency: 01 row in set (0.00 sec)-- 结合之前的SELECTIVITY和这里的数值，可以更好的判断索引是否合理-- 重启后数据归0
索引是要排序的，建立索引越多，排序以及维护成本会很大，插入数据的速度会变慢，所以索引建立的多，不是仅仅是浪费空间，还会降低性能，增加磁盘 IO。
注意：MySQL 5.6 的版本 STATISTICS 数据存在问题，截止 5.6.28 仍然存在，官方定性为 Bug。在 MySQL 5.6 中使用 mysql.innodb_index_stats 得到索引的选择性（SELECTIVITY）。
通过 information_schema 数据库，找出用户的表中没有创建主键的表。
mysql> desc information_schema.TABLES; -- 表的元信息+-----------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-----------------+---------------------+------+-----+---------+-------+| TABLE_CATALOG | varchar(512) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | | -- DB 的名字| TABLE_NAME | varchar(64) | NO | | | | -- TABLE的名字| TABLE_TYPE | varchar(64) | NO | | | || ENGINE | varchar(64) | YES | | NULL | || VERSION | bigint(21) unsigned | YES | | NULL | || ROW_FORMAT | varchar(10) | YES | | NULL | || TABLE_ROWS | bigint(21) unsigned | YES | | NULL | || AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | || DATA_LENGTH | bigint(21) unsigned | YES | | NULL | || MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | || INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | || DATA_FREE | bigint(21) unsigned | YES | | NULL | || AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | || CREATE_TIME | datetime | YES | | NULL | || UPDATE_TIME | datetime | YES | | NULL | || CHECK_TIME | datetime | YES | | NULL | || TABLE_COLLATION | varchar(32) | YES | | NULL | || CHECKSUM | bigint(21) unsigned | YES | | NULL | || CREATE_OPTIONS | varchar(255) | YES | | NULL | || TABLE_COMMENT | varchar(2048) | NO | | | |+-----------------+---------------------+------+-----+---------+-------+21 rows in set (0.00 sec)mysql> desc information_schema.COLUMNS; -- 每个列的元信息+--------------------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------------------------+---------------------+------+-----+---------+-------+| TABLE_CATALOG | varchar(512) | NO | | | || TABLE_SCHEMA | varchar(64) | NO | | | | -- DB的名字| TABLE_NAME | varchar(64) | NO | | | | -- TABLE的名字| COLUMN_NAME | varchar(64) | NO | | | || ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | || COLUMN_DEFAULT | longtext | YES | | NULL | || IS_NULLABLE | varchar(3) | NO | | | || DATA_TYPE | varchar(64) | NO | | | || CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | || CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | || NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | || NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | || DATETIME_PRECISION | bigint(21) unsigned | YES | | NULL | || CHARACTER_SET_NAME | varchar(32) | YES | | NULL | || COLLATION_NAME | varchar(32) | YES | | NULL | || COLUMN_TYPE | longtext | NO | | NULL | || COLUMN_KEY | varchar(3) | NO | | | | -- 列的索引| EXTRA | varchar(30) | NO | | | || PRIVILEGES | varchar(80) | NO | | | || COLUMN_COMMENT | varchar(1024) | NO | | | || GENERATION_EXPRESSION | longtext | NO | | NULL | |+--------------------------+---------------------+------+-----+---------+-------+21 rows in set (0.01 sec)-- 这里使用TABLES中的table_schema，table_name 与 COLUMNS 中的table_schema，table_name做一次关联子查询-- 不直接用COLUMNS中的table_schema和table_name是因为表中会含有多个列，-- 这样COLUMNS表中的记录（Row）的table_schema和table_name会存在重复值，需要再次使用DISTINCT-- 在MySQL中执行该语句SELECTtable_schema, table_nameFROMinformation_schema.TABLES --WHEREtable_name NOT IN (SELECT DISTINCTTABLE_NAMEFROMinformation_schema.COLUMNSWHERECOLUMN_KEY = 'PRI')AND table_schema NOT IN ('mysql' , 'information_schema','sys', 'performance_schema');+--------------+----------------------+| table_schema | table_name |+--------------+----------------------+| burn_test | child || burn_test | comps_test1 || burn_test | comps_test2 || burn_test | t1 || burn_test | t2 || burn_test | t3 || burn_test | t5 || burn_test | test_1 || burn_test | test_222 || burn_test | test_ger1 || burn_test | test_ger2 || burn_test | test_ger3 || burn_test | test_index_1 || burn_test | test_index_2 || burn_test | test_left_join_1 || burn_test | test_left_join_2 || burn_test | test_proc_1 || burn_test | test_rank || burn_test | test_rank_2 || burn_test | test_union_1 || burn_test | test_union_2 || burn_test | view_rank || burn_test | view_rank_1 || dbt3 | time_statistics || employees | current_dept_emp || employees | dept_emp_latest_date |+--------------+----------------------+26 rows in set (0.06 sec)-- 上述查询的结果就是数据库中没有主键的表
作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/ldtgin 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
若有收获，就点个赞吧
0 人点赞
