mysql> explain select a.* from citi_column_content as a inner join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc;
mysql> explain select a.* from citi_column_content as a inner join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%'; +----+-------------+-------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+----------------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+----------------------------+------+--------------------------+ | 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 37 | Using where; Using index | | 1 | SIMPLE | a | ref | article_n_01,article_n_04,article_n_05,article_n_06(Deprecated),article_n_07(Deprecated) | article_n_07(Deprecated) | 35 | citi_db.b.code,const,const | 1305 | Using where | +----+-------------+-------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+----------------------------+------+--------------------------+07这个索引就是column_code check_flag delete_flag
mysql> explain select a.* from citi_column_content as a inner join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc; +----+-------------+-------+-------+---------------+-------------+---------+----------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+-------------+---------+----------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 32 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | a | ref | column_code | column_code | 31 | citi_db.b.code | 260 | Using where | +----+-------------+-------+-------+---------------+-------------+---------+----------------+------+-----------------------------------------------------------+索引全部干掉了，就剩2个外键
show index in citi_column_content; show index in citi_column;
citi_column_content:column_code id上建立复合索引 create index rr on citi_column_content(column_code,id)
mysql> explain select a.* from citi_column_content as a join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc; +----+-------------+-------+-------+----------------+-------------+---------+----------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+-------------+---------+----------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 32 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | a | ref | column_code,rr | column_code | 31 | citi_db.b.code | 260 | Using where | +----+-------------+-------+-------+----------------+-------------+---------+----------------+------+-----------------------------------------------------------+ 2 rows in setmysql> explain select a.* from citi_column_content as a use index(`rr`) join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc; +----+-------------+-------+-------+---------------+---------+---------+----------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+----------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 32 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | a | ref | rr | rr | 31 | citi_db.b.code | 263 | Using where | +----+-------------+-------+-------+---------------+---------+---------+----------------+------+-----------------------------------------------------------+ 2 rows in set
create index xxx on citi_column_content (column_code,check_flag,delete_flag,id)
+----+-------------+-------+-------+-----------------------------------------------------------------+--------------------------+---------+----------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------------------------------------------------------+--------------------------+---------+----------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 37 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | article_n_01,article_n_04,article_n_05,article_n_06(Deprecated) | article_n_06(Deprecated) | 33 | citi_db.b.code,const | 3771 | Using where |
+----+-------------+-------+-------+-----------------------------------------------------------------+--------------------------+---------+----------------------+------+-----------------------------------------------------------+mysql> show index in citi_column;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| citi_column | 0 | PRIMARY | 1 | code | A | 677 | NULL | NULL | | BTREE | |
| citi_column | 0 | code | 1 | code | A | 677 | NULL | NULL | | BTREE | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+mysql> show index in citi_column_content;
+---------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| citi_column_content | 0 | PRIMARY | 1 | id | A | 315463 | NULL | NULL | | BTREE | |
| citi_column_content | 1 | article_n_01 | 1 | column_code | A | 8 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_02 | 1 | writer_id | A | 5 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_03 | 1 | keywords | A | 15 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 1 | column_code | A | 8 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 2 | check_flag | A | 8 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 3 | delete_flag | A | 8 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 4 | id | A | 315463 | NULL | NULL | | BTREE | |
| citi_column_content | 1 | article_n_05 | 1 | column_code | A | 13 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_05 | 2 | check_flag | A | 13 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_05 | 3 | delete_flag | A | 13 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_05 | 4 | issue_time | A | 315463 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_06(Deprecated) | 1 | column_code | A | 8 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_06(Deprecated) | 2 | check_flag | A | 83 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_06(Deprecated) | 3 | id | A | 315463 | NULL | NULL | | BTREE | |
| citi_column_content | 1 | article_n_07(Deprecated) | 1 | issue_time | A | 315463 | NULL | NULL | YES | BTREE | |
+---------------------+------------+--------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
mysql> desc citi_column;
+------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| code | char(10) | NO | PRI | | |
| column_name | char(40) | YES | | NULL | |
| title | char(30) | YES | | NULL | |
| meta_keyword | varchar(30) | YES | | NULL | |
| meta_instruction | varchar(60) | YES | | NULL | |
| title_image_src | char(20) | YES | | NULL | |
| image_width | int(11) | YES | | NULL | |
| image_height | int(11) | YES | | NULL | |
| show_order | int(11) | YES | | NULL | |
| show_flag | char(1) | YES | | NULL | |
| click_times | char(10) | YES | | NULL | |
| type | char(10) | YES | | NULL | |
| delete_flag | tinyint(1) | YES | | 0 | |
+------------------+-------------+------+-----+---------+-------+
mysql> desc citi_column_content;
+----------------+---------------------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------+------+-----+-------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| title | char(120) | YES | | NULL | |
| short_title | char(20) | YES | | NULL | |
| summary | varchar(400) | YES | | NULL | |
| content | longtext | YES | | NULL | |
| column_code | char(10) | YES | MUL | NULL | |
| attach_file | varchar(5000) | YES | | NULL | |
| check_flag | tinyint(1) | YES | | NULL | |
| check_time | datetime | YES | | NULL | |
| checker_id | int(11) | YES | | NULL | |
| click_times | int(11) unsigned zerofill | YES | | 00000000000 | |
| delete_flag | tinyint(1) | YES | | 0 | |
| flag | tinyint(1) | YES | | NULL | |
| issue_time | datetime | YES | MUL | NULL | |
| keywords | char(255) | YES | MUL | NULL | |
| source | char(60) | YES | | NULL | |
| source_url | varchar(100) | YES | | NULL | |
| title_color | char(7) | YES | | NULL | |
| update_time | datetime | YES | | NULL | |
| update_user_id | int(11) | YES | | NULL | |
| writer_id | int(11) | YES | MUL | NULL | |
| quoter | char(26) | YES | | NULL | |
| list_show_flag | tinyint(1) | YES | | NULL | |
| zhuanlan_flag | tinyint(1) | YES | | 0 | |
| content_level | varchar(10) | YES | | NULL | |
+----------------+---------------------------+------+-----+-------------+----------------+
在citi_column_content中建立
column_code check_flag delete_flag
复合索引
去掉ORDER BY，再EXPLAIN 什么结果
稍等一下，数据库现在被Copying to tmp table给占满了
+----+-------------+-------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+----------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+----------------------------+------+--------------------------+
| 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 37 | Using where; Using index |
| 1 | SIMPLE | a | ref | article_n_01,article_n_04,article_n_05,article_n_06(Deprecated),article_n_07(Deprecated) | article_n_07(Deprecated) | 35 | citi_db.b.code,const,const | 1305 | Using where |
+----+-------------+-------+-------+------------------------------------------------------------------------------------------+--------------------------+---------+----------------------------+------+--------------------------+07这个索引就是column_code check_flag delete_flag
从结果上看，索引已经用上了，表中有多少条记录，执行速度慢？
mysql> explain select a.* from citi_column_content as a inner join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc;
+----+-------------+-------+-------+---------------+-------------+---------+----------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------+---------+----------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 32 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | column_code | column_code | 31 | citi_db.b.code | 260 | Using where |
+----+-------------+-------+-------+---------------+-------------+---------+----------------+------+-----------------------------------------------------------+索引全部干掉了，就剩2个外键
show index in citi_column;
show index in citi_column;
+---------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| citi_column_content | 0 | PRIMARY | 1 | id | A | 118907 | NULL | NULL | | BTREE | | |
| citi_column_content | 1 | column_code | 1 | column_code | A | 457 | NULL | NULL | YES | BTREE | | |
| citi_column_content | 1 | writer_id | 1 | writer_id | A | 26 | NULL | NULL | YES | BTREE | | |
+---------------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| citi_column | 0 | PRIMARY | 1 | code | A | 341 | NULL | NULL | | BTREE | | |
| citi_column | 0 | code | 1 | code | A | 341 | NULL | NULL | | BTREE | | |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set
create index rr on citi_column_content(column_code,id)
+----+-------------+-------+-------+----------------+-------------+---------+----------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+----------------+-------------+---------+----------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 32 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | column_code,rr | column_code | 31 | citi_db.b.code | 260 | Using where |
+----+-------------+-------+-------+----------------+-------------+---------+----------------+------+-----------------------------------------------------------+
2 rows in setmysql> explain select a.* from citi_column_content as a use index(`rr`) join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc;
+----+-------------+-------+-------+---------------+---------+---------+----------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+----------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | b | range | PRIMARY,code | PRIMARY | 30 | NULL | 32 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | rr | rr | 31 | citi_db.b.code | 263 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+----------------+------+-----------------------------------------------------------+
2 rows in set
mysql> show index in citi_column_content;
+---------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| citi_column_content | 0 | PRIMARY | 1 | id | A | 299961 | NULL | NULL | | BTREE | |
| citi_column_content | 1 | article_n_01 | 1 | column_code | A | 828 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_02 | 1 | writer_id | A | 5 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_03 | 1 | keywords | A | 1704 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | check_flag | 1 | check_flag | A | 5 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | check_flag | 2 | delete_flag | A | 5 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 1 | column_code | A | 3796 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 2 | check_flag | A | 3946 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_04 | 3 | id | A | 299961 | NULL | NULL | | BTREE | |
| citi_column_content | 1 | article_n_05 | 1 | column_code | A | 93 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_05 | 2 | check_flag | A | 93 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_05 | 3 | delete_flag | A | 93 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_05 | 4 | id | A | 299961 | NULL | NULL | | BTREE | |
| citi_column_content | 1 | article_n_06 | 1 | column_code | A | 579 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_06 | 2 | check_flag | A | 721 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_06 | 3 | delete_flag | A | 721 | NULL | NULL | YES | BTREE | |
| citi_column_content | 1 | article_n_06 | 4 | issue_time | A | 299961 | NULL | NULL | YES | BTREE | |
+---------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
17 rows in set (0.41 sec)mysql> explain select a.* from citi_column_content as a inner join citi_column as b on (a.column_code = b.code ) where a.check_flag = 1 and a.delete_flag=0 and b.code like '04%' order by a.id desc;
+----+-------------+-------+--------+----------------------------------------------------------------+------------+---------+-----------------------+-------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------+------------+---------+-----------------------+-------+-----------------------------+
| 1 | SIMPLE | a | ref | article_n_01,check_flag,article_n_04,article_n_05,article_n_06 | check_flag | 4 | const,const | 63860 | Using where; Using filesort |
| 1 | SIMPLE | b | eq_ref | PRIMARY,code | PRIMARY | 30 | citi_db.a.column_code | 1 | Using index |
+----+-------------+-------+--------+----------------------------------------------------------------+------------+---------+-----------------------+-------+-----------------------------+
2 rows in set (0.00 sec)给check_flag,delete_flag做了个索引居然不用临时表了