索引 - 入门
2018-06-24 本文已影响9人
诺之林
本文的示例代码参考index_basic
目录
准备
MySQL
docker run --name mysql-index -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.17
关于Docker更多参考Docker入门
DB
docker exec -i mysql-index mysql -uroot -p123456 <<< "CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARSET utf8 COLLATE utf8_general_ci;"
Table
vim init.sql
CREATE TABLE `user` (
`id` INT unsigned NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(50) NOT NULL DEFAULT '',
`last_name` VARCHAR(50) NOT NULL DEFAULT '',
`age` INT NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
docker exec -i mysql-index mysql -uroot -p123456 demo < ./init.sql
Data
vim init.php
<?php
function connectDB() {
$db = array(
'host' => '127.0.0.1',
'port' => 3306,
'username' => 'root',
'password' => '123456',
'dbname' => 'demo',
);
$mysqli = new mysqli($db['host'], $db['username'], $db['password'], $db['dbname'], $db['port']);
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . '): '. $mysqli->connect_error);
}
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 2);
$mysqli->real_connect($db['host'], $db['username'], $db['password'], $db['dbname'], $db['port']);
return $mysqli;
}
function generateName() {
$username = '';
$length = mt_rand(3, 8);
$chars = ['a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
for ($i = 0; $i < $length; $i ++) {
$username = $username.($chars[mt_rand(0, count($chars) - 1)]);
}
return $username;
}
function generateUser() {
$data = [];
$data['first_name'] = generateName();
$data['last_name'] = generateName();
$data['age'] = mt_rand(10, 50);
return $data;
}
function main() {
$mysqli = connectDB();
$count = 1000;
$times = 1000;
for ($i = 0; $i < $count; $i++) {
$sql = "INSERT INTO user(`first_name`, `last_name`, `age`) VALUES";
for ($j = 0; $j < $times; $j++) {
$user = generateUser();
$sql = $sql . "('" . $user['first_name'] . "', '" . $user['last_name'] . "', " . $user['age'] . ')';
if ($j < $times - 1) {
$sql = $sql . ', ';
}
}
$ret = $mysqli->query($sql);
if (! $ret) {
echo $mysqli->error;
}
}
}
main();
php init.php
PHP环境搭建和扩展安装 详细参考PHP开发 之 开发环境 & PHP开发 之 开发调试
索引
Primary Key
SHOW INDEX FROM user;
SELECT * FROM `user` WHERE id = '66666';
-- 0.001s elapsed
SELECT * FROM `user` WHERE first_name = 'fcl';
-- 0.404s elapsed
Index
ALTER TABLE `user` ADD INDEX first_last_age(first_name, last_name, age);
SHOW INDEX FROM user;
SELECT * FROM `user` WHERE first_name = 'fcl';
-- 0.001s elapsed
SELECT * FROM `user` WHERE first_name LIKE 'fcl%';
-- 0.001s elapsed
Invalid
SELECT * FROM `user` WHERE last_name = 'fcl';
-- 0.412s elapsed
SELECT * FROM `user` WHERE first_name LIKE '%fcl';
-- 0.558s elapsed
有效索引
全值匹配
SELECT * FROM `user` WHERE id = '66666';
-- 0.001s elapsed
SELECT * FROM `user` WHERE first_name = 'fcl';
前缀索引
SELECT * FROM `user` WHERE first_name = 'fcl';
-- 0.001s elapsed
SELECT * FROM `user` WHERE last_name = 'fcl';
-- 0.412s elapsed
SELECT * FROM `user` WHERE first_name LIKE 'fcl%';
-- 0.001s elapsed
SELECT * FROM `user` WHERE first_name LIKE '%fcl';
-- 0.558s elapsed
