关于数据库语句的问题，mysql
一个mysql数据库查询性能的问题 ?这周工作时曾遇到一个问题。在一个MYSQL的表里做类似下面这一个很简单查询的时候耗时接近1秒钟的时间。? www。2cto。com ?1select sum(col5) , sum(col6) from table_name2where col_key_2='value1' and col_key_3 = 'value2'表定义如下：?01CREATE TABLE `table_name` (02? `col_key_1` date NOT NULL default '0000-00-00',03? `col_key_3` varchar(32) NO...全部
一个mysql数据库查询性能的问题 ?这周工作时曾遇到一个问题。在一个MYSQL的表里做类似下面这一个很简单查询的时候耗时接近1秒钟的时间。? www。2cto。com ?1select sum(col5) , sum(col6) from table_name2where col_key_2='value1' and col_key_3 = 'value2'表定义如下：?01CREATE TABLE `table_name` (02? `col_key_1` date NOT NULL default '0000-00-00',03? `col_key_3` varchar(32) NOT NULL default '',04? `col_key_2` varchar(32) NOT NULL default '',05? `col5` bigint(20) unsigned default NULL,06? `col6` bigint(20) unsigned default NULL,07? `col7` bigint(20) unsigned default NULL,08? `col8` bigint(20) unsigned default NULL,09? `col_key_4` varchar(32) NOT NULL default '',10? PRIMARY KEY ?(`col_key_1`,`col_key_2`,`col_key_3`,`col_key_4`)11) ENGINE=MyISAM DEFAULT CHARSET=latin1 |整个表里大概只有200多万条数据。
但查询的速度居然会慢到1秒钟才能查询出来，完全不可以忍受。然后我给这张加上了另一个索引：KEY `class` (`col_key_2`,`col_key_3`)? www。2cto。com ?查询的速度立马提高到0。
00秒。?于是认真的查看了一下mysql 手册的8。3小节。?MySQL索引的种类和作用mysql的索引分成：primary key, unique, index, fulltext index。
primary key是主键， unique是唯一索引， index是普通的索引。fulltext index是全文索引。 索引的作用就像C语言里的指针那样，直接指向表的一行。?可以对用col_name(N) 对符串的前N个字节做索引。
text类型和blob类型则必须要对前N个字节做索引。MYISAM最多支持1000个字节的索引， INNODB最多支持767字节的索引。?索引有下列作用：?1 帮助where语句快速查询。?2 进行多表连接?3 找到最大值和最小值（应该只有B-tree索引有这个功能，hash索引没有这个功能）?4 sort（应该只有B-tree索引有这个功能，hash索引没有这个功能）和group??多列索引多列索引在对多个列同时进行查询的时候特别有用。
多列索引最多支持16列。可以这样理解多列索引：?把多个列concat在一起，然后再对这个concat的值做一个索引。?比较神奇的一点是，比如你有一个索引针对col1 col2 col3这3个列时， 只查询col1和只查询col1 col2时也能用到这个索引。
?比如有这个表：?1CREATE TABLE test (2? ? id ? ? ? ? INT NOT NULL,3? ? last_name ?CHAR(30) NOT NULL,4? ? first_name CHAR(30) NOT NULL,5? ? PRIMARY KEY (id),6? ? INDEX name (last_name,first_name)7);下面这些查询都可以用到多列索引：01SELECT * FROM test WHERE last_name='Widenius';02?03SELECT * FROM test04? WHERE last_name='Widenius' AND first_name='Michael';05?06SELECT * FROM test07? WHERE last_name='Widenius'08? AND (first_name='Michael' OR first_name='Monty');09?10SELECT * FROM test11? WHERE last_name='Widenius'12? AND first_name >='M' AND first_name explain select sum(col5) , sum(col6) from table_name02where col_key_2='value1' and col_key_3 = 'value2' G03*************************** 1。
row ***************************04? ? ? ? ? ?id: 105? select_type: SIMPLE06? ? ? ? table: table_name07? ? ? ? ?type: ref08possible_keys: class09? ? ? ? ? key: class10? ? ? key_len: 6811? ? ? ? ? ref: const,const12? ? ? ? ?rows: 113? ? ? ? Extra: Using where141 row in set (0。
00 sec)而下面这个查询则不能使用到索引：?01mysql> explain select sum(col5) , sum(col6) from table_name02?where col5='value1' and col_key_3 = 'value2' G03*************************** 1。
row ***************************04? ? ? ? ? ?id: 105? select_type: SIMPLE06? ? ? ? table: table_name07? ? ? ? ?type: ALL08possible_keys: NULL09? ? ? ? ? key: NULL10? ? ? key_len: NULL11? ? ? ? ? ref: NULL12? ? ? ? ?rows: 235745513? ? ? ? Extra: Using where141 row in set (0。
00 sec)?索引的好坏MySQL使用一个指标value group size来衡量索引的好坏。什么是value group呢？ 就是具有相同索引key值的行数。这个指标显然是越小越好。最理想的情况就是每一个key值只对应1行， 这样的话我们的每次搜索一个key值都只返回一行，显然速度非常快。
?可以用mysql提供的工具查看一个表的索引的好坏。可以先用analyze table语句更新统计，然后用show index来查看统计：?1mysql> analyze table table_name;2 ----------------- --------- ---------- ---------- 3| Table ? ? ? ? ? | Op ? ? ?| Msg_type | Msg_text |4 ----------------- --------- ---------- ---------- 5| stat。
table_name | analyze | status ? | OK ? ? ? |6 ----------------- --------- ---------- ---------- 71 row in set (3。
13 sec)8?9mysql> show index in table_name;?table_name这张表有两个索引PRIMARY和class，PRIMARY这个索引是一个包含4列的多列索引。
?Cardinality这个值表示索引值的不同的行数。?例如：?col_key_1值有18行。?col_key_1＋col_key_2 值有392909行。?col_key_1 col_key_2 col_key_3 值有235745行。
?col_key_1 col_key_2 col_key_3 col_key_4值有235745行。?通过索引值的行数，我们就可以看出来索引好还是不好了。索引值不同的行数越多索引就越好。
当索引值不同的行数=表的总行数就达到最理想的情况 value group size = 1了。?B-tree索引和Hash索引的比较默认情况下MySQL都是使用B-tree索引。来谈一下Hash索引的缺陷:?1 只能处理’=‘ 这种where 子句，而对于是无能为力的。
这和B-tree索引是有序的，Hash无序的有关。?2 无法处理order by。 原因同上。?3 无法得知两行之间的距离。 原因同上。?4 只能搜完整的字段，不能只搜字段的一部分。 而对于B-tree索引， 支持搜索字符串最左边的一部分。
例如"police%" 。 。收起
