mysql索引结构原理、性能分析与优化 - 数据库编程

值然后在去检索主键索引找到对应的数据。这也是网上很多 mysql性能优化时提到的“主键尽可能简短”的原因，主键越长辅助索引也就越大，当然主键索引也越大。

MyISAM索引与InnoDB索引相比较

MyISAM支持全文索引（FULLTEXT）、压缩索引，InnoDB不支持InnoDB支持事务，MyISAM不支持MyISAM顺序储存数据，索引叶子节点保存对应数据行地址，辅助索引很主键索引相差无几；InnoDB主键节点同时保存数据行，其他辅助索引保存的是主键索引的值MyISAM键值分离，索引载入内存（key_buffer_size）,数据缓存依赖操作系统；InnoDB键值一起保存，索引与数据一起载入InnoDB缓冲池MyISAM主键（唯一）索引按升序来存储存储，InnoDB则不一定MyISAM索引的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值。这里涉及到信息统计的知识，MyISAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内MyISAM处理字符串索引时用增量保存的方式，如第一个索引是‘preform’，第二个是‘preformence’，则第二个保存是‘7，ance‘，这个明显的好处是缩短索引，但是缺陷就是不支持倒序提取索引，必须顺序遍历获取索引

第三部分：MYSQL优化

mysql优化是一个重大课题之一，这里会重点详细的介绍mysql优化，包括表数据类型选择，sql语句优化，系统配置与维护优化三类。

表数据类型选择

能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。避免用NULL，这个也是网上优化技术博文传的最多的一个。理由是额外增加字节，还有使索引，索引统计和值更复杂。很多还忽略一个count(列)的问题，count(列)是不会统计列值为null的行数。字符串如何选择char和varchar？一般phper能想到就是char是固定大小，varchar能动态储存数据。这里整理一下这两者的区别：

属性	Char	Varchar
值域大小	最长字符数是 255（不是字节），不管什么编码，超过此值则自动截取255个字符保存并没有报错。	65535 个字节，开始两位存储长度，超过 255 个字符，用 2 位储存长度，否则 1 位，具体字符长度根据编码来确定，如 utf8 则字符最长是 21845 个
如何处理字符串末尾空格	去掉末尾空格，取值出来比较的时候自动加上进行比较	Version<=4.1 ，字符串末尾空格被删掉， version>5.0 则保留
储存空间	固定空间，比喻 char(10) 不管字符串是否有 10 个字符都分配 10 个字符的空间	Varchar 内节约空间，但更新可能发生变化，若 varchar(10), 开始若储存 5 个字符，当 update 成 7 个时有 MyISAM 可能把行拆开， innodb 可能分页，这样开销就增大
适用场合	适用于存储很短或固定或长度相似字符，如 MD5 加密的密码 char(33) 、昵称 char(8) 等	当最大长度远大于平均长度并且发生更新的时候。

注意当一些英文或数据的时候，最好用每个字符用字节少的类型，如latin1

整型、整形优先原则

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

精确度与空间的转换。在存储相同数值范围的数据时，浮点数类型通常都会比DECIMAL类型使用更少的空间。FLOAT字段使用4 字节存储数据。DOUBLE类型需要8 个字节并拥有更高的精确度和更大的数值范围，DECIMAL类型的数据将会转换成DOUBLE类型。

sql语句优化

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多条数据.

| 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      |         |  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+

最左前缀原则

定义：最左前缀原则指的的是在sql

mysql索引结构原理、性能分析与优化(二)

MyISAM索引与InnoDB索引相比较

第三部分：MYSQL优化

表数据类型选择

sql语句优化

最左前缀原则