- 浏览: 61410 次
- 性别:
- 来自: 上海
最新评论
-
feipigzi:
我觉得文章内容不如改成四个字——“顾名思义”
FRAMESET和IFRAME各自的优点和缺点 -
yuchujin:
这个也太短了吧
FRAMESET和IFRAME各自的优点和缺点
转载:http://hi.baidu.com/wenzi_momo/blog/item/4ee4aeeac34d4a242cf53470.html
1、使你的数据尽可能
小
最基本的优化之一是使你的数据(和索引)在磁盘上(并且在内存中)占据的空间尽可能小。这能给出巨大的改 进,因为磁盘读入较快并且通常也用较少的主存储器。如果在更小的列上做索引,索引也占据较少的资源。
你能用下面的技术使表的性能更好并且使存储空间最小:
尽可能地使用最有效(最小)的类型。MySQL有很多节省磁盘空间和内存的专业化类型。
如果可能使表更小,使用较小的整数类型。例如,MEDIUMINT经常比INT好一些。
如果可能,声明列为NOT NULL。它使任何事情更快而且你为每列节省一位。注意如果在你的应用程序中你 确实需要NULL,你应该毫无疑问使用它,只是避免缺省地在所有列上有它。
2、使用定长列,不使用可变长列
这条准则对被经常修改,从而容易产生碎片的表来说特别重要。例如,应该选择 CHAR 列而不选择 VARCHAR 列。所要权衡的是使用定长列时,表所占用的空间更多,但如果能够承担这种空间的耗费,使用定长行将比使用可变 长的行处理快得多。
3、将列定义为 NOT NULL
这样处理更快,所需空间更少。而且有时还能简化查询,因为不需要检查是否存在特例 NULL。
4、考虑使用 ENUM 列
如果有一个只含有限数目的特定值的列,那么应该考虑将其转换为 ENUM 列。ENUM 列的值可以更快地处理,因 为它们在内部是以数值表示的。
二,索引优化.
1, 使用EXPLAIN语句检查SQL语句
从EXPLAIN的输出包括下面列:
table:
输出的行所引用的表。
type :
联结类型。各种类型的信息在下面给出。
不同的联结类型列在下面,以最好到最差类型的次序:
system const eq_ref ref range index ALL
ALL 全表扫描.
index 以索引的顺序进行表扫描.优点是不用进行排序,缺点是还要进行全表扫描.
range 被限制的index,它从索引的某个点开始,返回一定范围的数据.要好于index.(注:不一定 好于index,当在where上建的索引只能过滤调少量数据时,而还要在另一列上排序,index 效率会高于range)
ref 访问索引,返回某个值的数据.(可以返回多行) 通常使用=
eq_ref 访问索引,返回某单一行的数据.(通常在联接时出现)
const system :可以将查询的变量转为常量. 如article_id=1; 其中article_id为 主键或唯一键.
possible_keys :
可能用到的索引
key :
key列显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。
key_len :
key_len列显示MySQL决定使用的键长度。如果键是NULL,长度是NULL。注意这 告诉我们MySQL将实际使用一个多部键值的几个部分。
ref:
ref列显示哪个列或常数与key一起用于从表中选择行。
rows:
rows列显示MySQL相信它必须检验以执行查询的行数。
Extra :
using index 只用到索引,可以避免访问表.
using where 使用到where来过虑数据. 不是所有的where clause都要显示using where. 如以=方式访问索引.
using tmporary 用到临时表
using filesort 用到额外的排序. (当使用order by v1,而没用到索引时,就会使用额外的排序)
range checked for eache record(index map:N) 没有好的索引.
有关详细说明见注1;
2,多列索引
MySQL通常使用找出最少数量的行的索引。一个索引被用于你与下列操作符作比较的列:=、>、& gt;=、<、<=、BETWEEN和一个有一个非通配符前缀象'something%'的 LIKE的列。
对于一个多列索引,如果在WHERE子句的所有AND层次使用索引,将不使用来索引优化查询。为了能够使用索 引优化查询,必须把一个多列索引的前缀使用在一个AND条件组中。
3,强制索引
有时查询时mysqld会选择效率不高的索引,这时候就需要使用到强制索引.
select .. from tb use index(index_name) where ..或
select .. from tb force index(index_name) where ..
使用第一种方法是,告诉mysql使用哪个索引,但不一定用到(当mysql分析此查询时认为从原表查询效率 更高时,就不使用索引)
第二种方法,无论哪种情况都要使用到指定的索引.
注1:转自(High.Performance.MySql.second.Edition)
The Columns in EXPLAIN
EXPLAIN’s output always has the same columns (except for EXPLAIN EXTENDED, which
adds a filtered column in MySQL 5.1, and EXPLAIN PARTITIONS, which adds a
partitions column). The variability is in the number and contents of the rows. However,
to keep our examples clear, we don’t always show all columns in this appendix.
In the following sections, we show you the meaning of each of the columns in an
EXPLAIN result. Keep in mind that the rows in the output come in the order in which
MySQL actually executes the parts of the query, which is not always the same as the
order in which they appear in the original SQL.
The id Column
This column always contains a number, which identifies the SELECT to which the row
belongs. If there are no subqueries or unions in the statement, there is only one
SELECT, so every row will show a 1 in this column. Otherwise, the inner SELECT statements
generally will be numbered sequentially, according to their positions in the
original statement.
MySQL divides SELECT queries into simple and complex types, and the complex
types can be grouped into three broad classes: simple subqueries, so-called derived
tables (subqueries in the FROM clause),* and UNIONs. Here’s a simple subquery:
mysql> EXPLAIN SELECT (SELECT 1 FROM sakila.actor LIMIT 1) FROM sakila.film;
+----+-------------+-------+...
| id | select_type | table |...
+----+-------------+-------+...
| 1 | PRIMARY | film |...
| 2 | SUBQUERY | actor |...
+----+-------------+-------+...
Subqueries in the FROM clause and UNIONs add more complexity to the id column.
Here’s a basic subquery in the FROM clause:
mysql> EXPLAIN SELECT film_id FROM (SELECT film_id FROM sakila.film) AS der;
+----+-------------+------------+...
| id | select_type | table |...
+----+-------------+------------+...
| 1 | PRIMARY | <derived2> |...
| 2 | DERIVED | film |...
+----+-------------+------------+...
As you know, this query is executed with a temporary table. MySQL internally refers
to the temporary table by its alias (der) within the outer query, which you can see in
the ref column in more complicated queries.
Finally, here’s a UNION query:
mysql> EXPLAIN SELECT 1 UNION ALL SELECT 1;
+------+--------------+------------+...
| id | select_type | table |...
+------+--------------+------------+...
| 1 | PRIMARY | NULL |...
| 2 | UNION | NULL |...
| NULL | UNION RESULT | <union1,2> |...
+------+--------------+------------+...
Note the extra row in the output for the result of the UNION. UNION results are always
placed into a temporary table, and MySQL then reads the results back out of the
temporary table. The temporary table doesn’t appear in the original SQL, so its id
column is NULL. In contrast to the preceding example (illustrating a subquery in the
FROM clause), the temporary table that results from this query is shown as the last row
in the results, not the first.
So far this is all very straightforward, but mixtures of these three categories of statements
can cause the output to become more complicated, as we’ll see a bit later.
The select_type Column
This column shows whether the row is a simple or complex SELECT (and if it’s the latter,
which of the three complex types it is). The value SIMPLE means the query contains
no subqueries or UNIONs. If the query has any such complex subparts, the
outermost part is labeled PRIMARY, and other parts are labeled as follows:
SUBQUERY
A SELECT that is contained in a subquery in the SELECT list (in other words, not in
the FROM clause) is labeled as SUBQUERY.
DERIVED
The value DERIVED is used for a SELECT that is contained in a subquery in the FROM
clause, which MySQL executes recursively and places into a temporary table.
The server refers to this as a “derived table” internally, because the temporary
table is derived from the subquery.
UNION
The second and subsequent SELECTs in a UNION are labeled as UNION. The first
SELECT is labeled as though it is executed as part of the outer query. This is why
the previous example showed the first SELECT in the UNION as PRIMARY. If the UNION
were contained in a subquery in the FROM clause, its first SELECT would be labeled
as DERIVED.
UNION RESULT
The SELECT used to retrieve results from the UNION’s temporary table is labeled as
UNION RESULT.
In addition to these values, a SUBQUERY and a UNION can be labeled as DEPENDENT and
UNCACHEABLE. DEPENDENT means the SELECT depends on data that is found in an outer
query; UNCACHEABLE means something in the SELECT prevents the results from being
cached with an Item_cache. (Item_cache is undocumented; it is not the same thing as
the query cache, athough it can be defeated by some of the same types of constructs,
such as the RAND( ) function.)
The table Column
This column shows which table the row is accessing. In most cases, it’s straightforward:
it’s the table, or its alias if the SQL specifies one.
You can read this column from top to bottom to see the join order MySQL’s join
optimizer chose for the query. For example, you can see that MySQL chose a different
join order than the one specified for the following query:
mysql> EXPLAIN SELECT film.film_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> INNER JOIN sakila.actor USING(actor_id);
+----+-------------+------------+...
| id | select_type | table |...
+----+-------------+------------+...
| 1 | SIMPLE | actor |...
| 1 | SIMPLE | film_actor |...
| 1 | SIMPLE | film |...
+----+-------------+------------+...
Remember the left-deep tree diagrams we showed in “The execution plan” on
page 172? MySQL’s query execution plans are always left-deep trees. If you flip the
plan on its side, you can read off the leaf nodes in order, and they’ll correspond
directly to the rows in EXPLAIN. The plan for the preceding query looks like Figure B-1.
Derived tables and unions
The table column becomes much more complicated when there is a subquery in the
FROM clause or a UNION. In these cases, there really isn’t a “table” to refer to, because
the temporary table MySQL creates exists only while the query is executing.
When there’s a subquery in the FROM clause, the table column is of the form
<derivedN>, where N is the subquery’s id. This is always a “forward reference”―in
other words, N refers to a later row in the EXPLAIN output.
When there’s a UNION, the UNION RESULT table column contains a list of ids that participate
in the UNION. This is always a “backward reference,” because the UNION RESULT
comes after all of the rows that participate in the UNION. If there are more than about
20 ids in the list, the table column may be truncated to keep it from getting too long,
and you won’t be able to see all the values. Fortunately, you can still deduce which
rows were included, because you’ll be able to see the first row’s id. Everything that
comes between that row and the UNION RESULT is included in some way.
An example of complex SELECT types
Here’s a nonsense query that serves as a fairly compact example of some of the complex
SELECT types:
1 EXPLAIN
2 SELECT actor_id,
3 (SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id =
4 der_1.actor_id LIMIT 1)
5 FROM (
6 SELECT actor_id
7 FROM sakila.actor LIMIT 5
8 ) AS der_1
9 UNION ALL
10 SELECT film_id,
11 (SELECT @var1 FROM sakila.rental LIMIT 1)
12 FROM (
13 SELECT film_id,
14 (SELECT 1 FROM sakila.store LIMIT 1)
15 FROM sakila.film LIMIT 5
16 ) AS der_2;
The LIMIT clauses are just for convenience, in case you wish to execute the query
without EXPLAIN and see the results. Here is the result of the EXPLAIN:
+------+----------------------+------------+...
| id | select_type | table |...
+------+----------------------+------------+...
| 1 | PRIMARY | <derived3> |...
| 3 | DERIVED | actor |...
| 2 | DEPENDENT SUBQUERY | film_actor |...
| 4 | UNION | <derived6> |...
| 6 | DERIVED | film |...
| 7 | SUBQUERY | store |...
| 5 | UNCACHEABLE SUBQUERY | rental |...
| NULL | UNION RESULT | <union1,4> |...
+------+----------------------+------------+...
We’ve been careful to make each part of the query access a different table, so you can
see what goes where, but it’s still hard to figure out! Taking it from the top:
? The first row is a forward reference to der_1, which the query has labeled as
<derived3>. It comes from line 2 in the original SQL. To see which rows in the
output refer to SELECT statements that are part of <derived3>, look forward…
? …to the second row, whose id is 3. It is 3 because it’s part of the third SELECT in
the query, and it’s listed as a DERIVED type because it’s nested inside a subquery
in the FROM clause. It comes from lines 6 and 7 in the original SQL.
? The third row’s id is 2. It comes from line 3 in the original SQL. Notice that it
comes after a row with a higher id number, suggesting that it is executed afterward,
which makes sense. It is listed as a DEPENDENT SUBQUERY, which means its
results depend on the results of an outer query (also known as a correlated subquery).
The outer query in this case is the SELECT that begins in line 2 and
retrieves data from der_1.
? The fourth row is listed as a UNION, which means it is the second or later SELECT
in a UNION. Its table is <derived6>, which means it’s retrieving data from a subquery
in the FROM clause and appending to a temporary table for the UNION. As
before, to find the EXPLAIN rows that show the query plan for this subquery, you
must look forward.
? The fifth row is the der_2 subquery defined in lines 13, 14, and 15 in the original
SQL, which EXPLAIN refers to as <derived6>.
? The sixth row is an ordinary subquery in <derived6>’s SELECT list. Its id is 7,
which is important…
? …because it is greater than 5, which is the seventh row’s id. Why is this important?
Because it shows the boundaries of the <derived6> subquery. When
EXPLAIN outputs a row whose SELECT type is DERIVED, it represents the beginning
of a “nested scope.” If a subsequent row’s id is smaller (in this case, 5 is smaller
than 6), it means the nested scope has closed. This lets us know that the seventh
row is part of the SELECT list that is retrieving data from <derived6>―i.e., part of
the fourth row’s SELECT list (line 11 in the original SQL). This example is fairly
easy to understand without knowing the significance and rules of nested scopes,
but sometimes it’s not so easy. The other notable thing about this row in the
output is that it is listed as an UNCACHEABLE SUBQUERY because of the user variable.
? Finally, the last row is the UNION RESULT. It represents the stage of reading the
rows from the UNION’s temporary table. You can begin at this row and work
backward if you wish; it is returning results from rows whose ids are 1 and 4,
which are in turn references to <derived3> and <derived6>.
As you can see, the combination of these complicated SELECT types can result in
EXPLAIN output that’s pretty difficult to read. Understanding the rules makes it easier,
but there’s no substitute for practice.
Reading EXPLAIN’s output often requires you to jump forward and backward in the
list. For example, look again at the first row in the output. There is no way to know
just by looking at it that it is part of a UNION. You’ll only see that when you read the
last row of the output.
The type Column
The MySQL manual says this column shows the “join type,” but we think it’s more
accurate to say the access type―in other words, how MySQL has decided to find
rows in the table. Here are the most important access methods, from worst to best:
ALL
This is what most people call a table scan. It generally means MySQL must scan
through the table, from beginning to end, to find the row. (There are exceptions,
such as queries with LIMIT or queries that display “Using distinct/not
exists” in the Extra column.)
index
This is the same as a table scan, except MySQL scans the table in index order
instead of the rows. The main advantage is that this avoids sorting; the biggest
disadvantage is the cost of reading an entire table in index order. This usually
means accessing the rows in random order, which is very expensive.
If you also see “Using index” in the Extra column, it means MySQL is using a
covering index (see Chapter 3) and scanning only the index’s data, not reading
each row in index order. This is much less expensive than scanning the table in
index order.
range
A range scan is a limited index scan. It begins at some point in the index and
returns rows that match a range of values. This is better than a full index scan
because it doesn’t go through the entire index. Obvious range scans are queries
with a BETWEEN or > in the WHERE clause.
When MySQL uses an index to look up lists of values, such as IN( ) and OR lists,
it also displays it as a range scan. However, these are quite different types of
accesses, and they have important performance differences. See the sidebar
“What Is a Range Condition?” on page 134 for more information.
The same cost considerations apply for this type as for the index type.
ref
This is an index access (sometimes called an index lookup) that returns rows
that match a single value. However, it might find multiple rows, so it’s a mixture
of a lookup and a scan. This type of index access can happen only on a nonunique
index or a nonunique prefix of a unique index. It’s called ref because the
index is compared to some reference value. The reference value is either a constant
or a value from a previous table in a multiple-table query.
The ref_or_null access type is a variation on ref. It means MySQL must do a
second lookup to find NULL entries after doing the initial lookup.
eq_ref
This is an index lookup that MySQL knows will return at most a single value.
You’ll see this access method when MySQL decides to use a primary key or
unique index to satisfy the query by comparing it to some reference value.
MySQL can optimize this access type very well, because it knows it doesn’t have
to estimate ranges of matching rows or look for more matching rows once it
finds one.
const, system
MySQL uses these access types when it can optimize away some part of the
query and turn it into a constant. For example, if you select a row’s primary key
by placing its primary key into the WHERE clause, MySQL can convert the query
into a constant. It then effectively removes the table from the join execution.
NULL
This access method means MySQL can resolve the query during the optimization
phase and will not even access the table or index during the execution stage.
The Columns in EXPLAIN
For example, selecting the minimum value from an indexed column can be done
by looking at the index alone and requires no table access during execution.
The possible_keys Column
This column shows which indexes could be used for the query, based on the columns
the query accesses and the comparison operators used. This list is created early
in the optimization phase, so some of the indexes listed might be useless for the
query after subsequent optimization phases.
The key Column
This column shows which index MySQL decided to use to optimize the access to the
table. If the index doesn’t appear in possible_keys, MySQL chose it for another reason―
for example, it might choose a covering index even when there is no WHERE
clause.
In other words, possible_keys reveals which indexes can help make row lookups efficient,
but key shows which index the optimizer decided to use to minimize query cost
(see “The Query Optimization Process” on page 164 for more on the optimizer’s cost
metrics). Here’s an example:
mysql> EXPLAIN SELECT actor_id, film_id FROM sakila.film_actor\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_actor
type: index
possible_keys: NULL
key: idx_fk_film_id
key_len: 2
ref: NULL
rows: 5143
Extra: Using index
The key_len Column
This column shows the number of bytes MySQL will use in the index. If MySQL is
using only some of the index’s columns, you can use this value to calculate which
columns it uses. Remember that MySQL can use only the leftmost prefix of the
index. For example, sakila.film_actor’s primary key covers two SMALLINT columns,
and a SMALLINT is two bytes, so each tuple in the index is four bytes. Here’s a sample
query:
mysql> EXPLAIN SELECT actor_id, film_id FROM sakila.film_actor WHERE actor_id=4;
...+------+---------------+---------+---------+...
...| type | possible_keys | key | key_len |...
...+------+---------------+---------+---------+...
...| ref | PRIMARY | PRIMARY | 2 |...
...+------+---------------+---------+---------+...
Based on the key_len column in the result, you can deduce that the query performs
index lookups with only the first column, the actor_id. When calculating column
usage, be sure to account for character sets in character columns:
mysql> CREATE TABLE t (
-> a char(3) NOT NULL,
-> b int(11) NOT NULL,
-> c char(1) NOT NULL,
-> PRIMARY KEY (a,b,c)
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
mysql> INSERT INTO t(a, b, c)
-> SELECT DISTINCT LEFT(TABLE_SCHEMA, 3), ORD(TABLE_NAME),
-> LEFT(COLUMN_NAME, 1)
-> FROM INFORMATION_SCHEMA.COLUMNS:
mysql> EXPLAIN SELECT a FROM t WHERE a='sak' AND b = 112;
...+------+---------------+---------+---------+...
...| type | possible_keys | key | key_len |...
...+------+---------------+---------+---------+...
...| ref | PRIMARY | PRIMARY | 13 |...
...+------+---------------+---------+---------+...
The length of 13 bytes in this query is the sum of the lengths of the a and b columns.
Column a is three characters, which in utf8 require up to three bytes each, and column
b is a four-byte integer.
MySQL doesn’t always show you how much of an index is really being used. For
example, if you perform a LIKE query with a prefix pattern match, it will show that
the full width of the column is being used.
The key_len column shows the maximum possible length of the indexed fields, not
the actual number of bytes the data in the table used. MySQL will always show 13
bytes in the preceding example, even if column a happens to contain no values more
than one character long. In other words, key_len is calculated by looking at the
table’s definition, not the data in the table.
The ref Column
This column shows which columns or constants from preceding tables are being
used to look up values in the index named in the key column. Here’s an example that
shows a combination of join conditions and aliases. Notice that the ref column
reflects how the film table is aliased as f in the query text:
The Columns in EXPLAIN | 619
mysql> EXPLAIN
-> SELECT STRAIGHT_JOIN f.film_id
-> FROM sakila.film AS f
-> INNER JOIN sakila.film_actor AS fa
-> ON f.film_id=fa.film_id AND fa.actor_id = 1
-> INNER JOIN sakila.actor AS a USING(actor_id);
...+-------+...+--------------------+---------+------------------------+...
...| table |...| key | key_len | ref |...
...+-------+...+--------------------+---------+------------------------+...
...| a |...| PRIMARY | 2 | const |...
...| f |...| idx_fk_language_id | 1 | NULL |...
...| fa |...| PRIMARY | 4 | const,sakila.f.film_id |...
...+-------+...+--------------------+---------+------------------------+...
The rows Column
This column shows the number of rows MySQL estimates it will need to read to find
the desired rows. This number is per loop in the nested-loop join plan. That is, it’s not
just the number of rows MySQL thinks it will need to read from the table; it is the
number of rows, on average, MySQL thinks it will have to read to find rows that satisfy
the criteria in effect at that point in query execution. (The criteria include constants
given in the SQL as well as the current columns from previous tables in the
join order.)
This estimate can be quite inaccurate, depending on the table statistics and how
selective the indexes are. It also doesn’t reflect LIMIT clauses in MySQL 5.0 and earlier.
For example, the following query will not examine 1,022 rows:
mysql> EXPLAIN SELECT * FROM sakila.film LIMIT 1\G
...
rows: 1022
You can calculate roughly the number of rows the entire query will examine by multiplying
all the rows values together. For example, the following query might examine
approximately 2,600 rows:
mysql> EXPLAIN
-> SELECT f.film_id
-> FROM sakila.film AS f
-> INNER JOIN sakila.film_actor AS fa USING(film_id)
-> INNER JOIN sakila.actor AS a USING(actor_id);
...+------+...
...| rows |...
...+------+...
...| 200 |...
...| 13 |...
...| 1 |...
...+------+...
Remember, this is the number of rows MySQL thinks it will examine, not the number
of rows in the result set. Also realize that there are many optimizations, such as
join buffers and caches, that aren’t factored into the number of rows shown. MySQL
will probably not have to actually read every row it predicts it will. MySQL also
doesn’t know anything about the operating system or hardware caches.
The filtered Column
This column is new in MySQL 5.1 and appears when you use EXPLAIN EXTENDED. It
shows a pessimistic estimate of the percentage of rows that will satisfy some condition
on the table, such as a WHERE clause or a join condition. If you multiply the rows
column by this percentage, you will see the number of rows MySQL estimates it will
join with the previous tables in the query plan. At the time of this writing, the optimizer
uses this estimate only for the ALL, index, range, and index_merge access
methods.
To illustrate this column’s output, we created a table as follows:
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
filler char(200),
PRIMARY KEY(id)
);
We then inserted 1,000 rows into this table, with random text in the filler column.
Its purpose is to prevent MySQL from using a covering index for the query we’re
about to run:
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE id < 500\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 49.40
Extra: Using where
MySQL could use a range access to retrieve all rows with IDs less than 500 from the
table, but it won’t because that would eliminate only about half the rows. It thinks a
table scan is less expensive. As a result, it uses a table scan and a WHERE clause to filter
out rows. It knows how many rows the WHERE clause will remove from the result,
because of the range access cost estimates. That’s why the 49.40% value appears in
the filtered column.
The Extra Column
This column contains extra information that doesn’t fit into other columns. The
MySQL manual documents most of the many values that can appear here; we have
referred to many of them throughout this book.
The most important values you might see frequently are as follows:
“Using index”
This indicates that MySQL will use a covering index to avoid accessing the table
(see “Covering Indexes” on page 120). Don’t confuse covering indexes with the
index access type.
“Using where”
This means the MySQL server will post-filter rows after the storage engine
retrieves them. Many WHERE conditions that involve columns in an index can be
checked by the storage engine when (and if) it reads the index, so not all queries
with a WHERE clause will show “Using where.” Sometimes the presence of “Using
where” is a hint that the query can benefit from different indexing.
“Using temporary”
This means MySQL will use a temporary table while sorting the query’s result.
“Using filesort”
This means MySQL will use an external sort to order the results, instead of reading
the rows from the table in index order. MySQL has two filesort algorithms,
which you can read about in “Optimizing for filesorts” on page 300. Either type
can be done in memory or on disk. EXPLAIN doesn’t tell you which type of filesort
MySQL will use, and it doesn’t tell you whether the sort will be done in
memory or on disk.
“range checked for each record (index map: N)”
This value means there’s no good index, and the indexes will be reevaluated for
each row in a join. N is a bitmap of the indexes shown in possible_keys and is
redundant.
发表评论
-
SQL监控
2010-09-21 12:49 1118<script src="http://w ... -
SQL Server中各个系统表的作用(转载)
2006-06-07 16:22 1353sysaltfiles 主数据库 ... -
[SQL Server]管理常用SQL语句 (转载)
2006-06-07 16:23 917[SQL Server]管理常用SQL语 ... -
最新注册号码(不断更新)
2006-06-08 14:04 780http://vscool.net/read.php?tid= ... -
让SQL Server为工作负载高峰提前做好准备
2006-07-25 16:18 723毫无疑问,许多电子商 ... -
SQL SERVER乐观锁定和悲观锁定使用实例
2006-08-28 14:42 920乐观锁定,悲观锁定,锁 ... -
弹出新窗口,填写数据,提交后,关闭该弹出窗口,刷新父页面
2006-09-08 10:06 2482父窗口弹出子窗口的 button处理事件:private v ... -
如何删除SQL SERVER 2000的日志文件空间? 选择自 softj 的 Blog
2006-09-13 11:08 2890首先备份数据库然后备份文件,备份日志文件,可改名在 查询分 ... -
SQL SERVER导入导出大阅兵
2006-09-13 11:12 1386【IT168 技术专题】在我们使用SQL SERVER中,最常 ... -
数据导入导出工具BCP详解
2006-09-13 11:15 1569IT168 技术文档】bcp是SQL Server中负责导入导 ... -
使用Transact-SQL进行数据导入导出方法详解
2006-09-13 11:16 921IT168 技术文档】本文讨论了如何通过Transact-SQ ... -
SQL Server 2000中的触发器使用
2006-09-13 11:18 1004触发器是数据库应用中的重用工具,它的应用很广泛。这几天写一个化 ... -
SQL SERVER 与ACCESS、EXCEL的数据转换
2006-09-13 11:22 706熟悉SQL SERVER 2000的数据库管理员都知道,其DT ... -
Sql server优化50法
2006-09-13 11:22 896查询速度慢的原因很多,常见如下几种: 1、没有索引或者没有 ... -
SQL中怎么样自动生成表的更新脚本
2006-09-13 11:29 1684问题的提出:每当新建一个数据表,要建立个UP_TableNam ... -
SQL Server 2000 中递归
2006-09-22 13:31 1073CREATE FUNCTION dbo.Fun_Get_Cat ...
相关推荐
MySQL查询优化技术_索引
MySQL索引 聚集索引 如果你想了解MySQL索引查询优化,你首先应该对MySQL数据组织结构、B-Tree索引、聚集索引,次要索引有一定的了解,才能够更好地理解MySQL查询优化行为。这里主要探讨MySQL InnoDB的聚集索引。
MySQL索引原理及慢查询优化是数据库管理中的重要主题,尤其是在高并发、大数据量的互联网环境中,优化查询性能对于系统的整体效能至关重要。MySQL作为广泛使用的开源关系型数据库,其索引机制和查询优化技巧是开发者...
MySQL索引优化是数据库性能提升的关键环节,本篇主要探讨了几个关于MySQL索引使用和优化的重要知识点。 首先,创建了一个名为`employees`的员工记录表,其中包含`id`(主键)、`name`、`age`、`position`和`hire_...
2. **索引优化**:合理选择索引类型和设计索引方案对于提高查询效率至关重要。例如,在经常被用作查询条件的列上创建索引可以显著提高查询速度。 3. **索引维护**:定期检查并优化索引结构也是必要的,以确保索引...
MySQL索引优化是数据库性能提升的关键技术之一,尤其在处理大量数据时,高效索引能够显著加快查询速度,降低服务器负载。本课件主要聚焦于MySQL数据库的索引原理、优化策略以及相关存储过程和触发器的应用。 首先,...
从MySQL索引的角度出发,看各种SQL查询语句的优化怎么进行?(以前都是从语句的角度看怎么优化,现在站在索引的角度去总结SQL查询语句的优化) 预计时间1小时 第12课 表扫描与连接算法与MySQL多表连接优化实践 ...
MySQL查询优化是数据库管理中的关键环节,特别是在大数据量的场景下,索引优化能显著提升查询性能。本文将深入探讨“mysql查询优化之索引优化”这一主题。 首先,了解索引的基本概念至关重要。索引是数据库为了快速...
2. 索引优化:合理设计索引,根据查询模式创建最合适的索引。避免索引过多,因为这会增加写操作的开销。 3. 数据库设计:遵循第三范式,减少数据冗余,提高数据一致性。考虑数据分布,合理分区和分表。 4. 存储...
关于mysql索引一些优化介绍与创建原则,还有对order by排序的算法的介绍等等
### MySQL索引分析和优化 #### 一、索引的重要性及原理 索引在数据库管理中扮演着极其重要的角色,特别是在提高数据检索速度方面。**MySQL索引**本质上是用来加快数据检索过程的一种数据结构,类似于书籍中的目录...
Mysql的索引及优化策略,个人感觉还不错
"MySQL索引分析及优化" 索引是数据库中提高速度的一个关键因素。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。索引的使用可以大幅度地提高查询速度,减少数据库的...
Mysql索引优化案例 在对数据库进行操作时,尤其是在处理包含大量数据的表时,查询的性能问题是一个常见且重要的话题。在实际工作中,对于Mysql数据库进行索引优化是提高查询效率、减少查询时间的重要手段。本案例将...
mysql索引的优化方案技术分享,珍贵资料收藏下
前段时间我给朋友们准备的关于Mysql数据库索引优化及查询优化的ppt,送给这里有需要的朋友。
MySQL索引:优化查询的利器
* 优化 SQL 语句:使用 EXPLAIN 语句来分析 SQL 语句的执行计划,优化查询语句。 * 使用存储过程:使用存储过程可以将频繁查询的操作封装起来,提高查询效率。 * 优化数据库结构:优化数据库结构,例如,使用合适的...
8. **索引优化**:MySQL提供了`EXPLAIN`关键字,可以帮助分析查询执行计划,了解是否正确使用了索引。通过`EXPLAIN`,可以发现查询是否进行了全表扫描、是否使用了索引、索引的类型以及索引的覆盖程度等信息,从而...