时间：2021-07-01 10:21:17 帮助过：4人阅读
如何处理字符串末尾空格 去掉末尾空格，取值出来比较的时候自动加上进行比较 Version<=4.1，字符串末尾空格被删掉，version>5.0则保留
储存空间 固定空间，比喻char(10)不管字符串是否有10个字符都分配10个字符的空间 Varchar内节约空间，但更新可能发生变化，若varchar(10),开始若储存5个字符，当update成7个时有myisam可能把行拆 开，innodb可能分页，这样开销就增大
适用场合 适用于存储很短或固定或长度相似字符，如MD5加密的密码char(33)、昵称char(8)等 当最大长度远大于平均长度并且发生更新的时候。
注意当一些英文或数据的时候，最好用每个字符用字节少的类型，如latin1
(4)整型、整形优先原则
Tinyint、smallint、mediumint、int、bigint，分别需要8、16、24、32、64。
值域范围：-2^(n-1)~ 2^(n-1)-1
很多程序员在设计数据表的时候很习惯的用int，压根不考虑这个问题
笔者建议：能用tinyint的绝不用smallint
误区：int(1) 和int(11)是一样的，唯一区别是mysql客户端显示的时候显示多少位。
整形优先原则：能用整形的不用其他类型替换，如ip可以转换成整形保存，如商品价格‘50.00元’则保存成50
(5)精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4字节存储
数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL类型的数据将会转换成DOUBLE类型。
2、 sql语句优化
| 1 2 3 4 5 6 7 |
mysql>
create
table
one (
id
smallint
(10)
not
null
auto_increment
primary
key
,
username
char
(8)
not
null
,
password
char
(4)
not
null
,
`
level
` tinyint (1)
default
0,
last_login
char
(15)
not
null
,
index
(username,
password
,last_login))engine=innodb;
|
这是test表，其中id是主键，多列索引(username,password,last_login),里面有10000多条数据.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
Table
| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed |
Null
|
Index_type | Comment |
+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 0 |
PRIMARY
| 1 | id | A |20242 |
NULL
|
NULL
| |
BTREE | |
+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 1 | username | 1 | username | A |10121 |
NULL
|
NULL
| |
BTREE | |
+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 1 | username | 2 |
password
| A |10121 |
NULL
|
NULL
| YES |
BTREE | |
+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
| one | 1 | username | 3 | last_login | A |20242 |
NULL
|
NULL
| |
BTREE | |
+
-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+
|
(1) 最左前缀原则
定义：最左前缀原则指的的是在sql where 字句中一些条件或表达式中出现的列的顺序要保持和多索引的一致或以多列索引顺序出现，只要出现非顺序出现、断层都无法利用到多列索引。
举例说明：上面给出一个多列索引(username,password,last_login)，当三列在where中出现的顺序如 (username,password,last_login)、(username,password)、(username)才能用到索引，如下面几 个顺序(password,last_login)、(passwrod)、(last_login)—这三者不从username开始， (username,last_login)—断层，少了password，都无法利用到索引。
因为B+tree多列索引保存的顺序是按照索引创建的顺序，检索索引时按照此顺序检索
测试：以下测试不精确，这里只是说明如何才能正确按照最左前缀原则使用索引。还有的是以下的测试用的时间0.00sec看不出什么时间区别，因为数 据量只有20003条，加上没有在实体机上运行，很多未可预知的影响因素都没考虑进去。当在大数据量，高并发的时候，最左前缀原则对与提高性能方面是不可 否认的。
Ps：最左前缀原则中where字句有or出现还是会遍历全表
(1.1)能正确的利用索引
l Where子句表达式顺序是(username)
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
;
+
----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using
where
|
+
----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row
in
set
(0.00 sec)
|
l Where子句表达式顺序是(username,password)
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
and
password
=
‘123456‘
;
+
----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 43 | const,const | 1 | Using
where
|
+
----+-------------+-------+------+---------------+----------+---------+-------------+------+-------------+
1 row
in
set
(0.00 sec)
|
l Where子句表达式顺序是(username,password, last_login)
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
and
password
=
‘123456‘
and
last_login=
‘1338251170‘
;
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref|
rows
| Extra |
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,const,const | 1 | Using
where
|
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
1 row
in
set
(0.00 sec)
|
上面可以看出type=ref 是多列索引，key_len分别是24、43、83，这说明用到的索引分别是(username), (username,password), (username,password, last_login );row分别是5、1、1检索的数据行都很少，因为这三个查询都按照索引前缀原则，可以利用到索引。
(1.2)不能正确的利用索引
l Where子句表达式顺序是(password, last_login)
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
password
=
‘123456‘
and
last_login=
‘1338251170‘
;
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 20146 | Using
where
|
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row
in
set
(0.00 sec)
|
l Where 子句表达式顺序是(last_login)
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
last_login=
‘1338252525‘
;
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 20146 | Using
where
|
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row
in
set
(0.00 sec)
|
以上的两条语句都不是以username开始，这样是用不了索引，通过type=all(全表扫描)，key_len=null，rows都很大20146
Ps：one表里只有20003条数据，为什么出现20146，这是优化器对表的一个估算值，不精确的。
l Where 子句表达式虽然顺序是(username,password, last_login)或(username,password)但第一个是有范围’<’、’>’，’<=’，’>=’等出现
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
username>
‘abgvwfnt‘
and
password
=
‘123456‘
and
last_login=
‘1338251170‘
;
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one |
ALL
| username |
NULL
|
NULL
|
NULL
| 20146 | Using
where
|
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row
in
set
(0.00 sec)
|
这个查询很明显是遍历所有表，一个索引都没用到，非第一列出现范围(password列或last_login列)，则能利用索引到首先出现范围的 一列，也就是“where username=’abgvwfnt’ and password >’123456′and last_login=’1338251170′;”或则“where username=’abgvwfnt’ and password >’123456′and last_login<’1338251170′;”索引长度ref_len=43,索引检索到password列，所以考虑多列索引的时候把那些 查询语句用的比较的列放在最后(或非第一位)。
l 断层，即是where顺序(username, last_login)
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
and
last_login=
‘1338252525‘
;
+
----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 24 | const |5 | Using
where
|
+
----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+
1 row
in
set
(0.00 sec)
|
注意这里的key_len=24=8*3(8是username的长度，3是utf8编码)，rows=5，和下面一条sql语句搜索出来一样
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql>
select
*
from
one
where
username=
‘abgvwfnt‘
;
+
-------+----------+----------+-------+------------+
| id | username |
password
|
level
| last_login |
+
-------+----------+----------+-------+------------+
| 3597 | abgvwfnt | 234567 | 0 | 1338251420 |
| 7693 | abgvwfnt | 456789 | 0 | 1338251717 |
| 11789 | abgvwfnt | 456789 | 0 | 1338251992 |
| 15885 | abgvwfnt | 456789 | 0 | 1338252258 |
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
+
-------+----------+----------+-------+------------+
5
rows
in
set
(0.00 sec)
mysql>
select
*
from
one
where
username=
‘abgvwfnt‘
and
last_login=
‘1338252525‘
;
+
-------+----------+----------+-------+------------+
| id | username |
password
|
level
| last_login |
+
-------+----------+----------+-------+------------+
| 19981 | abgvwfnt | 456789 | 0 | 1338252525 |
+
-------+----------+----------+-------+------------+
1 row
in
set
(0.00 sec)
|
这个就是要的返回结果，所以可以知道断层(username,last_login)，这样只用到username索引，把用到索引的数据再重新检 查last_login条件，这个相对全表查询来说还是有性能上优化，这也是很多sql优化文章中提到的where 范围查询要放在最后(这不绝对，但可以利用一部分索引)
(1.3)如果一个查询where子句中确实不需要password列，那就用“补洞”。
| 1 2 3 4 5 6 7 8 9 10 |
mysql>
select
distinct
(
password
)
from
one;
+
----------+
|
password
|
+
----------+
| 234567 |
| 345678 |
| 456789 |
| 123456 |
+
----------+
4
rows
in
set
(0.08 sec)
|
可以看出password列中只有这几个值，当然在现实中不可能密码有这么多一样的，再说数据也可能不断更新，这里只是举例说明补洞的方法
| 1 2 3 4 5 6 7 8 |
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
and
password
in
(
‘123456‘
,
‘234567‘
,
‘345678‘
,
‘456789‘
)
and
last_login=
‘1338251170‘
;
+
----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | one | range | username | username| 83 |
NULL
|4 | Using
where
|
+
----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row
in
set
(0.00 sec)
|
可以看出ref=83 所有的索引都用到了，type=range是因为用了in子句。
这个被“补洞”列中的值应该是有限的，可预知的，如性别，其值只有男和女(加多一个不男不女也无妨)。
“补洞”方法也有瓶颈，当很多列，且需要补洞的相应列(可以多列)的值虽有限但很多(如中国城市)的时候，优化器在优化时组合起来的数量是很大，这样的话就要做好基准测试和性能分析，权衡得失，取得一个合理的优化方法。
(1.4)like
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> explain
select
*
from
one
where
username
like
‘abgvwfnt%‘
;
+
----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | one | range | username | username | 24 |
NULL
|
5 | Using
where
|
+
----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row
in
set
(0.00 sec)
mysql> explain
select
*
from
one
where
username
like
‘%abgvwfnt%‘
;
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 20259 | Using
where
|
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row
in
set
(0.01 sec)
|
对比就知道like操作abgvwfnt%能用到索引，%abgvwfnt%用不到
———————————————————————————————
(2) Order by 优化
(2.1)filesort优化算法.
在mysql version()<4.1之前，优化器采用的是filesort第一种优化算法，先提取键值和指针，排序后再去提取数据，前后要搜索数据两次，第 一次若能使用索引则使用，第二次是随机读(当然不同引擎也不同)。mysql version()>=4.1,更新了一个新算法，就是在第一次读的时候也把selcet的列也读出来，然后在sort_buffer_size中 排序(不够大则建临时表保存排序顺序)，这算法只需要一次读取数据。所以有这个广为人传的一个优化方法，那就是增大sort_buffer_size。 Filesort第二种算法要用到更的空间，sort_buffer_size不够大反而会影响速度，所以mysql开发团队定了个变量 max_length_for_sort_data，当算法中读出来的需要列的数据的大小超过该变量的值才使用，所以一般性能分析的时候会尝试把 max_length_for_sort_data改小。
(2.2)单独order by 用不了索引，索引考虑加where 或加limit
先建一个索引(last_login),建的过程就不给出了
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> explain
select
*
from
one
order
by
last_login
desc
;
+
----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
| 1 | SIMPLE | one |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 2046
3 | Using filesort |
+
----+-------------+-------+------+---------------+------+---------+------+-------+----------------+
1 row
in
set
(0.00 sec)
mysql> explain
select
*
from
one
order
by
last_login
desc
limit 10;
+
----+-------------+-------+-------+---------------+------------+---------+------+------+-------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref
|
rows
| Extra |
+
----+-------------+-------+-------+---------------+------------+---------+------+------+-------+
| 1 | SIMPLE | one |
index
|
NULL
| last_login | 4 |
NULL
| 10 | |
+
----+-------------+-------+-------+---------------+------------+---------+------+------+-------+
1 row
in
set
(0.00 sec)
|
开始没limit查询是遍历表的，加了limit后，索引可以使用，看key_len 和key
(2.3)where + orerby 类型，where满足最左前缀原则，且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引，where满足最左前缀原则且order by中列a、b、c的任意组合
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
and
password
=
‘123456
‘
and
last_login=
‘1338251001‘
order
by
password
desc
,last_login
desc
;
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref
|
rows
| Extra |
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,c
onst,const | 1 | Using
where
|
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-------------+
1 row
in
set
(0.00 sec)
mysql> explain
select
*
from
one
where
username=
‘abgvwfnt‘
and
password
=
‘123456
‘
and
last_login=
‘1338251001‘
order
by
password
desc
,
level
desc
;
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+----------------------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref|
rows
| Extra |
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+
| 1 | SIMPLE | one | ref | username | username | 83 | const,c
onst,const | 1 | Using
where
; Using filesort |
+
----+-------------+-------+------+---------------+----------+---------+-------------------+------+-----------------------------+
1 row
in
set
(0.00 sec)
|
上面两条语句明显的区别是多了一个非索引列level的排序，在extra这列对了Using filesort
笔者测试结果：where满足最左前缀且order by中的列是该多列索引的子集时(也就是说orerby中没最左前缀原则限制)，不管是否有asc ,desc混合出现，都能用索引来满足order by。
笔者测试过，因为篇幅比较大，这里就不一一列出。
Ps:很优化博文都说order by中的列要where中出现的列(是索引)的顺序一致，笔者认为不够严谨。
(2.3) where + orerby+limit
这个其实也差不多，只要where最左前缀，orderby也正确，limit在此影响不大
(2.4)如何考虑order by来建索引
这个回归到创建索引的问题来，在比较常用的oder by的列和where中常用的列建立多列索引，这样优化起来的广度和扩张性都比较好，当然如果要考虑UNION、JOIN、COUNT、IN等进来就复杂很多了
(3) 隔离列
隔离列是只查询语句中把索引列隔离出来，也就是说不能在语句中把列包含进表达式中，如id+1=2、 inet_aton(’210.38.196.138′)—ip转换成整数、convert(123,char(3))—数字转换成字符串、date函数 等mysql内置的大多函数。
非隔离列影响性能很大甚至是致命的，这也就是赶集网石展的《三十六军规》中的一条，虽然他没说明是隔离列。
以下就测试一下：
首先建立一个索引(last_login )，这里就不给出建立的代码了，且把last_login改成整型(这里只是为了方便测试，并不是影响条件)
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
mysql> explain
select
*
from
one
where
last_login = 8388605;
+
----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+
| 1 | SIMPLE | one | ref | last_login | last_login | 3 | const
| 1 | Using
where
|
+
----+-------------+-------+------+---------------+------------+---------+-------+-------+-------------+
1 row
in
set
, 1 warning (0.00 sec)
容易看出建的索引已起效
mysql> explain
select
*
from
one
where
last_login +1= 8388606 ;
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one |
ALL
|
NULL
|
NULL
|
NULL
|
NULL
| 2049
7 | Using
where
|
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row
in
set
(0.00 sec)
|
last_login +1=8388608非隔离列的出现导致查找的列20197，说明是遍历整张表且索引不能使用。
这是因为这条语句要找出所有last_login的数据，然后+1再和20197比较，优化器在这方面比较差，性能很差。
所以要尽可能的把列隔离出来，如last_login +1=8388606改成login_login=8388607,或者把计算、转换等操作先用php函数处理过再传递给mysql服务器
(4) OR、IN、UNION ALL，可以尝试用UNION ALL
(4.1)or会遍历表就算有索引
| 1 2 3 4 5 6 7 |
mysql> explain
select
*
from
one
where
username =
‘abgvwfnt‘
or
password
=
‘123456‘
;
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type |
table
| type | possible_keys |
key
| key_len | ref |
rows
| Extra |
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | one |
ALL
| username |
NULL
|
NULL
|
NULL
| 20259 | Using
where
|
+
----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
1 row
in
set
(0.00 sec)
|
(4.2)对于in，这个是有争议的，网上很多优化方案中都提到尽量少用in，这不全面，其实在in里面如果是常量的话，可一大胆的用in，这个也 是赶集网石展、阿里hellodab的观点(笔者从微博中获知)。应用hellodab一句话“MySQL用IN效率不好，通常是指in中嵌套一个子查 询，因为MySQL的查询重写可能会产生一个不好的执行计划，而如果in里面是常量的话，我认为性能没有任何问题，可以放心使用”———当然对于这个比较 的话，没有实战数据的话很难辩解，就算有，影响性能的因素也很多，也许会每个dba都有不同的测试结果.这也签名最左前缀中“补洞”一个方法
(4.3)UNION All 直接返回并集，可以避免去重的开销。之所说“尝试”用UNION All 替代 OR来优化sql语句，因为这不是一直能优化的了，这里只是作为一个方法去尝试。
(5) 索引选择性
索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值，索引选择性=基数/数据行，基数可以通过“show index from 表名”查看。
高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行，唯一索引的选择性最佳，值为1。
那么对于非唯一索引或者说要被创建索引的列的数据内容很长，那就要选择索引前缀。这里就简单说明一下：
| 1 2 3 4 5 6 7 |
mysql>
select
count
(
distinct
(username))/
count
(*)
from
one;
+
------------------------------------+
|
count
(
distinct
(username))/
count
(*) |
+
------------------------------------+
| 0.2047 |
+
------------------------------------+
1 row
in
set
(0.09 sec)
|
count(distinct(username))/count(*)就是索引选择性的值，这里0.2太小了。
假如username列数据很长，则可以通过
select count(distinct(concat(first_name, left(last_name, N))/count(*) from one;测试出接近1的索引选择性，其中N是索引的长度，穷举法去找出N的值，然后再建索引。
(6) 重复或多余索引
很多phper开始都以为建索引相对多点性能就好点，压根没考虑到有些索引是重复的，比如建一个(username), (username,password), (username,password,last_login),很明显第一个索引是重复的，因为后两者都能满足其功能。
要有个意识就是，在满足功能需求的情况下建最少索引。对于INNODB引擎的索引来说，每次修改数据都要把主键索引，辅助索引中相应索引值修改，这可能会出现大量数据迁移，分页，以及碎片的出现。
3、系统配置与维护优化
(1) 重要的一些变量
l key_buffer_size索引块缓存区大小, 针对MyISAM存储引擎,该值越大,性能越好.但是超过操作系统能承受的最大值,反而会使mysql变得不稳定. —-这是很重要的参数
l sort_buffer_size 这是索引在排序缓冲区大小，若排序数据大小超过该值，则创建临时文件，注意和myisam_sort_buffer_size的区别—-这是很重要的参数
l read_rnd_buffer_size当排序后按排序后的顺序读取行时，则通过该缓冲区读取行，避免搜索硬盘。将该变量设置为较大的值可以大大改进 ORDER BY的性能。但是，这是为每个客户端分配的缓冲区，因此你不应将全局变量设置为较大的值。相反，只为需要运行大查询的客户端更改会话变量
l join_buffer_size用于表间关联(join)的缓存大小
l tmp_table_size缓存表的大小
l table_cache允许 MySQL 打开的表的最大个数，并且这些都cache在内存中
l delay_key_write针对MyISAM存储引擎,延迟更新索引.意思是说,update记录时,先将数据up到磁盘,但不up索引,将索引存在内存里,当表关闭时,将内存索引,写到磁盘
更多参数查看http://www.phpben.com/?post=70
(2) optimize、Analyze、check、repair维护操作
l optimize 数据在插入，更新，删除的时候难免一些数据迁移，分页，之后就出现一些碎片，久而久之碎片积累起来影响性能，这就需要DBA定期的优化数据库减少碎片，这就通过optimize命令。
如对MyisAM表操作：optimize table 表名
对于InnoDB表是不支持optimize操作，否则提示“Table does not support optimize, doing recreate + analyze instead”，当然也可以通过命令：alter table one type=innodb; 来替代。
l Analyze 用来分析和存储表的关键字的分布，使得系统获得准确的统计信息，影响 SQL 的执行计划的生成。对于数据基本没有发生变化的表，是不需要经常进行表分析的。但是如果表的数据量变化很明显，用户感觉实际的执行计划和预期的执行计划不 同的时候，执行一次表分析可能有助于产生预期的执行计划。
Analyze table 表名
l Check检查表或者视图是否存在错误，对 MyISAM 和 InnoDB 存储引擎的表有作用。对于 MyISAM 存储引擎的表进行表检查，也会同时更新关键字统计数据
l Repair optimize需要有足够的硬盘空间，否则可能会破坏表，导致不能操作，那就要用上repair，注意INNODB不支持repair操作
以上的操作出现的都是如下这是check
+———-+——-+————–+————-+
| Table | Op | Msg_type| Msg_text |
+———-+——-+————–+————-+
| test.one | check | status | OK |
+———-+——-+————–+————-+
其中op是option 可以是repair check optimize，msg_type 表示信息类型，msg_text 表示信息类型，这里就说明表的状态正常。如在innodb表使用repair就出现note | The storage engine for the table doesn’t support repair
注意：以上操作最好在数据库访问量最低的时候操作，因为涉及到很多表锁定，扫描，数据迁移等操作，否则可能导致一些功能无法正常使用甚至数据库崩溃。
(3)表结构的更新与维护
l 改表结构。当要在数据量千万级的数据表中使用alter更改表结构的时候，这是一个棘手问题。一种方法是在低并发低访问量的时候用平常的alter更改 表。另外一种就是建另一个与要修改的表，这个表除了要修改的结构属性外其他的和原表一模一样，这样就能得到一个相应的.frm文件，然后用flush with read lock 锁定读，然后覆盖用新建的.frm文件覆盖原表的.frm，最后unlock table 释放表。
l 建立新的索引。一般方法这里不说。
1、 创建没索引的a表，导入数据形成.MYD文件。
2、 创建包括索引b表，形成.FRM和.MYI文件
3、 锁定读写
4、 把b表的.FRM和.MYI文件改成a表名字
5、 解锁
6、 用repair创建索引。
这个方法对于大表也是很有效的。这也是为什么很多dba坚持说“先导数据库在建索引，这样效率更快”
l 定期检查mysql服务器
定期使用show status、show processlist等命令检查数据库。这里就不细说，这说起来也篇幅是比较大的，笔者对这个也不是很了解
第四部分：图说mysql查询执行流程
1、 查询缓存，判断sql语句是否完全匹配，再判断是否有权限，两个判断为假则到解析器解析语句，为真则提取数据结果返回给用户。
2、 解析器解析。解析器先词法分析，语法分析，检查错误比如引号有没闭合等，然后生成解析树。
3、 预处理。预处理解决解析器无法决解的语义，如检查表和列是否存在，别名是否有错，生成新的解析树。
4、 优化器做大量的优化操作。
5、 生成执行计划。
6、 查询执行引擎，负责调度引擎获取相应数据
7、 返回结果。
由浅入深探究 MySQL索引结构原理、性能分析与优化
标签：