MySQL9：索引实战 （转）
构建50万条数据过程：
DROP TABLE IF EXISTS `students`; CREATE TABLE `students` ( `s_id` int(11) NOT NULL AUTO_INCREMENT, `s_name` varchar(100) DEFAULT NULL, `s_age` int(11) DEFAULT NULL, `s_phone` varchar(30) DEFAULT NULL, PRIMARY KEY (`s_id`), KEY `s_name` (`s_name`) ) ENGINE=InnoDB, CHARSET=utf8; CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1 BEGIN DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE return_str varchar(255) DEFAULT '' ; DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); SET i = i +1; END WHILE; RETURN return_str; END CREATE PROCEDURE `add_student_memory`(IN n int) BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n ) DO INSERT into students (s_name, s_age, s_phone) VALUEs (rand_string(20), FLOOR(RAND() * 100), FLOOR(RAND() * 100000)); set i=i+1; END WHILE; END 4、调用存储过程 CALL add_student_memory(5000000) // 50万数据
索引
无论是面试，还是实际工作中，对于一个Java程序员来说，数据库优化是避不开的一个技术点，关于数据库的优化，在性能达不到要求的情况下，我大致给出以下几个方向：
（1）优化表结构，对常用字段和非常用的字段分开存储
（2）优化SQL，合理使用索引
（3）做数据库读写分离，减少IO压力，由于数据库对记录做了持久化并存储在磁盘上，对磁盘的I/O又是非常消耗性能的操作，因此读、写都在一个库中会大大增加I/O的压力
（4）尝试使用缓存，不要让数据都走数据库
（5）对业务做垂直拆分
（6）对表做水平拆分，这一步比较麻烦，要注意主键生成规则以及请求路由规则
以上6个点是有优先级的，本文关注的是第二点的索引部分。正确合理地使用索引对于数据库性能提升是至关重要的，本文暂时不分析索引原理，只是从实战的角度，总结一下索引的使用技巧，理论结合实践，印象会更深一些。
当然，事前我已经建立了一张很简单的student表并向表中插入了10万条数据，SQL为：
使用普通索引与不使用普通索引的区别
先看一下不使用普通索引，进行查询，执行SQL语句：
select * from student where s_name = "99999ssss";
看一下查询时间：
花费了0.179秒，使用explain查看一下该条SQL语句的执行情况：
分析几个关键信息：
- select_type：SIMPLE，这个不是很关键，只是表示这是一次简单的查询，没有join，没有union，没有中间表
- type：ALL，表示该次SQL进行了全表查询
- key：MySQL使用的索引名，这里null表示此次SQL查询MySQL并没有使用索引
- rows：这个是最关键的，表示这次SQL查询了100665条记录
OK，接下来给s_name这一列加上普通索引：
alter table student add index s_name(s_name);
看一下运行结果：
看到在s_name上加上索引之后，查询速度马上快了3倍以上。
从分析结果上来看，由于此次SQL对列s_name使用了索引，因此rows只查了1条记录，大大提升了查询效率。
把索引建立在有大量重复数据的字段上
把索引建立在有大量重复数据的字段上，并不能有效地提升SQL效率，比如我的s_phone的取值为"00000000"~"99999999"，此时对s_phone做查询，未加索引的时候：
看到这条select语句的查询时间是0.05秒，而给s_phone字段加了索引之后：
反而变为了0.064秒，并没有显著地提升查询效率，反而更加缓慢。通过explain语句，发现此次SQL通过索引查询了18000条rows，再去定位这18000多条数据，自然会慢一点。
这说明了，即使查询的时候用到了索引，也未必能提升查询的效率，索引建立在重复数据量很少的字段上效果才明显，但是这也将导致索引的增大，不过大多数时候这并不是太大的问题。
索引与like
不建议对索引列使用like语句，比如说执行以下两句SQL：
select * from student where s_name like "%99999ssss%"; select * from student where s_name like "%99999ssss";
看一下explain出来的结果，都是一样的：
发现没有用到索引，这是对索引列使用like的限制，要对索引列使用like，通配符只能在结尾，开头不可以有任何的通配符，比如：
select * from student where s_name like "99999ssss%";
此时再explain看一下：
看到这么实用like则使用到了索引，这不得不说是一个限制。
索引与函数
在索引列上使用MySQL函数也会导致索引失效，看一个例子：
select * from student where "99999ssss" = left(s_name, 9);
这条SQL语句非常好理解，查询s_name列中从左边开始截取9个字符后的字符串为"99999ssss"的记录，查看一下explain的结果：
结果很明显，没有用到索引，这表明对索引列使用函数将导致索引失效。
一个技巧是，依然使用=，但是索引列不使用函数而对常数项使用函数，这样索引就有效了，当然这条语句是无法这么优化的。
我不能保证写的每个地方都是对的，但是至少能保证不复制、不黏贴，保证每一句话、每一行代码都经过了认真的推敲、仔细的斟酌。每一篇文章的背后，希望都能看到自己对于技术、对于生活的态度。
我相信乔布斯说的，只有那些疯狂到认为自己可以改变世界的人才能真正地改变世界。面对压力，我可以挑灯夜战、不眠不休；面对困难，我愿意迎难而上、永不退缩。
其实我想说的是，我只是一个程序员，这就是我现在纯粹人生的全部。
DROP TABLE IF EXISTS `students`;CREATE TABLE `students` ( `s_id` int(11) NOT NULL AUTO_INCREMENT, `s_name` varchar(100) DEFAULT NULL, `s_age` int(11) DEFAULT NULL, `s_phone` varchar(30) DEFAULT NULL, PRIMARY KEY (`s_id`), KEY `s_name` (`s_name`)) ENGINE=InnoDB, CHARSET=utf8;
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1BEGIN DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; DECLARE return_str varchar(255) DEFAULT '' ;DECLARE i INT DEFAULT 0; WHILE i < n DO SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1)); SET i = i +1; END WHILE; RETURN return_str; END
CREATE PROCEDURE `add_student_memory`(IN n int)BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= n ) DO INSERT into students (s_name, s_age, s_phone) VALUEs (rand_string(20), FLOOR(RAND() * 100), FLOOR(RAND() * 100000));set i=i+1; END WHILE;END
4、调用存储过程CALL add_student_memory(5000000) // 50万数据
