MySQL如何找出没有索引或主键的表

MySQL中如何找出没有主键的表或者没有索引的表呢？有时候，我们在管理、维护MySQL时，可能经常遇到这方面的需求，下面分享几个脚本，以便不时之需。

如何找出没有索引的用户表，下面三个脚本基本上能满足你的各种需求了：

找出MySQL所有用户数据库中没有索引的表

/*-**************************************************************************************************
--  Script Name     ：   find_without_index_tables.sql    
--  Script Funtion  :    找出MySQL所有用户数据库中没有索引的表
****************************************************************************************************/
SELECT t.table_schema
      ,t.table_name
      ,t.table_type
      ,t.engine
      ,t.table_rows
      ,t.row_format
      ,t.avg_row_length
      ,t.create_time
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
    ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE t.table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'ndbinfo') 
  AND s.index_name IS NULL;

找出指定数据库中没有索引的表

/*-**************************************************************************************************
--  Script Name     ：   find_without_index_tables.sql    
--  Script Funtion  :    找出指定数据库中没有索引的表
****************************************************************************************************/
set @db_name = 'kerry';
SELECT t.table_schema
      ,t.table_name
      ,t.table_type
      ,t.engine
      ,t.table_rows
      ,t.row_format
      ,t.avg_row_length
      ,t.create_time
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
    ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE t.table_schema=@db_name
  AND s.index_name IS NULL;

下面这个脚本加入了查询条件，找出表的记录数超过500，并且没有索引的表。因为，有时候数据库存在一些小表，可能只有 十几条或几十条记录，这些表没有索引也是合理和客观的。有时候可以忽略。

/*-**************************************************************************************************
--  Script Name     ：   find_without_index_tables.sql    
--  Script Funtion  ：   查询记录数超过500，没有索引的表。table_rows是个近似值，数据不精确，仅供参考
--                       MyISAM存储引擎的表值是准确的。          
****************************************************************************************************/
SELECT t.table_schema
      ,t.table_name
      ,t.table_type
      ,t.engine
      ,t.table_rows
      ,t.row_format
      ,t.avg_row_length
      ,t.create_time
FROM information_schema.tables t
LEFT JOIN information_schema.statistics s
    ON t.table_schema = s.table_schema AND t.table_name = s.table_name
WHERE t.table_schema NOT IN ( 'mysql', 'information_schema', 'performance_schema', 'sys', 'ndbinfo') 
  AND s.index_name IS NULL
  AND t.table_rows >500;

下面几个SQL，用来找出没有主键的表，基本上能满足日常的大多数需求。

找出特定数据库中没有主键的表

/*-**************************************************************************************************
--  Script Name     ：   find_without_pk_tables.sql    
--  Script Funtion  ：   找出特定数据库中没有主键的表。          
****************************************************************************************************/

set @db_name = 'kerry';
SELECT
    t.table_schema,
    t.table_name
FROM
    information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c ON t.table_schema = c.TABLE_SCHEMA
AND t.table_name = c.TABLE_NAME
AND c.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    c.table_name IS NULL
AND t.table_type = 'BASE TABLE'
AND t.TABLE_SCHEMA =@db_name;

找出实例下所有用户数据库中没有主键的表。

/*-**************************************************************************************************
--  Script Name     ：   find_without_pk_tables.sql    
--  Script Funtion  ：   找出实例下所有用户数据库中没有主键的表。          
****************************************************************************************************/
SELECT
    t.table_schema,
    t.table_name
FROM
    information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c ON t.table_schema = c.TABLE_SCHEMA
AND t.table_name = c.TABLE_NAME
AND c.CONSTRAINT_NAME IN ('PRIMARY')
WHERE
    c.table_name IS NULL
AND t.table_type = 'BASE TABLE'
AND t.TABLE_SCHEMA NOT IN (
    'information_schema',
    'performance_schema',
    'mysql',
    'sys'
);

找出实例下所有用户数据库中没有主键的表,这个SQL跟上面SQL的区别在于它使用的是视图information_schema.key_column_usage

/*-**************************************************************************************************
--  Script Name     ：   find_without_pk_tables.sql    
--  Script Funtion  ：   找出实例下所有用户数据库中没有主键的表          
****************************************************************************************************/
SELECT t.table_schema,
       t.table_name,
       k.constraint_name,
       k.column_name
FROM information_schema.tables t
     LEFT JOIN information_schema.key_column_usage k
          ON t.table_schema = k.table_schema
             AND t.table_name = k.table_name
             AND k.constraint_name = 'PRIMARY'
 WHERE t.table_schema NOT IN ( 'mysql', 'information_schema',
                               'sys','performance_schema' )
       AND k.constraint_name IS NULL
       AND t.table_type = 'BASE TABLE';