不管是开发人员,仍是数据库维护人员,给表建立索引是很常见的事情。普通到什么程度,初级,中级开发人员的印象中,只要写的sql语句效率不高,第一反应就是建索引,而无论这个字段是否适合建立索引。反正只要给字段建立好索引,应用就能跑的嗖嗖的,索引就是银弹,就是灵丹妙药。mysql
案例
在某个项目现场的数据库中,开发人员给一张业务表的全部字段都建立上了索引。因而就问,为何建立这么索引。开发说,我不懂sql怎么优化,碰到慢了,就建立索引。sql
因此在接手一个新库时,了解哪些索引不合理,低效是颇有必要的一件事情。数据库
高效索引特色
索引必须建立在索引选择性较高的列上,选择性的计算方式为ide
select count(distinct(col_name))/count(*) from tb_name
若是结果小于0.2,则不建议在此列上建立索引,不然大几率会拖慢SQL执行优化
查询低效索引
能够根据高效索引特色,即选择性小于0.2的列上建立索引是低效的,能够经过如下sql语句查询到低效索引code
mysql> SELECT
-> t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME,CARDINALITY,
-> TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
-> FROM
-> information_schema.TABLES t,
-> (
-> SELECT table_schema,table_name,index_name,cardinality
-> FROM information_schema.STATISTICS
-> WHERE (table_schema,table_name,index_name,seq_in_index) IN (
-> SELECT table_schema,table_name,index_name,MAX(seq_in_index)
-> FROM information_schema.STATISTICS
-> GROUP BY table_schema , table_name , index_name )
-> ) s
-> WHERE
-> t.table_schema = s.table_schema
-> AND t.table_name = s.table_name AND t.table_rows != 0
-> AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema','sys')
-> and index_name!='PRIMARY'
-> and CARDINALITY/TABLE_ROWS <=0.2
-> ORDER BY SELECTIVITY;
+--------------+------------+------------+-------------+------------+-------------+
| TABLE_SCHEMA | TABLE_NAME | index_name | cardinality | TABLE_ROWS | SELECTIVITY |
+--------------+------------+------------+-------------+------------+-------------+
| sbtest | sbtest1 | k_1 | 13167 | 79937 | 0.1647 |
+--------------+------------+------------+-------------+------------+-------------+
1 row in set (0.09 sec)
从上面结果就找到一个低效索引(k_1),经过下面的命令,能够更详细的获取索引信息orm
mysql> show index from sbtest.sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sbtest1 | 0 | PRIMARY | 1 | id | A | 79937 | NULL | NULL | | BTREE | | |
| sbtest1 | 1 | k_1 | 1 | k | A | 13167 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)