如题所述
ç´¢å¼æ¯å¿«éæç´¢çå ³é®ãMySQLç´¢å¼ç建ç«å¯¹äºMySQLçé«æè¿è¡æ¯å¾éè¦çãä¸é¢ä»ç»å ç§å¸¸è§çMySQLç´¢å¼ç±»åã
å¨æ°æ®åºè¡¨ä¸ï¼å¯¹å段建ç«ç´¢å¼å¯ä»¥å¤§å¤§æé«æ¥è¯¢é度ãåå¦æ们å建äºä¸ä¸ª mytable表ï¼
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL
); æ们éæºåéé¢æå ¥äº10000æ¡è®°å½ï¼å ¶ä¸æä¸æ¡ï¼5555, adminã
å¨æ¥æ¾username="admin"çè®°å½ SELECT * FROM mytable WHERE
username='admin';æ¶ï¼å¦æå¨usernameä¸å·²ç»å»ºç«äºç´¢å¼ï¼MySQLæ 须任ä½æ«æï¼å³åç¡®å¯æ¾å°è¯¥è®°å½ãç¸åï¼MySQLä¼æ«æææè®°å½ï¼å³è¦æ¥è¯¢10000æ¡è®°å½ã
ç´¢å¼åååç´¢å¼åç»åç´¢å¼ãååç´¢å¼ï¼å³ä¸ä¸ªç´¢å¼åªå å«å个åï¼ä¸ä¸ªè¡¨å¯ä»¥æå¤ä¸ªååç´¢å¼ï¼ä½è¿ä¸æ¯ç»åç´¢å¼ãç»åç´¢å¼ï¼å³ä¸ä¸ªç´¢å å«å¤ä¸ªåã
MySQLç´¢å¼ç±»åå æ¬ï¼
ï¼1ï¼æ®éç´¢å¼
è¿æ¯æåºæ¬çç´¢å¼ï¼å®æ²¡æä»»ä½éå¶ãå®æ以ä¸å ç§å建æ¹å¼ï¼
âå建索å¼
CREATE INDEX indexName ON mytable(username(length));
å¦ææ¯CHARï¼VARCHARç±»åï¼lengthå¯ä»¥å°äºå段å®é é¿åº¦ï¼å¦ææ¯BLOBåTEXTç±»åï¼å¿ é¡»æå® lengthï¼ä¸åã
âä¿®æ¹è¡¨ç»æ
ALTER mytable ADD INDEX [indexName] ON (username(length))
âå建表çæ¶åç´æ¥æå®
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length)) ); å é¤ç´¢å¼çè¯æ³ï¼
DROP INDEX [indexName] ON mytable;
ï¼2ï¼å¯ä¸ç´¢å¼
å®ä¸åé¢çæ®éç´¢å¼ç±»ä¼¼ï¼ä¸åçå°±æ¯ï¼ç´¢å¼åçå¼å¿ é¡»å¯ä¸ï¼ä½å 许æ空å¼ãå¦ææ¯ç»åç´¢å¼ï¼ååå¼çç»åå¿ é¡»å¯ä¸ãå®æ以ä¸å ç§å建æ¹å¼ï¼
âå建索å¼
CREATE UNIQUE INDEX indexName ON mytable(username(length))
âä¿®æ¹è¡¨ç»æ
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
âå建表çæ¶åç´æ¥æå®
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length)) );
ï¼3ï¼ä¸»é®ç´¢å¼
å®æ¯ä¸ç§ç¹æ®çå¯ä¸ç´¢å¼ï¼ä¸å 许æ空å¼ãä¸è¬æ¯å¨å»ºè¡¨çæ¶ååæ¶å建主é®ç´¢å¼ï¼
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,
PRIMARY KEY(ID) ); å½ç¶ä¹å¯ä»¥ç¨ ALTER å½ä»¤ãè®°ä½ï¼ä¸ä¸ªè¡¨åªè½æä¸ä¸ªä¸»é®ã
ï¼4ï¼ç»åç´¢å¼
为äºå½¢è±¡å°å¯¹æ¯ååç´¢å¼åç»åç´¢å¼ï¼ä¸ºè¡¨æ·»å å¤ä¸ªå段ï¼
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,
city VARCHAR(50) NOT NULL, age INT NOT NULL );
为äºè¿ä¸æ¥æ¦¨åMySQLçæçï¼å°±è¦èè建ç«ç»åç´¢å¼ãå°±æ¯å° name, city, age建å°ä¸ä¸ªç´¢å¼éï¼
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);
建表æ¶ï¼usernnameé¿åº¦ä¸º 16ï¼è¿éç¨
10ãè¿æ¯å 为ä¸è¬æ åµä¸ååçé¿åº¦ä¸ä¼è¶ è¿10ï¼è¿æ ·ä¼å éç´¢å¼æ¥è¯¢é度ï¼è¿ä¼åå°ç´¢å¼æ件ç大å°ï¼æé«INSERTçæ´æ°é度ã
å¦æåå«å¨
usernnameï¼cityï¼ageä¸å»ºç«ååç´¢å¼ï¼è®©è¯¥è¡¨æ3个ååç´¢å¼ï¼æ¥è¯¢æ¶åä¸è¿°çç»åç´¢å¼æçä¹ä¼å¤§ä¸ä¸æ ·ï¼è¿è¿ä½äºæ们çç»åç´¢å¼ãè½ç¶æ¤æ¶æäºä¸ä¸ªç´¢å¼ï¼ä½MySQLåªè½ç¨å°å ¶ä¸çé£ä¸ªå®è®¤ä¸ºä¼¼ä¹æ¯æææççååç´¢å¼ã
建ç«è¿æ ·çç»åç´¢å¼ï¼å ¶å®æ¯ç¸å½äºåå«å»ºç«äºä¸é¢ä¸ç»ç»åç´¢å¼ï¼
usernname,city,age usernname,city usernname 为ä»ä¹æ²¡æ
cityï¼ageè¿æ ·çç»åç´¢å¼å¢ï¼è¿æ¯å 为MySQLç»åç´¢å¼âæå·¦åç¼âçç»æãç®åçç解就æ¯åªä»æå·¦é¢çå¼å§ç»åã并ä¸æ¯åªè¦å å«è¿ä¸åçæ¥è¯¢é½ä¼ç¨å°è¯¥ç»åç´¢å¼ï¼ä¸é¢çå 个SQLå°±ä¼ç¨å°è¿ä¸ªç»åç´¢å¼ï¼
SELECT * FROM mytable WHREE username="admin" AND city="éå·" SELECT * FROM
mytable WHREE username="admin" èä¸é¢å 个åä¸ä¼ç¨å°ï¼
SELECT * FROM mytable WHREE age=20 AND city="éå·" SELECT * FROM mytable WHREE
city="éå·"
ï¼5ï¼å»ºç«ç´¢å¼çæ¶æº
å°è¿éæ们已ç»å¦ä¼äºå»ºç«ç´¢å¼ï¼é£ä¹æ们éè¦å¨ä»ä¹æ åµä¸å»ºç«ç´¢å¼å¢ï¼ä¸è¬æ¥è¯´ï¼å¨WHEREåJOINä¸åºç°çåéè¦å»ºç«ç´¢å¼ï¼ä½ä¹ä¸å®å ¨å¦æ¤ï¼å 为MySQLåªå¯¹<ï¼<=ï¼=ï¼>ï¼>=ï¼BETWEENï¼INï¼ä»¥åæäºæ¶åçLIKEæä¼ä½¿ç¨ç´¢å¼ãä¾å¦ï¼
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username
WHERE m.age=20 AND m.city='éå·'
æ¤æ¶å°±éè¦å¯¹cityåage建ç«ç´¢å¼ï¼ç±äºmytable表çuserameä¹åºç°å¨äºJOINåå¥ä¸ï¼ä¹æ对å®å»ºç«ç´¢å¼çå¿ è¦ã
åææå°åªææäºæ¶åçLIKEæé建ç«ç´¢å¼ãå 为å¨ä»¥éé 符%å_å¼å¤´ä½æ¥è¯¢æ¶ï¼MySQLä¸ä¼ä½¿ç¨ç´¢å¼ãä¾å¦ä¸å¥ä¼ä½¿ç¨ç´¢å¼ï¼
SELECT * FROM mytable WHERE username like'admin%' èä¸å¥å°±ä¸ä¼ä½¿ç¨ï¼
SELECT * FROM mytable WHEREt Name like'%admin' å æ¤ï¼å¨ä½¿ç¨LIKEæ¶åºæ³¨æ以ä¸çåºå«ã
ï¼6ï¼ç´¢å¼çä¸è¶³ä¹å¤
ä¸é¢é½å¨è¯´ä½¿ç¨ç´¢å¼ç好å¤ï¼ä½è¿å¤ç使ç¨ç´¢å¼å°ä¼é æ滥ç¨ãå æ¤ç´¢å¼ä¹ä¼æå®ç缺ç¹ï¼
âè½ç¶ç´¢å¼å¤§å¤§æé«äºæ¥è¯¢é度ï¼åæ¶å´ä¼éä½æ´æ°è¡¨çé度ï¼å¦å¯¹è¡¨è¿è¡INSERTãUPDATEåDELETEãå 为æ´æ°è¡¨æ¶ï¼MySQLä¸ä» è¦ä¿åæ°æ®ï¼è¿è¦ä¿åä¸ä¸ç´¢å¼æ件ã
â建ç«ç´¢å¼ä¼å ç¨ç£ç空é´çç´¢å¼æ件ãä¸è¬æ åµè¿ä¸ªé®é¢ä¸å¤ªä¸¥éï¼ä½å¦æä½ å¨ä¸ä¸ªå¤§è¡¨ä¸å建äºå¤ç§ç»åç´¢å¼ï¼ç´¢å¼æ件çä¼è¨èå¾å¿«ã
ç´¢å¼åªæ¯æé«æççä¸ä¸ªå ç´ ï¼å¦æä½ çMySQLæ大æ°æ®éç表ï¼å°±éè¦è±æ¶é´ç 究建ç«æä¼ç§çç´¢å¼ï¼æä¼åæ¥è¯¢è¯å¥ã
ï¼7ï¼ä½¿ç¨ç´¢å¼ç注æäºé¡¹
使ç¨ç´¢å¼æ¶ï¼æ以ä¸ä¸äºæå·§å注æäºé¡¹ï¼
âç´¢å¼ä¸ä¼å å«æNULLå¼çå
åªè¦åä¸å å«æNULLå¼é½å°ä¸ä¼è¢«å å«å¨ç´¢å¼ä¸ï¼å¤åç´¢å¼ä¸åªè¦æä¸åå«æNULLå¼ï¼é£ä¹è¿ä¸å对äºæ¤å¤åç´¢å¼å°±æ¯æ æçãæ以æ们å¨æ°æ®åºè®¾è®¡æ¶ä¸è¦è®©å段çé»è®¤å¼ä¸ºNULLã
â使ç¨çç´¢å¼
对串åè¿è¡ç´¢å¼ï¼å¦æå¯è½åºè¯¥æå®ä¸ä¸ªåç¼é¿åº¦ãä¾å¦ï¼å¦ææä¸ä¸ªCHAR(255)çåï¼å¦æå¨å10个æ20个å符å ï¼å¤æ°å¼æ¯æä¸çï¼é£ä¹å°±ä¸è¦å¯¹æ´ä¸ªåè¿è¡ç´¢å¼ãçç´¢å¼ä¸ä» å¯ä»¥æé«æ¥è¯¢é度èä¸å¯ä»¥èçç£ç空é´åI/Oæä½ã
âç´¢å¼åæåº
MySQLæ¥è¯¢åªä½¿ç¨ä¸ä¸ªç´¢å¼ï¼å æ¤å¦æwhereåå¥ä¸å·²ç»ä½¿ç¨äºç´¢å¼çè¯ï¼é£ä¹order
byä¸çåæ¯ä¸ä¼ä½¿ç¨ç´¢å¼çãå æ¤æ°æ®åºé»è®¤æåºå¯ä»¥ç¬¦åè¦æ±çæ åµä¸ä¸è¦ä½¿ç¨æåºæä½ï¼å°½éä¸è¦å å«å¤ä¸ªåçæåºï¼å¦æéè¦æ好ç»è¿äºåå建å¤åç´¢å¼ã
âlikeè¯å¥æä½
ä¸è¬æ åµä¸ä¸é¼å±ä½¿ç¨likeæä½ï¼å¦æé使ç¨ä¸å¯ï¼å¦ä½ä½¿ç¨ä¹æ¯ä¸ä¸ªé®é¢ãlike â%aaa%â ä¸ä¼ä½¿ç¨ç´¢å¼èlike
âaaa%âå¯ä»¥ä½¿ç¨ç´¢å¼ã
âä¸è¦å¨åä¸è¿è¡è¿ç®
select * from users where YEAR(adddate)<2007;
å°å¨æ¯ä¸ªè¡ä¸è¿è¡è¿ç®ï¼è¿å°å¯¼è´ç´¢å¼å¤±æèè¿è¡å ¨è¡¨æ«æï¼å æ¤æ们å¯ä»¥æ¹æ
select * from users where adddate<â2007-01-01â;
âä¸ä½¿ç¨NOT INå<>æä½
以ä¸ï¼å°±å¯¹å ¶ä¸MySQLç´¢å¼ç±»åè¿è¡äºä»ç»ã
(1)普通索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
◆创建索引
CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
◆修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))
◆创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法:
DROP INDEX [indexName] ON mytable;
(2)唯一索引
与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
◆创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
(3)主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住:一个表只能有一个主键。
(4)组合索引
为了形象地对比单列索引和组合索引,为表添加多个字段:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。
如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:
usernname,city,age usernname,city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:
SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到:
SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"
(5)建立索引的时机
一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:
SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用:
SELECT * FROM mytable WHEREt Name like'%admin' 因此,在使用LIKE时应注意以上的区别。
(6)索引的不足之处
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:
◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
(7)使用索引的注意事项
使用索引时,有以下一些技巧和注意事项:
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成
select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
(1)普通索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
◆创建索引
CREATE INDEX indexName ON mytable(username(length)); 如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
◆修改表结构
ALTER mytable ADD INDEX [indexName] ON (username(length))
◆创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) ); 删除索引的语法:
DROP INDEX [indexName] ON mytable;
(2)唯一索引
与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
◆创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
◆修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
◆创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
(3)主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) ); 当然也可以用 ALTER 命令。记住:一个表只能有一个主键。
(4)组合索引
为了形象地对比单列索引和组合索引,为表添加多个字段:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL ); 为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 建表时,usernname长度为 16,这里用 10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。
如果分别在 usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。
建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:
usernname,city,age usernname,city usernname 为什么没有 city,age这样的组合索引呢?这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:
SELECT * FROM mytable WHREE username="admin" AND city="郑州" SELECT * FROM mytable WHREE username="admin" 而下面几个则不会用到:
SELECT * FROM mytable WHREE age=20 AND city="郑州" SELECT * FROM mytable WHREE city="郑州"
(5)建立索引的时机
一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:
SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' 此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。
刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:
SELECT * FROM mytable WHERE username like'admin%' 而下句就不会使用:
SELECT * FROM mytable WHEREt Name like'%admin' 因此,在使用LIKE时应注意以上的区别。
(6)索引的不足之处
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:
◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
(7)使用索引的注意事项
使用索引时,有以下一些技巧和注意事项:
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
select * from users where YEAR(adddate)<2007; 将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成
select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
在满足语句需求的情况下,尽量少的访问资源是数据库设计的重要原则,这和执行的 SQL 有直接的关系,索引问题又是 SQL 问题中出现频率最高的,常见的索引问题包括:无索引(失效)、隐式转换。
1. SQL 执行流程看一个问题,在下面这个表 T 中,如果我要执行 select * from T where k between 3 and 5; 需要执行几次树的搜索操作,会扫描多少行?mysql> create table T ( -> ID int primary key, -> k int NOT NULL DEFAULT 0, -> s varchar(16) NOT NULL DEFAULT '', -> index k(k)) -> engine=InnoDB;mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),\ (300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
这分别是 ID 字段索引树、k 字段索引树。
这条 SQL 语句的执行流程:
1. 在 k 索引树上找到 k=3,获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录,对应 R33. 在 k 索引树找到下一个值 k=5,ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R4
5. 在 k 索引树去下一个值 k=6,不符合条件,循环结束
这个过程读取了 k 索引树的三条记录,回表了两次。因为查询结果所需要的数据只在主键索引上有,所以必须得回表。所以,我们该如何通过优化索引,来避免回表呢?
2. 常见索引优化2.1 覆盖索引覆盖索引,换言之就是索引要覆盖我们的查询请求,无需回表。
如果执行的语句是 select ID from T wherek between 3 and 5;,这样的话因为 ID 的值在 k 索引树上,就不需要回表了。
覆盖索引可以减少树的搜索次数,显著提升查询性能,是常用的性能优化手段。
但是,维护索引是有代价的,所以在建立冗余索引来支持覆盖索引时要权衡利弊。
2.2 最左前缀原则
B+ 树的数据项是复合的数据结构,比如 (name,sex,age) 的时候,B+ 树是按照从左到右的顺序来建立搜索树的,当 (张三,F,26) 这样的数据来检索的时候,B+ 树会优先比较 name 来确定下一步的检索方向,如果 name 相同再依次比较 sex 和 age,最后得到检索的数据。
# 有这样一个表 P
mysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;
mysql> insert into P values(1,'张三','F',26),(2,'张三','M',27),(3,'李四','F',28),(4,'乌兹','F',22),(5,'张三','M',21),(6,'王五','M',28);
# 下面的语句结果相同
mysql> select * from P where name='张三' and sex='F'; ## A1
mysql> select * from P where sex='F' and age=26; ## A2
# explain 看一下
mysql> explain select * from P where name='张三' and sex='F';
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | P | NULL | ref | tl | tl | 38 | const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+
mysql> explain select * from P where sex='F' and age=26;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | P | NULL | index | NULL | tl | 43 | NULL | 6 | 16.67 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
可以清楚的看到,A1 使用 tl 索引,A2 进行了全表扫描,虽然 A2 的两个条件都在 tl 索引中出现,但是没有使用到 name 列,不符合最左前缀原则,无法使用索引。所以在建立联合索引的时候,如何安排索引内的字段排序是关键。评估标准是索引的复用能力,因为支持最左前缀,所以当建立(a,b)这个联合索引之后,就不需要给 a 单独建立索引。原则上,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。上面这个例子中,如果查询条件里只有 b,就是没法利用(a,b)这个联合索引的,这时候就不得不维护另一个索引,也就是说要同时维护(a,b)、(b)两个索引。这样的话,就需要考虑空间占用了,比如,name 和 age 的联合索引,name 字段比 age 字段占用空间大,所以创建(name,age)联合索引和(age)索引占用空间是要小于(age,name)、(name)索引的。2.3 索引下推
以人员表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是26岁的所有男性”。那么,SQL 语句是这么写的mysql> select * from tuser where name like '张%' and age=26 and sex=M;通过最左前缀索引规则,会找到 ID1,然后需要判断其他条件是否满足在 MySQL 5.6 之前,只能从 ID1 开始一个个回表。到主键索引上找出数据行,再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown),可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。这样,减少了回表次数和之后再次过滤的工作量,明显提高检索速度。
2.4 隐式类型转化
隐式类型转化主要原因是,表结构中指定的数据类型与传入的数据类型不同,导致索引无法使用。所以有两种方案:修改表结构,修改字段数据类型。修改应用,将应用中传入的字符类型改为与表结构相同类型。
3. 为什么会选错索引3.1 优化器选择索引是优化器的工作,其目的是找到一个最优的执行方案,用最小的代价去执行语句。在数据库中,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。3.2 扫描行数
MySQL 在真正开始执行语句之前,并不能精确的知道满足这个条件的记录有多少条,只能通过索引的区分度来判断。显然,一个索引上不同的值越多,索引的区分度就越好,而一个索引上不同值的个数我们称为“基数”,也就是说,这个基数越大,索引的区分度越好。# 通过 show index 方法,查看索引的基数mysql> show index from t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t | 0 | PRIMARY | 1 | id | A | 95636 | NULL | NULL | | BTREE | | || t | 1 | a | 1 | a | A | 96436 | NULL | NULL | YES | BTREE | | || t | 1 | b | 1 | b | A | 96436 | NULL | NULL | YES | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+MySQL 使用采样统计方法来估算基数:采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:
on 表示统计信息会持久化存储。默认 N = 20,M = 10。
off 表示统计信息只存储在内存中。默认 N = 8,M = 16。
由于是采样统计,所以不管 N 是 20 还是 8,这个基数都很容易不准确。所以,冤有头债有主,MySQL 选错索引,还得归咎到没能准确地判断出扫描行数。可以用 analyze table 来重新统计索引信息,进行修正。
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...3.3 索引选择异常和处理1. 采用 force index 强行选择一个索引。2. 可以考虑修改语句,引导 MySQL 使用我们期望的索引。3. 有些场景下,可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引。