黑马程序员技术交流社区

标题: 【广州校区】+【原创】mysql优化 [打印本页]
作者: yqw_gz_java    时间: 2019-6-14 15:23
标题: 【广州校区】+【原创】mysql优化
本帖最后由 yqw_gz_java 于 2019-6-17 16:29 编辑

1：当只要一行数据时使用 LIMIT 1
当你查询表的有些时候，你已经知道结果只会有一条结果，但因为你可能需要去fetch游标，或是你也许会去检查返回的记录数。
在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
[AppleScript] 纯文本查看 复制代码
[mw_shl_code=applescript,true]SELECT * FROM user WHERE country = 'China'



SELECT 1 FROM user WHERE country = 'China' LIMIT 1
[/mw_shl_code]
假如你这边只是为了，查询有没有中国用户存在，那么第二句，效率明显高于第一句。
2：给平凡查询的字段加索引，为了测试先来创建一张表往里面添加100000条记录
[AppleScript] 纯文本查看 复制代码
CREATE TABLE users(

name VARCHAR(10),

id INT

);
插入数据的代码
[AppleScript] 纯文本查看 复制代码
public class Cp30Demo {



    public static void main(String[] args) {

        try {

            DataSource dataSource = new ComboPooledDataSource();

            Connection con = dataSource.getConnection();

            con.setAutoCommit(false);



            PreparedStatement psm = con.prepareStatement("INSERT  INTO users(name,id) value(?,?)");

            for (int i = 0; i < 5000000; i++) {

                psm.setString(1,getRandomString2(5));

                psm.setString(2,i+"");

                psm.addBatch();//添加到批次

            }

            psm.executeBatch();//提交批处理

            con.commit();//执行

            con.close();

        }catch (Exception e){

        e.printStackTrace();}

    }







    public static String getRandomString2(int length){

        Random random=new Random();

        StringBuffer sb=new StringBuffer();

        for(int i=0;i<length;i++){

            int number=random.nextInt(3);

            long result=0;

            switch(number){

                case 0:

                    result=Math.round(Math.random()*25+65);

                    sb.append(String.valueOf((char)result));

                    break;

                case 1:

                    result=Math.round(Math.random()*25+97);

                    sb.append(String.valueOf((char)result));

                    break;

                case 2:

                    sb.append(String.valueOf(new Random().nextInt(10)));

                    break;

            }

        }

        return sb.toString();

    }

    @Test

    public void testindex(){

        try {

            DataSource dataSource = new ComboPooledDataSource();

            Connection con = dataSource.getConnection();

            PreparedStatement psm = con.prepareStatement("SELECT * FROM users WHERE name='vC'");

            long l = System.currentTimeMillis();

            boolean execute = psm.execute();

            System.out.println(System.currentTimeMillis()-l);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }







    }
在建索引之前
[AppleScript] 纯文本查看 复制代码
    @Test

    public void testindex(){

        try {

            DataSource dataSource = new ComboPooledDataSource();

            Connection con = dataSource.getConnection();

            PreparedStatement psm = con.prepareStatement("SELECT * FROM users WHERE name='vcpwT'");

            long l = System.currentTimeMillis();

            boolean execute = psm.execute();

            System.out.println(System.currentTimeMillis()-l);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }
运行结果
[AppleScript] 纯文本查看 复制代码
C:\java8\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\lib\idea_rt.jar=2938:C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\lib\idea_rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\plugins\junit\lib\junit-rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\plugins\junit\lib\junit5-rt.jar;C:\java8\jre\lib\charsets.jar;C:\java8\jre\lib\deploy.jar;C:\java8\jre\lib\ext\access-bridge-64.jar;C:\java8\jre\lib\ext\cldrdata.jar;C:\java8\jre\lib\ext\dnsns.jar;C:\java8\jre\lib\ext\jaccess.jar;C:\java8\jre\lib\ext\jfxrt.jar;C:\java8\jre\lib\ext\localedata.jar;C:\java8\jre\lib\ext\nashorn.jar;C:\java8\jre\lib\ext\sunec.jar;C:\java8\jre\lib\ext\sunjce_provider.jar;C:\java8\jre\lib\ext\sunmscapi.jar;C:\java8\jre\lib\ext\sunpkcs11.jar;C:\java8\jre\lib\ext\zipfs.jar;C:\java8\jre\lib\javaws.jar;C:\java8\jre\lib\jce.jar;C:\java8\jre\lib\jfr.jar;C:\java8\jre\lib\jfxswt.jar;C:\java8\jre\lib\jsse.jar;C:\java8\jre\lib\management-agent.jar;C:\java8\jre\lib\plugin.jar;C:\java8\jre\lib\resources.jar;C:\java8\jre\lib\rt.jar;C:\Users\yqw\Desktop\jiuye\out\production\jiuye;C:\Users\yqw\Desktop\jiuye\lib\c3p0-0.9.5.2.jar;C:\Users\yqw\Desktop\jiuye\lib\druid-1.0.9.jar;C:\Users\yqw\Desktop\jiuye\lib\mchange-commons-java-0.2.12.jar;C:\Users\yqw\Desktop\jiuye\lib\mysql-connector-java-5.1.37-bin.jar;C:\Users\yqw\Desktop\jiuye\lib\c3p0-0.9.5.2-sources.jar;C:\Users\yqw\.m2\repository\junit\junit\4.12\junit-4.12.jar;C:\Users\yqw\.m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.itheima._04cp3o.Cp30Demo,testindex

六月 15, 2019 9:24:27 上午 com.mchange.v2.log.MLog 

信息: MLog clients using java 1.4+ standard logging.

六月 15, 2019 9:24:28 上午 com.mchange.v2.c3p0.C3P0Registry 

信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]

六月 15, 2019 9:24:28 上午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 

信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 5000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgerr5a3omcukmg48chl|65e579dc, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgerr5a3omcukmg48chl|65e579dc, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://192.168.136.128:3306/day17, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

1832


添加索引
[AppleScript] 纯文本查看 复制代码
ALTER TABLE `users` ADD UNIQUE INDEX `name` (`name`) USING BTREE
运行结果
[AppleScript] 纯文本查看 复制代码
C:\java8\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\lib\idea_rt.jar=3083:C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\bin" -Dfile.encoding=UTF-8 -classpath "C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\lib\idea_rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\plugins\junit\lib\junit-rt.jar;C:\Program Files\JetBrains\IntelliJ IDEA 2019.1.2\plugins\junit\lib\junit5-rt.jar;C:\java8\jre\lib\charsets.jar;C:\java8\jre\lib\deploy.jar;C:\java8\jre\lib\ext\access-bridge-64.jar;C:\java8\jre\lib\ext\cldrdata.jar;C:\java8\jre\lib\ext\dnsns.jar;C:\java8\jre\lib\ext\jaccess.jar;C:\java8\jre\lib\ext\jfxrt.jar;C:\java8\jre\lib\ext\localedata.jar;C:\java8\jre\lib\ext\nashorn.jar;C:\java8\jre\lib\ext\sunec.jar;C:\java8\jre\lib\ext\sunjce_provider.jar;C:\java8\jre\lib\ext\sunmscapi.jar;C:\java8\jre\lib\ext\sunpkcs11.jar;C:\java8\jre\lib\ext\zipfs.jar;C:\java8\jre\lib\javaws.jar;C:\java8\jre\lib\jce.jar;C:\java8\jre\lib\jfr.jar;C:\java8\jre\lib\jfxswt.jar;C:\java8\jre\lib\jsse.jar;C:\java8\jre\lib\management-agent.jar;C:\java8\jre\lib\plugin.jar;C:\java8\jre\lib\resources.jar;C:\java8\jre\lib\rt.jar;C:\Users\yqw\Desktop\jiuye\out\production\jiuye;C:\Users\yqw\Desktop\jiuye\lib\c3p0-0.9.5.2.jar;C:\Users\yqw\Desktop\jiuye\lib\druid-1.0.9.jar;C:\Users\yqw\Desktop\jiuye\lib\mchange-commons-java-0.2.12.jar;C:\Users\yqw\Desktop\jiuye\lib\mysql-connector-java-5.1.37-bin.jar;C:\Users\yqw\Desktop\jiuye\lib\c3p0-0.9.5.2-sources.jar;C:\Users\yqw\.m2\repository\junit\junit\4.12\junit-4.12.jar;C:\Users\yqw\.m2\repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 com.itheima._04cp3o.Cp30Demo,testindex

六月 15, 2019 9:26:35 上午 com.mchange.v2.log.MLog 

信息: MLog clients using java 1.4+ standard logging.

六月 15, 2019 9:26:35 上午 com.mchange.v2.c3p0.C3P0Registry 

信息: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]

六月 15, 2019 9:26:35 上午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 

信息: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 5000, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hgerr5a3omfkzeukux9|65e579dc, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hgerr5a3omfkzeukux9|65e579dc, idleConnectionTestPeriod -> 0, initialPoolSize -> 10, jdbcUrl -> jdbc:mysql://192.168.136.128:3306/day17, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

12
注意：查询的时候不要使用模糊查询不然加索引和没加索引，一样的效果
3：用多个小表代替一个大表，注意不要过度设计
4：批量插入代替循环单条插入






作者: 会飞的小蚂蚁    时间: 2019-6-15 14:06
非常感谢，在此谢过



欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) 黑马程序员IT技术论坛 X3.2